Plan Capture and Playback

Quite often performance tuning activity mainly involves working with execution plans and trying to identify the root cause of the problem and subsequently taking corrective actions.

Oracle's CBO is getting better by the day in determining the optimal execution plan and thus providing the best performance possible. However, there is always room for improvement and performance experts could help since they happen to have better information regarding the "data". In cases where we are tasked to work on sql performance issues, it is an often heard comment that, "this was working fine till last week and *NO CHANGE* was made to the system", yet the performance is horrible since morning. We need a fix.

In situations like these, we wish we had access to the execution plan from the "best performing" time so we can compare to the current plan. In many cases, this will help us quickly nail down the reason for the poor sql execution and take appropriate fix.

There are many ways to get plans. One of them being from the AWR data collected itself. But this would mean doing a lot more work, collecting more data than necessary. One solution would be to collect and store the execution plans for all SQLs in SQL CACHE based on a repeated interval.

Assuming the SQL itself remained unchanged; the SQL_ID will remain the same. This piece of information could be used to query for versions of sql plans for a given sql and start narrowing down to fix the problem.

The script below provides a simple solution to query and capture the execution plans for future troubleshooting.

**********************************************************************************
The script has been tested in both 10g and 11g environments. Please test the script in your sandbox environment and make sure it conforms to your needs and satisfaction before proceeding.
**********************************************************************************

Couple of comments:

1. The script prompts for SYS password to perform some "grants".
2. It expects a db username that will own the package, table and indexes to accomplish the task set forth.
3. I have commented couple of statements: "a drop table" before creating it, and a "drop job". I did not want to drop something with the same name in your schema inadvertently. Please make appropriate changes before executing.
4. The package currently captures the plan for a given SQL once per day. This is mainly to avoid capturing the same SQL/PLAN more than necessary. You may have to comment out the "where clause" in cursor c1 is this is not desirable and you want the plan captured no matter what.
5. The scheduler job's repeat interval is set to "1 minute". Make changes to fit your needs.





prompt please enter sys password
accept sys_password
prompt please enter the username that will own package
accept username
prompt please enter password
accept password
conn sys/&&sys_password as sysdba
grant resource, connect, create job to &&username;
grant select on v_$sql_plan to &&username;
conn &&username/&&password
--drop table plan_capture_hist;
CREATE TABLE PLAN_CAPTURE_HIST
(
EX_PLAN VARCHAR2(2000 BYTE) NULL,
SQL_ID VARCHAR2(20 BYTE) NULL,
PLAN_HASH_VALUE NUMBER NULL,
CAPTURED_DATE DATE DEFAULT SYSDATE NULL,
SQL_HASH_VALUE NUMBER NULL,
ITERATION NUMBER NULL,
COMMENTS VARCHAR2(2000 BYTE) NULL,
CHILD_NUMBER NUMBER(38) NULL,
RN NUMBER NULL
)
/
CREATE INDEX PCH_SQL_ID ON PLAN_CAPTURE_HIST
(SQL_ID)
/
create index fn_pch_captured_dt on plan_capture_hist(trunc(captured_date))
/
drop package xx_plan_capture;
CREATE OR REPLACE PACKAGE xx_plan_capture IS
PROCEDURE sqlcache_capture;
PROCEDURE sqlid_capture(v_sql_id IN VARCHAR2,v_child_number IN NUMBER);
END;
/
CREATE OR REPLACE PACKAGE BODY xx_plan_capture IS
PROCEDURE sqlcache_capture IS
v_sql_id VARCHAR2(20);
b NUMBER := NULL;
--defaults to the parameters passed to display_cursor
c VARCHAR2(30) := 'TYPICAL ALLSTATS -NOTE';
-- set the plan_capture iteration count for the day
v_iteration NUMBER;
CURSOR c1 IS
SELECT DISTINCT a.sql_id,a.plan_hash_value,a.hash_value,a.child_number
FROM sys.v_$sql_plan A
MINUS
SELECT sql_id,plan_hash_value,sql_hash_value,child_number
FROM plan_capture_hist
where trunc(captured_date) = trunc(sysdate);
CURSOR c2 IS
SELECT ROWNUM rn, plan_table_output
FROM (SELECT * FROM TABLE(dbms_xplan.display_cursor(v_sql_id,b,c)));
BEGIN
SELECT nvl2(MAX(iteration),MAX(iteration)+1,1) INTO v_iteration
FROM plan_capture_hist WHERE TRUNC(captured_date)=TRUNC(SYSDATE);
FOR c_plan_details IN c1 LOOP
BEGIN
v_sql_id := c_plan_details.sql_id;
b :=c_plan_details.child_number;
FOR c_sql_plan IN c2 LOOP
INSERT INTO plan_capture_hist(ex_plan,sql_id,plan_hash_value,sql_hash_value,iteration,child_number,rn)
VALUES (c_sql_plan.plan_table_output,c_plan_details.sql_id,c_plan_details.plan_hash_value,
c_plan_details.hash_value,v_iteration,c_plan_details.child_number,c_sql_plan.rn);
END LOOP; --c_sql_plan
END;
END LOOP; --c_plan_details
COMMIT;
END; --sqlcache_capture
PROCEDURE sqlid_capture(v_sql_id IN VARCHAR2,v_child_number IN NUMBER) IS
b NUMBER := NULL;
--defaults to the parameters passed to display_cursor
c VARCHAR2(30) := 'TYPICAL ALLSTATS -NOTE';
-- set the plan_capture iteration count for the day
v_iteration NUMBER;
x_sql_id VARCHAR2(20);
v1_sql_id VARCHAR2(20);
CURSOR c1(v1_sql_id VARCHAR2) IS
SELECT DISTINCT a.sql_id,a.plan_hash_value,a.hash_value,a.child_number
FROM sys.v_$sql_plan A
WHERE a.sql_id = v_sql_id AND child_number=v_child_number;
CURSOR c2 IS
SELECT ROWNUM rn, plan_table_output
FROM (SELECT * FROM TABLE(dbms_xplan.display_cursor(v_sql_id,v_child_number,c)));
BEGIN
SELECT nvl2(MAX(iteration),MAX(iteration)+1,1) INTO v_iteration
FROM plan_capture_hist WHERE TRUNC(captured_date)=TRUNC(SYSDATE);
dbms_output.put_line(v_iteration);
FOR c_plan_details IN c1(v1_sql_id) LOOP
dbms_output.put_line(c_plan_details.sql_id);
BEGIN
FOR c_sql_plan IN c2 LOOP
--dbms_output.put_line(c_sql_plan.plan_table_output);
INSERT INTO plan_capture_hist(ex_plan,sql_id,plan_hash_value,sql_hash_value,iteration,child_number,rn)
VALUES (c_sql_plan.plan_table_output,c_plan_details.sql_id,c_plan_details.plan_hash_value,
c_plan_details.hash_value,v_iteration,c_plan_details.child_number,c_sql_plan.rn);
END LOOP; -- c_sql_plan
END;
END LOOP; --cursor c_plan_details
COMMIT;
END; --sqlcache_capture
END; --package body
/
--exec dbms_scheduler.drop_job('sql_plan_capture');
exec DBMS_SCHEDULER.create_job( job_name => 'sql_plan_capture', job_type=>'STORED_PROCEDURE' -
,job_action=>'xx_plan_capture.sqlcache_capture' -
, start_date => SYSTIMESTAMP -
, repeat_interval => 'FREQ=MINUTELY; INTERVAL=1' -
, end_date => NULL, enabled => TRUE, comments => 'Plan Capture for all SQL Cache Cursors');