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

--

http://www.freelists.org/webpage/oracle-l Received on Wed Dec 07 2005 - 17:44:38 CST