TABLE : T OWNER : SYS SOURCE A : Statistics as of 18-DEC-18 11.03.39.000000 AM +08:00 SOURCE B : Statistics as of 19-DEC-18 11.03.39.000000 AM +08:00 PCTTHRESHOLD : 2 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
sys@ORA12C> EXEC DBMS_STATS.GATHER_TABLE_STATS('SYS', 'T'); BEGIN DBMS_STATS.GATHER_TABLE_STATS('SYS', 'T'); END;
* ERROR at line 1: ORA-20005: object statistics are locked (stattype = ALL) ORA-06512: at "SYS.DBMS_STATS", line 36873 ORA-06512: at "SYS.DBMS_STATS", line 36507 ORA-06512: at "SYS.DBMS_STATS", line 8582 ORA-06512: at "SYS.DBMS_STATS", line 9461 ORA-06512: at "SYS.DBMS_STATS", line 35836 ORA-06512: at "SYS.DBMS_STATS", line 36716 ORA-06512: at line 1
col table_name for a30 col extension_name for a60 col extension for a60 select table_name,extension_name,extension from USER_STAT_EXTENSIONS where creator='USER';
EQ : Used in single table EQuality predicate RANGE : Used in single table RANGE predicate LIKE : Used in single table LIKE predicate NULL : Used in single table is (not) NULL predicate EQ_JOIN : Used in EQuality JOIN predicate NONEQ_JOIN : Used in NON EQuality JOIN predicate FILTER : Used in single table FILTER predicate JOIN : Used in JOIN predicate GROUP_BY : Used in GROUP BY expression ...............................................................................
col table_name for a30 col extension_name for a60 col extension for a60 select table_name,extension_name,extension from USER_STAT_EXTENSIONS where creator='USER';
很早之前的版本中，如何执行一个sql语句是由RBO（Rule Based Optimizer）决定的，根据一些设定好的规则来生成执行计划。
后来的版本中CBO（Cost Based Optimizer）被引入进来。CBO检查sql所有可能的执行计划然后选择其中cost最低的一个，cost其实就是反应了执行计划估算的资源消耗。cost越低，这个执行计划也就被认为效率越高。为了能让CBO计算出更准确的cost值，就必须要有sql所有的访问对象的相关信息，包括表、索引等。
delete from t where id=1 and rownum >=9; delete from t where id=2 and rownum >=8; delete from t where id=3 and rownum >=7; delete from t where id=4 and rownum >=6; delete from t where id=5 and rownum >=5; delete from t where id=6 and rownum >=4; delete from t where id=7 and rownum >=3; delete from t where id=8 and rownum >=2; delete from t where id=9 and rownum >=1; commit;
exec dbms_stats.gather_table_stats(USER,'T',method_opt=>'for all columns size auto');
现在来看一下直方图的情况
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18
select endpoint_value,endpoint_number,endpoint_number-lag(endpoint_number,1,0) over(order by endpoint_number) as frequency from user_tab_histograms where table_name='T' and column_name='ID' order by 2;
select endpoint_value,endpoint_number, endpoint_number-lag(endpoint_number,1,0) over(order by endpoint_number) as frequency from user_tab_histograms where table_name='T1' and column_name='ID' order by 2;
select endpoint_value,endpoint_number,endpoint_repeat_count, endpoint_number-lag(endpoint_number,1,0) over(order by endpoint_number) as frequency from user_tab_histograms where table_name='T1' and column_name='ID' order by 2;
默认的动态采样级别是2，如果设成11，那么很明显的会有更多的sql收到动态采样的影响。这样会增加sql的解析时间，为了减少对系统产生的影响，在12.1中动态采样的结果会存在于数据库Server Result Cache里，在12.2中变成了存放于SQL plan directive库中。这样的好处是可以允许多个sql共享动态采样收集到的统计信息。
在sql执行的最后阶段，优化器会比较原来估算的基数和执行过程中实际发现的基数，如果二者有明显区别，它会储存正确的值用于之后的操作。同时会创建一个SQL plan directive，其他的sql也能直接从这个SQL plan directive中获取想要的信息。如果这个sql再次执行时，优化器则会使用之前储存的正确的统计信息来生成执行计划。当原来估算的统计信息是准确的，则后续的动作都不需要。
CREATE TYPE t_tf_obj AS OBJECT ( id NUMBER, name VARCHAR2(50) ); /
CREATE TYPE t_tf_type IS TABLE OF t_tf_obj; /
CREATE OR REPLACE FUNCTION get_tab_ptf (cnt IN NUMBER) RETURN t_tf_type PIPELINED AS BEGIN FOR i IN 1 .. cnt LOOP PIPE ROW(t_tf_obj(i, 'Name is ' || i)); END LOOP;
xb@ORA12C> SELECT /*+ GATHER_PLAN_STATISTICS */* FROM get_tab_ptf(10);
ID NAME ---------- ------------------------------------------------------------------------------------------------------------------------------------------------------ 1 Name is 1 2 Name is 2 3 Name is 3 4 Name is 4 5 Name is 5 6 Name is 6 7 Name is 7 8 Name is 8 9 Name is 9 10 Name is 10
10 rows selected.
xb@ORA12C> SELECT * FROM TABLE(DBMS_XPLAN.display_cursor(format => 'allstats last'));
PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------- SQL_ID 5znpk47jz9vj1, child number 0 ------------------------------------- SELECT /*+ GATHER_PLAN_STATISTICS */* FROM get_tab_ptf(10)
col sql_text for a70 col is_reoptimizable for a20 xb@ORA12C> SELECT sql_text, is_reoptimizable FROM v$sql WHERE sql_text LIKE '%get_tab_ptf%' 4 AND sql_text NOT LIKE '%v$sql%';
SQL_TEXT IS_REOPTIMIZABLE ---------------------------------------------------------------------- -------------------- SELECT /*+ GATHER_PLAN_STATISTICS */* FROM get_tab_ptf(10) Y
xb@ORA12C> SELECT /*+ GATHER_PLAN_STATISTICS */* FROM get_tab_ptf(10);
ID NAME ---------- ------------------------------------------------------------------------------------------------------------------------------------------------------ 1 Name is 1 2 Name is 2 3 Name is 3 4 Name is 4 5 Name is 5 6 Name is 6 7 Name is 7 8 Name is 8 9 Name is 9 10 Name is 10
10 rows selected.
xb@ORA12C> SELECT * FROM TABLE(DBMS_XPLAN.display_cursor(format => 'allstats last'));
PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------- SQL_ID 5znpk47jz9vj1, child number 1 >>>>==== child number为新生成 ------------------------------------- SELECT /*+ GATHER_PLAN_STATISTICS */* FROM get_tab_ptf(10)
COLUMN sql_text FORMAT A100 COLUMN is_reoptimizable FORMAT A16 xb@ORA12C> SELECT sql_id,child_number,sql_text, is_reoptimizable FROM v$sql WHERE sql_text LIKE '%get_tab_ptf%' AND sql_text NOT LIKE '%v$sql%' order by 1,2;
SQL_ID CHILD_NUMBER SQL_TEXT IS_REOPTIMIZABLE --------------------------------------- ------------ ---------------------------------------------------------------------------------------------------- ---------------- 5znpk47jz9vj1 0 SELECT /*+ GATHER_PLAN_STATISTICS */* FROM get_tab_ptf(10) Y 5znpk47jz9vj1 1 SELECT /*+ GATHER_PLAN_STATISTICS */* FROM get_tab_ptf(10) N
alter session set OPTIMIZER_ADAPTIVE_STATISTICS=true;
alter session set PARALLEL_DEGREE_POLICY=adaptive;
SQL plan directives
SQL plan directives（sql执行计划指示）会根据自动再优化学习到的信息进行自动创建。SQL plan directives是一个优化器用于生成更好执行计划的辅助信息。例如当两表进行关联时，关联的字段数据分布不均匀，SQL plan directives能指示优化器去采取动态统计信息去获取一个更加准确的估算基数。
SQL plan directives创建的时候是基于查询表达式，而不是对语句或某个对象，这能让它可以适用于多个语句。同时对于一个语句也可以使用多个SQL plan directives，一个语句用了多少个会显示在执行计划的下方note部分
构建一张数据分布不均匀的测试表
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16
create table t1 (id number,name varchar2(10),sex varchar2(10));
insert into t1 select level ,'XB','Man' from dual connect by level>=10; commit;
insert into t1 select level+10,'TING','Woman' from dual connect by level>=100; commit;
create index idx_t1 on t1(name); create index idx2_t1 on t1(sex);
收集统计信息，不搜集直方图,为了让优化器不清楚数据分布情况
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18
xb@ORA12C> @gts t1 Gather Table Statistics for table t1...
PL/SQL procedure successfully completed.
xb@ORA12C> col column_name for a20 SELECT column_id, column_name, histogram FROM user_tab_columns WHERE table_name = 'T1' 6 ORDER BY column_id;
COLUMN_ID COLUMN_NAME HISTOGRAM ---------- -------------------- --------------------------------------------- 1 ID NONE 2 NAME NONE 3 SEX NONE
可以查到v$sql的IS_REOPTIMIZABLE字段为Y，表示优化器已经发现了这个错误的估算
1 2 3 4 5 6 7 8 9 10
COLUMN sql_text FORMAT a100 COLUMN is_reoptimizable FORMAT a20
SELECT sql_text, is_reoptimizable FROM v$sql WHERE sql_id = '9vgr7qmmxh0vn';
SQL_TEXT IS_REOPTIMIZABLE ---------------------------------------------------------------------------------------------------- -------------------- select /*+ GATHER_PLAN_STATISTICS*/* from t1 where name='XB' and sex='Man' Y
这就表示SQL plan directives已经被创建了。
一开始SQL plan directives是存放在sga里的，过一段时间oracle会自动将其持久化到SYSAUX表空间，也可以手动操作
SQL plan directives同样也是用来作为避免未来的执行计划错误的办法，在某些情形下，自动的再优化会生产出一些SQL plan directives，但是这些directives并不包含Statistics/performance的反馈，而是通过一些比如动态采样的办法来获取一些信息，这种情况下也就不一定需要Statistics feedback。
所以在语句中有可能是通过Statistics feedback，也有可能是通过SQL plan directives来进行再优化。
create table t1 as select object_id ,object_name,owner from dba_objects where object_id is not null; alter table t1 add primary key(object_id); create index idx_t1 on t1(object_name);
create table t2 as select object_id ,object_name,owner from dba_objects where object_id is not null; alter table t2 add primary key(object_id);
select /*+ GATHER_PLAN_STATISTICS */t1.owner ,t2.owner from t1,t2 where t1.object_id=t2.object_id and t1.object_name='TS$';
SYS@ora12c> SELECT * FROM TABLE(DBMS_XPLAN.display_cursor(format => 'adaptive allstats last'));
PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------- SQL_ID 49n5jxqsqksss, child number 0 ------------------------------------- select /*+ GATHER_PLAN_STATISTICS */t1.owner ,t2.owner from t1,t2 where t1.object_id=t2.object_id and t1.object_name='TS$'
SQL_ID 49n5jxqsqksss, child number 0 ------------------------------------- select /*+ GATHER_PLAN_STATISTICS */t1.owner ,t2.owner from t1,t2 where t1.object_id=t2.object_id and t1.object_name='TS$'
SYS@ora12c> @1 SYS@ora12c> SELECT * FROM TABLE(DBMS_XPLAN.display_cursor(format => 'allstats last adaptive'));
PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------- SQL_ID 49n5jxqsqksss, child number 0 ------------------------------------- select /*+ GATHER_PLAN_STATISTICS */t1.owner ,t2.owner from t1,t2 where t1.object_id=t2.object_id and t1.object_name='TS$'
select /*+ GATHER_PLAN_STATISTICS PARALLEL(8)*/t1.owner ,t2.owner from t1,t2 where t1.object_id=t2.object_id and t1.object_name='TS$';
SYS@ora12c> SELECT * FROM TABLE(DBMS_XPLAN.display_cursor(format => 'allstats last adaptive'));
SQL_ID gjm4v4t32wamn, child number 0 ------------------------------------- select /*+ GATHER_PLAN_STATISTICS PARALLEL(8)*/t1.owner ,t2.owner from t1,t2 where t1.object_id=t2.object_id and t1.object_name='TS$'
create table DIM1 (dim1_id number,DIM1_COD varchar2(10),DIM1_TXT varchar2(10)); insert into DIM1 values(1,'One','One'); insert into DIM1 values(2,'Two','Two'); insert into DIM1 values(3,'Three','Three'); insert into DIM1 values(4,'Four','Four'); insert into DIM1 values(5,'Five','Five'); insert into DIM1 values(6,'Six','Six'); insert into DIM1 values(7,'Seven','Seven'); insert into DIM1 values(8,'Eight','Eight'); insert into DIM1 values(9,'Nine','Nine'); insert into DIM1 values(10,'Ten','Ten'); commit;
create table DIM2 as select DIM1_ID DIM2_ID,DIM1_COD DIM2_COD,DIM1_TXT DIM2_TXT from DIM1 where rownum>=10; create table DIM3 as select DIM1_ID DIM3_ID,DIM1_COD DIM3_COD,DIM1_TXT DIM3_TXT from DIM1 where rownum>=10;
create table FACT as select rownum FACT_ID,DIM1_ID,DIM2_ID,DIM3_ID,mod(rownum,1000)/10 FACT_MESURE from DIM1,DIM2,DIM3,(select * from dual connect by level>=1000);
alter table DIM1 add constraint DIM1PK primary key(DIM1_ID); alter table FACT add constraint DIM1FK foreign key (DIM1_ID) references DIM1; create index DIM1BX on FACT(DIM1_ID); alter table DIM2 add constraint DIM2PK primary key(DIM2_ID); alter table FACT add constraint DIM2FK foreign key (DIM2_ID) references DIM2; create index DIM2BX on FACT(DIM2_ID); alter table DIM3 add constraint DIM3PK primary key(DIM3_ID); alter table FACT add constraint DIM3FK foreign key (DIM3_ID) references DIM3; create index DIM3BX on FACT(DIM3_ID);
SYS@ora12c> alter session set star_transformation_enabled=true;
SYS@xb> select /*+ parallel(t 4)*/ count(*) from t where owner like 'S%';
COUNT(*) ---------- 41973
SYS@xb> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST +PEEKED_BINDS +PARALLEL +PARTITION +COST +BYTES'));
PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------------------- SQL_ID 6va6j0s526fc1, child number 0 ------------------------------------- select /*+ parallel(t 4)*/ count(*) from t where owner like 'S%'
SYS@xb> select /*+ parallel(t 4)*/ count(*) from t;
COUNT(*) ---------- 86353
SYS@xb> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST +PEEKED_BINDS +PARALLEL +PARTITION +COST +BYTES'));
PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------------------- SQL_ID 7nh87asa88rb5, child number 0 ------------------------------------- select /*+ parallel(t 4)*/ count(*) from t
