Re: Fixing Performance issue with less selective columns

From: Jonathan Lewis <jlewisoracle_at_gmail.com>
Date: Fri, 27 Aug 2021 11:44:26 +0100
Message-ID: <CAGtsp8n0edYfHfnQozk9M0hYkzq8FaP+FxCXpRBqLwviwTDnew_at_mail.gmail.com>



Physical reads = 5679480
Physical read requests = 44451
cell flash cache read hits = 42932

5,679,480 / 44,451 = 127.77 -- so all your reads are 1MB reads (less a bit for space management blocks)
Of which 96.6% came from the flash cache, so there will probably not be much improvement available from KEEPing the table.

The other thing about the suggestion is that you've also said that the query is run many times with concurrent sessions doing the same thing. This means while the peak load of executions of this query is going on the table is 100% in the flash cache, and your test may not be truly indicative of actual run-time caching.

Regards
Jonathan Lewis

On Thu, 26 Aug 2021 at 13:10, Lok P <loknath.73_at_gmail.com> wrote:

>
> Thank you.
>
> For a sample manual execution I tried picking the stats from V$Sesstats.
> They are as below. And it does state that currently it's already using a
> storage index.
> Cell_flash_cache and flash_cache both are showing DEFAULT in dba_tables
> for this object. But having below stats is it really pointing to the fact
> that making it define as 'CELL_FLASH_CACHE = KEEP will improve the response
> time significantly?
>
>

--
http://www.freelists.org/webpage/oracle-l
Received on Fri Aug 27 2021 - 12:44:26 CEST

Original text of this message