Re: Query running slow because cell offload discrepancy

>
> Actually this table TAB1 is inserted/written by an application having
> service pointed to Node-1 and getting read by another application whose
> service points to Node-2, and the impacted query is one from node-2. Can
> this read and write pattern be related anyway?

Yes, it's possible. It may be a side effect of node-2 doing something with undo records for read-consistency or commit cleanout. ,You will recall I made the comment:

> *The large number of cell single block reads is a little puzzle, of
> course, but you have the session stats so you can check for anomalies - in
> particular one that has been mentioned on Oracle-L fairly frequently, reads
> of the undo tablespace for read-consistency purposes (statistic "xxxx -
> undo records applied". *

Those statistics might be a clue about why node-2 ends up with a lot of cached blocks when node-1 is doing all the DML. You could also look at other stats relating to block handling (other than just the simplest ones about gets and reads) to see if they give you any other clues. e.g. cleanouts, cross-instance transfers.

I am a little curious about the use (or not) of direct path reads / smart scans - does the trace file show Oracle making the decision just once for the whole table, I would have expected it to decide for each partition in turn.

Your query is incorrect, by the way. There may be multiple copies of each block in the buffer so your count() should be of "distinct buf.block#". (You should also be a little careful about about how you run this query - if it runs from the CDB you ought to include the con_id in the query otherwise you could get some confusing results)

Regards
Jonathan Lewis

On Wed, 19 Jun 2024 at 19:20, Pap <oracle.developer35_at_gmail.com> wrote:

> Thank you so much Jonathan and Patrick.
>
> I checked the v$buffer_pool, during this run both of the nodes showed
> buffer_cache size as "32 GB" and "33 GB" respectively.
>
> However when we run the below command , it shows cached_blocks is 16935 on
> Node-1 vs 2652629 on Node-2.
>
>

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