As a first step of triage, I would suggest executing
the query with a GATHER_PLAN_STATISTICS hint followed by
a call to DBMS_XPLAN.DISPLAY_CURSOR.
The GATHER_PLAN_STATISTICS hint allows for the
collection of extra metrics during the execution of the
query. Specifically, it shows us the Optimizer's
estimated number of rows (E-Rows) and the actual number
of rows (A-Rows) for each row source. If the estimates
are vastly different from the actual, one probably needs
to investigate why. For example: In the below plan, look
at line 8.
The Optimizer estimates 5,899 rows and the row source
actually returns 5,479,000 rows. If the estimate is off
by three orders of magnitude (1000), chances are the
plan will be sub-optimal. Do note that with Nested Loop
Joins you need to multiply the Starts column by the
E-Rows column to get the A-Rows values (see line 10).
Note: The dbms_xplan utility can be
used with the gather_plan_statistics hint to display the
estimated and actual rows for a SQL statement:
select /*+
gather_plan_statistics */
cust_name
from
mytab
where
stuff='tRUE?;
select *
from
table
(dbms_xplan.display_cursor (format=>'ALLSTATS LAST'));
------------------------------------------------------------------------------------------
| Id | Operation
| Name |
Starts | E-Rows | A-Rows |
------------------------------------------------------------------------------------------
| 1 | SORT GROUP BY
|
| 1 |
1 | 1 |
|* 2 | FILTER
|
| 1 |
| 1728K |
| 3 | NESTED LOOPS
|
| 1 |
1 | 1728K |
|* 4 | HASH JOIN
|
| 1 |
1 | 1728K |
| 5 | PARTITION LIST
SINGLE
|
| 1 | 6844 | 3029
|
|* 6 | INDEX RANGE SCAN
| PROV_IX13 | 1
| 6844 | 3029 |
| 7 | PARTITION LIST
SINGLE
|
| 1 | 5899 | 5479K
|
|* 8 | TABLE ACCESS BY
LOCAL INDEX ROWID | SERVICE |
1 | 5899 | 5479K |
|* 9 | INDEX SKIP
SCAN
| SERVICE_IX8 | 1 |
4934 | 5479K |
| 10 | PARTITION LIST SINGLE
|
| 1728K | 1 | 1728K
|
|* 11 | INDEX RANGE SCAN
| CLAIM_IX7 | 1728K |
1 | 1728K |
------------------------------------------------------------------------------------------
and include the URL for the page.
