Re: Improving query performance further

From: yudhi s <learnerdatabase99_at_gmail.com>
Date: Thu, 13 Oct 2022 01:54:11 +0530
Message-ID: <CAEzWdqdPD=nuYdpw8VdfujTAJCX=MUpM_8em_62da+79f4qO3A_at_mail.gmail.com>

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
>

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

Received on Wed Oct 12 2022 - 22:24:11 CEST