Improving query performance further

From: yudhi s <learnerdatabase99_at_gmail.com>
Date: Wed, 12 Oct 2022 02:34:28 +0530
Message-ID: <CAEzWdqf_r+MM-JY1xj+YpdPqHWHiDT_2-+A8crjC5a7cVRtkDw_at_mail.gmail.com>

Hello Listers, We have a customer database on Oracle version 19C. We have a simple query as below. and as per current design is executing ~200 to 300 times per second and it's part of a bigger process and thus is one of the top consumers in that. Now as we are working to change the design to make the number of execution of this query lesser to help the process. But that needs much more impact analysis, so we were thinking of any possible easy way to make the individual execution of this query faster? Or say any structural change(new index etc.) which can further drop the IO/CPU requirement for individual execution of this query?

Currently this query is accessing table TABLE1 through a primary key which is on three columns (PART_COL,MIN_VALUE,MAX_VAL). The table is partitioned on column PART_COL. This table contains ~400K rows and is ~100MB in size. It's a master data kind of table.

SELECT column1 FROM TABLE1 WHERE PART_COL = :B2 AND :B1 BETWEEN MIN_VAL AND MAX_VALUE Global Information

---

 Status       : DONE (ALL ROWS)
 Instance ID     : 1

 SQL Execution ID : 16777216

 Execution Started  : 10/11/2022 09:36:48
 First Refresh Time : 10/11/2022 09:36:48
 Last Refresh Time  : 10/11/2022 09:36:48
 Duration      : .06173s
 Module/Action    : SQL*Plus/-
 Program       : sqlplus.exe
 Fetch Calls     : 1

Binds

---

| Name | Position | Type | Value |

---

| :B2 | 1 | NUMBER | 2 |
| :B1 | 2 | VARCHAR2(4000) | XXXXXXXXXXX |

---

Global Stats

---

| Elapsed | Cpu | IO | Concurrency | Cluster | Fetch | Buffer | Read |
Read |
| Time(s) | Time(s) | Waits(s) | Waits(s) | Waits(s) | Calls | Gets | Reqs
| Bytes |

---

| 0.06 | 0.04 | 0.02 | 0.00 | 0.00 | 1 | 911 | 778 | 6MB |

---

SQL Plan Monitoring Details (Plan Hash Value=692467662)

---

| Id | Operation | Name | Rows | Cost | Time
 | Start | Execs | Rows | Read | Read | Activity | Activity Detail |
| | | | (Estim) | | Active(s) |
Active | | (Actual) | Reqs | Bytes | (%) | (# samples) |

---

| 0 | SELECT STATEMENT | | | | | |

  1 |     |   |    |     |         |


| 1 |  PARTITION RANGE SINGLE           |              |  10610 | 928 |


 |    |   1 |     |   |    |     |         |


| 2 |  TABLE ACCESS BY LOCAL INDEX ROWID BATCHED | TABLE1          |  10610

| 928 |      |    |   1 |     |   |    |     |         |


| 3 |   INDEX RANGE SCAN             | PK_TABLE1         |  10610 | 771 |


   |    |   1 |     | 770 |  6MB |     |         |
======================================================================================================================================================================================

Predicate Information (identified by operation id):

---

  3 - access("PART_COL"=TO_NUMBER(:B2) AND "MAX_VALUE">=:B1 AND "MIN_VAL"<=:B1)

    filter("MAX_VALUE">=:B1)

Statistics

---

     37 recursive calls
     0 db block gets

    911 consistent gets
    778 physical reads
   41076 redo size
    260 bytes sent via SQL*Net to client     489 bytes received via SQL*Net from client

     1 SQL*Net roundtrips to/from client
     28 sorts (memory)
     0 sorts (disk)
     0 rows processed

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

Received on Tue Oct 11 2022 - 23:04:28 CEST