| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> how to get parse and execution number for a sql
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_at_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
MT_at_rex-SQL> /
PARSE_CALLS EXECUTIONS USERS_EXECUTING
----------- ---------- ---------------
SQL_TEXT
1 1 0
select COUNT(*) from WEEKLY
MT_at_rex-SQL> select COUNT(*) from WEEKLY;
COUNT(*)
926
MT_at_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
MT_at_rex-SQL> select COUNT(*) from WEEKLY;
COUNT(*)
926
MT_at_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
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
 |
 |