RE: Multiple child cursors with no difference in v$sql_shared_cursor, causing invalid statspack results
- From: "Bobak, Mark" <Mark.Bobak@xxxxxxxxxxxxxxx>
- To: <Brandon.Allen@xxxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 7 Dec 2005 19:16:40 -0500
I have heard of bugs where cursors are not shared and nothing is
recorded in V$SQL_SHARED_CURSOR. I think I remember Connor McDonald
mentioning that he ran into this on 9.2.0.x a while a ago....
Hmm...well, either it wasn't Connor, or my incomplete archives don't go
far enough back.....(I lost a bunch when I switched jobs a few months
ago....)
I'm not sure if he's still on this list....maybe he'll reply.
-Mark
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Allen, Brandon
Sent: Wednesday, December 07, 2005 6:44 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: Multiple child cursors with no difference in
v$sql_shared_cursor, causing invalid statspack results
I've got a sql statement coming from a 3rd party app (FacilityCenter8i)
that for some reason is being parsed in multiple cursors even though
there is no difference flagged in v$sql_shared_cursor:
SQL> select address, child_number, child_address, executions, disk_reads
SQL> from v$sql where hash_value = 4167788075;
ADDRESS CHILD_NUMBER CHILD_AD EXECUTIONS DISK_READS
-------- ------------ -------- ---------- ----------
64C5DC9C 0 6E9AE258 0 0
64C5DC9C 1 6FC5C6F8 0 0
64C5DC9C 2 6D1CEF8C 0 0
64C5DC9C 3 687B557C 0 0
64C5DC9C 4 66CE2900 0 0
64C5DC9C 5 68056F58 0 0
64C5DC9C 6 65390A10 0 0
64C5DC9C 7 6DEB1D94 0 0
64C5DC9C 8 64F7E0E8 0 0
64C5DC9C 9 69BF0BC0 0 0
10 rows selected.
SQL> select * from v$sql_shared_cursor where kglhdpar = '64C5DC9C';
ADDRESS KGLHDPAR U S O O S L S E B P I S T A B D L T R I I R L I O S M
U T N F
-------- -------- - - - - - - - - - - - - - - - - - - - - - - - - - - -
- - - -
6E9AE258 64C5DC9C N N N N N N N N N N N N N N N N N N N N N N N N N N N
N N N N
6FC5C6F8 64C5DC9C N N N N N N N N N N N N N N N N N N N N N N N N N N N
N N N N 6D1CEF8C 64C5DC9C N N N N N N N N N N N N N N N N N N N N N N N
N N N N N N N N 687B557C 64C5DC9C N N N N N N N N N N N N N N N N N N N
N N N N N N N N N N N N
6DEB1D94 64C5DC9C N N N N N N N N N N N N N N N N N N N N N N N N N N N
N N N N
64F7E0E8 64C5DC9C N N N N N N N N N N N N N N N N N N N N N N N N N N N
N N N N
6 rows selected.
You're probably thinking "why do you care" since they all show zero
executions and disk reads, but that is another part of my problem - I'm
having a difficult time tracking the impact of this query because it is
constantly jumping back and forth between different child cursors,
recording stats in v$sql for only one at a time and zeroing out the
others, for example if I query v$sql at 10:00 it will show 1000
executions in child number 1 and zero for the others - then when I look
at 11:00 it will show 400 executions in child number 2 and zero for the
others. This makes it impossible for me to get accurate stats out of
statspack because it doesn't consider the child number and just sees
1000 followed by 400 and calculates -600 executions for the interval.
Anyone know what's going on here and any suggestions for how to cope
with this? I'm not sure if it is just normal aging out of the shared
pool, or if there is something else going on.
Thanks for any ideas!
Brandon
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.
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
