Sql with Connect By is Slow

From: Krishnaprasad Yadav <chrishna0007_at_gmail.com>
Date: Fri, 18 Feb 2022 16:44:46 +0530
Message-ID: <CAO8FHeX2Qb2d5Rjjcf0+E4Yp33tBapqrazeks+q_jVpvWJiQEw_at_mail.gmail.com>

Dear Experts ,

we are facing slowness in below sql text , any way to tune the query as it is using ' CONNECT BY '.

SELECT PREFIX
FROM TEST1
WHERE LIMIT_C = :B1
AND LEVEL = 1
CONNECT BY NOCYCLE PRIOR LIMIT_C =LIMIT_P Executoin Plan:

---

---

| Id  | Operation                     | Name             | Rows  | Bytes |
Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                  |       |       |
 9914 (100)|          |
|*  1 |  FILTER                       |                  |       |       |
           |          |
|*  2 |   CONNECT BY WITHOUT FILTERING|                  |       |       |
           |          |
|   3 |    TABLE ACCESS FULL          | TEST1            |  1538K|    42M|

 9914 (1)| 00:00:01 |

---

Predicate Information (identified by operation id):

---

   1 - filter(("LIMIT_C"=:B1 AND LEVEL=1))    2 - access("LIMIT_P"=PRIOR NULL)

Global Stats

---

| Elapsed | Cpu | IO | Concurrency | Other | Buffer | Read | Read | Write | Write |
| Time(s) | Time(s) | Waits(s) | Waits(s) | Waits(s) | Gets | Reqs | Bytes | Reqs | Bytes |

---

| 94 | 16 | 27 | 0.00 | 51 | 26M | 41898 |   1GB | 1079 | 208MB |

---

SQL Plan Monitoring Details

---

---

| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Read | Read | Write | Write | Mem | Temp | Activity |

   Activity Detail       | Progress |
|      |                                 |                  | (Estim) |
 | Active(s) | Active |       | (Actual) | Reqs  | Bytes | Reqs  | Bytes |
      |       |   (%)    |
     (# samples)         |          |


====================================================================================================================================================================================


=====================================


| -> 0 | SELECT STATEMENT                |                  |         |
 |        55 |    +44 |     1 |        1 |       |       |       |       |
    . |     . |          |
                         |          |
| -> 1 |   FILTER                        |                  |         |
 |        55 |    +44 |     1 |        1 |       |       |       |       |
    . |     . |          |
                         |          |
| -> 2 |    CONNECT BY WITHOUT FILTERING |                  |         |
 |        98 |     +1 |     1 |       1M | 46178 | 507MB |  1079 | 208MB |
117KB | 108MB |    94.12 | Cp
u (59)                   |     256% |
|      |                                 |                  |         |
 |           |        |       |          |       |       |       |       |
      |       |          | di
rect path read temp (21) |          |
|    3 |     TABLE ACCESS FULL           | TEST1            |      2M |
9914 |        21 |     +2 |     1 |       3M |   831 | 800MB |       |
  |     . |     . |     5.88 | Cp
u (2)                    |     100% |
|      |                                 |                  |         |
 |           |        |       |          |       |       |       |       |
      |       |          | db
 file scattered read (3) |          |


====================================================================================================================================================================================


=====================================

Regards,
Krishna

--
http://www.freelists.org/webpage/oracle-l

Received on Fri Feb 18 2022 - 12:14:46 CET