问题背景：
客户反馈升级补丁包后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语句。
1 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的争用
1 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 会话
1 select 'alter system kill session ''' || a.sid || ',' || serial# || ''' immediate;'
2 from v$session a
3 where a.username='ECOLOGY'
4 AND a.STATUS='ACTIVE'
5 and event in('latch: cache buffers chains','latch free')
6
7 alter system kill session '56,18142' ;
8 alter system kill session '319,1510' ;
9 alter system kill session '1462,17432' ;
10 alter system kill session '3456,2847' ;
11 alter system kill session '3457,1717' ;
12 alter system kill session '3458,16756' ;
13 alter system kill session '3739,7185' ;
14 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工具
调优建议如下：
1 GENERAL INFORMATION SECTION 2 3 ------------------------------------------------------------------------------- 4 5 Tuning Task Name : staName58179 6 7 Tuning Task Owner : username 8 9 Tuning Task ID : 20591 10 11 Workload Type : Single SQL Statement 12 13 Execution Count : 1 14 15 Current Execution : EXEC_18371 16 17 Execution Type : TUNE SQL 18 19 Scope : COMPREHENSIVE 20 21 Time Limit(seconds): 1800 22 23 Completion Status : COMPLETED 24 25 Started at : 10/17/2019 16:42:31 26 27 Completed at : 10/17/2019 16:42:35 28 29 30 31 ------------------------------------------------------------------------------- 32 33 Schema Name: username 34 35 SQL ID : 48k1mg3r7vqms 36 37 SQL Text : 38 39 ------------------------------------------------------------------------------- 40 41 FINDINGS SECTION (2 findings) 42 43 ------------------------------------------------------------------------------- 44 45 46 47 1- Statistics Finding 48 49 --------------------- 50 51 表 "username"."table_name" 的优化程序统计信息已失效。 52 53 54 55 Recommendation 56 57 -------------- 58 59 - 考虑收集此表及其索引的优化程序统计信息。 60 61 execute dbms_stats.gather_table_stats(ownname => 'username', tabname => 62 63 'table_name', estimate_percent => 64 65 DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE 66 67 AUTO', cascade => TRUE); 68 69 70 71 Rationale 72 73 --------- 74 75 为了选择好的执行计划, 优化程序需要此表及其索引的最新统计信息。 76 77 78 79 2- SQL Profile Finding (see explain plans section below) 80 81 -------------------------------------------------------- 82 83 为此语句找到了性能更好的执行计划。 84 85 86 87 Recommendation (estimated benefit: 99.95%) 88 89 ------------------------------------------ 90 91 - 考虑接受推荐的 SQL 概要文件。 92 93 execute dbms_sqltune.accept_sql_profile(task_name => 'staName58179', 94 95 task_owner => 'username', replace => TRUE); 96 97 98 99 Validation results 100 101 ------------------ 102 103 已对 SQL profile 进行测试, 方法为执行其计划和原始计划并测量与计划相对应的执行统计信息。如果其中一个计划运行在很短的时间内就完成, 104 105 则另一计划可能只执行了一部分。 106 107 108 109 Original Plan With SQL Profile % Improved 110 111 ------------- ---------------- ---------- 112 113 Completion Status: COMPLETE COMPLETE 114 115 Elapsed Time(us): 76070 265 99.65 % 116 117 CPU Time(us): 61690 299 99.51 % 118 119 User I/O Time(us): 0 0 120 121 Buffer Gets: 36545 17 99.95 % 122 123 Physical Read Requests: 0 0 124 125 Physical Write Requests: 0 0 126 127 Physical Read Bytes: 0 0 128 129 Physical Write Bytes: 0 0 130 131 Rows Processed: 1 1 132 133 Fetches: 1 1 134 135 Executions: 1 1 136 137 138 139 Notes 140 141 ----- 142 143 1. original plan 已首先执行以预热缓冲区高速缓存。 144 145 2. original plan 的统计信息是后面的 9 执行的平均值。 146 147 3. SQL profile plan 已首先执行以预热缓冲区高速缓存。 148 149 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服务器负载已经正常，调优成功