Oracle sql profile using Hint

I like to create a sql profile which should force the sql to use a hint.(FIRST ROWS)
I user the below sql and I replaced the bind variables to test it. Now that it is working better how can I force the sql's to use the hint using sql profile? The actual sql has the bind variable (:1,:2,:3) which I replaced in my sql. The actual sql runs through the application which replaces the values (BIND variables...shown below ). The actual sql has no hint

SELECT /*+ FIRST_ROWS */
       A.ADP_TSP_ELIG_CODE
   FROM PS_GVT_PERS_DATA   A,
        PS_GVT_EMPLOYMENT  B,
        PS_GVT_JOB         C,
        PS_ACCOMP_TBL      AT,
        PS_ACCOMPLISHMENTS AC
 WHERE C.EMPLID = A.EMPLID
    AND C.EMPL_RCD = A.EMPL_RCD
    AND AC.EMPLID = C.EMPLID
    AND AC.ACCOMPLISHMENT = AT.ACCOMPLISHMENT
    AND C.EMPLID = 1234567  ----------------------Actual sql has bind :1
    AND C.EMPL_RCD = 0
    AND C.EFFDT = (SELECT MAX(H.EFFDT)
        FROM PS_GVT_JOB H
   WHERE H.EMPLID = C.EMPLID
                      H.EFFDT < '19-FEB-14'  ----------------------Actual sql has bind :2
                      AND H.PAYGROUP = 'ARS') ----------------------Actual sql has bind :3
OranewAsked:
Who is Participating?
 
johnsoneSenior Oracle DBACommented:
Also, realize that there is no need to put values into the bind variables and run the query.  You can generate a plan without running the query.
0
 
johnsoneSenior Oracle DBACommented:
You cannot tune a query with values and expect the same result with bind variables.  It doesn't work that way.  With a value, the optimizer can make assumptions.  Without a value (i.e. with a bind variable) it cannot make assumptions about the value.

The query you should be tuning is this one:
SELECT 
       A.ADP_TSP_ELIG_CODE
   FROM PS_GVT_PERS_DATA   A,
        PS_GVT_EMPLOYMENT  B,
        PS_GVT_JOB         C,
        PS_ACCOMP_TBL      AT,
        PS_ACCOMPLISHMENTS AC
 WHERE C.EMPLID = A.EMPLID
    AND C.EMPL_RCD = A.EMPL_RCD
    AND AC.EMPLID = C.EMPLID
    AND AC.ACCOMPLISHMENT = AT.ACCOMPLISHMENT
    AND C.EMPLID = :1
    AND C.EMPL_RCD = 0
    AND C.EFFDT = (SELECT MAX(H.EFFDT)
        FROM PS_GVT_JOB H
   WHERE H.EMPLID = C.EMPLID
                      H.EFFDT < :2
                      AND H.PAYGROUP = :3)

Open in new window

That query may not actually need a hint.  It may do what you are expecting it to do.

As soon as you put actual values into the query, you changed the plan of the original.
0
 
MikeOM_DBACommented:
You can "Manually" add a sql profile :
-- ---------------------------------------
-- manual_sqlprofile
--
DECLARE
    Sql_txt             CLOB;
    H                   SYS.Sqlprof_attr;
    Signature           VARCHAR2 (128);
BEGIN
    Sql_txt          :=
        q'[SELECT  A.Adp_tsp_elig_code FROM Ps_gvt_pers_data A, Ps_gvt_employment B, Ps_gvt_job C, Ps_accomp_tbl At, Ps_accomplishments Ac WHERE C.Emplid = A.Emplid AND C.Empl_rcd = A.Empl_rcd AND Ac.Emplid = C.Emplid AND Ac.Accomplishment = At.Accomplishment AND C.Emplid = :1 AND C.Empl_rcd = 0 AND C.Effdt = (SELECT MAX (H.Effdt) FROM Ps_gvt_job H WHERE H.Emplid = C.Emplid AND H.EFFDT < ':1' AND H.PAYGROUP = :3)]';
    H                :=
        Sys.Sqlprof_attr (q'[BEGIN_OUTLINE_DATA]'
                        , q'[IGNORE_OPTIM_EMBEDDED_HINTS]'
                        , q'[OPTIMIZER_FEATURES_ENABLE('11.2.0.1')]'
                        , q'[DB_VERSION('11.2.0.1')]'
                        , q'[FIRST_ROWS]'
                        , q'[END_OUTLINE_DATA]'
                         );


    Signature        := DBMS_SQLTUNE.Sqltext_to_signature (Sql_txt);

    DBMS_SQLTUNE.Import_sql_profile (
        Sql_text    => Sql_txt
      , Profile     => H
      , Name        => 's56775_p4272283873'
      , Description => 'Manual "FIRST_ROWS" profile ' || Signature
      , Category    => 'DEFAULT'
      , VALIDATE    => TRUE
      , REPLACE     => TRUE
      , Force_match => FALSE /* TRUE:FORCE (match even when different literals in SQL). FALSE:EXACT (similar to CURSOR_SHARING) */
                            );
END;
/

Open in new window

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
johnsoneSenior Oracle DBACommented:
If you wanted to run the query with the values you supplied in SQL*Plus, you can do it this way:
variable v1 number;
variable v2 varchar2(100);
variable v3 varchar2(100);
begin
  :v1 := 1234567;
  :v2 := '19-FEB-14';
  :v3 := 'ARS';
end;
/
SELECT 
       A.ADP_TSP_ELIG_CODE
   FROM PS_GVT_PERS_DATA   A,
        PS_GVT_EMPLOYMENT  B,
        PS_GVT_JOB         C,
        PS_ACCOMP_TBL      AT,
        PS_ACCOMPLISHMENTS AC
 WHERE C.EMPLID = A.EMPLID
    AND C.EMPL_RCD = A.EMPL_RCD
    AND AC.EMPLID = C.EMPLID
    AND AC.ACCOMPLISHMENT = AT.ACCOMPLISHMENT
    AND C.EMPLID = :v1
    AND C.EMPL_RCD = 0
    AND C.EFFDT = (SELECT MAX(H.EFFDT)
        FROM PS_GVT_JOB H
   WHERE H.EMPLID = C.EMPLID
                      H.EFFDT < :v2
                      AND H.PAYGROUP = :v3);

Open in new window

I would highly recommend that you do not use implicit date conversion on the date column.
0
 
OranewAuthor Commented:
Hi MikeOM,
I am on 11.2.0.3 . do I have to put 11.2.0.3 below?

q'[OPTIMIZER_FEATURES_ENABLE('11.2.0.1')]'    ----11.2.0.3
                        , q'[DB_VERSION('11.2.0.1')]'          ----11.2.0.3
0
 
OranewAuthor Commented:
Hi Joneson,
How can I pass variable for the date?
It is not taking and oracle says it is not declared?
variable v1 number;
variable v2 varchar2(100);
variable v3 varchar2(100);
]begin
  :v1 := 1234567;
  :v2 := '19-FEB-14';
  :v3 := 'ARS';
0
 
johnsoneSenior Oracle DBACommented:
You set the value for the date in the anonymous block.  Your original query specified a string and not a date which would cause an implicit conversion, so that is the way I left it.

Also, just as an FYI, DATE is not a supported type for a variable in SQL*Plus.
0
 
MikeOM_DBACommented:
I am on 11.2.0.3 . do I have to put 11.2.0.3 below?
Yes.
0
 
OranewAuthor Commented:
MikeOM,
I checked that the manual profile I created is not used in the plan.
Do I need to do something else?
It shows it is still using the old profile the created using oem months ago
0
 
johnsoneSenior Oracle DBACommented:
I haven't played with profiles in a long time.  Unless something has changed (which is entirely possible), the query has to be an exact match.  Spacing, punctuation, capitalization, everything.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.