Home » RDBMS Server » Performance Tuning » SQL performance (Oracle 11i)
SQL performance [message #611204] Sun, 30 March 2014 04:27 Go to next message
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 91 times)

[Updated on: Sun, 30 March 2014 04:30]

Report message to a moderator

Re: SQL performance [message #611214 is a reply to message #611204] Sun, 30 March 2014 09:15 Go to previous messageGo to next message
BlackSwan
Messages: 22905
Registered: January 2009
Senior Member
Welcome to this forum.

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/ and please read http://www.orafaq.com/forum/t/174502/102589/


Since NOBODY can optimize SQL just by looking at it, we need a few more details.
http://www.orafaq.com/forum/m/433888/#msg_433888
Please refer to URL above & be sure to provide the details requested:
1) DDL for all tables & indexes
2) output from SQL_TRACE & tkprof
Re: SQL performance [message #611217 is a reply to message #611204] Sun, 30 March 2014 11:36 Go to previous messageGo to next message
John Watson
Messages: 4686
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

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 #611229 is a reply to message #611217] Mon, 31 March 2014 00:35 Go to previous messageGo to next message
huda_10@yahoo.co.in
Messages: 7
Registered: March 2014
Junior Member
Thanks a lot for your replies.

Following your suggestion to provide the DDL,TKPROF etc, but I'm not able to upload the files. I don't find this option to attach files. Can you please help me.
Re: SQL performance [message #611231 is a reply to message #611229] Mon, 31 March 2014 00:38 Go to previous messageGo to next message
huda_10@yahoo.co.in
Messages: 7
Registered: March 2014
Junior Member
Hi John,

I just forgot the username and password , so created to new user.
Re: SQL performance [message #611233 is a reply to message #611231] Mon, 31 March 2014 00:43 Go to previous messageGo to next message
BlackSwan
Messages: 22905
Registered: January 2009
Senior Member
consider only using MS ACCESS since Oracle appears beyond your capabilities.
Re: SQL performance [message #611235 is a reply to message #611229] Mon, 31 March 2014 00:53 Go to previous messageGo to next message
Littlefoot
Messages: 19694
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 #611298 is a reply to message #611235] Tue, 01 April 2014 03:03 Go to previous messageGo to next message
huda_10@yahoo.co.in
Messages: 7
Registered: March 2014
Junior Member
Hi,

PLease find the attached fiels requested.
Re: SQL performance [message #611299 is a reply to message #611298] Tue, 01 April 2014 03:04 Go to previous messageGo to next message
huda_10@yahoo.co.in
Messages: 7
Registered: March 2014
Junior Member
Please find the metadata
Re: SQL performance [message #611300 is a reply to message #611299] Tue, 01 April 2014 03:05 Go to previous messageGo to next message
huda_10@yahoo.co.in
Messages: 7
Registered: March 2014
Junior Member
Please find the trace file
Re: SQL performance [message #611366 is a reply to message #611300] Wed, 02 April 2014 00:02 Go to previous messageGo to next message
Kevin Meade
Messages: 1943
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 messageGo to next message
John Watson
Messages: 4686
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 #611423 is a reply to message #611374] Wed, 02 April 2014 07:18 Go to previous messageGo to next message
huda_10@yahoo.co.in
Messages: 7
Registered: March 2014
Junior Member
If we run the gather stats, the query run little faster. But we have to run the gather stats 3 times in a day due to huge data inserts in those tables.
Re: SQL performance [message #611522 is a reply to message #611204] Thu, 03 April 2014 05:32 Go to previous message
michael_bialik
Messages: 604
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
Previous Topic: elapsed_time_delta in dba_hist_sqlstat
Next Topic: Materialized View not being used on production (works elsewhere)
Goto Forum:
  


Current Time: Thu Oct 23 14:03:22 CDT 2014

Total time taken to generate the page: 0.09077 seconds