tag:blogger.com,1999:blog-5124641802818980374.post699240282880697117..comments2023-09-06T11:58:30.752+02:00Comments on Oracle related stuff: Common tuning issues - avoiding the "fetch first rows quickly" trapUnknownnoreply@blogger.comBlogger5125tag:blogger.com,1999:blog-5124641802818980374.post-60849106895395616862009-03-04T16:22:00.000+01:002009-03-04T16:22:00.000+01:00> Only after changing no_invalidatefrom true to...> Only after changing no_invalidate<BR/>from true to false , the plan changed. why ?<BR/><BR/>That doesn't explain your previous issue described, since you were running EXPLAIN PLANs which should perform a hard parse every time.<BR/><BR/>But if you have a statement cached in the shared pool the default behaviour of 10g is to invalidate the statements affected by the updated statistics within a certain period, which is by default 18,000 secs (5 hours), controlled by the internal parameter "_optimizer_invalidation_period".<BR/><BR/>This can lead to the situation that you've updated the statistics but affected statements don't get reparsed immediately, but in a deferred fashion.<BR/><BR/>The rationale behind this default behaviour is to prevent high system load due to potentially excessive hard parsing.<BR/><BR/>If you want to have all affected statement invalidated immediately, you need the option you've described.<BR/><BR/>But again, this is a different issue: This is about the actual execution of the statement.<BR/><BR/>Your previous post was referring to EXPLAIN PLAN outputs.<BR/><BR/>So I'm not sure what you're referring to now.<BR/><BR/>Regards,<BR/>RandolfRandolfhttps://www.blogger.com/profile/13463198440639982695noreply@blogger.comtag:blogger.com,1999:blog-5124641802818980374.post-64218258537930231602009-03-04T16:07:00.000+01:002009-03-04T16:07:00.000+01:00Hi ,exec dbms_stats.gather_table_stats(null, '...Hi ,<BR/><BR/>exec dbms_stats.gather_table_stats(null, 't_fetch_first_rows', cascade => true, no_invalidate=>false);<BR/><BR/>Only after changing no_invalidate<BR/>from true to false , the plan changed. why ?<BR/><BR/>Thanks AgainAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-5124641802818980374.post-2802185519844937592009-03-04T15:39:00.000+01:002009-03-04T15:39:00.000+01:00Jonathan Lewis has a nice wrap-up summarizing the ...Jonathan Lewis has a nice wrap-up summarizing the general thoughts about the different modes:<BR/><BR/><A HREF="http://jonathanlewis.wordpress.com/2008/11/11/first_rows_n/" REL="nofollow">first_rows_n</A>:http://jonathanlewis.wordpress.com/2008/11/11/first_rows_n/Randolfhttps://www.blogger.com/profile/13463198440639982695noreply@blogger.comtag:blogger.com,1999:blog-5124641802818980374.post-59772384109050373092009-03-04T15:34:00.000+01:002009-03-04T15:34:00.000+01:00Are you sure that your application works best with...Are you sure that your application works best with FIRST_ROWS_100?<BR/><BR/>If yes, do you understand why?<BR/><BR/>In your example you tell the optimizer that you're going to fetch 100 rows at most, so therefore it's legitimate/expected that the optimizer uses the index, no matter how many rows are in the table/satisfying the filter predicate, because using the index will be the cheapest way to fetch the first 100 records.<BR/><BR/>If you fetch all rows but tell the optimizer that you're only fetching the first 100, you can expect that things are not going to perform well.<BR/><BR/>You need to understand what the FIRST_ROWS_n mode implies.<BR/><BR/>If your application doesn't behave like only fetching the first rows from a result set, the FIRST_ROWS_n mode is very likely not going to be best for your application.<BR/><BR/>If you have different performance issues using the ALL_ROWS mode you need to find out what the issue is about in detail, but in general if your application uses to fetch all rows from a result set, ALL_ROWS is the mode to use.<BR/><BR/>If you have further questions I suggest you post a request on the worldwide OTN forum so that other people can comment, too.<BR/><BR/>Regards,<BR/>RandolfRandolfhttps://www.blogger.com/profile/13463198440639982695noreply@blogger.comtag:blogger.com,1999:blog-5124641802818980374.post-23724744953703334552009-03-04T12:28:00.000+01:002009-03-04T12:28:00.000+01:00Hi Randolf,My oltp application is working the best...Hi Randolf,<BR/><BR/>My oltp application is working the best with optimizer mode : FIRST_ROWS_100 .<BR/><BR/>I followed your example (only changed the name of the table and the index ) but found that the CBO is still choosing INDEX as access path and not TABLE ACCESS FULL .<BR/>Bellow all the steps i took.<BR/>How come that only when i used ALL_ROWS hint it choose TABLE ACCESS FULL :<BR/><BR/>=========<BR/>STEP 1:<BR/>=========<BR/>I create table that contain 1.5 million records , as follow :<BR/><BR/>insert /*+ append */ into YOAV_BULK_LOAD (id, name, type, measure)<BR/> select object_id, object_name, object_type, object_id as measure<BR/> from all_objects, (select level as id from dual connect by level <= 1000) dup<BR/> where object_type in ('VIEW', 'SCHEDULE')<BR/> and rownum <= 1500000;<BR/><BR/>1500000 rows created.<BR/><BR/>SQL> commit;<BR/><BR/>I created index on this table :<BR/>=========<BR/>STEP 2:<BR/>=========<BR/>create index yoav_idx on yoav_bulk_load (type, id);<BR/><BR/>select type, count(*) <BR/>from YOAV_BULK_LOAD <BR/>group by type<BR/>order by 2 desc;<BR/><BR/>TYPE COUNT(*)<BR/>------------------- ------------<BR/>VIEW 1,499,497<BR/>SCHEDULE 503<BR/><BR/><BR/>=========<BR/>STEP 3:<BR/>=========<BR/><BR/>-- Drop statistics from the table:<BR/>SQL> exec dbms_stats.delete_table_stats(ownname =>'BEZEQ', tabname => 'yoav_bulk_load');<BR/><BR/>PL/SQL procedure successfully completed.<BR/><BR/>=========<BR/>STEP 4:<BR/>=========<BR/><BR/>-- Gather table stats using default method_opt<BR/>SQL> exec dbms_stats.gather_table_stats(ownname =>'BEZEQ', tabname => 'yoav_bulk_load' , method_opt => 'FOR ALL COLUMNS SIZE AUTO');<BR/><BR/>PL/SQL procedure successfully completed.<BR/><BR/><BR/>-- Check statistics on the table<BR/>SQL> select table_name,num_rows,last_analyzed<BR/> 2 from user_tab_statistics<BR/> 3 where table_name = 'YOAV_BULK_LOAD';<BR/><BR/>TABLE_NAME NUM_ROWS LAST_ANAL<BR/>------------------------------ ---------- ---------<BR/>YOAV_BULK_LOAD 1483038 04-MAR-09<BR/><BR/>1 row selected.<BR/><BR/>-- Check index statistics<BR/>SQL> select blevel, leaf_blocks, distinct_keys, num_rows, clustering_factor <BR/> 2 from user_ind_statistics <BR/> 3 where index_name = 'YOAV_IDX';<BR/><BR/> BLEVEL LEAF_BLOCKS DISTINCT_KEYS NUM_ROWS CLUSTERING_FACTOR<BR/>---------- ----------- ------------- ---------- -----------------<BR/> 2 8485 3802 1528259 975581<BR/><BR/>1 row selected.<BR/><BR/>=========<BR/>STEP 5:<BR/>=========<BR/><BR/>===> Oracle is choosing INDEX RANGE SCAN INSTED OF FTS<BR/><BR/>SQL> explain plan for<BR/> 2 select * from YOAV_BULK_LOAD <BR/> 3 where type = 'VIEW'<BR/> 4 order by id;<BR/><BR/>Explained.<BR/><BR/>SQL> select * from table(dbms_xplan.display);<BR/><BR/>PLAN_TABLE_OUTPUT<BR/><BR/>-------------------------------------------------------------------------------<BR/>| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |<BR/>-------------------------------------------------------------------------------<BR/>| 0 | SELECT STATEMENT | | 102 | 3366 | 70 (0)| 00:00:01 |<BR/>| 1 | TABLE ACCESS BY INDEX ROWID| YOAV_BULK_LOAD | 102 | 3366 | 70 (0)| 00:00:01 |<BR/>|* 2 | INDEX RANGE SCAN | YOAV_IDX | | | 3 (0)| 00:00:01 |<BR/>-------------------------------------------------------------------------------<BR/><BR/>=========<BR/>STEP 6:<BR/>=========<BR/><BR/>-- So , i gather histograms on this table :<BR/><BR/>SQL> exec DBMS_STATS.GATHER_TABLE_STATS(ownname =>'BEZEQ', tabname => 'yoav_bulk_load', method_opt => 'FOR COLUMNS TYPE SIZE 254');<BR/><BR/>PL/SQL procedure successfully completed.<BR/><BR/>SQL> select * from dba_tab_histograms where table_name = 'YOAV_BULK_LOAD';<BR/><BR/><BR/>OWNER TABLE_NAME COLUMN_NAME ENDPOINT_NUMBER ENDPOINT_VALUE ENDPOINT_ACTUAL_VALU<BR/>------------------------------ ------------------------------ -------------------- --------------- -------------- --------------------<BR/>BEZEQ YOAV_BULK_LOAD TYPE 1 4.3233E+35<BR/>BEZEQ YOAV_BULK_LOAD TYPE 5601 4.4802E+35<BR/>BEZEQ YOAV_BULK_LOAD ID 0 159<BR/>BEZEQ YOAV_BULK_LOAD NAME 0 3.3905E+35<BR/>BEZEQ YOAV_BULK_LOAD MEASURE 0 159<BR/>BEZEQ YOAV_BULK_LOAD ID 1 10976745<BR/>BEZEQ YOAV_BULK_LOAD NAME 1 4.9565E+35<BR/>BEZEQ YOAV_BULK_LOAD MEASURE 1 10976745<BR/><BR/>8 rows selected.<BR/><BR/>SQL> explain plan for<BR/> 2 select * from YOAV_BULK_LOAD <BR/> 3 where type = 'VIEW'<BR/> 4 order by id;<BR/><BR/>Explained.<BR/><BR/>SQL> <BR/>SQL> select * from table(dbms_xplan.display);<BR/><BR/>PLAN_TABLE_OUTPUT<BR/>----------------------------------------------------------------------------------------------<BR/>| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |<BR/>-------------------------------------------------------------------------------<BR/>| 0 | SELECT STATEMENT | | 101 | 3333 | 68 (0)| 00:00:01 |<BR/>| 1 | TABLE ACCESS BY INDEX ROWID| YOAV_BULK_LOAD | 101 | 3333 | 68 (0)| 00:00:01 |<BR/>|* 2 | INDEX RANGE SCAN | YOAV_IDX | | | 3 (0)| 00:00:01 |<BR/>----------------------------------------------------------------------------------------------<BR/><BR/>=====================================================================<BR/>STEP 7: ONLY AFTER USING ALL_ROWS HINT ORACLE IS CHOOCING FTS<BR/>=====================================================================<BR/><BR/>SQL> explain plan for<BR/> 2 select /*+ all_rows */ * from YOAV_BULK_LOAD <BR/> 3 where type = 'VIEW'<BR/> 4 order by id;<BR/><BR/>Explained.<BR/><BR/>SQL> <BR/>SQL> select * from table(dbms_xplan.display);<BR/><BR/>PLAN_TABLE_OUTPUT<BR/>---------------------------------------------------------------------------------------------<BR/>| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |<BR/>---------------------------------------------------------------------------------------------<BR/>| 0 | SELECT STATEMENT | | 1527K| 48M| | 27727 (6)| 00:05:05 |<BR/>| 1 | SORT ORDER BY | | 1527K| 48M| 142M| 27727 (6)| 00:05:05 |<BR/>|* 2 | TABLE ACCESS FULL| YOAV_BULK_LOAD | 1527K| 48M| | 3675 (10)| 00:00:41 |<BR/>---------------------------------------------------------------------------------------------Anonymousnoreply@blogger.com