The Oracle (tm) Users' Co-Operative FAQ
Why isn't my output from tkprof with the explain option showing row counts ?
| Author's name: Jonathan Lewis Author's Email: Jonathan@jlcomp.demon.co.uk |
Date written: 7th Mayy 2001 Oracle version(s): 7.3 - 9.0.1 |
| There can be two execution plans generated from tkprof when you run it with the explain option and if you see both of them, you usually see that they are the same (although one is a little sparser than the other) and that the first column is a row count. Sometimes this row count does not appear. Why not ? |
The row counts come from trace file lines which are identified as STAT lines. When a cursor is closed, the stat lines for that cursor are dumped to the trace file. consequently, if the trace file ends before the cursor is closed, the stat lines will be missing.
Typically, this occurs because you have issued
alter session set sql_trace true;
..... execution time
alter session set sql_trace false;
(or one of the variants from dbms_system, dbms_support or oradebug). If this is the case, and the SQL in question is inside a pl/sql block, then the pl/sql engine will have cached the cursor, keeping it open even if the program appears to have closed it.
In order to ensure that pl/sql child cursors are closed, you need to exit cleanly from the session that you are tracing.
Further reading: N/A
