how to get parse and execution number for a sql
- From: "Guang Mei" <gmei@xxxxxxxxxx>
- To: "Oracle-L-freelists" <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 25 Feb 2004 12:30:29 -0500
Hi:
According to metalink, the definition of these columns in v$sql are:
EXECUTIONS
NUMBER
The total number of executions, totalled over all the children
USERS_EXECUTING
NUMBER
The total number of users executing the statement over all children
PARSE_CALLS
NUMBER
The sum of all parse calls to all the child cursors under this parent
Now I have a table called WEEKLY in my 8173 schema, so I did:
MT@rex-SQL> select PARSE_CALLS ,EXECUTIONS ,USERS_EXECUTING, sql_text from
v$sql
where sql_text like 'select COUNT(*) from WEEKLY%';
PARSE_CALLS EXECUTIONS USERS_EXECUTING
----------- ---------- ---------------
SQL_TEXT
----------------------------------------------------------------------------
----
1 1 0
select COUNT(*) from WEEKLY
-- I thought I should get 0,0,0 becuase I have not executed 'select
COUNT(*) from WEEKLY' yet.
MT@rex-SQL> /
PARSE_CALLS EXECUTIONS USERS_EXECUTING
----------- ---------- ---------------
SQL_TEXT
----------------------------------------------------------------------------
----
1 1 0
select COUNT(*) from WEEKLY
MT@rex-SQL> select COUNT(*) from WEEKLY;
COUNT(*)
----------
926
MT@rex-SQL> select PARSE_CALLS ,EXECUTIONS ,USERS_EXECUTING, sql_text from
v$sql
where sql_text like 'select COUNT(*) from WEEKLY%';
PARSE_CALLS EXECUTIONS USERS_EXECUTING
----------- ---------- ---------------
SQL_TEXT
----------------------------------------------------------------------------
----
2 2 0
select COUNT(*) from WEEKLY
-- Why it is not 2,2,1 (assuming 1,1,0 was correct) ?
MT@rex-SQL> select COUNT(*) from WEEKLY;
COUNT(*)
----------
926
MT@rex-SQL> select PARSE_CALLS ,EXECUTIONS ,USERS_EXECUTING, sql_text from
v$sql
where sql_text like 'select COUNT(*) from WEEKLY%'; 2
PARSE_CALLS EXECUTIONS USERS_EXECUTING
----------- ---------- ---------------
SQL_TEXT
----------------------------------------------------------------------------
----
3 3 0
select COUNT(*) from WEEKLY
-- Why it is not 2,3,1 (again assuming 2,2,0 was correct) ? Does
PARSE_CALLS include both hard parse and soft parse?
Probably my understanding of v$sql column was not correct. My goal is to
find what the number of "parse" and "execution" BEFORE and AFTER a specific
dynamic sql statement (executed by using native dynamic sql method), so that
I know if the sql statement is hard parsed every time. Is it true that
even if I pass the exact same sql every time when it is called in native
dynamic sql, it is still hard parsed each time?
TIA.
Guang
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
