Hi all,
I have a SQL which recently was executed poorly. I tried the SQL tuning advisor for this particular SQL statement and it gives the following information:
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : 2q94zb7djr2xn
Tuning Task Owner : LMDBPROD
Workload Type : Single SQL Statement
Execution Count : 2
Current Execution : EXEC_8869
Execution Type : TUNE SQL
Scope : COMPREHENSIVE
Time Limit(seconds): 60
Completion Status : COMPLETED
Started at : 02/03/2014 20:43:39
Completed at : 02/03/2014 20:44:25
-------------------------------------------------------------------------------
Schema Name: LMDBPROD
SQL ID : 2q94zb7djr2xn
SQL Text : SELECT NPCOMMON.FGET_ACTION_CODE(ORDNUM) ACTIONCODE, STATUS,
COUNT(*) SUMA FROM SORDER WHERE HEADORDNUM IS NOT NULL AND
NPCOMMON.FGET_PRODUCTOFFER4ORDER(ORDNUM) IN ( SELECT ID FROM
PRODUCTOFFER WHERE PPSPECIFICATION_ID IN ( SELECT ID FROM
PPSPECIFICATION WHERE PCLASS_CODE IN ( SELECT PCLASS_CODE FROM
ARUSERGROUP WHERE CODE IN ( SELECT ARUSERGROUP_CODE FROM
ARUSER_ARUSERGROUP WHERE ARUSER_USERNAME = :B1 AND STATUS = '1')
AND STATUS = '1'))) GROUP BY NPCOMMON.FGET_ACTION_CODE(ORDNUM),
STATUS
-------------------------------------------------------------------------------
FINDINGS SECTION (1 finding)
-------------------------------------------------------------------------------
1- Alternative Plan Finding
---------------------------
Some alternative execution plans for this statement were found by searching
the system's real-time and historical performance data.
The following table lists these plans ranked by their average elapsed time.
See section "ALTERNATIVE PLANS SECTION" for detailed information on each
plan.
id plan hash last seen elapsed (s) origin note
-- ---------- -------------------- ------------ --------------- ----------------
1 617797893 2013-07-11/07:45:20 9.555 STS not reproducible
2 1311086720 2014-01-31/04:00:44 19.569 AWR
3 1226863820 2014-01-31/18:00:24 21.158 AWR
4 1359606848 2014-02-03/16:00:34 21.492 AWR original plan
The plan with hash 617797893 seems the most performant one and is based on some specific SQL Tuning Set I executed on that time. But the note says the plan is not reproducible. Is there anyway how can I force the SQL statement to be executed according to this plan? The database version is Standard Edition 11.2.0.1.0
Best Regards,
Edrin