Re: Odd results from v$sqlarea

"Ed Stevens" <Ed_Stevens_at_nospam.noway.nohow> wrote in message news:3bcdc800.17189397_at_ausnews.austin.ibm.com...
> Subject: Odd results from v$sqlarea
>
> In trying to attack some ill-performing SQL, I ran the following
> query:
>
> select b.username username,
> a.disk_reads reads,
> a.executions exec,
> a.disk_reads / decode (a.executions, 0, 1,a.executions)
> rds_exec_ratio,
> a.command_type,
> a.sql_text Statement
> from v$sqlarea a,
> dba_users b
> where a.parsing_user_id = b.user_id and
> a.executions > 100 and
> b.username <> 'SYS' AND
> b.username <> 'SYSTEM'
> Order by a.executions desc;
>
> What was particularly odd about the results is that it returned some
> SQL that was pretty clearly NOT being issued by the application, even
> though the userid associated with the query is used ONLY by the
> application. Things like
>
> select 99 / 100,
> 1 - to_number(to_char(to_date('1997-11-02','yyyy-mm-dd'),
> 'D')),
> ltrim(to_char(to_number(substr(banner,7,2)),'09')||'.00.0000
> ')||banner
> from v$version
>
>
>
> ALTER SESSION SET NLS_LANGUAGE= 'AMERICAN'
> NLS_TERRITORY= 'AMERICA'
> NLS_CURRENCY= '$'
> NLS_ISO_CURRENCY= 'AMERICA'
> NLS_NUMERIC_CHARACTERS= '.,'
> NLS_CALENDAR= 'GREGORIAN'
> NLS_DATE_FORMAT= 'DD-MON-YY'
> NLS_DATE_LANGUAGE= 'AMERICAN'
> NLS_SORT= 'BINARY'
>
> SELECT * FROM SYS.SESSION_ROLES
> WHERE ROLE = 'DBA'
>
>
> It would appear to us that these queries are being executed by Oracle
> at connect time, but under the userid of the connecting session.
>
> We also have some queries that show as being executed by a userid that
> owns some stored procedures but is never used directly for a
> connection. Would it be a true statement to say that when a stored
> procedure is executed it shows in the SQLArea as being from the owing
> userid instead of the connected session userid?
>
> --
> Ed Stevens
> (Opinions expressed do not necessarily represent those of my employer.)

When you connect sql*plus reads the registry or env vars and issues appropiate statements. You will see those statements when executing a sqlnet trace with trace_level 16
As the second issue: yes I would expect this is the case if the procedure or function has not been defined with pragmam authid, to run with the invokers privileges.

Hth,

Sybrand Bakker
Senior Oracle DBA Received on Wed Oct 17 2001 - 16:04:26 CDT