Home » RDBMS Server » Performance Tuning » Query Taking Alot of time ..Help in Tuning (11g)
Query Taking Alot of time ..Help in Tuning [message #674702] Tue, 12 February 2019 00:43 Go to next message
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 #674703 is a reply to message #674702] Tue, 12 February 2019 00:47 Go to previous messageGo to next message
John Watson
Messages: 8077
Registered: January 2010
Location: Global Village
Senior Member
Why 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.
Re: Query Taking Alot of time ..Help in Tuning [message #674704 is a reply to message #674703] Tue, 12 February 2019 01:02 Go to previous messageGo to next message
rrcr
Messages: 18
Registered: January 2019
Junior Member
John Watson wrote on Tue, 12 February 2019 00:47
Why 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 #674705 is a reply to message #674704] Tue, 12 February 2019 01:26 Go to previous messageGo to next message
John Watson
Messages: 8077
Registered: January 2010
Location: Global Village
Senior Member
He query is now readable. What about the exec plan?
Re: Query Taking Alot of time ..Help in Tuning [message #674706 is a reply to message #674705] Tue, 12 February 2019 01:37 Go to previous messageGo to next message
rrcr
Messages: 18
Registered: January 2019
Junior Member
John Watson wrote on Tue, 12 February 2019 01:26
He query is now readable. What about the exec plan?

Dont know how to foramt Explain plan. please help me in formatting.
Re: Query Taking Alot of time ..Help in Tuning [message #675766 is a reply to message #674706] Wed, 17 April 2019 21:28 Go to previous messageGo to next message
trantuananh24hg
Messages: 741
Registered: January 2007
Location: Ha Noi, Viet Nam
Senior Member
rrcr wrote on Tue, 12 February 2019 07:37
John Watson wrote on Tue, 12 February 2019 01:26
He 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

Re: Query Taking Alot of time ..Help in Tuning [message #675771 is a reply to message #675766] Thu, 18 April 2019 03:32 Go to previous message
cookiemonster
Messages: 13757
Registered: September 2008
Location: Rainy Manchester
Senior Member
I'd copy and paste into notepad or other similar text editor rather than word.
You know this thread is two months old?
Previous Topic: Oracle CPU usage Analysis
Next Topic: poor performance during switch log file
Goto Forum:
  


Current Time: Wed Dec 11 00:04:39 CST 2019