James's Blog

Oracle: Runtime Row Source Statistics

9iR2 introduces dynamic performance table: v$sql_plan_statistics.
it provides for each cached cursor the execution statistics of
each operation in the execution plan.
to use this view, parameter STATISTICS_LEVEL must be set to ALL.

additional information, see

v$sql_plan
v$sql_plan_statistics_all
v$sql_workarea ( contains memory usage statistics for row sources that use sql memory, for instance, hash-join and sort)


• 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 scans.

• 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.
Or turn on sql_trace will also populates these views.

posted by James Pei @ 3:43 PM