sql执行计划变了

set lines 150 pages 150
col BEGIN_INTERVAL_TIME for a23
col PLAN_HASH_VALUE for 9999999999
col date_time for a30
col snap_id heading 'SnapId'
col executions_delta heading "No. of exec"
col sql_profile heading "SQL|Profile" for a7
col date_time heading 'Date time'
 
col avg_lio heading 'LIO/exec' for 99999999999.99
col avg_cputime heading 'CPUTIM/exec' for 9999999.99
col avg_etime heading 'ETIME/exec' for 9999999.99
col avg_pio heading 'PIO/exec' for 9999999.99
col avg_row heading 'ROWs/exec' for 9999999.99
SELECT distinct
s.snap_id ,
PLAN_HASH_VALUE,
to_char(s.BEGIN_INTERVAL_TIME,'mm/dd/yy_hh24mi')|| to_char(s.END_INTERVAL_TIME,'_hh24mi') Date_Time,
SQL.executions_delta,
SQL.buffer_gets_delta/decode(nvl(SQL.executions_delta,0),0,1,SQL.executions_delta) avg_lio,
--SQL.ccwait_delta,
(SQL.cpu_time_delta/1000000)/decode(nvl(SQL.executions_delta,0),0,1,SQL.executions_delta) avg_cputime ,
(SQL.elapsed_time_delta/1000000)/decode(nvl(SQL.executions_delta,0),0,1,SQL.executions_delta) avg_etime,
SQL.DISK_READS_DELTA/decode(nvl(SQL.executions_delta,0),0,1,SQL.executions_delta) avg_pio,
SQL.rows_processed_total/decode(nvl(SQL.executions_delta,0),0,1,SQL.executions_delta) avg_row
--,SQL.sql_profile
FROM
dba_hist_sqlstat SQL,
dba_hist_snapshot s
WHERE
SQL.instance_number =(select instance_number from v$instance)
and SQL.dbid =(select dbid from v$database)
and s.snap_id = SQL.snap_id
AND sql_id in
('&SQLID') order by s.snap_id
/ 
 
 
 
 
 
select plan_table_output from table (dbms_xplan.display_awr('&sql_id',null,null,
'ADVANCED +PEEKED_BINDS'));

请把 结果发给我 ：

有问题请去OTN中文论坛开个帖子 我会回复 地址：http://www.otncn.org
如果需要发送附件，可以直接发邮件到 liu.maclean@gmail.com

==========================================================================================================
 
SQL> select plan_table_output from table (dbms_xplan.display_awr('&sql_id',null,null,'ADVANCED +PEEKED_BINDS'));
Enter value for sql_id: c9j9xmsyk285f
old   1: select plan_table_output from table (dbms_xplan.display_awr('&sql_id',null,null,'ADVANCED +PEEKED_BINDS'))
new   1: select plan_table_output from table (dbms_xplan.display_awr('c9j9xmsyk285f',null,null,'ADVANCED +PEEKED_BINDS'))
 
PLAN_TABLE_OUTPUT                                                                                                                                     
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID c9j9xmsyk285f                                                                                                                                  
--------------------                                                                                                                                  
select * from ( select content0_.CONTENT_ID as CONTENT1_30_0_,                                                                                        
contentblo1_.BLOB_ID as BLOB1_31_1_, content0_.FOLDER_ID as                                                                                           
FOLDER2_30_0_, content0_.TEMPLATE_ID as TEMPLATE3_30_0_,                                                                                              
content0_.CONTENT_PATH as CONTENT4_30_0_, content0_.CONTENT_PATH_CODE                                                                                 
as CONTENT5_30_0_, content0_.CONTENT_ORDER as CONTENT6_30_0_,                                                                                         
content0_.CONTENT_TITLE as CONTENT7_30_0_, content0_.CONTENT_AUTHOR as                                                                                
CONTENT8_30_0_, content0_.CONTENT_WRITE_DATE as CONTENT9_30_0_,                                                                                       
content0_.CONTENT_TIMER_STATUS as CONTENT10_30_0_,                                                                                                    
content0_.CONTENT_TIMER as CONTENT11_30_0_, content0_.CONTENT_KEYWORDS                                                                                
as CONTENT12_30_0_, content0_.CONTENT_SOURCE as CONTENT13_30_0_,                                                                                      
content0_.CONTENT_SUMMARY as CONTENT14_30_0_, content0_.CONTENT_STATE                                                                                 
as CONTENT15_30_0_, content0_.CREATER as CREATER30_0_,                                                                                                
content0_.CREATION_DATE as CREATION17_30_0_, content0_.MENDER as                                                                                      
MENDER30_0_, content0_.MODIFIED_DATE as MODIFIED19_30_0_,                                                                                             
content0_.CONTENT_VERIFY as CONTENT20_30_0_, content0_.CONTENT_EXPIRE                                                                                 
as CONTENT21_30_0_, content0_.ORDER_DATE as ORDER22_30_0_,                                                                                            
content0_.ORDER_NUMBER as ORDER23_30_0_, content0_.CONTENT_TYPE as                                                                                    
CONTENT24_30_0_, content0_.UPDATE_DATE as UPDATE25_30_0_,                                                                                             
content0_.START_DATE as START26_30_0_, content0_.OPENTYPE as                                                                                          
OPENTYPE30_0_, content0_.STATICURL as STATICURL30_0_, content0_.AFFIX                                                                                 
as AFFIX30_0_, content0_.IMP_CONTENT_ID as IMP30_30_0_,                                                                                               
content0_.SCORE_TYPE_ID as SCORE31_30_0_, content0_.COMMENT_TYPE_ID as                                                                                
COMMENT32_30_0_, content0_.PARENT_CONTENT_ID as PARENT33_30_0_,                                                                                       
content0_.COMMENT_TYPE as COMMENT34_30_0_, content0_.SCHEDULE_TIME as                                                                                 
SCHEDULE35_30_0_, content0_.ICON_TIME as ICON36_30_0_,                                                                                                
content0_.CONTENT_TIME as CONTENT37_30_0_, content0_.MESSAGE_FLAG as                                                                                  
MESSAGE38_30_0_, content0_.RELATED as RELATED30_0_,                                                                                                   
content0_.RELATED_CONTENT_ID as RELATED40_30_0_,                                                                                                      
content0_.CONTENT_HYPERLINK as CONTENT41_30_0_,                                                                                                       
contentblo1_.CONTENT_PROPERTY as CONTENT2_31_1_,                                                                                                      
contentblo1_.SECURITY_POLICY as SECURITY3_31_1_,                                                                                                      
contentblo1_.SUBSCRIPTION_POLICY as SUBSCRIP4_31_1_ from                                                                                              
VEGA_WCM_CONTENT_MAIN content0_, VEGA_WCM_CONTENT_BLOB contentblo1_                                                                                   
where content0_.CONTENT_ID=contentblo1_.BLOB_ID and                                                                                                   
content0_.CONTENT_STATE=:1 and content0_.CONTENT_PATH_CODE=:2 and                                                                                     
(content0_.CREATER='guest' or exists (select viewpolicy2_.POLICY_ID                                                                                   
from VEGA_WCM_VIEW_POLICY viewpolicy2_ where                                                                                                          
content0_.CONTENT_ID=viewpolicy2_.CONTENT_ID and                                                                                                      
(viewpolicy2_.POLICY_TYPE||viewpolicy2_.POLICY_NAME in                                                                                                
('com.coolwen.core.security.impl.UserPrincipalImplguest' ,                                                                                            
'com.coolwen.core.security.impl.UserPrincipalImplguest')))) order by                                                                                  
content0_.CONTENT_ORDER DESC, content0_.ORDER_DATE DESC,                                                                                              
content0_.ORDER_NUMBER DESC ) where rownum <= :3                                                                                                      
                                                                                                                                                      
Plan hash value: 1735989731                                                                                                                           
                                                                                                                                                      
----------------------------------------------------------------------------------------------------------                                            
| Id  | Operation                        | Name                  | Rows  | Bytes | Cost (%CPU)| Time     |                                            
----------------------------------------------------------------------------------------------------------                                            
|   0 | SELECT STATEMENT                 |                       |       |       |    30 (100)|          |                                            
|   1 |  COUNT STOPKEY                   |                       |       |       |            |          |                                            
|   2 |   VIEW                           |                       |     1 | 13713 |    30   (4)| 00:00:01 |                                            
|   3 |    SORT ORDER BY STOPKEY         |                       |     1 |   715 |    30   (4)| 00:00:01 |                                            
|   4 |     FILTER                       |                       |       |       |            |          |                                            
|   5 |      NESTED LOOPS                |                       |    11 |  7865 |    29   (0)| 00:00:01 |                                            
|   6 |       TABLE ACCESS BY INDEX ROWID| VEGA_WCM_CONTENT_MAIN |    11 |  4631 |     7   (0)| 00:00:01 |                                            
|   7 |        INDEX RANGE SCAN          | CONTENT_PATH_CODE     |    12 |       |     3   (0)| 00:00:01 |                                            
|   8 |       TABLE ACCESS BY INDEX ROWID| VEGA_WCM_CONTENT_BLOB |     1 |   294 |     2   (0)| 00:00:01 |                                            
|   9 |        INDEX UNIQUE SCAN         | SYS_C007292           |     1 |       |     1   (0)| 00:00:01 |                                            
|  10 |      TABLE ACCESS BY INDEX ROWID | VEGA_WCM_VIEW_POLICY  |     1 |    89 |     4   (0)| 00:00:01 |                                            
|  11 |       INDEX RANGE SCAN           | POLICY_CONTENT_ID     |     1 |       |     3   (0)| 00:00:01 |                                            
----------------------------------------------------------------------------------------------------------                                            
                                                                                                                                                      
Query Block Name / Object Alias (identified by operation id):                                                                                         
-------------------------------------------------------------                                                                                         
                                                                                                                                                      
   1 - SEL$1                                                                                                                                          
   2 - SEL$2 / from$_subquery$_001@SEL$1                                                                                                              
   3 - SEL$2                                                                                                                                          
   6 - SEL$2 / CONTENT0_@SEL$2                                                                                                                        
   7 - SEL$2 / CONTENT0_@SEL$2                                                                                                                        
   8 - SEL$2 / CONTENTBLO1_@SEL$2                                                                                                                     
   9 - SEL$2 / CONTENTBLO1_@SEL$2                                                                                                                     
  10 - SEL$3 / VIEWPOLICY2_@SEL$3                                                                                                                     
  11 - SEL$3 / VIEWPOLICY2_@SEL$3                                                                                                                     
                                                                                                                                                      
Outline Data                                                                                                                                          
-------------                                                                                                                                         
                                                                                                                                                      
  /*+                                                                                                                                                 
      BEGIN_OUTLINE_DATA                                                                                                                              
      IGNORE_OPTIM_EMBEDDED_HINTS                                                                                                                     
      OPTIMIZER_FEATURES_ENABLE('11.2.0.2')                                                                                                           
      DB_VERSION('11.2.0.2')                                                                                                                          
      ALL_ROWS                                                                                                                                        
      OUTLINE_LEAF(@"SEL$3")                                                                                                                          
      OUTLINE_LEAF(@"SEL$2")                                                                                                                          
      OUTLINE_LEAF(@"SEL$1")                                                                                                                          
      NO_ACCESS(@"SEL$1" "from$_subquery$_001"@"SEL$1")                                                                                               
      INDEX_RS_ASC(@"SEL$2" "CONTENT0_"@"SEL$2" ("VEGA_WCM_CONTENT_MAIN"."CONTENT_PATH_CODE"))                                                        
      INDEX_RS_ASC(@"SEL$2" "CONTENTBLO1_"@"SEL$2" ("VEGA_WCM_CONTENT_BLOB"."BLOB_ID"))                                                               
      LEADING(@"SEL$2" "CONTENT0_"@"SEL$2" "CONTENTBLO1_"@"SEL$2")                                                                                    
      USE_NL(@"SEL$2" "CONTENTBLO1_"@"SEL$2")                                                                                                         
      INDEX_RS_ASC(@"SEL$3" "VIEWPOLICY2_"@"SEL$3" ("VEGA_WCM_VIEW_POLICY"."CONTENT_ID"))                                                             
      END_OUTLINE_DATA                                                                                                                                
  */                                                                                                                                                  
                                                                                                                                                      
Peeked Binds (identified by position):                                                                                                                
--------------------------------------                                                                                                                
                                                                                                                                                      
   1 - :1 (NUMBER): 3                                                                                                                                 
   2 - :2 (VARCHAR2(30), CSID=873): '/zong/zh/wpt6xglw/o7npo2m5/be51d66w'                                                                             
   3 - :3 (NUMBER): 5                                                                                                                                 
                                                                                                                                                      
Note                                                                                                                                                  
-----                                                                                                                                                 
   - cardinality feedback used for this statement                                                                                                     
                                                                                                                                                      
SQL_ID c9j9xmsyk285f                                                                                                                                  
--------------------                                                                                                                                  
                                                                             
 
PLAN_TABLE_OUTPUT                                                                                                                                     
------------------------------------------------------------------------------------------------------------------------------------------------------
(content0_.CREATER='guest' or exists (select viewpolicy2_.POLICY_ID                                                                                   
from VEGA_WCM_VIEW_POLICY viewpolicy2_ where                                                                                                          
content0_.CONTENT_ID=viewpolicy2_.CONTENT_ID and                                                                                                      
(viewpolicy2_.POLICY_TYPE||viewpolicy2_.POLICY_NAME in                                                                                                
('com.coolwen.core.security.impl.UserPrincipalImplguest' ,                                                                                            
'com.coolwen.core.security.impl.UserPrincipalImplguest')))) order by                                                                                  
content0_.CONTENT_ORDER DESC, content0_.ORDER_DATE DESC,                                                                                              
content0_.ORDER_NUMBER DESC ) where rownum <= :3                                                                                                      
                                                                                                                                                      
Plan hash value: 4092041270                                                                                                                           
                                                                                                                                                      
-----------------------------------------------------------------------------------------------------------------                                     
| Id  | Operation                       | Name                  | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |                                     
-----------------------------------------------------------------------------------------------------------------                                     
|   0 | SELECT STATEMENT                |                       |       |       |       | 13605 (100)|          |                                     
|   1 |  COUNT STOPKEY                  |                       |       |       |       |            |          |                                     
|   2 |   VIEW                          |                       |  1028 |    13M|       | 13605   (1)| 00:02:44 |                                     
|   3 |    SORT ORDER BY STOPKEY        |                       |  1028 |   717K|    12M| 13605   (1)| 00:02:44 |                                     
|   4 |     FILTER                      |                       |       |       |       |            |          |                                     
|   5 |      HASH JOIN                  |                       | 17880 |    12M|  7568K| 10908   (1)| 00:02:11 |                                     
|   6 |       TABLE ACCESS FULL         | VEGA_WCM_CONTENT_MAIN | 17880 |  7351K|       |  1681   (1)| 00:00:21 |                                     
|   7 |       TABLE ACCESS FULL         | VEGA_WCM_CONTENT_BLOB |   101K|    28M|       |  7383   (1)| 00:01:29 |                                     
|   8 |      TABLE ACCESS BY INDEX ROWID| VEGA_WCM_VIEW_POLICY  |     1 |    89 |       |     4   (0)| 00:00:01 |                                     
|   9 |       INDEX RANGE SCAN          | POLICY_CONTENT_ID     |     1 |       |       |     3   (0)| 00:00:01 |                                     
-----------------------------------------------------------------------------------------------------------------                                     
                                                                                                                                                      
Query Block Name / Object Alias (identified by operation id):                                                                                         
-------------------------------------------------------------                                                                                         
                                                                                                                                                      
   1 - SEL$1                                                                                                                                          
   2 - SEL$2 / from$_subquery$_001@SEL$1                                                                                                              
   3 - SEL$2                                                                                                                                          
   6 - SEL$2 / CONTENT0_@SEL$2                                                                                                                        
   7 - SEL$2 / CONTENTBLO1_@SEL$2                                                                                                                     
   8 - SEL$3 / VIEWPOLICY2_@SEL$3                                                                                                                     
   9 - SEL$3 / VIEWPOLICY2_@SEL$3                                                                                                                     
                                                                                                                                                      
Outline Data                                                                                                                                          
-------------                                                                                                                                         
                                                                                                                                                      
  /*+                                                                                                                                                 
      BEGIN_OUTLINE_DATA                                                                                                                              
      IGNORE_OPTIM_EMBEDDED_HINTS                                                                                                                     
      OPTIMIZER_FEATURES_ENABLE('11.2.0.2')                                                                                                           
      DB_VERSION('11.2.0.2')                                                                                                                          
      ALL_ROWS                                                                                                                                        
      OUTLINE_LEAF(@"SEL$3")                                                                                                                          
      OUTLINE_LEAF(@"SEL$2")                                                                                                                          
      OUTLINE_LEAF(@"SEL$1")                                                                                                                          
      NO_ACCESS(@"SEL$1" "from$_subquery$_001"@"SEL$1")                                                                                               
      FULL(@"SEL$2" "CONTENT0_"@"SEL$2")                                                                                                              
      FULL(@"SEL$2" "CONTENTBLO1_"@"SEL$2")                                                                                                           
      LEADING(@"SEL$2" "CONTENT0_"@"SEL$2" "CONTENTBLO1_"@"SEL$2")                                                                                    
      USE_HASH(@"SEL$2" "CONTENTBLO1_"@"SEL$2")                                                                                                       
      INDEX_RS_ASC(@"SEL$3" "VIEWPOLICY2_"@"SEL$3" ("VEGA_WCM_VIEW_POLICY"."CONTENT_ID"))                                                             
      END_OUTLINE_DATA                                                                                                                                
  */                                                                                                                                                  
                                                                                                                                                      
Peeked Binds (identified by position):                                                                                                                
--------------------------------------                                                                                                                
                                                                                                                                                      
   1 - :1 (NUMBER): 3                                                                                                                                 
   2 - :2 (VARCHAR2(30), CSID=873): '/guizhoufgs/zh/4ezxm9j6/ktt03p29/pbqtb4tt/zw0ixoxa'                                                              
   3 - :3 (NUMBER): 1                                                                                                                                 
                                                                                                                                                      
Note                                                                                                                                                  
-----                                                                                                                                                 
   - cardinality feedback used for this statement                                                                                                     
                                                                                                                                                      
 
216 rows selected.
 
SQL> spool off

cardinality feedback used for this statement , cardinality feedback技术反馈是 11g优化器新特性

action plan:

alter SYSTEM set "_optimizer_use_feedback" = false ; --关闭cardinality feedback
alter SYSTEM set "_optim_peek_user_binds"=false; --关闭BIND PEEK

尝试设置以上参数 并使用楼上的脚本 定期检查该SQL的执行计划