Reputation: 11
ROW_NUMBER OVER Partition order by is causing performance issue
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
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 | | | |
Upvotes: 1
Views: 1529
Answers (1)
Reputation: 36807
ROW_NUMBER is increasing the amount of time to return the first N rows, but it probably isn't affecting the time to return all rows. Instead of worrying about ROW_NUMBER, you should look at the predicates on OE_ORDER_LINES_ALL and find some combination of indexes or partitioning that can more selectively filter out the records.
Operations like ROW_NUMBER or an ORDER BY clause require Oracle to process all the data at once before returning a single row. Without those operations, Oracle can process the data in smaller chunks, and can return a small number of rows before the entire result is processed. This First-N processing will often appear to make the query look faster, as your IDE will get the first 100 rows quickly. But if you try to retrieve the whole result set, you'll see that the total run time is about the same. (However, in some applications, getting the first N rows is all you need to display a page of information. If you really only care about the first N rows, let us know.)
If you look at the execution plan, you can see that operations 34, 35, and 36 are responsible for almost all of the processing time. The actual time ("A-Time") of those operations is about 36 of the 37 minutes of run time. This query all boils down to retrieving rows efficiently from OE_ORDER_LINES_ALL. Unfortunately, the estimated rows ("E-Rows") and the actual rows ("A-Rows") are very close. (For the optimizer, estimating 3.7M and retrieving 22M is actually a very good estimate.) And even the estimated time, 6 minutes, is pretty close to the actual time, 37 minutes.
Those close estimates mean that Oracle is probably doing the best that it can with the query, tables, indexes, and partitions that it has. The index OE_ORDER_LINES_X101 just isn't very efficient, and Oracle knows that it's not efficient, which means there's probably not a better access path available.
You need to look at all of the predicates on OE_ORDER_LINES_ALL and find some combination of them that would more quickly allow Oracle to filter out a large number of rows. Maybe you need to add an index for some selective predicate, or add a partition for some semi-selective predicate with a low number of distinct values (maybe the ITEM_TYPE_CODE), or modify the predicates to use an existing index or partition.
It may be a lot of work to create the right data structure and ensure that Oracle uses it. But you're on the right path by generating the actual execution plans and numbers. Focus on the slowest operations, and keep comparing the estimates and actual numbers to check that Oracle is filtering data as efficiently as possible.
Or possibly there is no simple, efficient way to reduce the 40 million rows to 3 thousand, and it would be somewhat faster to read the whole table than to use a poor index. Perhaps a hint like /*+ FULL(OOL) */ would improve performance.
Upvotes: 1
Related Questions
- ROW_NUMBER with partition by returns result with duplicate rows
- Query Optimization with ROW_NUMBER
- How to order when using partition by clause
- Optimize the Performance of ROW_NUMBER() OVER (PARTITION BY..)
- How to obtain the latest record using PARTITION BY in a performant way in SQL?
- ROW_NUMBER() OVER PARTITION optimization
- Optimize Oracle order by query
- Oracle SQL efficient query on Partitioned Table: "order by case"
- Using Row_number over partition by in SQL with Partitioning Feature Disabled
- Improve performance with index for row_number over partition query