Re: Improving query performance further

There are a couple of copying or obfuscation errors in the text you've posted.
Assuming the Monitor report is for the execution that produced the autotrace statistics the key point is:
Monitor: 770 disk blocks read in the index range scan Autotrace: 778 disk blocks read in total.

So all the work is done in a very large index range scan. There are two possibilities for simple improvement (a) reverse the order of max_val and min_val in the index (b) recreate the index with compression on the first column or first two columns if the 2nd column has more than a couple of rows per value.

(a) may help (part_col, max_val, min_val) if the typical value supplied for :B1 produces a relatively small number of rows from the max_val predicate that then have to be filtered by the min_val. At present you MAY happen to be using :B1 values that produce a large number of rows that have to be filter by the max_val predicate.

(b) should help simply because the index will be physically smaller. Whichever order you finally use, if the 2nd column is a little repetitive try rebuilding the index with "COMPRESS 2", if it's not repetitive "COMPRESS 1" It's also worth considering whether your use of the index means the efficiency of the index constantly drops over time - you have a fairly large index for a fairly small table - are you constantly inserting and deleting rows in a way that keeps the table a fairly constant size but causes the index to grow and the existing index leaf blocks to slowly empty out ?

In your case it would be helpful to find a current representative value for :B1 and execute the query from sql*plus after declaring and setting the variable, setting statistics_level to all and using the "allstats last" format option to get a plan with execution stats.

set serveroutput off
alter session set statistics_level = all;

set linesize 180
set pagesize 40
set trimspool on
set tab off

• run the query

select * from table(dbms_xplan.display_cursor(format=>'allstats last'));

Regards
Jonathan Lewis

On Tue, 11 Oct 2022 at 22:04, 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
>

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