SPM Fixing (SPM:- SQL PLAN MANAGEMENT).
1) Identify the problematic query.In my example say.
afcd3rktapas1
It is taking lots of buffer gets when optimizer_features_enabled='11.2.0.3'
select sql_id,DISK_READS,BUFFER_GETS,executions from v$sql where sql_id='afcd3rktapas1';
SQL_ID DISK_READ BUFFER_GETS EXECUTIONS
------ --------- ----------- ----------
afcd3rktapas1 364958 67544379 0
Sqltext.
---------
UPDATE C SET T = (SELECT
NVL(SUM(C.G(NVL(E.T,0.00),G
.A,'INR','NR',:B4 )),0.00) FROM E,G,C A
WHERE G.A = E.A AND A.A = G.A AND
G.BA != '16050001' AND A.A =
C.A AND A.S = C.S AND
A.L = C.L AND A.L =
C.L AND A.S = G.S AND A.L
= :B2 AND A.L = :B1 AND A.SET_ID = :B3 AND
TO_DATE(E.E) <= TO_DATE(:B4 ) AND TO_DATE(E.END_E)>=
TO_DATE(:B4 ) AND (G.S = 'O' OR G.S='O')) WHERE
SET_ID = :B3 AND L = :B2 AND L = :B1
Take the bind variable of the query.
NAME POSITION DATATYPE_STRING VALUE_STRING
------------------------------ ---------- --------------- ------------------------------
:B4 1 DATE
:B2 2 VARCHAR2(32) XXXXX
:B1 3 DATE 12/26/2013 00:00:00
:B3 4 VARCHAR2(32) 0190
:B4 5 DATE 12/24/2013 00:00:00
:B4 6 DATE 12/24/2013 00:00:00
:B3 7 VARCHAR2(32) 0190
:B2 8 VARCHAR2(32) XXXXX
:B1 9 DATE 12/26/2013 00:00:00
2) Find the best possible plan for the same query,
In my example I have change optimizer_features_enabled='10.2.0.4';
Sql> alter session set optimizer_features_enabled='10.2.0.4';
Sql> variable cnt number; /* it will execute the query without effecting the data of table,
and will create the plan by returning 0 row */
/* DECLARE THE DIND VARIABLE NOTE: CHANGE THE DATE FIELD TO VARCHAR */
variable B4 VARCHAR2(32)
variable B2 VARCHAR2(32)
variable B1 VARCHAR2(32)
variable B3 VARCHAR2(32)
/* EXECUTE THE VARIABLE */
exec :B1:='12/26/2013'
exec :B2:='2388'
exec :B3:='2388'
exec :B4:='12/26/2013'
Sql> UPDATE /*good_plan*/ C SET T = (SELECT
NVL(SUM(C.G(NVL(E.T,0.00),G
.A,'INR','NR',:B4 )),0.00) FROM E,G,C A
WHERE G.A = E.A AND A.A = G.A AND
G.BA != '16050001' AND A.A =
C.A AND A.S = C.S AND
A.L = C.L AND A.L =
C.L AND A.S = G.S AND A.L
= :B2 AND A.L = :B1 AND A.SET_ID = :B3 AND
TO_DATE(E.E) <= TO_DATE(:B4 ) AND TO_DATE(E.END_E)>=
TO_DATE(:B4 ) AND (G.S = 'O' OR G.S='O')) WHERE
SET_ID = :B3 AND L = :B2 AND L = :B1;
0 rows updated.
Sql> select sql_id,DISK_READS,BUFFER_GETS,executions,plan_hash_value from v$sql where sql_text like '%/*good_plan*/%';
SQL_ID DISK_READ BUFFER_GETS EXECUTIONS PLAN_HASH_VALUE
------ --------- ----------- ---------- ---------------
dcba3rxapyfs1 949 346 0 4567846893
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Steps for fixing the plan.
--------------------------
1) variable cnt number;
exec :cnt:=dbms_spm.load_plans_from_cursor_cache(sql_id=>'&sql_id');
enter bad query ----> it will load the value of plan of bad query in SPM
afcd3rktapas1
2) select sql_handle, sql_text, plan_name, enabled from dba_sql_plan_baselines;
SQL_HANDLE SQL_TEXT PLAN_NAME ENA
------------------------------ ------------------------------------------------- ------------------------------ ---
SQL_ae48a49af377a3d6 UPDATE C SET T = (SELECTNVL(SUM(C.G(NVL(E.T,0.00) SQL_PLAN_awk54mbtrg8yqc03d1b54 YES
3) exec :cnt:=dbms_spm.alter_sql_plan_baseline( -
sql_handle=>'&handle', -
plan_name=>'&plan_name', -
attribute_name=>'enabled', -
attribute_value=>'NO');> > > >
Enter value for handle: SQL_ae48a49af377a3d6
Enter value for plan_name: SQL_PLAN_awk54mbtrg8yqc03d1b54
select sql_handle, sql_text, plan_name, enabled from dba_sql_plan_baselines;
SQL_HANDLE SQL_TEXT PLAN_NAME ENA
------------------------------ ------------------------------------------------- ------------------------------ ---
SQL_ae48a49af377a3d6 UPDATE C SET T = (SELECTNVL(SUM(C.G(NVL(E.T,0.00) SQL_PLAN_awk54mbtrg8yqc03d1b54 NO
4) exec :cnt:=dbms_spm.load_plans_from_cursor_cache(sql_id=>'&sql_id',plan_hash_value=>&plan_hash,sql_handle=>'&sql_handle');
Enter value for sql_id: dcba3rxapyfs1 /good/
Enter value for plan_hash: 4567846893 /good/
Enter value for sql_handle: SQL_ae48a49af377a3d6 /old/
select sql_handle, sql_text, plan_name, enabled from dba_sql_plan_baselines;
SQL_HANDLE SQL_TEXT PLAN_NAME ENA
------------------------------ ------------------------------------------------- ------------------------------ ---
SQL_ae48a49af377a3d6 UPDATE C SET T = (SELECTNVL(SUM(C.G(NVL(E.T,0.00) SQL_PLAN_ccd5w9pkt1u713969ca76 YES
SQL_ae48a49af377a3d6 UPDATE C SET T = (SELECTNVL(SUM(C.G(NVL(E.T,0.00) SQL_PLAN_awk54mbtrg8yqc03d1b54 NO
====================================================================================
Conclusion.
Now after doing this when you check the buffer gets for the same query after executing it,you will get a huge change.
