check for existence of an object by v$mystat?
- From: Martin Berger <martin.a.berger@xxxxxxxxx>
- To: oracle-l <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 14 Sep 2012 22:12:51 +0200
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.
@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?
--
//www.freelists.org/webpage/oracle-l
Other related posts:
