Re: Improving query performance further

Hi Yudhi,

> :B1 BETWEEN MIN_VAL AND MAX_VALUE
That's a well-known problem of such range searches. Could you provide a bit more details:
What is the nature of those MIN_VAL/MAX_VAL? and some statistics:

select
  min(max_val - min_val) as m0

 ,median(max_val - min_val) as m2
 ,max(max_val - min_val) as m1
 ,min(MIN_VAL) m3, max(MIN_VAL ) as m7
 ,min(MAX_VAL) m5, max(MAX_VAL ) as m8

On Tue, Oct 11, 2022 at 10:04 PM yudhi s <learnerdatabase99_at_gmail.com> wrote:

> 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
>

-- 
Best regards,
Sayan Malakshinov
Oracle performance tuning engineer
Oracle ACE
http://orasql.org

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