Solved

Oracle sql profile using Hint

Posted on 2015-02-13
10
174 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
  • 5
  • 3
  • 2
10 Comments
 
LVL 34

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 34

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
 

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 34

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 34

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 34

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
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.

758 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now