set serverout on;
declare
v_owner varchar2(200) := upper('&tab_owner');
v_table_name varchar2(200) := upper('&tab_name');
v_num_rows number;
v_message varchar2(300);
v_est_percent varchar2(300);
V_SQLTEXT VARCHAR2(4000);
v_degree number;
V_COUNT number;
V_COUNT2 number;
v_statid varchar2(31);
v_METHOD_OPT varchar2(50);
v_last_analyzed varchar2(30);
v_sqltext2 varchar2(400);
begin
SELECT SUBSTR(V_TABLE_NAME, 1, 28)||MOD(NVL(MAX(SUBSTR(STATID, -1, 1)), 0) + 1,10)
INTO v_statid
FROM DBMGR.CBO_STATS_BAK
WHERE C1 = V_TABLE_NAME
AND C5 = v_owner
AND D1 = (SELECT MAX(D1)
FROM DBMGR.CBO_STATS_BAK
WHERE C1 = V_TABLE_NAME
AND C5 = v_owner
AND STATID NOT LIKE 'DATABASE_');
select to_char(last_analyzed,'YYYYMMDD HH24:MI:SS') into v_last_analyzed from dba_tables where table_name=V_TABLE_NAME and wner=v_owner;
v_sqltext:='begin dbms_stats.export_table_stats(ownname => '''||v_owner||''','||chr(10)||
'tabname => '''||v_table_name||''','||chr(10)||
'stattab => ''CBO_STATS_BAK'','||chr(10)||
'statown => ''dbmgr'','||chr(10)||
'cascade => true,'||chr(10)||
'statid => '''||v_statid||''');'||chr(10)||
'end;'||chr(10)||
'/';
v_sqltext2:='insert into dbmgr.cbo_backup_info(OWNER,TABLE_NAME,last_analyzed,statid,Backup_Date) values('''||v_owner||''','''||v_table_name||''',to_date('''||v_last_analyzed||''',''YYYY-MM-DD HH24:MI:SS''),'''||v_statid||''',SYSDATE);';
dbms_output.put_line('------------backup_scripts---------------') ;
dbms_output.put_line(v_sqltext) ;
dbms_output.put_line(v_sqltext2) ;
dbms_output.put_line('commit;') ;
select count(*)
into v_count
from dbstats.STATS_GATHER_LOG_DETAIL sts
where sts.object_owner = v_owner
and sts.object_name = v_table_name
and sts.gather_message like 'BEGIN%'
and rownum = 1;
if v_count > 0 then
begin
SELECT replace(gather_message, 'CASCADE => FALSE', 'CASCADE => TRUE')
into v_sqltext
FROM DBSTATS.STATS_GATHER_LOG_DETAIL SG
WHERE SG.GATHER_START_TIME =
(select MAX(GATHER_START_TIME)
from dbstats.STATS_GATHER_LOG_DETAIL sts
where sts.object_owner = v_owner
and sts.object_name = v_table_name
and sts.object_type='TABLE'
and sts.gather_message like 'BEGIN%')
AND SG.object_owner = v_owner
AND SG.object_name = v_table_name;
SELECT COUNT(*)
INTO V_COUNT2
FROM DBA_TAB_PARTITIONS
WHERE table_owner = v_owner
AND table_name = v_table_name;
if v_count2=0 then
v_sqltext:=v_sqltext;
else
select REPLACE(V_SQLTEXT,'GRANULARITY => ''DEFAULT''','GRANULARITY => ''ALL''') into V_SQLTEXT from dual;
end if;
dbms_output.put_line('------------gather_scripts---------------') ;
dbms_output.put_line(substr(v_sqltext,
0,
instr(v_sqltext, 'BLOCK_SAMPLE') - 1));
dbms_output.put_line(substr(v_sqltext,
instr(v_sqltext, 'BLOCK_SAMPLE')));
end;
else
begin
select num_rows
into v_num_rows
from dba_tables
where wner = v_owner
AND table_name = v_table_name;
if v_num_rows is null then
V_METHOD_OPT:='FOR ALL COLUMNS SIZE 1';
else v_METHOD_OPT:='FOR ALL COLUMNS SIZE REPEAT';
end if;
if v_num_rows < 5000000 or v_num_rows is null then
v_est_percent := 20;
end if;
if v_num_rows between 5000000 and 10000000 then
v_est_percent := 10;
end if;
if v_num_rows between 10000000 and 100000000 then
v_est_percent := 5;
end if;
if v_num_rows > 100000000 then
v_est_percent := 1;
end if;
if v_num_rows < 1000000 or v_num_rows is null then
v_degree := 1;
end if;
if v_num_rows between 1000000 and 5000000 then
v_degree := 2;
end if;
if v_num_rows between 5000000 and 10000000 then
v_degree := 4;
end if;
if v_num_rows between 10000000 and 50000000 then
v_degree := 6;
end if;
if v_num_rows between 50000000 and 100000000 then
v_degree := 8;
end if;
if v_num_rows > 100000000 then
v_degree := 12;
end if;
SELECT COUNT(*)
INTO V_COUNT
FROM DBA_TAB_PARTITIONS
WHERE table_owner = v_owner
AND table_name = v_table_name;
if V_COUNT = 0 then
V_SQLTEXT := 'BEGIN DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => ''' || V_OWNER || ''',' || CHR(10) ||
'TABNAME => ''' || V_TABLE_NAME || ''',' || CHR(10) ||
'ESTIMATE_PERCENT => ' || v_est_percent || ',' || CHR(10) ||
'METHOD_OPT => '''||V_METHOD_OPT||''',' || CHR(10) ||
'DEGREE => ' || v_degree || ',' || CHR(10) ||
'CASCADE => TRUE,' || CHR(10) ||
'NO_INVALIDATE => FALSE);' || CHR(10) ||
'END;';
dbms_output.put_line('------------gather_scripts---------------') ;
dbms_output.put_line(V_SQLTEXT);
dbms_output.put_line('------------gather_scripts---------------') ;
else
V_SQLTEXT := 'BEGIN DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => ''' || V_OWNER || ''',' || CHR(10) ||
'TABNAME => ''' || V_TABLE_NAME || ''',' || CHR(10) ||
'ESTIMATE_PERCENT => ' || v_est_percent || ',' || CHR(10) ||
'METHOD_OPT => '''||V_METHOD_OPT||''',' || CHR(10) ||
'DEGREE => ' || v_degree || ',' || CHR(10) ||
'GRANULARITY => ''ALL'',' || CHR(10) ||
'CASCADE => TRUE,' || CHR(10) ||
'NO_INVALIDATE => FALSE);' || CHR(10) ||
'END;';
dbms_output.put_line('------------gather_scripts---------------') ;
dbms_output.put_line(V_SQLTEXT);
dbms_output.put_line('------------gather_scripts---------------') ;
end if;
end;
end if;
exception
when others then
v_message := sqlerrm;
dbms_output.put_line(v_message);
end;
/
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/11411056/viewspace-734328/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/11411056/viewspace-734328/