| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Using miss-spelled hint changes explain plan ...
Thanks Rob,
me thought the very same until I ran it through ... here is my script file ...
---------------------------- script file --------------------------set pagesize 0
:"SYS_B_01"
FROM (SELECT /*+ driving_site(a) INDEX(C PF_EVENTS_N2) INDEX(G PF_ACCOUNTS_U2) ORDERED */
DISTINCT a.seg_6, actual actual, h.line_name ch_line,
NVL (NVL (g.line_name, h.line_name),
:"SYS_B_02") line_name
FROM if_classes b, if_classes e, if_classes f, if_events c,
if_event_hours d, if_accounts g, if_accounts h,
(SELECT /*+ USE_NL (GCC) */
gcc.segment6 seg_6,
SUM ( NVL (gb.period_net_dr, :"SYS_B_03")
- NVL (gb.period_net_cr, :"SYS_B_04")) actual,
gcc.segment1 seg_1, gcc.segment3 seg_3,
gcc.segment2 seg_2,
ar.parent_flex_value pfv, ar.parent_name pn,
ar.child_flex_value cfv
FROM gl_code_combinations_at_apps2_aol_dbl gcc,
gl_balances_at_apps2_aol_dbl gb,
v_50132_account_rollups_at_apps2_aol_dbl ar
WHERE gb.code_combination_id = gcc.code_combination_id
AND :"SYS_B_05" = gcc.chart_of_accounts_id
AND TO_DATE (gb.period_name, :"SYS_B_06")
BETWEEN TO_DATE (:"SYS_B_07", :"SYS_B_08")
AND TO_DATE (:"SYS_B_09", :"SYS_B_10")
AND ar.child_flex_value = gcc.segment2
AND ar.parent_flex_value LIKE :"SYS_B_11"
GROUP BY gcc.segment6, gcc.segment1, gcc.segment3, gcc.segment2,
ar.parent_name, ar.parent_flex_value, ar.child_flex_value) a
WHERE c.NETWORK = :"SYS_B_12"
AND c.company_id = :"SYS_B_13"
AND d.event_id = c.event_id
AND d.version_id = :"SYS_B_14"
AND a.seg_6 = c.project_num(+)
AND a.cfv = h.VALUE(+)
AND h.parent_line_name = g.line_name(+)
AND c.analyst_id = c.analyst_id + :"SYS_B_15"
AND :"SYS_B_16" >= -d.event_id
AND c.event_id >= -:"SYS_B_17"
AND a.seg_3 = b.center
AND b.class_id = c.sport_id
AND e.class_id(+) = c.category_id
AND f.class_id(+) = c.sanction_id) val
GROUP BY val.seg_6, val.line_name
:"SYS_B_01"
FROM (SELECT /*+ diving_site(a) INDEX(C PF_EVENTS_N2) INDEX(G PF_ACCOUNTS_U2) ORDERED */
DISTINCT a.seg_6, actual actual, h.line_name ch_line,
NVL (NVL (g.line_name, h.line_name),
:"SYS_B_02") line_name
FROM if_classes b, if_classes e, if_classes f, if_events c,
if_event_hours d, if_accounts g, if_accounts h,
(SELECT /*+ USE_NL (GCC) */
gcc.segment6 seg_6,
SUM ( NVL (gb.period_net_dr, :"SYS_B_03")
- NVL (gb.period_net_cr, :"SYS_B_04")) actual,
gcc.segment1 seg_1, gcc.segment3 seg_3,
gcc.segment2 seg_2,
ar.parent_flex_value pfv, ar.parent_name pn,
ar.child_flex_value cfv
FROM gl_code_combinations_at_apps2_aol_dbl gcc,
gl_balances_at_apps2_aol_dbl gb,
v_50132_account_rollups_at_apps2_aol_dbl ar
WHERE gb.code_combination_id = gcc.code_combination_id
AND :"SYS_B_05" = gcc.chart_of_accounts_id
AND TO_DATE (gb.period_name, :"SYS_B_06")
BETWEEN TO_DATE (:"SYS_B_07", :"SYS_B_08")
AND TO_DATE (:"SYS_B_09", :"SYS_B_10")
AND ar.child_flex_value = gcc.segment2
AND ar.parent_flex_value LIKE :"SYS_B_11"
GROUP BY gcc.segment6, gcc.segment1, gcc.segment3, gcc.segment2,
ar.parent_name, ar.parent_flex_value, ar.child_flex_value) a
WHERE c.NETWORK = :"SYS_B_12"
AND c.company_id = :"SYS_B_13"
AND d.event_id = c.event_id
AND d.version_id = :"SYS_B_14"
AND a.seg_6 = c.project_num(+)
AND a.cfv = h.VALUE(+)
AND h.parent_line_name = g.line_name(+)
AND c.analyst_id = c.analyst_id + :"SYS_B_15"
AND :"SYS_B_16" >= -d.event_id
AND c.event_id >= -:"SYS_B_17"
AND a.seg_3 = b.center
AND b.class_id = c.sport_id
AND e.class_id(+) = c.category_id
AND f.class_id(+) = c.sanction_id) val
GROUP BY val.seg_6, val.line_name
Here is the output from the script file above ....
Predicate Information (identified by operation id):
5 - filter(TO_DATE(:Z,:Z)<=TO_DATE(:Z,:Z))
7 - access("AR"."CHILD_FLEX_VALUE"="H"."VALUE"(+))
8 - access("AR"."CHILD_FLEX_VALUE"="GCC"."SEGMENT2")
14 - access("B"."CLASS_ID"="C"."SPORT_ID")
15 - filter("B"."CENTER" IS NOT NULL)
16 - filter("C"."NETWORK"=:Z AND "C"."COMPANY_ID"=TO_NUMBER(:Z) AND "C"."ANALYST_ID"="C"."ANALYST_ID"+TO_NUMBER(:Z) AN
D "C"."EVENT_ID">=(-TO_NUMBER(:Z)))
17 - filter("C"."PROJECT_NUM" IS NOT NULL)
18 - access("E"."CLASS_ID"(+)="C"."CATEGORY_ID")
19 - access("F"."CLASS_ID"(+)="C"."SANCTION_ID")
20 - access("D"."EVENT_ID"="C"."EVENT_ID" AND "D"."VERSION_ID"=TO_NUMBER(:Z))
filter((-"D"."EVENT_ID")<=TO_NUMBER(:Z) AND "D"."EVENT_ID">=(-TO_NUMBER(:Z)))
25 - access("H"."PARENT_LINE_NAME"="G"."LINE_NAME"(+))
Note: cpu costing is off
50 rows selected.
26 rows deleted.
Commit complete.
Explained.
| Id | Operation | Name | Rows | Bytes | Cost | TQ |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 54 | 20 | | | | | 1 | SORT GROUP BY | | 1 | 54 | 20 | | | | | 2 | VIEW | | 1 | 54 | 17 | | | | | 3 | SORT UNIQUE | | 1 | 539 | 17 | | | | | 4 | SORT GROUP BY | | 1 | 539 | 17 | | | | |* 5 | FILTER | | | | | | | | | 6 | NESTED LOOPS | | 1 | 539 | 12 | | | | | 7 | NESTED LOOPS OUTER | | 1 | 491 | 10 | | | | | 8 | NESTED LOOPS OUTER | | 1 | 480 | 10 | | | | | 9 | NESTED LOOPS | | 1 | 469 | 10 | | | | | 10 | NESTED LOOPS | | 1 | 456 | 9 | | | | | 11 | NESTED LOOPS | | 1 | 440 | 9 | | | | | 12 | NESTED LOOPS | | 1 | 402 | 7 | | | | | 13 | NESTED LOOPS OUTER | | 1 | 320 | 5 | | | | |* 14 | HASH JOIN OUTER | | 1 | 304 | 4 | | | | | 15 | REMOTE | | 1 | 276 | |WO,LD |SERIAL| | | 16 | TABLE ACCESS FULL | IF_ACCOUNTS | 103 | 2884 | 3 | | | | |* 17 | INDEX RANGE SCAN | PF_ACCOUNTS_U2 | 1 | 16 | 1 | | | | | 18 | REMOTE | | 1 | 82 | |WO,LD |SERIAL| | |* 19 | TABLE ACCESS BY INDEX ROWID| IF_EVENTS | 1 | 38 | 2 | | | | |* 20 | INDEX RANGE SCAN | PF_EVENTS_N2 | 1 | | 1 | | | | |* 21 | INDEX UNIQUE SCAN | PK_PF_EVENT_HOURS | 1 | 16 | | | | | |* 22 | TABLE ACCESS BY INDEX ROWID | IF_CLASSES | 1 | 13 | 1 | | | | |* 23 | INDEX UNIQUE SCAN | PF_CLASSES_PK | 15 | | | | | | |* 24 | INDEX UNIQUE SCAN | PF_CLASSES_PK | 1 | 11 | | | | | |* 25 | INDEX UNIQUE SCAN | PF_CLASSES_PK | 1 | 11 | | | | | | 26 | REMOTE | | 1 | 48 | |WO,LD |SERIAL| |
------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
5 - filter(TO_DATE(:Z,:Z)<=TO_DATE(:Z,:Z))
14 - access("AR"."CHILD_FLEX_VALUE"="H"."VALUE"(+))
17 - access("H"."PARENT_LINE_NAME"="G"."LINE_NAME"(+))
19 - filter("C"."NETWORK"=:Z AND "C"."COMPANY_ID"=TO_NUMBER(:Z) AND "C"."ANALYST_ID"="C"."ANALYST_ID"+TO_NUMBER(:Z)
AND "C"."EVENT_ID">=(-TO_NUMBER(:Z)))
20 - access("GCC"."SEGMENT6"="C"."PROJECT_NUM")
filter("C"."PROJECT_NUM" IS NOT NULL)
21 - access("D"."EVENT_ID"="C"."EVENT_ID" AND "D"."VERSION_ID"=TO_NUMBER(:Z))
filter((-"D"."EVENT_ID")<=TO_NUMBER(:Z) AND "D"."EVENT_ID">=(-TO_NUMBER(:Z)))
22 - filter("GCC"."SEGMENT3"="B"."CENTER" AND "B"."CENTER" IS NOT NULL)
23 - access("B"."CLASS_ID"="C"."SPORT_ID")
24 - access("E"."CLASS_ID"(+)="C"."CATEGORY_ID")
25 - access("F"."CLASS_ID"(+)="C"."SANCTION_ID")
Note: cpu costing is off
51 rows selected.
---------------------------- output file --------------------------
something somewhere is scre*ed up and the plan with less cost didn't do very well anyways ... Without driving_site or diving_site the plan still comes up with a cost of 2008.
What gives?
Raj
-----Original Message-----
Sent: Thursday, November 20, 2003 11:25 AM
To: Multiple recipients of list ORACLE-L
If you miss-spell a hint, it is ignored.
select /*+ FULL (PERSON) */ full_name from person where emplid = '123456'; Execution Plan
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=56 Card=1 Bytes=25) 1 0 TABLE ACCESS (FULL) OF 'PERSON' (Cost=56 Card=1 Bytes=25)
select /*+ FULLOFIT (PERSON) */ full_name from person where emplid = '123456'; Execution Plan
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=25) 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'PERSON' (Cost=2 Card=1 Bytes=25) 2 1 INDEX (UNIQUE SCAN) OF 'PK_EMPLID_1' (UNIQUE) (Cost=1 Card=1)
Rob Caldwell
DBA - United Space Alliance
www.unitedspacealliance.com
-----Original Message-----
Sent: Thursday, November 20, 2003 10:20 AM
To: Multiple recipients of list ORACLE-L
Okay,
I found this by accident ...
I was trying to get explain plan on a query that involved a in-line-query that selects from financials database. This in-line query was alias'd as "A". Current cost of query was ~2800.
So, to test for explain plan I added hint
/*+ diving_site(A) */ -- note the spelling mistake here ... the cost of the query changed to 25. I corrected the spelling mistake and make it driving_site, cost went back to ~2900.
Has anyone experienced this before? Our original db is 9202 and financials is on a RBO, 805 db accessed through a db_link.
Raj
**************************************************************************************5 **************************************************************************************This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.
**************************************************************************************5
-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jamadagni, Rajendra INET: Rajendra.Jamadagni_at_espn.com Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).Received on Thu Nov 20 2003 - 10:49:57 CST
 |
 |