Re: Odd results from v$sqlarea

On Wed, 17 Oct 2001 23:04:26 +0200, "Sybrand Bakker" <postbus_at_sybrandb.demon.nl> wrote:

>
>"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
>
>
>

Sybrand,

You said "When you connect sql*plus reads the registry or env vars and issues appropiate statements." Is this really SQL*Plus, or something deeper within the SQL*Net client? We are observing this in a production environment where there is little use of SQL*Plus.

--
Ed Stevens
(Opinions expressed do not necessarily represent those of my employer.)