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;
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;
