Re: suddenly direct path read from small tables

From: Martin Berger <martin.a.berger_at_gmail.com>
Date: Sun, 23 Oct 2016 21:28:36 +0200
Message-ID: <CALH8A90dsJd9OS0PSmRs6yDgxtqe+4FRXr=PHA-BZ0rad2n56A_at_mail.gmail.com>



Samuel,

there can be various reasons why a direct path read is preferred against filling buffer cache.
Just as a reference, here is Tanel Põders article about it. http://blog.tanelpoder.com/2012/09/03/optimizer-statistics-driven-direct-path-read-decision-for-full-table-scans-_direct_read_decision_statistics_driven/

First of all it's not an optimizer decision. That's the reason why rebuilding index didn't help.
As Tanel (and your workaround) showed, new/different statistics can change the behaviour.

So let's separate things:
You jump from "direct path read" to "FTS" - can you please check, if the PLAN changed?
In this case you have to identify, why the optimizer picked a different plan.

Another question would be why it flips from scattered read to direct read. That's more about the value of _small_table_threshold, number of blocks in buffer cache and others.
Not only the number of blocks in cache can be very volatile (and quite small when the instance started).
Do you have _small_table_threshold set in your spfile or is it calculated at startup?
If not, do run ASMM? If yes, do you set a minimum value for buffer cache? As _small_table_threshold is derived from buffer bache size (2% afaik), and volatile buffer cache size, it just can happen it was different in previous instances, and just due to the crash other parameters are effective now. You can check this in your instances alert.log

I just threw a lot of details in your direction, maybe let's start to clarify if it's an optimizer/Plan issue or direct/scattered read?

Then we can dig deeper.

best regards,
 Martin

2016-10-23 20:31 GMT+02:00 samuel guiñales cristobal < samuelg.cristobal_at_gmail.com>:

> Hi all,
>
> Recently we suffer a storage problem in RAC, all instances down.
> After recover, detected high "direct path read" from some
> small tables in some data bases, OS I/O wait 20-40%, hard performance
> problem.
>
> Rebuilding indexes and runing statistics in this tables not solve problem.
> execution plan changed to do Full table scan and index not used like
> before.
>
> maybe optimizer not involve and is parameter _small_table_threshold?
> but why now start psyh reads?
>
> *some info of one database and workaround :*
>
> version............................................: 11.2.0.4
> OS....................................................: Linux x86-64
> info: no tunning and diagnostic pack
> _small_table_threshold..............: 29022
> Table with high psy rds.(almos all, 95%)
> -Table size................: 273M
> -Table block num .....: 34730
> db memory buffers............................ : 1451136
>
> we apply *workaround*,we fake table stats so it looks like that there’s
> only 20K blocks for that table so avoid phy reads:
>
> EXEC DBMS_STATS.SET_TABLE_STATS('user','table_name',*numblks=>20000*);
>
> any idea to solve that FTS? why after storage problem?
>
> Regards
>
>

--
http://www.freelists.org/webpage/oracle-l
Received on Sun Oct 23 2016 - 21:28:36 CEST

Original text of this message