Sql tuning request
January 11, 2012 4 Comments
Without knowing anything about the problem in advance, I thought it would be good to do a walkthrough post of a sql tuning request.
But now that I’m done I’m unconvinced as it’s probably too long, the query too meaningless and the real time sql monitoring text output too unreadable in a blog post.
I’m always reluctant to post real, specific application issues because I’m never sure how well they translate and illustrate the desired points unless you convert them to a standalone test case.
Let’s see how it goes.
- The idea is to touch on the broad strategies that I’m going through.
- The scope of the solution should match the scope of the problem – so for a single problem query, table design and current indexes, stats and histograms should be considered as set in stone.
- Ideally we want to avoid hinting as much as possible or at least stick to acceptable hints.
- And if we’re going to manually intervene, we’re not particularly interested in what the current production plan is nor the usage of any plan stability features to preserve it (unless it’s better than we can do ourselves).
This is from an 11gR2 testing environment, 11.2.0.3 to be specific.
Having done the latest merge of a production code release (9.2.0.8) into our 11gR2 environment (upgrade ETA March), a report has been reported as slow, taking about 1.5 minutes in production and some 20 minutes in the 11gR2 environment.
So, having traced the report and found that the driving query is the prime suspect, let’s get some feedback on the performance of the SQL statement.
Here’s the original sql statement to give some context – I’ve commented out most of the columns because they don’t add much other than just length to the post.
SELECT ... some columns ...,
... a function call ...,
... some more columns ...
FROM isbk,
inst,
bsta,
isco,
sdol,
borg,
book,
isdm,
rule
WHERE book.book_num = rule.book_num
AND rule.rule_type_code = 'IBK'
AND isbk.inst_num = inst.inst_num
AND inst.inst_num = bsta.inst_num
AND inst.inst_num = isco.inst_num
AND isco.inst_num = isdm.inst_num
AND isco.xcod_code = 'SEDL'
AND isco.inst_num = sdol.inst_num
AND sdol.xcod_code = 'ISIN'
AND borg.borg_num = inst.issuer_num
AND book.book_num = isbk.book_num
AND bsta.current_mat_date >= TO_DATE(v_bus_date, 'yyyymmdd')
UNION ALL
SELECT ... some columns ...
FROM inst,
bsta,
isco,
sdol,
borg,
isdm,
inix
WHERE inst.inst_num = bsta.inst_num
AND inst.inst_num = isco.inst_num
AND isco.inst_num = isdm.inst_num
AND isco.xcod_code = 'SEDL'
AND isco.inst_num = sdol.inst_num
AND sdol.xcod_code = 'ISIN'
AND borg.borg_num = inst.issuer_num
AND bsta.current_mat_date >= TO_DATE(v_bus_date, 'yyyymmdd')
AND isco.inst_num = inix.inst_num
AND inix.xcod_code = 'NIX'
AND NOT EXISTS (SELECT 1
FROM isbk,
rule,
book,
bsta
WHERE rule.rule_type_code = 'IBK'
AND book.book_num = rule.book_num
AND book.book_num = isbk.book_num
AND inst.inst_num = isbk.inst_num
AND inst.inst_num = bsta.inst_num
AND bsta.current_mat_date >= TO_DATE(v_bus_date, 'yyyymmdd'));
Looking at the query and the repetition of tables and joins between the two UNION ALL parts, it looks like a classic case of two sets of disparate driving data that then need to be joined to the same additional tables.
Ironically, expanding ORs out into UNIONs is a common initial tuning step for performance problems with OR predicates.
Here’s what little extra schema knowledge might be useful:
- INST has a pk of INST_NUM.
- ISCO has an n:1 relationship with INST.
- ISDM has an n:1 relationship with INST.
- BORG has a 1:1 relationship with INST.ISSUER_NUM.
- BSTA has a 1:1 relationship with INST.
- ISBK has a n:1 relationship with INST and a n:1 relationship with BOOK.
- BOOK has a pk of BOOK_NUM.
- RULE is a table used for generic filtering rules, in this case related to BOOK.BOOK_NUM.
Let’s use Real-Time SQL Monitoring (usual license caveats apply) to see what the current performance story is.
SELECT dbms_sqltune.report_sql_monitor(<sql_id>) FROM DUAL;
If there’s one downside to RTSM, it’s a bit too wide for these blog posts 😦
Maybe the RTSM pictures would have been better?
Anyway, I’ve sacrificed some of the columns in the report and tried to shrink the font (there’s a scrollbar at the bottom of the plan window).
Global Stats =========================================================== | Elapsed | Cpu | PL/SQL | Other | Fetch | Buffer | | Time(s) | Time(s) | Time(s) | Waits(s) | Calls | Gets | =========================================================== | 1094 | 1094 | 0.29 | 0.20 | 60 | 257K | =========================================================== SQL Plan Monitoring Details (Plan Hash Value=1036829859) =============================================================================================================================== | Id | Operation | Name | Rows | Execs | Rows | Activity | Activity Detail | | | | | (Estim) | | (Actual) | (%) | (# samples) | =============================================================================================================================== | 0 | SELECT STATEMENT | | | 1 | 29225 | | | | 1 | NESTED LOOPS | | 1 | 1 | 29225 | | | | 2 | NESTED LOOPS | | 1 | 1 | 29205 | | | | 3 | NESTED LOOPS | | 1 | 1 | 29205 | | | | 4 | VIEW | VW_JF_SET$AD8EBC08 | 6147 | 1 | 29205 | | | | 5 | UNION-ALL | | | 1 | 29205 | | | | 6 | NESTED LOOPS | | 9 | 1 | 28719 | | | | 7 | NESTED LOOPS | | 53 | 1 | 28722 | | | | 8 | NESTED LOOPS | | 370 | 1 | 31674 | | | | 9 | NESTED LOOPS | | 370 | 1 | 31674 | | | | 10 | NESTED LOOPS | | 3 | 1 | 1 | | | | 11 | INDEX RANGE SCAN | RULE_IDX_2 | 3 | 1 | 1 | | | | 12 | TABLE ACCESS BY INDEX ROWID | BOOK | 1 | 1 | 1 | | | | 13 | INDEX UNIQUE SCAN | BOOK_IDX | 1 | 1 | 1 | | | | 14 | INDEX RANGE SCAN | ISBK_IDX | 116 | 1 | 31674 | | | | 15 | TABLE ACCESS BY INDEX ROWID | INST | 1 | 31674 | 31674 | | | | 16 | INDEX UNIQUE SCAN | ISTR_PK | 1 | 31674 | 31674 | | | | 17 | TABLE ACCESS BY INDEX ROWID | BSTA | 1 | 31674 | 28722 | | | | 18 | INDEX UNIQUE SCAN | BSTAC_IDX | 1 | 31674 | 31654 | | | | 19 | INDEX RANGE SCAN | ISCO_4_IDX | 1 | 28722 | 28719 | | | | 20 | HASH JOIN | | 6138 | 1 | 486 | | | | 21 | HASH JOIN ANTI | | 5716 | 1 | 54961 | 67.16 | Cpu (732) | | 22 | HASH JOIN | | 24385 | 1 | 83680 | | | | 23 | INDEX RANGE SCAN | ISCO_4_IDX | 170K | 1 | 377K | | | | 24 | HASH JOIN | | 144K | 1 | 266K | | | | 25 | TABLE ACCESS FULL | BSTA | 144K | 1 | 266K | 0.09 | Cpu (1) | | 26 | TABLE ACCESS FULL | INST | 1M | 1 | 1M | | | | 27 | VIEW | VW_SQ_1 | 53M | 1 | 4G | 5.60 | Cpu (61) | | 28 | HASH JOIN | | 53M | 1 | 4G | 27.06 | Cpu (295) | | 29 | MERGE JOIN CARTESIAN | | 458K | 1 | 266K | | | | 30 | NESTED LOOPS | | 3 | 1 | 1 | | | | 31 | INDEX RANGE SCAN | RULE_IDX_2 | 3 | 1 | 1 | | | | 32 | INDEX UNIQUE SCAN | BOOK_IDX | 1 | 1 | 1 | | | | 33 | BUFFER SORT | | 144K | 1 | 266K | | | | 34 | INDEX FAST FULL SCAN | BSTA_IDX_2 | 144K | 1 | 266K | | | | 35 | TABLE ACCESS FULL | ISBK | 2M | 1 | 2M | | | | 36 | INDEX RANGE SCAN | ISCO_4_IDX | 170K | 1 | 32983 | | | | 37 | TABLE ACCESS BY INDEX ROWID | BORG | 1 | 29205 | 29205 | | | | 38 | INDEX UNIQUE SCAN | BORG_PK | 1 | 29205 | 29205 | | | | 39 | INDEX RANGE SCAN | ISCO_4_IDX | 1 | 29205 | 29205 | | | | 40 | INDEX RANGE SCAN | ISDM_IDX | 1 | 29205 | 29225 | | | ===============================================================================================================================
There’s a lot in this report.
First thing you might have spotted is the Join Factorisation going on @ step 4 as indicated by the name VW_JF*.
Maybe you’re always wary of the MERGE JOIN CARTESIAN … BUFFER SORT? Not always a problem of course but where there’s trouble you’ll often find her (in real production scenarios, I find that this mechanism is a problem not so much when there are missing join conditions – because these are rarely found in production code – but rather as a valid join mechanism but where the rowsource estimates are significantly inaccurate).
If you look at the SQL, you’ll see a function call as well.
There are also a whole bunch of estimates that are significantly off.
Where to start?
There are four main areas of questioning:
- What’s taking all the time? Are there some particular steps in the plan which are more problematic than others?
- If estimates are inaccurate, where do they go most wrong or go wrong first?
- Which predicates eliminate the most data? i.e. it’s rarely a good thing to join thousands upon thousands of rows only to do a late filter in the plan to reduce it down to a few handfuls. Aka eliminate early.
- What is the simplest / quickest / least invasive change that can be made to significantly improve performance? And will it actually be sufficient?
From “Activity %”, I hope it’s clear from the report that all the time is taken up by the bottom half of the UNION ALL.
- The rowsource cardinality estimates are not that accurate.
- Plus we find in that second half our old friend the MJC+BS.
- And we’re just burning CPU down there.
So, I’d like to isolate that bottom half of the UNION ALL and run it standalone.
However, in a clear indication of the issues with it, it won’t run standalone – It’s just blown 30+ gig of temp space.
But it returns only 486 rows so let’s try to get a runnable standalone version.
Before we start looking at possible solutions, let’s start to ask questions about the query logic itself, keeping an eye out for redundant tables and joins and asking ourselves whether this is the best way to word the question we think is being asked.
So, we’ve got a few “filters” predicates, a couple of “join predicates” and a NOT EXISTS correlated subquery.
The logic of the subquery is the first thing that jumps out at me.
1. BOOK seems redundant
We join RULE to BOOK and BOOK to ISBK all by BOOK_NUM and ISBK is correlated to the outer INST by INST_NUM.
We do no filtering by any BOOK attribute so it serves no purpose so, let’s remove BOOK, join RULE straight to ISBK.
2. The subquery filtering by BSTA.CURRENT_MAT_DATE is irrelevant.
This is the same filter as in the outer select.
It doesn’t make sense.
Ignoring the actual evaluation order of all these predicates, we should effectively only be checking the NOT EXISTS against INST_NUMS that have passed the outer BSTA filter. So, by definition, this particular predicate in the subquery will always be true. Why repeat it? This is a mistake.
The only thing this subquery should be doing is checking they’re not in the RULE/ISBK combo. So, let’s remove that.
If we run just comment out the tables and joins as per suggestion above, then that bottom query runs in a couple of seconds:
Global Stats ================================================= | Elapsed | Cpu | Other | Fetch | Buffer | | Time(s) | Time(s) | Waits(s) | Calls | Gets | ================================================= | 1.12 | 1.12 | 0.00 | 2 | 73879 | ================================================= SQL Plan Monitoring Details (Plan Hash Value=2734204492) ========================================================================================================== | Id | Operation | Name | Rows | Execs | Rows | Activity | Activity Detail | | | | | (Estim) | | (Actual) | (%) | (# samples) | ========================================================================================================== | 0 | SELECT STATEMENT | | | 1 | 489 | | | | 1 | HASH JOIN RIGHT ANTI | | 38162 | 1 | 489 | | | | 2 | VIEW | VW_SQ_1 | 555 | 1 | 31674 | | | | 3 | NESTED LOOPS | | 555 | 1 | 31674 | | | | 4 | INDEX RANGE SCAN | RULE_IDX_2 | 3 | 1 | 1 | | | | 5 | INDEX RANGE SCAN | ISBK_IDX | 176 | 1 | 31674 | | | | 6 | HASH JOIN | | 38189 | 1 | 28700 | | | | 7 | HASH JOIN | | 37626 | 1 | 28700 | | | | 8 | HASH JOIN | | 35036 | 1 | 86956 | 100.00 | Cpu (1) | | 9 | HASH JOIN | | 32625 | 1 | 87212 | | | | 10 | HASH JOIN | | 31565 | 1 | 83593 | | | | 11 | INDEX RANGE SCAN | ISCO_4_IDX | 170K | 1 | 377K | | | | 12 | HASH JOIN | | 144K | 1 | 266K | | | | 13 | TABLE ACCESS FULL | BSTA | 144K | 1 | 266K | | | | 14 | TABLE ACCESS FULL | INST | 796K | 1 | 796K | | | | 15 | TABLE ACCESS FULL | ISDM | 1M | 1 | 1M | | | | 16 | INDEX RANGE SCAN | ISCO_4_IDX | 170K | 1 | 861K | | | | 17 | INDEX RANGE SCAN | ISCO_4_IDX | 170K | 1 | 32983 | | | | 18 | TABLE ACCESS FULL | BORG | 328K | 1 | 328K | | | ==========================================================================================================
Note that I’ve had to hint this with the /*+ monitor */ hint because by default this now executes beneath the default threshold for monitoring.
It looks like we’ve arrived at our quickest/simplest change just by going through the query quickly trying to understand the question it’s asking. No hints required.
So this is would be a good place to stop, thoroughly test the change, validate the original and the changed results and move on to something else.
We should drop it back into the UNION ALL and see what we get:
Global Stats =========================================================== | Elapsed | Cpu | PL/SQL | Other | Fetch | Buffer | | Time(s) | Time(s) | Time(s) | Waits(s) | Calls | Gets | =========================================================== | 4.68 | 4.68 | 0.38 | 0.00 | 60 | 230K | =========================================================== SQL Plan Monitoring Details (Plan Hash Value=4171599168) =============================================================================================================================== | Id | Operation | Name | Rows | Execs | Rows | Activity | Activity Detail | | | | | (Estim) | | (Actual) | (%) | (# samples) | =============================================================================================================================== | 0 | SELECT STATEMENT | | | 1 | 29225 | | | | 1 | NESTED LOOPS | | 1 | 1 | 29225 | | | | 2 | NESTED LOOPS | | 1 | 1 | 29205 | | | | 3 | HASH JOIN | | 1 | 1 | 29205 | | | | 4 | TABLE ACCESS FULL | BORG | 328K | 1 | 328K | | | | 5 | VIEW | VW_JF_SET$AD8EBC08 | 26182 | 1 | 29205 | | | | 6 | UNION-ALL | | | 1 | 29205 | | | | 7 | NESTED LOOPS | | 9 | 1 | 28719 | | | | 8 | NESTED LOOPS | | 53 | 1 | 28722 | | | | 9 | NESTED LOOPS | | 370 | 1 | 31674 | | | | 10 | NESTED LOOPS | | 370 | 1 | 31674 | | | | 11 | NESTED LOOPS | | 3 | 1 | 1 | | | | 12 | INDEX RANGE SCAN | RULE_IDX_2 | 3 | 1 | 1 | | | | 13 | TABLE ACCESS BY INDEX ROWID | BOOK | 1 | 1 | 1 | | | | 14 | INDEX UNIQUE SCAN | BOOK_IDX | 1 | 1 | 1 | | | | 15 | INDEX RANGE SCAN | ISBK_IDX | 116 | 1 | 31674 | | | | 16 | TABLE ACCESS BY INDEX ROWID | INST | 1 | 31674 | 31674 | | | | 17 | INDEX UNIQUE SCAN | ISTR_PK | 1 | 31674 | 31674 | | | | 18 | TABLE ACCESS BY INDEX ROWID | BSTA | 1 | 31674 | 28722 | | | | 19 | INDEX UNIQUE SCAN | BSTA_IDX | 1 | 31674 | 31654 | | | | 20 | INDEX RANGE SCAN | ISCO_4_IDX | 1 | 28722 | 28719 | | | | 21 | HASH JOIN RIGHT ANTI | | 26173 | 1 | 486 | | | | 22 | VIEW | VW_SQ_1 | 555 | 1 | 31674 | | | | 23 | NESTED LOOPS | | 555 | 1 | 31674 | | | | 24 | INDEX RANGE SCAN | RULE_IDX_2 | 3 | 1 | 1 | | | | 25 | INDEX RANGE SCAN | ISBK_IDX | 176 | 1 | 31674 | | | | 26 | HASH JOIN | | 26187 | 1 | 28693 | | | | 27 | HASH JOIN | | 24385 | 1 | 83680 | 33.33 | Cpu (1) | | 28 | INDEX RANGE SCAN | ISCO_4_IDX | 170K | 1 | 377K | | | | 29 | HASH JOIN | | 144K | 1 | 266K | | | | 30 | TABLE ACCESS FULL | BSTA | 144K | 1 | 266K | | | | 31 | TABLE ACCESS FULL | INST | 1M | 1 | 1M | | | | 32 | INDEX RANGE SCAN | ISCO_4_IDX | 170K | 1 | 32983 | | | | 33 | INDEX RANGE SCAN | ISCO_4_IDX | 1 | 29205 | 29205 | | | | 34 | INDEX RANGE SCAN | ISDM_IDX | 1 | 29205 | 29225 | | | ===============================================================================================================================
So we could leave it there.
However, I wouldn’t blame you if you wanted to go further, even if we’re bordering on Compulsive Tuning Disorder:
- I don’t really like leaving behind a plan that’s got significantly inaccurate estimates – it leaves behind too much of a future threat.
- And maybe we can also look at the original query and the direction the Join Factorisation was indicating and take it further? How about doing the UNION ALL a bit earlier and then do one lot of joining to the shared tables from the original SQL?
- Let’s use some dynamic sampling to improve some single table cardinality estimates but then perhaps let’s go too far and add some join estimate adjustments (I say too far because I’m going to use opt_estimate but it’s undocumented and I’m not recommending it but if you do use it, then use it in conjunction with qb_name).
- And let’s get rid of the function call which, take it from me, in this case is effectively a single table outer join lookup anyway.
SELECT /*+
find_me
monitor
qb_name(main)
dynamic_sampling(sdol@main 4)
dynamic_sampling(isin@main 4)
*/
... some columns ...
FROM (SELECT /*+
qb_name(union1)
opt_estimate(join(isbk@union1 rule@union1) scale_rows=100)
*/
isbk.inst_num inst_num
, inix.inst_code nix_code
, (SELECT book.name
FROM book
WHERE book.book_num = rule.book_num) book_name
FROM isbk
, rule
, inix
WHERE rule.rule_type_code = 'IBK'
AND rule.book_num = isbk.book_num
AND inix.inst_num (+) = isbk.inst_num
AND inix.xcod_code (+) = 'NIX'
UNION ALL
SELECT /*+
qb_name(union2)
dynamic_sampling(inix@union2 4)
*/
inix.inst_num inst_num
, inix.inst_code nix_code
, NULL book_name
FROM inix
WHERE inix.xcod_code = 'NIX'
AND NOT EXISTS (SELECT /*+
qb_name(sub1)
opt_estimate(join(isbk@sub1 rule@sub1) scale_rows=100)
*/
1
FROM isbk
, rule
WHERE rule.rule_type_code = 'IBK'
AND rule.book_num = isbk.book_num
AND isbk.inst_num = inix.inst_num))
xxxx
, bsta
, isin
, sdol
, inst
, borg
, isdm
WHERE bsta.inst_num = xxxx.inst_num
AND bsta.current_mat_date >= TO_DATE(:v_bus_date, 'yyyymmdd')
AND isin.inst_num = xxxx.inst_num
AND isin.xcod_code = 'SEDL'
AND sdol.inst_num = xxxx.inst_num
AND sdol.xcod_code = 'ISIN'
AND inst.inst_num = xxxx.inst_num
AND borg.borg_num = inst.issuer_num
AND isdm.inst_num = xxxx.inst_num;
Which gives:
Global Stats ================================================= | Elapsed | Cpu | Other | Fetch | Buffer | | Time(s) | Time(s) | Waits(s) | Calls | Gets | ================================================= | 1.40 | 1.40 | 0.00 | 60 | 79273 | ================================================= SQL Plan Monitoring Details (Plan Hash Value=2392527494) ======================================================================================================================= | Id | Operation | Name | Rows | Execs | Rows | Activity | Activity Detail | | | | | (Estim) | | (Actual) | (%) | (# samples) | ======================================================================================================================= | 0 | SELECT STATEMENT | | | 1 | 29225 | | | | 1 | HASH JOIN | | 94337 | 1 | 29225 | | | | 2 | INDEX RANGE SCAN | ISCO_4_IDX | 373K | 1 | 377K | | | | 3 | HASH JOIN | | 80747 | 1 | 29629 | | | | 4 | HASH JOIN | | 80747 | 1 | 29629 | | | | 5 | HASH JOIN | | 80747 | 1 | 29629 | | | | 6 | HASH JOIN | | 78122 | 1 | 29604 | | | | 7 | MERGE JOIN | | 78003 | 1 | 33947 | | | | 8 | INDEX RANGE SCAN | ISCO_4_IDX | 818K | 1 | 861K | | | | 9 | SORT JOIN | | 55765 | 861K | 33947 | | | | 10 | VIEW | | 55765 | 1 | 33960 | | | | 11 | UNION-ALL | | | 1 | 33960 | | | | 12 | TABLE ACCESS BY INDEX ROWID | BOOK | 1 | 1 | 1 | | | | 13 | INDEX UNIQUE SCAN | BOOK_IDX | 1 | 1 | 1 | | | | 14 | HASH JOIN OUTER | | 55485 | 1 | 31674 | | | | 15 | NESTED LOOPS | | 55485 | 1 | 31674 | | | | 16 | INDEX RANGE SCAN | RULE_IDX_2 | 3 | 1 | 1 | | | | 17 | INDEX RANGE SCAN | ISBK_IDX | 17609 | 1 | 31674 | | | | 18 | INDEX RANGE SCAN | ISCO_4_IDX | 170K | 1 | 32983 | | | | 19 | HASH JOIN ANTI | | 280 | 1 | 2286 | | | | 20 | INDEX RANGE SCAN | ISCO_4_IDX | 28028 | 1 | 32983 | | | | 21 | VIEW | VW_SQ_1 | 55485 | 1 | 31674 | | | | 22 | NESTED LOOPS | | 55485 | 1 | 31674 | | | | 23 | INDEX RANGE SCAN | RULE_IDX_2 | 3 | 1 | 1 | | | | 24 | INDEX RANGE SCAN | ISBK_IDX | 17609 | 1 | 31674 | | | | 25 | TABLE ACCESS FULL | BSTA | 144K | 1 | 266K | | | | 26 | TABLE ACCESS FULL | ISDM | 1M | 1 | 1M | | | | 27 | TABLE ACCESS FULL | INST | 796K | 1 | 796K | 100.00 | Cpu (1) | | 28 | TABLE ACCESS FULL | BORG | 328K | 1 | 328K | | | =======================================================================================================================
It might not be perfect but it’s certainly a lot better.
An acceptable compromise might be to refactor the UNION ALL as per above but omit the undocumented opt_estimate hints.
All we need to do is some more testing to validate the results and to also triple check the performance when the data is not cached and we’re done.
If you made it this far then Wow! I probably wouldn’t have done.
So, to summarise, what have we done?
- Well, we haven’t needed to know much about the original intention of the query nor the schema.
- We’ve improved the accuracies of some of the estimates.
- We’ve found some redundant tables and joins.
- And we’ve reordered the query slightly to better phrase the question that we think was being asked, in the process moving the UNION ALL earlier in the processing so that some of the joining tables only needed to be referenced once (an advantage that might not be preserved if the CBO decides to merge the UNION ALL but that would be the opposite of the recently developed join factorisation mechanism).
And these simple, quick steps effectively reduced the execution time of a query from 20 minutes to a few seconds.
In hindsight, a good example because of the gains realised but a bad example because of the length of query and the associated detail, particularly the redundant tables.
I’m always reluctant to post real, specific application issues because I’m never sure how well they translate and illustrate the desired points unless you convert them to a standalone test case.
The reluctance is good, because your audience skill level is so unpredictable. Overcoming the reluctance is better, because there is value added to demonstrating your thought process away from a canned simple test. All you need to resolve is to say the prerequisite is to already be somewhat familiar with plans and the access methods involved. Of course there will be questions from the under-prepared and perhaps some complaints about no replicable test case, the former you can politely answer and the latter, well, they will have such a complex problem if they don’t already, and maybe some will be tempted to try to put one together.
Test cases are necessary for demonstrating principles and bugs, your post reflects the reality of support. Being explicit about strategies, knowledge and conclusions is extremely helpful. You might consider uploading things that don’t size well in a blog to other places where they do, but keep the cut-down things in the blog so people don’t have to go there.
I, at least, encourage you to make more posts like this.
Thanks for the feedback Joel.
I know I’ve done at least one post previously with real time sql monitoring output in a pop out xls. I’m not sure that worked particularly well.
A wider output would be better but it costs.
Cheers,
Dominic
Dominic,
Nice Point to look at the query logic from redudant tables and joins point of view .
Buffer gets are reduced from 257K to 230K. Can you please what you mean by “We’re doing a lot more buffer gets, but the query is executing a heck of a lot faster.”
Is there is something, that I am missing
Harman,
Nothing you’re missing – my mistake (deliberate of course just to check that you were paying attention 😉 )
You’ve got an unfair advantage when it comes to this query above…
Thanks for commenting. Good to hear from you.