SETTING OUTLINES FOR QUERY FOR GIVEN SQL_ID

Hi

As we all know that stored outline is a collection of hints associated with a specific SQL statement that allows a standard execution plan to be maintained, regardless of changes in the system environment or associated statistics.

Now a days we are getting frequent requests from the client that we need this plan for the query instead of the existing being used by the given sql_id. Client checks the performance of the query and somtimes finds that the other plan was better the the current one used by  the query.generally the sql_id is provided by the client to make our job little bit easier and further he adds the PLAN_HASH_VALUE which is good and Bad

Once we get he request that we need to set different plan for the query we need to create the oulines for that sql to use that instead of jumping here and there.

As in this case clients identifies by using DB Control which plan_hash_value query is using right now. And which was better one used by the query.

Or else we need to dig further to chose that..

We follow this steps to identfy and fix that..the sql_id given here is '9t7tbvqnwux5p'

Step 1)

db33@prepl11 > select hash_value,CHILD_NUMBER, plan_hash_value from V$sql where sql_id = '9t7tbvqnwux5p';


HASH_VALUE            CHILD_NUMBER      PLAN_HASH_VALUE
----------                        ------------                  ---------------
2489344821                         0                              3607206622
2489344821                         4                              1085058146

2 rows selected.

Now as the client has already determined that the plan '3607206622' is not as efficient as plan '1085058146' is form his experience. it makes our job easier.

The next step is to create the oulines using the findings.

In our environment we have a default category defined as 'TUNED' which will be used by the query once oulines are created. we have to create the oulines and the switch them to the TUNED category so that query can use that outlines.

So the next step is to create the outlines

USER is "SYS"


sys@prepl11 > exec dbms_outln.create_outline(2489344821,4,'GOODSQLA');

sys@prepl11 > exec dbms_outln.create_outline(2489344821,0,'BADSQLA');

Now we can check there will be two oulines created one for bad and another for good. to compare using 
 
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('', , 'OUTLINE'); 
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('', , 'ADVANCED');
 
db33@prepl101 > select NAME,CATEGORY,ENABLED,USED from dba_outlines where CATEGORY like '%SQLA';


NAME                                                      CATEGORY    ENABLED    USED
------------------------------                        ------------------------------ -------- ------
SYS_OUTLINE_09120713441992221    GOODSQLA   ENABLED    UNUSED
SYS_OUTLINE_09120713444323823    BADSQLA       ENABLED    UNUSED


2 rows selected.

But when we do not have a time to compare..which can be done later if required jump to next step

Check how may oulines are already there in TUNED category

db33@prepl11 > select NAME,CATEGORY,ENABLED,USED from dba_outlines where CATEGORY like 'TUNED';


NAME                                                        CATEGORY   ENABLED    USED
------------------------------                       ---------------    --------------- -------- ------
SYS_OUTLINE_09110916403391908      TUNED          ENABLED USED
SYS_OUTLINE_09110917012875212      TUNED          ENABLED USED


2 rows selected.
 
So we have two oulines already stored and being used by some other sql_id. we need to move our to this one.
 
db33@prepl101 > exec outln_pkg.update_by_cat('GOODSQLA','TUNED');


PL/SQL procedure successfully completed.
db33@prepl101 > commit;
Commit complete.

Now we can check our is being used or not which we have just created

db33@prepl101 > select NAME,CATEGORY,ENABLED,USED from dba_outlines where CATEGORY like 'TUNED';


NAME                                                      CATEGORY ENABLED USED
------------------------------ ------------------------------ -------- ------
SYS_OUTLINE_09110916403391908    TUNED ENABLED USED
SYS_OUTLINE_09110917012875212    TUNED ENABLED USED
SYS_OUTLINE_09120713441992221    TUNED ENABLED USED

3 rows selected.

Get the confirmation from client :) if right plan is being used or you too can check from db control.

Vijay Kumar