Re: v$sql - executions vs loads vs invalidations
Date: Wed, 02 May 2012 17:31:11 +1000
Message-ID: <4FA0E2BF.7090304_at_millerandbowman.com>
Hi Jonathan
I think I would go along with this explanation, even though the tables in question are not temporary tables. I didn't really think that the query wasn't executing but wasn't sure if executions would be reset without resetting loads, invalidations etc.
As I mentioned in my reply to Carlos, there are over 6000 such queries in theshared pool, each with the structure: SELECT <schema_name>.<table_name>.*, rowid from <schema>.<table_name> WHERE1=0; I think I should quiz the developers as to what they are trying to do with these queries.
Thanks
Gerry
Jonathan Lewis wrote: ----- Original Message ----- From: "GG" <grzegorzof_at_interia.pl>[1] To: <gerry_at_millerandbowman.com>[2] Cc: "Oracle-L Group" <oracle-l_at_freelists.org>[3] Sent: Tuesday, May 01, 2012 11:53 AM Subject: Re: v$sql - executions vs loads vs invalidations W dniu 2012-05-01 11:41, Gerry Miller pisze: LOADS: 2794 INVALIDATIONS: 2793 EXECUTIONS: 0 You've never mentioned DB version :) but basically looks like statements are only parsed and never executed (application issue ?). You got invalidation because some dependent objects are 'manipulated' via (grants, ddls, stats gathering and so) , loads are because of flushes which could be caused by shared pool free space pressure . For better understanding we need some info about sga size and allocations and ora-4031 related dumps . Meantime You can check =============== Depending on version, invalidation may zero out the execution stats. Earlier 10g versions, for example, would invalidate ALL cursors relating to a global temporary table if ANY user truncated their private copy of that GTT - resulting in lots of library cache invalidation for everyone all the time - and lots of cursors with high invalidation and reload counts, but zero execution stats Regards Jonathan Lewis
http://jonathanlewis.wordpress.com[4] Oracle Core (Apress 2011) http://www.apress.com/9781430239543[5] -- http://www.freelists.org/webpage/oracle-l[6]
- Links --- 1 mailto:grzegorzof_at_interia.pl 2 mailto:gerry_at_millerandbowman.com 3 mailto:oracle-l_at_freelists.org 4 http://jonathanlewis.wordpress.com 5 http://www.apress.com/9781430239543 6 http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-l