Identify good candidate sql statements for Result Cache

Hi There,
This the query I am using to identify good candidate sql statements for result_cache sqlpatch.
[code language=”sql”]
select s.sql_id,s.executions,
round (s.cpu_time/1000000) cpu,
round (s.ELAPSED_TIME/1000000) elapse,
round ((s.ELAPSED_TIME/1000000)/ decode (s.executions,0,null,s.executions),4) elapseperexec,
round ((s.cpu_time/1000000)/decode (s.executions,0,null,s.executions),4) cpuperexec, t.PARSING_SCHEMA_NAME, t.sql_patch, s.SQL_FULLTEXT
from v$sqlstats s join v$sql t on (s.sql_id = t.sql_id)
where s.sql_text like ‘SELECT%’
and t.sql_text not like ‘%DS_SVC%’
and round ((s.ELAPSED_TIME/1000000)/ decode (s.executions,0,null,s.executions),4) > 0.5
and s.executions >20
order by 5 desc;
[/code]
You can adjust to some specific schema and when you are fine with you result you can then run this PL/SQL block to create your SQL Patches.
Note: as sysdba
[code language=”sql”]
set serveroutput on
declare
Cursor C1 is select sql_id
from
(
select s.sql_id
from v$sqlstats s join v$sql t on (s.sql_id = t.sql_id)
where s.sql_text like ‘SELECT%’
and t.sql_text not like ‘%DS_SVC%’
and round ((s.ELAPSED_TIME/1000000)/ decode (s.executions,0,null,s.executions),4) > .5
and s.executions >20
);
l_patchname varchar2(30);
l_sqltext clob;
begin
For lSqlId in C1
Loop
begin
select sql_fulltext into l_sqltext from v$sqlarea where sql_id = lSqlId.sql_id;
l_patchname := lSqlId.sql_id||’_patch’;
dbms_sqldiag_internal.i_create_patch(sql_text => cl_sql_text, hint_text => ‘RESULT_CACHE’, name => l_patch_name);
dbms_output.put_line(‘ ‘);
dbms_output.put_line(‘SQL Patch ‘||l_patch_name||’ created.’);
dbms_output.put_line(‘ ‘);
exception
WHEN OTHERS THEN
dbms_output.put_line(‘SQL Patch ‘||l_patch_name||’ already created.’);
end;
End loop;
end;
/
[/code]
Cheers
jko


     

Leave a Reply

Your email address will not be published. Required fields are marked *