we have one sql and that is causing performance issue.ROW_NUMBER is main culprit,when i remove it i got rows in a min,though the number of records are more but with row_number to retirve 3k records it took 40 min of time.i tried with few options but it didn't imporve the performance.I don't know how to tackle that row_num order by.
WITH
lines
AS
(SELECT oh.order_number || ool.line_number AS key_id,
ool.line_id,
ool.header_id,
oh.order_number,
ool.line_number,
ool.item_type_code,
ool.inventory_item_id,
ool.ship_from_org_id,
TO_CHAR (x3h.poreceiptdate, 'mm/dd/yyyy') poreceiveddate,
mc.segment2 prodline
FROM apps.oe_order_lines_all ool
LEFT JOIN apps.oe_order_headers_all oh
ON oh.header_id = ool.header_id
LEFT JOIN apps.xxom_3lp_sym_ora_order_hdr x3h
ON ool.header_id = x3h.header_id
LEFT JOIN apps.mtl_item_categories mic
ON ool.inventory_item_id = mic.inventory_item_id
AND mic.organization_id = 85
AND mic.category_set_id = 1100000041
LEFT JOIN apps.mtl_categories_b mc
ON mc.category_id = mic.category_id
WHERE ool.cancelled_flag = 'N'
AND ool.item_type_code IN ('OPTION', 'CONFIG', 'STANDARD')
AND ool.org_id IN (4622,
5241,
5259,
6281,
6421,
8054,
8060,
8266,
8284,
8530,
7165,
7193,
9287,
4575,
8272,
8280,
9907,
9925,
10907,
10927,
11911,
11916,
11924,
15363,
15366,
4190,
13063,
16596,
6741,
4195,
6761,
90467486,
9051,
7824,
10447,
10452,
7491,
17302)
AND ool.ship_from_org_id IN (4576,
4623,
4681,
4682,
5242,
5260,
6300,
6422,
7166,
7194,
8055,
8061,
8268,
8285,
8531,
8545,
9026,
9288,
9294,
9295,
9425,
9745,
10025,
10707,
10708,
12891,
12894,
12895,
12897,
12898,
12942,
12962,
13263,
13282,
13283,
13284,
13285,
13286,
13287,
13502,
13682,
13702,
13703,
13763,
149030,
12989,
4191,
12449,
16597,
16598,
16599,
16600,
12900,
12893,
12892,
9047,
12890,
7487,
9052,
12901,
7825,
10448,
10453,
12899,
17322)
AND SUBSTR (mc.segment2, 1, 1) IN ('J')
AND ( (ool.last_update_date > SYSDATE - 5)
OR (oh.last_update_date > SYSDATE - 5)
OR (x3h.last_update_date > SYSDATE - 5)))
SELECT *
FROM (SELECT ROW_NUMBER ()
OVER (PARTITION BY header_id,
line_id,
ph.segment1,
pla.line_num
ORDER BY line_id)
AS RowNbr,
lines.*,
pll.closed_code
po_line_status,
TO_CHAR (pll.need_by_date, 'mm/dd/yyyy')
AS need_by_date,
TO_CHAR (pll.promised_date, 'mm/dd/yyyy')
AS popromised_date,
pla.line_num
po_line_number,
TO_CHAR (pla.creation_date, 'mm/dd/yyyy')
pocreateddate,
pla.unit_price,
pla.quantity,
ph.segment1
po_number,
ph.authorization_status
po_status,
ph.currency_code,
ROUND (ph.rate, 4)
AS rate,
(SELECT mc.segment1
FROM apps.mtl_item_categories mic,
apps.mtl_category_sets_tl mcs,
apps.mtl_categories_b mc
WHERE lines.inventory_item_id = mic.inventory_item_id
AND mic.organization_id = lines.ship_from_org_id
AND mic.category_set_id = mcs.category_set_id
AND mcs.language = 'US'
AND mcs.category_set_name = 'Inventory'
AND mic.category_id = mc.category_id)
commcode,
(SELECT MAX (transaction_date)
FROM apps.rcv_transactions rcv
WHERE rcv.po_header_id = ph.po_header_id
AND rcv.po_line_id = pla.po_line_id
AND transaction_type = 'RECEIVE'
AND ph.po_header_id = rcv.po_header_id
AND pla.po_line_id = rcv.po_line_id)
AS poactualdockdate,
pv.vendor_name,
pvs.vendor_site_code
vendor_site,
CASE
WHEN ph.currency_code = 'USD' THEN 1
ELSE ROUND (gldr.conversion_rate, 4)
END
conversion_rate,
CASE
WHEN ph.currency_code = 'USD'
THEN
ROUND (pla.unit_price * pla.quantity * 1, 2)
ELSE
ROUND (
pla.unit_price
* pla.quantity
* gldr.conversion_rate,
2)
END
usd_total_price,
NVL (papf1.full_name, papf2.full_name)
buyer
FROM lines
LEFT JOIN apps.po_requisition_lines_all prl
ON TO_CHAR (lines.line_id) = prl.attribute1
LEFT JOIN apps.po_line_locations_all pll
ON prl.line_location_id = pll.line_location_id
LEFT JOIN apps.po_lines_all pla
ON pll.po_line_id = pla.po_line_id
LEFT JOIN apps.po_headers_all ph
ON pla.po_header_id = ph.po_header_id
AND pla.org_id = ph.org_id
LEFT JOIN apps.ap_supplier_sites_all pvs
ON NVL (ph.vendor_site_id, prl.vendor_site_id) =
pvs.vendor_site_id
LEFT JOIN apps.ap_supplier_sites_all pvs
ON NVL (ph.vendor_site_id, prl.vendor_site_id) =
pvs.vendor_site_id
JOIN apps.ap_suppliers pv ON ph.vendor_id = pv.vendor_id
LEFT JOIN apps.mtl_system_items_b msib
ON lines.inventory_item_id = msib.inventory_item_id
AND lines.ship_from_org_id = msib.organization_id
LEFT JOIN apps.per_all_people_f papf1
ON prl.suggested_buyer_id = papf1.person_id
LEFT JOIN apps.per_all_people_f papf2
ON msib.buyer_id = papf2.person_id
LEFT JOIN
(SELECT *
FROM apps.gl_daily_rates gldr
WHERE gldr.to_currency = 'USD'
AND conversion_type = 'Corporate') gldr
ON ( gldr.from_currency = ph.currency_code
AND TRUNC (ph.rate_date) =
TRUNC (gldr.conversion_date))
) a
WHERE RowNbr = 1
i tried with rank function and removing row_number.It impacted the output
CodePudding user response：
Yes, as i mentioned that when i comment it out row_number then data comes faster
| Id | Operation | Name | Starts | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | O/1/M |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | | 9265K(100)| | 2935 |00:37:28.08 | 26M| 297K| | | |
| 1 | NESTED LOOPS | | 1748 | | | | | | 1739 |00:00:00.84 | 83859 | 518 | | | |
| 2 | NESTED LOOPS | | 1748 | 1 | 82 | | 7 (0)| 00:00:01 | 1739 |00:00:00.84 | 82120 | 518 | | | |
| 3 | NESTED LOOPS | | 1748 | 1 | 63 | | 5 (0)| 00:00:01 | 1739 |00:00:00.83 | 80247 | 518 | | | |
|* 4 | INDEX RANGE SCAN | MTL_ITEM_CATEGORIES_U1 | 1748 | 1 | 22 | | 4 (0)| 00:00:01 | 42491 |00:00:00.61 | 5540 | 518 | | | |
|* 5 | TABLE ACCESS BY INDEX ROWID | MTL_CATEGORY_SETS_TL | 42491 | 1 | 41 | | 1 (0)| 00:00:01 | 1739 |00:00:00.08 | 74707 | 0 | | | |
|* 6 | INDEX UNIQUE SCAN | MTL_CATEGORY_SETS_TL_U1 | 42491 | 1 | | | 0 (0)| | 42491 |00:00:00.04 | 32216 | 0 | | | |
|* 7 | INDEX UNIQUE SCAN | MTL_CATEGORIES_B_U1 | 1739 | 1 | | | 1 (0)| 00:00:01 | 1739 |00:00:00.01 | 1873 | 0 | | | |
| 8 | TABLE ACCESS BY INDEX ROWID | MTL_CATEGORIES_B | 1739 | 1 | 19 | | 2 (0)| 00:00:01 | 1739 |00:00:00.01 | 1739 | 0 | | | |
| 9 | SORT AGGREGATE | | 2744 | 1 | 27 | | | | 2744 |00:00:02.20 | 41381 | 301 | | | |
|* 10 | TABLE ACCESS BY INDEX ROWID | RCV_TRANSACTIONS | 2744 | 1 | 27 | | 6 (0)| 00:00:01 | 1821 |00:00:02.20 | 41381 | 301 | | | |
|* 11 | INDEX RANGE SCAN | RCV_TRANSACTIONS_N5 | 2744 | 4 | | | 3 (0)| 00:00:01 | 3646 |00:00:02.06 | 39812 | 270 | | | |
|* 12 | VIEW | | 1 | 51 | 31314 | | 9265K (1)| 00:06:02 | 2935 |00:37:28.08 | 26M| 297K| | | |
|* 13 | WINDOW SORT PUSHED RANK | | 1 | 51 | 25755 | | 9265K (1)| 00:06:02 | 2935 |00:37:27.17 | 26M| 296K| 8841K| 1162K| 1/0/0|
| 14 | NESTED LOOPS OUTER | | 1 | 51 | 25755 | | 9265K (1)| 00:06:02 | 16980 |00:01:42.38 | 26M| 296K| | | |
| 15 | NESTED LOOPS OUTER | | 1 | 51 | 24939 | | 9265K (1)| 00:06:02 | 16980 |00:01:42.34 | 26M| 296K| | | |
| 16 | NESTED LOOPS OUTER | | 1 | 51 | 24633 | | 9265K (1)| 00:06:02 | 16980 |00:01:42.32 | 26M| 296K| | | |
| 17 | NESTED LOOPS OUTER | | 1 | 32 | 14688 | | 9265K (1)| 00:06:02 | 5081 |00:04:23.98 | 26M| 296K| | | |
| 18 | NESTED LOOPS OUTER | | 1 | 3 | 1305 | | 9265K (1)| 00:06:02 | 2936 |00:07:11.30 | 26M| 296K| | | |
| 19 | NESTED LOOPS OUTER | | 1 | 3 | 1170 | | 9265K (1)| 00:06:02 | 2936 |00:07:02.75 | 25M| 296K| | | |
| 20 | NESTED LOOPS | | 1 | 3 | 1131 | | 9265K (1)| 00:06:02 | 2936 |00:07:02.61 | 25M| 296K| | | |
| 21 | VIEW | | 1 | 3 | 1041 | | 9265K (1)| 00:06:02 | 448K|00:16:04.92 | 25M| 296K| | | |
| 22 | NESTED LOOPS OUTER | | 1 | 3 | 918 | | 9265K (1)| 00:06:02 | 448K|00:16:04.47 | 25M| 296K| | | |
| 23 | NESTED LOOPS OUTER | | 1 | 3 | 753 | | 9265K (1)| 00:06:02 | 448K|00:16:04.12 | 25M| 296K| | | |
| 24 | NESTED LOOPS OUTER | | 1 | 3 | 642 | | 9265K (1)| 00:06:02 | 448K|00:16:03.56 | 25M| 296K| | | |
| 25 | NESTED LOOPS OUTER | | 1 | 3 | 540 | | 9265K (1)| 00:06:02 | 448K|00:16:02.92 | 25M| 296K| | | |
|* 26 | FILTER | | 1 | | | | | | 447K|00:13:31.89 | 25M| 296K| | | |
| 27 | NESTED LOOPS OUTER | | 1 | 3 | 492 | | 9265K (1)| 00:06:02 | 535K|00:13:53.58 | 25M| 296K| | | |
| 28 | NESTED LOOPS OUTER | | 1 | 3 | 372 | | 9265K (1)| 00:06:02 | 535K|00:13:52.23 | 24M| 296K| | | |
|* 29 | FILTER | | 1 | | | | | | 535K|00:13:31.84 | 23M| 289K| | | |
|* 30 | HASH JOIN RIGHT OUTER | | 1 | 3 | 306 | 174M| 9265K (1)| 00:06:02 | 31M|00:36:25.36 | 23M| 289K| 343M| 14M| 1/0/0|
|* 31 | TABLE ACCESS FULL | XXOM_3LP_SYM_ORA_ORDER_HDR | 1 | 5553K| 111M| | 205K (1)| 00:00:09 | 5499K|00:00:03.66 | 423K| 0 | | | |
|* 32 | HASH JOIN RIGHT OUTER | | 1 | 3736K| 288M| 194M| 9005K (1)| 00:05:52 | 31M|00:36:05.83 | 22M| 289K| 401M| 14M| 1/0/0|
| 33 | TABLE ACCESS FULL | OE_ORDER_HEADERS_ALL | 1 | 6382K| 121M| | 205K (1)| 00:00:09 | 6353K|00:00:02.41 | 429K| 0 | | | |
| 34 | INLIST ITERATOR | | 1 | | | | | | 31M|00:35:45.93 | 22M| 289K| | | |
|* 35 | TABLE ACCESS BY INDEX ROWID| OE_ORDER_LINES_ALL | 195 | 3736K| 217M| | 8750K (1)| 00:05:42 | 31M|00:35:50.09 | 22M| 289K| | | |
|* 36 | INDEX RANGE SCAN | OE_ORDER_LINES_X101 | 195 | 28M| | | 77180 (1)| 00:00:04 | 41M|00:01:46.97 | 113K| 79464 | | | |
|* 37 | INDEX RANGE SCAN | MTL_ITEM_CATEGORIES_U1 | 535K| 1 | 22 | | 3 (0)| 00:00:01 | 532K|00:00:18.45 | 1590K| 6970 | | | |
| 38 | TABLE ACCESS BY INDEX ROWID | MTL_CATEGORIES_B | 535K| 1 | 40 | | 2 (0)| 00:00:01 | 532K|00:00:01.18 | 873K| 0 | | | |
|* 39 | INDEX UNIQUE SCAN | MTL_CATEGORIES_B_U1 | 535K| 1 | | | 1 (0)| 00:00:01 | 532K|00:00:00.63 | 341K| 0 | | | |
| 40 | TABLE ACCESS BY INDEX ROWID | PO_REQUISITION_LINES_ALL | 447K| 1 | 16 | | 3 (0)| 00:00:01 | 3264 |00:00:01.12 | 205K| 11 | | | |
|* 41 | INDEX RANGE SCAN | PO_REQUISITION_LINES_ALL_X3 | 447K| 1 | | | 2 (0)| 00:00:01 | 3264 |00:00:00.99 | 201K| 9 | | | |
| 42 | TABLE ACCESS BY INDEX ROWID | PO_LINE_LOCATIONS_ALL | 448K| 1 | 34 | | 2 (0)| 00:00:01 | 2936 |00:00:00.38 | 8724 | 12 | | | |
|* 43 | INDEX UNIQUE SCAN | PO_LINE_LOCATIONS_U1 | 448K| 1 | | | 1 (0)| 00:00:01 | 2936 |00:00:00.16 | 4986 | 0 | | | |
| 44 | TABLE ACCESS BY INDEX ROWID | PO_LINES_ALL | 448K| 1 | 37 | | 2 (0)| 00:00:01 | 2936 |00:00:00.52 | 9324 | 165 | | | |
|* 45 | INDEX UNIQUE SCAN | PO_LINES_U1 | 448K| 1 | | | 1 (0)| 00:00:01 | 2936 |00:00:00.14 | 4844 | 1 | | | |
|* 46 | TABLE ACCESS BY INDEX ROWID | PO_HEADERS_ALL | 448K| 1 | 55 | | 2 (0)| 00:00:01 | 2936 |00:00:00.26 | 7659 | 22 | | | |
|* 47 | INDEX UNIQUE SCAN | PO_HEADERS_U1 | 448K| 1 | | | 1 (0)| 00:00:01 | 2936 |00:00:00.13 | 4674 | 0 | | | |
| 48 | TABLE ACCESS BY INDEX ROWID | AP_SUPPLIERS | 448K| 1 | 30 | | 1 (0)| 00:00:01 | 2936 |00:00:00.22 | 4204 | 0 | | | |
|* 49 | INDEX UNIQUE SCAN | AP_SUPPLIERS_U1 | 448K| 1 | | | 0 (0)| | 2936 |00:00:00.11 | 1268 | 0 | | | |
| 50 | TABLE ACCESS BY INDEX ROWID | MTL_SYSTEM_ITEMS_B | 2936 | 1 | 13 | | 3 (0)| 00:00:01 | 2936 |00:00:00.13 | 7421 | 1 | | | |
|* 51 | INDEX UNIQUE SCAN | MTL_SYSTEM_ITEMS_B_U1 | 2936 | 1 | | | 2 (0)| 00:00:01 | 2936 |00:00:00.03 | 5908 | 0 | | | |
| 52 | TABLE ACCESS BY INDEX ROWID | GL_DAILY_RATES | 2936 | 1 | 45 | | 24 (0)| 00:00:01 | 2506 |00:00:06.66 | 326K| 0 | | | |
|* 53 | INDEX RANGE SCAN | GL_DAILY_RATES_U1 | 2936 | 1 | | | 23 (0)| 00:00:01 | 2506 |00:00:06.89 | 325K| 0 | | | |
| 54 | TABLE ACCESS BY INDEX ROWID | PER_ALL_PEOPLE_F | 2936 | 10 | 240 | | 12 (0)| 00:00:01 | 3197 |00:00:00.02 | 3187 | 2 | | | |
|* 55 | INDEX RANGE SCAN | XXPO_PER_ALL_PEOPLE_F_N1 | 2936 | 10 | | | 2 (0)| 00:00:01 | 3197 |00:00:00.01 | 578 | 0 | | | |
| 56 | TABLE ACCESS BY INDEX ROWID | PER_ALL_PEOPLE_F | 5081 | 1 | 24 | | 12 (0)| 00:00:01 | 15096 |00:00:00.02 | 15414 | 5 | | | |
|* 57 | INDEX RANGE SCAN | XXPO_PER_ALL_PEOPLE_F_N1 | 5081 | 10 | | | 2 (0)| 00:00:01 | 15096 |00:00:00.01 | 970 | 0 | | | |
|* 58 | INDEX UNIQUE SCAN | AP_SUPPLIER_SITES_U1 | 16980 | 1 | 6 | | 1 (0)| 00:00:01 | 16980 |00:00:00.02 | 2609 | 0 | | | |
| 59 | TABLE ACCESS BY INDEX ROWID | AP_SUPPLIER_SITES_ALL | 16980 | 1 | 16 | | 2 (0)| 00:00:01 | 16980 |00:00:00.03 | 20748 | 0 | | | |
|* 60 | INDEX UNIQUE SCAN | AP_SUPPLIER_SITES_U1 | 16980 | 1 | | | 1 (0)| 00:00:01 | 16980 |00:00:00.01 | 2609 | 0 | | | |
CodePudding user response：
when i comment out row_number and order by it throw data very quickly.i don't know how to get rid of that