Home » RDBMS Server » Performance Tuning » PLAN_TABLE_OUTPUT (Oracle 11g)
PLAN_TABLE_OUTPUT [message #645634] Thu, 10 December 2015 03:49 Go to next message
P1Oracle
Messages: 60
Registered: August 2014
Location: Hyderabad
Member
Hi All,
I am just working on performance of view which is created against query, i have used below two queries i order to tune:
EXPLAIN PLAN FOR SELECT xss.operating_unit_name, xss.organization_code,
          xss.trx_number invoice_number, xss.trx_date invoice_date,
          xss.account_number, xss.cust_name, xss.price_level,
          xss.order_number, xss.cust_po_number, xss.line_number,
          xss.oe_line_source_type, xss.mfg_code, xss.mfg_desc,
          xss.item_number, xss.description, xss.quantity_invoiced,
          xss.uom_code, xss.fulfillment_date, xss.invoice_currency_code,
          xss.exchange_rate, xss.sales_rate, xss.daily_sales_rate,
          xss.line_creation_date, xss.base_currency, xss.pricing_date,
          ROUND (xss.msrp / NVL (xss.sales_rate, xss.exchange_rate), 2) msrp,
          xss.list_price,
          ROUND (  xss.unit_selling_price
                 / NVL (xss.sales_rate, xss.exchange_rate),
                 2
                ) unit_selling_price,
          ROUND (xss.extended_amount / NVL (xss.sales_rate, xss.exchange_rate),
                 2
                ) extended_amount,
          ROUND (xss.unit_cost / NVL (xss.sales_rate, xss.exchange_rate),
                 2
                ) unit_cost,
          ROUND (xss.extend_cost / NVL (xss.sales_rate, xss.exchange_rate),
                 2
                ) extend_cost,
          ROUND (xss.std_unit_cost / NVL (xss.sales_rate, xss.exchange_rate),
                 2
                ) std_unit_cost,
          xss.line_category_code,
          ROUND (xss.mfg_rebate_amt / NVL (xss.sales_rate, xss.exchange_rate),
                 2
                ) mfg_rebate_amt,
          ROUND (  xss.mfg_rebate_amt_extend
                 / NVL (xss.sales_rate, xss.exchange_rate),
                 2
                ) mfg_rebate_amt_extend,
          xss.mfg_program_desc, xss.partner_authorization, xss.partner_level,
          ROUND (  xss.special_rebate_amt
                 / NVL (xss.sales_rate, xss.exchange_rate),
                 2
                ) special_rebate_amt,
          ROUND (  xss.special_rebate_amt_extend
                 / NVL (xss.sales_rate, xss.exchange_rate),
                 2
                ) special_rebate_amt_extend,
          xss.special_rebate_descr, xss.mfg_price_exception_num,
          xss.bill_to_site_use_id, xss.bill_to_addr1, xss.bill_to_city,
          xss.bill_to_state, xss.bill_to_postal_code, xss.bill_to_country,
          xss.ship_to_name, xss.ship_to_addr1, xss.ship_to_city,
          xss.ship_to_state, xss.ship_to_postal_code, xss.ship_to_country,
          xss.end_cust_name, xss.end_cust_addr1, xss.end_cust_city,
          xss.end_cust_state, xss.end_cust_postal_code, xss.end_cust_country,
          xss.price_category, xss.istore_category, xss.salesrep_name,
          xss.territory_name, xss.tran_type_name, xss.tran_type,
          xss.ship_method_code, xss.ship_method_meaning,
          xss.freight_terms_code, xss.freight_terms_meaning, xss.term_name,
          xss.po_number, NULL blank_serial_num, oola.subinventory,
          oola.open_flag line_open_flag, oola.booked_flag line_booked_flag,
          oola.flow_status_code line_status_code, oola.request_date,
          xss.operating_unit_id
     FROM xbsi.xbsi_sales_summary xss,
          jtf.jtf_rs_salesreps jrs,
          apps.fnd_user fu,
          ar.hz_cust_site_uses_all csu,
          apps.oe_order_lines_all oola
    WHERE xss.bill_to_site_use_id = csu.site_use_id
      AND csu.primary_salesrep_id = jrs.salesrep_id(+)
and  xss.OPERATING_UNIT_ID = jrs.ORG_ID
      AND csu.org_id = jrs.org_id(+)
      AND jrs.person_id = fu.employee_id(+)
      AND oola.header_id = xss.header_id
      AND oola.line_id = xss.line_id; 
Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------------
| Id  | Operation             | Name                  | Rows  | Bytes |TempSpc| Cost (%CPU)|
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                       |  1382K|   956M|       |   552K  (4)|
|*  1 |  HASH JOIN RIGHT OUTER|                       |  1382K|   956M|       |   552K  (4)|
|*  2 |   INDEX FULL SCAN     | FND_USER_N1           |  1021 |  2042 |       |     4   (0)|
|*  3 |   HASH JOIN           |                       |  1382K|   953M|   433M|   552K  (4)|
|   4 |    TABLE ACCESS FULL  | OE_ORDER_LINES_ALL    |  9890K|   320M|       |   277K  (6)|
|*  5 |    HASH JOIN          |                       |  1382K|   908M|       |   207K  (3)|
|   6 |     TABLE ACCESS FULL | JTF_RS_SALESREPS      |   375 |  6000 |       |     5   (0)|
|*  7 |     HASH JOIN         |                       |  8296K|  5325M|       |   207K  (3)|
|*  8 |      TABLE ACCESS FULL| HZ_CUST_SITE_USES_ALL | 70000 |   820K|       | 11282   (7)|
|   9 |      TABLE ACCESS FULL| XBSI_SALES_SUMMARY    |  8296K|  5230M|       |   195K  (3)|
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("JRS"."PERSON_ID"="FU"."EMPLOYEE_ID"(+))
   2 - filter("FU"."EMPLOYEE_ID"(+) IS NOT NULL)
   3 - access("OOLA"."HEADER_ID"="XSS"."HEADER_ID" AND
              "OOLA"."LINE_ID"="XSS"."LINE_ID")
   5 - access("CSU"."PRIMARY_SALESREP_ID"="JRS"."SALESREP_ID" AND
              "XSS"."OPERATING_UNIT_ID"="JRS"."ORG_ID" AND "CSU"."ORG_ID"="JRS"."ORG_ID")
   7 - access("XSS"."BILL_TO_SITE_USE_ID"="CSU"."SITE_USE_ID")
   8 - filter("CSU"."PRIMARY_SALESREP_ID" IS NOT NULL)

Note
-----
   - 'PLAN_TABLE' is old version

31 rows selected.

SQL> 


From the above output i didn't understand, could any one help please in order to retrieve data faster..
Thanq In advance
Re: PLAN_TABLE_OUTPUT [message #645636 is a reply to message #645634] Thu, 10 December 2015 03:52 Go to previous messageGo to next message
P1Oracle
Messages: 60
Registered: August 2014
Location: Hyderabad
Member
I am just created a view against above query and trying to retrieving data from below select statement
select * from XBSI_SALES_COST_DATA_V where OPERATING_UNIT_ID=185

but it is taking long time to execute the query..please help me anyone, what should i do?
Re: PLAN_TABLE_OUTPUT [message #645637 is a reply to message #645634] Thu, 10 December 2015 04:02 Go to previous messageGo to next message
John Watson
Messages: 7663
Registered: January 2010
Location: Global Village
Senior Member
What is the purpose of the joins to these tables:

jtf.jtf_rs_salesreps jrs,
apps.fnd_user fu,
ar.hz_cust_site_uses_all csu,


you are not projecting any columns from them, and if you intend to use them to eliminate rows from the result set (is that your intention?) then an EXISTS predicate would be more efficient than a join.
Re: PLAN_TABLE_OUTPUT [message #645639 is a reply to message #645637] Thu, 10 December 2015 04:47 Go to previous messageGo to next message
cookiemonster
Messages: 13333
Registered: September 2008
Location: Rainy Manchester
Senior Member
Two of three - jrs and fu are outer-joined (though the outer-join to jrs is incomplete), so those two definitely should be removed.
csu appears to be there to join though to jrs so the need for that one is doubtful as well.
Re: PLAN_TABLE_OUTPUT [message #645641 is a reply to message #645639] Thu, 10 December 2015 05:37 Go to previous messageGo to next message
msol25
Messages: 396
Registered: June 2011
Senior Member
Please mention Access key for the View.Explain Plan should be decided based on Access key when you are using query in view script.

As per my Understanding your Query should be Modified like below Mentioned:


WHERE 1 = 1
AND   jrs.ORG_ID = xss.OPERATING_UNIT_ID
AND   xss.header_id = AND oola.header_id
AND   xss.line_id = oola.line_id
AND   jrs.org_id(+) = csu.org_id
AND   jrs.person_id = fu.employee_id(+)



I hope,In your Query,JRS should be the Driving table.

Please share explain plan with Access Key for getting more details about your explain plan.
Re: PLAN_TABLE_OUTPUT [message #645643 is a reply to message #645639] Thu, 10 December 2015 06:16 Go to previous messageGo to next message
P1Oracle
Messages: 60
Registered: August 2014
Location: Hyderabad
Member
Thanks for the replies...
Even though i comment out the joins (AND jrs.person_id = fu.employee_id(+)), it is taking long time nearly of 30+ minutes..
Re: PLAN_TABLE_OUTPUT [message #645644 is a reply to message #645643] Thu, 10 December 2015 06:20 Go to previous messageGo to next message
John Watson
Messages: 7663
Registered: January 2010
Location: Global Village
Senior Member
Removing the join conditions from the predicate means you will be getting cartesian joins. You have to think about what you are doing: remove the tables as well. If you were to rewrite the code to use ANSI join syntax, as all good programmers do, you wouldn't be making this sort of error.
Re: PLAN_TABLE_OUTPUT [message #645645 is a reply to message #645644] Thu, 10 December 2015 06:24 Go to previous messageGo to next message
P1Oracle
Messages: 60
Registered: August 2014
Location: Hyderabad
Member
As per msol25 : i reran the query , here is output for
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------------
| Id  | Operation              | Name                 | Rows  | Bytes |TempSpc| Cost (%CPU)|
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |                      |   149T|    94P|       |  3044M(100)|
|*  1 |  HASH JOIN             |                      |   149T|    94P|    26M|  3044M(100)|
|   2 |   INDEX FULL SCAN      | HZ_CUST_SITE_USES_N5 |  1730K|  6761K|       |  7621   (2)|
|*  3 |   HASH JOIN RIGHT OUTER|                      |   518M|   340G|       |   781K  (6)|
|*  4 |    INDEX FULL SCAN     | FND_USER_N1          |  1021 |  2042 |       |     4   (0)|
|*  5 |    HASH JOIN           |                      |   518M|   339G|       |   770K  (4)|
|   6 |     TABLE ACCESS FULL  | JTF_RS_SALESREPS     |   375 |  3375 |       |     5   (0)|
|*  7 |     HASH JOIN          |                      |  8296K|  5499M|   433M|   760K  (3)|
|   8 |      TABLE ACCESS FULL | OE_ORDER_LINES_ALL   |  9890K|   320M|       |   277K  (6)|
|   9 |      TABLE ACCESS FULL | XBSI_SALES_SUMMARY   |  8296K|  5230M|       |   195K  (3)|
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("JRS"."ORG_ID"="CSU"."ORG_ID")
   3 - access("JRS"."PERSON_ID"="FU"."EMPLOYEE_ID"(+))
   4 - filter("FU"."EMPLOYEE_ID"(+) IS NOT NULL)
   5 - access("JRS"."ORG_ID"="XSS"."OPERATING_UNIT_ID")
   7 - access("XSS"."HEADER_ID"="OOLA"."HEADER_ID" AND
              "XSS"."LINE_ID"="OOLA"."LINE_ID")

Note
-----
   - 'PLAN_TABLE' is old version

29 rows selected.
Re: PLAN_TABLE_OUTPUT [message #645646 is a reply to message #645645] Thu, 10 December 2015 07:10 Go to previous messageGo to next message
msol25
Messages: 396
Registered: June 2011
Senior Member
Dear,

Please can you tell us your access key for View.Please provide Access Key column.
Re: PLAN_TABLE_OUTPUT [message #645647 is a reply to message #645646] Thu, 10 December 2015 07:46 Go to previous messageGo to next message
cookiemonster
Messages: 13333
Registered: September 2008
Location: Rainy Manchester
Senior Member
Why don't you remove the pointless tables and then re-run it?
Re: PLAN_TABLE_OUTPUT [message #645649 is a reply to message #645641] Thu, 10 December 2015 07:49 Go to previous messageGo to next message
cookiemonster
Messages: 13333
Registered: September 2008
Location: Rainy Manchester
Senior Member
msol25 wrote on Thu, 10 December 2015 11:37

As per my Understanding your Query should be Modified like below Mentioned:


WHERE 1 = 1
AND   jrs.ORG_ID = xss.OPERATING_UNIT_ID
AND   xss.header_id = AND oola.header_id
AND   xss.line_id = oola.line_id
AND   jrs.org_id(+) = csu.org_id
AND   jrs.person_id = fu.employee_id(+)





No it really shouldn't. Sorting out the mess of the outer-joins would be far more useful than re-ordering the where clause.
Re: PLAN_TABLE_OUTPUT [message #645650 is a reply to message #645649] Thu, 10 December 2015 07:50 Go to previous messageGo to next message
pablolee
Messages: 2880
Registered: May 2007
Location: Scotland
Senior Member
cookiemonster wrote on Thu, 10 December 2015 13:49
msol25 wrote on Thu, 10 December 2015 11:37

As per my Understanding your Query should be Modified like below Mentioned:


WHERE 1 = 1
AND   jrs.ORG_ID = xss.OPERATING_UNIT_ID
AND   xss.header_id = AND oola.header_id
AND   xss.line_id = oola.line_id
AND   jrs.org_id(+) = csu.org_id
AND   jrs.person_id = fu.employee_id(+)





No it really shouldn't. Sorting out the mess of the outer-joins would be far more useful than re-ordering the where clause.

Especially considering the where clause has been rewritten to be syntactically incorrect Smile
Re: PLAN_TABLE_OUTPUT [message #645661 is a reply to message #645650] Thu, 10 December 2015 10:49 Go to previous message
cookiemonster
Messages: 13333
Registered: September 2008
Location: Rainy Manchester
Senior Member
I had to look at that a few times before I spotted the extraneous AND.
Previous Topic: Export from table vs external table
Next Topic: Database slowness and high load due to IO Latencies?
Goto Forum:
  


Current Time: Wed Nov 14 07:37:48 CST 2018