I’ve been working at home recently on three different areas <\/p>\n
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<\/a>. 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.<\/p>\n 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.<\/p>\n Cardinality<\/u><\/strong> 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.<\/p>\n Updated – I just noticed the following thread<\/a> at asktom which also discusses the 5% factor. Here’s another one<\/a>.<\/p>\n <\/span>In Step 19 of the plan, the CBO is predicting it will retrieve 1 row from the remote table, when it should be 4 rows, as stated in the business requirement. This difference between the expected and real cardinality was also present in the ‘bad’ plan.<\/p>\n System-generated Alias<\/u><\/strong> You can also see this SYS_ALIAS_1 being used in the Access Predicates for Step 3 – where TAB5.N8 should equal TAB4.N8<\/p>\n 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<\/em> 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.<\/p>\n A few closing thoughts (not technical)<\/u><\/strong> 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.<\/p>\n 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 😉<\/p>\n 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!<\/p><\/p>\n","protected":false},"excerpt":{"rendered":"SELECT (a very long ...) column_list
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. <\/p>\n<\/p>\n15 TABLE ACCESS FULL TAB4 1124<\/strong> 15736 7 (15)<\/pre>\n
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.<\/p>\n14 - access(\"TAB8\".\"N7\"=\"SYS_ALIAS_1\".\"N7\")<\/pre>\n
3 - access(\"TAB3\".\"N6\"=\"TAB5\".\"N6\" AND \"TAB5\".\"N8\"=\"SYS_ALIAS_1\".\"N8\")<\/pre>\n
The CBO is a constantly changing landscape. If there’s a better example of the “Things you think<\/em> you know” (Source – various), I can’t think of it.<\/p>\n