About me:Oracle ACE pro,optimistic,passionate and harmonious. Focus on ORACLE,MySQL and other database programming,peformance tuning,db design, j2ee,Linux/AIX,Architecture tech,etc
全部博文(169)
分类: Oracle
2013-05-01 21:21:13
--关闭11g acs,因为11g的ACS会解决这个问题 alter SESSION set "_optimizer_extended_cursor_sharing_rel"=none; alter SESSION set "_optimizer_extended_cursor_sharing"=none; alter SESSION set "_optimizer_adaptive_cursor_sharing"=false; dingjun123@ORADB> var vid number; dingjun123@ORADB> exec :vid :=100; PL/SQL procedure successfully completed. Elapsed: 00:00:00.00 dingjun123@ORADB> select/*+gather_plan_statistics*/ * from test where object_id <= :vid ---- dingjun123@ORADB> @display_cursor; SQL_ID 902f2vh0y4pha, child number 0 ------------------------------------- select/*+gather_plan_statistics*/ * from test where object_id <= :vid Plan hash value: 115135762 ------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | ------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 99 |00:00:00.01 | 18 | | 1 | TABLE ACCESS BY INDEX ROWID| TEST | 1 | 69 | 99 |00:00:00.01 | 18 | |* 2 | INDEX RANGE SCAN | PK_TEST | 1 | 69 | 99 |00:00:00.04 | 9 | ------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("OBJECT_ID"<=:VID) dingjun123@ORADB> exec :vid := 100000; dingjun123@ORADB> select/*+gather_plan_statistics*/ * from test where object_id <= :vid --- dingjun123@ORADB> @display_cursor SQL_ID 902f2vh0y4pha, child number 0 ------------------------------------- select/*+gather_plan_statistics*/ * from test where object_id <= :vid Plan hash value: 115135762 ---------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | ---------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 74989 |00:00:00.87 | 11335 | 125 | | 1 | TABLE ACCESS BY INDEX ROWID| TEST | 1 | 69 | 74989 |00:00:00.87 | 11335 | 125 | |* 2 | INDEX RANGE SCAN | PK_TEST | 1 | 69 | 74989 |00:00:00.39 | 5143 | 125 | ---------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("OBJECT_ID"<=:VID) |