Re: Query running slow because cell offload discrepancy

https://orasql.org/2017/05/04/when-bloggers-get-it-wrong-part-2/

On Tue, Jun 18, 2024 at 11:25:58, Jonathan Lewis < jlewisoracle_at_gmail.com > wrote:

>
> 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/ (
> 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 (
> 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