Menu
I have a query which is never finishing (left running for over 24 hours and was still going).
Now there isn't a huge amount of data in each table so I can only assume it is the efficiency of the query I have written.
SELECT DISTINCT s.supplier_id
FROM supplier_info s
INNER JOIN purchase_order_line_all po ON s.supplier_id = po.vendor_no
INNER JOIN purchase_req_line_all pr ON s.supplier_id = pr.vendor_no
INNER JOIN man_supp_invoice m ON s.supplier_id = m.IDENTITY
WHERE s.creation_date >= TRUNC(SYSDATE) - INTERVAL '6' MONTH
OR po.state NOT IN ('Closed', 'Cancelled')
OR pr.state NOT IN ('PO Created', 'Cancelled')
OR m.invoice_date >= TRUNC(SYSDATE) - INTERVAL '18' MONTH
Execution Plan
Plan hash value: 2195330353
-------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1884 | 318K| | 112K (1)| 00:30:34 |
| 1 | HASH UNIQUE | | 1884 | 318K| 1299M| 112K (1)| 00:30:34 |
|* 2 | HASH JOIN | | 7484K| 1234M| | 2474 (8)| 00:00:41 |
| 3 | INDEX FULL SCAN | PURCHASE_REQUISITION_PK | 45348 | 265K| | 18 (0)| 00:00:01 |
|* 4 | HASH JOIN RIGHT OUTER | | 7484K| 1191M| | 2410 (6)| 00:00:40 |
| 5 | INDEX FULL SCAN | PUR_ORD_LINE_EXT_PK | 1 | 16 | | 1 (0)| 00:00:01 |
|* 6 | HASH JOIN | | 7484K| 1077M| 3160K| 2364 (4)| 00:00:39 |
| 7 | VIEW | index$_join$_013 | 92445 | 2076K| | 351 (2)| 00:00:06 |
|* 8 | HASH JOIN | | | | | | |
|* 9 | HASH JOIN | | | | | | |
| 10 | INDEX FAST FULL SCAN | PURCHASE_REQ_LINE2_IX | 92445 | 2076K| | 40 (0)| 00:00:01 |
| 11 | INDEX FAST FULL SCAN | PURCHASE_REQ_LINE1_IX | 92445 | 2076K| | 71 (0)| 00:00:02 |
| 12 | INDEX FAST FULL SCAN | PURCHASE_REQ_LINE_PK | 92445 | 2076K| | 57 (0)| 00:00:01 |
|* 13 | HASH JOIN | | 387K| 47M| 2984K| 1139 (2)| 00:00:19 |
| 14 | VIEW | index$_join$_015 | 92589 | 1898K| | 184 (2)| 00:00:04 |
|* 15 | HASH JOIN | | | | | | |
| 16 | INDEX FAST FULL SCAN | PURCHASE_ORDER_LINE_PK | 92589 | 1898K| | 57 (0)| 00:00:01 |
| 17 | INDEX FAST FULL SCAN | PURCHASE_ORDER_LINE_1_IX | 92589 | 1898K| | 64 (2)| 00:00:02 |
|* 18 | HASH JOIN | | 172K| 17M| 1008K| 619 (2)| 00:00:11 |
| 19 | VIEW | index$_join$_016 | 41115 | 521K| | 58 (2)| 00:00:01 |
|* 20 | HASH JOIN | | | | | | |
| 21 | INDEX FAST FULL SCAN | PURCHASE_ORDER2_IX | 41115 | 521K| | 17 (0)| 00:00:01 |
| 22 | INDEX FAST FULL SCAN | PURCHASE_ORDER_PK | 41115 | 521K| | 13 (0)| 00:00:01 |
|* 23 | HASH JOIN | | 13700 | 1257K| | 523 (1)| 00:00:09 |
| 24 | TABLE ACCESS FULL | SUPPLIER_INFO_TAB | 3269 | 45766 | | 10 (0)| 00:00:01 |
| 25 | NESTED LOOPS | | | | | | |
| 26 | NESTED LOOPS | | 23568 | 1841K| | 512 (1)| 00:00:09 |
| 27 | SORT UNIQUE | | 4 | 76 | | 1 (0)| 00:00:01 |
|* 28 | INDEX RANGE SCAN | USER_PROFILE_ENTRY_SYS_PK | 4 | 76 | | 1 (0)| 00:00:01 |
|* 29 | INDEX RANGE SCAN | INVOICE_IND9 | 15928 | | | 6 (0)| 00:00:01 |
|* 30 | TABLE ACCESS BY INDEX ROWID| INVOICE_TAB | 6246 | 372K| | 255 (0)| 00:00:05 |
-------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
2 - access("A"."REQUISITION_NO"="B"."REQUISITION_NO")
4 - access("POL"."ORDER_NO"="POLET"."ORDER_NO"(+) AND "POL"."LINE_NO"="POLET"."LINE_NO"(+) AND
"POL"."RELEASE_NO"="POLET"."RELEASE_NO"(+) AND "POL"."ORDER_NO"="POLET"."ORDER_NO"(+))
6 - access("SUPPLIER_ID"="A"."VENDOR_NO")
filter("CREATION_DATE">=TRUNC(SYSDATE@!)-INTERVAL'+00-06' YEAR(2) TO MONTH OR
"PURCHASE_ORDER_LINE_API"."FINITE_STATE_DECODE__"("POL"."ROWSTATE")<>'Closed' AND
"PURCHASE_ORDER_LINE_API"."FINITE_STATE_DECODE__"("POL"."ROWSTATE")<>'Cancelled' OR
"PURCHASE_REQ_LINE_API"."FINITE_STATE_DECODE__"("A"."ROWSTATE")<>'PO Created' AND
"PURCHASE_REQ_LINE_API"."FINITE_STATE_DECODE__"("A"."ROWSTATE")<>'Cancelled' OR
"I"."INVOICE_DATE">=TRUNC(SYSDATE@!)-INTERVAL'+01-06' YEAR(2) TO MONTH)
8 - access(ROWID=ROWID)
9 - access(ROWID=ROWID)
13 - access("POL"."ORDER_NO"="PO"."ORDER_NO")
15 - access(ROWID=ROWID)
18 - access("SUPPLIER_ID"="PO"."VENDOR_NO")
20 - access(ROWID=ROWID)
23 - access("SUPPLIER_ID"="I"."IDENTITY")
28 - access("USER_NAME"=NVL(RTRIM(SUBSTR(USERENV('CLIENT_INFO'),1,30)),USER@!) AND "ENTRY_CODE"='COMPANY')
29 - access("I"."COMPANY"="ENTRY_VALUE")
30 - filter("I"."CREATOR"='MAN_SUPP_INVOICE_API' AND "I"."PARTY_TYPE"='SUPPLIER' AND
"I"."ROWSTATE"<>'Cancelled')
767
SQL performance is only as good as the execution plan, which is only as good as the statistics the optimizer uses. Oracle query optimization depends on up-to-date statistics that the database collects automatically. If you prefer to intervene manually, you can also use DBMS_STATS to gather optimizer statistics and system statistics. 3.
Approaches to SQL Statement Tuning This section describes five ways you can improve SQL statement efficiency: Restructuring the Indexes Restructuring the Statement Modifying or Disabling Triggers Restructuring the Data Keeping Statistics Current and Using Plan Stability to Preserve Execution Plans Note:
Another factor in Oracle query optimization is the role that it plays in your job description. Are you going to check database performance regularly and look for ways to write more efficient SQL statements? Or are you going to lurch from crisis to crisis, making time for optimization only when database performance problems arise?
With the cost-based optimizer, performance of distributed queries is generally satisfactory. Only in rare occasions is it necessary to change SQL statements, create views, or use procedural code. Use views. In some situations, views can be used to improve performance of distributed queries. For example:
You have a bunch of OR conditions. I would suggest replacing these by not exists:
SELECT s.supplier_id
FROM supplier_info s
WHERE s.creation_date >= TRUNC(SYSDATE) - INTERVAL '6' MONTH OR
NOT EXISTS (SELECT 1
FROM purchase_order_line_all po
WHERE s.supplier_id = po.vendor_no AND
po.state IN ('Closed', 'Cancelled')
) AND
NOT EXISTS (SELECT 1
FROM purchase_req_line_all pr
WHERE s.supplier_id = pr.vendor_no AND
r.state IN ('PO Created', 'Cancelled')
)
EXISTS (SELECT 1
FROM man_supp_invoice m
WHERE s.supplier_id = m.IDENTITY AND
m.invoice_date >= TRUNC(SYSDATE) - INTERVAL '18' MONTH
);
I'm pretty sure your performance problem is caused by cartesian products. If a supplier has 100 order lines, and 100 req lines, and 100 invoices, then the join is creating 100*100*100 = 1,000,000 rows just for that one supplier. This is a big intermediate table.
By using EXISTS instead, Oracle will not be producing gargantuan intermediate tables.
Also, you can test the performance by adding one clause at a time.
Finally, I'm not 100% sure if the logic is correct for the middle two conditions. For instance, you might really want this for first NOT EXISTS:
EXISTS (SELECT 1
FROM purchase_order_line_all po
WHERE s.supplier_id = po.vendor_no AND
po.state NOT IN ('Closed', 'Cancelled')
) AND
As written, your logic is that at least one state is not 'Closed' or 'Cancelled', which is what the above revision also does. I put in that no states are 'Closed' or 'Cancelled', just because that made more sense to me.
110
It looks like you are working with IFS.
In the view PURCHASE_ORDER_LINE_ALL the columns objstate and state are defined in the DDL as:
pol.rowstate objstate
PURCHASE_ORDER_LINE_API.Finite_State_Decode__(pol.rowstate) state
I inherited some code that was using state in the query and it was occasionally timing out. When I changed the query to use objstate it ran much quicker. That trip through the Finite_State_Decode__ function was adding a lot of overhead to my query.
A little bit of background reading on why functions like this exist in the system: https://yourifs.blogspot.com/2007/10/read-only-methods-pragma-methods.html
1
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
