| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: v$sql.parsing_user_id=0 when parser was not SYS
Not to belabor the issue, but you can might try "breaking" one of the rules
in v$sql_shared_cursor to make the target SQL stand out from the rest.
Also, I believe putting a fake hint/comment into the SQL will also do this - effectively making the hash value different.
-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]
On Behalf Of Saibabu Devabhaktuni
Sent: Tuesday, July 25, 2006 8:46 PM
To: oracle-l_at_freelists.org
Subject: RE: v$sql.parsing_user_id=0 when parser was not SYS
You can get owner schema and object name from v$object_dependency.
Join v$sql with v$object_dependency on v$sql.address = v$object_dependecy.from_address and v$sql.hash_value = v$object_dependency.from_hash
I guess "parsing_user_id=0" for non SYS schema issued sql statements has something to do with shared pool fragmentation.
Thanks,
Sai
http://sai-oracle.blogspot.com
Not sure about the parsing_user_id=0, but to answer
your question about
the separate child cursors in v$sql:
No, there should not be a separate child cursor for
each user, there
should only be separate child cursors if for some
reason different
executions of the same query have different schemas,
optimizer parameter
settings, etc. that make it impossible for them to
share the same
cursor. Usually you can see the cause of the multiple
child cursors in
v$sql_shared_cursor, but not always.
Regards,
Brandon
-----Original Message-----
From: oracle-l-bounce_at_xxxxxxxxxxxxx
[mailto:oracle-l-bounce_at_xxxxxxxxxxxxx] On Behalf Of
Jeremiah Wilton
Sent: Tuesday, July 25, 2006 5:11 PM
To: oracle-l_at_xxxxxxxxxxxxx
Subject: v$sql.parsing_user_id=0 when parser was not
SYS
Looking at v$sql, I see that quite often a cursor that
was actually
originally parsed by some user other than SYS shows a
PARSING_USER_ID of
0 (SYS). How can I tell who really parsed this
cursor? Shouldn't there
be a separate child cursor in v$sql for each user that
uses the cursor?
Privileged/Confidential Information may be contained
in this message or
attachments hereto. Please advise immediately if you
or your employer do not
consent to Internet email for messages of this kind.
Opinions, conclusions and
other information in this message that do not relate
to the official business
of this company shall be understood as neither given
nor endorsed by it.
--
http://www.freelists.org/webpage/oracle-l
--
http://www.freelists.org/webpage/oracle-l
Received on Tue Jul 25 2006 - 23:28:03 CDT
 |
 |