drop table MIGUPS.LUGZ0_SPM_2020;
declare
v_ret number;
v_tab_name varchar2(300) := 'LUGZ0_SPM_2020';
begin
dbms_spm.create_stgtab_baseline(table_name => v_tab_name, table_owner => 'MIGUPS');
v_ret := dbms_spm.pack_stgtab_baseline(table_name => v_tab_name,table_owner => 'MIGUPS', enabled => 'YES', accepted => 'YES');
dbms_output.put_line('plans:' || v_ret);
execute immediate ' create index MIGUPS.idx_sqlh on MIGUPS.'||v_tab_name||'(sql_handle) parallel 8';
execute immediate ' alter index MIGUPS.idx_sqlh parallel 1';
end;
/
select count(distinct sql_handle||plan_name) ||' SPM WAS EXPORTED' from dba_sql_plan_baselines where accepted='YES' and enabled='YES';
drop table MIGUPS.LUGZ0_STATS_2020;
drop table migups.stats_config_lugz0;
begin
DBMS_STATS.CREATE_STAT_TABLE('MIGUPS','LUGZ0_STATS_2020');
end;
/
create table migups.stats_config_lugz0 as
select owner,table_name,num_rows,last_analyzed,temporary,partitioned,rownum rn from dba_tables
where owner in (select username from migups.s_User_List where flag='Y') order by dbms_random.random;
alter table migups.stats_config_lugz0 add tab_size number;
alter table migups.stats_config_lugz0 add flag varchar2(10);
alter table migups.stats_config_lugz0 add err varchar2(2000);
update migups.stats_config_lugz0 set rn=rownum;
commit;
begin
for i in (select owner, table_name from migups.stats_config_lugz0) loop
begin
dbms_stats.export_table_stats(ownname => i.owner,
tabname => i.table_name,
stattab => 'LUGZ0_STATS_2020',
statown => 'MIGUPS',
statid => 'STATS_2020',
cascade => true);
exception when others then
dbms_output.put_line(i.owner||'.'||i.table_name||' '||sqlerrm);
end;
end loop;
end;
/
create index migups.idxx_c1c2 on migups.LUGZ0_STATS_2020(c1,c5);
begin
dbms_stats.gather_table_stats(ownname =>'MIGUPS' ,tabname =>'LUGZ0_STATS_2020',cascade=>true );
end;
/
select count(1)||' STATISTIC WAS EXPORTED;' from MIGUPS.LUGZ0_STATS_2020;
DECLARE
l_plans_unpacked PLS_INTEGER;
BEGIN
l_plans_unpacked := DBMS_SPM.unpack_stgtab_baseline(
table_name =>'LUGZ0_SPM_2020',
table_owner => 'MIGUPS' );
DBMS_OUTPUT.put_line('Plans Unpacked: ' || l_plans_unpacked);
END;
/
begin
dbms_stats.upgrade_stat_table(ownname => 'MIGUPS',stattab => 'LUGZ0_STATS_2020' );
end;
/
create index MIGUPS.stats_idx_c5_c1 on MIGUPS.LUGZ0_STATS_2020(c5,c1);
begin
dbms_stats.gather_table_stats(ownname =>'MIGUPS' ,tabname =>'LUGZ0_STATS_2020',cascade=>true );
for i in (select distinct c5, c1 from migups.LUGZ0_STATS_2020 where type='T') loop
dbms_stats.import_table_stats(OWNNAME => i.c5,
tabname => i.c1,
STATTAB => 'LUGZ0_STATS_2020',
STATOWN => 'MIGUPS',
statid => 'STATS_2020',
cascade => true
);
commit;
end loop;
end;
/
