| Query Taking Alot of time ..Help in Tuning [message #674702] |
Tue, 12 February 2019 00:43  |
 |
rrcr
Messages: 18
Registered: January 2019
|
Junior Member |
|
|
Hi
I have the following query.
The requirement is to identify distinct ORDER_ID from table A based on Data from C and the joining conditions to join A and C.
I have attached execution plan too. Can you please suggest a solution.
Cant alter DDLs of A,B,C,D.
SELECT DISTINCT A.ORDER_ID,1,0,1,0,TO_DATE(SYSDATE)
FROM A,B,C,D
WHERE A.ORDER_ID=D.ORDER_ID
AND D.ORDER_ID=B.ORDER_ID
AND B.TRADE_ORDER_ID=C.col
AND C.col IN (13,14)
AND C.C_TYPE IN (1,2,92)
AND C.C_DT < ADD_MONTHS(SYSDATE, -40)
AND NOT EXISTS ( SELECT 1 FROM E WHERE C1='ABC' AND C2='XYZ' AND C3.=A.ORDER_ID);
------------------------------------------------------------------------------------------------------------------------------------- -----------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------------------------- -----------
| 0 | SELECT STATEMENT | | 3513K| 2063M| | 1560K (1)| 05:12:02 | | |
| 1 | HASH UNIQUE | | 3513K| 2063M| 2111M| 1560K (1)| 05:12:02 | | |
|* 2 | HASH JOIN RIGHT ANTI | | 3513K| 2063M| 10M| 1103K (1)| 03:40:43 | | |
|* 3 | TABLE ACCESS STORAGE FULL | E | 18550 | 9M| | 329K (1)| 01:05:50 | | |
|* 4 | HASH JOIN | | 3513K| 201M| 90M| 763K (1)| 02:32:40 | | |
|* 5 | HASH JOIN | | 1604K| 71M| 79M| 635K (1)| 02:07:02 | | |
|* 6 | HASH JOIN | | 1600K| 61M| 31M| 619K (1)| 02:03:53 | | |
| 7 | PARTITION LIST ALL | | 1045K| 19M| | 557K (1)| 01:51:30 | 1 | 7 |
| 8 | PARTITION LIST ALL | | 1045K| 19M| | 557K (1)| 01:51:30 | 1 | LAST |
| 9 | INLIST ITERATOR | | | | | | | | |
|* 10 | TABLE ACCESS BY LOCAL INDEX ROWID| C | 1045K| 19M| | 557K (1)| 01:51:30 | 1 | 39 |
|* 11 | INDEX RANGE SCAN | C_IX13 | 4073K| | | 12594 (1)| 00:02:32 | 1 | 39 |
| 12 | TABLE ACCESS STORAGE FULL | B | 20M| 386M| | 32956 (1)| 00:06:36 | | |
| 13 | INDEX STORAGE FAST FULL SCAN | D_PK | 9365K| 62M| | 4631 (1)| 00:00:56 | | |
| 14 | PARTITION LIST ALL | | 20M| 253M| | 102K (1)| 00:20:29 | 1 | 7 |
| 15 | PARTITION LIST ALL | | 20M| 253M| | 102K (1)| 00:20:29 | 1 | LAST |
| 16 | TABLE ACCESS STORAGE FULL | A | 20M| 253M| | 102K (1)| 00:20:29 | 1 | 39 |
------------------------------------------------------------------------------------------------------------------------------------- -----------
|
|
|
|
|
|
| Re: Query Taking Alot of time ..Help in Tuning [message #674704 is a reply to message #674703] |
Tue, 12 February 2019 01:02  |
|
rrcr
Messages: 18
Registered: January 2019
|
Junior Member |
|
|
John Watson wrote on Tue, 12 February 2019 00:47Why do you persist in ignoring our forum guidelines?
Your code and exec plan is unreadable. Format it, stop acting like some little kid and use [code] tags.
My Apologies.. Dont know exactly how to use tags.. i have used instant formatting tool.
SELECT DISTINCT a.order_id,
1,
0,
1,
0,
To_date(SYSDATE)
FROM a,
b,
c,
d
WHERE a.order_id = d.order_id
AND d.order_id = b.order_id
AND b.trade_order_id = c.col
AND c.col IN ( 13, 14 )
AND c.c_type IN ( 1, 2, 92 )
AND c.c_dt < Add_months(SYSDATE, -40)
AND NOT EXISTS (SELECT 1
FROM e
WHERE c1 = 'ABC'
AND c2 = 'XYZ'
AND c3 .= a.order_id);
------------------------------------------------------------------------------------------------------------------------------------- -----------
| id | operation | name | ROWS | bytes |tempspc| cost (%cpu)| TIME | pstart| pstop |
------------------------------------------------------------------------------------------------------------------------------------- -----------
| 0 |
SELECT STATEMENT | | 3513k| 2063m| | 1560k (1)| 05:12:02 | | | | 1 | hash UNIQUE | | 3513k| 2063m| 2111m| 1560k (1)| 05:12:02 | | | | * 2 | hash
join right anti | | 3513k| 2063m| 10m| 1103k (1)| 03:40:43 | | | | * 3 | TABLE ACCESS STORAGE full | e | 18550 | 9m| | 329k (1)| 01:05:50 | | | |* 4 | hash
join | | 3513k| 201m| 90m| 763k (1)| 02:32:40 | | | | * 5 | hash
join | | 1604k| 71m| 79m| 635k (1)| 02:07:02 | | | | * 6 | hash
join | | 1600k| 61m| 31m| 619k (1)| 02:03:53 | | | | 7 | PARTITION list ALL | | 1045k| 19m| | 557k (1)| 01:51:30 | 1 | 7 | | 8 | PARTITION list ALL | | 1045k| 19m| | 557k (1)| 01:51:30 | 1 | last | | 9 | inlist iterator | | | | | | | | | |* 10 | TABLE ACCESS BY local INDEX ROWID| c | 1045k| 19m| | 557k (1)| 01:51:30 | 1 | 39 | |* 11 | INDEX RANGE scan | c_ix13 | 4073k| | | 12594 (1)| 00:02:32 | 1 | 39 | | 12 | TABLE ACCESS STORAGE full | b | 20m| 386m| | 32956 (1)| 00:06:36 | | | | 13 | INDEX STORAGE fast full scan | d_pk | 9365k| 62m| | 4631 (1)| 00:00:56 | | | | 14 | PARTITION list ALL | | 20m| 253m| | 102k (1)| 00:20:29 | 1 | 7 | | 15 | PARTITION list ALL | | 20m| 253m| | 102k (1)| 00:20:29 | 1 | last | | 16 | TABLE ACCESS STORAGE full | a | 20m| 253m| | 102k (1)| 00:20:29 | 1 | 39 |
------------------------------------------------------------------------------------------------------------------------------------- -----------
[Updated on: Tue, 12 February 2019 01:05] Report message to a moderator |
|
|
|
|
|
|
|
| Re: Query Taking Alot of time ..Help in Tuning [message #675766 is a reply to message #674706] |
Wed, 17 April 2019 21:28 |
trantuananh24hg
Messages: 744
Registered: January 2007
Location: Ha Noi, Viet Nam
|
Senior Member |
|
|
rrcr wrote on Tue, 12 February 2019 07:37John Watson wrote on Tue, 12 February 2019 01:26He query is now readable. What about the exec plan?
Dont know how to foramt Explain plan. please help me in formatting.
Use SQL*Plus
- set linesize 150
- set autotrace ...
Example:
------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 589 (100)| |
| 1 | SORT ORDER BY | | 1 | 67 | 589 (1)| 00:00:08 |
| 2 | NESTED LOOPS | | 1 | 67 | 588 (1)| 00:00:08 |
| 3 | NESTED LOOPS | | 1 | 67 | 588 (1)| 00:00:08 |
| 4 | HASH JOIN | | 1 | 50 | 587 (1)| 00:00:08 |
| 5 | NESTED LOOPS | | 1 | 28 | 3 (0)| 00:00:01 |
| 6 | NESTED LOOPS | | 1 | 28 | 3 (0)| 00:00:01 |
| 7 | TABLE ACCESS BY INDEX ROWID| SHORTCODE_LIST | 1 | 14 | 2 (0)| 00:00:01 |
| 8 | INDEX RANGE SCAN | SHORTCODE_LIST_IDX1 | 1 | | 1 (0)| 00:00:01 |
| 9 | INDEX RANGE SCAN | INDX_SHCODECP_SHCODEID | 1 | | 0 (0)| |
| 10 | TABLE ACCESS BY INDEX ROWID | SHORTCODE_CP | 1 | 14 | 1 (0)| 00:00:01 |
| 11 | TABLE ACCESS FULL | CMDCODE_LIST | 4 | 88 | 584 (1)| 00:00:08 |
| 12 | INDEX UNIQUE SCAN | CP_LIST_PK | 1 | | 0 (0)| |
| 13 | TABLE ACCESS BY INDEX ROWID | CP_LIST | 1 | 17 | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------
Copy & paste the explain plan into word, and then, use CourierNew to format (I usually do as).
Additional:
- Extract DDL or select metadata to find which Primary Key or any constraints. The better way is SQL*Developer.
- Describe table's structure, including data_type of every columns.
- Which type of partition? Hash, List, Composite or anything else? In your result, I saw the LIST, but LIST of? How many values in LIST?
- Indexes description of A, B, C, D.
- Which columns does impact DDL often?
[Updated on: Wed, 17 April 2019 21:31] Report message to a moderator |
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
