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;
Predicate Information (identified by operation id): ---------------------------------------------------
1 - access("SADDR"="S"."ADDR" AND TO_CHAR(USERENV('INSTANCE'))||RAWTOHEX(" RADDR")=TO_CHAR("R"."INST_ID")||RAWTOHEX("R"."ADDR")) 4 - filter(USERENV('INSTANCE') IS NOT NULL) 7 - filter(("KSQLKMOD">>0 OR "KSQLKREQ">>0) AND "INST_ID"=USERENV('INSTANCE') AND BITAND("KSSOBFLG",1)>>0) 8 - filter(("KSQLKMOD">>0 OR "KSQLKREQ">>0) AND "INST_ID"=USERENV('INSTANCE') AND BITAND("KSSOBFLG",1)>>0) 9 - filter(("KSQLKMOD">>0 OR "KSQLKREQ">>0) AND "INST_ID"=USERENV('INSTANCE') AND BITAND("KSSOBFLG",1)>>0) 10 - filter(("KSQLKMOD">>0 OR "KSQLKREQ">>0) AND "INST_ID"=USERENV('INSTANCE') AND BITAND("KSSOBFLG",1)>>0) 11 - filter(("KSQLKMOD">>0 OR "KSQLKREQ">>0) AND "INST_ID"=USERENV('INSTANCE') AND BITAND("KSSOBFLG",1)>>0) 12 - filter(("KSQLKMOD">>0 OR "KSQLKREQ">>0) AND "INST_ID"=USERENV('INSTANCE') AND BITAND("KSSOBFLG",1)>>0) 13 - filter(("KSQLKMOD">>0 OR "KSQLKREQ">>0) AND "INST_ID"=USERENV('INSTANCE') AND BITAND("KSSOBFLG",1)>>0) 14 - filter(("KSQLKMOD">>0 OR "KSQLKREQ">>0) AND "INST_ID"=USERENV('INSTANCE') AND BITAND("KSSOBFLG",1)>>0) 15 - filter(("KSQLKMOD">>0 OR "KSQLKREQ">>0) AND "INST_ID"=USERENV('INSTANCE') AND BITAND("KSSOBFLG",1)>>0) 16 - filter(("KSQLKMOD">>0 OR "KSQLKREQ">>0) AND "INST_ID"=USERENV('INSTANCE') AND BITAND("KSSPAFLG",1)>>0) 18 - filter("S"."INST_ID"=USERENV('INSTANCE'))
Statistics ---------------------------------------------------------- 0 recursive calls 2 db block gets 0 consistent gets 0 physical reads 0 redo size 2780 bytes sent via SQL*Net to client 545 bytes received via SQL*Net from client 4 SQL*Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 36 rows processed
SYS@xb> begin dbms_stats.gather_table_stats('SYS','x$ksuse',method_opt=>'for all columns size 1'); dbms_stats.gather_table_stats('SYS','x$ksqrs',method_opt=>'for all columns size 1'); end; 5 /
PL/SQL procedure successfully completed.
查看x$表的统计信息情况
1 2 3 4 5
SYS@xb> select OWNER, TABLE_NAME, LAST_ANALYZED from dba_tab_statistics where table_name='X$KSUSE';
Predicate Information (identified by operation id): ---------------------------------------------------
1 - access(TO_CHAR(USERENV('INSTANCE'))||RAWTOHEX("RADDR")=TO_CHAR("R"."INS T_ID")||RAWTOHEX("R"."ADDR")) 2 - access("SADDR"="S"."ADDR") 3 - filter("S"."INST_ID"=USERENV('INSTANCE')) 6 - filter(USERENV('INSTANCE') IS NOT NULL) 9 - filter(("KSQLKMOD">>0 OR "KSQLKREQ">>0) AND BITAND("KSSOBFLG",1)>>0 AND "INST_ID"=USERENV('INSTANCE')) 10 - filter(BITAND("KSSOBFLG",1)>>0 AND ("KSQLKMOD">>0 OR "KSQLKREQ">>0) AND "INST_ID"=USERENV('INSTANCE')) 11 - filter(("KSQLKMOD">>0 OR "KSQLKREQ">>0) AND "INST_ID"=USERENV('INSTANCE') AND BITAND("KSSOBFLG",1)>>0) 12 - filter(("KSQLKMOD">>0 OR "KSQLKREQ">>0) AND "INST_ID"=USERENV('INSTANCE') AND BITAND("KSSOBFLG",1)>>0) 13 - filter(("KSQLKMOD">>0 OR "KSQLKREQ">>0) AND "INST_ID"=USERENV('INSTANCE') AND BITAND("KSSOBFLG",1)>>0) 14 - filter(("KSQLKMOD">>0 OR "KSQLKREQ">>0) AND "INST_ID"=USERENV('INSTANCE') AND BITAND("KSSOBFLG",1)>>0) 15 - filter(("KSQLKMOD">>0 OR "KSQLKREQ">>0) AND "INST_ID"=USERENV('INSTANCE') AND BITAND("KSSOBFLG",1)>>0) 16 - filter(("KSQLKMOD">>0 OR "KSQLKREQ">>0) AND "INST_ID"=USERENV('INSTANCE') AND BITAND("KSSOBFLG",1)>>0) 17 - filter(("KSQLKMOD">>0 OR "KSQLKREQ">>0) AND "INST_ID"=USERENV('INSTANCE') AND BITAND("KSSOBFLG",1)>>0) 18 - filter(("KSQLKMOD">>0 OR "KSQLKREQ">>0) AND "INST_ID"=USERENV('INSTANCE') AND BITAND("KSSPAFLG",1)>>0)
Statistics ---------------------------------------------------------- 1 recursive calls 2 db block gets 0 consistent gets 0 physical reads 0 redo size 2876 bytes sent via SQL*Net to client 545 bytes received via SQL*Net from client 4 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 38 rows processed