Home » RDBMS Server » Performance Tuning » Need to tune query (Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production)
| Need to tune query [message #474696] |
Wed, 08 September 2010 05:35  |
knw15pwr
Messages: 134
Registered: March 2010
|
Senior Member |
|
|
This is the script for an MV which currently takes about 15 to 20 mins. This needs to be tuned to complete within 5 mins.
INSERT /*+ APPEND */ INTO "ABC"."DAILY_MV_1" SELECT /*+ NO_MERGE(i) NO_PUSH_PRED(i) */ i.component_item_id,
i.organization_id,
i.schedule_date,
ecd1.calendar_date,
count(i.seq_num),
decode(ecd1.seq_num, null, 0, sum(i.mps_quantity)/decode(count(i.seq_num), 0, 1, count(i.seq_num))) mps_daily_qty
from calendar_dates ecd1,
(SELECT msd.inventory_item_id,
bom.component_item_id,
msd.organization_id,
msd.schedule_date,
null week_start_date,
ecd.seq_num,
null schedule_designator,
0 mps_quantity
from schedule_dates msd,
calendar_dates ecd,
bom_table bom
where 1=1
and msd.global_name = 'US'
and msd.schedule_level = 2
and bom.ORGANIZATION_ID = msd.organization_id
and bom.inventory_item_id = msd.inventory_item_id
and msd.schedule_designator = (select max(schedule_designator)
from MPS_SUMMARY_TABLE
)
and ecd.calendar_date between msd.schedule_date and msd.schedule_date+6
and ecd.calendar_date > sysdate-8
and ecd.SEQ_NUM is not null
UNION all
(
SELECT msd.inventory_item_id,
bom.component_item_id,
msd.organization_id,
msd.schedule_date,
ecd.week_start_date,
null seq_num,
msd.SCHEDULE_DESIGNATOR,
msd.schedule_quantity
from schedule_dates msd,
calendar_dates ecd,
bom_table bom
where 1=1
and msd.schedule_level = 2
and bom.ORGANIZATION_ID = msd.organization_id
and bom.inventory_item_id = msd.inventory_item_id
and msd.schedule_designator = (select max(schedule_designator)
from MPS_SUMMARY_TABLE
)
and ecd.week_start_date = msd.schedule_date
and ecd.calendar_date > sysdate-8
and ecd.seq_num is not null
group by msd.inventory_item_id,
bom.component_item_id,
msd.organization_id,
msd.schedule_date,
ecd.week_start_date,
msd.SCHEDULE_DESIGNATOR,
msd.schedule_quantity
union all
SELECT msd.inventory_item_id,
bom.component_item_id,
msd.organization_id,
msd.schedule_date,
ecd.week_start_date,
null seq_num,
msd.SCHEDULE_DESIGNATOR,
msd.schedule_quantity
from schedule_dates msd,
calendar_dates ecd,
bom_table bom
where 1=1
and msd.schedule_level = 2
and bom.ORGANIZATION_ID = msd.organization_id
and bom.inventory_item_id = msd.inventory_item_id
and msd.schedule_designator = (select max(schedule_designator)
from MPS_SUMMARY_TABLE
)
and ecd.week_start_date = msd.schedule_date+1
and ecd.calendar_date > sysdate-8
and ecd.seq_num is not null
group by msd.inventory_item_id,
bom.component_item_id,
msd.organization_id,
msd.schedule_date,
ecd.week_start_date,
msd.SCHEDULE_DESIGNATOR,
msd.schedule_quantity
)
) i
where 1=1
and ecd1.calendar_date between sysdate-1 and sysdate+119
and ecd1.week_start_date = i.schedule_date(+)
group by i.component_item_id,
i.organization_id,
i.schedule_date,
ecd1.calendar_date,
ecd1.seq_num
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 1 1465.00 2149.30 235346 216513 131 901921
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1 1465.00 2149.30 235346 216513 131 901921
Misses in library cache during parse: 0
Misses in library cache during execute: 1
Optimizer goal: CHOOSE
Parsing user id: 60 (recursive depth: 1)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
direct path read 853 0.02 1.17
db file sequential read 50446 0.10 188.49
latch free 56 0.29 0.56
db file scattered read 28 0.03 0.27
direct path write 5 0.00 0.00
PX Deq Credit: need buffer 4 0.00 0.00
PX Deq: Execute Reply 15 1.96 5.83
control file sequential read 20 0.00 0.01
rdbms ipc reply 5 0.01 0.03
PX Deq: Signal ACK 3 0.00 0.00
log file sync 1 0.00 0.00
********************************************************************************
Explain Plan for the query
SQL> SELECT * FROM table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost |
----------------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 312 | 24024 | | 13979 |
| 1 | LOAD AS SELECT | | | | | |
| 2 | SORT GROUP BY | | 312 | 24024 | | 13979 |
|* 3 | FILTER | | | | | |
|* 4 | HASH JOIN OUTER | | 1814 | 136K| | 13920 |
| 5 | TABLE ACCESS BY INDEX ROWID | CALENDAR_DATES | 21 | 336 | | 3 |
|* 6 | INDEX RANGE SCAN | CALENDAR_DATES_N1 | 39 | | | 2 |
| 7 | VIEW | | 103K| 6178K| | 13918 |
| 8 | UNION-ALL | | | | | |
| 9 | MERGE JOIN | | 2815 | 156K| | 3709 |
| 10 | SORT JOIN | | 3786 | 177K| | 3660 |
|* 11 | HASH JOIN | | 3786 | 177K| | 3584 |
|* 12 | INDEX FULL SCAN | SCHEDULE_DATES_N8 | 21542 | 736K| | 34558 |
| 13 | SORT AGGREGATE | | 1 | 10 | | |
| 14 | TABLE ACCESS FULL | MPS_SUMMARY_TABLE | 13292 | 129K| | 18 |
| 15 | INDEX FULL SCAN | BOM_TABLE_PK | 220K| 2793K| | 792 |
|* 16 | FILTER | | | | | |
|* 17 | SORT JOIN | | | | | |
|* 18 | INDEX FULL SCAN | CALENDAR_DATES_U1 | 297 | 2673 | | 27 |
| 19 | SORT GROUP BY | | 82494 | 5236K| 12M| 5554 |
|* 20 | HASH JOIN | | 82494 | 5236K| | 4634 |
|* 21 | TABLE ACCESS BY INDEX ROWID | CALENDAR_DATES | 297 | 4752 | | 5 |
|* 22 | INDEX RANGE SCAN | CALENDAR_DATES_N1 | 77 | | | 2 |
|* 23 | HASH JOIN | | 75713 | 3622K| 5376K| 4629 |
| 24 | INDEX FULL SCAN | BOM_TABLE_PK | 220K| 2793K| | 792 |
| 25 | TABLE ACCESS BY INDEX ROWID| SCHEDULE_DATES | 430K| 14M| | 4151 |
|* 26 | INDEX RANGE SCAN | SCHEDULE_DATES_N3 | 2 | | | 5845 |
| 27 | SORT AGGREGATE | | 1 | 10 | | |
| 28 | TABLE ACCESS FULL | MPS_SUMMARY_TABLE | 13292 | 129K| | 18 |
| 29 | SORT GROUP BY | | 18415 | 1168K| 2776K| 4655 |
|* 30 | HASH JOIN | | 18415 | 1168K| 5376K| 4424 |
| 31 | INDEX FULL SCAN | BOM_TABLE_PK | 220K| 2793K| | 792 |
|* 32 | HASH JOIN | | 104K| 5321K| | 4159 |
|* 33 | TABLE ACCESS BY INDEX ROWID| CALENDAR_DATES | 297 | 4752 | | 5 |
|* 34 | INDEX RANGE SCAN | CALENDAR_DATES_N1 | 77 | | | 2 |
| 35 | TABLE ACCESS BY INDEX ROWID| SCHEDULE_DATES | 430K| 14M| | 4151 |
|* 36 | INDEX RANGE SCAN | SCHEDULE_DATES_N3 | 430K| | | 5845 |
| 37 | SORT AGGREGATE | | 1 | 10 | | |
| 38 | TABLE ACCESS FULL | MPS_SUMMARY_TABLE | 13292 | 129K| | 18 |
----------------------------------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter(SYSDATE@!-1<=SYSDATE@!+119)
4 - access("ECD1"."WEEK_START_DATE"="I"."SCHEDULE_DATE"(+))
6 - access("ECD1"."CALENDAR_DATE">=SYSDATE@!-1 AND "ECD1"."CALENDAR_DATE"<=SYSDATE@!+119)
11 - access("BOM"."ORGANIZATION_ID"="MSD"."ORGANIZATION_ID" AND
"BOM"."INVENTORY_ITEM_ID"="MSD"."INVENTORY_ITEM_ID")
12 - access("MSD"."SCHEDULE_DESIGNATOR"= (SELECT /*+ */ MAX("MPS_SUMMARY_TABLE"."SCHEDULE_DESIGNATOR")
FROM "MPS_SUMMARY_TABLE" "MPS_SUMMARY_TABLE") AND "MSD"."SCHEDULE_LEVEL"=2 AND "MSD"."GLOBAL_NAME"='US')
filter("MSD"."GLOBAL_NAME"='US' AND "MSD"."SCHEDULE_LEVEL"=2 AND
"MSD"."SCHEDULE_DATE"+6>SYSDATE@!-8 AND "MSD"."SCHEDULE_DESIGNATOR"= (SELECT /*+ */
MAX("MPS_SUMMARY_TABLE"."SCHEDULE_DESIGNATOR") FROM "MPS_SUMMARY_TABLE" "MPS_SUMMARY_TABLE"))
16 - filter("ECD"."CALENDAR_DATE"<="MSD"."SCHEDULE_DATE"+6)
17 - access("ECD"."CALENDAR_DATE">="MSD"."SCHEDULE_DATE")
filter("ECD"."CALENDAR_DATE">="MSD"."SCHEDULE_DATE")
18 - access("ECD"."CALENDAR_DATE">SYSDATE@!-8)
filter("ECD"."CALENDAR_DATE">SYSDATE@!-8 AND "ECD"."SEQ_NUM" IS NOT NULL)
20 - access("ECD"."WEEK_START_DATE"="MSD"."SCHEDULE_DATE")
21 - filter("ECD"."SEQ_NUM" IS NOT NULL)
22 - access("ECD"."CALENDAR_DATE">SYSDATE@!-8 AND "ECD"."CALENDAR_DATE" IS NOT NULL)
23 - access("BOM"."ORGANIZATION_ID"="MSD"."ORGANIZATION_ID" AND
"BOM"."INVENTORY_ITEM_ID"="MSD"."INVENTORY_ITEM_ID")
26 - access("MSD"."SCHEDULE_DESIGNATOR"= (SELECT /*+ */ MAX("MPS_SUMMARY_TABLE"."SCHEDULE_DESIGNATOR")
FROM "MPS_SUMMARY_TABLE" "MPS_SUMMARY_TABLE") AND "MSD"."SCHEDULE_LEVEL"=2)
filter("MSD"."SCHEDULE_LEVEL"=2)
30 - access("BOM"."ORGANIZATION_ID"="MSD"."ORGANIZATION_ID" AND
"BOM"."INVENTORY_ITEM_ID"="MSD"."INVENTORY_ITEM_ID")
32 - access("ECD"."WEEK_START_DATE"="MSD"."SCHEDULE_DATE"+1)
33 - filter("ECD"."SEQ_NUM" IS NOT NULL)
34 - access("ECD"."CALENDAR_DATE">SYSDATE@!-8 AND "ECD"."CALENDAR_DATE" IS NOT NULL)
36 - access("MSD"."SCHEDULE_DESIGNATOR"= (SELECT /*+ */ MAX("MPS_SUMMARY_TABLE"."SCHEDULE_DESIGNATOR")
FROM "MPS_SUMMARY_TABLE" "MPS_SUMMARY_TABLE") AND "MSD"."SCHEDULE_LEVEL"=2)
filter("MSD"."SCHEDULE_LEVEL"=2)
Note: cpu costing is off
81 rows selected.
|
|
|
|
| Re: Need to tune query [message #474699 is a reply to message #474696] |
Wed, 08 September 2010 06:03  |
cookiemonster
Messages: 13921
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Obvious question is why do the 2nd and 3rd select in the inline view have group by clauses?
I'd move the sub-query on MPS_SUMMARY_TABLE into a with clause and add an index on schedule_designator.
|
|
|
|
| Re: Need to tune query [message #474705 is a reply to message #474699] |
Wed, 08 September 2010 06:17 |
knw15pwr
Messages: 134
Registered: March 2010
|
Senior Member |
|
|
Thanks CM.
I obviously dont have an answer to your first question. But now that you suggest, I will check the code without the group by.
There are not many records in the MPS_SUMMARY_TABLE and the max(schedule_designator) returns a record in almost 625 millisec. Will it still need indexing ?
|
|
|
|
| Re: Need to tune query [message #474711 is a reply to message #474705] |
Wed, 08 September 2010 06:58 |
cookiemonster
Messages: 13921
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
To be honest speeding up the mps_summary_table access is unlikely to make much difference, but it's the only obvious fix I could spot. Try with and without index and see what difference it makes.
About the group bys - they could be doing a distinct, in which case the code should be changed to use distinct to make the purpose clear.
Alternatively, if you're selecting unique keys from all three tables then the group by is doing nothing useful and should be removed.
|
|
|
|
| Re: Need to tune query [message #474720 is a reply to message #474711] |
Wed, 08 September 2010 07:52 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
You can see the big wait event is db file sequential read. That means INDEX UNIQUE/RANGE/FULL scans and TABLE ACCESS BY INDEX ROWID. These types of access are single-block reads from disk - very slow. The biggest contributors seem to be INDEX FULL scans of BOM_TABLE_PK. I'm not sure why it is not choosing a FAST FULL SCAN, which would perform a multi-block read and be much faster. The only advantage of an INDEX FULL scan over a FAST FULL SCAN is that it is pre-sorted, and can be useful for GROUP BY / ORDER BY and the like. You don't appear to be deriving any such benefit because each of these scans is feeding into a HASH join.
Try to encourage a INDEX FAST FULL scan:
SELECT /*+ INDEX_FFS(bom)*/ msd.inventory_item_id,
bom.component_item_id,
msd.organization_id,
msd.schedule_date,
ecd.week_start_date,
null seq_num,
msd.SCHEDULE_DESIGNATOR,
msd.schedule_quantity
from schedule_dates msd,
calendar_dates ecd,
bom_table bom
...
Ross Leishman
[Updated on: Wed, 08 September 2010 07:52] Report message to a moderator |
|
|
|
| Re: Need to tune query [message #474779 is a reply to message #474696] |
Wed, 08 September 2010 16:17 |
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
|
Senior Member |
|
|
You could try peeling the various subqueries apart, running them in isolation to check if any of those are disproportinately slow and tune them individually.
Long shot, relatively speaking, but it wouldnt be the first time I've seen a big query dragged down by an unexpected component.
On a relatively quick read through you seem to have excessive querying of a table, possible a requirement though I cant tell from here.
This part
SELECT msd.inventory_item_id,
bom.component_item_id,
msd.organization_id,
msd.schedule_date,
ecd.week_start_date,
null seq_num,
msd.SCHEDULE_DESIGNATOR,
msd.schedule_quantity
from schedule_dates msd,
calendar_dates ecd,
bom_table bom
where 1=1
and msd.schedule_level = 2
and bom.ORGANIZATION_ID = msd.organization_id
and bom.inventory_item_id = msd.inventory_item_id
and msd.schedule_designator = (select max(schedule_designator)
from MPS_SUMMARY_TABLE
)
* and ecd.week_start_date = msd.schedule_date
and ecd.calendar_date > sysdate-8
and ecd.seq_num is not null
group by msd.inventory_item_id,
bom.component_item_id,
msd.organization_id,
msd.schedule_date,
ecd.week_start_date,
msd.SCHEDULE_DESIGNATOR,
msd.schedule_quantity
union all
SELECT msd.inventory_item_id,
bom.component_item_id,
msd.organization_id,
msd.schedule_date,
ecd.week_start_date,
null seq_num,
msd.SCHEDULE_DESIGNATOR,
msd.schedule_quantity
from schedule_dates msd,
calendar_dates ecd,
bom_table bom
where 1=1
and msd.schedule_level = 2
and bom.ORGANIZATION_ID = msd.organization_id
and bom.inventory_item_id = msd.inventory_item_id
and msd.schedule_designator = (select max(schedule_designator)
from MPS_SUMMARY_TABLE
)
* and ecd.week_start_date = msd.schedule_date+1
and ecd.calendar_date > sysdate-8
and ecd.seq_num is not null
group by msd.inventory_item_id,
bom.component_item_id,
msd.organization_id,
msd.schedule_date,
ecd.week_start_date,
msd.SCHEDULE_DESIGNATOR,
msd.schedule_quantity
)
Would appear to be fulfilled by
SELECT msd.inventory_item_id,
bom.component_item_id,
msd.organization_id,
msd.schedule_date,
ecd.week_start_date,
null seq_num,
msd.SCHEDULE_DESIGNATOR,
msd.schedule_quantity
from schedule_dates msd,
calendar_dates ecd,
bom_table bom
where 1=1
and msd.schedule_level = 2
and bom.ORGANIZATION_ID = msd.organization_id
and bom.inventory_item_id = msd.inventory_item_id
and msd.schedule_designator = (select max(schedule_designator)
from MPS_SUMMARY_TABLE
)
* and ecd.week_start_date between msd.schedule_date and msd.schedule_date+1
and ecd.calendar_date > sysdate-8
and ecd.seq_num is not null
The only difference I can see its the date field on the rows marked by '*' on the code. It doesnt appear that the union is needed and a 'between' will cover it.
May be necessary, may not save much time, but it can't hurt if it'll work (and as mentioned, grouping shouldnt be needed).
Did 9i have WITH query subfactoring? There's a some repeated queries that it might help with if its available.
Edit: actually it appears as though it can be written differently, let me look in the morning after coffee before you quote me though 
[Updated on: Wed, 08 September 2010 16:41] Report message to a moderator |
|
|
|
| Re: Need to tune query [message #474780 is a reply to message #474779] |
Wed, 08 September 2010 16:49 |
cookiemonster
Messages: 13921
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
That would depend on whether the group by is doing anything. If it is then merging those two could give different results.
If it isn't then doing that might help, but probably not as much as getting rid of the pointless sort.
|
|
|
|
| Re: Need to tune query [message #474839 is a reply to message #474780] |
Thu, 09 September 2010 05:05 |
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
|
Senior Member |
|
|
Lordy what a morning. Still no coffee yet its that bad.
That's true, I'll wait until the OP comes back on if its required before doing much more.
|
|
|
|
| Re: Need to tune query [message #474920 is a reply to message #474839] |
Thu, 09 September 2010 12:27 |
knw15pwr
Messages: 134
Registered: March 2010
|
Senior Member |
|
|
Hello All,
Thanks for all your inputs. Since we were kinda running out of time, we used the suggestion by CM. Added a WITH clause, this helped bring down the time a good deal.
Ross - the hint you suggested also worked to reduce the time, however we already had made changes using WITH clause and hence couldn't test it further.
I am currently doing an emergency fix...will get back to you folks later. Thanks again !!
|
|
|
|
|
|
| Re: Need to tune query [message #475047 is a reply to message #474921] |
Sat, 11 September 2010 12:17 |
knw15pwr
Messages: 134
Registered: March 2010
|
Senior Member |
|
|
Yes actually. The 2nd and 3rd SELECTs were untouched.
Turns out only the first select was using up most of the time. Thanks Roachcoach, we did 'peel-off' individual queries to figure this one out.
|
|
|
|
|
|
|
|
|
|
| Re: Need to tune query [message #475276 is a reply to message #474696] |
Tue, 14 September 2010 02:28 |
michael_bialik
Messages: 621
Registered: July 2006
|
Senior Member |
|
|
1. Why do you perform 2 full table scans on
( select max(schedule_designator)
from MPS_SUMMARY_TABLE
)
Try using WITH or DETERMINISTIC function to perform it once.
2. Post complete TKPROF resuls (row_source trace info) in order to understand where all these WAIT occurrs.
|
|
|
|
|
|
Goto Forum:
Current Time: Tue Apr 30 07:31:30 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
