Home » RDBMS Server » Performance Tuning » Advice on SQL tuning (Oracle 10g)
| Advice on SQL tuning [message #362705] |
Wed, 03 December 2008 16:16  |
svguerin3
Messages: 44
Registered: November 2005
Location: TX
|
Member |
|
|
Figured I'd throw this out there to see if anyone has any suggestions off the top of their heads. We recently partitioned 2 tables based on the "RUN_ID" column (both tables below have this column, of course). The query below runs much faster, but I think it can improve further if we "tailor" it to the new partitioning. I've played around with the query a bit, and the one below is the lowest cost I can achieve in my limited knowledge. Below is the query and JPG explain-plan from SQLNav attached. Thanks in advance for any advice, and let me know if more info is needed!
SELECT * FROM
( SELECT t1.ORD_ID,
t1.PART_ID,
t1.CLIENT_ID_N,
t1.PLAN_N,
t2.CHAN_TYPE_C,
t2.SEQ_N,
t2.CHAN_CNTNT_KEY_TYPE_C,
t2.CNTNT_KEY_VAL1_C,
t2.CNTNT_KEY_VAL2_C,
t2.EMAIL_ADDR_X,
t2.LINE_1_AD_X,
t2.CITY_NM,
t2.PREF_SEL_I,
t2.IS_DFLT_PREF_I,
t2.CTGY_C,
t2.RSVP_C,
count(*) over (partition by t1.ORD_ID)
FROM T_FBSOS_OFFR_STG t1, T_FBSCO_CHAN_ORD t2
WHERE t1.RUN_ID = 23373
AND t2.RUN_ID = 23373
AND t1.ORD_ID = t2.ORD_ID
AND t1.RUN_ID = t2.RUN_ID
AND t1.CPGN_ST_C = 'UNDEL')
WHERE SEQ_N = 1
|
|
|
|
|
|
| Re: Advice on SQL tuning [message #362854 is a reply to message #362720] |
Thu, 04 December 2008 05:37  |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
The NESTED LOOPS join is a bit strange. I suspect T_FBSCO_CHAN_ORD does not have an index on (RUN_ID, ORD_ID). Create one and gather statistics with DBMS_STATS.GATHER_TABLE_STATS().
Ross Leishman
|
|
|
|
| Re: Advice on SQL tuning [message #362940 is a reply to message #362854] |
Thu, 04 December 2008 11:23 |
svguerin3
Messages: 44
Registered: November 2005
Location: TX
|
Member |
|
|
Thanks, Ross, for the heads-up on that! You are correct, the only indexes on T_FBSCO_CHAN_ORD is (ORD_ID, SEQ_N, RUN_ID), which is the PK. And a FK of ORD_ID, which connects to our other table, T_FBSOS_OFFR_STG.
It pretty much takes an act of Congress to get an index created at this place, but I will start the work on that to see if the results change. Thanks again for the input, and let me know if anything else jumps out at you as odd.
|
|
|
|
| Re: Advice on SQL tuning [message #363107 is a reply to message #362940] |
Fri, 05 December 2008 02:20 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
If you have an index on ORD_ID then Oracle is not using it because it thinks it is not helpful. If it is a LOCAL index, then that is equivalent to having a composite index on (RUN_ID, ORD_ID), so a new index won't help.
If you are retrieving more than 10% of the rows in that partition of each table, then you would be better off without indexes. Try:
SELECT * FROM
( SELECT /*+ORDERED USE_HASH(t1,t2)*/ t1.ORD_ID,
t1.PART_ID,
t1.CLIENT_ID_N,
t1.PLAN_N,
t2.CHAN_TYPE_C,
t2.SEQ_N,
t2.CHAN_CNTNT_KEY_TYPE_C,
t2.CNTNT_KEY_VAL1_C,
t2.CNTNT_KEY_VAL2_C,
t2.EMAIL_ADDR_X,
t2.LINE_1_AD_X,
t2.CITY_NM,
t2.PREF_SEL_I,
t2.IS_DFLT_PREF_I,
t2.CTGY_C,
t2.RSVP_C,
count(*) over (partition by t1.ORD_ID)
FROM T_FBSOS_OFFR_STG t1, T_FBSCO_CHAN_ORD t2
WHERE t1.RUN_ID = 23373
AND t2.RUN_ID = 23373
AND t1.ORD_ID = t2.ORD_ID
AND t1.RUN_ID = t2.RUN_ID
AND t1.CPGN_ST_C = 'UNDEL')
WHERE SEQ_N = 1
If this is much faster then Oracle should have worked it out for itself. Have you gethered statistics with DBMS_STATS.GATHER_TABLE_STATS()?
Ross Leishman
|
|
|
|
| Re: Advice on SQL tuning [message #363272 is a reply to message #363107] |
Fri, 05 December 2008 11:45 |
svguerin3
Messages: 44
Registered: November 2005
Location: TX
|
Member |
|
|
Great point about the LOCAL index on ORD_ID. I believe you are correct in that an index on (RUN_ID,ORD_ID) won't help b/c of this.
I believe for this particular query, we are retrieving more than 10% of the rows in a given partition. There will be many times in which this isn't the case, but I think it's more important to tackle the "large" batches, which are the ones that will cover more than 10% of the partition. This is where our performance issue lies. And yep, I've gathered stats on both tables.
Interestingly enough, when I added the hint, the cost doubled in the explain plan, but in testing, it seems to run twice as fast! Thoughts on that? I've attached the new explain plan with the added hint.
Thanks again for the help with this! Query performance is looking a lot better so far! It runs in about 15 secs average now, as opposed to 30-40 before. This is definitely acceptable, but I'm going to keep at it to see if I can get it running even faster.
|
|
|
|
| Re: Advice on SQL tuning [message #363332 is a reply to message #363272] |
Fri, 05 December 2008 19:12 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
It's hardly surprising that the cost has gone up. If the cost was lower then Oracle would have chosen that plan.
Oracle doesn't always get it right.
It's still performing an index range scan. It's worth trying a full table scan on that table
SELECT * FROM
( SELECT /*+ORDERED USE_HASH(t1,t2) FULL(t1) FULL(t2)*/ t1.ORD_ID,
t1.PART_ID,
t1.CLIENT_ID_N,
t1.PLAN_N,
t2.CHAN_TYPE_C,
t2.SEQ_N,
t2.CHAN_CNTNT_KEY_TYPE_C,
t2.CNTNT_KEY_VAL1_C,
t2.CNTNT_KEY_VAL2_C,
t2.EMAIL_ADDR_X,
t2.LINE_1_AD_X,
t2.CITY_NM,
t2.PREF_SEL_I,
t2.IS_DFLT_PREF_I,
t2.CTGY_C,
t2.RSVP_C,
count(*) over (partition by t1.ORD_ID)
FROM T_FBSOS_OFFR_STG t1, T_FBSCO_CHAN_ORD t2
WHERE t1.RUN_ID = 23373
AND t2.RUN_ID = 23373
AND t1.ORD_ID = t2.ORD_ID
AND t1.RUN_ID = t2.RUN_ID
AND t1.CPGN_ST_C = 'UNDEL')
WHERE SEQ_N = 1
Ross Leishman
|
|
|
|
| Re: Advice on SQL tuning [message #364478 is a reply to message #363332] |
Mon, 08 December 2008 15:55 |
svguerin3
Messages: 44
Registered: November 2005
Location: TX
|
Member |
|
|
Ah that definitely makes sense as to the cost being higher but it being faster, since Oracle would have chosen that path if the cost was lower.
Interesting update, I tried adding the hint to include a FULL table scan on both tables, and it definitely ran a bit slower. No biggie there, but then I tried just adding in one FULL table scan for t1, and then one for t2, and both of those queries ran about the same amount of time! Not much difference there - and that run-time was slightly slower than no FULL scans at all.
So in essence, adding a FULL table scan for both tables makes it run slower, and it runs about the same time for both FULL scans on t1 and t2 (individually). All in all, it runs fastest without any FULL table scans, but not by much. I'm curious as to what I should conclude from this. I'm guessing I should leave out the FULL table scans and assume this might be the quickest it's going to run? I still have the USE_HASH in there, since that definitely improved the query run-time.
Thanks again for all your help with this!!
|
|
|
|
| Re: Advice on SQL tuning [message #364512 is a reply to message #364478] |
Mon, 08 December 2008 23:32 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
FULL scans suit scans of large proportions of tables, index scans suit scans of small proportions.
What you can conclude is that you are scanning a volume of data that is pretty darned close to the break-even point.
Decide whether it more likely to increase (in proportion, not volume) or decrease and select an appropriate plan accordingly.
Ross Leishman
|
|
|
|
Goto Forum:
Current Time: Tue Nov 26 08:05:34 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
