[20230203]完善awr.sql指令碼.txt

[20230203]完善awr.sql指令碼.txt

--//來自WRH$_ACTIVE_SESSION_HISTORY Does Not Get Purged Based Upon the Retention Policy.
--//主要原因是執行很慢,如果WRH$_ACTIVE_SESSION_HISTORY表很大的情況下.
--//自己改寫如下:

set serveroutput on
declare
CURSOR cur_part IS
SELECT partition_name from dba_tab_partitions
WHERE table_name = 'WRH$_ACTIVE_SESSION_HISTORY';

query1 varchar2(200);
query2 varchar2(200);

&&1 TYPE partrec IS RECORD (snapid number, dbid number);
&&2 TYPE partrec IS RECORD (snapid number);
TYPE partlist IS TABLE OF partrec;

Outlist partlist;
begin
&&1 dbms_output.put_line('PARTITION  NAME             SNAP_ID DBID');
&&2 dbms_output.put_line('PARTITION  NAME             SNAP_ID ');
    dbms_output.put_line('--------------------------- ------------------');

    for part in cur_part loop
&&1     query1 := 'select min(snap_id), dbid from sys.WRH$_ACTIVE_SESSION_HISTORY partition ('||part.partition_name||') group by dbid';
&&2     query1 := 'select min(snap_id) from sys.WRH$_ACTIVE_SESSION_HISTORY partition ('||part.partition_name||')';
        execute immediate query1 bulk collect into OutList;

        if OutList.count > 0 then
            for i in OutList.first..OutList.last loop
&&1             dbms_output.put_line(part.partition_name||' Min '||OutList(i).snapid||' '||OutList(i).dbid);
&&2             dbms_output.put_line(part.partition_name||' Min '||OutList(i).snapid);
            end loop;
        end if;

&&1     query2 := 'select max(snap_id), dbid from sys.WRH$_ACTIVE_SESSION_HISTORY partition ('||part.partition_name||') group by dbid';
&&2     query2 := 'select max(snap_id)  from sys.WRH$_ACTIVE_SESSION_HISTORY partition ('||part.partition_name||')';
        execute immediate query2 bulk collect into OutList;

        if OutList.count > 0 then
            for i in OutList.first..OutList.last loop
&&1             dbms_output.put_line(part.partition_name||' Max '||OutList(i).snapid||' '||OutList(i).dbid);
&&2             dbms_output.put_line(part.partition_name||' Max '||OutList(i).snapid);
                dbms_output.put_line('---');
            end loop;
        end if;
    end loop;
end;
/