« Oracle 11GR2 Upgrade Resources | Main | Include-Directive for SQL-Scripts with Powershell (II) »
Monday
May032010

Modifying an execution plan inside a View using a Query Block Hint (QB_NAME hint)

DateMonday, May 3, 2010 at 7:35PM

Optimizer Hints like INDEX, CARDINALITY, ORDERED, ... are local settings to a SQL statement. Local means the context given is the context of the SQL statement, the scope is local. If you query a View you cannot use for example an Index hint because the table names and aliases are hidden behind the view. So normal hinting would not work without  changing the view to add a hint. With Query Blocks you are able to tag the SQL sections in your view and if the view is merge-able to address the named Query Bocks with Hints from outside of the View - something like Deep Hinting;

Usually a query block is system generated and used by the optimizer to navigate thru a query. So you would not know the name to be generated and this would make it hard to use them with hints.

But you are able to define query block names on your own.

An example of use

First the introduction of the critical SQL :

SELECT Oasis.Vw_Activity_Customer.Id
FROM   Oasis.Vw_Activity_Customer
WHERE  Upper(TRIM(Vw_Activity_Customer.Custname)) LIKE Upper(TRIM(:P01)) ESCAPE '\'
AND    Vw_Activity_Customer.Activitytypekey = '33'
AND    Vw_Activity_Customer.Act_Grp = 99
AND    Vw_Activity_Customer.Activitydate >= To_Date('2010-03-04 00:00:00',
                                                   'YYYY-MM-DD HH24:MI:SS')
AND    Vw_Activity_Customer.Activitydate < To_Date('2010-05-04 00:00:00',
                                                  'YYYY-MM-DD HH24:MI:SS')

 

The view text (shortened), between the customer and it's activities there are tow relations; direct and via additional relations; This is implemented as view with two selects combined with UNION ALL;

CREATE OR REPLACE VIEW VW_ACTIVITY_CUSTOMER AS
SELECT Act.Id                   AS Id
      ,Cnt.Customerid           AS Customerid
...
      ,Com.Legalname
      ,Cst.Firstname
      ,Cst.Lastname      
FROM   Contact                  Cnt
...
      ,Customer                 Com
WHERE  Act.Id = Afc.Parentobject
...
AND    Cnt.Customerid = Com.Id
UNION ALL
SELECT Act.Id                   AS Id
      ,Act.Customerid           AS Customerid
      ,Com.Legalname
      ,NULL
      ,NULL
FROM   Activity Act
      ,Customer Com
WHERE  Act.Customerid = Com.Id;

 

The explain plan : the leading Access predicate should be the Custname - comparison,
which is used in the first part of the SQL (accessing function based index CUSTOMER_FX01) but not in the second part;
expected cardinality due to the LIKE(TRIM(:P01)) expression is to high.
You can see also the system generated Query Block Names : SET$1, SEL$2, SEL$3

Description                                 Object Name                  Cost   Cardinality Qblock name
SELECT STATEMENT, GOAL = ALL_ROWS                                       20139   1388085    
 VIEW                                       VW_ACTIVITY_CUSTOMER        20139   1388085     SET$1
  UNION-ALL                                                                                 SET$1
   NESTED LOOPS                                                         20110   1388084     SEL$2
    HASH JOIN                                                           20075   1388084    
     PARTITION LIST ALL                                                   175      1961    
      TABLE ACCESS BY LOCAL INDEX ROWID     CUSTOMER                      175      1961     SEL$2
       INDEX RANGE SCAN                     CUSTOMER_FX01                   3       353     SEL$2
     HASH JOIN                                                          19886   2956487    
      PARTITION LIST ALL                                                  102    132900    
       TABLE ACCESS FULL                    CONTACT                      1027    132900     SEL$2
      HASH JOIN                                                          7251   2956487    
       PARTITION LIST SINGLE                                               28      1018    
        TABLE ACCESS FULL                   ACTIVITY                       28      1018     SEL$2
       PARTITION LIST SINGLE                                             7196   5512480    
        TABLE ACCESS FULL                   ACT_FC                       7196   5512480     SEL$2
    INDEX UNIQUE SCAN                       CONTACTSTATICS_PK               1         1     SEL$2
   NESTED LOOPS                                                            29         1     SEL$3
    PARTITION LIST SINGLE                                                  28      1018    
     TABLE ACCESS FULL                      ACTIVITY                       28      1018     SEL$3
    TABLE ACCESS BY GLOBAL INDEX ROWID      CUSTOMER                        1         1     SEL$3
     INDEX UNIQUE SCAN                      CUSTOMER_PK                     1         1     SEL$3

This SQL with :P01='boston%' run over 20 seconds retrieving about 300 rows.

Hunting the Hint

Now placing an index hint to change the execution plan would not work ...

SELECT /*+ INDEX (CUSTOMER CUSTOMER_FX01*/
     Oasis.Vw_Activity_Customer.Id
FROM Oasis.Vw_Activity_Customer
...

 ... because the hint addressed the table CUSTOMER which is not visible in the current sql scope -  the hint would not work; A Method would be needed to address the sql sections inside of the view.

 

Don't leave anything to chance : using Query Block Names

The view is instrumented with user defined query block names using the QB_NAME hint.
Interesting that the syntax is the same like with other hints but does not affect the execution plan at all.
It's just tagging.

CREATE OR REPLACE VIEW VW_ACTIVITY_CUSTOMER AS
SELECT /*+ QB_NAME (SUB01) */
       Act.Id                   AS Id
      ,Cnt.Customerid           AS Customerid
...
      ,Com.Legalname
      ,Cst.Firstname
      ,Cst.Lastname      
FROM   Contact                  Cnt
...
      ,Customer                 Com
WHERE  Act.Id = Afc.Parentobject
...
AND    Cnt.Customerid = Com.Id
UNION ALL
SELECT /*+ QB_NAME (SUB02) */
       Act.Id                   AS Id
      ,Act.Customerid           AS Customerid
      ,Com.Legalname
      ,NULL
      ,NULL
FROM   Activity Act
      ,Customer Com
WHERE  Act.Customerid = Com.Id;

 

 You are able then to use hints addressing he hidden objects with Query Block Names;

SELECT /*+ FIRST_ROWS
           INDEX(@SUB01 COM@SUB01 CUSTOMER_FX01)
           INDEX(@SUB02 COM@SUB02 CUSTOMER_FX01) */
 Isis.Vw_Activity_Customer.Id
FROM   Isis.Vw_Activity_Customer
WHERE  Upper(TRIM(Vw_Activity_Customer.Legalname)) LIKE Upper(TRIM(:P01)) ESCAPE '\'
AND    Vw_Activity_Customer.Activitytypekey = '33'
AND    Vw_Activity_Customer.Act_Grp = 99
AND    Vw_Activity_Customer.Activitydate >= To_Date('2010-03-04 00:00:00',
                                                    'YYYY-MM-DD HH24:MI:SS')
AND    Vw_Activity_Customer.Activitydate < To_Date('2010-05-04 00:00:00',
                                                   'YYYY-MM-DD HH24:MI:SS')
AND    (Vw_Activity_Customer.Activitystate = 2 OR Vw_Activity_Customer.Activitystate = 4 OR
        Vw_Activity_Customer.Activitystate = 5)

Explaining the hints ...

  • FIRST_ROWS : avoid Hash joins caused by wrong cardianlity estimation
  • INDEX(@SUB01 COM@SUB01 CUSTOMER_FX01)
    • @SUB01 : scope is now Query block SUB01
    • COM@SUB01 : The customer Table (it's alias) with Scope SUB01 is referenced
    • CUSTOMER_FX01 : Name of the index to be used containing UPPER(TRIM(Custname))

 

The resulting explain plan,
interesting : the plan with the dramatically higher costs caused dramatically better response time;
You can see also the user generated Query Block Names : SUB01, SUB02;

Description                                     Object Name                    Cost    Cardinality   Qblock Name
SELECT STATEMENT, GOAL = HINT: FIRST_ROWS                                      6616488     1388085    
 VIEW                                           VW_ACTIVITY_CUSTOMER           6616488     1388085   SET$1
  UNION-ALL                                                                                          SET$1
   NESTED LOOPS                                                                6615333     1388084   SUB01
    NESTED LOOPS                                                               6615298     1388084   
     NESTED LOOPS                                                              5320848     2588133   
      NESTED LOOPS                                                               15869       62397   
       PARTITION LIST ALL                                                          175        1961   
        TABLE ACCESS BY LOCAL INDEX ROWID       CUSTOMER                           175        1961   SUB01
         INDEX RANGE SCAN                       CUSTOMER_FX01                        3         353   SUB01
       PARTITION LIST ALL                                                            8          32   
        TABLE ACCESS BY LOCAL INDEX ROWID       CONTACT                              8          32   SUB01
         INDEX RANGE SCAN                       CONACT_COMANY_ASSOCI_API_FK02        1          32   SUB01
      PARTITION LIST SINGLE                                                         85          41    
       TABLE ACCESS BY LOCAL INDEX ROWID        ACT_FC                              85          41   SUB01
        INDEX RANGE SCAN                        ACTCTS_CONACT_LINKED_API_FK02        1         153   SUB01
     TABLE ACCESS BY GLOBAL INDEX ROWID         ACTIVITY                             1           1   SUB01
      INDEX UNIQUE SCAN                         ACTIVITY_PK                          1           1   SUB01
    INDEX UNIQUE SCAN                           CONTACTSTATICS_PK                    1           1   SUB01
   TABLE ACCESS BY LOCAL INDEX ROWID            ACTIVITY                             1           1   SUB02
    NESTED LOOPS                                                                  1155           1    
     PARTITION LIST ALL                                                            175        1961    
      TABLE ACCESS BY LOCAL INDEX ROWID         CUSTOMER                           175        1961   SUB02
       INDEX RANGE SCAN                         CUSTOMER_FX01                        3         353   SUB02
     PARTITION LIST SINGLE                                                           1           5   
      INDEX RANGE SCAN                          ACTIVITY_IX03                        1           5   SUB02
                                                                                      

The same SQL with additional hints did run < 0.5 seconds.

Conclusion

Using Query block names may not only be helpful setting hints in a more complex SQL
but also to set hints if the SQL itself is defined behind a view. this helps to let the view untouched
and tune your SQL for the specific case;

I think this would work only with merge-able views;

/Karl Reitschuster

 

 

AuthorKarl Reitschuster | Comment5 Comments | Reference3 References |
in , , ,

References (3)

References allow you to track sources for this article, as well as articles that were written in response to this article.
  • Source
    Source: Query Blocks
    by Jonathan lewis on June 25, 2007
  • Source
    Source: Optimizer Hints
    by Oracle
  • Response
    Response: wedding photograpchy in cheltenham
    at wedding photograpchy in cheltenham on September 30, 2013
    Karl Reitschuster's Tech Space - ORACLE RDBMS - Modifying an execution plan inside a View using a Query Block Hint (QB_NAME hint)

Reader Comments (5)

Both of the explain plans are identical, is this expected?

May 4, 2010 | Unregistered CommenterDoug Phelps

No Doug that's a wrong copy
thanks for the hint!
will fix this

/Karl

May 5, 2010 | Unregistered CommenterKarl

Fixed!
/Karl

May 5, 2010 | Registered CommenterKarl Reitschuster

If only I had a dime for every time I came here... Superb article!

May 29, 2010 | Unregistered CommenterGenaro Kang

Thanks Genaro,
you are welcome!
/Karl

May 30, 2010 | Registered CommenterKarl Reitschuster

PostPost a New Comment

Enter your information below to add a new comment.

My response is on my own website »
Author Email (optional):
Author URL (optional):
Post:
 
Some HTML allowed: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <code> <em> <i> <strike> <strong>

Notify me of follow-up comments via email.