Solved

Oracle sql profile using Hint

Posted on 2015-02-13
10
199 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
Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

 

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

Get HTML5 Certified

Want to be a web developer? You'll need to know HTML. Prepare for HTML5 certification by enrolling in July's Course of the Month! It's free for Premium Members, Team Accounts, and Qualified Experts.

Question has a verified solution.

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

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
Suggested Courses

623 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