latches and events - from 11.1.0.6 to 11.1.0.7

I was just curious what parts of latches and waits oracle changed in it's first patchset of 11.1.0.7. here my results (i run a create table latch_11106 as select * from v$latch; before and after the patch, with 11107 obviously):

Counts:
select count(*) from latch_11106;

COUNT(*)
----------
   496

select count(*) from latch_11107;

  COUNT(*)
----------
    502

select count(*) from EVENT_NAME_11106;

  COUNT(*)
----------
    961

select count(*) from EVENT_NAME_11107;

  COUNT(*)
----------
    997

More Details:
Events which does not exist in 11.1.0.7 anymore:
select name from event_name_11106 minus select name from event_name_11107;


NAME
---------------------------------------------
Intelligent Storage OSS I/O completion
Streams capture: resolve low memory condition
storage device registration
virtual circuit status

new Events:
select name from event_name_11107 minus select name from event_name_11106;

NAME
----------------------------------------------
Archiver slave I/O
DBWR range invalidation sync
DBWR slave I/O
LGWR slave I/O
Logical Standby Debug
PMON to cleanup detached branches at shutdown
RMAN Disk slave I/O
RMAN Tape slave I/O
Streams apply: waiting for dependency
Streams apply: waiting to commit
Streams: flow control
Streams: resolve low memory condition
Streams: waiting for messages
WCR: replay paused
cell multiblock physical read
cell single block physical read
cell smart file creation
cell smart incremental backup
cell smart index scan
cell smart restore from backup
cell smart table scan
cell statistics gather
direct path sync
enq: AM - ASM File Destroy
enq: XC - XDB Configuration
enq: ZA - add std audit table partition
enq: ZF - add fga audit table partition
external table misc IO
external table read
external table write
gc cancel retry
gc recovery
ges DFS hang analysis phase 2 acks
ges/gcs diag dump
listener registration dump
master diskmon startup
process diagnostic dump
securefile chain update
shared server idle wait
virtual circuit wait

40 rows selected.

There are no changes between WAIT_CLASSes.

latches:
removed:
select name from latch_11106 minus select name from latch_11107;

NAME
---------------------
FAL subheap alocation
TXN SGA
cv apply list lock
cv free list lock

New:
select name from latch_11107 minus select name from latch_11106;

NAME
---------------------------------------
VPSO SGA
WCR: MMON Create dir
WCR: kecr File Count
cvmap freelist lock
ges DFS wait callback info
heartbeat check
kcfis stats shared latch
parallel ibr array
rsb inst ckpt scn
storage server table manipulation latch

10 rows selected.

Unfortunately I do't know how to get a List of all existing mutexes. v$mutex_sleep_history does not give all possible mutexes, only these, qou used in the past (and the system holds historical infos about.

If someone knows, how to get all mutexes, please inform me!

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.