Problem statement:Application team reported one of the application query was taking time and you were reported.You got the sql_id either from awr/ash report which exactly matches with the query provided by application team.Now you analyze execution plan and tends to run SQL tuning advisor to see what improvement options it can offer.You tried to run tuning advisor manually and you get the error message saying The SQL has aged out of the cursor cache
Lets see how we can resolve this issue
Reproduce the case:
lets take a random sql_id and try to find whether it is in cursor cache.Simple query which can be used below
SQL> select sql_id from v$sql where sql_id='ajq0rjdv5p34z';
SQL_ID
-------------
ajq0rjdv5p34z
-------------
ajq0rjdv5p34z
There are various way to run SQL tuning advisor calling set of PL/SQL code,from OEM and script.
Out of these I like oracle supplied script in case you dont have OEM license and remembering PL/SQL code is not an option for you.
execute script from $ORACLE_HOME/rdbms/admin/sqltrpt.sql which asks sql_id as input parameter but before that it will show 15 most expensive SQL in cursor cahe.It also lists out our intended SQL highlighted
Don't you think this is amazing feature....
15 Most expensive SQL in the cursor cache
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SQL_ID ELAPSED
------------- ----------
SQL_TEXT_FRAGMENT
-------------------------------------------------------
4xm1ruvkx3awx 5,916.14
DECLARE job BINARY_INTEGER := :job; next_date TIMESTAM
1fb9p2aq7dvbg 3,547.03
INSERT INTO CI_BILL
ajq0rjdv5p34z 2,810.85
select billcycles0_.BILL_CYC_CD as col_0_0_, billcycles
b6usrg82hwsa3 2,482.74
It immediately list outs recommendation like below
1- SQL Profile Finding (see explain plans section below)
--------------------------------------------------------
A potentially better execution plan was found for this statement.
Recommendation (estimated benefit: 79.46%)
------------------------------------------
- Consider accepting the recommended SQL profile.
execute dbms_sqltune.accept_sql_profile(task_name => 'TASK_37088',
task_owner => 'SYS', replace => TRUE);
Validation results
------------------
The SQL profile was tested by executing both its plan and the original plan
and measuring their respective execution statistics. A plan may have been
only partially executed if the other could be run to completion in less time
.
Original Plan With SQL Profile % Improved
------------- ---------------- ----------
Completion Status: COMPLETE COMPLETE
Elapsed Time (s): .000912 .000124 86.4 %
CPU Time (s): .000879 .000096 89.07 %
User I/O Time (s): 0 0
Buffer Gets: 224 46 79.46 %
Physical Read Requests: 0 0
Physical Write Requests: 0 0
Physical Read Bytes: 0 0
Physical Write Bytes: 0 0
Rows Processed: 1 1
Fetches: 1 1
Executions: 1 1
Notes
-----
1. Statistics for the original plan were averaged over 10 executions.
2. Statistics for the SQL profile plan were averaged over 10 executions.
Now I will flush shared_pool so that sql gets aged out of cursor cache.
SQL> select ADDRESS, HASH_VALUE from V$SQLAREA where SQL_Id='ajq0rjdv5p34z';
ADDRESS HASH_VALUE
---------------- ----------
0000000181BF7E68 1985645727
1 row selected.
SQL> exec DBMS_SHARED_POOL.PURGE('0000000181BF7E68,1985645727','C');
PL/SQL procedure successfully completed.
SQL> select ADDRESS, HASH_VALUE from V$SQLAREA where SQL_Id='ajq0rjdv5p34z';
no rows selected
Now I am executing again
Specify the Sql id
~~~~~~~~~~~~~~~~~~
Enter value for sqlid: ajq0rjdv5p34z
Sql Id specified: ajq0rjdv5p34z
Tune the sql
~~~~~~~~~~~~
ERROR: statement is not in the cursor cache or the workload repository.
Execute the statement and try again
Get the awr snap_id like below
SQL> select snap_id from dba_hist_sqlstat where sql_id='ajq0rjdv5p34z';
SNAP_ID
----------
671
672
Then, you can create a tuning task using the DBMS_SQLTUNE.CREATE_TUNING_TASK procedure, specifying the begin_snap and end_snap values, as well as the SQL ID of the statement to be tuned.
set serveroutput on
declare
l_sql_tune_task_id varchar2(100);
begin
l_sql_tune_task_id := dbms_sqltune.create_tuning_task (
begin_snap => 671,
end_snap => 672,
sql_id => 'ajq0rjdv5p34z',
scope => dbms_sqltune.scope_comprehensive,
time_limit => 10800,
task_name => 'SQLTUNE',
description => 'task description');
dbms_output.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
end;
/
BEGIN
DBMS_SQLTUNE.execute_tuning_task(task_name => 'SQLTUNE');
END;
/
SET LONG 10000;
SET PAGESIZE 1000
SET LINESIZE 200
SELECT DBMS_SQLTUNE.report_tuning_task('SQLTUNE') AS recommendations FROM dual;
Hurray! now you got the same result
Recommendation (estimated benefit: 79.47%)
------------------------------------------
- Consider accepting the recommended SQL profile.
execute dbms_sqltune.accept_sql_profile(task_name => 'SQLTUNE',
task_owner => 'SYS', replace => TRUE);
Validation results
------------------
The SQL profile was tested by executing both its plan and the original plan
and measuring their respective execution statistics. A plan may have been
only partially executed if the other could be run to completion in less time.
Original Plan With SQL Profile % Improved
------------- ---------------- ----------
Completion Status: COMPLETE COMPLETE
Elapsed Time (s): .001606 .000196 87.79 %
CPU Time (s): .001658 .000152 90.83 %
User I/O Time (s): 0 0
Buffer Gets: 224 46 79.46 %
Physical Read Requests: 0 0
Physical Write Requests: 0 0
Physical Read Bytes: 0 0
Physical Write Bytes: 0 0
Rows Processed: 1 1
Fetches: 1 1
Executions: 1 1
So save this set of statement as a .sql file like bwlow and try if your sql is aged out of cursor cache..
set serveroutput on
select snap_id from dba_hist_sqlstat where sql_id='&&sql_id';
declare
l_sql_tune_task_id varchar2(100);
begin
l_sql_tune_task_id := dbms_sqltune.create_tuning_task (
begin_snap => &begin_snap,
end_snap => &end_snap,
sql_id => '&&sql_id',
scope => dbms_sqltune.scope_comprehensive,
time_limit => 10800,
task_name => '&&Tuning_task_name',
description => 'task description');
dbms_output.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
end;
/
BEGIN
DBMS_SQLTUNE.execute_tuning_task(task_name => '&&Tuning_task_name');
END;
/
SET LONG 10000;
SET PAGESIZE 1000
SET LINESIZE 200
SELECT DBMS_SQLTUNE.report_tuning_task('&&Tuning_task_name') AS recommendations FROM dual;
I hope you have learnt something new...
Happy learning...
Please ask question/comment if you still have any question
Excellent Technical thread with detailed explanation. SQL tuning is one of the key stuff to normalize the long running query and this thread will eventually help to all incase if anyone facing this cursor cache error.
ReplyDelete