check for existence of an object by v$mystat?

From: Martin Berger <martin.a.berger_at_gmail.com>
Date: Fri, 14 Sep 2012 22:12:51 +0200
Message-Id: <C53561F3-9E8E-48CA-B13F-8FC9C27F64FA_at_gmail.com>

Hi List,
I tried to check if I can 'guess' the existence of an object by v$mystat.

Here is my test case - comments and questions are inline as comments (I removed the SQL> prompt so a cut&paste should be easier) My comments and 'guesses' are also backed up somehow by a 10046 trace. But there are some discrepancies I can not explain right now.

   _at_exec_tests
   col name for A20    

   set sqlp ' '
   set echo on
--

• just the setup
  --
  

   select ms.sid, ms.statistic#, sn.name, ms.value from v$mystat ms, V$STATNAME sn where ms.statistic# in(9,588) and ms.statistic# = sn.statistic# order by 2;

       SID STATISTIC# NAME VALUE
---------- ---------- -------------------- ----------

	64	    9 recursive calls		 2656
	64	  588 execute count		  660

--

• the first baseline - we will need these values

   select * from btedrewclt -- object does not exist, no alias exists   2 /
select * from btedrewclt -- object does not exist, no alias exists

              *
ERROR at line 1:
ORA-00942: table or view does not exist    

   select ms.sid, ms.statistic#, sn.name, ms.value from v$mystat ms, V$STATNAME sn where ms.statistic# in(9,588) and ms.statistic# = sn.statistic# order by 2;

       SID STATISTIC# NAME VALUE
---------- ---------- -------------------- ----------

	64	    9 recursive calls		 2659
	64	  588 execute count		  663

--

• here we have 3 "recursive calls" and 3 "execute counts"
  --
  

   select * from btedrewclu -- object does not exist, no alias exists   2 /
select * from btedrewclu -- object does not exist, no alias exists

              *
ERROR at line 1:
ORA-00942: table or view does not exist    

   select ms.sid, ms.statistic#, sn.name, ms.value from v$mystat ms, V$STATNAME sn where ms.statistic# in(9,588) and ms.statistic# = sn.statistic# order by 2;

       SID STATISTIC# NAME VALUE
---------- ---------- -------------------- ----------

	64	    9 recursive calls		 2662
	64	  588 execute count		  666

--

• here we have 3 "recursive calls" and 3 "execute counts"
• again 3 and 3 - seems somehow stable
  --
  

   select * from a.x -- usr a exists, table a.x does not exist   2 /
select * from a.x -- usr a exists, table a.x does not exist

                *

ERROR at line 1:
ORA-00942: table or view does not exist    

   select ms.sid, ms.statistic#, sn.name, ms.value from v$mystat ms, V$STATNAME sn where ms.statistic# in(9,588) and ms.statistic# = sn.statistic# order by 2;

       SID STATISTIC# NAME VALUE
---------- ---------- -------------------- ----------

	64	    9 recursive calls		 2683
	64	  588 execute count		  669
 

--

• here we have 21 "recursive calls" and 3 "execute counts"
• the 21 recursive calls seems to check if user "a" exists and if object "x" exists
  --
  

   select * from a.y -- usr a exists, table a.x does not exist   2 /
select * from a.y -- usr a exists, table a.x does not exist

                *

ERROR at line 1:
ORA-00942: table or view does not exist    

   select ms.sid, ms.statistic#, sn.name, ms.value from v$mystat ms, V$STATNAME sn where ms.statistic# in(9,588) and ms.statistic# = sn.statistic# order by 2;

       SID STATISTIC# NAME VALUE
---------- ---------- -------------------- ----------

	64	    9 recursive calls		 2685
	64	  588 execute count		  671

--

• here we have 2 "recursive calls" and 2 "execute counts"
• I'd say there are only 2 "recursive calls" as informations about "a" is already in row cache
  --
  

   select * from a.m -- a.m exists!
  2 /
select * from a.m -- a.m exists!

                *

ERROR at line 1:
ORA-00942: table or view does not exist    

   select ms.sid, ms.statistic#, sn.name, ms.value from v$mystat ms, V$STATNAME sn where ms.statistic# in(9,588) and ms.statistic# = sn.statistic# order by 2;

       SID STATISTIC# NAME VALUE
---------- ---------- -------------------- ----------

	64	    9 recursive calls		 2696
	64	  588 execute count		  678

--

• here we have 11 "recursive calls" and 7 "execute counts"
• I'd say there are 11 "recursive calls" as informations about "a" is already in row cache,
• so afterwards existence of "a.m" was checked, and at the end permissions must be checked!
  --
  

   select * from all_objects where 1=2;

no rows selected    

   select ms.sid, ms.statistic#, sn.name, ms.value from v$mystat ms, V$STATNAME sn where ms.statistic# in(9,588) and ms.statistic# = sn.statistic# order by 2;

       SID STATISTIC# NAME VALUE
---------- ---------- -------------------- ----------

	64	    9 recursive calls		 3551
	64	  588 execute count		  833

--

• here we have 855 "recursive calls" and 155 "execute counts"
• I did not check the details here, just an example there can be 'many' recursive calls and execute counts for only 1 (or 2) statements.
  --
  

For all those who followed the email until here, now my question:
Is it somehow reasonable to tell the existence of an object by the higher "recursive calls" for a ORA-00942 on an existing object (in comparison to a non-existing object) ? As an add-on, can someone please tell me the different jumps in "execute count" between all the tests? the docu says: http://docs.oracle.com/cd/E11882_01/server.112/e25513/stats002.htm execute count
64
Total number of calls (user and recursive) that executed SQL statements which does not match the observation, I'd say?

--

http://www.freelists.org/webpage/oracle-l Received on Fri Sep 14 2012 - 15:12:51 CDT