Re: Improving query performance further

Have you considered making TABLE1 an IOT?  No, not an "internet of things" but an "index-organized table"...

If the primary method of access is the primary key, and TABLE1 is has "narrow" rows (i.e. AVG_ROW_LEN less than 20-25 bytes or so), then an IOT could save on the number of logical reads.  There's a lot of "if"s, but the best approach is not to think about it, but just go ahead and test it, side-by-side with the existing table. After all, it's only about ~100MB in size, right?

But, at the very least, it shouldn't be difficult to just put the table and the PK index together into the KEEP pool of the Buffer Cache?  After all, although the ideal is to size the KEEP pool to accommodate the entire objects assigned to it, you certainly aren't required to size it that way.  You just want to size it so that buffers flush out far more slowly than they do in the DEFAULT pool.

<rant>Too many DBAs think that the SGA should only use about 50% of the available physical memory on a host, which is nonsense. The Linux/UNIX operating systems only need a few GB of memory, and AWR can tell you unequivocally how much space is needed for PGA, so the SGA should be sized closer to the Oracle-imposed maximum of 90% of host physical memory.  It's there.  It's not being used.  Use it.  If I had a nickel for every unused GB of RAM on Oracle database servers, I could buy my own Hawaiian island.</rant>

Hope this helps!

Enjoy!

On 10/11/2022 2:04 PM, yudhi s 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