Freitag, 29. August 2008

gc current block 2-way - p1,p2,p3

In some tracing I stumbled about a lot of lines like this:

nam='gc current block 2-way' ela= 1716 p1=7 p2=449034 p3=33554433 obj#=61165 tim=1190815206676979

Even I know the Interconnect is too slow (only 100MBit), I just want to know what I read here. (at least the best way I can do).
the meaning of ela=, obj#= and tim= can be found on many places. But the p1, p2 and p3 where of some interrest. So I started to search. but neither Metalink nor Oracle-L could answer my search-results (maybe I just used the wrong words?) There where even no other direct source I could find.
I also run

SELECT name, parameter1, parameter2, parameter3
FROM V$EVENT_NAME
WHERE name like 'gc current block %way';

and got

NAME P1 P2 P3
------------------------ ----- ----- -----
gc current block 2-way
gc current block 3-way

back.
there is also nothing in the 11g documentation about gc current block.

Not so good informations so far. This led me to a searching marathon:
(only my findings)
  • The Book Oracle 10g RAC Grid, Services & Clustering gives a link between gc current block 2-way (10g Wait name) and global cache open x (9i Wait name).
  • The Book Oracle Wait Interface tells me about global cache open x

    global cache open x
    Wait Parameters
    Wait parameters for global cache open x are described here:

    P1 File number from which Oracle is reading the block
    P2 Block number from the file that Oracle wants to read into a buffer
    P3 The lock element number or class of the buffer

  • one more finding (from limited value): in the docu there is an event GCS lock open X which seems to have the same content for P1, P2 and P3. But of course a 'lock open' is not simply equal to a 'cache open' or a request for a 'current block'.

So far for the (very limited) documentation I found.
Now let's do some tests (on an other DB):

  1. instance 1: create table test1 as select * from dba_objects;

  2. instance 1: select * from test1;27 cr multi block request, 2 gc cr grant 2-way. I don't know where they come from(!) this will be matter for further investigations.

  3. instance 1: select * From test1;no gc cr ... only SQL*Net message

  4. instance 2: select * from test1; this is the most interresting part: a lot of differnt waits, most interresting is:
    WAIT #1: nam='gc cr multi block request' ela= 144 file#=6 block#=48017 class#=1 obj#=62850 tim=685587786409
    WAIT #1: nam='gc current block 2-way' ela= 1121 p1=6 p2=48018 p3=1 obj#=62850 tim=685588475668

    looking down for 6. ( i run it with 48017 and 48018) I can at least compare file# to p1 and block# to p2 - obj# also matches

  5. instance 2: select * From test1;only one library cache lock and of course SQL*Net message

  6.  SELECT segment_name
    FROM dba_segments
    WHERE header_file=6 and 48017 between HEADER_BLOCK and HEADER_BLOCK+blocks;


    SEGMENT_NAME
    ------------
    TEST1

    and
    SELECT OBJECT_NAME, OBJECT_ID
    FROM dba_objects
    WHERE OBJECT_ID=62850;

    OBJECT_NAME OBJECT_ID
    ----------- ----------
    TEST1 62850


Conclusio:

So my findings are:

  1. more questions, e.g. why are there any gc in the 1st select?
  2. Even I still cannot proove, I assume p1, p2 and p3 of gc current block 2-way (and mayby also others) match to file#, block# (and maybe also class# - even I have not thought about this!)



Kommentar veröffentlichen