select t.column_name,
t.num_distinct,
t.num_buckets,
t.last_analyzed,
t.HISTOGRAM
from dba_tab_col_statistics t
where table_name = 'TI_O_YWTOCREDIT'
关于histogram总共有两种,一种是HEIGHT BALANCED,另外一种是FREQUENCY,这个是oracle是根据NDV来进行选择
如果有254个以下的非空的不同值,就是FREQUENCY的柱状图
如果有254个以上的非空的不同值,就是HEIGHT BALANCED的柱状图
HEIGHT BALANCED 高度平衡直方图(a.ENDPOINT_NUMBER(代表桶号),a.ENDPOINT_VALUE )
FREQUENCY 频率直方图(a.ENDPOINT_NUMBER(代表桶号),a.ENDPOINT_VALUE )
今天谈谈下面这几个参数对数据库性能和稳定性的影响:
cursor_sharing:游标共享
_optim_peek_user_binds:绑定变量窥视
_optimizer_adaptive_cursor_sharing:自适应游标共享(简称ACS),一般还包括另外两个_optimizer_extended_cursor_sharing和_optimizer_extended_cursor_sharing_rel 参数)
_optim_peek_user_binds和ACS:
在10g,因为没有ACS,一般建议客户关闭绑定变量窥视功能的情况多一些。
在11g,很多客户还是将绑定变量窥视和ACS都关闭了,原因有的是数据库从1g升级而来,升级后没有改,还有就是因为ACS早期版本有一些bug。
其实这是两个很好的参数,可以在代码写的不是太好的情况下,也能获得比较好的性能。虽然ACS可能还有一些小bug没有解决(有的bug是在很特殊的情况下才会触发),
到了11204版本应该都不是大问题了。老虎刘建议还是都开启比较好。
最重要的参数还在下面,如果做到了下面这两点,上面两个参数就显得不是那么重要了:
首先,cursor_sharing这个参数对系统性能和稳定性都非常重要,可惜经常被忽略,建议使用该参数的默认值:
即 cursor_sharing=EXACT (而不是FORCE或similar)
这要求应该使用绑定变量的地方,必须使用绑定变量。这个对于OLTP系统来说是铁律,不容置疑,
cursor_sharing=FORCE通常就是为了解决该使用绑定变量而没有使用绑定变量的情况。
前年在网上看到一个广为流传的某水果公司的AWR报告,居然设置 cursor_sharing= FORCE ,令人感叹啊。
其次,还有一个重要的补充条件:
不该使用绑定变量的地方,不用绑定变量:对那些唯一值较少的字段,特别是数据分布不均的情况,
不建议使用绑定变量。如type、status等字段,我们建议使用常量:where type=1 and status=2。
这种情况如果使用了绑定变量,就是绑定变量窥视和ACS发挥作用的时候。
如果cursor_sharing=FORCE;或者cursor_sharing=EXACT,但是在数据分布不均的字段上也使用了绑定变量(两者基本上是等同的,虽然后一种略好于前一种情况),
那么就要考虑“绑定变量窥视”和“自适应游标”两个参数的影响了。
看下面几种情况:
1、关闭“绑定变量窥视”(默认是开启):
ACS同时失效,这时系统的稳定性好(不会因为绑定变量的不同,发生执行计划改变),
但是整体性能会下降:因为不能窥视绑定变量,只能按照字段是数据分布均匀的情况来计算,
在能否使用索引,返回行源的估值上,都会出现较大的偏差,有时可能会配合使用hint来提高SQL性能。
2、如果开启了“绑定变量窥视”而不开启ACS(默认是开启):
那么系统就会极不稳定:比如硬解析窥视到一个绑定变量适合全表扫描的执行计划,不管接下来的绑定变量是否能使用索引,
都会一直全表扫描下去,直到下次硬解析时再次窥视绑定变量才可能重新生成新的执行计划。
3、如果开启“绑定变量窥视”,同时开启ACS:
这种情况在解决了一部分稳定性的同时,兼顾了性能。也是11g新增的ACS比10g没有ACS进步的地方:
执行计划不再从一而终,而是会根据绑定变量的不同,不是很及时的做出调整:
比如第一次窥视到的绑定变量适合全表扫描,那么第二次即使使用的绑定变量适合走索引,也还是会使用全表扫描的执行计划,
下一次再次执行就会纠正为使用索引的执行计划(具体请参考ACS的实现原理)。
绑定变量窥视和ACS这两个参数是与直方图信息紧密联系在一起的,关闭直方图收集,也就相当于关闭了绑定变量窥视和ACS,即使开启了这两个参数。
直方图能较为准确的反映数据分布不均字段的数据分布情况,一般使用默认选项(auto),
某些特殊情况可以补充或去掉某些字段的直方图信息。一些客户在数据库级关闭收集直方图的做法是不建议的。
总结:
最佳实践:
cursor_sharing=EXACT + 合理使用绑定变量(合理就是:类似ID、account_no等唯一值等于或接近表行数的字段,必须使用绑定变量;
而type、status等唯一值少且数分别不均的字段,不使用绑定变量)。
绑定变量窥视和ACS保持默认开启状态。
特殊情况:
1、字段唯一值有一定的数量(介于少与多之间),比如1000个,如果数据分布均匀,则可以使用绑定变量。
如果字段分布不均,则把占比多的几个值,使用常量,其他值使用绑定变量。
2、字段唯一值少,还有经常互相转变的情况,比如常见的工单处理表:没有处理的状态是0,处理后的状态是1,
夜间统计信息收集后,由于字段值的不稳定,统计信息经常不能反映表的实时数据分布情况,这种情况谈是否使用绑定变量已没有意义,
涉及这类表的SQL,可以关闭字段上的直方图收集,再配合rownum和hint 来提高SQL效率和稳定性,
必要时还可以使用dynamic_sampling(动态采样)来辅助优化器做出正确的执行计划。
最差组合:
cursor_sharing=FORCE
_optim_peek_user_binds=TRUE(开启绑定变量窥视)
_optimizer_adaptive_cursor_sharing=FALSE(关闭ACS,还有其他两个参数也要一起设置)
执行计划不稳定的同时还会带来低性能。
使用ACS的前提条件:
1.绑定变量使用变量窥视;
2.绑定变量的列上使用直方图;
关闭acs步骤:
我们先来看看跟ACS相关的三个隐藏参数,是用来控制是否启用ACS
col ksppinm for a30
col ksppstvl for a20
col ksppdesc for a35
SELECT ksppinm, ksppstvl, ksppdesc
FROM x$ksppi x, x$ksppcv y
WHERE x.indx = y.indx AND ksppinm = '_optimizer_adaptive_cursor_sharing';
KSPPINM KSPPSTVL KSPPDESC
------------------------------ -------------------- -----------------------------------
_optimizer_adaptive_cursor_sha TRUE optimizer adaptive cursor sharing
ring
SELECT ksppinm, ksppstvl, ksppdesc
FROM x$ksppi x, x$ksppcv y
WHERE x.indx = y.indx AND ksppinm = '_optimizer_extended_cursor_sharing';
KSPPINM KSPPSTVL KSPPDESC
------------------------------ -------------------- -----------------------------------
_optimizer_extended_cursor_sha UDO optimizer extended cursor sharing
ring
SELECT ksppinm, ksppstvl, ksppdesc
FROM x$ksppi x, x$ksppcv y
WHERE x.indx = y.indx AND ksppinm = '_optimizer_extended_cursor_sharing_rel';
KSPPINM KSPPSTVL KSPPDESC
------------------------------ -------------------- -----------------------------------
_optimizer_extended_cursor_sha SIMPLE optimizer extended cursor sharing f
ring_rel
所以如果我们要关闭ACS,使用如下的命令
alter system set "_optimizer_extended_cursor_sharing_rel"=none;
alter system set "_optimizer_extended_cursor_sharing"=none;
alter system set "_optimizer_adaptive_cursor_sharing"=false;
关于直方图案例:
下面我们就来进行实验
准备一张测试表,数据分布极不均匀
20:10:16 scott@prod> select count(*) from test where object_id=1;
COUNT(*)
-------------
2400000
Elapsed: 00:00:00.60
20:11:41 scott@prod> select count(*) from test;
COUNT(*)
-------------
2406496
20:07:06 scott@prod> create index idx_test on test(object_id);
Index created.
收集柱状图
exec dbms_stats.gather_table_stats('SCOTT','TEST',method_opt=>'for all columns size skewonly');
20:14:41 scott@prod> select COLUMN_NAME,HISTOGRAM from user_tab_columns where TABLE_NAME='TEST' and column_name='OBJECT_ID';
COLUMN_NAME HISTOGRAM
------------------------------ ---------------
OBJECT_ID HEIGHT BALANCED
关于histogram总共有两种,一种是HEIGHT BALANCED,另外一种是FREQUENCY,这个是oracle是根据NDV来进行选择
如果有254个以下的非空的不同值,就是FREQUENCY的柱状图
如果有254个以上的非空的不同值,就是HEIGHT BALANCED的柱状图
关于柱状图的其他的特性,我会在另外的文章中再进行总结
由于set autotrace on 不是显示的实际的执行计划,是根据统计信息进行估算的,并不一定能够反应真是的执行计划,我们使用
select * from table(dbms_xplan.display_cursor('sql_id',null,'advanced'));来显示正确的执行计划
20:26:37 scott@prod> var x number
20:26:51 scott@prod> exec :x:=48056
PL/SQL procedure successfully completed.
20:27:03 scott@prod> select * from test where object_id=:x;
20:35:50 sys@prod> select sql_id,sql_text,EXECUTIONS from v$sql where sql_text ='select * from test where object_id=:x';
SQL_ID SQL_TEXT EXECUTIONS
------------- ---------------------------------------- -------------
fk1y97mvmdu8f select * from test where object_id=:x 1
20:33:22 sys@prod> select * from table(dbms_xplan.display_cursor('fk1y97mvmdu8f',null,'advanced'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
SQL_ID fk1y97mvmdu8f, child number 0
-------------------------------------
select * from test where object_id=:x
Plan hash value: 2473784974
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 4 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 3 | 285 | 4 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_TEST | 3 | | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / TEST@SEL$1
2 - SEL$1 / TEST@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
DB_VERSION('11.2.0.3')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX_RS_ASC(@"SEL$1" "TEST"@"SEL$1" ("TEST"."OBJECT_ID"))
END_OUTLINE_DATA
*/
Peeked Binds (identified by position):
--------------------------------------
1 - :X (NUMBER): 48056
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=:X)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "TEST"."OWNER"[VARCHAR2,30], "TEST"."OBJECT_NAME"[VARCHAR2,128],
"TEST"."SUBOBJECT_NAME"[VARCHAR2,30], "OBJECT_ID"[NUMBER,22],
"TEST"."DATA_OBJECT_ID"[NUMBER,22], "TEST"."OBJECT_TYPE"[VARCHAR2,19],
"TEST"."CREATED"[DATE,7], "TEST"."LAST_DDL_TIME"[DATE,7],
"TEST"."TIMESTAMP"[VARCHAR2,19], "TEST"."STATUS"[VARCHAR2,7],
"TEST"."TEMPORARY"[VARCHAR2,1], "TEST"."GENERATED"[VARCHAR2,1],
"TEST"."SECONDARY"[VARCHAR2,1], "TEST"."NAMESPACE"[NUMBER,22],
"TEST"."EDITION_NAME"[VARCHAR2,30]
2 - "TEST".ROWID[ROWID,10], "OBJECT_ID"[NUMBER,22]
可以看到此时走的是索引的范围扫描
我们换object_id=1的,可以看到,总共240多万的表,object_id=1的就有240万,按道理这种肯定是应该走全表扫描的
exec :x:=1
select * from test where object_id=:x;
20:36:23 sys@prod> select sql_id,sql_text,EXECUTIONS from v$sql where sql_text ='select * from test where object_id=:x';
SQL_ID SQL_TEXT EXECUTIONS
------------- ---------------------------------------- -------------
fk1y97mvmdu8f select * from test where object_id=:x 2
可以看到这个子游标的执行次数已经变成了2次
在来看此时的执行计划
20:38:39 sys@prod> select * from table(dbms_xplan.display_cursor('fk1y97mvmdu8f',null,'advanced'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
SQL_ID fk1y97mvmdu8f, child number 0
-------------------------------------
select * from test where object_id=:x
Plan hash value: 2473784974
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 4 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 3 | 285 | 4 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_TEST | 3 | | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / TEST@SEL$1
2 - SEL$1 / TEST@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
DB_VERSION('11.2.0.3')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX_RS_ASC(@"SEL$1" "TEST"@"SEL$1" ("TEST"."OBJECT_ID"))
END_OUTLINE_DATA
*/
Peeked Binds (identified by position):
--------------------------------------
1 - :X (NUMBER): 48056
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=:X)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "TEST"."OWNER"[VARCHAR2,30], "TEST"."OBJECT_NAME"[VARCHAR2,128],
"TEST"."SUBOBJECT_NAME"[VARCHAR2,30], "OBJECT_ID"[NUMBER,22],
"TEST"."DATA_OBJECT_ID"[NUMBER,22], "TEST"."OBJECT_TYPE"[VARCHAR2,19],
"TEST"."CREATED"[DATE,7], "TEST"."LAST_DDL_TIME"[DATE,7],
"TEST"."TIMESTAMP"[VARCHAR2,19], "TEST"."STATUS"[VARCHAR2,7],
"TEST"."TEMPORARY"[VARCHAR2,1], "TEST"."GENERATED"[VARCHAR2,1],
"TEST"."SECONDARY"[VARCHAR2,1], "TEST"."NAMESPACE"[NUMBER,22],
"TEST"."EDITION_NAME"[VARCHAR2,30]
2 - "TEST".ROWID[ROWID,10], "OBJECT_ID"[NUMBER,22]
还是索引的范围的扫描,现在现在走这个执行计划是不合适的,由于绑定变量窥视,oracle走错了执行计划
select CHILD_NUMBER,PLAN_HASH_VALUE,EXECUTIONS,
BUFFER_GETS/EXECUTIONS BG_PER_EX,
IS_BIND_SENSITIVE BS,IS_BIND_AWARE BA,IS_SHAREABLE S
from v$sql where sql_id='fk1y97mvmdu8f';
select CHILD_NUMBER,PLAN_HASH_VALUE,EXECUTIONS,
BUFFER_GETS/EXECUTIONS BG_PER_EX,
IS_BIND_SENSITIVE BS,IS_BIND_AWARE BA,IS_SHAREABLE S
from v$sql where sql_id='fk1y97mvmdu8f';
CHILD_NUMBER PLAN_HASH_VALUE EXECUTIONS BG_PER_EX B B S
------------- --------------- ------------- ------------- - - -
0 2473784974 2 3913 Y N Y
从v$SQL中,可以看到这个cursor的数据,其中
IS_BIND_SENSITIVE=Y,表明使用绑定变量窥视来生成这次执行计划,这次执行计划是取决于这个绑定变量的,如果Oracle发现有其他的绑定变量出现,是可能生成其他的执行计划的。
IS_BIND_AWARE=N,表明Oracle还没有使用extended cursor sharing。
IS_SHAREABLE=Y,表明这个cursor可以被再次使用,即能够共享;反之,设为N代表着这个cursor已经过时了,不会被再用了,这个cursor将会等待被age out出shared pool。
此时我们在执行这条sql语句
select sql_id,sql_text,child_number,executions,is_bind_sensitive,is_bind_aware,is_shareable,plan_hash_value from v$sql where sql_text ='select * from test where object_id=:x';
SQL_ID SQL_TEXT CHILD_NUMBER EXECUTIONS I I I PLAN_HASH_VALUE
------------- ---------------------------------------- ------------- ------------- - - - ---------------
fk1y97mvmdu8f select * from test where object_id=:x 0 2 Y N N 2473784974
fk1y97mvmdu8f select * from test where object_id=:x 1 1 Y Y Y 1357081020
可以看到重新生成了新的子游标
查看新游标的执行计划
Elapsed: 00:00:00.13
06:14:18 sys@orcl> select * from table(dbms_xplan.display_cursor('fk1y97mvmdu8f',1,'advanced'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
SQL_ID fk1y97mvmdu8f, child number 1
-------------------------------------
select * from test where object_id=:x
Plan hash value: 1357081020
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 5528 (100)| |
|* 1 | TABLE ACCESS FULL| TEST | 1369K| 125M| 5528 (1)| 00:01:07 |
--------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / TEST@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
DB_VERSION('11.2.0.4')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "TEST"@"SEL$1")
END_OUTLINE_DATA
*/
Peeked Binds (identified by position):
--------------------------------------
1 - :X (NUMBER): 100
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID"=:X)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "TEST"."OWNER"[VARCHAR2,30], "TEST"."OBJECT_NAME"[VARCHAR2,128],
"TEST"."SUBOBJECT_NAME"[VARCHAR2,30], "OBJECT_ID"[NUMBER,22],
"TEST"."DATA_OBJECT_ID"[NUMBER,22], "TEST"."OBJECT_TYPE"[VARCHAR2,19],
"TEST"."CREATED"[DATE,7], "TEST"."LAST_DDL_TIME"[DATE,7],
"TEST"."TIMESTAMP"[VARCHAR2,19], "TEST"."STATUS"[VARCHAR2,7],
"TEST"."TEMPORARY"[VARCHAR2,1], "TEST"."GENERATED"[VARCHAR2,1],
"TEST"."SECONDARY"[VARCHAR2,1], "TEST"."NAMESPACE"[NUMBER,22],
"TEST"."EDITION_NAME"[VARCHAR2,30]
如果此时我们赋予一个另外的值,我们再重新执行以下这个sql语句
06:18:00 kiwi@orcl> exec :x:=57603
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.01
06:19:14 kiwi@orcl> select * from test where object_id=:x;
SQL_ID SQL_TEXT CHILD_NUMBER EXECUTIONS I I I PLAN_HASH_VALUE
------------- ---------------------------------------- ------------- ------------- - - - ---------------
fk1y97mvmdu8f select * from test where object_id=:x 0 2 Y N N 2473784974
fk1y97mvmdu8f select * from test where object_id=:x 1 4 Y N N 1357081020
fk1y97mvmdu8f select * from test where object_id=:x 2 1 Y Y Y 2473784974
我们此时如果在反复的执行
06:26:39 kiwi@orcl> exec :x:=100
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.00
06:27:05 kiwi@orcl> select * from test where object_id=:x;
SQL_ID SQL_TEXT CHILD_NUMBER EXECUTIONS I I I PLAN_HASH_VALUE
------------- ---------------------------------------- ------------- ------------- - - - ---------------
fk1y97mvmdu8f select * from test where object_id=:x 0 2 Y N N 2473784974
fk1y97mvmdu8f select * from test where object_id=:x 1 4 Y N N 1357081020
fk1y97mvmdu8f select * from test where object_id=:x 2 4 Y Y Y 2473784974
fk1y97mvmdu8f select * from test where object_id=:x 3 1 Y Y Y 1357081020
这样重复的执行下来,oracle就会找到可以被重复使用是几种执行计划,并把他们共享出去
ACS相关的视图
select child_number,predicate,range_id,low,high from v$sql_cs_selectivity
where sql_id='fk1y97mvmdu8f' order by 1;
CHILD_NUMBER PREDICATE RANGE_ID LOW HIGH
------------- ---------------------------------------- ------------- ---------- ----------
2 =X 0 0.000018 0.000022
3 =X 0 0.892941 1.091373
统计的各个子游标的选择性范围
select child_number,bind_set_hash_value,peeked,executions,rows_processed,buffer_gets
from v$sql_cs_statistics where sql_id='fk1y97mvmdu8f'
order by 1;
CHILD_NUMBER BIND_SET_HASH_VALUE P EXECUTIONS ROWS_PROCESSED BUFFER_GETS
------------- ------------------- - ------------- -------------- -------------
0 2251692860 Y 1 2 5
1 336594526 Y 1 1380000 110576
2 1059637539 Y 1 2 5
3 336594526 Y 1 1411 111
统计的各个子游标的执行情况
关于oracle收集直方图时 method_opt中参数 auto跟skewonly的区别:
我们先来看官方文档中对这两个参数的解释
- AUTO : Oracle determines the columns to collect histograms based on data distribution and the workload of the columns.
- SKEWONLY : Oracle determines the columns to collect histograms based on the data distribution of the columns
从字面上解读,AUTO和SKEWONLY的区别就在于收集histograms时ORACLE的选择条件不同。
AUTO会根据column数据分布情况以及column的负载情况进行判断是不是收集这个column的histogram.
而SKEWONLY只会根据column的数据分布情况决定是否收集histogram。
这里,我会强调这个“只”字,因为在SKEWONLY时,只要Oracle觉得数据分布可能不均,就会收集列的柱状图。
而AUTO时,即使某一个column它的数据分布不均,但只要这个column的workload很低,或者说没有workload,那么Oracle就不会收集这个column的histogram
Oracle自行判断的依据就来源于col_usage$字典基表,若表上的某一列曾在硬解析(hard parse)过的SQL语句中充当过predicate(通俗的说就是where后的condition)的话,我们认为此列上有收集柱状图的必要,那么col_usage$上就会被加入该列曾充当predicate的记录。当DBMS_STATS.GATHER_TABLE_STATS存储过程以’SIZE AUTO’模式执行时,收集进程会检查col_usage$基表以判断哪些列之前曾充当过predicate,若充当过则说明该列有收集柱状图的价值。
t.num_distinct,
t.num_buckets,
t.last_analyzed,
t.HISTOGRAM
from dba_tab_col_statistics t
where table_name = 'TI_O_YWTOCREDIT'
关于histogram总共有两种,一种是HEIGHT BALANCED,另外一种是FREQUENCY,这个是oracle是根据NDV来进行选择
如果有254个以下的非空的不同值,就是FREQUENCY的柱状图
如果有254个以上的非空的不同值,就是HEIGHT BALANCED的柱状图
HEIGHT BALANCED 高度平衡直方图(a.ENDPOINT_NUMBER(代表桶号),a.ENDPOINT_VALUE )
FREQUENCY 频率直方图(a.ENDPOINT_NUMBER(代表桶号),a.ENDPOINT_VALUE )
今天谈谈下面这几个参数对数据库性能和稳定性的影响:
cursor_sharing:游标共享
_optim_peek_user_binds:绑定变量窥视
_optimizer_adaptive_cursor_sharing:自适应游标共享(简称ACS),一般还包括另外两个_optimizer_extended_cursor_sharing和_optimizer_extended_cursor_sharing_rel 参数)
_optim_peek_user_binds和ACS:
在10g,因为没有ACS,一般建议客户关闭绑定变量窥视功能的情况多一些。
在11g,很多客户还是将绑定变量窥视和ACS都关闭了,原因有的是数据库从1g升级而来,升级后没有改,还有就是因为ACS早期版本有一些bug。
其实这是两个很好的参数,可以在代码写的不是太好的情况下,也能获得比较好的性能。虽然ACS可能还有一些小bug没有解决(有的bug是在很特殊的情况下才会触发),
到了11204版本应该都不是大问题了。老虎刘建议还是都开启比较好。
最重要的参数还在下面,如果做到了下面这两点,上面两个参数就显得不是那么重要了:
首先,cursor_sharing这个参数对系统性能和稳定性都非常重要,可惜经常被忽略,建议使用该参数的默认值:
即 cursor_sharing=EXACT (而不是FORCE或similar)
这要求应该使用绑定变量的地方,必须使用绑定变量。这个对于OLTP系统来说是铁律,不容置疑,
cursor_sharing=FORCE通常就是为了解决该使用绑定变量而没有使用绑定变量的情况。
前年在网上看到一个广为流传的某水果公司的AWR报告,居然设置 cursor_sharing= FORCE ,令人感叹啊。
其次,还有一个重要的补充条件:
不该使用绑定变量的地方,不用绑定变量:对那些唯一值较少的字段,特别是数据分布不均的情况,
不建议使用绑定变量。如type、status等字段,我们建议使用常量:where type=1 and status=2。
这种情况如果使用了绑定变量,就是绑定变量窥视和ACS发挥作用的时候。
如果cursor_sharing=FORCE;或者cursor_sharing=EXACT,但是在数据分布不均的字段上也使用了绑定变量(两者基本上是等同的,虽然后一种略好于前一种情况),
那么就要考虑“绑定变量窥视”和“自适应游标”两个参数的影响了。
看下面几种情况:
1、关闭“绑定变量窥视”(默认是开启):
ACS同时失效,这时系统的稳定性好(不会因为绑定变量的不同,发生执行计划改变),
但是整体性能会下降:因为不能窥视绑定变量,只能按照字段是数据分布均匀的情况来计算,
在能否使用索引,返回行源的估值上,都会出现较大的偏差,有时可能会配合使用hint来提高SQL性能。
2、如果开启了“绑定变量窥视”而不开启ACS(默认是开启):
那么系统就会极不稳定:比如硬解析窥视到一个绑定变量适合全表扫描的执行计划,不管接下来的绑定变量是否能使用索引,
都会一直全表扫描下去,直到下次硬解析时再次窥视绑定变量才可能重新生成新的执行计划。
3、如果开启“绑定变量窥视”,同时开启ACS:
这种情况在解决了一部分稳定性的同时,兼顾了性能。也是11g新增的ACS比10g没有ACS进步的地方:
执行计划不再从一而终,而是会根据绑定变量的不同,不是很及时的做出调整:
比如第一次窥视到的绑定变量适合全表扫描,那么第二次即使使用的绑定变量适合走索引,也还是会使用全表扫描的执行计划,
下一次再次执行就会纠正为使用索引的执行计划(具体请参考ACS的实现原理)。
绑定变量窥视和ACS这两个参数是与直方图信息紧密联系在一起的,关闭直方图收集,也就相当于关闭了绑定变量窥视和ACS,即使开启了这两个参数。
直方图能较为准确的反映数据分布不均字段的数据分布情况,一般使用默认选项(auto),
某些特殊情况可以补充或去掉某些字段的直方图信息。一些客户在数据库级关闭收集直方图的做法是不建议的。
总结:
最佳实践:
cursor_sharing=EXACT + 合理使用绑定变量(合理就是:类似ID、account_no等唯一值等于或接近表行数的字段,必须使用绑定变量;
而type、status等唯一值少且数分别不均的字段,不使用绑定变量)。
绑定变量窥视和ACS保持默认开启状态。
特殊情况:
1、字段唯一值有一定的数量(介于少与多之间),比如1000个,如果数据分布均匀,则可以使用绑定变量。
如果字段分布不均,则把占比多的几个值,使用常量,其他值使用绑定变量。
2、字段唯一值少,还有经常互相转变的情况,比如常见的工单处理表:没有处理的状态是0,处理后的状态是1,
夜间统计信息收集后,由于字段值的不稳定,统计信息经常不能反映表的实时数据分布情况,这种情况谈是否使用绑定变量已没有意义,
涉及这类表的SQL,可以关闭字段上的直方图收集,再配合rownum和hint 来提高SQL效率和稳定性,
必要时还可以使用dynamic_sampling(动态采样)来辅助优化器做出正确的执行计划。
最差组合:
cursor_sharing=FORCE
_optim_peek_user_binds=TRUE(开启绑定变量窥视)
_optimizer_adaptive_cursor_sharing=FALSE(关闭ACS,还有其他两个参数也要一起设置)
执行计划不稳定的同时还会带来低性能。
使用ACS的前提条件:
1.绑定变量使用变量窥视;
2.绑定变量的列上使用直方图;
关闭acs步骤:
我们先来看看跟ACS相关的三个隐藏参数,是用来控制是否启用ACS
col ksppinm for a30
col ksppstvl for a20
col ksppdesc for a35
SELECT ksppinm, ksppstvl, ksppdesc
FROM x$ksppi x, x$ksppcv y
WHERE x.indx = y.indx AND ksppinm = '_optimizer_adaptive_cursor_sharing';
KSPPINM KSPPSTVL KSPPDESC
------------------------------ -------------------- -----------------------------------
_optimizer_adaptive_cursor_sha TRUE optimizer adaptive cursor sharing
ring
SELECT ksppinm, ksppstvl, ksppdesc
FROM x$ksppi x, x$ksppcv y
WHERE x.indx = y.indx AND ksppinm = '_optimizer_extended_cursor_sharing';
KSPPINM KSPPSTVL KSPPDESC
------------------------------ -------------------- -----------------------------------
_optimizer_extended_cursor_sha UDO optimizer extended cursor sharing
ring
SELECT ksppinm, ksppstvl, ksppdesc
FROM x$ksppi x, x$ksppcv y
WHERE x.indx = y.indx AND ksppinm = '_optimizer_extended_cursor_sharing_rel';
KSPPINM KSPPSTVL KSPPDESC
------------------------------ -------------------- -----------------------------------
_optimizer_extended_cursor_sha SIMPLE optimizer extended cursor sharing f
ring_rel
所以如果我们要关闭ACS,使用如下的命令
alter system set "_optimizer_extended_cursor_sharing_rel"=none;
alter system set "_optimizer_extended_cursor_sharing"=none;
alter system set "_optimizer_adaptive_cursor_sharing"=false;
关于直方图案例:
下面我们就来进行实验
准备一张测试表,数据分布极不均匀
20:10:16 scott@prod> select count(*) from test where object_id=1;
COUNT(*)
-------------
2400000
Elapsed: 00:00:00.60
20:11:41 scott@prod> select count(*) from test;
COUNT(*)
-------------
2406496
20:07:06 scott@prod> create index idx_test on test(object_id);
Index created.
收集柱状图
exec dbms_stats.gather_table_stats('SCOTT','TEST',method_opt=>'for all columns size skewonly');
20:14:41 scott@prod> select COLUMN_NAME,HISTOGRAM from user_tab_columns where TABLE_NAME='TEST' and column_name='OBJECT_ID';
COLUMN_NAME HISTOGRAM
------------------------------ ---------------
OBJECT_ID HEIGHT BALANCED
关于histogram总共有两种,一种是HEIGHT BALANCED,另外一种是FREQUENCY,这个是oracle是根据NDV来进行选择
如果有254个以下的非空的不同值,就是FREQUENCY的柱状图
如果有254个以上的非空的不同值,就是HEIGHT BALANCED的柱状图
关于柱状图的其他的特性,我会在另外的文章中再进行总结
由于set autotrace on 不是显示的实际的执行计划,是根据统计信息进行估算的,并不一定能够反应真是的执行计划,我们使用
select * from table(dbms_xplan.display_cursor('sql_id',null,'advanced'));来显示正确的执行计划
20:26:37 scott@prod> var x number
20:26:51 scott@prod> exec :x:=48056
PL/SQL procedure successfully completed.
20:27:03 scott@prod> select * from test where object_id=:x;
20:35:50 sys@prod> select sql_id,sql_text,EXECUTIONS from v$sql where sql_text ='select * from test where object_id=:x';
SQL_ID SQL_TEXT EXECUTIONS
------------- ---------------------------------------- -------------
fk1y97mvmdu8f select * from test where object_id=:x 1
20:33:22 sys@prod> select * from table(dbms_xplan.display_cursor('fk1y97mvmdu8f',null,'advanced'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
SQL_ID fk1y97mvmdu8f, child number 0
-------------------------------------
select * from test where object_id=:x
Plan hash value: 2473784974
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 4 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 3 | 285 | 4 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_TEST | 3 | | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / TEST@SEL$1
2 - SEL$1 / TEST@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
DB_VERSION('11.2.0.3')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX_RS_ASC(@"SEL$1" "TEST"@"SEL$1" ("TEST"."OBJECT_ID"))
END_OUTLINE_DATA
*/
Peeked Binds (identified by position):
--------------------------------------
1 - :X (NUMBER): 48056
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=:X)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "TEST"."OWNER"[VARCHAR2,30], "TEST"."OBJECT_NAME"[VARCHAR2,128],
"TEST"."SUBOBJECT_NAME"[VARCHAR2,30], "OBJECT_ID"[NUMBER,22],
"TEST"."DATA_OBJECT_ID"[NUMBER,22], "TEST"."OBJECT_TYPE"[VARCHAR2,19],
"TEST"."CREATED"[DATE,7], "TEST"."LAST_DDL_TIME"[DATE,7],
"TEST"."TIMESTAMP"[VARCHAR2,19], "TEST"."STATUS"[VARCHAR2,7],
"TEST"."TEMPORARY"[VARCHAR2,1], "TEST"."GENERATED"[VARCHAR2,1],
"TEST"."SECONDARY"[VARCHAR2,1], "TEST"."NAMESPACE"[NUMBER,22],
"TEST"."EDITION_NAME"[VARCHAR2,30]
2 - "TEST".ROWID[ROWID,10], "OBJECT_ID"[NUMBER,22]
可以看到此时走的是索引的范围扫描
我们换object_id=1的,可以看到,总共240多万的表,object_id=1的就有240万,按道理这种肯定是应该走全表扫描的
exec :x:=1
select * from test where object_id=:x;
20:36:23 sys@prod> select sql_id,sql_text,EXECUTIONS from v$sql where sql_text ='select * from test where object_id=:x';
SQL_ID SQL_TEXT EXECUTIONS
------------- ---------------------------------------- -------------
fk1y97mvmdu8f select * from test where object_id=:x 2
可以看到这个子游标的执行次数已经变成了2次
在来看此时的执行计划
20:38:39 sys@prod> select * from table(dbms_xplan.display_cursor('fk1y97mvmdu8f',null,'advanced'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
SQL_ID fk1y97mvmdu8f, child number 0
-------------------------------------
select * from test where object_id=:x
Plan hash value: 2473784974
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 4 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 3 | 285 | 4 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_TEST | 3 | | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / TEST@SEL$1
2 - SEL$1 / TEST@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
DB_VERSION('11.2.0.3')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX_RS_ASC(@"SEL$1" "TEST"@"SEL$1" ("TEST"."OBJECT_ID"))
END_OUTLINE_DATA
*/
Peeked Binds (identified by position):
--------------------------------------
1 - :X (NUMBER): 48056
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=:X)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "TEST"."OWNER"[VARCHAR2,30], "TEST"."OBJECT_NAME"[VARCHAR2,128],
"TEST"."SUBOBJECT_NAME"[VARCHAR2,30], "OBJECT_ID"[NUMBER,22],
"TEST"."DATA_OBJECT_ID"[NUMBER,22], "TEST"."OBJECT_TYPE"[VARCHAR2,19],
"TEST"."CREATED"[DATE,7], "TEST"."LAST_DDL_TIME"[DATE,7],
"TEST"."TIMESTAMP"[VARCHAR2,19], "TEST"."STATUS"[VARCHAR2,7],
"TEST"."TEMPORARY"[VARCHAR2,1], "TEST"."GENERATED"[VARCHAR2,1],
"TEST"."SECONDARY"[VARCHAR2,1], "TEST"."NAMESPACE"[NUMBER,22],
"TEST"."EDITION_NAME"[VARCHAR2,30]
2 - "TEST".ROWID[ROWID,10], "OBJECT_ID"[NUMBER,22]
还是索引的范围的扫描,现在现在走这个执行计划是不合适的,由于绑定变量窥视,oracle走错了执行计划
select CHILD_NUMBER,PLAN_HASH_VALUE,EXECUTIONS,
BUFFER_GETS/EXECUTIONS BG_PER_EX,
IS_BIND_SENSITIVE BS,IS_BIND_AWARE BA,IS_SHAREABLE S
from v$sql where sql_id='fk1y97mvmdu8f';
select CHILD_NUMBER,PLAN_HASH_VALUE,EXECUTIONS,
BUFFER_GETS/EXECUTIONS BG_PER_EX,
IS_BIND_SENSITIVE BS,IS_BIND_AWARE BA,IS_SHAREABLE S
from v$sql where sql_id='fk1y97mvmdu8f';
CHILD_NUMBER PLAN_HASH_VALUE EXECUTIONS BG_PER_EX B B S
------------- --------------- ------------- ------------- - - -
0 2473784974 2 3913 Y N Y
从v$SQL中,可以看到这个cursor的数据,其中
IS_BIND_SENSITIVE=Y,表明使用绑定变量窥视来生成这次执行计划,这次执行计划是取决于这个绑定变量的,如果Oracle发现有其他的绑定变量出现,是可能生成其他的执行计划的。
IS_BIND_AWARE=N,表明Oracle还没有使用extended cursor sharing。
IS_SHAREABLE=Y,表明这个cursor可以被再次使用,即能够共享;反之,设为N代表着这个cursor已经过时了,不会被再用了,这个cursor将会等待被age out出shared pool。
此时我们在执行这条sql语句
select sql_id,sql_text,child_number,executions,is_bind_sensitive,is_bind_aware,is_shareable,plan_hash_value from v$sql where sql_text ='select * from test where object_id=:x';
SQL_ID SQL_TEXT CHILD_NUMBER EXECUTIONS I I I PLAN_HASH_VALUE
------------- ---------------------------------------- ------------- ------------- - - - ---------------
fk1y97mvmdu8f select * from test where object_id=:x 0 2 Y N N 2473784974
fk1y97mvmdu8f select * from test where object_id=:x 1 1 Y Y Y 1357081020
可以看到重新生成了新的子游标
查看新游标的执行计划
Elapsed: 00:00:00.13
06:14:18 sys@orcl> select * from table(dbms_xplan.display_cursor('fk1y97mvmdu8f',1,'advanced'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
SQL_ID fk1y97mvmdu8f, child number 1
-------------------------------------
select * from test where object_id=:x
Plan hash value: 1357081020
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 5528 (100)| |
|* 1 | TABLE ACCESS FULL| TEST | 1369K| 125M| 5528 (1)| 00:01:07 |
--------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / TEST@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
DB_VERSION('11.2.0.4')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "TEST"@"SEL$1")
END_OUTLINE_DATA
*/
Peeked Binds (identified by position):
--------------------------------------
1 - :X (NUMBER): 100
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID"=:X)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "TEST"."OWNER"[VARCHAR2,30], "TEST"."OBJECT_NAME"[VARCHAR2,128],
"TEST"."SUBOBJECT_NAME"[VARCHAR2,30], "OBJECT_ID"[NUMBER,22],
"TEST"."DATA_OBJECT_ID"[NUMBER,22], "TEST"."OBJECT_TYPE"[VARCHAR2,19],
"TEST"."CREATED"[DATE,7], "TEST"."LAST_DDL_TIME"[DATE,7],
"TEST"."TIMESTAMP"[VARCHAR2,19], "TEST"."STATUS"[VARCHAR2,7],
"TEST"."TEMPORARY"[VARCHAR2,1], "TEST"."GENERATED"[VARCHAR2,1],
"TEST"."SECONDARY"[VARCHAR2,1], "TEST"."NAMESPACE"[NUMBER,22],
"TEST"."EDITION_NAME"[VARCHAR2,30]
如果此时我们赋予一个另外的值,我们再重新执行以下这个sql语句
06:18:00 kiwi@orcl> exec :x:=57603
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.01
06:19:14 kiwi@orcl> select * from test where object_id=:x;
SQL_ID SQL_TEXT CHILD_NUMBER EXECUTIONS I I I PLAN_HASH_VALUE
------------- ---------------------------------------- ------------- ------------- - - - ---------------
fk1y97mvmdu8f select * from test where object_id=:x 0 2 Y N N 2473784974
fk1y97mvmdu8f select * from test where object_id=:x 1 4 Y N N 1357081020
fk1y97mvmdu8f select * from test where object_id=:x 2 1 Y Y Y 2473784974
我们此时如果在反复的执行
06:26:39 kiwi@orcl> exec :x:=100
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.00
06:27:05 kiwi@orcl> select * from test where object_id=:x;
SQL_ID SQL_TEXT CHILD_NUMBER EXECUTIONS I I I PLAN_HASH_VALUE
------------- ---------------------------------------- ------------- ------------- - - - ---------------
fk1y97mvmdu8f select * from test where object_id=:x 0 2 Y N N 2473784974
fk1y97mvmdu8f select * from test where object_id=:x 1 4 Y N N 1357081020
fk1y97mvmdu8f select * from test where object_id=:x 2 4 Y Y Y 2473784974
fk1y97mvmdu8f select * from test where object_id=:x 3 1 Y Y Y 1357081020
这样重复的执行下来,oracle就会找到可以被重复使用是几种执行计划,并把他们共享出去
ACS相关的视图
select child_number,predicate,range_id,low,high from v$sql_cs_selectivity
where sql_id='fk1y97mvmdu8f' order by 1;
CHILD_NUMBER PREDICATE RANGE_ID LOW HIGH
------------- ---------------------------------------- ------------- ---------- ----------
2 =X 0 0.000018 0.000022
3 =X 0 0.892941 1.091373
统计的各个子游标的选择性范围
select child_number,bind_set_hash_value,peeked,executions,rows_processed,buffer_gets
from v$sql_cs_statistics where sql_id='fk1y97mvmdu8f'
order by 1;
CHILD_NUMBER BIND_SET_HASH_VALUE P EXECUTIONS ROWS_PROCESSED BUFFER_GETS
------------- ------------------- - ------------- -------------- -------------
0 2251692860 Y 1 2 5
1 336594526 Y 1 1380000 110576
2 1059637539 Y 1 2 5
3 336594526 Y 1 1411 111
统计的各个子游标的执行情况
关于oracle收集直方图时 method_opt中参数 auto跟skewonly的区别:
我们先来看官方文档中对这两个参数的解释
- AUTO : Oracle determines the columns to collect histograms based on data distribution and the workload of the columns.
- SKEWONLY : Oracle determines the columns to collect histograms based on the data distribution of the columns
从字面上解读,AUTO和SKEWONLY的区别就在于收集histograms时ORACLE的选择条件不同。
AUTO会根据column数据分布情况以及column的负载情况进行判断是不是收集这个column的histogram.
而SKEWONLY只会根据column的数据分布情况决定是否收集histogram。
这里,我会强调这个“只”字,因为在SKEWONLY时,只要Oracle觉得数据分布可能不均,就会收集列的柱状图。
而AUTO时,即使某一个column它的数据分布不均,但只要这个column的workload很低,或者说没有workload,那么Oracle就不会收集这个column的histogram
Oracle自行判断的依据就来源于col_usage$字典基表,若表上的某一列曾在硬解析(hard parse)过的SQL语句中充当过predicate(通俗的说就是where后的condition)的话,我们认为此列上有收集柱状图的必要,那么col_usage$上就会被加入该列曾充当predicate的记录。当DBMS_STATS.GATHER_TABLE_STATS存储过程以’SIZE AUTO’模式执行时,收集进程会检查col_usage$基表以判断哪些列之前曾充当过predicate,若充当过则说明该列有收集柱状图的价值。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/25462274/viewspace-2131162/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/25462274/viewspace-2131162/