Re: Odd results from v$sqlarea

From: Sybrand Bakker <postbus_at_sybrandb.demon.nl>
Date: Thu, 18 Oct 2001 20:15:05 +0200
Message-ID: <3t6ustkqmmbrin76fhgg70dqjn86r0urpc@4ax.com>

On Thu, 18 Oct 2001 14:52:44 GMT, Ed_Stevens_at_nospam.noway.nohow (Ed Stevens) wrote:

>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,
I'm not too sure. It could be sqlnet. You can easily verify this by adding/changing this to sqlnet.ora

trace_level_client = 16
trace_unique_client = true
trace_directory_client = <any sensible dir>

the resulting cli*.trc files will definitely show all the statements executed, and this trace mechanism is not limited to sql*plus

Hth

Sybrand Bakker, Senior Oracle DBA Received on Thu Oct 18 2001 - 13:15:05 CDT