tag:blogger.com,1999:blog-37174762646153861092024-02-19T02:51:03.133+01:00Alen OblakAlen Oblakhttp://www.blogger.com/profile/07343361836188211697noreply@blogger.comBlogger1125tag:blogger.com,1999:blog-3717476264615386109.post-69433547336330796142009-09-16T13:35:00.009+02:002009-10-24T19:33:19.483+02:00Hint: gather plan statisticsMy 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.<br /><br />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?<br /><pre class="brush:sql"><br />---------------------------------------------------------<br />| Id | Operation | Name | E-Rows |<br />---------------------------------------------------------<br />| 1 | NESTED LOOPS | | 1 |<br />| 2 | TABLE ACCESS BY INDEX ROWID| TABLE_1 | 1 |<br />|* 3 | INDEX RANGE SCAN | INDEX_1 | 1 |<br />|* 4 | TABLE ACCESS BY INDEX ROWID| TABLE_2 | 1 |<br />|* 5 | INDEX UNIQUE SCAN | INDEX_2 | 1 |<br />---------------------------------------------------------<br /></pre><br />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:<br /><pre class="brush:sql"><br />select *<br />from table( dbms_xplan.display_cursor ( '8g51ffxg663m3' , null , 'RUNSTATS_LAST' ) ) ;<br /></pre><br />You get additional columns:<br /><ul><br /><li>Starts: how many times was the step performed<br /><li>A-Rows: actual rows proccessed<br /><li>A-Time: actual elapsed time spent<br /><li>Buffers: number of logical reads<br /></ul><br /><pre class="brush:sql"><br />--------------------------------------------------------------------------------------------------<br />| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |<br />--------------------------------------------------------------------------------------------------<br />| 1 | NESTED LOOPS | | 1 | 1 | 501 |00:00:00.09 | 27921 |<br />| 2 | TABLE ACCESS BY INDEX ROWID| TABLE_1 | 1 | 1 | 9456 |00:00:00.03 | 255 |<br />|* 3 | INDEX RANGE SCAN | INDEX_1 | 1 | 1 | 9456 |00:00:00.01 | 57 |<br />|* 4 | TABLE ACCESS BY INDEX ROWID| TABLE_2 | 9456 | 1 | 501 |00:00:00.10 | 27666 |<br />|* 5 | INDEX UNIQUE SCAN | INDEX_2 | 9456 | 1 | 9222 |00:00:00.06 | 18444 |<br />--------------------------------------------------------------------------------------------------<br /></pre><br />Now it's obvious that the estimated rows are way off and you can start working on that.Alen Oblakhttp://www.blogger.com/profile/07343361836188211697noreply@blogger.com0