Re: Query running slow because cell offload discrepancy

If you want to check the actual size of the buffer cache you can query v$buffer_pool; if you want to check if the buffer pool has been shrinking and growing unexpectedly you can query v$sga_resize_ops ( https://jonathanlewis.wordpress.com/2007/04/16/sga-resizing/ ) or v$memory_resize_ops. This MAY have had an impact.

The decision about direct path also includes an assessment of how much of the object is already cached - and when you compare the stats for the two nodes you can see that they do roughly the same amount of "logical" I/O, despite the fact that node 2 seems to have done only a tiny fraction of the necessary physical I/O to read the table ... i.e. something has already managed to get most of the table into the local buffer cache (or, possibly but it seems unlikely given the timing, a HUGE amount of the table is being access by cache transfer from node 1).

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".

There is a little oddity in the execution stats - specifically the EXECs for the final nested loops, which are higher than they should be given the number of "rows" acquired in the preceding steps. In itself this probably has a minimal impact, but it does raise the question of whether something catastriphic has happened but only been captured in these two lines.

If you want to test the caching hupthesis execute "alter system flush buffer cache" and re-run the query.

N.B. for information purposes only: I think the VLOT, MTT, STT, stuff applies at the partition level not the table level.

Regards
Jonathan Lewis

On Mon, 17 Jun 2024 at 20:08, Pap <oracle.developer35_at_gmail.com> wrote:

> Can someone guide here, how to proceed to get the root cause of this
> issue?
>
>
>

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