从75到6000
Posted: April 24, 2013 | Author: Cui Hua | Filed under: Oracle | 21 Comments »这篇文章的标题颇有些标题党的味道,但这不是我的初衷。
其实我想表达的是,基于RDBMS的应用系统的性能问题绝大多数都是和SQL直接相关,而基于Oracle数据库的SQL优化能否奏效、效果的好与坏归根结底比拼的还是对CBO和执行计划的理解程度,这也正是我在我的新书《基于Oracle的SQL优化》中提出来的SQL优化方法论的第一点——Oracle里SQL优化的本质是基于对CBO和执行计划的深刻理解。
整个事情是这样的……
快下班的时候去打卡,兄弟部门的同事拦住了我,说他们有一个测试系统,性能一直上不去,现在每分钟仅能跑75个Job,想让我帮忙看一下。
简单的用TOP和VMSTAT看了一下,发现在他们并发跑Job的时候,CPU占用率接近100%,IDLE值接近零,看上去瓶颈像是在CPU上。
我让他们将事发时间段的AWR报告发给我,然后我就回家了。
第二天早上来到公司,我打开了这份AWR报告。
如下是这份AWR报告的部分内容:
DB Name |
DB Id |
Instance |
Inst num |
Startup Time |
Release |
RAC |
GALT |
3872212397 |
galt |
1 |
17-Apr-13 20:04 |
11.2.0.1.0 |
NO |
Host Name |
Platform |
CPUs |
Cores |
Sockets |
Memory (GB) |
riserver2 |
Linux x86 64-bit |
4 |
4 |
1 |
15.50 |
|
Snap Id |
Snap Time |
Sessions |
Cursors/Session |
Begin Snap: |
277 |
22-Apr-13 18:00:45 |
40 |
1.8 |
End Snap: |
278 |
22-Apr-13 19:00:58 |
91 |
1.9 |
Elapsed: |
|
60.22 (mins) |
|
|
DB Time: |
|
1,413.75 (mins) |
|
|
Top 5 Timed Foreground Events
Event |
Waits |
Time(s) |
Avg wait (ms) |
% DB time |
Wait Class |
latch: cache buffers chains |
894,797 |
22,111 |
25 |
26.07 |
Concurrency |
DB CPU |
|
11,130 |
|
13.12 |
|
direct path write temp |
53,039 |
1,877 |
35 |
2.21 |
User I/O |
db file sequential read |
14,467 |
145 |
10 |
0.17 |
User I/O |
latch free |
6,208 |
141 |
23 |
0.17 |
Other |
从上述显示内容中我们可以看出,上述数据库在上述采样时间段是非常繁忙的,Cache Buffers Chains有894797次等待,平均等待时间25毫秒,占了26.07%的DB time。
Cache Buffers Chains等待通常说明存在热点块,这可能是由于Oracle自身的Bug(例如“Bug 10396041 – ‘cache buffers chains’ latch contention [ID 10396041.8]”),当然更多的情形是由于应用的某些SQL不恰当的多读了一些本不应该它们读的数据块。所以通常情况下缓解Cache Buffers Chains的方法就是调整SQL,缩减目标SQL所需要读的数据块的数量,这样Cache Buffers Chains等待的数量自然就下去了。
从上述AWR报告中SQL ordered by Gets部分我们可以很轻易的定位出待调整的目标SQL为SQL ID为“73qyztauub2a1”和“74hnt3vjjm85a”的这两条SQL:
SQL ID为“73qyztauub2a1”的SQL文本为如下所示:
SELECT s.LOCATOR_ID, s.segment_ID, n.en_name
FROM ANA_SEG_CLASS S, ana_segment a, ana_name n
where A.SEGMENT_ID = S.SEGMENT_ID
and s.locator_id = n.locator_id
and n.en_name = :1
and ((a.orgn_city = :2 and a.start_time > :3 and a.start_time < :4) or
(a.dstn_city = :5 and a.end_time > :6 and a.end_time < :7))
and n.person_id_count > 0
从同样采样时间段的AWR SQL Report中我们可以看出SQL ID为“73qyztauub2a1”的目标SQL的资源消耗和执行计划为如下所示:
Stat Name |
Statement Total |
Per Execution |
% Snap Total |
Elapsed Time (ms) |
80,758,772 |
1,009,484.65 |
95.21 |
CPU Time (ms) |
10,583,843 |
132,298.04 |
95.09 |
Executions |
80 |
|
|
Buffer Gets |
2,032,606,807 |
25,407,585.09 |
93.88 |
Disk Reads |
419,668 |
5,245.85 |
61.45 |
Parse Calls |
80 |
1.00 |
1.01 |
Rows |
8 |
0.10 |
|
User I/O Wait Time (ms) |
1,729,574 |
|
|
Cluster Wait Time (ms) |
0 |
|
|
Application Wait Time (ms) |
0 |
|
|
Concurrency Wait Time (ms) |
0 |
|
|
Invalidations |
0 |
|
|
Version Count |
15 |
|
|
Sharable Mem(KB) |
482 |
|
|
从上述显示内容中我们可以看出,SQL ID为“73qyztauub2a1”的目标SQL在采样时间段每次执行平均只返回0.1行记录,但平均每次执行需要耗时1009秒,平均每次执行所消耗的逻辑读为25407585。
从该SQL的执行计划我们可以看出,CBO评估出来执行该SQL所对应的成本值为2,这是一个明显异常的值,假如成本值真的像CBO评估的那样为2的话,那么该SQL就不可能平均每次执行需要消耗超过2500万的逻辑读。
很明显,这里很可能是上述SQL所涉及到的对象的统计信息出现了问题,我们现在来检查一下:
表ANA_NAME的统计信息为如下所示:
从上述显示内容中我们可以看出,表ANA_NAME的相关统计信息值全部为0,但实际上表ANA_NAME是一个有300多万数据量的大表:
SQL> select count(*) from ana_name;
COUNT(*)
———-
3180474
所以很明显这里是由于表ANA_NAME的统计信息严重不准而导致CBO选错了执行计划。分析清楚了问题的原因,解决起来就很简单了,我们只需要重新对上述SQL所涉及到的表用如下命令重新收集一下统计信息就好了:
SQL> exec dbms_stats.gather_table_stats(ownname => ‘GALT’,tabname => ‘ANA_NAME’,cascade => true,no_invalidate => false);
PL/SQL procedure successfully completed
SQL> exec dbms_stats.gather_table_stats(ownname => ‘GALT’,tabname => ‘ANA_SEGMENT’,cascade => true,no_invalidate => false);
PL/SQL procedure successfully completed
SQL> exec dbms_stats.gather_table_stats(ownname => ‘GALT’,tabname => ‘ANA_SEG_CLASS’,cascade => true,no_invalidate => false);
PL/SQL procedure successfully completed
重新收集完统计信息后表ANA_NAME的相关统计信息已经有了值:
同时,上述SQL ID为“73qyztauub2a1”的目标SQL的执行计划也已经恢复了正常:
SQL> explain plan for SELECT s.LOCATOR_ID, s.segment_ID, n.en_name FROM ANA_SEG_CLASS S, ana_segment a , ana_name n where A.SEGMENT_ID = S.SEGMENT_ID and s.locator_id = n.locator_id and n.en_name = :1 and ( ( a.orgn_city = :2 and a.start_time > :3 and a.start_time < :4 ) or ( a.dstn_city=:5 and a.end_time> :6 and a.end_time < :7 ) ) and n.person_id_count > 0;
Explained
SQL> col plan_table_output for a200
SQL> select * from table(dbms_xplan.display);
是不是这就完事了?——事情没有这么简单。
表ANA_NAME的那300多万的数据早就存在了,并不是今天早上才导入的,为什么经过了昨晚的自动收集统计信息作业,其各项统计信息的值还是为0?
是不是因为这个库的自动统计信息收集作业被禁掉了?
我们来检查一下:
SQL> select client_name,status from dba_autotask_task;
CLIENT_NAME STATUS
—————————————————————- ——–
sql tuning advisor ENABLED
auto optimizer stats collection ENABLED
auto space advisor ENABLED
SQL> select window_name,autotask_status from dba_autotask_window_clients;
WINDOW_NAME AUTOTASK_STATUS
—————————— —————
MONDAY_WINDOW ENABLED
TUESDAY_WINDOW ENABLED
WEDNESDAY_WINDOW ENABLED
THURSDAY_WINDOW ENABLED
FRIDAY_WINDOW ENABLED
SATURDAY_WINDOW ENABLED
SUNDAY_WINDOW ENABLED
7 rows selected
SQL> select window_name,repeat_interval from dba_scheduler_windows where enabled=’TRUE’;
WINDOW_NAME REPEAT_INTERVAL
—————- ——————————————————
MONDAY_WINDOW freq=daily;byday=MON;byhour=22;byminute=0; bysecond=0
TUESDAY_WINDOW freq=daily;byday=TUE;byhour=22;byminute=0; bysecond=0
WEDNESDAY_WINDOW freq=daily;byday=WED;byhour=22;byminute=0; bysecond=0
THURSDAY_WINDOW freq=daily;byday=THU;byhour=22;byminute=0; bysecond=0
FRIDAY_WINDOW freq=daily;byday=FRI;byhour=22;byminute=0; bysecond=0
SATURDAY_WINDOW freq=daily;byday=SAT;byhour=6;byminute=0; bysecond=0
SUNDAY_WINDOW freq=daily;byday=SUN;byhour=6;byminute=0; bysecond=0
7 rows selected
SQL> select window_name, job_name, job_start_time from (select * from dba_autotask_job_history where client_name=’auto optimizer stats collection’ order by window_start_time desc) where rownum<4;
WINDOW_NAME JOB_NAME JOB_START_TIME
————— ——————— ————————————
TUESDAY_WINDOW ORA$AT_OS_OPT_SY_1 09-4月 -13 10.00.00.825901 下午 PRC
SQL> select window_name,window_next_time from dba_autotask_window_clients;
WINDOW_NAME WINDOW_NEXT_TIME
—————————— ————————————-
MONDAY_WINDOW 15-4月 -13 10.00.00.000000 下午 PRC
TUESDAY_WINDOW 16-4月 -13 10.00.00.000000 下午 PRC
WEDNESDAY_WINDOW 10-4月 -13 10.00.00.000000 下午 PRC
THURSDAY_WINDOW 11-4月 -13 10.00.00.000000 下午 PRC
FRIDAY_WINDOW 12-4月 -13 10.00.00.000000 下午 PRC
SATURDAY_WINDOW 13-4月 -13 06.00.00.000000 上午 PRC
SUNDAY_WINDOW 14-4月 -13 06.00.00.000000 上午 PRC
7 rows selected
从上述显示内容中我们可以看出,上述库的自动统计信息收集作业并没有被禁掉,只是出于某种原因,自4月9日晚上10点最后一次运行完后就再没有被执行过。
Oracle 11g里的自动统计信息收集作业每次运行时会先生成名为ORA$AT_OS_OPT_XXX的Job,然后再执行这个Job。所以有一种可能就是虽然自动统计信息收集作业没有被禁掉,但Oracle在生成Job的时候碰到了问题,比如参数job_queue_processes的值被设成了0。
从如下查询结果中我们可以看到,上述库的job_queue_processes的值果然是0:
SQL> show parameter job;
NAME TYPE VALUE
———————————— ———– ——————————
job_queue_processes integer 0
从上述库的alert log里我们看到了如下内容:
Wed Apr 10 14:09:50 2013
ALTER SYSTEM SET job_queue_processes=0 SCOPE=BOTH;
Wed Apr 10 14:10:26 2013
ALTER SYSTEM SET aq_tm_processes=0 SCOPE=BOTH;
ALTER DATABASE OPEN
Wed Apr 10 14:10:35 2013
这说明上述库的参数job_queue_processes和aq_tm_processes在4月10日下午2点多的时候被修改成了0,难怪上述库的自动统计信息收集作业自4月9日晚上10点最后一次运行完后就再没有被成功执行过。
我们将上述两个参数的值给改回来:
SQL> alter system set job_queue_processes = 1000 scope=both;
System altered
SQL> alter system set aq_tm_processes = 1 scope=both;
System altered
SQL> show parameter job_queue_processes;
NAME TYPE VALUE
———————————— ———– ——————————
job_queue_processes integer 1000
SQL> show paramete aq_tm_processes;
NAME TYPE VALUE
———————————— ———– ——————————
aq_tm_processes integer 1
从如下查询结果中我们可以看到,参数job_queue_processes恢复成大于0的值后,上述库的自动统计信息收集作业的各个窗口所对应的WINDOW_NEXT_TIME的值也随之恢复了正常:
SQL> select window_name,window_next_time from dba_autotask_window_clients;
WINDOW_NAME WINDOW_NEXT_TIME
—————————— ————————————
MONDAY_WINDOW 29-4月 -13 10.00.00.000000 下午 PRC
TUESDAY_WINDOW 23-4月 -13 10.00.00.000000 下午 PRC
WEDNESDAY_WINDOW 24-4月 -13 10.00.00.000000 下午 PRC
THURSDAY_WINDOW 25-4月 -13 10.00.00.000000 下午 PRC
FRIDAY_WINDOW 26-4月 -13 10.00.00.000000 下午 PRC
SATURDAY_WINDOW 27-4月 -13 06.00.00.000000 上午 PRC
SUNDAY_WINDOW 28-4月 -13 06.00.00.000000 上午 PRC
7 rows selected
接下来,我们对上述SQL所涉及到的schema重新收集了一下统计信息(以避免再次出现有目标表的统计信息严重不准的情形):
SQL> EXEC DBMS_STATS.GATHER_SCHEMA_STATS(OWNNAME => ‘GALT’,ESTIMATE_PERCENT => DBMS_STATS.AUTO_SAMPLE_SIZE,CASCADE => TRUE,METHOD_OPT => ‘FOR ALL COLUMNS SIZE REPEAT’,no_invalidate => false);
PL/SQL procedure successfully completed
此时第一阶段的调整就结束了。
根据同事的反馈,现在每分钟能跑的Job的数量已经从之前的75上升到了现在的2000,但是这个还不能满足他们的要求,他们希望能在此基础上再提升2-3倍。
刚才我们只调整了SQL ID为“73qyztauub2a1”的目标SQL,现在我们再来调整剩下的那个SQL ID为“74hnt3vjjm85a”的目标SQL。
SQL ID为“74hnt3vjjm85a”的SQL文本为如下所示:
SELECT s.LOCATOR_ID, s.segment_ID, n.en_name
FROM ANA_SEG_CLASS S, ana_segment a, ana_name n
where A.SEGMENT_ID = S.SEGMENT_ID
and s.locator_id = n.locator_id
and n.cn_name = :1
and ((a.orgn_city = :2 and a.start_time > :3 and a.start_time < :4) or
(a.dstn_city = :5 and a.end_time > :6 and a.end_time < :7))
and n.person_id_count > 0
从对应采样时间段的AWR SQL Report中我们可以看出,上述SQL对应有三个Child Cursor:
# |
Plan Hash Value |
Total Elapsed Time(ms) |
Executions |
1st Capture Snap ID |
Last Capture Snap ID |
1 |
2064935335 |
2,663,311 |
1,368 |
295 |
295 |
2 |
105883452 |
327,086 |
506 |
295 |
295 |
3 |
1495819153 |
0 |
0 |
295 |
295 |
这三个Child Cursor,真正被Oracle在采样时间段使用的有两个执行计划(分别是Plan Hash Value为2064935335和105883452所对应的执行计划),这两个执行计划所对应的资源消耗情况和执行计划的具体内容为如下所示:
Plan 1(PHV: 2064935335)
Stat Name |
Statement Total |
Per Execution |
% Snap Total |
Elapsed Time (ms) |
2,663,311 |
1,946.86 |
79.43 |
CPU Time (ms) |
612,884 |
448.01 |
68.63 |
Executions |
1,368 |
|
|
Buffer Gets |
168,610,877 |
123,253.57 |
88.24 |
Disk Reads |
10,557 |
7.72 |
3.88 |
Parse Calls |
1,369 |
1.00 |
0.81 |
Rows |
1,256 |
0.92 |
|
User I/O Wait Time (ms) |
7,637 |
|
|
Cluster Wait Time (ms) |
0 |
|
|
Application Wait Time (ms) |
0 |
|
|
Concurrency Wait Time (ms) |
0 |
|
|
Invalidations |
0 |
|
|
Version Count |
10 |
|
|
Sharable Mem(KB) |
38 |
|
|
Id |
Operation |
Name |
Rows |
Bytes |
Cost (%CPU) |
0 |
SELECT STATEMENT |
|
|
|
6721 (100) |
1 |
NESTED LOOPS |
|
|
|
|
2 |
NESTED LOOPS |
|
20 |
2020 |
6721 (1) |
3 |
NESTED LOOPS |
|
1179 |
83709 |
3247 (1) |
4 |
TABLE ACCESS FULL |
ANA_SEGMENT |
180 |
7920 |
2116 (1) |
5 |
TABLE ACCESS BY INDEX ROWID |
ANA_SEG_CLASS |
7 |
189 |
8 (0) |
6 |
INDEX RANGE SCAN |
IDX_ANA_SEG_CLASS |
7 |
|
2 (0) |
7 |
INDEX RANGE SCAN |
IDX_ANA_NAME_LOCATOR_ID |
2 |
|
2 (0) |
8 |
TABLE ACCESS BY INDEX ROWID |
ANA_NAME |
1 |
30 |
3 (0) |
Plan 2(PHV: 105883452)
Stat Name |
Statement Total |
Per Execution |
% Snap Total |
Elapsed Time (ms) |
327,086 |
646.42 |
9.75 |
CPU Time (ms) |
83,901 |
165.81 |
9.39 |
Executions |
506 |
|
|
Buffer Gets |
9,477,916 |
18,731.06 |
4.96 |
Disk Reads |
2,399 |
4.74 |
0.88 |
Parse Calls |
505 |
1.00 |
0.30 |
Rows |
667 |
1.32 |
|
User I/O Wait Time (ms) |
1,623 |
|
|
Cluster Wait Time (ms) |
0 |
|
|
Application Wait Time (ms) |
0 |
|
|
Concurrency Wait Time (ms) |
0 |
|
|
Invalidations |
0 |
|
|
Version Count |
10 |
|
|
Sharable Mem(KB) |
38 |
|
|
Id |
Operation |
Name |
Rows |
Bytes |
Cost (%CPU) |
0 |
SELECT STATEMENT |
|
|
|
5063 (100) |
1 |
NESTED LOOPS |
|
|
|
|
2 |
NESTED LOOPS |
|
1 |
101 |
5063 (1) |
3 |
NESTED LOOPS |
|
2 |
114 |
5059 (1) |
4 |
TABLE ACCESS FULL |
ANA_NAME |
2 |
60 |
5051 (1) |
5 |
TABLE ACCESS BY INDEX ROWID |
ANA_SEG_CLASS |
1 |
27 |
4 (0) |
6 |
INDEX RANGE SCAN |
IDX_ANA_SEG_CLASS_LOCATOR_ID |
1 |
|
2 (0) |
7 |
INDEX UNIQUE SCAN |
PK_ANA_SEGMENT |
1 |
|
1 (0) |
8 |
TABLE ACCESS BY INDEX ROWID |
ANA_SEGMENT |
1 |
44 |
2 (0) |
显然,上述SQL的这两个执行计划均不是最优解,它们分别对表ANA_SEGMENT和ANA_NAME做了不必要的全表扫描。
注意到上述SQL和之前我们处理的第一条SQL在SQL文本上的区别仅仅是SQL文本中的“and n.en_name = :1”和“and n.cn_name = :1”的不同,其它部分都是一样的。
从表ANA_NAME的统计信息显示结果中我们可以看到,列EN_NAME和CN_NAME的可选择性都非常好,在列EN_NAME上存在一个名为IDX_ANA_NAME的单键值B树索引,但在列CN_NAME上并不存在任何索引:
所以这里我们只需要在列CN_NAME上创建一个单键值的B树索引,就可以让该SQL走出和之前第一个SQL那样一模一样的执行计划:
SQL> create index idx_ana_cname on ana_name(cn_name);
Index created
SQL> explain plan for SELECT s.LOCATOR_ID, s.segment_ID, n.en_name FROM ANA_SEG_CLASS S, ana_segment a , ana_name n where A.SEGMENT_ID = S.SEGMENT_ID and s.locator_id = n.locator_id and n.cn_name = :1 and ( ( a.orgn_city = :2 and a.start_time > :3 and a.start_time < :4 ) or ( a.dstn_city=:5 and a.end_time> :6 and a.end_time < :7 ) ) and n.person_id_count > 0;
Explained
SQL> select * from table(dbms_xplan.display);
从上述显示内容中我们可以看到,我们的目的已经实现了。
是不是这就完事了?——事情没有这么简单。
根据同事的反馈,当我们在列CN_NAME上创建一个单键值的B树索引IDX_ANA_CNAME后,现在每分钟能跑的Job的数量反而从之前的2000下降到了现在的800。
为什么会这样?
此时的AWR SQL Report显示,Oracle此时同时启用了两个执行计划,这两个执行计划所对应的资源消耗情况和执行计划的具体内容为如下所示:
# |
Plan Hash Value |
Total Elapsed Time(ms) |
Executions |
1st Capture Snap ID |
Last Capture Snap ID |
1 |
2268138873 |
5,929,600 |
1,183 |
297 |
297 |
2 |
2110666080 |
1,886 |
25 |
297 |
297 |
3 |
2064935335 |
0 |
0 |
297 |
297 |
4 |
1495819153 |
0 |
0 |
297 |
297 |
Plan 1(PHV: 2268138873)
Stat Name |
Statement Total |
Per Execution |
% Snap Total |
Elapsed Time (ms) |
5,929,600 |
5,012.34 |
64.55 |
CPU Time (ms) |
837,076 |
707.59 |
61.62 |
Executions |
1,183 |
|
|
Buffer Gets |
55,477,410 |
46,895.53 |
53.63 |
Disk Reads |
0 |
0.00 |
0.00 |
Parse Calls |
1,192 |
1.01 |
2.02 |
Rows |
369 |
0.31 |
|
User I/O Wait Time (ms) |
1 |
|
|
Cluster Wait Time (ms) |
0 |
|
|
Application Wait Time (ms) |
0 |
|
|
Concurrency Wait Time (ms) |
0 |
|
|
Invalidations |
0 |
|
|
Version Count |
155 |
|
|
Sharable Mem(KB) |
34 |
|
|
Id |
Operation |
Name |
Rows |
Bytes |
Cost (%CPU) |
Time |
0 |
SELECT STATEMENT |
|
|
|
12726 (100) |
|
1 |
HASH JOIN |
|
156 |
15756 |
12726 (1) |
00:02:33 |
2 |
HASH JOIN |
|
9270 |
642K |
7674 (1) |
00:01:33 |
3 |
TABLE ACCESS FULL |
ANA_SEGMENT |
1413 |
62172 |
2116 (1) |
00:00:26 |
4 |
TABLE ACCESS FULL |
ANA_SEG_CLASS |
2710K |
69M |
5549 (1) |
00:01:07 |
5 |
TABLE ACCESS FULL |
ANA_NAME |
34841 |
1020K |
5051 (1) |
00:01:01 |
Plan 2(PHV: 2110666080)
Stat Name |
Statement Total |
Per Execution |
% Snap Total |
Elapsed Time (ms) |
1,886 |
75.43 |
0.02 |
CPU Time (ms) |
374 |
14.96 |
0.03 |
Executions |
25 |
|
|
Buffer Gets |
64,535 |
2,581.40 |
0.06 |
Disk Reads |
124 |
4.96 |
0.10 |
Parse Calls |
16 |
0.64 |
0.03 |
Rows |
12 |
0.48 |
|
User I/O Wait Time (ms) |
15 |
|
|
Cluster Wait Time (ms) |
0 |
|
|
Application Wait Time (ms) |
0 |
|
|
Concurrency Wait Time (ms) |
0 |
|
|
Invalidations |
0 |
|
|
Version Count |
155 |
|
|
Sharable Mem(KB) |
1,300 |
|
|
Id |
Operation |
Name |
Rows |
Bytes |
Cost (%CPU) |
0 |
SELECT STATEMENT |
|
|
|
15 (100) |
1 |
NESTED LOOPS |
|
|
|
|
2 |
NESTED LOOPS |
|
1 |
101 |
15 (0) |
3 |
NESTED LOOPS |
|
2 |
114 |
11 (0) |
4 |
TABLE ACCESS BY INDEX ROWID |
ANA_NAME |
2 |
60 |
5 (0) |
5 |
INDEX RANGE SCAN |
IDX_ANA_CNAME |
2 |
|
3 (0) |
6 |
TABLE ACCESS BY INDEX ROWID |
ANA_SEG_CLASS |
1 |
27 |
3 (0) |
7 |
INDEX RANGE SCAN |
IDX_ANA_SEG_CLASS_LOCATOR_ID |
1 |
|
2 (0) |
8 |
INDEX UNIQUE SCAN |
PK_ANA_SEGMENT |
1 |
|
1 (0) |
9 |
TABLE ACCESS BY INDEX ROWID |
ANA_SEGMENT |
1 |
44 |
2 (0) |
这里为什么每分钟能跑的Job的数量反而下降的原因是显而易见的——因为对于上述SQL而言,Oracle此时一共执行了1208次,其中只有25次走的是我们想让Oracle走的理想执行计划;剩下的1183次Oracle选择的是走哈希连接,分别对表ANA_SEGMENT、ANA_SEG_CLASS和ANA_NAME做了全表扫描,这样单次的平均执行时间一下子递增为5.012秒,所以相当于是执行时间更长、更慢了,于是并发执行的Job数量就降了下来。
现在的关键问题是——上述SQL明明使用了绑定变量且绑定变量窥探在默认情况下已经被开启,那为什么这里Oracle还会同时启用两个执行计划?
我们再来看一下表ANA_NAME的统计信息:
注意到此时列CN_NAME是有直方图统计信息的(显然是Height Balanced类型的直方图),再结合这个数据库的版本(Oracle 11.2.0.1),我们之前的疑惑就有了答案。
在Oracle 11g之前,绑定变量窥探的副作用就在于使用了绑定变量的目标SQL就只会沿用之前硬解析时所产生的解析树和执行计划,即使这种沿用完全不适合于当前的情形。
在Oracle 10g及其后续的版本中,Oracle会自动收集直方图统计信息,这意味着和之前的版本相比,在Oracle 10g及其后续的版本中Oracle有更大的概率会知道目标列实际数据的分布情况,这意味着绑定变量窥探的副作用将会更加明显。例如,如果表T1的列COL1没有直方图统计信息,同时在列COL1上又存在一个单键值的B树索引IDX_T1,则不管列COL1的实际数据分布情况是否均衡,Oracle在执行目标SQL“select count(*) from t1 where col1 = 😡”时都会走固定的执行计划(很可能是走对索引IDX_T1的索引范围扫描),这个时候绑定变量窥探实际上是没有副作用的;但假如列COL1上有了直方图统计信息,同时列COL1的实际数据分布又是极度不均衡的,那么这个时候当Oracle执行绑定变量窥探操作时绑定变量x所对应的输入值是否具有代表性就至关重要了(这里“代表性”的含义是指该输入值所对应的执行计划和该SQL在大多数情况下的执行计划相同),因为这会直接决定上述SQL在硬解析时所选择的执行计划,进而会决定后续以软解析/软软解析重复执行时所沿用的执行计划。为什么会发生这样的变化是因为一旦列COL1上有了直方图统计信息,Oracle就知道了列COL1的实际数据分布情况,这样在列COL1的实际数据分布又是极度不均衡的前提条件下,Oracle就能随着上述SQL中对列COL1的输入值的不同而调整上述SQL的执行计划。这种现象的本质是因为当列COL1没有直方图统计信息时,CBO在计算上述谓词条件的可选择率时会根据列COL1的distinct值的数量来计算,这意味着这种情况下该谓词条件的可选择率是固定的;而一旦列COL1上有了直方图统计信息,CBO就会根据列COL1的直方图统计信息来计算上述谓词条件的可选择率,这就决定了该谓词条件的可选择率是不固定的,并且可能会随着对列COL1的输入值的变化而变化,而这正好给了绑定变量窥探发挥其副作用的机会!
为了解决上述绑定变量窥探的副作用,Oracle在11g中引入了自适应游标共享(Adaptive Cursor Sharing)。
自适应游标共享可以让使用了绑定变量的目标SQL在启用了绑定变量窥探的前提条件下不再只沿用之前硬解析时所产生的解析树和执行计划,也就是说自适应游标共享可以在启用了绑定变量窥探的前提条件下让目标SQL在其可能的多个执行计划之间“自适应”的做出选择,而不再像之前那样必须得僵化的沿用该SQL硬解析时所产生的解析树和执行计划。
所以这里为什么Oracle在执行上述含绑定变量且开启了绑定变量窥探的目标SQL时依然会同时启用两个执行计划的原因就是因为自适应游标共享的副作用,而这里自适应游标之所以会被Oracle启用的本质原因又是因为上述SQL的等值查询条件“and n.cn_name = :1”中的列cn_name上有直方图统计信息(关于绑定变量窥探、自适应游标共享和直方图统计信息对可选择率的影响,我会在我的新书《基于Oracle的SQL优化》中详细描述,这里不再赘述)。
分析清楚了根本原因,解决方法就非常简单了,我们来只需要删除直方图统计信息,不给自适应游标共享发挥副作用的机会就好了。
无论是用Oracle自带的自动统计信息收集作业还是用我们自己写的shell脚本来收集统计信息,对于直方图统计信息的收集而言,我们都建议采用如下的方式:只对已经存在直方图统计信息的列重复收集直方图统计信息,而目标列的初次直方图统计信息的收集则是由了解系统的相关DBA手工来做。具体来说就是这样:
1、 设置METHOD_OPT的值为‘FOR ALL COLUMNS SIZE 1’后先收集一次统计信息,这意味着删除了所有列上的直方图;
2、 在已经删除了所有列上的直方图后,设置METHOD_OPT的值为‘FOR ALL COLUMNS SIZE REPEAT’,这意味着今后将只对已经存在直方图统计信息的列重复收集直方图统计信息;
3、 在系统使用或调优的过程中,目标列的初次直方图统计信息的收集是由了解系统的相关DBA手工来做。
按照上述原则,我们重新来处理一下上述对象所涉及到的直方图统计信息:
SQL> EXEC DBMS_STATS.GATHER_SCHEMA_STATS(OWNNAME => ‘GALT’,ESTIMATE_PERCENT => DBMS_STATS.AUTO_SAMPLE_SIZE,CASCADE => TRUE,METHOD_OPT => ‘FOR ALL COLUMNS SIZE 1’,no_invalidate => false);
PL/SQL procedure successfully completed
SQL> select dbms_stats.get_prefs(pname => ‘METHOD_OPT’) from dual;
DBMS_STATS.GET_PREFS(PNAME=>’M
——————————————————————————–
FOR ALL COLUMNS SIZE AUTO
SQL> exec dbms_stats.set_global_prefs(‘METHOD_OPT’,’FOR ALL COLUMNS SIZE REPEAT’);
PL/SQL procedure successfully completed
SQL> select dbms_stats.get_prefs(pname => ‘METHOD_OPT’) from dual;
DBMS_STATS.GET_PREFS(PNAME=>’M
——————————————————————————–
FOR ALL COLUMNS SIZE REPEAT
至此,我们就完成了第二阶段的调整。
这个做完没多久,就收到了同事的邮件:
崔老师,这次能每分钟完成近6000个任务了,基本上满足了我们业务的基本需求,太感激了。。。
一会您回来传授下经验!
太感谢了!
从随后产生的AWR SQL Report中我们可以看到,当我们删除直方图统计信息后,在随后的执行过程中,对于上述SQL而言,Oracle确实只启用了一个执行计划,且启用的执行计划就是我们想要的:
# |
Plan Hash Value |
Total Elapsed Time(ms) |
Executions |
1st Capture Snap ID |
Last Capture Snap ID |
1 |
2110666080 |
757,107 |
10,169 |
298 |
298 |
2 |
2064935335 |
0 |
0 |
298 |
298 |
3 |
1495819153 |
0 |
0 |
298 |
298 |
Plan 1(PHV: 2110666080)
Stat Name |
Statement Total |
Per Execution |
% Snap Total |
Elapsed Time (ms) |
757,107 |
74.45 |
41.72 |
CPU Time (ms) |
220,902 |
21.72 |
28.79 |
Executions |
10,169 |
|
|
Buffer Gets |
31,392,043 |
3,087.03 |
41.77 |
Disk Reads |
4,166 |
0.41 |
1.38 |
Parse Calls |
10,169 |
1.00 |
2.05 |
Rows |
5,733 |
0.56 |
|
User I/O Wait Time (ms) |
109 |
|
|
Cluster Wait Time (ms) |
0 |
|
|
Application Wait Time (ms) |
0 |
|
|
Concurrency Wait Time (ms) |
0 |
|
|
Invalidations |
0 |
|
|
Version Count |
155 |
|
|
Sharable Mem(KB) |
2,597 |
|
|
Execution Plan
Id |
Operation |
Name |
Rows |
Bytes |
Cost (%CPU) |
0 |
SELECT STATEMENT |
|
|
|
15 (100) |
1 |
NESTED LOOPS |
|
|
|
|
2 |
NESTED LOOPS |
|
1 |
101 |
15 (0) |
3 |
NESTED LOOPS |
|
2 |
114 |
11 (0) |
4 |
TABLE ACCESS BY INDEX ROWID |
ANA_NAME |
2 |
60 |
5 (0) |
5 |
INDEX RANGE SCAN |
IDX_ANA_CNAME |
2 |
|
3 (0) |
6 |
TABLE ACCESS BY INDEX ROWID |
ANA_SEG_CLASS |
1 |
27 |
3 (0) |
7 |
INDEX RANGE SCAN |
IDX_ANA_SEG_CLASS_LOCATOR_ID |
1 |
|
2 (0) |
8 |
INDEX UNIQUE SCAN |
PK_ANA_SEGMENT |
1 |
|
1 (0) |
9 |
TABLE ACCESS BY INDEX ROWID |
ANA_SEGMENT |
1 |
44 |
2 (0) |
并且此时整个数据库的Cache Buffers Chains的等待次数已经从最开始的894797次降到了现在的11488次:
Top 5 Timed Foreground Events
Event |
Waits |
Time(s) |
Avg wait (ms) |
% DB time |
Wait Class |
DB CPU |
|
767 |
|
42.29 |
|
latch: cache buffers chains |
11,488 |
366 |
32 |
20.19 |
Concurrency |
db file scattered read |
23,949 |
7 |
0 |
0.37 |
User I/O |
db file sequential read |
66,466 |
7 |
0 |
0.36 |
User I/O |
log file sync |
328 |
2 |
7 |
0.13 |
Commit |
至此,上述测试系统的性能问题就被我们成功解决了。
从整个解决过程我们可以看出,虽然最后用的解决方法很简单,但整个解决过程实际上倚赖的还是我们对CBO(包括统计信息、自动收集统计信息作业、直方图统计信息、绑定变量窥探、自适应游标共享等知识点)和执行计划的深刻理解。
可以把“表ANA_NAME的统计信息”的脚本共享一下么?
这里查看统计信息的脚本为sosi.txt,sosi.txt可以通过我网站上的Books专栏下载,网址为http://www.dbsnake.net/books
佩服。目标。
能不能不删histogram用sql plan management?
可以,SPM(Sql Plan Management)和ACS(Adaptive Cursor Sharing)可以共存
老师的书什么时候出版,期待了好久。每次活动上听课,都是激动啊。买回来了,必须拜读好几遍。
出版社说7月底就能出来了,谢谢支持!
上面同一条语句有两个统计数据和执行计划的表格,这个是从哪里获得的?
AWR SQL Report
太激动了,看完整个文章心里有一种莫名的佩服!偶像也!
您好!既然11g使用了自适应游标,为什么大多数sql的执行计划还是错误(不当)的?这样的话,使用了自适应游标弊大于利了?
自适应游标会有副作用,但不是弊大于利。我会在我的新书《基于Oracle的SQL优化》中详细描述自适应游标共享。
你们实际环境上有尝试使用SPM和ACS的吗?像ACS,我们直接就从系统层面给禁用掉了。
有。如果碰到问题,可以再调,当然,一开始禁掉ACS也是一种选择,10g里没有ACS不一样跑吗
你的新书有没有签售活动啊?给我留一本签名的呗。
好,没问题。
大师文中提到“例如,如果表T1的列COL1没有直方图统计信息,同时在列COL1上又存在一个单键值的B树索引IDX_T1,则不管列COL1的实际数据分布情况是否均衡,Oracle在执行目标SQL“select count(*) from t1 where col1 = ”时都会走固定的执行计划(很可能是走对索引IDX_T1的索引范围扫描),这个时候绑定变量窥探实际上是没有副作用的”
Q1: 为什么“这个时候绑定变量窥视实际上是没有副作用的”?
Q2: 绑定变量窥视与直方图的存在与否有什么关系呢?
Q1: 为什么“这个时候绑定变量窥视实际上是没有副作用的”?
Answer:因为如果没有直方图统计信息,则“select count(*) from t1 where col1 = 😡 ”的where条件“col1 = :x”的可选择率就是固定的,不管对绑定变量“:x”的实际输入值是多少。
Q2:绑定变量窥视与直方图的存在与否有什么关系呢?
Answer:是否做绑定变量窥探跟直方图是否存在没有关系,但直方图的存在是使绑定变量窥探产生副作用的一种场景(并不是唯一的场景)下的必要条件。
崔大,你好。请教个问题:
数据库版本为10.2.0.4。数据库中对应的一条已经绑定了变量的SQL语句跑出的AWR SQL Report竟然有两种执行计划。性能好的时候,走的是Plan 2的执行计划。性能差的时候,走的是Plan 1的执行计划。走Plan 1的执行计划的时候就会产生17万的物理读,以至于产生大量的read by other session的等待事件。这是什么情况?为什么已经做了绑定变量的SQL会走两种执行计划呢?具体情况稍好email给你。
关于崔大师这篇文章,我的想法:
删除直方图,得到的执行计划可能适应大部分的SQL,
但对特例则没解决方法,11G的自适应共享游标恰是
根据直方图解决这一问题的关键,删除了虽然没
副作用,但也失去了解决特例的机会.
我觉得,关键还是找出为何效率不佳的执行计划,而不
仅仅是简单地删除直方图.
我并不反对采集直方图,执行计划不佳,那是CBO的问题,没用好直方图.
> 我觉得,关键还是找出为何效率不佳的执行计划,而不
仅仅是简单地删除直方图.
效率不佳是不是正是说明了CBO的缺陷所在呢?它没有办法在普例和特例之间做出区分,所以需要DBA的介入。而DBA首先需要考虑的是大多数SQL语句的执行情况。