ANY translated to filter(("col1"= OR "col2=))

I had to tweak a sequence of statements, in pseudo-code it was something like this:

select col1 into v1
from tab1
where col2=<condition1>;
delete from tab2
where col3 = v1;
delete from tab2
where col4 = v1;

My first step was to avoid the variable v1 and put the select into the deletes as a subselect.
My 2nd step was to merge the 2 deletes into one, combining the 2 selerated conditions using OR.
this gave an execution plan similar to this (I use the test-table from my previous post and a really simple statement):
delete
from berx1
where type=(select 1 from dual)
or dim=(select 1 from dual);


-------------------------------------
| Id  | Operation           | Name  |
-------------------------------------
|   1 |  DELETE             | BERX1 |
|*  2 |   TABLE ACCESS FULL | BERX1 |
|   3 |    FAST DUAL        |       |
|   4 |     FAST DUAL       |       |
-------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(("TYPE"= OR "DIM"=))

The next idea was to avoid running the same subselect twice:
My first thought was to use the subquery_factoring_clause (WITH), but it's only useable in SELECT statements. So I loitered through the documentation and stumbled across the ANY keyword. After some ests I came to this statement:
delete
from berx1
where (select 1 from dual) = any (type, dim);

------------------------------------
| Id | Operation | Name |
------------------------------------
| 1 | DELETE | BERX1 |
|* 2 | TABLE ACCESS FULL| BERX1 |
| 3 | FAST DUAL | |
------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(("TYPE"= OR "DIM"=))

Anything more to say? Only one subselect, even the filter is the same.
I tested this behaviour with 11.1.0.6 and 10.2.0.4 - both are the same.

simple tuning using decode

Currently I have to assist in tuning of a really time critic application (in comparison to other applications/projects I have to support). There I found 2 statements called within an PL/SQL procedure:

1. select count(id)
   from berx1
   where val=1 and type is null

2. select count(id)
   from berx1
   where val=1 and type is null and dim= 2

(The statements are reduced to test-cases, my test-table is
create table berx1 (id number, val number, type number, dim number);

insert into berx1 values (1, 1, NULL, 1);
insert into berx1 values (2, 1, 1, 2);
insert into berx1 values (3, 1, NULL, 3);
insert into berx1 values (4, 1, 1, 1);
insert into berx1 values (5, 2, NULL, 2);
insert into berx1 values (6, 2, 1, 3);
insert into berx1 values (7, 2, NULL, 1);
insert into berx1 values (8, 2, 1, 2);
insert into berx1 values (9, 3, NULL, 3);
insert into berx1 values (10, 3, 1, 1);
insert into berx1 values (11, 3, NULL, 2);
insert into berx1 values (12, 3, 1, 3);
insert into berx1 values (13, 4, NULL, 1);
insert into berx1 values (14, 4, 1, 2);
insert into berx1 values (15, 4, NULL, 3);
insert into berx1 values (16, 4, 1, 1);
insert into berx1 values (17, 5, NULL, 2);
insert into berx1 values (18, 5, 1, 3);)
Even both of them are very fast (with an index on val), As we have to save every slice of time we can, I merged the statements into one:

SELECT count(id) count
, NVL( SUM( DECODE(dim, 2   -- if 'dim' is 2 
                      , 1   -- add 1 to sum
                      , 0)  -- otherwise add nothing
          ), 0
     ) count_dim
FROM berx1 where val=1 and type is null;

What did I expect so far? (not tested into detail!)

• less context-switches between PL/SQL and SQL
• less soft parses / library cache lookups
• less activity in the buffer cache

In fact, at the end of the optimization I hope I can modify the program logic, maybe we can save a lot more time on other places and also get rid of this statement. But this is still matter of investigation with the application-designers.

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.

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)

TimeMachine for windows

I love my Mac, and I like TimeMachine as the best new feature (I use) from Leopard.
I also searched for such a solution for Windows (e.g. for my parents or my sisters laptop).
My search has an end, I found rsyncbackup.vbs from heise.de.
I know, it's not a solution, jus a script. but I'm sure, I can adapt it a little bit, so it would be useable for my family (e.g.: in startup-script, check if target-disk is available, etc.)
We will see...

DBAs little helper

You might know this situation: Your users call and complain, the DB is hanging. While trying to connect (and making plans what to check first, sessions, waits, locks, ...) you do not get any prompt. Ok, there would be a lot of things you get in mind to check, analyze and then fix. But in fact, the users want their connections back, don't care about current open sessions and just ask to reboot the instance.
Even reboot is not that simple in this situation (have I mentioned the missing prompt in sql*plus?). So the next way is to try kill on os-level (which doesn't do anything at the end, as the process seems to wait for something on OS). A much more entertaining way is kill -9. I hope, if I kill a vital kernel process, the others will do the cleanup and close everything the best way they can (similar to a shutdown abort). But even this doesn't happen. The others are hanging around and doing nothing. At the end I have to kill all processes using -9.
There is just one more thing to do: startup. but, unfortunately, at the login there is one thing missing: 'Connecting to an idle instance' - and a 'startup' just hangs.
Why? Because no one did the cleanup! Do you remember the kill -9 of all, also the last background process? Doing so, I did not gave any process the chance to release the semaphore and the shared memory segments. They are just hanging around. Even no one uses them, sqlplus belives because they exists, there is a running system. So it connects to them, sends messages, but noone ever listens. this makes this sqlplus-instance also hanging.
How can i do the cleanup manually?
The old way, back in those days, when Versions did not contain characters like i or g, is to search all other instances for their ressources using oradebug ipc, and afterwards removing them with ipcs. If anything went wrong - another DB crashed very beautiful and interresting.
The new way, introduced in this millenium, is sysresv, which you can find in $ORACLE_HOME/bin. With this, you can show the semaphor and shared memory segments used by your current os-user, $ORACLE_HOME and $ORACLE_SID (or for multiple SIDs). It can also release the ressources for a given SID. This makes live much easier and more secure in situations like shown above.
I doesn't know why Oracle doesn't mention this binary in its documentation.