Thanks for the question, Ramasubbu.

Asked: May 30, 2002 - 9:42 pm UTC

Last updated: August 13, 2006 - 9:23 am UTC

Version: Oracle8i Enterprise Edition Release 8.1.6.3.0

Viewed 10K+ times! This question is

You Asked

Hi Tom,
One of the cursor is often executing inside of stored procedure since it is in the loop.I could see the sql text and rows processed from following query.

select sql_text,rows_processed from v$sql
where USERS_EXECUTING>0

Actually what is happening that rows_processed always accumlating the values from previous session executed value.

For ex,
I am executing the procedure generate_proc;

devusr@oracle.WORLD> exec generate_proc;

devusr@oracle.WORLD> select sql_text,rows_processed from v$sql
where USERS_EXECUTING>0;

sql_text:
SELECT custic,balance,mnemonic FROM BAL_table
WHERE FILE_DATE = :b1 AND PROD BETWEEN 5
01 AND 600 AND
((LAST_MNT IS NULL ) OR (LAST_MNT BETWEEN :b2 AND :b1 )
OR (NVL(EOMBAL,0) != 0 ))ORDER BY 2,1
Rows_processed: 1000

At end of generate_proc procedure it stored the rows_processed 1000 for the above sql.

Logout the session.
devusr@oracle.WORLD> exit;

Aagain am login and executing the procedure

devusr@oracle.WORLD> exec generate_proc;

devusr@oracle.WORLD> select sql_text,rows_processed from v$sql
where USERS_EXECUTING>0;

sql_text:
SELECT custic,balance,mnemonic FROM BAL_table
WHERE FILE_DATE = :b1 AND PROD BETWEEN 5
01 AND 600 AND
((LAST_MNT IS NULL ) OR (LAST_MNT BETWEEN :b2 AND :b1 )
OR (NVL(EOMBAL,0) != 0 ))ORDER BY 2,1
Rows_processed: 1200

Here basically my expectation is rows_processed must be bewlow 1000. ie,200. But it is accumulated with the previous session value.

devusr@oracle.WORLD> select sql_text,rows_processed from v$sql
where USERS_EXECUTING>0;

sql_text:
SELECT custic,balance,mnemonic FROM BAL_table
WHERE FILE_DATE = :b1 AND PROD BETWEEN 5
01 AND 600 AND
((LAST_MNT IS NULL ) OR (LAST_MNT BETWEEN :b2 AND :b1 )
OR (NVL(EOMBAL,0) != 0 ))ORDER BY 2,1
Rows_processed: 1900

devusr@oracle.WORLD> select sql_text,rows_processed from v$sql
where USERS_EXECUTING>0;

sql_text:
SELECT custic,balance,mnemonic FROM BAL_table
WHERE FILE_DATE = :b1 AND PROD BETWEEN 5
01 AND 600 AND
((LAST_MNT IS NULL ) OR (LAST_MNT BETWEEN :b2 AND :b1 )
OR (NVL(EOMBAL,0) != 0 ))ORDER BY 2,1
Rows_processed: 2000

Now procedure execution completed.

Now my question is
How to find out the number of rows processed for an active SQL of active session?.

Thanx
muthu

and Tom said...

v$sql is a view into the SHARED pool (not "your private pool"). The information in there is by its very definition "shared".

You cannot get what you are after -- especially in a multi-user system. We just don't record the rowcounts for your individual queries. The rows processed you are seeing are an aggregate count not only across your many sessions but across ALL sessions (all other user sessions). If two people run your procedure at the same time, they'll be adding counts to this v$sql entry as well.

If you are in a single user system -- just run the query before your procedure and again after -- subtract and you'll have the new "total". In a multi-user system where others might be running that same query, this'll not work (perhaps the stored procedure should be written with some dbms_output.put_lines to print out relevant information?) Additionally, you could enable sql_trace and use tkprof </code> http://asktom.oracle.com/~tkyte/tkprof.html <code>to review the rows processed by all queries in the procedure for your session.

Rating

(4 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

DBMS_APPLICATION_INFO

Suresh, May 31, 2002 - 9:24 am UTC

Tom,
Can't this be done using DBMS_APPLICATION_INFO package as per in your book Page 1042? Thanks.

May 31, 2002 - 9:38 am UTC

If you rewrite the code, adding this information for this query (like dbms_output) yes.

If you have just 1 bit of data you want to convey in client_info, sure. For general purpose reporting -- dbms_output, sql_trace will be the only way (and sql_trace involves no extra work on your part, no changes)

V$SQL

Andre, May 31, 2002 - 11:15 am UTC

Would you briefly dscribe in your words these columns in V$SQL ?

- USERS_OPENING
- EXECUTIONS
- USER_EXECUTING
- PARSING_USER_ID
- PARSING_SCHEMA_ID

Are the latter two related to invoker's rights X definer's rights ?

May 31, 2002 - 12:45 pm UTC

users opening -- how many cursors are opened using it right now.

executions -- <quote>The number of executions that took place on this object since it was brought into the library cache</quote> (the docs did a fine job on that one)

users_executing <quote>The number of users executing the statement</quote>  Many people might have open cursors to it -- but how many of those cursors are executing RIGHT NOW, at this instant.

parsing user id -- what schema's authority was used to security check this query.  Who parsed it (eg: if a definers rights procedure owned by SCOTT runs "select * from emp" and the user GEORGE runs it, the parsing user id is SCOTT, it was parsed using SCOTTS authority

parsing schema id -- what schema was used by default when parsing the query (eg: what schema name would be stuck in to fully qualify object references that didn't have a schema associated with them)

For the last two, an example:

ops$tkyte@ORA817DEV.US.ORACLE.COM> alter system flush shared_pool;

System altered.

ops$tkyte@ORA817DEV.US.ORACLE.COM> 
ops$tkyte@ORA817DEV.US.ORACLE.COM> alter session set current_schema=ops$tkyte;

Session altered.

ops$tkyte@ORA817DEV.US.ORACLE.COM> select * from emp where 1=0;

no rows selected

ops$tkyte@ORA817DEV.US.ORACLE.COM> alter session set current_schema=scott;

Session altered.

ops$tkyte@ORA817DEV.US.ORACLE.COM> select * from emp where 1=0;

no rows selected

ops$tkyte@ORA817DEV.US.ORACLE.COM> 
ops$tkyte@ORA817DEV.US.ORACLE.COM> select parsing_user_id, parsing_schema_id, sql_text from v$sql
  2  where sql_text like 'select * from emp%';

PARSING_USER_ID PARSING_SCHEMA_ID
--------------- -----------------
SQL_TEXT
-----------------------------------------------------------------------------------------------------------------------------------
            216               216
select * from emp where 1=0

            216               212
select * from emp where 1=0


ops$tkyte@ORA817DEV.US.ORACLE.COM> 
ops$tkyte@ORA817DEV.US.ORACLE.COM> 
ops$tkyte@ORA817DEV.US.ORACLE.COM> select user_id, username from dba_users where username in ( user, 'SCOTT' );

   USER_ID USERNAME
---------- ------------------------------
       212 SCOTT
       216 OPS$TKYTE


Same query -- both were parsed by me (uid=216).  One was really executed as:


select * from OPS$TKYTE.emp where 1=0;  (parsing schema id = 216)

the other as:

select * from SCOTT.mp where 1=0;  (parsing schema id = 212)

Excellent !!!

Andre, May 31, 2002 - 2:09 pm UTC

Thanks again, Tom ! Great job !

value of users_executing

Susan, August 13, 2006 - 2:37 am UTC

Can u tell what is the meaning of users_executing column having value 0 in v$sqlare view. Do they represent the recursive calls mad by the database?
Thanks in advance.

August 13, 2006 - 9:23 am UTC

"U"? I don't know what they may or may not be able to tell you.

but, if you were to ask "me", I'd say "that means zero users are currently executing the sql statement, it is just sitting there waiting to be executed by someone"

(I'd suggest not using v$sqlarea, use v$sql - v$sqlarea is a "less performant" aggregation of v$sql)

</code> http://docs.oracle.com/docs/cd/B19306_01/server.102/b14237/dynviews_2113.htm#sthref4036 <code>

Breadcrumb

Question and Answer