Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Unusually high number of current mode reads for a GTT insert

Re: Unusually high number of current mode reads for a GTT insert

From: srivenu <srivenu_at_hotmail.com>
Date: 2 Mar 2004 22:22:03 -0800
Message-ID: <1a68177.0403022222.47899f13@posting.google.com>


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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US