Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Oracle sql profile using Hint

Posted on 2015-02-13
10
Medium Priority
?
201 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 1600 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
Fill in the form and get your FREE NFR key NOW!

Veeam® is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 

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 400 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

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

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

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to take different types of Oracle backups using RMAN.

721 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