{"id":818,"date":"2006-04-10T12:00:00","date_gmt":"2006-04-10T12:00:00","guid":{"rendered":"http:\/\/orcldoug.com\/blog\/?p=818"},"modified":"2006-04-10T12:00:00","modified_gmt":"2006-04-10T12:00:00","slug":"topsy-turvy-ii-the-plans","status":"publish","type":"post","link":"http:\/\/orcldoug.com\/blog\/2006\/04\/10\/topsy-turvy-ii-the-plans\/","title":{"rendered":"Topsy Turvy II – The plans"},"content":{"rendered":"

This blog arrives four days later than I expected to post; apologies for that.<\/p>\n

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.<\/p>\n

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.<\/p>\n

“which means the remote subquery was probably executed as a late running filter”<\/i><\/p>\n

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?<\/span>” in this review<\/a>.)<\/p>\n

Hopefully this additional information will be enough to decide but I suspect it’ll just throw up more interesting questions 😉<\/p>\n

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.<\/p>\n

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.<\/p>\n

First, here is the original query without the no_unnest hint.<\/p>\n

SELECT (a very long ...) column_list<\/span>FROM tab7, tab2, tab3, tab5, tab8, tab4, tab1, tab6, tab9WHERE tab7.n11 = 3AND tab7.n4 = tab2.n4AND tab2.n5 = tab3.n5AND tab3.n6 = tab5.n6AND tab5.n8 IS NOT NULLAND tab5.n6 = tab6.n6AND tab6.n10 IS NULLAND tab7.n12 = tab8.n13AND tab8.n7 = tab4.n7AND tab8.n7 = tab9.n7AND tab8.n3 = tab1.n3AND tab1.n1 = tab9.n1AND tab1.n1 = tab6.n1AND NVL (tab1.n2, 0) = 0AND tab5.n8 = tab4.n8AND tab4.n9 IN (SELECT int_valueFROM v_extn_system_parametersWHERE process_key = 'CAFTE003'AND parameter_name LIKE 'ACCRUED%')<\/pre>\n
Now on to the execution plans. Here are some high-level details of where the plans came from.<\/p>\n
1) The Oracle server version is 9.2.0.7<\/p>\n
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.<\/p>\n
explain plan for statement<\/span>;
select * from TABLE(dbms_xplan.display);<\/p><\/blockquote>\n
3) The statement was originally an INSERT .. SELECT, but I just tuned the SELECT block.<\/p>\n
4) These plans are from a test environment that should be identical to production.<\/p>\n
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.<\/p>\n
6) The indexes used in the plans are<\/p>\n
\n
TAB1_PK on TAB1(N3)
 TAB3_PK on TAB3(N5)
REMOTE_PK on Remote table<\/p>\n<\/blockquote>\n
---------------------------------------------------------------------------------------------------| 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):---------------------------------------------------<\/p>
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\")<\/p><\/pre>\n
 And here is the plan after using the \/*+ NO_UNNEST *\/ hint in the subquery.<\/p>\n
-------------------------------------------------------------------------------------------------| 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):---------------------------------------------------<\/p>
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<\/span>\".\"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)<\/p><\/pre>\n","protected":false},"excerpt":{"rendered":"
This blog arrives four days later than I expected to post; apologies for that. 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… Continue reading Topsy Turvy II – The plans<\/span><\/a><\/p>\n","protected":false},"author":0,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-818","post","type-post","status-publish","format-standard","hentry","category-uncategorized","entry"],"jetpack_featured_media_url":"","jetpack-related-posts":[{"id":851,"url":"http:\/\/orcldoug.com\/blog\/2006\/02\/28\/my-hotsos-agenda\/","url_meta":{"origin":818,"position":0},"title":"My Hotsos Agenda","date":"February 28, 2006","format":false,"excerpt":"I'm a bit surprised how excited I am about going to Dallas for the Hotsos Symposium next week (have I mentioned it yet?). I've already worked my personal agenda out but it hasn't been easy with two streams of equally high quality presentations. The full schedule is here and here's\u2026","rel":"","context":"With 11 comments","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":929,"url":"http:\/\/orcldoug.com\/blog\/2005\/10\/21\/book-review-oracle-insights-tales-of-the-oak-table\/","url_meta":{"origin":818,"position":1},"title":"Book Review: Oracle Insights, Tales of the Oak Table","date":"October 21, 2005","format":false,"excerpt":"I've been looking for something to read about Oracle recently and couldn't wait until Jonathan Lewis' CBO book arrived. I knew of TOTOT (as Mogens N\ufffdrgaard has referred to it) and planned to buy it at some stage because I've loved past work by some of the authors, but didn't\u2026","rel":"","context":"With 1 comment","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1303,"url":"http:\/\/orcldoug.com\/blog\/2007\/08\/02\/why-you-cant-just-say-stop-feeding-the-troll\/","url_meta":{"origin":818,"position":2},"title":"Why you can’t just say ‘Stop feeding the troll’ …","date":"August 2, 2007","format":false,"excerpt":"... and walk away.In the course of the past year, I've heard more than one person suggest that Jonathan Lewis should stop debunking Don Burleson's arguments. Their opinion is that it makes Jonathan look narrow-minded and spiteful and that he should simply ignore Don, stop giving him extra publicity and\u2026","rel":"","context":"Similar post","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":901,"url":"http:\/\/orcldoug.com\/blog\/2005\/11\/19\/another-interesting-lewis-tip\/","url_meta":{"origin":818,"position":3},"title":"Another Interesting Lewis Tip","date":"November 19, 2005","format":false,"excerpt":"Updated 24th November 2005I wouldn't normally update an existing entry but there are some very interesting and important comments on this entry, particularly from Jonathan Lewis and Howard Rogers, that I'd recommend you take the time to read. Jonathan also mentions it on his website. There's much more in the\u2026","rel":"","context":"With 15 comments","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":847,"url":"http:\/\/orcldoug.com\/blog\/2006\/03\/06\/hotsos-day-0\/","url_meta":{"origin":818,"position":4},"title":"Hotsos – Day 0","date":"March 6, 2006","format":false,"excerpt":"Well, that's me registered. Despite all my best efforts to get some sleep this afternoon and hopefully get myself back in sync, it didn't happen. I went for a liquid lunch with David Aldridge who turns out to be the nice guy and cantankerous old British b*gger I expected and\u2026","rel":"","context":"With 4 comments","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":939,"url":"http:\/\/orcldoug.com\/blog\/2005\/09\/16\/decode-paper\/","url_meta":{"origin":818,"position":5},"title":"DECODE paper","date":"September 16, 2005","format":false,"excerpt":"Over the past few months I've occasionally pondered what to do about the DECODE paper on this site.On the one hand, it was written 6 years ago when DECODE was more prevalent than CASE so it's out of date.On the other, it's popular and is the main reason by far\u2026","rel":"","context":"With 3 comments","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]}],"_links":{"self":[{"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/posts\/818","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"replies":[{"embeddable":true,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/comments?post=818"}],"version-history":[{"count":0,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/posts\/818\/revisions"}],"wp:attachment":[{"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/media?parent=818"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/categories?post=818"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/tags?post=818"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}