Different options to display execution plan and statistics from SQL*Plus?

— Turn off display of execution plan and statistics
SET AUTOTRACE OFF

— show user query output, execution plan
SET AUTOTRACE ON EXPLAIN

— display output from query and statistics
SET AUTOTRACE ON STATISTICS

— show user query output, execution plan and statistics
SET AUTOTRACE ON

— set trace on but suppress output from query, display execution plan and statistics
SET AUTOTRACE TRACEONLY

To be able view the trace using the above option the user needs PLUSTRACE role assigned, the role is created by running the script which is ORACLE_HOME @?/sqlplus/admin/plustrce.sql to be run as SYS. If the PLUSTRACE role is not granted you will get the following error “SP2-0618: Cannot find the Session Identifier. Check PLUSTRACE role is enabled”

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s