suddenly direct path read from small tables
Date: Sun, 23 Oct 2016 20:31:19 +0200
Message-ID: <CAESzQCEKLDByuQotNag9tbOChZHg-1TCgsgvYBcGZ+ZbGAo3Pg_at_mail.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-64info: 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
--- Samuel Guiñales Cristobal <samuelg.cristobal_at_gmail.com> «Que tu alimento sea tu medicina, y la medicina tu alimento.» Hipócrates -- http://www.freelists.org/webpage/oracle-lReceived on Sun Oct 23 2016 - 20:31:19 CEST