advisor调优工具优化sql(基于sql_id)

问题背景：
客户反馈数据库迁移后cpu负载激增，帮忙查看原因

解决思路：
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'

首先把DB服务器的cpu降下来，kill latch: cache buffers chains 会话

1 select 'alter system kill session ''' || a.sid || ',' || serial# || ''';'
2 from v$session a
3 where a.username='USERNAME' 
4 AND a.STATUS='ACTIVE' 
5 and event in('latch: cache buffers chains','latch free')
6 alter system kill session '56,18142' ;
7 alter system kill session '319,1510' ;

2> 根据sql_id查看问题sql的执行计划，（有三个执行计划）

  1 SQL> select * from table(dbms_xplan.display_awr('&1')); 
  2 Enter value for 1: a6su7qgyywvn8
  3 old 1: select * from table(dbms_xplan.display_awr('&1'))
  4 new 1: select * from table(dbms_xplan.display_awr('a6su7qgyywvn8'))
  5 
  6 PLAN_TABLE_OUTPUT
  7 --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  8 SQL_ID a6su7qgyywvn8
  9 --------------------
 10 
 11 
 12 Plan hash value: 1019409405
 13 
 14 ---------------------------------------------------------------------------------------------------------------------------
 15 | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
 16 ---------------------------------------------------------------------------------------------------------------------------
 17 | 0 | SELECT STATEMENT | | | | 331 (100)| |
 18 | 1 | VIEW | | 532 | 205K| 331 (1)| 00:00:04 |
 19 | 2 | COUNT | | | | | |
 20 | 3 | VIEW | | 532 | 198K| 331 (1)| 00:00:04 |
 21 | 4 | SORT ORDER BY | | 532 | 192K| 331 (1)| 00:00:04 |
 22 | 5 | COUNT | | | | | |
 23 | 6 | VIEW | | 532 | 192K| 331 (1)| 00:00:04 |
 24 | 7 | HASH UNIQUE | | 532 | 97K| 331 (1)| 00:00:04 |
 25 | 8 | CONCATENATION | | | | | |
 26 | 9 | FILTER | | | | | |
 27 | 10 | NESTED LOOPS | | 1 | 188 | 12 (0)| 00:00:01 |
 28 | 11 | NESTED LOOPS | | 1 | 169 | 11 (0)| 00:00:01 |
 29 | 12 | NESTED LOOPS | | 1 | 90 | 10 (0)| 00:00:01 |
 30 | 13 | NESTED LOOPS | | 1 | 84 | 9 (0)| 00:00:01 |
 31 | 14 | INDEX FAST FULL SCAN | WORKFLOWCENTERSETTINGDETAIL_I | 1 | 19 | 4 (0)| 00:00:01 |
 32 | 15 | INLIST ITERATOR | | | | | |
 33 | 16 | TABLE ACCESS BY INDEX ROWID| WORKFLOW_CURRENTOPERATOR | 1 | 65 | 5 (0)| 00:00:01 |
 34 | 17 | INDEX RANGE SCAN | WRKCUOPER_USER_IN2 | 1 | | 4 (0)| 00:00:01 |
 35 | 18 | TABLE ACCESS BY INDEX ROWID | WORKFLOW_BASE | 1 | 6 | 1 (0)| 00:00:01 |
 36 | 19 | INDEX RANGE SCAN | IX_WFBASE_ID | 1 | | 0 (0)| |
 37 | 20 | TABLE ACCESS BY INDEX ROWID | WORKFLOW_REQUESTBASE | 1 | 79 | 1 (0)| 00:00:01 |
 38 | 21 | INDEX UNIQUE SCAN | WORKFLOW_REQUESTID | 1 | | 0 (0)| |
 39 | 22 | INDEX RANGE SCAN | WORKFLOWCENTERSETTINGDETAIL_I | 1 | 19 | 1 (0)| 00:00:01 |
 40 | 23 | INDEX RANGE SCAN | WORKFLOWCENTERSETTINGDETAIL_I | 1 | 19 | 2 (0)| 00:00:01 |
 41 | 24 | NESTED LOOPS | | 1 | 35 | 3 (0)| 00:00:01 |
 42 | 25 | MERGE JOIN CARTESIAN | | 1 | 26 | 2 (0)| 00:00:01 |
 43 | 26 | INDEX RANGE SCAN | IX_WORKFLOW_NODEBASE_IDIS | 1 | 7 | 1 (0)| 00:00:01 |
 44 | 27 | BUFFER SORT | | 1 | 19 | 1 (0)| 00:00:01 |
 45 | 28 | INDEX RANGE SCAN | WORKFLOWCENTERSETTINGDETAIL_I | 1 | 19 | 1 (0)| 00:00:01 |
 46 | 29 | INDEX RANGE SCAN | IX_WKFFLND_NW | 1 | 9 | 1 (0)| 00:00:01 |
 47 | 30 | FILTER | | | | | |
 48 | 31 | NESTED LOOPS | | | | | |
 49 | 32 | NESTED LOOPS | | 1 | 188 | 318 (0)| 00:00:04 |
 50 | 33 | NESTED LOOPS | | 1 | 182 | 317 (0)| 00:00:04 |
 51 | 34 | NESTED LOOPS | | 1 | 163 | 316 (0)| 00:00:04 |
 52 | 35 | NESTED LOOPS | | 1 | 84 | 315 (0)| 00:00:04 |
 53 | 36 | INDEX FAST FULL SCAN | WORKFLOWCENTERSETTINGDETAIL_I | 1 | 19 | 4 (0)| 00:00:01 |
 54 | 37 | TABLE ACCESS BY INDEX ROWID| WORKFLOW_CURRENTOPERATOR | 7 | 455 | 311 (0)| 00:00:04 |
 55 | 38 | INDEX RANGE SCAN | USERANDREQUESTID | 316 | | 3 (0)| 00:00:01 |
 56 | 39 | TABLE ACCESS BY INDEX ROWID | WORKFLOW_REQUESTBASE | 1 | 79 | 1 (0)| 00:00:01 |
 57 | 40 | INDEX UNIQUE SCAN | WORKFLOW_REQUESTID | 1 | | 0 (0)| |
 58 | 41 | INDEX RANGE SCAN | WORKFLOWCENTERSETTINGDETAIL_I | 1 | 19 | 1 (0)| 00:00:01 |
 59 | 42 | INDEX RANGE SCAN | IX_WFBASE_ID | 1 | | 0 (0)| |
 60 | 43 | TABLE ACCESS BY INDEX ROWID | WORKFLOW_BASE | 5 | 30 | 1 (0)| 00:00:01 |
 61 | 44 | INDEX RANGE SCAN | WORKFLOWCENTERSETTINGDETAIL_I | 1 | 19 | 2 (0)| 00:00:01 |
 62 | 45 | NESTED LOOPS | | 1 | 35 | 3 (0)| 00:00:01 |
 63 | 46 | MERGE JOIN CARTESIAN | | 1 | 26 | 2 (0)| 00:00:01 |
 64 | 47 | INDEX RANGE SCAN | IX_WORKFLOW_NODEBASE_IDIS | 1 | 7 | 1 (0)| 00:00:01 |
 65 | 48 | BUFFER SORT | | 1 | 19 | 1 (0)| 00:00:01 |
 66 | 49 | INDEX RANGE SCAN | WORKFLOWCENTERSETTINGDETAIL_I | 1 | 19 | 1 (0)| 00:00:01 |
 67 | 50 | INDEX RANGE SCAN | IX_WKFFLND_NW | 1 | 9 | 1 (0)| 00:00:01 |
 68 ---------------------------------------------------------------------------------------------------------------------------
 69 
 70 Note
 71 -----
 72 - cardinality feedback used for this statement
 73 
 74 SQL_ID a6su7qgyywvn8
 75 --------------------
 76 
 77 Plan hash value: 1061600654
 78 
 79 ---------------------------------------------------------------------------------------------------------------------------
 80 | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
 81 ---------------------------------------------------------------------------------------------------------------------------
 82 | 0 | SELECT STATEMENT | | | | 1507 (100)| |
 83 | 1 | VIEW | | 532 | 205K| 1507 (1)| 00:00:19 |
 84 | 2 | COUNT | | | | | |
 85 | 3 | VIEW | | 532 | 198K| 1507 (1)| 00:00:19 |
 86 | 4 | SORT ORDER BY | | 532 | 192K| 1507 (1)| 00:00:19 |
 87 | 5 | COUNT | | | | | |
 88 | 6 | VIEW | | 532 | 192K| 1507 (1)| 00:00:19 |
 89 | 7 | HASH UNIQUE | | 532 | 97K| 1507 (1)| 00:00:19 |
 90 | 8 | CONCATENATION | | | | | |
 91 | 9 | FILTER | | | | | |
 92 | 10 | NESTED LOOPS | | 1 | 188 | 12 (0)| 00:00:01 |
 93 | 11 | NESTED LOOPS | | 1 | 169 | 11 (0)| 00:00:01 |
 94 | 12 | NESTED LOOPS | | 1 | 90 | 10 (0)| 00:00:01 |
 95 | 13 | NESTED LOOPS | | 1 | 84 | 9 (0)| 00:00:01 |
 96 | 14 | INDEX FAST FULL SCAN | WORKFLOWCENTERSETTINGDETAIL_I | 1 | 19 | 4 (0)| 00:00:01 |
 97 | 15 | INLIST ITERATOR | | | | | |
 98 | 16 | TABLE ACCESS BY INDEX ROWID| WORKFLOW_CURRENTOPERATOR | 1 | 65 | 5 (0)| 00:00:01 |
 99 | 17 | INDEX RANGE SCAN | WRKCUOPER_USER_IN2 | 1 | | 4 (0)| 00:00:01 |
100 | 18 | TABLE ACCESS BY INDEX ROWID | WORKFLOW_BASE | 1 | 6 | 1 (0)| 00:00:01 |
101 | 19 | INDEX RANGE SCAN | IX_WFBASE_ID | 1 | | 0 (0)| |
102 | 20 | TABLE ACCESS BY INDEX ROWID | WORKFLOW_REQUESTBASE | 1 | 79 | 1 (0)| 00:00:01 |
103 | 21 | INDEX UNIQUE SCAN | WORKFLOW_REQUESTID | 1 | | 0 (0)| |
104 | 22 | INDEX RANGE SCAN | WORKFLOWCENTERSETTINGDETAIL_I | 1 | 19 | 1 (0)| 00:00:01 |
105 | 23 | INDEX RANGE SCAN | WORKFLOWCENTERSETTINGDETAIL_I | 1 | 19 | 2 (0)| 00:00:01 |
106 | 24 | NESTED LOOPS | | 1 | 35 | 3 (0)| 00:00:01 |
107 | 25 | MERGE JOIN CARTESIAN | | 1 | 26 | 2 (0)| 00:00:01 |
108 | 26 | INDEX RANGE SCAN | IX_WORKFLOW_NODEBASE_IDIS | 1 | 7 | 1 (0)| 00:00:01 |
109 | 27 | BUFFER SORT | | 1 | 19 | 1 (0)| 00:00:01 |
110 | 28 | INDEX RANGE SCAN | WORKFLOWCENTERSETTINGDETAIL_I | 1 | 19 | 1 (0)| 00:00:01 |
111 | 29 | INDEX RANGE SCAN | IX_WKFFLND_NW | 1 | 9 | 1 (0)| 00:00:01 |
112 | 30 | FILTER | | | | | |
113 | 31 | NESTED LOOPS | | | | | |
114 | 32 | NESTED LOOPS | | 1 | 188 | 1494 (1)| 00:00:18 |
115 | 33 | NESTED LOOPS | | 1 | 182 | 1493 (1)| 00:00:18 |
116 | 34 | MERGE JOIN CARTESIAN | | 40 | 4680 | 1373 (1)| 00:00:17 |
117 | 35 | MERGE JOIN CARTESIAN | | 1 | 38 | 5 (0)| 00:00:01 |
118 | 36 | INDEX FAST FULL SCAN | WORKFLOWCENTERSETTINGDETAIL_I | 1 | 19 | 4 (0)| 00:00:01 |
119 | 37 | BUFFER SORT | | 1 | 19 | 1 (0)| 00:00:01 |
120 | 38 | INDEX RANGE SCAN | WORKFLOWCENTERSETTINGDETAIL_I | 1 | 19 | 1 (0)| 00:00:01 |
121 | 39 | BUFFER SORT | | 105K| 8150K| 1372 (1)| 00:00:17 |
122 | 40 | TABLE ACCESS FULL | WORKFLOW_REQUESTBASE | 105K| 8150K| 1368 (1)| 00:00:17 |
123 | 41 | TABLE ACCESS BY INDEX ROWID | WORKFLOW_CURRENTOPERATOR | 1 | 65 | 3 (0)| 00:00:01 |
124 | 42 | INDEX RANGE SCAN | WORKFLOWUSERANDID | 1 | | 2 (0)| 00:00:01 |
125 | 43 | INDEX RANGE SCAN | IX_WFBASE_ID | 1 | | 0 (0)| |
126 | 44 | TABLE ACCESS BY INDEX ROWID | WORKFLOW_BASE | 5 | 30 | 1 (0)| 00:00:01 |
127 | 45 | INDEX RANGE SCAN | WORKFLOWCENTERSETTINGDETAIL_I | 1 | 19 | 2 (0)| 00:00:01 |
128 | 46 | NESTED LOOPS | | 1 | 35 | 3 (0)| 00:00:01 |
129 | 47 | MERGE JOIN CARTESIAN | | 1 | 26 | 2 (0)| 00:00:01 |
130 | 48 | INDEX RANGE SCAN | IX_WORKFLOW_NODEBASE_IDIS | 1 | 7 | 1 (0)| 00:00:01 |
131 | 49 | BUFFER SORT | | 1 | 19 | 1 (0)| 00:00:01 |
132 | 50 | INDEX RANGE SCAN | WORKFLOWCENTERSETTINGDETAIL_I | 1 | 19 | 1 (0)| 00:00:01 |
133 | 51 | INDEX RANGE SCAN | IX_WKFFLND_NW | 1 | 9 | 1 (0)| 00:00:01 |
134 ---------------------------------------------------------------------------------------------------------------------------
135 
136 Note
137 -----
138 - cardinality feedback used for this statement
139 
140 SQL_ID a6su7qgyywvn8
141 --------------------
142 
143 Plan hash value: 1147966846
144 
145 ---------------------------------------------------------------------------------------------------------------------------
146 | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
147 ---------------------------------------------------------------------------------------------------------------------------
148 | 0 | SELECT STATEMENT | | | | 24 (100)| |
149 | 1 | VIEW | | 1 | 396 | 24 (5)| 00:00:01 |
150 | 2 | COUNT | | | | | |
151 | 3 | VIEW | | 1 | 383 | 24 (5)| 00:00:01 |
152 | 4 | SORT ORDER BY | | 1 | 370 | 24 (5)| 00:00:01 |
153 | 5 | COUNT | | | | | |
154 | 6 | VIEW | | 1 | 370 | 24 (5)| 00:00:01 |
155 | 7 | HASH UNIQUE | | 1 | 188 | 24 (5)| 00:00:01 |
156 | 8 | CONCATENATION | | | | | |
157 | 9 | FILTER | | | | | |
158 | 10 | NESTED LOOPS | | | | | |
159 | 11 | NESTED LOOPS | | 1 | 188 | 10 (0)| 00:00:01 |
160 | 12 | NESTED LOOPS | | 1 | 109 | 9 (0)| 00:00:01 |
161 | 13 | NESTED LOOPS | | 1 | 103 | 8 (0)| 00:00:01 |
162 | 14 | MERGE JOIN CARTESIAN | | 1 | 38 | 3 (0)| 00:00:01 |
163 | 15 | INDEX RANGE SCAN | WORKFLOWCENTERSETTINGDETAIL_I | 1 | 19 | 2 (0)| 00:00:01 |
164 | 16 | BUFFER SORT | | 1 | 19 | 1 (0)| 00:00:01 |
165 | 17 | INDEX RANGE SCAN | WORKFLOWCENTERSETTINGDETAIL_I | 1 | 19 | 1 (0)| 00:00:01 |
166 | 18 | INLIST ITERATOR | | | | | |
167 | 19 | TABLE ACCESS BY INDEX ROWID| WORKFLOW_CURRENTOPERATOR | 1 | 65 | 5 (0)| 00:00:01 |
168 | 20 | INDEX RANGE SCAN | WRKCUOPER_USER_IN2 | 1 | | 4 (0)| 00:00:01 |
169 | 21 | TABLE ACCESS BY INDEX ROWID | WORKFLOW_BASE | 1 | 6 | 1 (0)| 00:00:01 |
170 | 22 | INDEX RANGE SCAN | IX_WFBASE_ID | 1 | | 0 (0)| |
171 | 23 | INDEX UNIQUE SCAN | WORKFLOW_REQUESTID | 1 | | 0 (0)| |
172 | 24 | TABLE ACCESS BY INDEX ROWID | WORKFLOW_REQUESTBASE | 1 | 79 | 1 (0)| 00:00:01 |
173 | 25 | INDEX RANGE SCAN | WORKFLOWCENTERSETTINGDETAIL_I | 1 | 19 | 2 (0)| 00:00:01 |
174 | 26 | NESTED LOOPS | | 1 | 35 | 3 (0)| 00:00:01 |
175 | 27 | MERGE JOIN CARTESIAN | | 1 | 26 | 2 (0)| 00:00:01 |
176 | 28 | INDEX RANGE SCAN | IX_WORKFLOW_NODEBASE_IDIS | 1 | 7 | 1 (0)| 00:00:01 |
177 | 29 | BUFFER SORT | | 1 | 19 | 1 (0)| 00:00:01 |
178 | 30 | INDEX RANGE SCAN | WORKFLOWCENTERSETTINGDETAIL_I | 1 | 19 | 1 (0)| 00:00:01 |
179 | 31 | INDEX RANGE SCAN | IX_WKFFLND_NW | 1 | 9 | 1 (0)| 00:00:01 |
180 | 32 | FILTER | | | | | |
181 | 33 | NESTED LOOPS | | | | | |
182 | 34 | NESTED LOOPS | | 1 | 188 | 13 (0)| 00:00:01 |
183 | 35 | NESTED LOOPS | | 1 | 109 | 12 (0)| 00:00:01 |
184 | 36 | NESTED LOOPS | | 1 | 103 | 11 (0)| 00:00:01 |
185 | 37 | MERGE JOIN CARTESIAN | | 1 | 38 | 3 (0)| 00:00:01 |
186 | 38 | INDEX RANGE SCAN | WORKFLOWCENTERSETTINGDETAIL_I | 1 | 19 | 2 (0)| 00:00:01 |
187 | 39 | BUFFER SORT | | 1 | 19 | 1 (0)| 00:00:01 |
188 | 40 | INDEX RANGE SCAN | WORKFLOWCENTERSETTINGDETAIL_I | 1 | 19 | 1 (0)| 00:00:01 |
189 | 41 | TABLE ACCESS BY INDEX ROWID | WORKFLOW_CURRENTOPERATOR | 1 | 65 | 8 (0)| 00:00:01 |
190 | 42 | INDEX RANGE SCAN | WRKCUOPER_USER_IN2 | 7 | | 2 (0)| 00:00:01 |
191 | 43 | TABLE ACCESS BY INDEX ROWID | WORKFLOW_BASE | 1 | 6 | 1 (0)| 00:00:01 |
192 | 44 | INDEX RANGE SCAN | IX_WFBASE_ID | 1 | | 0 (0)| |
193 | 45 | INDEX UNIQUE SCAN | WORKFLOW_REQUESTID | 1 | | 0 (0)| |
194 | 46 | TABLE ACCESS BY INDEX ROWID | WORKFLOW_REQUESTBASE | 1 | 79 | 1 (0)| 00:00:01 |
195 | 47 | INDEX RANGE SCAN | WORKFLOWCENTERSETTINGDETAIL_I | 1 | 19 | 2 (0)| 00:00:01 |
196 | 48 | NESTED LOOPS | | 1 | 35 | 3 (0)| 00:00:01 |
197 | 49 | MERGE JOIN CARTESIAN | | 1 | 26 | 2 (0)| 00:00:01 |
198 | 50 | INDEX RANGE SCAN | IX_WORKFLOW_NODEBASE_IDIS | 1 | 7 | 1 (0)| 00:00:01 |
199 | 51 | BUFFER SORT | | 1 | 19 | 1 (0)| 00:00:01 |
200 | 52 | INDEX RANGE SCAN | WORKFLOWCENTERSETTINGDETAIL_I | 1 | 19 | 1 (0)| 00:00:01 |
201 | 53 | INDEX RANGE SCAN | IX_WKFFLND_NW | 1 | 9 | 1 (0)| 00:00:01 |
202 ---------------------------------------------------------------------------------------------------------------------------
203 270 rows selected.

可以看出执行计划非常糟糕，这次不手工调优，尝试使用advisor工具（基于sql_id）
可以用sql_id创建sql tunning任务，比用sql_text方便很多

 1 FUNCTION CREATE_TUNING_TASK RETURNS VARCHAR2
 2 Argument Name Type In/Out Default?
 3 ------------------------------ ----------------------- ------ --------
 4 SQL_ID VARCHAR2 IN
 5 PLAN_HASH_VALUE NUMBER IN DEFAULT
 6 SCOPE VARCHAR2 IN DEFAULT
 7 TIME_LIMIT NUMBER IN DEFAULT
 8 TASK_NAME VARCHAR2 IN DEFAULT
 9 DESCRIPTION VARCHAR2 IN DEFAULT
10 ————————————————
11 
12 DECLARE
13 my_task_name VARCHAR2(30);
14 BEGIN
15 my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
16 SQL_ID => 'xxxx',
17 scope => 'COMPREHENSIVE',
18 time_limit => 60,
19 task_name => 'tunning_task_xxxx', 
20 description => 'Task to tune a query on xxxx');
21 END;
22 /
23 
24 我们查看此时任务的状态
25 > select task_name,EXECUTION_START,EXECUTION_END,STATUS from DBA_ADVISOR_LOG where task_name like 'tunning_task_xxxx%';
26 
27 TASK_NAME EXECUTION_START EXECUTION_END STATUS
28 ------------------------------ ------------------- ------------------- -----------
29 tunning_task_xxxx INITIAL
30 
31 执行sql tuning任务
32 BEGIN
33 DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'tunning_task_xxxx' );
34 END;
35 /
36 
37 展示sql tunning结果
38 SET LONG 10000
39 SET LONGCHUNKSIZE 1000
40 SET LINESIZE 100
41 SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('tunning_task_xxxx')
42 FROM DUAL;

调优后

  1 SQL> SET LONG 10000
  2 SQL> SET LONGCHUNKSIZE 1000
  3 SQL> SET LINESIZE 100
  4 SQL> SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('tunning_task_ddw7j6yfnw0vz')
  5 2 FROM DUAL;
  6 
  7 DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNNING_TASK_DDW7J6YFNW0VZ')
  8 ----------------------------------------------------------------------------------------------------
  9 GENERAL INFORMATION SECTION
 10 -------------------------------------------------------------------------------
 11 Tuning Task Name : tunning_task_ddw7j6yfnw0vz
 12 Tuning Task Owner : USERNAME
 13 Workload Type : Single SQL Statement
 14 Scope : COMPREHENSIVE
 15 Time Limit(seconds): 60
 16 Completion Status : COMPLETED
 17 Started at : 10/24/2019 16:54:27
 18 Completed at : 10/24/2019 16:54:33
 19 
 20 -------------------------------------------------------------------------------
 21 Schema Name: USERNAME
 22 SQL ID : a6su7qgyywvn8
 23 SQL Text :
 24 
 25 -------------------------------------------------------------------------------
 26 FINDINGS SECTION (3 findings)
 27 -------------------------------------------------------------------------------
 28 
 29 1- SQL Profile Finding (see explain plans section below)
 30 --------------------------------------------------------
 31 A potentially better execution plan was found for this statement.
 32 
 33 Recommendation (estimated benefit: 27.05%)
 34 ------------------------------------------
 35 - Consider accepting the recommended SQL profile.
 36 execute dbms_sqltune.accept_sql_profile(task_name =>
 37 'tunning_task_ddw7j6yfnw0vz', task_owner => 'USERNAME', replace =>
 38 TRUE);
 39 
 40 Validation results
 41 ------------------
 42 The SQL profile was tested by executing both its plan and the original plan
 43 and measuring their respective execution statistics. A plan may have been
 44 only partially executed if the other could be run to completion in less time.
 45 
 46 Original Plan With SQL Profile % Improved
 47 ------------- ---------------- ----------
 48 Completion Status: COMPLETE COMPLETE
 49 Elapsed Time(us): 61144 61755 -.99 %
 50 CPU Time(us): 21282 20590 3.25 %
 51 User I/O Time(us): 0 0
 52 Buffer Gets: 3062 2233 27.07 %
 53 Physical Read Requests: 0 0
 54 Physical Write Requests: 0 0
 55 Physical Read Bytes: 0 0
 56 Physical Write Bytes: 0 0
 57 Rows Processed: 5 5
 58 Fetches: 5 5
 59 Executions: 1 1
 60 
 61 Notes
 62 -----
 63 1. The original plan was first executed to warm the buffer cache.
 64 2. Statistics for original plan were averaged over next 9 executions.
 65 3. The SQL profile plan was first executed to warm the buffer cache.
 66 4. Statistics for the SQL profile plan were averaged over next 9 executions.
 67 
 68 2- Index Finding (see explain plans section below)
 69 --------------------------------------------------
 70 The execution plan of this statement can be improved by creating one or more
 71 indices.
 72 
 73 Recommendation (estimated benefit: 97.87%)
 74 ------------------------------------------
 75 - Consider running the Access Advisor to improve the physical schema design
 76 or creating the recommended index.
 77 create index USERNAME.IDX$$_6F3D0001 on
 78 USERNAME.WORKFLOWCENTERSETTINGDETAIL("EID","TYPE","TABID");
 79 
 80 - Consider running the Access Advisor to improve the physical schema design
 81 or creating the recommended index.
 82 create index USERNAME.IDX$$_6F3D0002 on
 83 USERNAME.WORKFLOW_CURRENTOPERATOR("WORKFLOWID");
 84 
 85 Rationale
 86 ---------
 87 Creating the recommended indices significantly improves the execution plan
 88 of this statement. However, it might be preferable to run "Access Advisor"
 89 using a representative SQL workload as opposed to a single statement. This
 90 will allow to get comprehensive index recommendations which takes into
 91 account index maintenance overhead and additional space consumption.
 92 
 93 3- Alternative Plan Finding
 94 ---------------------------
 95 Some alternative execution plans for this statement were found by searching
 96 the system's real-time and historical performance data.
 97 
 98 The following table lists these plans ranked by their average elapsed time.
 99 See section "ALTERNATIVE PLANS SECTION" for detailed information on each
100 plan.
101 
102 id plan hash last seen elapsed (s) origin note
103 -- ---------- -------------------- ------------ --------------- ----------------
104 1 1019409405 2019-10-24/14:22:29 0.935 Cursor Cache
105 2 1147966846 2019-10-24/14:14:00 49.312 Cursor Cache
106 3 1061600654 2019-10-24/14:22:32 7432.859 Cursor Cache
107 
108 Information
109 -----------
110 - Because no execution history for the Original Plan was found, the SQL
111 Tuning Advisor could not determine if any of these execution plans are
112 superior to it. However, if you know that one alternative plan is better
113 than the Original Plan, you can create a SQL plan baseline for it. This
114 will instruct the Oracle optimizer to pick it over any other choices in
115 the future.
116 execute dbms_sqltune.create_sql_plan_baseline(task_name =>
117 'tunning_task_ddw7j6yfnw0vz', owner_name => 'USERNAME',
118 plan_hash_value => xxxxxxxx);
119 
120 -------------------------------------------------------------------------------
121 ADDITIONAL INFORMATION SECTION
122 -------------------------------------------------------------------------------
123 - The optimizer could not merge the view at line ID 6 of the execution plan.
124 The optimizer cannot merge a complex view if its parent query contains a
125 reference to a "ROWNUM" pseudo-column.
126 - The optimizer could not merge the view at line ID 3 of the execution plan.
127 The optimizer cannot merge a view that contains a "ROWNUM" pseudo column.
128 - The optimizer could not merge the view at line ID 1 of the execution plan.
129 The optimizer cannot merge a view that contains a "ROWNUM" pseudo column.
130 - SQL Profile "SYS_SQLPROF_016dfcf4b7e90000" exists for this statement and
131 was ignored during the tuning process.
132 
133 -------------------------------------------------------------------------------
134 EXPLAIN PLANS SECTION
135 -------------------------------------------------------------------------------
136 
137 1- Original With Adjusted Cost
138 ------------------------------
139 Plan hash value: 3347560359
140 
141 ----------------------------------------------------------------------------------------------------
142 -------------------------------
143 | Id | Operation | Name | Rows | Bytes |T
144 empSpc| Cost (%CPU)| Time |
145 ----------------------------------------------------------------------------------------------------
146 -------------------------------
147 | 0 | SELECT STATEMENT | | 1 | 396 |
148 | 94499 (1)| 00:18:54 |
149 |* 1 | VIEW | | 1 | 396 |
150 | 94499 (1)| 00:18:54 |
151 | 2 | COUNT | | | |
152 | | |
153 | 3 | VIEW | | 1 | 383 |
154 | 94499 (1)| 00:18:54 |
155 | 4 | SORT ORDER BY | | 1 | 370 |
156 | 94499 (1)| 00:18:54 |
157 | 5 | COUNT | | | |
158 | | |
159 | 6 | VIEW | | 1 | 370 |
160 | 94632 (1)| 00:18:56 |
161 | 7 | HASH UNIQUE | | 1 | 182 |
162 | 94632 (1)| 00:18:56 |
163 | 8 | CONCATENATION | | | |
164 | | |
165 |* 9 | FILTER | | | |
166 | | |
167 | 10 | NESTED LOOPS | | 37216 | 6614K|

根据advisor的建议调整
查看DB服务器负载已经正常，调优成功

posted on 2020-06-18 09:25 数据与人文阅读(259) 评论(0) 收藏举报

刷新页面返回顶部

数据与人文

advisor调优工具优化sql(基于sql_id)

导航

公告