SQLT XTRACT is the easiest report to create because it does not require the execution of the SQL at the time of the report generation. The report can be collected after the statement has been executed.
SQLTXECUTE, on the other hand, executes the SQL statement and thus has better runtime information and access to the actual rows returned. It will run only when statistics_level=all or “_rowsource_execution_ statistics=true”. This will get the accuracy of the estimated cardinality at run time.
The disadvantage is that it is not always possible to use the SQLTEXECUTE because we cannot run in production environment. Perhaps SQL statement is running long time which is why we are diagnosing with this utility.
So, always we need to start with SQLT XTRACT.
Below is the steps to run the SQLT XTRACT:
SQL> select count(*) from dba_extents;
SQL> select sql_id from v$sqlarea where sql_text like ‘select count(*) from dba_extents%’;
SQL> @sqltxtract <SQL ID>
Enter the password for SQLTXPLAIN (which you entered during the installation) and wait for “SQLTXTRACT completed” message.
Now, pull the zip file into your local Desktop and now you have the report ready to diagnose.
Happy Performance Tuning!!!
