Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Unusually high number of current mode reads for a GTT insert
VC,
I too have some questions on this.
Probably Jonathan has some time to look at this.
While reading from a normal table, I see that the block is pinned once
and all the rows got from it. So for a FTS, the number for consistent
gets is equal to the number of blocks in the table below HWM.
Whereas for an update, each block is pinned in current mode once for
every row updated. so if you have to update 10 rows in a block, the
block in pinned in current mode 10 times. There must be some reason
why all the rows in a block are not updated at one go while the block
is pinned in current mode.
This would have reduced the latch contention a bit.
For example - This is from a test i did.
Oracle 9.2.0.3
create table t11 as select * from all_objects;
insert into t11 select * from t11;
insert into t11 select * from t11;
create table t13 as select * from t11;
select count(*) from t13;
123768
Now i run these 2 statements and get the difference of session
statistics.
Gather Session stats here for this session from an other session
select count(*) from t13;
Gather Session stats here for this session from an other session
update t13 set object_name='XYZ';
Now the major statistis difference between Run1 and Run2 is
NAME RUN1 RUN2 DIFF ------------------------------ ---------- ---------- ---------- buffer is not pinned count 571 610 -39 consistent gets 575 627 -52 messages sent 0 149 -149 switch current to new buffer 0 570 -570 calls to kcmgas 0 654 -654 change write time 0 824 -824 CPU used by this session 1 854 -853 CPU used when call started 1 854 -853 free buffer requested 1 2266 -2265 redo entries 0 124345 -124345 db block gets 0 127820 -127820 session logical reads 575 128447 -127872 db block changes 0 250892 -250892 redo size 0 31977396 -31977396
You can see the db block gets almost equal to the number of rows.
regards
Srivenu
Received on Wed Mar 03 2004 - 00:22:03 CST