Use the gather_plan_statistics hint to view the optimizer’s estimation of rows vs. the actual returned number of rows.
Notice the case – gather_plan_statistics – I had trouble getting it to work when stated in capital letters as GATHER_PLAN_STATISTICS. So stick to lower case!
Use the hint in the SELECT part of your query, for example:
select /*+ gather_plan_statistics */
from mytable t1 join mytable t2….
After the query has run to completion, check the cursor cache for the details of your specific SQL ID:
set linesize 200
set pagesize 0
select * from table
(dbms_xplan.display_cursor( ‘dnyrjuumj8psq’,1,’TYPICAL ALLSTATS LAST’));
exit
Example output:
Plan hash value: 4147659309
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
| 0 | SELECT STATEMENT | | 1 | | | 1862 (100)| | 3012 |00:08:14.63 | 576K| 553K| | | |
| 1 | SORT ORDER BY | | 1 | 2 | 294 | 1862 (1)| 00:00:01 | 3012 |00:08:14.63 | 576K| 553K| 372K| 372K| 330K (0)|
|* 2 | FILTER | | 1 | | | | | 3012 |00:08:45.50 | 576K| 553K| | | |
| 3 | NESTED LOOPS | | 1 | 2 | 294 | 1861 (1)| 00:00:01 | 3012 |00:08:45.50 | 576K| 553K| | | |
| 4 | NESTED LOOPS | | 1 | 2 | 294 | 1861 (1)| 00:00:01 | 3012 |00:08:45.34 | 573K| 553K| | | |
| 5 | NESTED LOOPS | | 1 | 2 | 270 | 1859 (1)| 00:00:01 | 3012 |00:08:45.31 | 573K| 553K| | | |
|* 6 | TABLE ACCESS BY INDEX ROWID BATCHED| T1 | 1 | 1 | 31 | 3 (0)| 00:00:01 | 439 |00:00:00.27 | 89 | 73 | | | |
|* 7 | INDEX RANGE SCAN | T1_IDX4 | 1 | 4 | | 2 (0)| 00:00:01 | 440 |00:00:00.01 | 3 | 1 | | | |
|* 8 | TABLE ACCESS BY INDEX ROWID BATCHED| T2 | 439 | 2 | 208 | 1856 (1)| 00:00:01 | 3012 |00:08:54.15 | 573K| 553K| | | |
|* 9 | INDEX RANGE SCAN | T2_IDX4 | 439 | 23118 | | 69 (0)| 00:00:01 | 12M|00:00:54.73 | 38436 | 37439 | | | |
|* 10 | INDEX UNIQUE SCAN | T3_IDX1 | 3012 | 1 | | 0 (0)| | 3012 |00:00:00.01 | 9 | 2 | | | |
| 11 | TABLE ACCESS BY INDEX ROWID | T3 | 3012 | 1 | 12 | 1 (0)| 00:00:01 | 3012 |00:00:00.01 | 3012 | 2 | | | |
Notice the column “E-Rows”. It deviates substantially from the column A-Rows, which are the actual rows returned.
So the optimizer is clearly not able to make a good estimation about the number of rows.
Let’s add a couple of statements which will give the optimizer better statistics. This particular database is not certified with any of the 12.1 adaptive tuning features, so I will have to feed the optimizer using other methods:
alter session set statistics_level=’ALL’;
alter session set optimizer_dynamic_sampling=11;
After running it a second time, the following plan is generated:
Plan hash value: 1509429641
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
| 0 | SELECT STATEMENT | | 1 | | | 735K(100)| | 3012 |00:09:44.25 | 572K| 551K| | | |
| 1 | SORT ORDER BY | | 1 | 2986 | 428K| 735K (1)| 00:00:29 | 3012 |00:09:44.25 | 572K| 551K| 372K| 372K| 330K (0)|
|* 2 | FILTER | | 1 | | | | | 3012 |00:09:44.25 | 572K| 551K| | | |
|* 3 | HASH JOIN | | 1 | 2986 | 428K| 735K (1)| 00:00:29 | 3012 |00:09:44.25 | 572K| 551K| 1066K| 1066K| 954K (0)|
| 4 | NESTED LOOPS | | 1 | 2986 | 393K| 735K (1)| 00:00:29 | 3012 |00:09:43.01 | 569K| 547K| | | |
| 5 | NESTED LOOPS | | 1 | 10M| 393K| 735K (1)| 00:00:29 | 12M|00:00:38.92 | 38488 | 37114 | | | |
|* 6 | TABLE ACCESS BY INDEX ROWID BATCHED| T1 | 1 | 439 | 13609 | 72 (0)| 00:00:01 | 439 |00:00:00.01 | 76 | 0 | | | |
|* 7 | INDEX RANGE SCAN | T1_IDX4| 1 | 440 | | 2 (0)| 00:00:01 | 440 |00:00:00.01 | 3 | 0 | | | |
|* 8 | INDEX RANGE SCAN | T2_IDX4| 439 | 23118 | | 69 (0)| 00:00:01 | 12M|00:00:28.47 | 38412 | 37114 | | | |
|* 9 | TABLE ACCESS BY INDEX ROWID | T2 | 12M| 7 | 728 | 1856 (1)| 00:00:01 | 3012 |00:08:47.65 | 531K| 510K| | | |
| 10 | TABLE ACCESS FULL | T3 | 1 | 74866 | 877K| 572 (1)| 00:00:01 | 74866 |00:00:01.22 | 3022 | 3017 | | | |
Consider the same column – the E-Rows and the A-Rows, and how much more accurate the optimizer is estimating the rows to be returned.
Notice also that the former of these two plans generates an index lookup on the T3 table, while the ladder generates a full table scan. The FTS proved to be the most efficient, since the runtime dropped from 15 to 9 minutes.
