Re: Improving query performance further

From: Sayan Malakshinov <xt.and.r_at_gmail.com>
Date: Wed, 12 Oct 2022 10:37:15 +0100
Message-ID: <CAOVevU6JA9xu+YK_ZiZYOX-ZKXY4u4isLwA481PjitP3h8XJAQ_at_mail.gmail.com>

Hi Yudhi,

So your case looks like my 1.1. Try this query to prove it:

select 1
from table1 t1, table1 t2
where t1.part_col=t2.part_col

and t1.min_val < t2.min_val
and t1.max_val > t2.min_val
and t2.max_val > t1.min_val

and rownum=1;

If this query returns no rows, you can rewrite your query like this:

select *
from
  (select *
   from

      (select *
       from TABLE1
       where PART_COL=:PART_COL
         and MIN_VAL <= :X
       order by MIN_VAL desc
      ) -- to force IRS DESCENDING

   where rownum=1) -- and stop after 1 row where
  MAX_VAL >= :X -- our second predicate

Just test it and show execution statistics. Or if it goes to 1.2 (ie intersected intervals), please provide your oracle version, so I could show other variants.

On Wed, Oct 12, 2022 at 10:01 AM Jonathan Lewis <jlewisoracle_at_gmail.com> wrote:

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

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

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

Received on Wed Oct 12 2022 - 11:37:15 CEST