69 lines
2.3 KiB
Plaintext
Executable File
69 lines
2.3 KiB
Plaintext
Executable File
Optimizer Statistics Gathering – pending and history
|
||
https://www.dbi-services.com/blog/optimizer-statistics-gathering-pending-and-history/
|
||
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
|
||
|
||
create user XIZOR identified by secret;
|
||
grant connect, resource to XIZOR;
|
||
grant unlimited tablespace to XIZOR;
|
||
grant select any dictionary to XIZOR;
|
||
|
||
connect XIZOR/secret
|
||
|
||
create table DEMO as select rownum n from dual;
|
||
|
||
col analyzed for a30
|
||
col published_prefs for a30
|
||
|
||
select num_rows,cast(last_analyzed as timestamp) analyzed,dbms_stats.get_prefs('PUBLISH',owner,table_name) published_prefs from dba_tab_statistics where owner='XIZOR' and table_name in ('DEMO');
|
||
|
||
|
||
insert into DEMO select rownum n from xmltable('1 to 41');
|
||
|
||
|
||
set pages 999 lines 200
|
||
|
||
select /*+ gather_plan_statistics */ count(*) from DEMO;
|
||
|
||
select * from table(dbms_xplan.display_cursor(format=>'basic +rows +rowstats last'));
|
||
|
||
exec dbms_stats.set_table_prefs('XIZOR','DEMO','PUBLISH','FALSE');
|
||
|
||
|
||
exec dbms_stats.gather_table_stats('XIZOR','DEMO');
|
||
|
||
|
||
select num_rows,cast(last_analyzed as timestamp) analyzed,dbms_stats.get_prefs('PUBLISH',owner,table_name) published_prefs from dba_tab_pending_stats where owner='XIZOR' and table_name in ('DEMO');
|
||
|
||
exec dbms_stats.delete_pending_stats('XIZOR','DEMO');
|
||
|
||
exec dbms_stats.publish_pending_stats('XIZOR','DEMO',no_invalidate=>false);
|
||
|
||
exec dbms_stats.set_table_prefs('XIZOR','DEMO','PUBLISH','TRUE');
|
||
|
||
exec dbms_stats.restore_table_stats('XIZOR','DEMO',sysdate-1,no_invalidate=>false);
|
||
|
||
|
||
|
||
select report from table(dbms_stats.diff_table_stats_in_history('XIZOR','DEMO',sysdate-1,sysdate,0));
|
||
|
||
select
|
||
end_time,end_time-start_time,operation,target,
|
||
regexp_replace(regexp_replace(notes,'" val="','=>'),'(||)',' '),
|
||
status
|
||
from
|
||
DBA_OPTSTAT_OPERATIONS where regexp_like(target,'"?'||'XIZOR'||'"?."?'||'DEMO'||'"?') order by end_time desc fetch first 10 rows only
|
||
/
|
||
|
||
|
||
select table_name,stats_update_time from dba_tab_stats_history where owner='XIZOR' and table_name='DEMO';
|
||
|
||
set long 2000000
|
||
set pagesize 1000
|
||
|
||
select * from table(dbms_stats.diff_table_stats_in_history(
|
||
ownname => 'XIZOR',
|
||
tabname => 'DEMO',
|
||
time1 => systimestamp-1,
|
||
time2 => systimestamp,
|
||
pctthreshold => 0));
|