Apr 19: Topsy Turvy III - The Good Plan
I've been working at home recently on three different areas
- Trying to get a demo of DUDE working with the able assistance of Kurt. I'll blog about DUDE later.
- Looking at some Parallel Execution trace files with Jeff Moss to help decipher what's going on. There'll be more on that later, too.
- Tons of (not always productive) analysis on the subquery performance problem at work the other week.
It's been a busy time and the latter has taken quite a while despite Jonathan Lewis' valuable help, both by email and by re-reading sections of Cost Based Oracle. In this blog I want to focus on a few apparent anomalies in the 'good' plan that resulted from using the no_unnest hint in the subquery, so here is the query, followed by the plan.
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 /*+ no_unnest */ int_value
FROM v_extn_system_parameters
WHERE process_key = 'CAFTE003'
AND parameter_name LIKE 'ACCRUED%')
----------------------------------------------------------------------------------------
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)
Remember that this plan worked well. However, Jonathan pointed out several interesting aspects of it which are covered in Chapter 9 of his book on 'Query Transformation'. I grasped the basic idea of what he was describing but found some of the details counter-intuitive. That's why I decided to describe my understanding and others can chip in with their comments.
Cardinality
In step 15 of the plan, the number of rows expected to be retrieved from TAB4 is 56. In fact this should be 1124, as shown in the original 'bad' plan.
15 TABLE ACCESS FULL TAB4 1124 15736 7 (15)
The 56 is 5% of 1124, which is the CBO's fixed estimate of how many rows will be returned by a filter on an unknown value. You can see the filter against TAB4 in step 1 (the :B1 is the unknown value). i.e. The CBO doesn't know how many rows will be filtered out so it assumes that the filter will only match 5% of the rows. In fact, nearly all of the rows in TAB4 match the filter so the assumption is wrong in this case. Bear in mind that this is the cardinality that the CBO is using to decide the join order, so I suppose this could lead to some very poor plans if this table had been bigger than in this example.
Updated - I just noticed the following thread at asktom which also discusses the 5% factor. Here's another one.
System-generated Alias
The Access Predicate for step 14 refers to "SYS_ALIAS_1"."N7" instead of "TAB4"."N7". The original un-obfuscated code does contain an alias for this table but Oracle has decided to override it with SYS_ALIAS_1 instead.
14 - access("TAB8"."N7"="SYS_ALIAS_1"."N7")You can also see this SYS_ALIAS_1 being used in the Access Predicates for Step 3 - where TAB5.N8 should equal TAB4.N8
3 - access("TAB3"."N6"="TAB5"."N6" AND "TAB5"."N8"="SYS_ALIAS_1"."N8")
So SYS_ALIAS_1 is obviously something a little different to TAB4 and when I generated a 10053 trace file, it appeared at the first mention of TAB4. So even before the filter is applied, the CBO is aware that TAB4 will be subject to a filter as the result of the transformation and has a hidden inline view that needs an alias. What I found counter-intuitive is that, despite the fact that the filter has not been applied at steps 14/15/16, the CBO treats the cardinality as though it is being applied (the 5% estimate) and also generates this fake alias before it looks like it's needed.
A few closing thoughts (not technical)
The CBO is a constantly changing landscape. If there's a better example of the "Things you think you know" (Source - various), I can't think of it.
Execution plans are a representation of what the server is doing. They might be very close representations most of the time, but there are limitations and quirks.
Jonathan's book is excellent. I don't mind being repetitive because every time I read bits of it, my opinion of it improves. It can be a little hard to read, but only because there's so much information that you need to take it carefully and pay attention. One minute you're sailing along quite happily; you stop paying attention and 'Bang!', you have to retrace a couple of pages. Don't even think about trying it when you're full of flu 
When I thought back to *why* I was so keen to use the /*+ no_unnest hint */ it was because I'd remembered from some of Jonathan's presentations that you could run into these types of problems when moving from 8i to 9i. Working at a site where we've had a large number of 9i upgrades recently has taught me that there's no way you should be planning large scale 9i upgrades without having good 8i environment availability during the process. It *is* possible to tune SQL statements that start to play up after a 9i upgrade, but *far* easier to be able to compare the code in 8i and 9i test environments to see what's changed. Be prepared for things to change and be prepared for Oracle Support to say 'How did the execution plan look before the upgrade?'. That can be quite a time-consuming question to answer if you don't have a matching 8i environment kicking around!
