Freitag, 29. August 2008

switching patches on and off

You want to play around with optimizer-patches in your DB?
you want to break your DB a curious way?
have a look at v$system_fix_control and v$session_fix_control and gamble by change the parameter _fix_control. I also found  _fix_control_key but no information about it.
For more than one patch to switch, use 
alter session set "_fix_control"='4728348:OFF','4663698:OFF'; i learned here.
What is it good for?
To test if the just-applied patch works as expected.
Recommended? 
Patches are to fix bugs. Playing around with bugs is a job for exterminators.
Happy playing ;-)

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!)



the 'Magic of 2'

There is a very popular pdf about parallel queries.
Why I quote it here?
to create another link , give you something to read and as a starting-point for my own tests.

Mittwoch, 27. August 2008

manual Remastering of Global Cache

this all is stolen from a Presentation from Julian Dyke. Some infos are also from Fairlie Rego.

for more background, please read there (or wherever you want). This is only a container for some code sniplets:

find the object_id from the object you are interested in:

SELECT data_object_id
FROM dba_objects
WHERE owner = 'US01' AND object_name = 'T1';


find current and previous master and a remaster-count (this might be 0 all the time due to BUG:5649377 - fixed in 11G - Backports available for Linux32-10.2.0.2&10.2.0.3

SELECT object_id,current_master, previous_master ,remaster_cnt
FROM V$GCSPFMASTER_INFO
WHERE object_id = 144615;


to remaster the object to a dedicate instance go tho this instance and run

oradebug setmypid
oradebug lkdebug -m pkey <obj_id>


To dissolve remastering of this object on this instance

oradebug lkdebug -m dpkey <obj_id>


There are some more infos availalbe from a bunch of internal tables.
[X$KJDRMREQ-Dynamic Remastering Requests, X$KJDRMAFNSTATS-File Remastering Statistics, X$KJDRMHVSTATS-Hash Value Statistics,X$KJBL-Externalizes GCS Enqueues,
X$KJBR-Externalizes GCS Resources]
(I will not discuss it here)

Sonntag, 24. August 2008

a fix for OS X syslogd going crazy

recently the fan of my PowerBook run permanently. I checked which process created the load (and therefore the heat) and found syslogd.
After some tracing, I saw a lot of

lseek(0x6, 0xF0, 0x0) = 240 0
read_nocancel(0x6, "\004\0", 0x1000) = 4096 0
lseek(0x6, 0xFD, 0x0) = 253 0
write_nocancel(0x6, "\0", 0x4)

With some googeling, I found (only) this blog-entry.

The solution from there (copy&paste)

sudo launchctl stop com.apple.syslogd
sudo mv /var/log/system.log /var/log/system.log.old
sudo launchctl start com.apple.syslogd
sudo gzip /var/log/system.log.old

It works really fine for me. Thank you