cf(聚簇因子)调优，计划调优,SQL改造

---------------1、---------数据、检测准备--------------------------------------------
drop table TEST1;
drop table TEST2;
--select OBJECT_ID,OBJECT_NAME,owner,created,last_ddl_time,namespace from TEST1 a;

create table TEST1 as select * from all_objects a where OBJECT_ID<=500000 order by a.OBJECT_NAME;
create table TEST2 as select * from all_objects a where OBJECT_ID<=500000 order by a.OBJECT_ID desc;

create index TEST1_IDX on TEST1(OBJECT_ID);
create index TEST2_IDX on TEST2(OBJECT_ID);


select * from TEST1;
select * from TEST2;

select count(1) from TEST1;
select count(1) from TEST2;
delete from test1 a where a.OBJECT_ID>1000000;
delete from TEST2 a where a.OBJECT_ID>1000;

declare
i NUMBER;
begin
for i in 1..1000000 loop
INSERT INTO TEST1(OBJECT_ID,OBJECT_NAME,owner,created,last_ddl_time,namespace) VALUES(13333334448+i,'杭州','HS-D90',ADD_MONTHS(SYSDATE,7),ADD_MONTHS(SYSDATE,7),i);
end LOOP;
END;

declare
i NUMBER;
begin
for i in 1..1000000 loop
INSERT INTO TEST2(OBJECT_ID,OBJECT_NAME,owner,created,last_ddl_time,namespace) VALUES(13333334448+i,'深圳','HS-D90',ADD_MONTHS(SYSDATE,7),ADD_MONTHS(SYSDATE,7),i);
end LOOP;
END;


EXEC DBMS_STATS.gather_index_stats(USER, 'TEST1_IDX');
EXEC DBMS_STATS.gather_index_stats(USER, 'TEST2_IDX');
EXEC dbms_stats.gather_table_stats(USER,'TEST1');
EXEC dbms_stats.gather_table_stats(USER,'TEST2');
select b.table_name,
a.index_name,
b.num_rows,
b.blocks,
a.clustering_factor
from user_indexes a, user_tables b
where b.table_name in ('TEST1', 'TEST2')
and a.table_name = b.table_name;

----------------------------------------------------------

---2、-----执行比较------------------------------------------------------------------------------
--1、TEST2 cf值与block值相同情况下校验(表数据1万,5万,5万+,百万+ 比较) cf很好
--去重性能高 rownum cg 136 740 1011 百万(0 recursive calls 0 db block gets 8470 consistent gets 8466 physical reads)
select t.OBJECT_ID,t.OBJECT_NAME,t.owner
from (select a.OBJECT_ID,a.OBJECT_NAME,a.owner,
row_number() over(partition by a.OBJECT_ID order by rownum) rn
from TEST2 a) t
where t.rn = 1;


--去重比较 rowid 性能中等 cg 795 856 1169 百万（83 recursive calls 305 db block gets 11016 consistent gets 18759 physical reads）
select a.OBJECT_ID,a.OBJECT_NAME,a.owner
from TEST2 a
where a.rowid =
(select min(rowid) from TEST2 b where b.OBJECT_ID = a.OBJECT_ID);



--去重查询方法二：根据rownum,性能差 cg 101450 太慢不用测了
select *
from (select a.OBJECT_ID,a.OBJECT_NAME,a.owner, rownum rn from TEST1 a) x
where x.rn in (select min(rownum) from TEST2 group by OBJECT_ID)
order by OBJECT_ID asc;
-------------------------------------------------------------

--2、TEST1 cf值与rows相近情况下校验(表数据1万，5万,5万+,百万+ 比较)，cf很差

--去重性能高 rownum cg 136 740 1011 百万(43 recursive calls 3 db block gets 55363 consistent gets 60750 physical reads)
select t.OBJECT_ID,t.OBJECT_NAME,t.owner
from (select a.OBJECT_ID,a.OBJECT_NAME,a.owner,
row_number() over(partition by a.OBJECT_ID order by rownum) rn
from TEST1 a) t
where t.rn = 1;


--去重比较 rowid 性能中等 cg 797 856 1169 百万(34 recursive calls 0 db block gets 74334 consistent gets 76136 physical reads)
select a.OBJECT_ID,a.OBJECT_NAME,a.owner
from TEST1 a
where a.rowid =
(select min(rowid) from TEST1 b where b.OBJECT_ID = a.OBJECT_ID);



--去重查询方法二：根据rownum,性能差 cg 101450 太慢不用测了
select *
from (select a.OBJECT_ID,a.OBJECT_NAME,a.owner, rownum rn from TEST1 a) x
where x.rn in (select min(rownum) from TEST1 group by OBJECT_ID)
order by OBJECT_ID asc;


-----------------------------------------------------------------------------------------------------
TABLE_NAME INDEX_NAME NUM_ROWS BLOCKS CLUSTERING_FACTOR
------------------------------ ------------------------------ ---------- ---------- -----------------
TEST1 TEST1_IDX 1068142 55358 48049
TEST2 TEST2_IDX 1068143 8466 8463
----统计结论：cf值与rows、blocks值接近，说明计划会很差，性能越差，需调优cf值

---------cf调优-------------------------------------------
drop table TEST3;
create table TEST3 as select * from TEST1 order by object_id desc;

--创建索引：
create index TEST3_IDX on TEST3(object_id);


--统计索引、表
EXEC DBMS_STATS.gather_index_stats(USER, 'TEST3_IDX');
EXEC dbms_stats.gather_table_stats(USER,'TEST3');


--查看聚簇因子：
select b.table_name,
a.index_name,
b.num_rows,
b.blocks,
a.clustering_factor
from user_indexes a, user_tables b
where b.table_name in ('TEST3', 'TEST1')
and a.table_name = b.table_name;

TABLE_NAME INDEX_NAME NUM_ROWS BLOCKS CLUSTERING_FACTOR
------------------------------ ------------------------------ ---------- ---------- -----------------
TEST1 TEST1_IDX 1068142 55358 48049
TEST3 TEST3_IDX 1068142 8463 8463

--blocks与cf值一致，说明已最优
--清缓存
ALTER SYSTEM FLUSH SHARED_POOL;
ALTER SYSTEM FLUSH buffer_cache;
ALTER SYSTEM FLUSH GLOBAL CONTEXT;
--执行改造SQL set autot trace;
--去重性能高 rownum 百万(44 recursive calls 3 db block gets 8467 consistent gets 13863 physical reads)
--清缓存 百万(458 recursive calls 3 db block gets 8552 consistent gets 13881 physical reads）
select t.OBJECT_ID,t.OBJECT_NAME,t.owner
from (select a.OBJECT_ID,a.OBJECT_NAME,a.owner,
row_number() over(partition by a.OBJECT_ID order by rownum) rn
from TEST3 a) t
where t.rn = 1;


--去重比较 rowid 性能中等 百万（34 recursive calls 0 db block gets 11286 consistent gets 12560 physical reads）
--清缓存 百万(468 recursive calls 0 db block gets 11367 consistent gets 15377 physical reads）
select a.OBJECT_ID,a.OBJECT_NAME,a.owner
from TEST3 a
where a.rowid =
(select min(rowid) from TEST3 b where b.OBJECT_ID = a.OBJECT_ID);

-----------------------------------------------------------------------------------------------------