Re: Improving query performance further
Date: Thu, 13 Oct 2022 10:37:23 +0100
Message-ID: <CAOVevU5SJ1ofMAr63x9jxksKwoLJAr-Oz3tb7QwxhCHvaAS5Rg_at_mail.gmail.com>
Hi Yudhi,
>Wondering if this query will give any surprises if overlap range data came
to this table? And yes, its version 19.15 of oracle.
In this case you can use the approach from 1.2. I've created an extended
example of it:
full test case with plans and statistics (that script is for sqlplus):
https://gist.github.com/xtender/63acc0749f8aa7ec81d348b232cb7da8
Duplicating it here, just in case if gist.github will lose it:
with
function get_rowids(x number) return sys.odciridlist as
i int;
ret sys.odciridlist;
cursor c_1(p int) is
select
t.rowid rid
,t.b
from T
where t.A<=p
order by t.A desc;
R c_1%rowtype;
begin
ret:=sys.odciridlist();
i:=0;
open c_1(x);
<<LL>>
loop
fetch c_1 into R;
exit when c_1%notfound;
if R.B>=X then
ret.extend(1);
i:=i+1;
ret(i):=r.rid;
else
exit LL;
end if;
end loop;
return ret;
end;
select/*+ cardinality(rids 1) */
*
from table(get_rowids(:X)) rids
join t on t.rowid=rids.column_value /
On Wed, Oct 12, 2022 at 9:24 PM yudhi s <learnerdatabase99_at_gmail.com> wrote:
> Thank You so much Sayan.
>
> I tried running the below query and it gave me zero records.
>
> 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;
>
> Then I tried your suggested modified query with two different binds , one
> with rows and another without rows. It seems to me the modified query is
> doing a lot less IO and coming pretty fast as compared to the existing
> query. I am still trying to understand if this rewritten query is logically
> doing exactly the same as the existing/old query. Wondering if this query
> will give any surprises if overlap range data came to this table? And yes,
> its version 19.15 of oracle.
>
> -- Existing query with zero row output---
>
> ----------------------------------------------------------------------------------------------------------------------------------
> | Id | Operation | Name | Starts | E-Rows |
> A-Rows | A-Time | Buffers |
>
> ----------------------------------------------------------------------------------------------------------------------------------
> | 0 | SELECT STATEMENT | | 1 | | 0
> |00:00:00.01 | 37 |
> | 1 | PARTITION RANGE SINGLE | | 1 | 5124 | 0
> |00:00:00.01 | 37 |
> | 2 | TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| TABLE1 | 1 |
> 5124 | 0 |00:00:00.01 | 37 |
> |* 3 | INDEX RANGE SCAN | PK_TABLE1 | 1 | 5124 |
> 0 |00:00:00.01 | 37 |
>
> ----------------------------------------------------------------------------------------------------------------------------------
>
> Predicate Information (identified by operation id):
> ---------------------------------------------------
> 3 - access("PART_COL"=:B2 AND "MAX_VAL">=:B1 AND "MIN_VAL"<=:B1)
> filter("MAX_VAL">=:B1)
>
>
> --modified query with zero rows output------
>
> -------------------------------------------------------------------------------------------------------------------------------------
> | Id | Operation | Name | Starts | E-Rows | A-Rows
> | A-Time | Buffers | Reads |
>
> --------------------------------------------------------------------------------------------------------------------------------------
> | 0 | SELECT STATEMENT | | 1 | | 0
> |00:00:01.02 | 4 | 1 |
> |* 1 | VIEW | | 1 | 1 | 0 |00:00:01.02
> | 4 | 1 |
> |* 2 | COUNT STOPKEY | | 1 | | 1
> |00:00:01.02 | 4 | 1 |
> | 3 | VIEW | | 1 | 5351 | 1
> |00:00:01.02 | 4 | 1 |
> | 4 | PARTITION RANGE SINGLE | | 1 | 5351 | 1
> |00:00:01.02 | 4 | 1 |
> | 5 | TABLE ACCESS BY LOCAL INDEX ROWID| TABLE1 | 1 | 5351
> | 1 |00:00:01.02 | 4 | 1 |
> |* 6 | INDEX RANGE SCAN DESCENDING | PK_TABLE1 | 1 | 5351
> | 1 |00:00:00.01 | 3 | 0 |
>
> --------------------------------------------------------------------------------------------------------------------------------------
>
> Predicate Information (identified by operation id):
> ---------------------------------------------------
> 1 - filter("MAX_VAL">=:B1)
> 2 - filter(ROWNUM=1)
> 6 - access("PART_COL"=:B2 AND "MIN_VAL"<=:B1)
>
> --Existing query with one rows output------
>
>
> -----------------------------------------------------------------------------------------------------------------------------
> | Id | Operation | Name | Starts | E-Rows | A-Rows
> | A-Time | Buffers |
>
> -----------------------------------------------------------------------------------------------------------------------------
> | 0 | SELECT STATEMENT | | 1 | | 1
> |00:00:00.01 | 4 |
> |* 1 | VIEW | | 1 | 1 | 1 |00:00:00.01
> | 4 |
> |* 2 | COUNT STOPKEY | | 1 | | 1
> |00:00:00.01 | 4 |
> | 3 | VIEW | | 1 | 5351 | 1
> |00:00:00.01 | 4 |
> | 4 | PARTITION RANGE SINGLE | | 1 | 5351 | 1
> |00:00:00.01 | 4 |
> | 5 | TABLE ACCESS BY LOCAL INDEX ROWID| TABLE1 | 1 | 5351
> | 1 |00:00:00.01 | 4 |
> |* 6 | INDEX RANGE SCAN DESCENDING | PK_TABLE1 | 1 | 5351
> | 1 |00:00:00.01 | 3 |
>
> -----------------------------------------------------------------------------------------------------------------------------
>
> Predicate Information (identified by operation id):
> ---------------------------------------------------
> 1 - filter("MAX_VAL">=:B1)
> 2 - filter(ROWNUM=1)
> 6 - access("PART_COL"=:B2 AND "MIN_VAL"<=:B1)
>
>
> --modified query with one rows output------
>
> -------------------------------------------------------------------------------------------------------------------------------------------
> | Id | Operation | Name | Starts | E-Rows |
> A-Rows | A-Time | Buffers | Reads |
>
> -------------------------------------------------------------------------------------------------------------------------------------------
> | 0 | SELECT STATEMENT | | 1 | | 1
> |00:00:00.01 | 40 | 35 |
> | 1 | PARTITION RANGE SINGLE | | 1 | 5267 | 1
> |00:00:00.01 | 40 | 35 |
> | 2 | TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| TABLE1 | 1 |
> 5267 | 1 |00:00:00.01 | 40 | 35 |
> |* 3 | INDEX RANGE SCAN | PK_TABLE1 | 1 | 5267 |
> 1 |00:00:00.01 | 39 | 35 |
>
> -------------------------------------------------------------------------------------------------------------------------------------------
>
> redicate Information (identified by operation id):
> ------------------------------------------------
> 3 - access("PART_COL"=:B2 AND "MAX_VAL">=:B1 AND "MIN_VAL"<=:B1)
> filter("MAX_VAL">=:B1)
>
> On Wed, Oct 12, 2022 at 3:07 PM Sayan Malakshinov <xt.and.r_at_gmail.com>
> wrote:
>
>> 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
>>
>
-- Best regards, Sayan Malakshinov Oracle performance tuning engineer Oracle ACE http://orasql.org -- http://www.freelists.org/webpage/oracle-lReceived on Thu Oct 13 2022 - 11:37:23 CEST