Hint: gather plan statistics

Posted by Alen Oblak in , on Wednesday, September 16, 2009

My favourite method when analyzing poor performing SQL is the hint gather_plan_statistics. You can get so much more of valuable information when diagnosing explain plans. Here's how the story usually goes.

You are faced with a poor performing SQL statement. First thing you do - check the explain plan and it looks OK. Indexes are being used, estimated rows are low, so why the heck is the statement producing a lot of LIO?


---------------------------------------------------------
| Id  | Operation                    | Name    | E-Rows |
---------------------------------------------------------
|   1 |  NESTED LOOPS                |         |      1 |
|   2 |   TABLE ACCESS BY INDEX ROWID| TABLE_1 |      1 |
|*  3 |    INDEX RANGE SCAN          | INDEX_1 |      1 |
|*  4 |   TABLE ACCESS BY INDEX ROWID| TABLE_2 |      1 |
|*  5 |    INDEX UNIQUE SCAN         | INDEX_2 |      1 |
---------------------------------------------------------

You re-run the statement with the hint gather_plan_statistics and explain plan again, using dbms_xplan.display_cursor and setting the third parameter to RUNSTATS_LAST, which shows you the last run time statistics:

select *
from   table( dbms_xplan.display_cursor ( '8g51ffxg663m3' , null , 'RUNSTATS_LAST' ) ) ;

You get additional columns:

  • Starts: how many times was the step performed
  • A-Rows: actual rows proccessed
  • A-Time: actual elapsed time spent
  • Buffers: number of logical reads


--------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------
|   1 |  NESTED LOOPS                |         |      1 |      1 |    501 |00:00:00.09 |   27921 |
|   2 |   TABLE ACCESS BY INDEX ROWID| TABLE_1 |      1 |      1 |   9456 |00:00:00.03 |     255 |
|*  3 |    INDEX RANGE SCAN          | INDEX_1 |      1 |      1 |   9456 |00:00:00.01 |      57 |
|*  4 |   TABLE ACCESS BY INDEX ROWID| TABLE_2 |   9456 |      1 |    501 |00:00:00.10 |   27666 |
|*  5 |    INDEX UNIQUE SCAN         | INDEX_2 |   9456 |      1 |   9222 |00:00:00.06 |   18444 |
--------------------------------------------------------------------------------------------------

Now it's obvious that the estimated rows are way off and you can start working on that.

No Comments