Modifying an execution plan inside a View using a Query Block Hint (QB_NAME hint)
Monday, 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
Karl Reitschuster
Additionally i put the row source created by 10046 trace file; Row Source plans show effective amount of rows processed during the execution of the SQL;
Untuned version ...
SELECT OASIS.VW_ACTIVITY_CUSTOMER.ID
FROM
OASIS.VW_ACTIVITY_CUSTOMER WHERE UPPER(TRIM(VW_ACTIVITY_CUSTOMER.LEGALNAME))
LIKE UPPER(TRIM(:B1 )) ESCAPE '\' AND VW_ACTIVITY_CUSTOMER.ACTIVITYTYPEKEY =
'20' AND VW_ACTIVITY_CUSTOMER.ACT_GRP = 1 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)
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.02 0.02 0 0 0 0
Fetch 1 6.16 14.63 31061 37572 0 322
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 6.18 14.65 31061 37572 0 322
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 55 (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
322 VIEW VW_ACTIVITY_CUSTOMER (cr=37572 pr=31061 pw=0 time=10632896 us)
322 UNION-ALL (cr=37572 pr=31061 pw=0 time=10632573 us)
322 NESTED LOOPS (cr=37455 pr=31061 pw=0 time=10631606 us)
322 HASH JOIN (cr=37131 pr=31061 pw=0 time=10624844 us)
8 PARTITION LIST ALL PARTITION: 1 3 (cr=12 pr=0 pw=0 time=139 us)
8 TABLE ACCESS BY LOCAL INDEX ROWID CUSTOMER PARTITION: 1 3 (cr=12 pr=0 pw=0 time=149 us)
8 INDEX RANGE SCAN CUSTOMER_FX01 PARTITION: 1 3 (cr=4 pr=0 pw=0 time=78 us)(object id 153894)
1978537 HASH JOIN (cr=37119 pr=31061 pw=0 time=15342074 us)
135184 PARTITION LIST ALL PARTITION: 1 2 (cr=4643 pr=0 pw=0 time=135259 us)
135184 TABLE ACCESS FULL CONTACT PARTITION: 1 2 (cr=4643 pr=0 pw=0 time=111 us)
1978537 HASH JOIN (cr=32476 pr=31061 pw=0 time=11232741 us)
705 PARTITION LIST SINGLE PARTITION: KEY KEY (cr=117 pr=0 pw=0 time=934 us)
705 TABLE ACCESS FULL ACTIVITY PARTITION: 2 2 (cr=117 pr=0 pw=0 time=927 us)
5527258 PARTITION LIST SINGLE PARTITION: 1 1 (cr=32359 pr=31061 pw=0 time=27653586 us)
5527258 TABLE ACCESS FULL ACT_FC PARTITION: 1 1 (cr=32359 pr=31061 pw=0 time=22126319 us)
322 INDEX UNIQUE SCAN CONTACTSTATICS_PK (cr=324 pr=0 pw=0 time=5499 us)(object id 151514)
0 NESTED LOOPS (cr=117 pr=0 pw=0 time=3128 us)
705 PARTITION LIST SINGLE PARTITION: KEY KEY (cr=117 pr=0 pw=0 time=1633 us)
705 TABLE ACCESS FULL ACTIVITY PARTITION: 2 2 (cr=117 pr=0 pw=0 time=1627 us)
0 TABLE ACCESS BY GLOBAL INDEX ROWID CUSTOMER PARTITION: ROW LOCATION ROW LOCATION (cr=0 pr=0 pw=0 time=1620 us)
0 INDEX UNIQUE SCAN CUSTOMER_PK (cr=0 pr=0 pw=0 time=632 us)(object id 151212)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
db file scattered read 2471 0.04 9.03
db file sequential read 100 0.02 0.06
********************************************************************************
Tuned Version ...
SELECT /*+ FIRST_ROWS
INDEX(@SUB01 COM@SUB01 CUSTOMER_FX01)
INDEX(@SUB02 COM@SUB02 CUSTOMER_FX01) */ OASIS.VW_ACTIVITY_CUSTOMER.ID FROM OASIS.VW_ACTIVITY_CUSTOMER WHERE UPPER(TRIM(VW_ACTIVITY_CUSTOMER.LEGALNAME)) LIKE UPPER(TRIM(:B1 )) ESCAPE '\' AND VW_ACTIVITY_CUSTOMER.ACTIVITYTYPEKEY = '20' AND VW_ACTIVITY_CUSTOMER.ACT_GRP = 1 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)
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.02 0.02 0 0 0 0
Fetch 1 0.02 0.01 0 4677 0 322
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.04 0.03 0 4677 0 322
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: FIRST_ROWS
Parsing user id: 55 (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
322 VIEW VW_ACTIVITY_CUSTOMER (cr=4677 pr=0 pw=0 time=17080 us)
322 UNION-ALL (cr=4677 pr=0 pw=0 time=17076 us)
322 NESTED LOOPS (cr=4649 pr=0 pw=0 time=16752 us)
322 NESTED LOOPS (cr=4325 pr=0 pw=0 time=15136 us)
1000 NESTED LOOPS (cr=1322 pr=0 pw=0 time=7259 us)
82 NESTED LOOPS (cr=80 pr=0 pw=0 time=750 us)
8 PARTITION LIST ALL PARTITION: 1 3 (cr=12 pr=0 pw=0 time=272 us)
8 TABLE ACCESS BY LOCAL INDEX ROWID CUSTOMER PARTITION: 1 3 (cr=12 pr=0 pw=0 time=275 us)
8 INDEX RANGE SCAN CUSTOMER_FX01 PARTITION: 1 3 (cr=4 pr=0 pw=0 time=221 us)(object id 153894)
82 PARTITION LIST ALL PARTITION: 1 2 (cr=68 pr=0 pw=0 time=613 us)
82 TABLE ACCESS BY LOCAL INDEX ROWID CONTACT PARTITION: 1 2 (cr=68 pr=0 pw=0 time=599 us)
82 INDEX RANGE SCAN CONACT_COMANY_ASSOCI_API_FK02 PARTITION: 1 2 (cr=32 pr=0 pw=0 time=172 us)(object id 154952)
1000 PARTITION LIST SINGLE PARTITION: 1 1 (cr=1242 pr=0 pw=0 time=7001 us)
1000 TABLE ACCESS BY LOCAL INDEX ROWID ACT_FC PARTITION: 1 1 (cr=1242 pr=0 pw=0 time=5829 us)
1000 INDEX RANGE SCAN ACTCTS_CONACT_LINKED_API_FK02 PARTITION: 1 1 (cr=248 pr=0 pw=0 time=1540 us)(object id 156515)
322 TABLE ACCESS BY GLOBAL INDEX ROWID ACTIVITY PARTITION: 2 2 (cr=3003 pr=0 pw=0 time=8154 us)
1000 INDEX UNIQUE SCAN ACTIVITY_PK (cr=2002 pr=0 pw=0 time=3377 us)(object id 150781)
322 INDEX UNIQUE SCAN CONTACTSTATICS_PK (cr=324 pr=0 pw=0 time=1004 us)(object id 151514)
0 TABLE ACCESS BY LOCAL INDEX ROWID ACTIVITY PARTITION: 2 2 (cr=28 pr=0 pw=0 time=143 us)
9 NESTED LOOPS (cr=28 pr=0 pw=0 time=203 us)
8 PARTITION LIST ALL PARTITION: 1 3 (cr=12 pr=0 pw=0 time=59 us)
8 TABLE ACCESS BY LOCAL INDEX ROWID CUSTOMER PARTITION: 1 3 (cr=12 pr=0 pw=0 time=62 us)
8 INDEX RANGE SCAN CUSTOMER_FX01 PARTITION: 1 3 (cr=4 pr=0 pw=0 time=34 us)(object id 153894)
0 PARTITION LIST SINGLE PARTITION: KEY KEY (cr=16 pr=0 pw=0 time=66 us)
0 INDEX RANGE SCAN ACTIVITY_IX03 PARTITION: 2 2 (cr=16 pr=0 pw=0 time=46 us)(object id 150838)
/Karl Reitschuster
Reader Comments (5)
Both of the explain plans are identical, is this expected?
No Doug that's a wrong copy
thanks for the hint!
will fix this
/Karl
Fixed!
/Karl
If only I had a dime for every time I came here... Superb article!
Thanks Genaro,
you are welcome!
/Karl