My Oracle Support Banner

How to Force Tracing Rowsource for Every Execution (Doc ID 873206.1)

Last updated on AUGUST 18, 2020

Applies to:

Oracle Database - Enterprise Edition - Version 11.1.0.6 to 11.2.0.2 [Release 11.1 to 11.2]
Information in this document applies to any platform.
Information in this document applies to any platform.


Goal

Tuning SQL statements in general needs 10046 to know the exact execution plan and rowsource that
current execution is using. In many cases, if the cursor already parsed the rowsources won't be included in
the trace, which might force  to invalidate the cursor or flush shared_pool to force reparsing and
get the rowsource in the next run. The problem is more complicated if the SQL is coming from application.
 
11g introduced a new parameter in DBMS_MONITOR.SESSION_TRACE_ENABLE to force dumping
rowsource each time the cursor (SQL) is executed.

plan_stat : Frequency at which we dump row source statistics.

Values

'NEVER' never dump rowsource ,
'FIRST_EXECUTION' (equivalent to NULL) dump rowsource once at the first execution
'ALL_EXECUTIONS': dump rowsource each time SQL is executed.


The new parameter is also introduced in other DBMS_MONITOR procedures

CLIENT_ID_TRACE_DISABLE
DATABASE_TRACE_ENABLE

Get same behavior with statistics_level=ALL. Setting statistics_level=all at session level
can be affordable, and it is not recommended on system level.

 

Solution

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Goal
Solution
References

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.