基于advisor优化工具的一次sql优化

来源：这里教程网时间：2026-03-03 14:20:58 作者：

问题背景：客户反馈升级补丁包后DB服务器CPU压力增致99% 解决思路： 1> 查看问题系统发现有大量的latch: cache buffers chains 等待； latch:cache buffers chains出现的原因 1、不够优化的SQL。大量逻辑读的SQL语句就有可能产生非常严重的latch:cache buffers chains等待，因为每次要访问一个block，就需要获得该latch，由于有大量的逻辑读，那么就增加了latch:cache buffers chains争用的机率。对于正在运行的SQL语句，产生非常严重的latch:cache buffers chains争用，可以利用下面SQL查看执行计划，并设法优化SQL语句。 select * from table(dbms_xplan.display_cursor('sql_id',sql_child_number)); 如果SQL已经运行完毕，我们就看AWR报表里面的SQL Statistics->SQL ordered by Gets->Gets per Exec，试图优化这些SQL。 2、热点块争用查找数据库是否存在latch的争用 select sid,event,p1text,p1raw from v$session_wait where event='latch: cache buffers chains' 配合awr报告查看可以确认确实有大量的latch：cache buffers chains 等待 2> 根据客户反馈昨天刚刚升级完毕补丁包并且补丁包里有新上线的sql，怀疑是新的sql不够优化导致首先把DB服务器的cpu降下来，kill latch: cache buffers chains 会话 select 'alter system kill session ''' || a.sid || ',' || serial# || ''' immediate;' from v$session a where a.username='ECOLOGY' AND a.STATUS='ACTIVE' and event in('latch: cache buffers chains','latch free') alter system kill session '56,18142' ; alter system kill session '319,1510' ; alter system kill session '1462,17432' ; alter system kill session '3456,2847' ; alter system kill session '3457,1717' ; alter system kill session '3458,16756' ; alter system kill session '3739,7185' ; alter system kill session '4000,2064' ; 3> 根据sql_id 查看问题sql的执行计划 SQL_ID gj9y6g28qx8hw, child number 3 ------------------------------------- Plan hash value: 563984120 Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$6D6869C2 7 - SEL$6D6869C2 / WORKFLOWCENTERSETTINGDETAIL@SEL$3 10 - SEL$6D6869C2 / WORKFLOWCENTERSETTINGDETAIL@SEL$4 12 - SEL$6D6869C2 / T1@SEL$2 13 - SEL$6D6869C2 / T2@SEL$2 14 - SEL$6D6869C2 / T2@SEL$2 15 - SEL$6D6869C2 / WORKFLOW_BASE@SEL$5 16 - SEL$6D6869C2 / WORKFLOW_BASE@SEL$5 Outline Data ------------- /*+ BEGIN_OUTLINE_DATA IGNORE_OPTIM_EMBEDDED_HINTS OPTIMIZER_FEATURES_ENABLE('11.2.0.1') DB_VERSION('11.2.0.1') ALL_ROWS OUTLINE_LEAF(@"SEL$6D6869C2") UNNEST(@"SEL$3") UNNEST(@"SEL$4") UNNEST(@"SEL$5") OUTLINE(@"SEL$F5BB74E1") MERGE(@"SEL$2") OUTLINE(@"SEL$3") OUTLINE(@"SEL$4") OUTLINE(@"SEL$5") OUTLINE(@"SEL$1") OUTLINE(@"SEL$2") INDEX(@"SEL$6D6869C2" "WORKFLOWCENTERSETTINGDETAIL"@"SEL$3" ("WORKFLOWCENTERSETTINGDETAIL"."EID" "WORKFLOWCENTERSETTINGDETAIL"."TABID" "WORKFLOWCENTERSETTINGDETAIL"."TYPE" "WORKFLOWCENTERSETTINGDETAIL"."CONTENT")) INDEX(@"SEL$6D6869C2" "WORKFLOWCENTERSETTINGDETAIL"@"SEL$4" ("WORKFLOWCENTERSETTINGDETAIL"."EID" "WORKFLOWCENTERSETTINGDETAIL"."TABID" "WORKFLOWCENTERSETTINGDETAIL"."TYPE" "WORKFLOWCENTERSETTINGDETAIL"."CONTENT")) FULL(@"SEL$6D6869C2" "T1"@"SEL$2") INDEX_RS_ASC(@"SEL$6D6869C2" "T2"@"SEL$2" ("WORKFLOW_CURRENTOPERATOR"."REQUESTID" "WORKFLOW_CURRENTOPERATOR"."USERID" "WORKFLOW_CURRENTOPERATOR"."WORKFLOWID")) INDEX_RS_ASC(@"SEL$6D6869C2" "WORKFLOW_BASE"@"SEL$5" ("WORKFLOW_BASE"."ID")) LEADING(@"SEL$6D6869C2" "WORKFLOWCENTERSETTINGDETAIL"@"SEL$3" "WORKFLOWCENTERSETTINGDETAIL"@"SEL$4" "T1"@"SEL$2" "T2"@"SEL$2" "WORKFLOW_BASE"@"SEL$5") USE_MERGE(@"SEL$6D6869C2" "WORKFLOWCENTERSETTINGDETAIL"@"SEL$4") USE_MERGE_CARTESIAN(@"SEL$6D6869C2" "T1"@"SEL$2") USE_NL(@"SEL$6D6869C2" "T2"@"SEL$2") USE_NL(@"SEL$6D6869C2" "WORKFLOW_BASE"@"SEL$5") END_OUTLINE_DATA */ Predicate Information (identified by operation id): --------------------------------------------------- 7 - access("EID"=962 AND "TABID"=5 AND "TYPE"='typeid') 10 - access("EID"=962 AND "TABID"=5 AND "TYPE"='flowid') 12 - filter((("T1"."DELETED"=0 OR "T1"."DELETED" IS NULL) AND ("T1"."CURRENTSTATUS" IS NULL OR "T1"."CURRENTSTATUS"<>1))) 13 - filter(("T2"."VIEWTYPE"=0 AND "T2"."ISLASTTIMES"=1 AND INTERNAL_FUNCTION("T2"."ISREMARK") AND 可以看出执行计划非常糟糕，这次不手工调优，尝试使用advisor工具调优建议如下： GENERAL INFORMATION SECTION ------------------------------------------------------------------------------- Tuning Task Name : staName58179 Tuning Task Owner : username Tuning Task ID : 20591 Workload Type : Single SQL Statement Execution Count : 1 Current Execution : EXEC_18371 Execution Type : TUNE SQL Scope : COMPREHENSIVE Time Limit(seconds): 1800 Completion Status : COMPLETED Started at : 10/17/2019 16:42:31 Completed at : 10/17/2019 16:42:35 ------------------------------------------------------------------------------- Schema Name: username SQL ID : 48k1mg3r7vqms SQL Text : ------------------------------------------------------------------------------- FINDINGS SECTION (2 findings) ------------------------------------------------------------------------------- 1- Statistics Finding --------------------- 表 "username"."table_name" 的优化程序统计信息已失效。 Recommendation -------------- - 考虑收集此表及其索引的优化程序统计信息。 execute dbms_stats.gather_table_stats(ownname => 'username', tabname => 'table_name', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE); Rationale --------- 为了选择好的执行计划, 优化程序需要此表及其索引的最新统计信息。 2- SQL Profile Finding (see explain plans section below) -------------------------------------------------------- 为此语句找到了性能更好的执行计划。 Recommendation (estimated benefit: 99.95%) ------------------------------------------ - 考虑接受推荐的 SQL 概要文件。 execute dbms_sqltune.accept_sql_profile(task_name => 'staName58179', task_owner => 'username', replace => TRUE); Validation results ------------------ 已对 SQL profile 进行测试, 方法为执行其计划和原始计划并测量与计划相对应的执行统计信息。如果其中一个计划运行在很短的时间内就完成, 则另一计划可能只执行了一部分。 Original Plan With SQL Profile % Improved ------------- ---------------- ---------- Completion Status: COMPLETE COMPLETE Elapsed Time(us): 76070 265 99.65 % CPU Time(us): 61690 299 99.51 % User I/O Time(us): 0 0 Buffer Gets: 36545 17 99.95 % Physical Read Requests: 0 0 Physical Write Requests: 0 0 Physical Read Bytes: 0 0 Physical Write Bytes: 0 0 Rows Processed: 1 1 Fetches: 1 1 Executions: 1 1 Notes ----- 1. original plan 已首先执行以预热缓冲区高速缓存。 2. original plan 的统计信息是后面的 9 执行的平均值。 3. SQL profile plan 已首先执行以预热缓冲区高速缓存。 4. the SQL profile plan 的统计信息是后面的 9 执行的平均值。 ------------------------------------------------------------------------------- EXPLAIN PLANS SECTION ------------------------------------------------------------------------------- 1- Original ----------- Plan hash value: 2478385950 Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$6D6869C2 8 - SEL$6D6869C2 / WORKFLOWCENTERSETTINGDETAIL@SEL$3 11 - SEL$6D6869C2 / WORKFLOWCENTERSETTINGDETAIL@SEL$4 13 - SEL$6D6869C2 / T2@SEL$2 14 - SEL$6D6869C2 / T2@SEL$2 15 - SEL$6D6869C2 / WORKFLOW_BASE@SEL$5 16 - SEL$6D6869C2 / WORKFLOW_BASE@SEL$5 17 - SEL$6D6869C2 / T1@SEL$2 18 - SEL$6D6869C2 / T1@SEL$2 Predicate Information (identified by operation id): --------------------------------------------------- 8 - access("EID"=962 AND "TABID"=5 AND "TYPE"='typeid') 11 - access("EID"=962 AND "TABID"=5 AND "TYPE"='flowid') 13 - filter("T2"."VIEWTYPE"=0 AND "T2"."ISLASTTIMES"=1 AND "T2"."WORKFLOWTYPE"=TO_NUMBER("CONTENT") AND "T2"."WORKFLOWID"=TO_NUMBER("CONTENT")) 14 - access(("T2"."ISREMARK"='7' OR "T2"."ISREMARK"='8' OR "T2"."ISREMARK"='9') AND "T2"."USERID"=695 AND "T2"."USERTYPE"=0) 15 - filter("ISVALID"='1' OR "ISVALID"='3') 16 - access("T2"."WORKFLOWID"="ID") 17 - access("T1"."REQUESTID"="T2"."REQUESTID") 18 - filter(("T1"."DELETED"=0 OR "T1"."DELETED" IS NULL) AND ("T1"."CURRENTSTATUS" IS NULL OR "T1"."CURRENTSTATUS"<>1)) Column Projection Information (identified by operation id): ----------------------------------------------------------- 1 - (#keys=0) COUNT(*)[22] 2 - (#keys=0) 3 - (#keys=0) "T1".ROWID[ROWID,10] 4 - (#keys=0) "T2"."REQUESTID"[NUMBER,22] 5 - (#keys=0) "T2"."REQUESTID"[NUMBER,22], "T2"."WORKFLOWID"[NUMBER,22] 6 - (#keys=0) TO_NUMBER("CONTENT")[22], TO_NUMBER("CONTENT")[22] 7 - (#keys=1) TO_NUMBER("CONTENT")[22] 8 - "CONTENT"[VARCHAR2,100] 9 - (#keys=0) TO_NUMBER("CONTENT")[22] 10 - (#keys=1) TO_NUMBER("CONTENT")[22] 11 - "CONTENT"[VARCHAR2,100] 12 - "T2"."REQUESTID"[NUMBER,22], "T2"."WORKFLOWID"[NUMBER,22] 13 - "T2"."REQUESTID"[NUMBER,22], "T2"."WORKFLOWID"[NUMBER,22] 14 - "T2".ROWID[ROWID,10] 16 - "WORKFLOW_BASE".ROWID[ROWID,10] 17 - "T1".ROWID[ROWID,10] 2- Original With Adjusted Cost ------------------------------ Plan hash value: 2478385950 Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$6D6869C2 8 - SEL$6D6869C2 / WORKFLOWCENTERSETTINGDETAIL@SEL$3 11 - SEL$6D6869C2 / WORKFLOWCENTERSETTINGDETAIL@SEL$4 13 - SEL$6D6869C2 / T2@SEL$2 14 - SEL$6D6869C2 / T2@SEL$2 15 - SEL$6D6869C2 / WORKFLOW_BASE@SEL$5 16 - SEL$6D6869C2 / WORKFLOW_BASE@SEL$5 17 - SEL$6D6869C2 / T1@SEL$2 18 - SEL$6D6869C2 / T1@SEL$2 Predicate Information (identified by operation id): --------------------------------------------------- 8 - access("EID"=962 AND "TABID"=5 AND "TYPE"='typeid') 11 - access("EID"=962 AND "TABID"=5 AND "TYPE"='flowid') 13 - filter("T2"."VIEWTYPE"=0 AND "T2"."ISLASTTIMES"=1 AND "T2"."WORKFLOWTYPE"=TO_NUMBER("CONTENT") AND "T2"."WORKFLOWID"=TO_NUMBER("CONTENT")) 14 - access(("T2"."ISREMARK"='7' OR "T2"."ISREMARK"='8' OR "T2"."ISREMARK"='9') AND "T2"."USERID"=695 AND "T2"."USERTYPE"=0) 15 - filter("ISVALID"='1' OR "ISVALID"='3') 16 - access("T2"."WORKFLOWID"="ID") 17 - access("T1"."REQUESTID"="T2"."REQUESTID") 18 - filter(("T1"."DELETED"=0 OR "T1"."DELETED" IS NULL) AND ("T1"."CURRENTSTATUS" IS NULL OR "T1"."CURRENTSTATUS"<>1)) Column Projection Information (identified by operation id): ----------------------------------------------------------- 1 - (#keys=0) COUNT(*)[22] 2 - (#keys=0) 3 - (#keys=0) "T1".ROWID[ROWID,10] 4 - (#keys=0) "T2"."REQUESTID"[NUMBER,22] 5 - (#keys=0) "T2"."REQUESTID"[NUMBER,22], "T2"."WORKFLOWID"[NUMBER,22] 6 - (#keys=0) TO_NUMBER("CONTENT")[22], TO_NUMBER("CONTENT")[22] 7 - (#keys=1) TO_NUMBER("CONTENT")[22] 8 - "CONTENT"[VARCHAR2,100] 9 - (#keys=0) TO_NUMBER("CONTENT")[22] 10 - (#keys=1) TO_NUMBER("CONTENT")[22] 11 - "CONTENT"[VARCHAR2,100] 12 - "T2"."REQUESTID"[NUMBER,22], "T2"."WORKFLOWID"[NUMBER,22] 13 - "T2"."REQUESTID"[NUMBER,22], "T2"."WORKFLOWID"[NUMBER,22] 14 - "T2".ROWID[ROWID,10] 16 - "WORKFLOW_BASE".ROWID[ROWID,10] 17 - "T1".ROWID[ROWID,10] 3- Using SQL Profile -------------------- Plan hash value: 1474559118 Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$6D6869C2 7 - SEL$6D6869C2 / T2@SEL$2 8 - SEL$6D6869C2 / T2@SEL$2 9 - SEL$6D6869C2 / WORKFLOWCENTERSETTINGDETAIL@SEL$3 10 - SEL$6D6869C2 / T1@SEL$2 11 - SEL$6D6869C2 / T1@SEL$2 12 - SEL$6D6869C2 / WORKFLOW_BASE@SEL$5 13 - SEL$6D6869C2 / WORKFLOW_BASE@SEL$5 14 - SEL$6D6869C2 / WORKFLOWCENTERSETTINGDETAIL@SEL$4 Predicate Information (identified by operation id): --------------------------------------------------- 5 - access("T2"."WORKFLOWTYPE"=TO_NUMBER("CONTENT")) 7 - filter("T2"."VIEWTYPE"=0 AND "T2"."ISLASTTIMES"=1) 8 - access(("T2"."ISREMARK"='7' OR "T2"."ISREMARK"='8' OR "T2"."ISREMARK"='9') AND "T2"."USERID"=695 AND "T2"."USERTYPE"=0) 9 - access("EID"=962 AND "TABID"=5 AND "TYPE"='typeid') 10 - filter(("T1"."DELETED"=0 OR "T1"."DELETED" IS NULL) AND ("T1"."CURRENTSTATUS" IS NULL OR "T1"."CURRENTSTATUS"<>1)) 11 - access("T1"."REQUESTID"="T2"."REQUESTID") 12 - filter("ISVALID"='1' OR "ISVALID"='3') 13 - access("T2"."WORKFLOWID"="ID") 14 - access("EID"=962 AND "TABID"=5 AND "TYPE"='flowid') filter("T2"."WORKFLOWID"=TO_NUMBER("CONTENT")) Column Projection Information (identified by operation id): ----------------------------------------------------------- 1 - (#keys=0) COUNT(*)[22] 2 - (#keys=0) 3 - (#keys=0) "T2"."WORKFLOWID"[NUMBER,22] 4 - (#keys=0) "T2"."WORKFLOWID"[NUMBER,22] 5 - (#keys=1) "T2"."REQUESTID"[NUMBER,22], "T2"."WORKFLOWID"[NUMBER,22] 6 - "T2"."REQUESTID"[NUMBER,22], "T2"."WORKFLOWID"[NUMBER,22], "T2"."WORKFLOWTYPE"[NUMBER,22] 7 - "T2"."REQUESTID"[NUMBER,22], "T2"."WORKFLOWID"[NUMBER,22], "T2"."WORKFLOWTYPE"[NUMBER,22] 8 - "T2".ROWID[ROWID,10] 9 - "CONTENT"[VARCHAR2,100] 11 - "T1".ROWID[ROWID,10] 13 - "WORKFLOW_BASE".ROWID[ROWID,10] ------------------------------------------------------------------------------- advisor建议收集统计信息 execute dbms_stats.gather_table_stats(ownname => 'username', tabname => 'table_name', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE); 建议绑定良好的执行计划 execute dbms_sqltune.accept_sql_profile(task_name => 'staName58179',task_owner => 'usrename', replace => TRUE); 4> 根据advisor给出的建议实际查看相关表是否统计信息失效，确实发现相关表的统计信息失效，收集完毕统计信息再次执行advisor测试 GENERAL INFORMATION SECTION ------------------------------------------------------------------------------- Tuning Task Name : staName86486 Tuning Task Owner : username Tuning Task ID : 20592 Workload Type : Single SQL Statement Execution Count : 1 Current Execution : EXEC_18372 Execution Type : TUNE SQL Scope : COMPREHENSIVE Time Limit(seconds): 1800 Completion Status : COMPLETED Started at : 10/17/2019 16:48:25 Completed at : 10/17/2019 16:48:26 ------------------------------------------------------------------------------- Schema Name: username SQL ID : 48k1mg3r7vqms SQL Text : ------------------------------------------------------------------------------- ADDITIONAL INFORMATION SECTION ------------------------------------------------------------------------------- - 此语句的 SQL 概要文件 "SYS_SQLPROF_016dd8e4cbcb0000" 已存在, 但在优化时被忽略。 ------------------------------------------------------------------------------- EXPLAIN PLANS SECTION ------------------------------------------------------------------------------- 1- Original ----------- Plan hash value: 1474559118 Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$6D6869C2 7 - SEL$6D6869C2 / T2@SEL$2 8 - SEL$6D6869C2 / T2@SEL$2 9 - SEL$6D6869C2 / WORKFLOWCENTERSETTINGDETAIL@SEL$3 10 - SEL$6D6869C2 / T1@SEL$2 11 - SEL$6D6869C2 / T1@SEL$2 12 - SEL$6D6869C2 / WORKFLOW_BASE@SEL$5 13 - SEL$6D6869C2 / WORKFLOW_BASE@SEL$5 14 - SEL$6D6869C2 / WORKFLOWCENTERSETTINGDETAIL@SEL$4 Predicate Information (identified by operation id): --------------------------------------------------- 5 - access("T2"."WORKFLOWTYPE"=TO_NUMBER("CONTENT")) 7 - filter("T2"."VIEWTYPE"=0 AND "T2"."ISLASTTIMES"=1) 8 - access(("T2"."ISREMARK"='7' OR "T2"."ISREMARK"='8' OR "T2"."ISREMARK"='9') AND "T2"."USERID"=695 AND "T2"."USERTYPE"=0) 9 - access("EID"=962 AND "TABID"=5 AND "TYPE"='typeid') 10 - filter(("T1"."DELETED"=0 OR "T1"."DELETED" IS NULL) AND ("T1"."CURRENTSTATUS" IS NULL OR "T1"."CURRENTSTATUS"<>1)) 11 - access("T1"."REQUESTID"="T2"."REQUESTID") 12 - filter("ISVALID"='1' OR "ISVALID"='3') 13 - access("T2"."WORKFLOWID"="ID") 14 - access("EID"=962 AND "TABID"=5 AND "TYPE"='flowid') filter("T2"."WORKFLOWID"=TO_NUMBER("CONTENT")) Column Projection Information (identified by operation id): ----------------------------------------------------------- 1 - (#keys=0) COUNT(*)[22] 2 - (#keys=0) 3 - (#keys=0) "T2"."WORKFLOWID"[NUMBER,22] 4 - (#keys=0) "T2"."WORKFLOWID"[NUMBER,22] 5 - (#keys=1) "T2"."REQUESTID"[NUMBER,22], "T2"."WORKFLOWID"[NUMBER,22] 6 - "T2"."REQUESTID"[NUMBER,22], "T2"."WORKFLOWID"[NUMBER,22], "T2"."WORKFLOWTYPE"[NUMBER,22] 7 - "T2"."REQUESTID"[NUMBER,22], "T2"."WORKFLOWID"[NUMBER,22], "T2"."WORKFLOWTYPE"[NUMBER,22] 8 - "T2".ROWID[ROWID,10] 9 - "CONTENT"[VARCHAR2,100] 11 - "T1".ROWID[ROWID,10] 13 - "WORKFLOW_BASE".ROWID[ROWID,10] Note ----- - SQL profile "SYS_SQLPROF_016dd8e4cbcb0000" used for this statement ------------------------------------------------------------------------------- advisor工具没有给出合理的调整，说明advisor工具判断目前的sql执行计划良好，再次执行相关sql结果秒出查看DB服务器负载已经正常，调优成功