- 浏览: 785944 次
- 性别:
- 来自: 广州
文章分类
最新评论
oracle 11g新特性:Pending Statistics
- 博客分类:
- oracle
oracle 11g新特性:Pending Statistics 转
从11g开始,表与索引的统计信息收集完毕后,可以选择收集的统信息立即发布,也可以选择使新收集的统计信息处于pending状态,待确定处于pending状态的统计信息是安全的,再使处于pending状态的统计信息发布,这样就会避免一些因为收集统计信息立即发布而导致SQL执行计划走错的灾难。
在 11g 之前的版本中,DBMS_STATS 自动统计收集(Automatic Statistics Gathering)默认的阀值是 10%, 这个 10% 是不可以修改的。这对千变万化的企业数据库来说环境来说,有些死板,如果是个超大的表,默认的 10% 数据也是海量了,会把整个资源占死。Oracle 11g 中,这个属性可以通过修改 STALE_PERCENT 属性来修改, 有全局(DBMS_STATS.SET_GLOBAL_PREFS )和表级别(DBMS_STATS.SET_TABLE_PREFS)两种。
1 如何判断是否有pending的统计信息需要生效?
SQL> Select dbms_stats.get_prefs('PUBLISH') publish from dual;
PUBLISH
--------------------------
TRUE
dbms_stats的get_prefs函数返回true,表示对象的统计信息收集后立即生效,如果返回flase,收集的统计信息将处于pending状态。
2 如果查看相关的视图
A 立即生效的统计信息可以通过以下字典可以查看
user_tab_stats
user_ind_stats
B pending状态的统计信息可以通过以下字典可以查看
user_tab_pending_stats
user_ind_pending_stats
3 如何设置表或schema的统计信息的publish状态
用dbms_stats的set_table_prefs或者set_schema_prefs过程可以在表级或schema表设置它们的统计信息是否立即生效,当我们设置tmp_test表的统计信息收集后处于pending状态,那该表收集统计信息后,将存放于user_tab_pending_stats字典中。
SQL> Exec dbms_stats.set_table_prefs('yekai','tmp_test','publish','false');
PL/SQL procedure successfully completed.
SQL> select count(*) from user_tab_pending_stats;
COUNT(*)
----------
0
SQL> exec dbms_stats.gather_table_stats('yekai','tmp_test');
PL/SQL procedure successfully completed.
SQL> select count(*) from user_tab_pending_stats;
COUNT(*)
----------
1
4 如何测试并使用处于pending状态的统计信息
在11g,新的参数optimizer_pending_statistics将可以来解决这个问题,当我们在session级设置optimizer_pending_statistics为true时,我们就可以使用存放在user_*_pending_stats字典中的统计信息啦,当我们确保该处于pending状态的统计信息是正确时,我们就可以决定是否使它们立即生效。
SQL> alter session set optimizer_pending_statistics = TRUE;
5 如何发布处于pending状态的统计信息
当测试过统计信息有效后,我们可以选择发布pending状态的统计信息
SQL> exec dbms_stats.publish_pending_stats('yekai','tmp_test');
如果我们不需要该处于pending状态的统计信息,可以选择删除这个pending的统计信息
SQL> exec dbms_stats.publish_pending_stats('yekai','tmp_test');
在CBO时代,SQL语句的执行计划完全依赖于在数据字典中保存的统计量信息和优化器Optimizer的计算公式参数。从9i开始到现在的11gR2,我们说CBO优化器已经很成熟和完善。在通常情况下,我们的SQL都是可以获取到较好的执行计划以及执行效率的。
在实际工作中,我们经常会遇到执行计划低效的情况。但是这种故障根源中,绝大多数的原因在于统计量的错误或者失效。错误的统计量连带生成的就是不恰当的执行计划,以至于低效的执行过程。在9i时代,RBO和CBO混合使用,让我们经常需要自定义的统计量收集过程。
从10g开始,Oracle引入了自动收集统计量的作业,以保证数据字典中统计量正确反映数据对象状态。这在很大程度上,缓解了由于数据变化导致的统计量过期问题。但是,我们在实际工作中,还是会发现执行计划的突然变化。究其原因,就是某个时间点收集的统计量,也许不能反映数据的全貌(如中间表)。
1、统计量Pending
在系统运维中,我们常常希望维持SQL执行计划的稳定。很多DBA和开发人员对于hint的依赖,很大程度上也是源于对CBO情况下,执行计划对于统计量过于依赖,容易形成不稳定执行计划。
那么,我们SQL语句执行计划的稳定性,就变成统计量的稳定性问题。更进一步,就是新的统计量更新,无论是否手动收集还是自动收集,能否促进SQL语句生成更高效的执行计划。
所以,一种思路是:在新的统计量收集生成时,暂时不要生效投入执行计划生成。等待最后确认统计量正确之后,再投入生产环境。
在Oracle 11g中,推出了统计量管理的一种新技术——Pending Statistic技术,提供了这种功能。
简单的说,我们可以对一系列的数据表设置pending属性。设置pending属性之后,数据的统计量在数据字典中相当于已经锁定Lock住。但新统计量生成之后,不是直接替换原有的数据,而是存放在pending数据字典中。
在pending字典中的统计量,默认情况下是不会参与SQL执行计划的生产的。只有在进行SQL测试通过的时候,经过用户手工的确定,才会将其Publish出来,替换原有的统计量信息。
这样,就给我们运维DBA一种维持执行计划稳定的思路。通过固定统计量,将新统计量pending的方式将原有的统计量固定,从而稳定执行计划。进而,对pending的统计量进行测试,只有在更好执行计划的情况下,才会替换原有的方案。
下面,我们通过实验来验证pending统计量的使用。
2、实验环境构建
我们选择11gR2进行实验。
SQL> select * from v$version;
BANNER
-----------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
构建数据表T,以及对应的索引。注意,我们首先在数据表中不保存任何数据。
SQL> create table t as select * from dba_objects where 1=0;
Table created
SQL> create index idx_t_owner on t(owner);
Index created
SQL> create index idx_t_id on t(object_id);
Index created
在不显式的收集统计量的情况下,是没有对应的数据表统计量的。
SQL> select NUM_ROWS, BLOCKS EMPTY_BLOCKS, AVG_SPACE, CHAIN_CNT, AVG_ROW_LEN from user_tab_statistics where table_name='T';
NUM_ROWS EMPTY_BLOCKS AVG_SPACE CHAIN_CNT AVG_ROW_LEN
---------- ------------ ---------- ---------- -----------
SQL> select count(*) from user_tab_col_statistics where table_name='T';
COUNT(*)
----------
0
SQL> select BLEVEL, LEAF_BLOCKS, DISTINCT_KEYS, CLUSTERING_FACTOR NUM_ROWS from user_ind_statistics where index_name='IDX_T_OWNER';
BLEVEL LEAF_BLOCKS DISTINCT_KEYS NUM_ROWS
---------- ----------- ------------- ----------
0 0 0 0
收集统计量,获取最新的数据分布状况。
SQL> exec dbms_stats.gather_table_stats(user,'T',cascade => true);
PL/SQL procedure successfully completed
当我们修改数据内容,没有收集统计量,会存在新旧差异。
SQL> insert into t select * from dba_objects;
72202 rows inserted
SQL> commit;
Commit complete
SQL> select NUM_ROWS, BLOCKS EMPTY_BLOCKS, AVG_SPACE, CHAIN_CNT, AVG_ROW_LEN from user_tab_statistics where table_name='T';
NUM_ROWS EMPTY_BLOCKS AVG_SPACE CHAIN_CNT AVG_ROW_LEN
---------- ------------ ---------- ---------- -----------
0 0 0 0 0
3、Pending Statistics设置
在11g环境中,数据表、Schema都存在一个统计量相关参数PUBLISH,表示当有新统计量的时候,新统计量是否立即被publish出来,作为最新的统计信息使用。
该参数的默认值为TRUE。
SQL> select dbms_stats.get_prefs(pname => 'PUBLISH',ownname => 'SYS',tabname => 'T') from dual;
DBMS_STATS.GET_PREFS(PNAME=>'P
-------------------------------------------------------
TRUE
--设置数据表的publish参数取值;
SQL> exec dbms_stats.set_table_prefs(user,'T','PUBLISH','false');
PL/SQL procedure successfully completed
SQL> select dbms_stats.get_prefs('PUBLISH',ownname => 'SYS',tabname => 'T') from dual;
DBMS_STATS.GET_PREFS('PUBLISH'
--------------------------------------
FALSE
此时,数据表中已经包括了七万余条数据,重新收集统计量。
SQL> exec dbms_stats.gather_table_stats(user,'T',cascade => true);
PL/SQL procedure successfully completed
SQL> select NUM_ROWS, BLOCKS EMPTY_BLOCKS, AVG_SPACE, CHAIN_CNT, AVG_ROW_LEN from user_tab_statistics where table_name='T';
NUM_ROWS EMPTY_BLOCKS AVG_SPACE CHAIN_CNT AVG_ROW_LEN
---------- ------------ ---------- ---------- -----------
0 0 0 0 0
当我们将数据表T的PUBLISH参数修改为false之后,我们重新收集统计量,发现原有统计信息并没有连带的更新。
新统计量不是没有收集,而是被记录在了pending信息中。我们可以通过user_ind_pending_stats和user_tab_pending_stats两个视图查看被pending的统计量信息。
SQL> select NUM_ROWS, BLOCKS, AVG_ROW_LEN, SAMPLE_SIZE, LAST_ANALYZED from user_tab_pending_stats where table_name='T';
NUM_ROWS BLOCKS AVG_ROW_LEN SAMPLE_SIZE LAST_ANALYZED
---------- ---------- ----------- ----------- -------------
72202 1028 97 72202 2012/6/20 20:
SQL> select index_name, LEAF_BLOCKS, DISTINCT_KEYS, CLUSTERING_FACTOR,LAST_ANALYZED from user_ind_pending_stats where table_name='T';
INDEX_NAME LEAF_BLOCKS DISTINCT_KEYS CLUSTERING_FACTOR LAST_ANALYZED
------------------------------ ----------- ------------- ----------------- -------------
IDX_T_OWNER 293 23 1884 2012/6/20 20:
IDX_T_ID 256 72202 1665 2012/6/20 20:
4、Pending和SQL执行计划
新的统计量没有被publish出来。那么,在一般情况下,我们的SQL执行计划还是依据正式被publish的统计量生成。
SQL> explain plan for select * from t where wner='SYS';
Explained
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------
Plan hash value: 1516787156
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 207 | 1 (0)|
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 207 | 1 (0)|
|* 2 | INDEX RANGE SCAN | IDX_T_OWNER | 1 | | 1 (0)|
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OWNER"='SYS')
14 rows selected
实际执行情况;
SQL> select * from t where wner='SYS';
已选择58799行。
已用时间: 00: 00: 06.19
执行计划
----------------------------------------------------------
Plan hash value: 1516787156
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 207 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 207 | 1 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_T_OWNER | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OWNER"='SYS')
统计信息
----------------------------------------------------------
528 recursive calls
0 db block gets
8962 consistent gets
1108 physical reads
0 redo size
6291375 bytes sent via SQL*Net to client
43520 bytes received via SQL*Net from client
3921 SQL*Net roundtrips to/from client
4 sorts (memory)
0 sorts (disk)
58799 rows processed
SQL>
在sys用户下,行数比例超过了数据表T的绝大多数。按照CBO的原则,走全表扫描可能是较好的方法。但是,由于统计量还是在空表的状态下,所以,Oracle CBO认为Index路径会更好。
在Oracle中,存在一个参数optimizer_use_pending_statistics,用来控制当前是否使用pending的统计量来生成执行计划。作为运维DBA,可以通过这个参数暂时性的启用pending统计量,观察一下性能状况。再决定是否启用publish这些统计量。
默认情况下,该参数取值为false。我们可以在session级别设置下该参数为true。
SQL> show parameter optimizer_use_pending
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_use_pending_statistics boolean FALSE
修改参数为true之后,Oracle CBO在生成执行计划的时候就会使用Pending的统计量。
SQL> alter session set optimizer_use_pending_statistics=true;
Session altered
SQL> select value from v$parameter where name='optimizer_use_pending_statistics';
VALUE
------------------------------------------
TRUE
SQL> explain plan for select * from t where wner='SYS';
Explained
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 58274 | 5463K| 281 (1)| 00:00:04 |
|* 1 | TABLE ACCESS FULL| T | 58274 | 5463K| 281 (1)| 00:00:04 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OWNER"='SYS')
13 rows selected
SQL> select * from t where wner='SYS';
已选择58799行。
已用时间: 00: 00: 04.68
执行计划
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 58274 | 5463K| 281 (1)| 00:00:04 |
|* 1 | TABLE ACCESS FULL| T | 58274 | 5463K| 281 (1)| 00:00:04 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OWNER"='SYS')
统计信息
----------------------------------------------------------
7511 recursive calls
50 db block gets
6599 consistent gets
1118 physical reads
0 redo size
2392962 bytes sent via SQL*Net to client
43520 bytes received via SQL*Net from client
3921 SQL*Net roundtrips to/from client
211 sorts (memory)
0 sorts (disk)
58799 rows processed
果然,设置参数后,Oracle生成了FTS路径,说明更新的统计量起了作用。同时,执行时间减少了近2秒钟,说明结果上也确实是生成了更好的执行计划。
5、Pending统计量的后续处理
在对pending统计量进行合理评估之后,DBA是可以做出删除还是发布统计量的决定的。具体操作如下:
--删除pending信息
SQL> exec dbms_stats.delete_pending_stats(user,'T');
PL/SQL procedure successfully completed
SQL> select count(*) from user_tab_pending_stats;
COUNT(*)
----------
0
--重新收集pending统计量
SQL> exec dbms_stats.gather_table_stats(user,'T',cascade => true);
PL/SQL procedure successfully completed
SQL> select NUM_ROWS, BLOCKS EMPTY_BLOCKS, AVG_SPACE, CHAIN_CNT, AVG_ROW_LEN from user_tab_statistics where table_name='T';
NUM_ROWS EMPTY_BLOCKS AVG_SPACE CHAIN_CNT AVG_ROW_LEN
---------- ------------ ---------- ---------- -----------
0 0 0 0 0
--发布pending统计量
SQL> exec dbms_stats.publish_pending_stats(user,'T');
PL/SQL procedure successfully completed
SQL> select NUM_ROWS, BLOCKS EMPTY_BLOCKS, AVG_SPACE, CHAIN_CNT, AVG_ROW_LEN from user_tab_statistics where table_name='T';
NUM_ROWS EMPTY_BLOCKS AVG_SPACE CHAIN_CNT AVG_ROW_LEN
---------- ------------ ---------- ---------- -----------
72202 1028 0 0 96
单发布完统计量之后,就可以在正常的情况下使用统计量生成执行计划了。
SQL> show parameter optimizer_use_pen
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_use_pending_statistics boolean FALSE
SQL> alter session set optimizer_use_pending_statistics=false;
会话已更改。
已用时间: 00: 00: 00.01
SQL> select * from t where wner='SYS';
已选择58799行。
已用时间: 00: 00: 04.33
执行计划
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 58794 | 5511K| 281 (1)| 00:00:04 |
|* 1 | TABLE ACCESS FULL| T | 58794 | 5511K| 281 (1)| 00:00:04 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OWNER"='SYS')
统计信息
----------------------------------------------------------
426 recursive calls
0 db block gets
4975 consistent gets
0 physical reads
0 redo size
2392962 bytes sent via SQL*Net to client
43520 bytes received via SQL*Net from client
3921 SQL*Net roundtrips to/from client
4 sorts (memory)
0 sorts (disk)
58799 rows processed
6、结论
在11g中提出的pending statistic的方法,可以在生产运维和稳定优化执行计划方面,给我们提供帮助。
从11g开始,表与索引的统计信息收集完毕后,可以选择收集的统信息立即发布,也可以选择使新收集的统计信息处于pending状态,待确定处于pending状态的统计信息是安全的,再使处于pending状态的统计信息发布,这样就会避免一些因为收集统计信息立即发布而导致SQL执行计划走错的灾难。
在 11g 之前的版本中,DBMS_STATS 自动统计收集(Automatic Statistics Gathering)默认的阀值是 10%, 这个 10% 是不可以修改的。这对千变万化的企业数据库来说环境来说,有些死板,如果是个超大的表,默认的 10% 数据也是海量了,会把整个资源占死。Oracle 11g 中,这个属性可以通过修改 STALE_PERCENT 属性来修改, 有全局(DBMS_STATS.SET_GLOBAL_PREFS )和表级别(DBMS_STATS.SET_TABLE_PREFS)两种。
1 如何判断是否有pending的统计信息需要生效?
SQL> Select dbms_stats.get_prefs('PUBLISH') publish from dual;
PUBLISH
--------------------------
TRUE
dbms_stats的get_prefs函数返回true,表示对象的统计信息收集后立即生效,如果返回flase,收集的统计信息将处于pending状态。
2 如果查看相关的视图
A 立即生效的统计信息可以通过以下字典可以查看
user_tab_stats
user_ind_stats
B pending状态的统计信息可以通过以下字典可以查看
user_tab_pending_stats
user_ind_pending_stats
3 如何设置表或schema的统计信息的publish状态
用dbms_stats的set_table_prefs或者set_schema_prefs过程可以在表级或schema表设置它们的统计信息是否立即生效,当我们设置tmp_test表的统计信息收集后处于pending状态,那该表收集统计信息后,将存放于user_tab_pending_stats字典中。
SQL> Exec dbms_stats.set_table_prefs('yekai','tmp_test','publish','false');
PL/SQL procedure successfully completed.
SQL> select count(*) from user_tab_pending_stats;
COUNT(*)
----------
0
SQL> exec dbms_stats.gather_table_stats('yekai','tmp_test');
PL/SQL procedure successfully completed.
SQL> select count(*) from user_tab_pending_stats;
COUNT(*)
----------
1
4 如何测试并使用处于pending状态的统计信息
在11g,新的参数optimizer_pending_statistics将可以来解决这个问题,当我们在session级设置optimizer_pending_statistics为true时,我们就可以使用存放在user_*_pending_stats字典中的统计信息啦,当我们确保该处于pending状态的统计信息是正确时,我们就可以决定是否使它们立即生效。
SQL> alter session set optimizer_pending_statistics = TRUE;
5 如何发布处于pending状态的统计信息
当测试过统计信息有效后,我们可以选择发布pending状态的统计信息
SQL> exec dbms_stats.publish_pending_stats('yekai','tmp_test');
如果我们不需要该处于pending状态的统计信息,可以选择删除这个pending的统计信息
SQL> exec dbms_stats.publish_pending_stats('yekai','tmp_test');
在CBO时代,SQL语句的执行计划完全依赖于在数据字典中保存的统计量信息和优化器Optimizer的计算公式参数。从9i开始到现在的11gR2,我们说CBO优化器已经很成熟和完善。在通常情况下,我们的SQL都是可以获取到较好的执行计划以及执行效率的。
在实际工作中,我们经常会遇到执行计划低效的情况。但是这种故障根源中,绝大多数的原因在于统计量的错误或者失效。错误的统计量连带生成的就是不恰当的执行计划,以至于低效的执行过程。在9i时代,RBO和CBO混合使用,让我们经常需要自定义的统计量收集过程。
从10g开始,Oracle引入了自动收集统计量的作业,以保证数据字典中统计量正确反映数据对象状态。这在很大程度上,缓解了由于数据变化导致的统计量过期问题。但是,我们在实际工作中,还是会发现执行计划的突然变化。究其原因,就是某个时间点收集的统计量,也许不能反映数据的全貌(如中间表)。
1、统计量Pending
在系统运维中,我们常常希望维持SQL执行计划的稳定。很多DBA和开发人员对于hint的依赖,很大程度上也是源于对CBO情况下,执行计划对于统计量过于依赖,容易形成不稳定执行计划。
那么,我们SQL语句执行计划的稳定性,就变成统计量的稳定性问题。更进一步,就是新的统计量更新,无论是否手动收集还是自动收集,能否促进SQL语句生成更高效的执行计划。
所以,一种思路是:在新的统计量收集生成时,暂时不要生效投入执行计划生成。等待最后确认统计量正确之后,再投入生产环境。
在Oracle 11g中,推出了统计量管理的一种新技术——Pending Statistic技术,提供了这种功能。
简单的说,我们可以对一系列的数据表设置pending属性。设置pending属性之后,数据的统计量在数据字典中相当于已经锁定Lock住。但新统计量生成之后,不是直接替换原有的数据,而是存放在pending数据字典中。
在pending字典中的统计量,默认情况下是不会参与SQL执行计划的生产的。只有在进行SQL测试通过的时候,经过用户手工的确定,才会将其Publish出来,替换原有的统计量信息。
这样,就给我们运维DBA一种维持执行计划稳定的思路。通过固定统计量,将新统计量pending的方式将原有的统计量固定,从而稳定执行计划。进而,对pending的统计量进行测试,只有在更好执行计划的情况下,才会替换原有的方案。
下面,我们通过实验来验证pending统计量的使用。
2、实验环境构建
我们选择11gR2进行实验。
SQL> select * from v$version;
BANNER
-----------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
构建数据表T,以及对应的索引。注意,我们首先在数据表中不保存任何数据。
SQL> create table t as select * from dba_objects where 1=0;
Table created
SQL> create index idx_t_owner on t(owner);
Index created
SQL> create index idx_t_id on t(object_id);
Index created
在不显式的收集统计量的情况下,是没有对应的数据表统计量的。
SQL> select NUM_ROWS, BLOCKS EMPTY_BLOCKS, AVG_SPACE, CHAIN_CNT, AVG_ROW_LEN from user_tab_statistics where table_name='T';
NUM_ROWS EMPTY_BLOCKS AVG_SPACE CHAIN_CNT AVG_ROW_LEN
---------- ------------ ---------- ---------- -----------
SQL> select count(*) from user_tab_col_statistics where table_name='T';
COUNT(*)
----------
0
SQL> select BLEVEL, LEAF_BLOCKS, DISTINCT_KEYS, CLUSTERING_FACTOR NUM_ROWS from user_ind_statistics where index_name='IDX_T_OWNER';
BLEVEL LEAF_BLOCKS DISTINCT_KEYS NUM_ROWS
---------- ----------- ------------- ----------
0 0 0 0
收集统计量,获取最新的数据分布状况。
SQL> exec dbms_stats.gather_table_stats(user,'T',cascade => true);
PL/SQL procedure successfully completed
当我们修改数据内容,没有收集统计量,会存在新旧差异。
SQL> insert into t select * from dba_objects;
72202 rows inserted
SQL> commit;
Commit complete
SQL> select NUM_ROWS, BLOCKS EMPTY_BLOCKS, AVG_SPACE, CHAIN_CNT, AVG_ROW_LEN from user_tab_statistics where table_name='T';
NUM_ROWS EMPTY_BLOCKS AVG_SPACE CHAIN_CNT AVG_ROW_LEN
---------- ------------ ---------- ---------- -----------
0 0 0 0 0
3、Pending Statistics设置
在11g环境中,数据表、Schema都存在一个统计量相关参数PUBLISH,表示当有新统计量的时候,新统计量是否立即被publish出来,作为最新的统计信息使用。
该参数的默认值为TRUE。
SQL> select dbms_stats.get_prefs(pname => 'PUBLISH',ownname => 'SYS',tabname => 'T') from dual;
DBMS_STATS.GET_PREFS(PNAME=>'P
-------------------------------------------------------
TRUE
--设置数据表的publish参数取值;
SQL> exec dbms_stats.set_table_prefs(user,'T','PUBLISH','false');
PL/SQL procedure successfully completed
SQL> select dbms_stats.get_prefs('PUBLISH',ownname => 'SYS',tabname => 'T') from dual;
DBMS_STATS.GET_PREFS('PUBLISH'
--------------------------------------
FALSE
此时,数据表中已经包括了七万余条数据,重新收集统计量。
SQL> exec dbms_stats.gather_table_stats(user,'T',cascade => true);
PL/SQL procedure successfully completed
SQL> select NUM_ROWS, BLOCKS EMPTY_BLOCKS, AVG_SPACE, CHAIN_CNT, AVG_ROW_LEN from user_tab_statistics where table_name='T';
NUM_ROWS EMPTY_BLOCKS AVG_SPACE CHAIN_CNT AVG_ROW_LEN
---------- ------------ ---------- ---------- -----------
0 0 0 0 0
当我们将数据表T的PUBLISH参数修改为false之后,我们重新收集统计量,发现原有统计信息并没有连带的更新。
新统计量不是没有收集,而是被记录在了pending信息中。我们可以通过user_ind_pending_stats和user_tab_pending_stats两个视图查看被pending的统计量信息。
SQL> select NUM_ROWS, BLOCKS, AVG_ROW_LEN, SAMPLE_SIZE, LAST_ANALYZED from user_tab_pending_stats where table_name='T';
NUM_ROWS BLOCKS AVG_ROW_LEN SAMPLE_SIZE LAST_ANALYZED
---------- ---------- ----------- ----------- -------------
72202 1028 97 72202 2012/6/20 20:
SQL> select index_name, LEAF_BLOCKS, DISTINCT_KEYS, CLUSTERING_FACTOR,LAST_ANALYZED from user_ind_pending_stats where table_name='T';
INDEX_NAME LEAF_BLOCKS DISTINCT_KEYS CLUSTERING_FACTOR LAST_ANALYZED
------------------------------ ----------- ------------- ----------------- -------------
IDX_T_OWNER 293 23 1884 2012/6/20 20:
IDX_T_ID 256 72202 1665 2012/6/20 20:
4、Pending和SQL执行计划
新的统计量没有被publish出来。那么,在一般情况下,我们的SQL执行计划还是依据正式被publish的统计量生成。
SQL> explain plan for select * from t where wner='SYS';
Explained
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------
Plan hash value: 1516787156
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 207 | 1 (0)|
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 207 | 1 (0)|
|* 2 | INDEX RANGE SCAN | IDX_T_OWNER | 1 | | 1 (0)|
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OWNER"='SYS')
14 rows selected
实际执行情况;
SQL> select * from t where wner='SYS';
已选择58799行。
已用时间: 00: 00: 06.19
执行计划
----------------------------------------------------------
Plan hash value: 1516787156
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 207 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 207 | 1 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_T_OWNER | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OWNER"='SYS')
统计信息
----------------------------------------------------------
528 recursive calls
0 db block gets
8962 consistent gets
1108 physical reads
0 redo size
6291375 bytes sent via SQL*Net to client
43520 bytes received via SQL*Net from client
3921 SQL*Net roundtrips to/from client
4 sorts (memory)
0 sorts (disk)
58799 rows processed
SQL>
在sys用户下,行数比例超过了数据表T的绝大多数。按照CBO的原则,走全表扫描可能是较好的方法。但是,由于统计量还是在空表的状态下,所以,Oracle CBO认为Index路径会更好。
在Oracle中,存在一个参数optimizer_use_pending_statistics,用来控制当前是否使用pending的统计量来生成执行计划。作为运维DBA,可以通过这个参数暂时性的启用pending统计量,观察一下性能状况。再决定是否启用publish这些统计量。
默认情况下,该参数取值为false。我们可以在session级别设置下该参数为true。
SQL> show parameter optimizer_use_pending
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_use_pending_statistics boolean FALSE
修改参数为true之后,Oracle CBO在生成执行计划的时候就会使用Pending的统计量。
SQL> alter session set optimizer_use_pending_statistics=true;
Session altered
SQL> select value from v$parameter where name='optimizer_use_pending_statistics';
VALUE
------------------------------------------
TRUE
SQL> explain plan for select * from t where wner='SYS';
Explained
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 58274 | 5463K| 281 (1)| 00:00:04 |
|* 1 | TABLE ACCESS FULL| T | 58274 | 5463K| 281 (1)| 00:00:04 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OWNER"='SYS')
13 rows selected
SQL> select * from t where wner='SYS';
已选择58799行。
已用时间: 00: 00: 04.68
执行计划
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 58274 | 5463K| 281 (1)| 00:00:04 |
|* 1 | TABLE ACCESS FULL| T | 58274 | 5463K| 281 (1)| 00:00:04 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OWNER"='SYS')
统计信息
----------------------------------------------------------
7511 recursive calls
50 db block gets
6599 consistent gets
1118 physical reads
0 redo size
2392962 bytes sent via SQL*Net to client
43520 bytes received via SQL*Net from client
3921 SQL*Net roundtrips to/from client
211 sorts (memory)
0 sorts (disk)
58799 rows processed
果然,设置参数后,Oracle生成了FTS路径,说明更新的统计量起了作用。同时,执行时间减少了近2秒钟,说明结果上也确实是生成了更好的执行计划。
5、Pending统计量的后续处理
在对pending统计量进行合理评估之后,DBA是可以做出删除还是发布统计量的决定的。具体操作如下:
--删除pending信息
SQL> exec dbms_stats.delete_pending_stats(user,'T');
PL/SQL procedure successfully completed
SQL> select count(*) from user_tab_pending_stats;
COUNT(*)
----------
0
--重新收集pending统计量
SQL> exec dbms_stats.gather_table_stats(user,'T',cascade => true);
PL/SQL procedure successfully completed
SQL> select NUM_ROWS, BLOCKS EMPTY_BLOCKS, AVG_SPACE, CHAIN_CNT, AVG_ROW_LEN from user_tab_statistics where table_name='T';
NUM_ROWS EMPTY_BLOCKS AVG_SPACE CHAIN_CNT AVG_ROW_LEN
---------- ------------ ---------- ---------- -----------
0 0 0 0 0
--发布pending统计量
SQL> exec dbms_stats.publish_pending_stats(user,'T');
PL/SQL procedure successfully completed
SQL> select NUM_ROWS, BLOCKS EMPTY_BLOCKS, AVG_SPACE, CHAIN_CNT, AVG_ROW_LEN from user_tab_statistics where table_name='T';
NUM_ROWS EMPTY_BLOCKS AVG_SPACE CHAIN_CNT AVG_ROW_LEN
---------- ------------ ---------- ---------- -----------
72202 1028 0 0 96
单发布完统计量之后,就可以在正常的情况下使用统计量生成执行计划了。
SQL> show parameter optimizer_use_pen
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_use_pending_statistics boolean FALSE
SQL> alter session set optimizer_use_pending_statistics=false;
会话已更改。
已用时间: 00: 00: 00.01
SQL> select * from t where wner='SYS';
已选择58799行。
已用时间: 00: 00: 04.33
执行计划
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 58794 | 5511K| 281 (1)| 00:00:04 |
|* 1 | TABLE ACCESS FULL| T | 58794 | 5511K| 281 (1)| 00:00:04 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OWNER"='SYS')
统计信息
----------------------------------------------------------
426 recursive calls
0 db block gets
4975 consistent gets
0 physical reads
0 redo size
2392962 bytes sent via SQL*Net to client
43520 bytes received via SQL*Net from client
3921 SQL*Net roundtrips to/from client
4 sorts (memory)
0 sorts (disk)
58799 rows processed
6、结论
在11g中提出的pending statistic的方法,可以在生产运维和稳定优化执行计划方面,给我们提供帮助。
发表评论
-
Oracle 10g 的clusterware 32位 下载地址
2013-04-19 23:03 1213Oracle 10g 的clusterware 32位 下载地 ... -
oracle 分析函数 RANK()
2013-04-11 00:05 1099RANK()既是一个聚合函数,也是一个分析函数 其具体的语法 ... -
oracle 分析函数
2013-04-09 23:25 1174分析函数是用于计算一组中多行的聚合值,与聚合函数的区别在于聚合 ... -
批量执行 bulk collect与forall用法
2013-04-08 23:49 1394BULK COLLECT 子句会批量检 ... -
pl/sql集合类型
2013-03-26 10:12 1574--集合类型 /* 单行单列的数据,使用标量变量 单行 ... -
oracle 行链接与行迁移
2013-03-16 01:06 1105表里的一行对于一个数据块太大的情况有二种(一行在一个数据块里放 ... -
oracle Health Monitor
2013-01-20 00:02 1613About Health Monitor Beginning ... -
oracle moving window size与 AWR retention period关系
2013-01-19 15:58 8486转自: http://tomszrp.itpub.net/po ... -
Oracle11.2新特性之INSERT提示IGNORE_ROW_ON_DUPKEY_INDEX
2013-01-12 00:20 2914insert提示IGNORE_ROW_ON_DUPKEY_IN ... -
oracle 11g新特性Flashback data archive
2013-01-09 22:52 30871. 什么是flashback data archive F ... -
RMAN List和report 命令
2012-12-25 00:07 2898LIST 命令 使用RMAN LIST 命令显示有关资料档案库 ... -
oracle ASM中ASM_POWER_LIMIT参数
2012-12-24 23:46 6432ASM_POWER_LIMIT 该初始化参数用于指定ASM例程 ... -
oracle I/O 从属进程
2012-12-24 23:24 1426I/O 从属进程 I/O从 ... -
easy connect 之 ORA-12154: TNS: 无法解析指定的连接标识符
2012-12-19 23:43 5640用easy connect连接出现“tns无法解析指定的连接标 ... -
Flashback Database --闪回数据库
2012-12-19 23:38 1392Flashback 技术是以Undo segment中的内容为 ... -
Oracle 11g新特性:Automatic Diagnostic Repository
2012-12-19 22:35 1395Oracle Database 11g的FDI(Fault D ... -
RMAN配置中通道(CHANNEL)相关参数 PARALLELISM 、FILESPERSET的关系
2012-12-19 22:09 2699RMAN配置中通道(CHANNEL)相 ... -
oracle 空间RESUMABLE
2012-12-14 22:05 3061空间RESUMABLE操作 转 Oracle从9i开始 ... -
oracle 创建视图 with check option
2012-12-13 23:14 1529我们来看下面的例子: create or replace vi ... -
flashback transaction闪回事务查询
2012-11-26 22:00 1505闪回事务查询有别于闪回查询的特点有以下3个: (1) ...
相关推荐
Oracle闪回特性是Oracle数据库提供的一种强大的数据恢复和时间旅行功能,它允许用户查看和恢复到数据库的某个历史状态,而无需依赖传统的备份和恢复过程。这一特性在故障排查、误操作恢复以及历史数据分析等方面有着...
DBus Pending Call是DBus框架中的一个重要概念,它在进程间通信(IPC)中起到关键作用。DBus是一个开放源代码的消息总线系统,允许不同应用程序之间进行通信。在DBus中,Pending Call是一个代表正在进行中的方法调用...
1. 修改TUXEDO安装路径下`udataobj`目录的`RM`文件,取消以`Oracle_XA:xaosw:`开头的行的注释,并添加新的配置行,指示ORACLE的库文件路径。 - 在Windows环境下,配置示例如下: ```text Oracle_XA;xaosw;d:ora81...
根据提供的文档信息,本文将详细解析"WPS6.1.2基于Oracle10g Solaris 64位操作系统集群安装向导"中的关键知识点,主要包括数据库环境准备、WPS集群安装配置以及一些必要的补充说明。 ### 数据库环境准备 #### 1.1 ...
DBA_2PC_PENDING 是Oracle数据库中与分布式事务管理相关的一个重要视图,它用于跟踪和管理尚未完成的两阶段提交(Two-Phase Commit, 2PC)分布式事务。在分布式数据库环境中,2PC协议确保所有参与节点的数据一致性,...
- **R12的供应商定义与维护**:ORACLE EBS R12版本中供应商管理的新特性和改进。 - **供应商的合并**:合并重复的供应商记录,避免数据冗余。 #### 客户(Customer) 客户管理是企业与市场互动的纽带,直接影响...
第一部分 数据库体系结构 第1章 Oracle体系结构 1 1.1 数据库概述及实例 1 1.2 数据库 1 1.2.1 表空间 2 1.2.2 文件 2 1.3 实例 3 1.4 数据库内部结构 3 1.4.1 表、列和数据类型 4 1.4.2 约束条件 5 1.4.3 抽象数据...
Oracle系统视图是Oracle数据库数据字典的重要组成部分,它们提供了关于数据库结构、状态以及活动的详细信息。这些视图对于数据库管理员(DBA)来说至关重要,因为它们可以帮助监控和管理数据库,解决性能问题,以及...
"Laravel开发-pending-migration-advisor"是一个专门为Laravel项目设计的工具,它帮助开发者识别并处理待执行的迁移文件,确保数据库与应用代码保持同步。这个工具可能是通过检查`database/migrations`目录中的迁移...
为了保持网站的正常运行,用户应定期检查并更新插件,因为开发者会发布新版本来修复安全漏洞、提升性能或者添加新特性。同时,定期备份数据库和文件是必要的,以防插件更新出现问题时能够快速恢复。 总的来说,...
关于“老白ORACLE面试题”中所涉及的知识点,我们首先会看到ORACLE数据库的高级概念,面试题围绕了多个主题,如性能优化、故障诊断、系统分析等,这要求面试者对ORACLE数据库有深入的理解和实践经验。以下是对每个...
首先,通过 Oracle 的 DBCA (Database Configuration Assistant) 工具来创建一个新的名为 `portaldb` 的数据库。在创建过程中,需注意以下几点: - 数据库语言设置:虽然官方推荐使用 UTF-8,但如果仅需支持中文,...
自 Oracle 9i 版本开始,Oracle Text 成为了数据库的一个内置特性,它允许用户通过 SQL 语句进行复杂的全文搜索操作。 - **历史沿革**: - 在 Oracle 8/8i 中称为 Oracle InterMedia Text; - 在 Oracle 8 之前...
在Oracle数据库中,你需要创建一个名为ESB_DEV的用户,赋予其连接、资源、无限制表空间的权限,以及创建视图、Java用户权限、执行DBMS_LOCK包和查询DBA_PENDING_TRANSACTIONS表的权限。执行以下SQL语句来创建和授权...
Oracle EBS(Enterprise Business Suite)是Oracle公司推出的一套全面的企业级应用软件,它涵盖了财务管理、供应链管理、项目管理等多个领域。在这个系统中,基础设置和基础数据是至关重要的组成部分,因为它们为...
该脚本的核心代码 "Pending orders UP.mq5" 使用的是MetaQuotes Language 5 (MQL5) 编程语言,这是MT5平台专用的一种脚本语言,具备高级编程特性,如面向对象编程和函数库支持。通过编写MQL5代码,开发者可以创建...
Oracle EBS(Enterprise Business Suite)系统是Oracle公司提供的一个全面的企业资源规划(ERP)解决方案,其主数据管理是系统核心功能之一。主数据是企业运营的基础,它涉及到多个业务流程和部门之间的共享信息。在...
Oracle EBS(Enterprise Business Suite)系统中的主数据管理是一个关键组成部分,尽管官方文档中并未明确提及“主数据”这一概念,但在这个系统中,主数据指的是全局性、基础性且对业务操作至关重要的数据,例如...
设置为离线状态时,Oracle会将回滚段的状态设置为PENDING OFFLINE,此时可以通过查询V$ROLLSTAT视图来查看回滚段的状态。 ##### 3. 调整回滚段的参数 可以通过以下命令修改回滚段的属性,例如最大扩展数: ```sql...
then方法返回一个新的Promise对象,因此可以链式调用。每个then方法都可以指定成功和失败的回调,当上一个Promise被解决(成功或失败)时,会触发下一个then中的回调。这种链式调用使得异步操作的流程清晰,易于...