RE: Very high count of SQLs parsed by SYS

From: Hemant K Chitale <hkchital_at_singnet.com.sg>
Date: Tue, 18 May 2004 22:35:23 +0800
Message-Id: <6.1.0.6.0.20040518223339.024a6da8@pop.singnet.com.sg>

At 06:48 AM Tuesday, you wrote:
>Hemant
>
> >Would you define "recursive SQL" as only SQL executed by SYS against
> the DataDictionary
> >{eg Space Transactions or lookups to validate object definitions,
> privleges etc}.
> >Here I do not see "recursive SQL"s in that sense... Could this be DML
> executed
> >by a Procedure/Package or one Package calling another, which seemingly is
> >parsed by SYS ?
>
>This seems very much possible.... Does 10046 tracing show up anything? I
>would like to know where this leads to...
>

This SQL shows me a number of DB sessions where PARSING_USER_ID=0 in V$SQL. The sessions are a mix of user Forms sessions and Concurrent Managers.

select s.sid, to_char(s.logon_time,'DD-MON HH24:MI:SS') logon_time,

        p.pid oraclepid, p.spid "ServerPID", s.process "ClientPID",
        s.program clientprogram, s.module, s.machine, s.osuser,
        f.user_name, f.responsibility_name, f.user_form_name
from apps.fnd_signon_audit_view f, v$session s, v$process p

where 1=1
and s.paddr=p.addr
and p.pid=f.pid(+)
and (s.sql_hash_value, s.sql_address)
in
(
select q.hash_value, q.address
from v$sql q
where
q.parsing_user_id=0
group by q.hash_value, q.address
having count(*) > 10)

will continue digging for information.
The past few days I haven't had much time on the database -- been busy with meetings on
a couple of new projects and RFPs.

Hemant K Chitale
Oracle 9i Database Administrator Certified Professional http://web.singnet.com.sg/~hkchital

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Tue May 18 2004 - 09:40:32 CDT