Home » RDBMS Server » Performance Tuning » PLAN_TABLE_OUTPUT (Oracle 11g)
| PLAN_TABLE_OUTPUT [message #645634] |
Thu, 10 December 2015 03:49  |
 |
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 #645637 is a reply to message #645634] |
Thu, 10 December 2015 04:02  |
John Watson
Messages: 8964
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 |
cookiemonster
Messages: 13963
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 #645644 is a reply to message #645643] |
Thu, 10 December 2015 06:20 |
John Watson
Messages: 8964
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 #645649 is a reply to message #645641] |
Thu, 10 December 2015 07:49 |
cookiemonster
Messages: 13963
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.
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Thu Jan 30 13:27:44 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
