Buffer gets - array size, fetch size - High latch activity - buffer gets??? bug or default behavior
Date: Wed, 25 Dec 2019 04:43:14 +0530
Message-ID: <CAP-RywzyyK2EC4qh0xVgHuaXm+Dt9dQdWTJ-0T54bcZhdkJ9Zg_at_mail.gmail.com>
Hi,
The situation is as follows:
create table randomload2 as select * from randomload order by mark4;
create index idx on randomload2(mark4);
now carefully consider any value from mark4 (indexed column) that
retrieves more than 500 rows (example) eventually we will find that 100
rows also causes high latching activity...
select * from randomload2 where mark4= 100;
see the buffergets for this SQL and also the carefully monitor the latch
activity on each of these latches holding these data blocks (CBC) latches.
i used a query such as the following:
testing with set fetchsize 20, 30, 60, 100, 200.
we will see different buffer gets per execution each time and latch
activity is very high...
Can someone please tell me
Thanks
create table randomload(roll number, mark1 number, mark2 number, mark3
number, mark4 number);
load the table with random data;
then create an different table so that the clustering factor on mark4
column will be close to table blocks.
exec dbms_stats.gather_table_stats('VISHNU','RANDOMLOAD', CASCADE=> TRUE);
select sum(gets) from v$latch_children where addr in
('000000006C9A4160','000000006CA29DA8','000000006CA37068','000000006CAFEF20','000000006CB52450','000000006CB52450','000000006CB52450','000000006CB91E28','000000006CB91EF0');
it is very interesting that the latch activity or buffer gets for a SQL
statement is dependent on the array size or fetch size..
average latch gets or buffer gets will always be higher when the fetch size
is small, even 100, the database repeatedly accesses the data blocks, or
latches...
even though the number of blocks accessed will be less (physical blocks in
my case only 6) since average row length is less and the mark4 column had
only 3200 distinct values).
Is this expected? or I am missing any thing?
Vishnu
--
http://www.freelists.org/webpage/oracle-l
Received on Wed Dec 25 2019 - 00:13:14 CET