Solved

Oracle sql profile using Hint

Posted on 2015-02-13
10
193 Views
Last Modified: 2015-02-20
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
0
Comment
Question by:Oranew
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 2
10 Comments
 
LVL 35

Expert Comment

by:johnsone
ID: 40608846
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
 
LVL 29

Assisted Solution

by:MikeOM_DBA
MikeOM_DBA earned 400 total points
ID: 40608859
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
 
LVL 35

Expert Comment

by:johnsone
ID: 40608860
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:Oranew
ID: 40613907
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
 

Author Comment

by:Oranew
ID: 40613912
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
 
LVL 35

Expert Comment

by:johnsone
ID: 40614024
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
 
LVL 35

Accepted Solution

by:
johnsone earned 100 total points
ID: 40614322
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
 
LVL 29

Expert Comment

by:MikeOM_DBA
ID: 40615641
I am on 11.2.0.3 . do I have to put 11.2.0.3 below?
Yes.
0
 

Author Comment

by:Oranew
ID: 40622208
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
 
LVL 35

Expert Comment

by:johnsone
ID: 40622210
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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

737 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question