SQL> select sql_handle, sql_text from dba_sql_plan_baselines
2 where sql_text like 'SELECT CHAR_VALUE FROM SYSTEM.PRODUCT_PRIVS %';
SQL_HANDLE SQL_TEXT
------------------------------ --------------------------------------------------------------------------------
SYS_SQL_2bdf77bedbcdea50 SELECT CHAR_VALUE FROM SYSTEM.PRODUCT_PRIVS WHERE (UPPER('SQL*Plus') LIKE UPPE
SQL>
SQL> set serveroutput on
SQL> declare
2 ret_value pls_integer;
3 sql_handle_in varchar2(30);
4 cursor c1 is
5 select sql_handle from dba_sql_plan_baselines
6 where sql_text like 'SELECT CHAR_VALUE FROM SYSTEM.PRODUCT_PRIVS %' ;
7 begin
8 open c1 ;
9 loop
10 fetch c1 into sql_handle_in;
11 exit when c1%notfound;
12 ret_value := dbms_spm.drop_sql_plan_baseline(sql_handle=>sql_handle_in,plan_name=>NULL);
13 dbms_output.put_line(ret_value);
14 end loop;
15 end;
16 /
1
PL/SQL procedure successfully completed.
SQL> select sql_handle, sql_text from dba_sql_plan_baselines
2 where sql_text like 'SELECT CHAR_VALUE FROM SYSTEM.PRODUCT_PRIVS %'
3 /
no rows selected
SQL>
Deletion of multiple SQL_HANDLEs or PLAN_NAMEs can also be executed similarly.
.
.
.
3 comments:
This actually became a lifesaver for me! I inherited a DB that had optimizer_capture_sql_plan_baselines to TRUE and over the years had collected 25 million + SQL handles with multiple plans per handle. The sysaux tablespace bloated to 210G+ with over 95% being occupied by this.
I used your script and as I tailed the alert log I found that the undo began to grow very fast... since all of this was going to do one large commit at the end!!!
I surely would have run out of space and juice fast... So I stopped the script and added a commit clause right before end loop. Resubmitted the script. Now, the alert log showed no change on the undo size, and redo logs were switching which told me that the actual drops of handles was happening. I let it run overnight and tried getting a count of SQL handles and it was down to 8 million. So I knew its working. I have a long way to go but will reach the finish line eventually!
Thanks Hemant for this info! Of course, the next actions will be to enable row movement and then shrink and disable row movement to reclaim the space!
Hemant,
I am trying to figure out a way to parallelize this process.
Can you modify the code and add parallelism to allow this to run faster?
One full weekend was not enough to delete the millions of SQL handles.
Here is how your code looks like in my case after adding the commit:
declare
ret_value pls_integer;
sql_handle_in varchar2(30);
cursor c1 is
select sql_handle from dba_sql_plan_baselines
where last_executed <= '31-MAY-18' ;
begin
open c1 ;
loop
fetch c1 into sql_handle_in;
exit when c1%notfound;
ret_value := dbms_spm.drop_sql_plan_baseline(sql_handle=>sql_handle_in,plan_name=>NULL);
commit;
dbms_output.put_line(ret_value);
end loop;
end;
/
Thank you.
Load the SQL_HANDLE values into a table with a numeric ID column (e.g. a sequence).
Then write four different PLSQL blocks that read the SQL_HANDLE from this table based on
mod(ID,4)
where mod(ID,4) returns 0 for the first PLSQL Block,
mod(ID,4) returns 1 for the second PLSQL Block
etc
Post a Comment