92 lines
1.9 KiB
Plaintext
92 lines
1.9 KiB
Plaintext
drop table DEMO purge;
|
|
|
|
create table DEMO(
|
|
c1 INTEGER not null
|
|
,c2 INTEGER not null
|
|
,c3 DATE
|
|
);
|
|
|
|
create index IDX1 on DEMO(c1);
|
|
|
|
insert into DEMO(c1,c2,c3)
|
|
select
|
|
to_number(COLUMN_VALUE)
|
|
,to_number(COLUMN_VALUE)
|
|
,DATE'2024-01-01' + trunc(DBMS_RANDOM.VALUE(1,30*4))
|
|
from xmltable('1 to 10000');
|
|
|
|
update DEMO set c1=1 where c1<=9000;
|
|
|
|
commit;
|
|
|
|
exec dbms_stats.delete_table_stats(user,'DEMO');
|
|
exec dbms_stats.gather_table_stats(user,'DEMO');
|
|
exec dbms_stats.gather_table_stats (user, 'DEMO', method_opt=>'for all columns size 1');
|
|
exec dbms_stats.gather_table_stats (user, 'DEMO', method_opt=>'for all columns size auto');
|
|
|
|
col column_name for a20
|
|
|
|
select column_name,num_distinct,density,num_nulls,num_buckets,sample_size,histogram
|
|
from user_tab_col_statistics
|
|
where table_name='DEMO';
|
|
|
|
-- best with FULL scan
|
|
alter system flush shared_pool;
|
|
var b1 NUMBER;
|
|
var b2 NUMBER;
|
|
execute :b1:=1;
|
|
execute :b2:=128;
|
|
select /*+ GATHER_PLAN_STATISTICS */ max(c3) from DEMO where c1=:b1 and c2=:b2;
|
|
@xlast
|
|
|
|
|
|
@coe_xfr_sql_profile 9g1m1cg9uprrp 2180342005
|
|
|
|
-- disable / drop SQL Profile
|
|
exec dbms_sqltune.alter_sql_profile('coe_9g1m1cg9uprrp_2180342005','STATUS','DISABLED');
|
|
exec dbms_sqltune.drop_sql_profile('coe_9g1m1cg9uprrp_2180342005');
|
|
|
|
create index IDX2 on DEMO(c1,c2);
|
|
|
|
-- best with INDEX scan
|
|
alter system flush shared_pool;
|
|
var b1 NUMBER;
|
|
var b2 NUMBER;
|
|
execute :b1:=9999;
|
|
execute :b2:=9999;
|
|
select /*+ GATHER_PLAN_STATISTICS */ max(c3) from DEMO where c1=:b1 and c2=:b2;
|
|
@xlast
|
|
|
|
|
|
-- drop SQL baseline(s)
|
|
declare
|
|
drop_result pls_integer;
|
|
begin
|
|
drop_result := DBMS_SPM.DROP_SQL_PLAN_BASELINE(
|
|
sql_handle => 'SQL_d6312d092279077a',
|
|
plan_name => NULL
|
|
);
|
|
dbms_output.put_line(drop_result);
|
|
end;
|
|
/
|
|
|
|
|
|
|
|
SET LONG 10000
|
|
var report clob;
|
|
begin
|
|
:report := dbms_spm.evolve_sql_plan_baseline(
|
|
sql_handle=>'SQL_7e1afe4c21a1e2af'
|
|
,plan_name=>'SQL_PLAN_7w6ry9hhu3spg179a032f'
|
|
,time_limit=>5
|
|
,VERIFY=>'YES'
|
|
,COMMIT=>'NO'
|
|
);
|
|
end;
|
|
/
|
|
print :report
|
|
|
|
|
|
|
|
|