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:

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 ?

Back to index of questions

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

Back to top

Back to index of questions