Re: suddenly direct path read from small tables
- From: samuel guiñales cristobal <samuelg.cristobal@xxxxxxxxx>
- To: Martin Berger <martin.a.berger@xxxxxxxxx>
- Date: Tue, 25 Oct 2016 10:01:33 +0200
you sent a set of emails today, but I could not find an answer to the
questions raised by Stefan, LS Cheng or me.
sorry, what question i did not answer?
Based on your last email, you saw a change in execution plan.
As stated before, this has nothing to do with _stt!
I know that parameter(small_table_threshold) impact on runing execution
decision to do direct
path read for FTS. no CBO, no stats.
If you want to focus on the reason for the change in your plan, you have
to identify, what made the plan change?
Beside different optimizer environments I see the major reasons for
different plans in changed statistics or (first) bind variables.
You will have some history about statistics in your database, but it's
hard to identify optimizer environments or binds -
especially when the instance was restarted/crashed.
And probably you do not even care much why the plan changed, you want
"your preferred" plan - or plans.
This leads me back to the reasons why a plan changes.
Anyhow, identify if you suffer changing statistics or binds.
Then address those.
It will not help to post some random values here, bring it down to a
complete picture.
A Index degradation for purge process some days before storage fail lead to
do rebuild indexes and gather statistics
for one of the databases with de direct path read problem.
Very diferent problem now, impact more databases, couldnt restore stats,
statistics history not available.
---
Samuel Guiñales Cristobal <samuelg.cristobal@xxxxxxxxx>
«Que tu alimento sea tu medicina, y la medicina tu alimento.» Hipócrates
On 24 October 2016 at 20:59, Martin Berger <martin.a.berger@xxxxxxxxx>
wrote:
Hi Samuel,
you sent a set of emails today, but I could not find an answer to the
questions raised by Stefan, LS Cheng or me.
Based on your last email, you saw a change in execution plan.
As stated before, this has nothing to do with _stt!
If you want to focus on the reason for the change in your plan, you have
to identify, what made the plan change?
Beside different optimizer environments I see the major reasons for
different plans in changed statistics or (first) bind variables.
You will have some history about statistics in your database, but it's
hard to identify optimizer environments or binds - especially when the
instance was restarted/crashed.
And probably you do not even care much why the plan changed, you want
"your preferred" plan - or plans.
This leads me back to the reasons why a plan changes.
Anyhow, identify if you suffer changing statistics or binds.
Then address those.
It will not help to post some random values here, bring it down to a
complete picture.
Martin
2016-10-24 13:12 GMT+02:00 samuel guiñales cristobal <
samuelg.cristobal@xxxxxxxxx>:
For add more info.
We´re reviewing also information related with query execution plans[1].
We´ve detected that execution plans has changed on at least two instances
and two queries on them after storage failure.
Question is how turn back this behavewith out use workaround fakeing
table stats
or any other like setting "event 10949" and
"_very_large_object_threshold"?
is posible?
[1]
*Before incident:*
* Buffer Gets: 34,492,899 45,746.6 9.05*
* Disk Reads: 0 0.0 .00*
------------------------------------------------------------
--------------------
| Operation | PHV/Object Name | Rows | Bytes|
Cost |
------------------------------------------------------------
--------------------
|SELECT STATEMENT |----- 3237944248 ----| |
| 291 |
|NESTED LOOPS | | |
| |
| NESTED LOOPS | | 279 |
36K| 291 |
| INDEX RANGE SCAN |INDEX1 | 279 |
15K| 9 |
| INDEX UNIQUE SCAN |INDEX2 | 1 |
| 1 |
| TABLE ACCESS BY INDEX ROWID |TABLA _A | 1 | 79
| 2 |
--------------------------------------------------------------------------------
*During and after the incident*
* Buffer Gets: 7,235,001 39,752.8 8.84*
* Disk Reads: 7,294,145 40,077.7 92.56*
------------------------------------------------------------
--------------------
| Operation | PHV/Object Name | Rows | Bytes|
Cost |
------------------------------------------------------------
--------------------
|SELECT STATEMENT |----- 2237180378 ----| | |
5080 |
|HASH JOIN | | 6K| 847K|
5080 |
| INDEX RANGE SCAN |INDEX1 | 6K|
351K| 127 |
*| TABLE ACCESS FULL |TABLA_A | 1M| 130M|
4943 |*
------------------------------------------------------------
--------------------
---
Samuel Guiñales Cristobal <samuelg.cristobal@xxxxxxxxx>
«Que tu alimento sea tu medicina, y la medicina tu alimento.» Hipócrates
On 24 October 2016 at 12:44, samuel guiñales cristobal <
samuelg.cristobal@xxxxxxxxx> wrote:
workaround applyed in this case was this to go under
_small_table_threshold(2416) like in the other database.
EXEC DBMS_STATS.SET_TABLE_STATS('user','table_name',*numblks=>1000*);
Regards
---
Samuel Guiñales Cristobal <samuelg.cristobal@xxxxxxxxx>
«Que tu alimento sea tu medicina, y la medicina tu alimento.» Hipócrates
On 24 October 2016 at 12:08, samuel guiñales cristobal <
samuelg.cristobal@xxxxxxxxx> wrote:
yes, my mistake to paste, cause this problem afecting two
databases(same direct path read io eait issue) i pasted
wrong the other databsase info sorry i correct:
table is 41MB, number of rows: 35473
SELECT name,block_size,buffers FROM v$buffer_pool; ---> 120832 buffers
SELECT blocks FROM dba_tables WHERE table_name = 'NOMBRE_TABLA''; -->
5158
_small_table_threshold 2416
---
Samuel Guiñales Cristobal <samuelg.cristobal@xxxxxxxxx>
«Que tu alimento sea tu medicina, y la medicina tu alimento.» Hipócrates
On 24 October 2016 at 11:47, Ls Cheng <exriscer@xxxxxxxxx> wrote:
Hi
You only have 1GB db cache and 1680MB sga max size but in your first
post you said you have 1451136 database memory buffer which is 11337MB if
you have 8K db block size which is clearly wrong. That confused probably
all of us because if your table is 273MB clearly it passes the 2%
threshold
(it's actually 27% of 1024MB) therefore it's perfectly a direct path read
candidate. Why it does so probably it changed from index excution plan to
FTS plan.
BR
On Mon, Oct 24, 2016 at 11:40 AM, samuel guiñales cristobal <
samuelg.cristobal@xxxxxxxxx> wrote:
Hi Cheng,
ASMM no doubt. allways same little mem, why now problems?
sga_max_size big integer 1680M
sga_target big integer 0
NAME SIZE MB
-------------------- ----------
Shared Pool Size 480
Large Pool Size 32
Java Pool Size 128
sga_max_size: 1680
shared_pool_size: 480
large_pool_size: 32
java_pool_size: 128
db_cache_size: 1024
log_buffer: 6.140625
sort_area_size: .0625
pga_aggregate_target: 256
% Free SharedPool
-----------------
11.54
NAME POOL SIZE MB
-------------------- ------------ ----------
free memory shared pool 32.9287186
free memory large pool 32
free memory java pool 128
row cache shared pool 8.23996735
---
Samuel Guiñales Cristobal <samuelg.cristobal@xxxxxxxxx>
«Que tu alimento sea tu medicina, y la medicina tu
alimento.» Hipócrates
On 24 October 2016 at 11:28, Ls Cheng <exriscer@xxxxxxxxx> wrote:
Hi
AMM => memory_target
ASMM => sga_target
Do you use one of them? I guess so otherwise you would not have
1451136 db block buffers with 1GB db_cache_size. What Martin is
suggesting
is that if you dont have a large enough minimum db_cache_size then due
to
ASMM or AMM nature you could end up with a quite small db cache size
therefore a table which was ont 2% of db cache can suddently become so
and
become a direct path read candidate.
Aggregate v$sgastat by pool and show us the output we we can see the
current sga distribution.
BR
On Mon, Oct 24, 2016 at 11:21 AM, samuel guiñales cristobal <
samuelg.cristobal@xxxxxxxxx> wrote:
Hi Martin,
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?
not in spfile, calculated by instance.
If not, do run ASMM? If yes, do you set a minimum value for buffer
cache?
no AMM, last start buffer cache size:
db_cache_size big integer 1G
never change in this database
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
checked all starts in alertlog, allways 1G db_cache_size, no
volatil memory change (no ALTER SYTEM of memory in alertlog)
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?
thanks Martin, after woraround we fix problem, but FTS continue in
memory :(
---
Samuel Guiñales Cristobal <samuelg.cristobal@xxxxxxxxx>
«Que tu alimento sea tu medicina, y la medicina tu
alimento.» Hipócrates
On 23 October 2016 at 21:28, Martin Berger <
martin.a.berger@xxxxxxxxx> wrote:
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-d
riven-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@xxxxxxxxx>:
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
Other related posts:
