Apr 10: Topsy Turvy II - The plans
First, I've been ill and whilst my brain is functioning at the level of casual browsing, any deep or determined thought is off the agenda. (I'm still not back at work, but I'm so bored that I've mustered half an hour for this.) Second, it was a pain in the butt obfuscating the code. Third, I made the mistake of sending the execution plans to Jonathan Lewis. I think I've mentioned before that, if you're going to ask someone to review something, don't pick someone with a forensic eye for detail who only asks more questions ;-> I suspect that Jonathan likes to encourage people with 'that's strange, what do you make of that?' type comments. Which are good fun until you're feeling very poorly and sorry for yourself. I've got as far as having a quick look at chapter 9 of his book on Query Transformations but it was all too much. I know I'm going to hit those answers soon, but can't say when! I've decided to just post it 'as is' because the comments and subsequent dialogue are educational in themselves, despite me not having all of the answers yet. Anyway, on with the task at hand.
The query improvement I mentioned last week has generated many interested (and interesting) comments. I think people are fascinated by anything to do with the workings of the Cost Based Optimiser. Out of all the thought-provoking comments, I particularly liked this one from Jonathan Lewis because he expressed what I knew I was trying to achieve with the NO_UNNEST hint but didn't express properly myself.
"which means the remote subquery was probably executed as a late running filter"
Whether I achieved that is debatable. (However I'm reminded of this comment - "... deciphering execution plans as best they can and then flailing around trying out different solutions until they get lucky?" in this review.)
Hopefully this additional information will be enough to decide but I suspect it'll just throw up more interesting questions
As I mentioned in the original blog, I need to respect client confidentiality so I've modified the table and column names in the examples shown here. Any columns are prefixed with an n for NUMBER, because they all happened to be numbers (which is slightly interesting itself). Where columns in two tables have identical names and are clearly join columns, I've used the same name in all tables. I didn't bother changing the key section of code quoted in the original blog.
That's all I've changed but it was quite a laborious task so hopefully I haven't introduced any errors. The obfuscation has also made the examples more difficult to read, in my opinion, but I don't want to be too specific about an individual business' application.
First, here is the original query without the no_unnest hint.
SELECT (a very long ...) column_list
FROM tab7, tab2, tab3, tab5, tab8, tab4, tab1, tab6, tab9
WHERE tab7.n11 = 3
AND tab7.n4 = tab2.n4
AND tab2.n5 = tab3.n5
AND tab3.n6 = tab5.n6
AND tab5.n8 IS NOT NULL
AND tab5.n6 = tab6.n6
AND tab6.n10 IS NULL
AND tab7.n12 = tab8.n13
AND tab8.n7 = tab4.n7
AND tab8.n7 = tab9.n7
AND tab8.n3 = tab1.n3
AND tab1.n1 = tab9.n1
AND tab1.n1 = tab6.n1
AND NVL (tab1.n2, 0) = 0
AND tab5.n8 = tab4.n8
AND tab4.n9 IN (
SELECT int_value
FROM v_extn_system_parameters
WHERE process_key = 'CAFTE003'
AND parameter_name LIKE 'ACCRUED%')
Now on to the execution plans. Here are some high-level details of where the plans came from.
1) The Oracle server version is 9.2.0.7
2) The plans were produced using dbms_xplan. I'd originally used set autotrace traceonly explain with an old-style plan table (didn't want to mess with the environment by changing it) but there's more information available this way.
explain plan for statement;
select * from TABLE(dbms_xplan.display);
3) The statement was originally an INSERT .. SELECT, but I just tuned the SELECT block.
4) These plans are from a test environment that should be identical to production.
5) v_extn_system_parameters is a view in the local database against a real table in the remote database. The sole purpose of the view is to obscure some update timestamp columns.
6) The indexes used in the plans are
TAB1_PK on TAB1(N3)
TAB3_PK on TAB3(N5)
REMOTE_PK on Remote table
---------------------------------------------------------------------------------------------------And here is the plan after using the /*+ NO_UNNEST */ hint in the subquery.
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)|
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 13 | 2457 | | 32659 (3)|
|* 1 | HASH JOIN | | 13 | 2457 | | 32659 (3)|
|* 2 | TABLE ACCESS BY INDEX ROWID| TAB1 | 1 | 23 | | 3 (34)|
| 3 | NESTED LOOPS | | 13 | 2275 | | 32651 (3)|
|* 4 | HASH JOIN | | 889 | 131K| 36M| 30871 (3)|
|* 5 | HASH JOIN | | 264K| 33M| | 1440 (5)|
|* 6 | HASH JOIN | | 5955 | 604K| | 92 (11)|
|* 7 | HASH JOIN | | 2978 | 255K| | 63 (13)|
| 8 | TABLE ACCESS FULL | TAB2 | 9788 | 88092 | | 19 (6)|
|* 9 | HASH JOIN | | 2978 | 229K| | 43 (14)|
| 10 | INDEX FAST FULL SCAN| TAB3_PK | 9788 | 88092 | | 9 (12)|
|* 11 | HASH JOIN | | 2978 | 203K| | 33 (13)|
|* 12 | HASH JOIN | | 5 | 305 | | 11 (28)|
| 13 | SORT UNIQUE | | | | | |
| 14 | REMOTE | | 1 | 47 | | |
| 15 | TABLE ACCESS FULL | TAB4 | 1124 | 15736 | | 7 (15)|
|* 16 | TABLE ACCESS FULL | TAB5 | 9758 | 87822 | | 22 (5)|
|* 17 | TABLE ACCESS FULL | TAB6 | 19574 | 305K| | 28 (4)|
|* 18 | TABLE ACCESS FULL | TAB7 | 432K| 11M| | 1343 (5)|
| 19 | TABLE ACCESS FULL | TAB8 | 6031K| 115M| | 18348 (3)|
|* 20 | INDEX RANGE SCAN | TAB1_PK | 1 | | | 2 (50)|
| 21 | TABLE ACCESS FULL | TAB9 | 2232 | 31248 | | 7 (15)|
---------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("TAB8"."N7"="TAB9"."N7" AND "TAB1"."N1"="TAB9"."N1")
2 - filter(NVL("TAB1"."N2",0)=0 AND "TAB1"."N1"="TAB6"."N1")
4 - access("TAB7"."N12"="TAB8"."N13" AND "TAB8"."N7"="TAB4"."N7")
5 - access("TAB7"."N4"="TAB2"."N4")
6 - access("TAB5"."N6"="TAB6"."N6")
7 - access("TAB2"."N5"="TAB3"."N5")
9 - access("TAB3"."N6"="TAB5"."N6")
11 - access("TAB5"."N8"="TAB4"."N8")
12 - access("TAB4"."N9"="EXTN_SYSTEM_PARAMETERS"."INT_VALUE")
16 - filter("TAB5"."N8" IS NOT NULL)
17 - filter("TAB6"."N10" IS NULL)
18 - filter("TAB7"."N11"=3)
20 - access("TAB8"."N3"="TAB1"."N3")
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)|
------------------------------------------------------------------------------------------------|
| 0 | SELECT STATEMENT | | 1206 | 167K| | 45991 (5)|
|* 1 | FILTER | | | | | |
|* 2 | HASH JOIN | | 1206 | 167K| | 42373 (2)|
|* 3 | HASH JOIN | | 1207 | 148K| | 42344 (2)|
|* 4 | TABLE ACCESS FULL | TAB5 | 9758 | 87822 | | 22 (5)|
|* 5 | HASH JOIN | | 16997 | 1942K| | 42321 (2)|
| 6 | INDEX FAST FULL SCAN | TAB3_PK | 9788 | 88092 | | 9 (12)|
|* 7 | HASH JOIN | | 16997 | 1792K| | 42312 (2)|
| 8 | TABLE ACCESS FULL | TAB2 | 9788 | 88092 | | 19 (6)|
|* 9 | HASH JOIN | | 16896 | 1633K| | 42292 (2)|
| 10 | TABLE ACCESS FULL | TAB9 | 2232 | 31248 | | 7 (15)|
|* 11 | HASH JOIN | | 17017 | 1412K| 4808K| 42284 (2)|
|* 12 | HASH JOIN | | 71245 | 3965K| | 39878 (2)|
|* 13 | TABLE ACCESS FULL | TAB1 | 44542 | 1000K| | 21422 (1)|
|* 14 | HASH JOIN | | 2492K| 80M| | 18426 (4)|
| 15 | TABLE ACCESS FULL| TAB4 | 56 | 784 | | 7 (15)|
| 16 | TABLE ACCESS FULL| TAB8 | 6031K| 115M| | 18348 (3)|
|* 17 | TABLE ACCESS FULL | TAB7 | 432K| 11M| | 1343 (5)|
|* 18 | TABLE ACCESS FULL | TAB6 | 19574 | 305K| | 28 (4)|
| 19 | REMOTE | | 1 | 47 | | |
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( EXISTS (SELECT /*+ NO_UNNEST */ 0 FROM "EXTN_SYSTEM_PARAMETERS"@ADM "EXTN_SYSTEM_PARAMETERS"
WHERE "EXTN_SYSTEM_PARAMETERS"."PROCESS_KEY"='CAFTE003' AND "EXTN_SYSTEM_PARAMETERS"."INT_VALUE"=:B1 AND
"EXTN_SYSTEM_PARAMETERS"."PARAMETER_NAME" LIKE 'ACCRUED%'))
2 - access("TAB5"."N6"="TAB6"."N6" AND "TAB1"."N1"="TAB5"."N1")
3 - access("TAB3"."N6"="TAB5"."N6" AND "TAB5"."N8"="SYS_ALIAS_1"."N8")
4 - filter("TAB5"."N8" IS NOT NULL)
5 - access("TAB2"."N5"="TAB3"."N5")
7 - access("TAB7"."N4"="TAB2"."N4")
9 - access("TAB8"."N7"="TAB9"."N7" AND "TAB1"."N1"="TAB9"."N1")
11 - access("TAB7"."N12"="TAB8"."N13")
12 - access("TAB8"."N3"="TAB1"."N3")
13 - filter(NVL("TAB1"."N2",0)=0)
14 - access("TAB8"."N7"="SYS_ALIAS_1"."N7")
17 - filter("TAB7"."N11"=3)
18 - filter("TAB6"."N10" IS NULL)
