Donald K. Burleson
Oracle Utilities Tips
SQL Execution Statistics in 9i
The SQL tuning process prior to 9.2 involved executing SQL commands, then
OS commands, and then SQL commands again. This is a very time-consuming and
burdensome process. In 9.2, Oracle decided to retain the SQL metrics for
each statement in the SGA (library cache) while the statement remains
cached. The DBA could then diagnose SQL issues at a SQL prompt and leave
tkprof alone. This is a vast improvement over prior versions.
Oracle9.2 contains the following views that enable the DBA to identify SQL
issues directly from a SQL prompt. These views should be used to
periodically check SQL statistics and full-table scans, alerting the DBA to
problem areas requiring corrective action.
• v$sql_plan - This view shows the same information as shown by Explain
Plan except it is the actual execution plan and not the predicted one –
just like tkprof and even better than Explain Plan.
• v$sql_plan_statistics - This view contains the execution statistics for
each operation (step) in the v$sql_plan. Queries should access this view
and look for poor SQL operations including TABLE ACCESS FULL – full-table
• v$sql_plan_statistics_all - This view combines data from v$sql_plan,
v$sql_plan_statistics and v$sql_workarea.
Both v$sql_plan_statistics and v$sql_plan_statistics_all are not populated
by default. The option statistics_level=all must be set.
To learn more about these techniques, see the book "Advanced
Oracle Utilities: The Definitive Reference".
You can buy it
directly from the
publisher and get instant access to the code depot of utilities scripts.
Download your Oracle scripts now:
definitive Oracle Script collection for every Oracle professional DBA