Home » RDBMS Server » Performance Tuning » SQL performance (Oracle 11i)
SQL performance [message #611204] |
Sun, 30 March 2014 04:27 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/941df0b59ad808f720adddecf35d2697?s=64&d=mm&r=g) |
huda_10@yahoo.co.in
Messages: 7 Registered: March 2014
|
Junior Member |
|
|
Hi,
The attached query is taking very long time to execute. Attahed also the Explain plan on the query.
Appreciate if you can help in tuning the query to execute it faster
Thanks,
-
Attachment: query.txt
(Size: 20.39KB, Downloaded 2480 times)
[Updated on: Sun, 30 March 2014 04:30] Report message to a moderator
|
|
|
|
Re: SQL performance [message #611217 is a reply to message #611204] |
Sun, 30 March 2014 11:36 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
John Watson
Messages: 8944 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
There are many "red flags" in this query.
First, the author has tried to tune it by using a hint, but the hint is totally meaningless. This suggests that the developer is working beyond his level of competance, which is very dangerous when working with EBS. The query may not even be returning a result that is valid.
Second, the plan_table is an old version. This suggests that your DBA is not very diligent: he should have located and dropped all out-dated copies of the plan_table when he last upgraded the database. You really need good DBA for an EBS installation.
Third, the statistics look very suspect. Operation id 47 is full scan with no filter of OE_ORDER_HEADERS_ALL, and the stats say this will return only 1747 rows. This is very unlikely for a production system. Another indication that your DBA may not be up to the job.
There is more....
Here's an action plan:
1. Follow the instructions in the Black Swan's post. Except perhaps for the DDLs, they are huge and anyone can look them up on etrm.oracle.com
2. Drop all copies of the PLAN_TABLE, and use the publc synonym pointing to the correct version instead.
3. Analyze the database, with estimate_percent=>100, method_opt=>'for all columns size skewonly'. I believe this is better than using fnd_stats (others may disagree). Gather system stats, too.
4. Run EXPLAIN PLAN against the query again.
5. Add the hint /+ gather_plan_statistics */ and run the query. Post the plan, with the estimated and actual statistics.
6. Hire me as your apps dba ![Smile](images/smiley_icons/icon_smile.gif)
By the way, is there a reason for you creating a new account? I see that you have been posting here as huda10 since 2008.
|
|
|
|
|
|
Re: SQL performance [message #611235 is a reply to message #611229] |
Mon, 31 March 2014 00:53 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/72104.gif) |
Littlefoot
Messages: 21818 Registered: June 2005 Location: Croatia, Europe
|
Senior Member Account Moderator |
|
|
huda_10@yahoo.co.in wrote on Mon, 31 March 2014 07:35
... I'm not able to upload the files. I don't find this option to attach files
This option doesn't exist when posting "quick replies" (located at the bottom of the topic). In order to attach files, click "Reply" button at the bottom right corner of every message. It will open a Post Form which contains the "File Attachments" section that allows you to browse files stored on your computer, upload & attach them to your message.
P.S. If you want, your accounts can be merged.
[Updated on: Mon, 31 March 2014 00:55] Report message to a moderator
|
|
|
|
|
|
Re: SQL performance [message #611366 is a reply to message #611300] |
Wed, 02 April 2014 00:02 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/56289.jpg) |
Kevin Meade
Messages: 2103 Registered: December 1999 Location: Connecticut USA
|
Senior Member |
|
|
one way to check out long queries like this is to use the old technique of query decomposition and reconstruction. It takes a bit of effort but can be very insightful if you have the time. Essentially you break the query down by table. Then you rebuild the query one table at a time to see how long it takes. At some point you may see a significant increase in runtime. Also, you can see where adding pieces of a query back do not consume significant runtime. Either way it gives you some direction on where to concentrate to achieve a speed up. It is a little error prone so you have to go slow to make sure you have not missed an join as you add a new table. But it is a old method and it works. Let me get you started. Here are three queries taht pick some table (somewhat at random) as a starting point. You should get the idea. Running each will tell you how much more time adding the additional table and its join takes.
Notice that the distinct and order by have been removed. This lets you see the number of intermediary rows you are dealing with. Thus you can spot a significant jump in data movement. It also lets you see how much the order by or distinct adds.
create table mytemp1
nologging
as
select
xpdcr.element_name,
xpdcr.actual_value
from xxgmd_pm_drop_chem_results xpdcr
where upper(xpdcr.element_name) <> 'AL'
/
create table mytemp2
nologging
as
select
xpdcr.element_name,
xpdcr.actual_value,
xpd.drop_id,
xpd.drop_number,
xpd.plant_code
from xxgmd_pm_drop_chem_results xpdcr,
xxgmd_pm_drops xpd
where upper(xpdcr.element_name) <> 'AL'
AND xpd.drop_id = xpdcr.drop_id
/
create table mytemp3
nologging
as
select /*+ hint index(element.print_order) */
xpdcr.element_name,
xpdcr.actual_value,
xpd.drop_id,
xpd.drop_number,
xpd.plant_code,
element.print_order
from xxgmd_pm_drop_chem_results xpdcr,
xxgmd_pm_drops xpd,
xxgmd_pm_elements element
where upper(xpdcr.element_name) <> 'AL'
AND xpd.drop_id = xpdcr.drop_id
and upper(element.element_name) = upper(xpdcr.element_name)
/
Since your big query has 18 tables, you will eventually have 18 different queries if you do all tables.
Good luck. Kevin
|
|
|
Re: SQL performance [message #611374 is a reply to message #611300] |
Wed, 02 April 2014 01:41 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
John Watson
Messages: 8944 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
THe results of the trace show a very different plan from that you posted originally. Did you follow my advice, and gather statistics? Did you follow any of my other suggestions? Is the query any faster now?
|
|
|
|
Re: SQL performance [message #611522 is a reply to message #611204] |
Thu, 03 April 2014 05:32 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
michael_bialik
Messages: 621 Registered: July 2006
|
Senior Member |
|
|
IMHO - the problem is incorrect JOIN order, causing very inefficient access to WSH_DELIVERY_DETAILS table:
182534 182534 182534 INDEX RANGE SCAN WSH_DELIVERY_DETAILS_N3 (cr=16027 pr=816 pw=0 time=2753402 us cost=2 size=0 card=41)(object id 71030)
182534 182534 182534 TABLE ACCESS BY INDEX ROWID WSH_DELIVERY_DETAILS (cr=20517 pr=13885 pw=0 time=27595345 us cost=6 size=1428 card=34)
Can you try:
WITH W AS ( SELECT /*+ ORDERED */ WDD.ORGANIZATION_ID, WDD.SOURCE_LINE_ID, WDD.LOT_NUMBER,
WDD.SHIPPED_QUANTITY2, WDD.NET_WEIGHT, wdd.inventory_item_id,
WND.GROSS_WEIGHT, WDD.WEIGHT_UOM_CODE, ROWNUM RNN
FROM WSH_NEW_DELIVERIES WND,
WSH_DELIVERY_ASSIGNMENTS WDA,
WSH_DELIVERY_DETAILS WDD
WHERE wnd.DELIVERY_ID = 189373 --199369--704404--:P_DELIVERY_ID
AND WND.DELIVERY_ID = WDA.DELIVERY_ID
AND WDD.DELIVERY_DETAIL_ID = WDA.DELIVERY_DETAIL_ID )
SELECT /*+ ORDERED index(element.print_order) */
distinct xpd.drop_id,
xpd.drop_number,
xpdcr.element_name,
xpdcr.actual_value,
nvl(MLN.PARENT_LOT_NUMBER, 0) PARENT_LOT,
nvl(W.LOT_NUMBER, 0) CHILD_LOT1,
nvl(W.SHIPPED_QUANTITY2,0) SEC_UOM_PC,
nvl(W.NET_WEIGHT, 0) NET_WEIGHT,
nvl(W.GROSS_WEIGHT, 0) GROSS_WEIGHT,
W.WEIGHT_UOM_CODE NET_GROSS_UOM,
mdev.element_value Alloy_no,
ooha.order_number,
ooha.header_id,
ooha.sold_to_org_id,
CUST_ACCT.ACCOUNT_NUMBER CUSTOMER_NUMBER,
ship_cas.party_site_id,
xpd.plant_code,
element.print_order
FROM W,
OE_ORDER_LINES_ALL OOLA,
OE_ORDER_HEADERS_ALL OOHA,
MTL_SYSTEM_ITEMS_B MSIB,
MTL_DESCR_ELEMENT_VALUES MDEV,
MTL_LOT_NUMBERS MLN,
hz_cust_accounts cust_acct,
hz_cust_site_uses_all ship_su,
hz_cust_acct_sites_all ship_cas,
-- HR_ORGANIZATION_UNITS hou,
-- hr_locations_all hla,
org_organization_definitions ood,
xxgmd_pm_drops xpd,
xxgmd_pm_drop_chem_results xpdcr,
xxgmd_pm_cspecs cspec,
xxgmd_pm_cspec_elements cspec_el,
xxgmd_pm_cspec_element_rules cspec_rule,
xxgmd_pm_elements element
WHERE W.SOURCE_LINE_ID = OOLA.LINE_ID
AND OOLA.HEADER_ID = OOHA.HEADER_ID
AND MSIB.INVENTORY_ITEM_ID = OOLA.INVENTORY_ITEM_ID
AND MSIB.ORGANIZATION_ID(+) = W.ORGANIZATION_ID
and msib.inventory_item_id = mdev.inventory_item_id
AND OOHA.SHIP_FROM_ORG_ID = OOD.ORGANIZATION_ID
AND OOHA.sold_to_org_id = cust_acct.cust_account_id(+)
AND MLN.LOT_NUMBER(+) = W.LOT_NUMBER
AND mln.inventory_item_id(+) = w.inventory_item_id --Added on 26-Nov-12 from offshore
and mdev.element_name = 'C-ALLOY'
and xpd.drop_number = MLN.PARENT_LOT_NUMBER
AND xpd.plant_code = ood.organization_code
AND xpd.drop_id = xpdcr.drop_id
-- AND ship_cas.cust_account_id = ooha.sold_to_org_id
AND ship_cas.cust_acct_site_id = ship_su.cust_acct_site_id
AND ooha.deliver_to_org_id = ship_su.site_use_id
AND cspec.cspec_name = mdev.element_value
AND cspec.cspec_id = cspec_el.cspec_id
AND cspec_rule.cspec_element_id = cspec_el.cspec_element_id
and cspec_rule.party_site_id = ship_cas.party_site_id
and cspec_rule.print_element = 'Y'
and cspec_el.element_id = element.element_id
and upper(element.element_name) = upper(xpdcr.element_name)
and upper(xpdcr.element_name) <> 'AL'
AND SYSDATE BETWEEN cspec.effective_start_date
AND NVL (cspec.effective_end_date,SYSDATE + 500 )
order by element.print_order
|
|
|
Goto Forum:
Current Time: Sat Sep 07 18:40:58 CDT 2024
|