Windows NTP client - allow large time offsets

During the christmas holidays, I 'm doing my own christmas ralley to meet a rather huge family. At these visits I'm often asked to solve some smal or great PC-Problems. Even I'm no PC-pro, I'm something like the one-eyed among blindes. Ad of course it makes more sense to solve problems onsite than remote using just the telephone.
One of the problems was an old laptop with an decrepit BIOS-battery. This led to an totally insane system date at every startup. Even with a proper system time and NTP-Server setup it never set the time correctly as the time offset was to large.
After some searching within the registry I changed under HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\W32Time\Config the parameters MaxNegPhaseCorrection and MaxPosPhaseCorrection toFFFFFFFF, which is the maximum value (approx 136 years). Now at every connection, or at least at every manual time adjustment, the time is really set (and no boring error message is shown).

no row information in data dictionary

It all began with a simple question how to get all ROWIDs from a table (without selecting it)? I posted on oracle-l. After some private forks of conversations I got an email by Jared Still where he mentioned:
"If you constructed a rowid, and did a select on the row, it probably doesn't
matter if the row exists or not.

Oracle has to read the block from disk to read the row directory.
The block would get cached whether or not the row exists.

I haven't tested this, but I imagine you will.  :)"
Of course, he was right:
My testcase was very simple:

1. preparation
   

    create table jared as select * from dual;

2. testcase
   

   ALTER SESSION SET events '10046 trace name context forever, level 12';
   
   Session altered.
   
   select * from jared where rowid='AAAPa5AABAAATHhAAB';
   
   no rows selected
   
   /
   
   no rows selected
   
   select * from jared where rowid='AAAPa5AABAAATHhAAA';
   
   D
   -
   X
   

   which created a tracefile.
   
3. summary
   the first statement hase one related wait: 'db file sequential read' the second and the third have no such waits!
   I crosschecked the situation by asking the buffer:
   
   
   

   select tch from x$bh where obj=63161 and dbablk=78305 and dbarfil=1;

   and - as expected - it just counted up from no rows selected (before the first run) counting 1, 2, 3.
4. conclusio
   Disclaimer: Even I hope it's well coated, I never can be 100% sure. A modern RDBMS has so many ways to do it's job I would have to ask the developers to prove. (which I can't).
   
   
   • the buffer_cache caches blocks, not rows. (obvious?)
   • even if a row does not exist, it's including block is cached, if it's available.
   • as the smallest granularity the RDBMS takes care at IO is a block, and it even stores blocks if the row asked for does not exist.
   this brings me to my own answer to my initial question:
   There is no ROWID information for a given table stored in data dictionary.
   

Frohe Weihnachten!

Ich wünsche allen frohe Weihnachten!

Möge eurer Leben so friedvoll und zufrieden verlaufen, dass ihr jeden Tag das Gefühl habt, es sei Weihnachten.

(translation available if required )

Doing it better ... (Part 2)

Based on my Doing it better ... (Part 1), several days after the first approach and a lot of testcases (for other parts of the application) the performance of our so well tuned statement decreased dramatically.
What happened? The index on RESOURCE_POOL.FREED_TIME became soomething unbalanced. Not the kind you might argue 'B* trees can not be unbalanced per definition', but more of the kind 'based on the code there often remain some blocks nearly unused, which makes the selectivity of the index apear less and less usable. In fact, the statment is not really nice to the CBO, as an AND ROWNUM=1 is hard to estimate.

This brings me to one of the highlights in this project: Based on good arguments and a test-case we could argue to make a slightly change in the code and the logic. To understand the change some words must be said to the logic: the part v_time>FREED_TIME was used to introduce a kind of 'cold down period' where a resource was not used but neverthleless NOT reused again. This period was ever planned to be several minutes (about 5 was the asumption for the project, but it could also be 2 or 15, but it was agreed NEVER to be less than 1 or more than 20 minutes).

This led us to these changes:
we could change the table to make FREED_TIME of type NUMBER and changed the statement to:

UPDATE RESOURCE_POOL SET
           STATUS='INUSE',
        FREED_TIME=NULL,
        SESSION_START_TIME=:B7
WHERE
     FREED_TIME NOT IN
       (select (to_char(sysdate, 'MI')-(rownum - 1)) - 60 * FLOOR((to_char(sysdate, 'MI')-(rownum - 1))/60) safe_minutes
        from dual
        connect by level <= :MINUTES_GRACE)
 AND STATUS='FREE'
 AND ADDRESS_POOL=:B1
 AND ROWNUM=1 
RETURNING IP_ADDR INTO :O0 

and the statement which 'frees' the resource:

UPDATE RESOURCE_POOL SET
...
STATUS='FREE' FREED_TIME=to_char(sysdate, 'MI')
...

This leads to a logic, where every time the status is freed the current minute is stored in FREED_TIME. There are only 60 different values which come again and again and again. (so The selectivity is quite stable.
The select ... from dual ... connect by ... is a simple way to have a eaily changable list of 'the last x minutes' which is aware the break every hour; the more or less strange formula is a kind of MOD().

Doing it better ... (Part 1)

I never thought something like this could be worth a blog entry, but as Tom Kyte can do this, I can also! ;-)

I had the pleasure to assist in a little project as a DBA.
The pleasure was mine, as the target (in response-time) was outstanding (in my current working circumstances) and the setup was done to achieve this in a more or less professional manner (which much more outstanding ...), so we had testcases, a kind of change-management, really load-tests, totally cooperative designers/developers. Great!

The goal of the whole thing was to 'lock' and 'free' available resources out of a pool (according to some additional 'quality'). The example I'm writing is about the process 'provide the next free ressource out of the given pool'. It should come back in about 10ms.

Here the Iterations we came through:

1) primary code:

SELECT resource into v_resource
FROM RESOURCE_POOL
WHERE v_time>FREED_TIME
AND STATUS='FREE'
AND POOL=v_pool
AND ROWNUM=1
FOR UPDATE;
UPDATE RESOURCE_POOL set
     STATUS='INUSE',
     FREED_TIME=null,
     START_TIME=current_timestamp,
WHERE RESOURCE=v_resource;

with this comment beside these statements:

-- FOR UPDATE statement ensures that the selected row is locked until the transition is completed --
--    open 2 different sqlplus commands, issue the command and watch the last command. it waits until update is completed (or commit) on the first session
-- FOR UPDATE NOWAIT would report an error if the row cant be locked immediately --
-- FOR UPDATE SKIP LOCKED pauses other db queries - this could be the perf bottleneck ...

BRRR! Even with an index in RESOURCE_POOL.RESOURCE it's not really optimal. And, the hard fact is, it's to slow.

2) after some iterations we came to:

UPDATE RESOURCE_POOL set
            STATUS='INUSE',
            FREED_TIME=null,
            SESSION_START_TIME=current_timestamp,
WHERE v_time>FREED_TIME
           AND STATUS='FREE'
           AND RESOURCE_POOL=v_pool
           AND ROWNUM=1
           AND v_time>FREED_TIME
RETURNING RESOURCE  INTO v_resource;

As we are counting miliseconds, this WAS an advantage. And for me personally, it's more beautiful, even this is not really a tuning-goal.

Beside some tests on the physical layout (we came to the conclusion to use partitions by resource pools and local indexes on them) this saved us enough time so we could stop tuning.
Maybe I also have to tell all the data is small enough to fit into the buffer_pool.
And for the Records, it's 10.2.0.4 EE on HP-UX (Itanium), Failover ServiceGuard.

Detuning soft parses

During our seminar Oracle 10g Performance analysis with Doug Burns the question arises how to force the optimizer to do a hard-parse all the teme, even when prepared statements and bind variables are used. The question was about a 10.2.0.4 DWH env.

For 11g and some backports I found DBMS_SHARED_POOL.PURGE which just purges the cursor (of course, you have to know it first; but that's no problem for one who complain about bind peeking in 10g).
And just for the records there are some Notes
Note:457309.1 - How To Flush an Object out the Library Cache [SGA]
Note:751876.1 - DBMS_SHARED_POOL.PURGE Is Not Working On 10.2.0.4
and other blogs also: Flushing a single cursor

But my initial idea was tha classic art or de-tuning:
Sometimes we complain about situatiions, where the statement is literally the same, but the optimizer creates several childs by hard-parsing. So let's use this bahaviour this time: Just change one of the many related optimizer environments.
1og Optimizer Environment Views will be a good start, but I assume (not tested!) even a NLS_SORT might help for the start.
Just grab one environment parameter which provides a wide range of values with little to no affect. So first bitmap_merge_area_size came into my mind, but I'm not sure if it will have no side effects, at least in a DWH env.

At the end, the common disclaimer: I have not tested any of the methods described here, so be warned ;-)

Oracle 10g Performance Analysis with Doug Burns in Vienna

Doug Burns hold a 2 day class in Vienna. The topic was "Oracle 10g Performance Analysis with Doug Burns".
I had the pleasure to attend this class and saw (and of course listen to) Doug.
The course had 3 major parts (from my point of view):

1. General introduction to Tuning like:
   
   • It's all about time (not costs, not ratios, not beauty, ...)
   • the customer is the only who can tell what's good (and must do this, before tuning begins)
   • beware Compulsive tuning disorder
2. the history of the tools:
   
   • bstat/estat
   • wait interface
   • statspack
3. current tools (10g/11g) and new features (11g) - (or why it's all the same, just better)
   
   • ASH
   • AWR
   • ADDM
   • improvements in 11g

That's all I remember (high level) - so if I miss anything substantial, sorry Doug, I must have drifted off (which I have to account my little daughters growing teeth much more than Dough) .

It was the first time to see Dough and it was great. It was full of good examples (many of them known if you follow his blog or others, but that's good!), but he never lost the red line through all his slides.
As he started with some history, he picked me up in a well known and familiar area and guided me to the brave new world, their strenghts and pitfalls.

During all the coffee breaks (and there where enough, even for the smokers) quite refreshing debates arose. Very convenient.

At the end, Doug just finished before time, but I had no feeling he missed something during the past 2 days. So I was happy to use the time for some more  general, not so technical chat.

At all, these 2 days where the perfect recreation from ordinary labour work as a DBA for a geek like me!

dynamic SQL with DMLs

Once again, Christian Antognini is the reason for this entry (beside my curiosity of course).
In Chapter 8 - Parsing of his book Troubleshooting Oracle Performance he showed the advantage of explicit cursor handling. The package dbms_sql shows the most flexibility. Unfortunately he only showed SELECT statements, no DML (which I'm interrested for some reasons). So I had to do the tests myselve:

CREATE TABLE dyn_sql (id number, val varchar2(32));

DECLARE   v_cur_hdl         INTEGER;
  v_stmt_str        VARCHAR2(200);
  v_rows_processed  BINARY_INTEGER;
  gid               INTEGER;
  gval              VARCHAR2(32);
BEGIN   v_stmt_str := 'INSERT INTO dyn_sql VALUES (:gid, :gval)';
  v_cur_hdl := DBMS_SQL.OPEN_CURSOR(2); -- to avoid ORA-29471 in 11g  
  DBMS_SQL.PARSE(v_cur_hdl, v_stmt_str, DBMS_SQL.NATIVE);
  DBMS_SQL.BIND_VARIABLE (v_cur_hdl, ':gid',  1);
  DBMS_SQL.BIND_VARIABLE (v_cur_hdl, ':gval', 'V01C01');
  
  v_rows_processed := DBMS_SQL.EXECUTE(v_cur_hdl);
  
  DBMS_SQL.BIND_VARIABLE (v_cur_hdl, ':gid',  2);
  DBMS_SQL.BIND_VARIABLE (v_cur_hdl, ':gval', 'V02C01');
  v_rows_processed := DBMS_SQL.EXECUTE(v_cur_hdl);
  COMMIT;

  DBMS_SQL.BIND_VARIABLE (v_cur_hdl, ':gid',  3);
  DBMS_SQL.BIND_VARIABLE (v_cur_hdl, ':gval', 'V03C02');

  v_rows_processed := DBMS_SQL.EXECUTE(v_cur_hdl);
  DBMS_SQL.BIND_VARIABLE (v_cur_hdl, ':gid',  4);
  DBMS_SQL.BIND_VARIABLE (v_cur_hdl, ':gval', 'V04C02');
  
  v_rows_processed := DBMS_SQL.EXECUTE(v_cur_hdl);
  ROLLBACK;
  
  DBMS_SQL.BIND_VARIABLE (v_cur_hdl, ':gid',  5);
  DBMS_SQL.BIND_VARIABLE (v_cur_hdl, ':gval', 'V05C03');
  
  v_rows_processed := DBMS_SQL.EXECUTE(v_cur_hdl);
  
  DBMS_SQL.CLOSE_CURSOR(v_cur_hdl);
END;
 /

SQL> select * from dyn_sql;
        ID VAL
---------- ------
         1 V01C01
         2 V02C01
         5 V05C03

blubb
what are my findings?

1. DBMS_SQL works fine also for DMLs.
2. commit/rollback within an open cursor works fine.
3. closing a cursor implicite commits (direct sql-trace sequence: EXEC(insert) - CLOSE(insert) - EXEC(pl/sql) - CLOSE (pl/sql)Deleted after Chris commentar

There is an explicite warning in the documentation: Native Dynamic SQL is faster than DBMS_SQL. I did not test this. But be warned. (and test it yourselve, If you fear performance!)

minimal init.ora

my minimal init.ora has only 4 lines:

*.compatible='11.1.0.0.0'
*.control_files='/appl/oracle/oradata/BERX2/control01.ctl','/appl/oracle/oradata/BERX2/control02.ctl','/appl/oracle/oradata/BERX2/control03.ctl'
*.db_block_size=8192
*.db_name='BERX2'

where I have to check if I can omit the compatible. So it would be down to 3 lines. nice!
Without setting compatible, I get

ORA-00201: control file version 11.1.0.0.0 incompatible with ORACLE version
11.0.0.0.0
ORA-00202: control file: '/appl/oracle/oradata/BERX2/control01.ctl'

and a show parameter compatible gives

compatible                           string      11.0.0

strange, as the instance identifies itselve as

SQL> select version from v$instance;

VERSION
-----------------
11.1.0.7.0

So it seems the automatic value for compatible is derived only from the first Version number of v$instance.

sysstat_sample.sql

Based on Tanel Poders sample.sql and ses.sql I was interrested to get some informations about the System at all.
There are various discussions wether or not it is useful to have a look on the system when hunting a problem within a session. Nevertheless I decided I need such a tool. On the one hand to generate a 'good' baseline, on the other to have a quick, zentralised view. It's also of some use to compare the system-statistics to the values of one dedicated sessions to see if this session is the major reason for any changes or submit only a small amount, but might suffer from others in the system.

So here is the code:

select name,
  min(value)               "MIN",
  max(value)               "MAX",
  max(value) - min(value)  "overall_delta",
  max(delta)               "max_single_delta",
  sum(decode(delta,0,0,1)) "#changes"   -- count distinct 
from
  (with c as
     (select &1 counter from dual)
  select     rn,
 name,
 value,
     decode(rn,1,0,value-prev) delta  
from    ( select /*+ ordered use_nl(t) */
         r.rn,
 NAME,
 Value,
         LAG(VALUE) OVER (Partition BY NAME order by r.rn) prev
      from
        (select /*+ no_unnest */
 rownum rn,
 1 sample_dummy
          from dual
            connect by level <= (select counter from c)        ) r,        v$sysstat t      order by name, r.rn     )  ) syss
where   value     > 0
  and delta > 0
group by name
order by name
/

This piece of code samples through v$sysstat and generates for every entry (where the value > 0, to reduce the lines) a line with the minimal value, the maximal value, the delta between them (this could also be done by DBMS_LOCK.SLEEP) but also the greatest single step between 2 sequent samples.
There might also be other statistical functions of some interrest, but I have too little knowledge on statistics and how to use them (comments/suggestions welcome!).

The script can also be found here, where I will keep the most recent version all the time.

Composite Bitmap Indexes

In hist Book Troubleshoot Oracle Performance Christian Antognini wrote also about Composite Bitmap Indexes. (p400)
Unfortunately he only proclaims

Composite bitmap indexes are rarely created. This is because several indexes can be combined efficiently in order to apply a restriction. To see how powerful bitmap indexes are, let’s look at several queries. (p400)

without givin gany evidence. So I grabbed his scripts (thank you for providing them) and run some testcases (on 11.1.0.6):
I just created this additional bitmap index:

CREATE BITMAP INDEX bx_i_n456 on t (n4, n5, n6);

bitmap AND:

index_combine:

SELECT /*+ index_combine(t i_n4 i_n5 i_n6) */ *
FROM t
WHERE n4 = 6 AND n5 = 42 AND n6 = 11

-----------------------------------------------------------------------------------------------
| Id  | Operation                    | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------
|   1 |  TABLE ACCESS BY INDEX ROWID | T    |      1 |      1 |      1 |00:00:00.01 |       7 |
|   2 |   BITMAP CONVERSION TO ROWIDS|      |      1 |        |      1 |00:00:00.01 |       6 |
|   3 |    BITMAP AND                |      |      1 |        |      1 |00:00:00.01 |       6 |
|*  4 |     BITMAP INDEX SINGLE VALUE| I_N5 |      1 |        |      1 |00:00:00.01 |       2 |
|*  5 |     BITMAP INDEX SINGLE VALUE| I_N6 |      1 |        |      1 |00:00:00.01 |       2 |
|*  6 |     BITMAP INDEX SINGLE VALUE| I_N4 |      1 |        |      1 |00:00:00.01 |       2 |
-----------------------------------------------------------------------------------------------

   4 - access("N5"=42)
   5 - access("N6"=11)
   6 - access("N4"=6)

Cost: 3

composite bitmap index(CIB):

SELECT *
FROM t
WHERE n4 = 6 AND n5 = 42 AND n6 = 11

----------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------
|   1 |  TABLE ACCESS BY INDEX ROWID | T         |      1 |      1 |      1 |00:00:00.01 |       4 |
|   2 |   BITMAP CONVERSION TO ROWIDS|           |      1 |        |      1 |00:00:00.01 |       3 |
|*  3 |    BITMAP INDEX SINGLE VALUE | BX_I_N456 |      1 |        |      1 |00:00:00.01 |       3 |
----------------------------------------------------------------------------------------------------

   3 - access("N4"=6 AND "N5"=42 AND "N6"=11)

Cost:1

In this case the CBI wins.

bitmap OR:

index_combine:

SELECT /*+ index_combine(t i_n4 i_n5 i_n6) */ *
FROM t
WHERE n4 = 6 OR n5 = 42 OR n6 = 11

-----------------------------------------------------------------------------------------------
| Id  | Operation                    | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------
|   1 |  TABLE ACCESS BY INDEX ROWID | T    |      1 |    797 |    767 |00:00:00.01 |     419 |
|   2 |   BITMAP CONVERSION TO ROWIDS|      |      1 |        |    767 |00:00:00.01 |       7 |
|   3 |    BITMAP OR                 |      |      1 |        |      1 |00:00:00.01 |       7 |
|*  4 |     BITMAP INDEX SINGLE VALUE| I_N4 |      1 |        |      1 |00:00:00.01 |       3 |
|*  5 |     BITMAP INDEX SINGLE VALUE| I_N6 |      1 |        |      1 |00:00:00.01 |       2 |
|*  6 |     BITMAP INDEX SINGLE VALUE| I_N5 |      1 |        |      1 |00:00:00.01 |       2 |
-----------------------------------------------------------------------------------------------

   4 - access("N4"=6)
   5 - access("N6"=11)
   6 - access("N5"=42)

 Cost: 135

no hints:

SELECT *
FROM t
WHERE n4 = 6 OR n5 = 42 OR n6 = 11

-----------------------------------------------------------------------------------------------
| Id  | Operation                    | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------
|   1 |  TABLE ACCESS BY INDEX ROWID | T    |      1 |    797 |    767 |00:00:00.01 |     419 |
|   2 |   BITMAP CONVERSION TO ROWIDS|      |      1 |        |    767 |00:00:00.01 |       7 |
|   3 |    BITMAP OR                 |      |      1 |        |      1 |00:00:00.01 |       7 |
|*  4 |     BITMAP INDEX SINGLE VALUE| I_N4 |      1 |        |      1 |00:00:00.01 |       3 |
|*  5 |     BITMAP INDEX SINGLE VALUE| I_N6 |      1 |        |      1 |00:00:00.01 |       2 |
|*  6 |     BITMAP INDEX SINGLE VALUE| I_N5 |      1 |        |      1 |00:00:00.01 |       2 |
-----------------------------------------------------------------------------------------------

   4 - access("N4"=6)
   5 - access("N6"=11)
   6 - access("N5"=42)

Cost:135

index_combine with BX_I_N456: 

SELECT /*+ index_combine(t BX_I_N456 i_n5 i_n6) */ *
FROM t
WHERE n4 = 6 OR n5 = 42 OR n6 = 11   

----------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------
|   1 |  TABLE ACCESS BY INDEX ROWID | T         |      1 |    797 |    767 |00:00:00.04 |     420 |
|   2 |   BITMAP CONVERSION TO ROWIDS|           |      1 |        |    767 |00:00:00.03 |       8 |
|   3 |    BITMAP OR                 |           |      1 |        |      1 |00:00:00.03 |       8 |
|   4 |     BITMAP MERGE             |           |      1 |        |      1 |00:00:00.03 |       4 |
|*  5 |      BITMAP INDEX RANGE SCAN | BX_I_N456 |      1 |        |    527 |00:00:00.01 |       4 |
|*  6 |     BITMAP INDEX SINGLE VALUE| I_N6      |      1 |        |      1 |00:00:00.01 |       2 |
|*  7 |     BITMAP INDEX SINGLE VALUE| I_N5      |      1 |        |      1 |00:00:00.01 |       2 |
----------------------------------------------------------------------------------------------------

   5 - access("N4"=6)
       filter("N4"=6)
   6 - access("N6"=11)
   7 - access("N5"=42)

Cost: 138

index_combine with BX_I_N456 on 2nd place: 

SELECT /*+ index_combine(t i_n4 BX_I_N456 i_n6) */ *
FROM t
WHERE n4 = 6 OR n5 = 42 OR n6 = 11   

----------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------
|   1 |  TABLE ACCESS BY INDEX ROWID | T         |      1 |    797 |    767 |00:00:00.01 |     420 |
|   2 |   BITMAP CONVERSION TO ROWIDS|           |      1 |        |    767 |00:00:00.01 |       8 |
|   3 |    BITMAP OR                 |           |      1 |        |      1 |00:00:00.01 |       8 |
|   4 |     BITMAP MERGE             |           |      1 |        |      1 |00:00:00.01 |       4 |
|*  5 |      BITMAP INDEX RANGE SCAN | BX_I_N456 |      1 |        |    527 |00:00:00.01 |       4 |
|*  6 |     BITMAP INDEX SINGLE VALUE| I_N6      |      1 |        |      1 |00:00:00.01 |       2 |
|*  7 |     BITMAP INDEX SINGLE VALUE| I_N5      |      1 |        |      1 |00:00:00.01 |       2 |
----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   5 - access("N4"=6)
       filter("N4"=6)
   6 - access("N6"=11)
   7 - access("N5"=42)

Costs: 138

in this case, the 3 seperated bitmap indices wins.

NOT EQUAL AND:

index_combine:

SELECT /*+ index_combine(t i_n4 i_n5 i_n6) */ *
FROM t
WHERE n4 != 6 AND n5 = 42 AND n6 = 11   

-----------------------------------------------------------------------------------------------
| Id  | Operation                    | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------
|*  1 |  TABLE ACCESS BY INDEX ROWID | T    |      1 |      1 |      1 |00:00:00.01 |       6 |
|   2 |   BITMAP CONVERSION TO ROWIDS|      |      1 |        |      2 |00:00:00.01 |       4 |
|   3 |    BITMAP AND                |      |      1 |        |      1 |00:00:00.01 |       4 |
|*  4 |     BITMAP INDEX SINGLE VALUE| I_N5 |      1 |        |      1 |00:00:00.01 |       2 |
|*  5 |     BITMAP INDEX SINGLE VALUE| I_N6 |      1 |        |      1 |00:00:00.01 |       2 |
-----------------------------------------------------------------------------------------------

   1 - filter("N4"<>6)
   4 - access("N5"=42)
   5 - access("N6"=11)

Costs: 2

a different execution plan from Chris' Book, there a BITMAP MINUS was shown in the execution plan.
Maybe a question for a seperated session, where the BITMAP MINUS disappeared.

for some reason, this hint generated the BITMAP MINUS

SELECT /*+ index(t  BX_I_N456) */ *
FROM t
WHERE n4 != 6 AND n5 = 42 AND n6 = 11   

------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------------
|   1 |  TABLE ACCESS BY INDEX ROWID   | T         |      1 |      1 |      1 |00:00:00.01 |      13 |
|   2 |   BITMAP CONVERSION TO ROWIDS  |           |      1 |        |      1 |00:00:00.01 |      12 |
|   3 |    BITMAP MINUS                |           |      1 |        |      1 |00:00:00.01 |      12 |
|   4 |     BITMAP MINUS               |           |      1 |        |      1 |00:00:00.01 |       8 |
|   5 |      BITMAP AND                |           |      1 |        |      1 |00:00:00.01 |       4 |
|*  6 |       BITMAP INDEX SINGLE VALUE| I_N5      |      1 |        |      1 |00:00:00.01 |       2 |
|*  7 |       BITMAP INDEX SINGLE VALUE| I_N6      |      1 |        |      1 |00:00:00.01 |       2 |
|   8 |      BITMAP MERGE              |           |      1 |        |      1 |00:00:00.01 |       4 |
|*  9 |       BITMAP INDEX RANGE SCAN  | BX_I_N456 |      1 |        |    526 |00:00:00.01 |       4 |
|  10 |     BITMAP MERGE               |           |      1 |        |      1 |00:00:00.01 |       4 |
|* 11 |      BITMAP INDEX RANGE SCAN   | BX_I_N456 |      1 |        |    526 |00:00:00.01 |       4 |
------------------------------------------------------------------------------------------------------

   6 - access("N5"=42)
   7 - access("N6"=11)
   9 - access("N4"=6)
  11 - access("N4" IS NULL)

Cost: 4

So I decided to do a slightly different testcase:

index_combine:

SELECT /*+ index_combine(t i_n4 i_n5 i_n6) */ * 
FROM t 
WHERE n4 = 6 and n5 != 42 and n6 = 11

-------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------
|   1 |  TABLE ACCESS BY INDEX ROWID   | T    |      1 |      8 |      7 |00:00:00.01 |      15 |
|   2 |   BITMAP CONVERSION TO ROWIDS  |      |      1 |        |      7 |00:00:00.01 |       8 |
|   3 |    BITMAP MINUS                |      |      1 |        |      1 |00:00:00.01 |       8 |
|   4 |     BITMAP MINUS               |      |      1 |        |      1 |00:00:00.01 |       6 |
|   5 |      BITMAP AND                |      |      1 |        |      1 |00:00:00.01 |       4 |
|*  6 |       BITMAP INDEX SINGLE VALUE| I_N6 |      1 |        |      1 |00:00:00.01 |       2 |
|*  7 |       BITMAP INDEX SINGLE VALUE| I_N4 |      1 |        |      1 |00:00:00.01 |       2 |
|*  8 |      BITMAP INDEX SINGLE VALUE | I_N5 |      1 |        |      1 |00:00:00.01 |       2 |
|*  9 |     BITMAP INDEX SINGLE VALUE  | I_N5 |      1 |        |      1 |00:00:00.01 |       2 |
-------------------------------------------------------------------------------------------------

   6 - access("N6"=11)
   7 - access("N4"=6)
   8 - access("N5"=42)
   9 - access("N5" IS NULL)

Costs: 6

CIB:

SELECT /*+ index(t BX_I_N456) */ * 
FROM t 
WHERE n4 =6 and n5 != 42 and n6 = 11

----------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------
|   1 |  TABLE ACCESS BY INDEX ROWID | T         |      1 |      8 |      7 |00:00:00.01 |      12 |
|   2 |   BITMAP CONVERSION TO ROWIDS|           |      1 |        |      7 |00:00:00.01 |       5 |
|*  3 |    BITMAP INDEX RANGE SCAN   | BX_I_N456 |      1 |        |      7 |00:00:00.01 |       5 |
----------------------------------------------------------------------------------------------------

   3 - access("N4"=6)
       filter(("N6"=11 AND "N5"<>42 AND "N4"=6))

Costs: 107

no hints:

SELECT  * 
FROM t 
WHERE n4 =6 and n5 != 42 and n6 = 11    
    
-----------------------------------------------------------------------------------------------
| Id  | Operation                    | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------
|*  1 |  TABLE ACCESS BY INDEX ROWID | T    |      1 |      8 |      7 |00:00:00.01 |      12 |
|   2 |   BITMAP CONVERSION TO ROWIDS|      |      1 |        |      8 |00:00:00.01 |       4 |
|   3 |    BITMAP AND                |      |      1 |        |      1 |00:00:00.01 |       4 |
|*  4 |     BITMAP INDEX SINGLE VALUE| I_N6 |      1 |        |      1 |00:00:00.01 |       2 |
|*  5 |     BITMAP INDEX SINGLE VALUE| I_N4 |      1 |        |      1 |00:00:00.01 |       2 |
-----------------------------------------------------------------------------------------------

   1 - filter("N5"<>42)
   4 - access("N6"=11)
   5 - access("N4"=6) 

Costs: 4

My a little bit more explicit view of composite bitmap indexes is:

• They can be useful in AND statements:
• even not optimal for OR statements, they can replace the bitmap index which is created only on the first column without high cost increasement
• in NOT EQUAL AND statements they really kill the performance if enfoced.
  

syntax highlighter

I'm bored of playing around with html-tags just to get the code formatted a little bot better. So I'm switching to syntaxhighlighter from alex.gorbatchev As I'm a lazy boy, I used this documentation.

from now on, code should look lihe this:

select sysdate from dual;select sysdate+1 from dual;select sysdate+2 from dual;

Maybe I will rewrite some of my older posts to use this for better readability. - Maybe ;-)


UPDATE1: there is something going wrong, I don't know where the <br> come from. But I will check.
UPDATE2: solution: I must NOT put a newline at the beginning and end of the statements.
UPDATE3: I have to switch to draft blogger for editiong to make it work. strange thing. but the code formatting is worth the change.
UPDATE4: A solution is posted in syntaxhighlighters wiki.

result_cache invalidated within the same transaction

I just stumbled across one sentence about Server Result Cache in Christian Antogninis Troubleshooting Oracle Performance: " ... To guarantee the consistency of the results ... every time that something changes on the objects reference by a query, the cache entries dependent on it are invalidated ..." (p484).

This made me a little bit curious, as other caches can deal with different versions of cached objects.

so here are some tests:

(2 connections to the same instance, no RAC, 11.1.0.6):

Default isolation level:

Session 1:

preparations:

alter session set timed_statistics = true;
   alter session set statistics_level=all;
   alter session set "_rowsource_execution_statistics" = true;
   set serveroutput on
   BEGIN
     IF dbms_result_cache.flush 
  THEN 
    dbms_output.put_line('Flush Successful');
  ELSE 
    dbms_output.put_line('Flush Failure');
  END IF;
  END;
  /
  set serveroutput off
  drop table dob;
  create table dob as select * from dba_objects;  

test:

select /*+ result_cache */ max(OBJECT_ID) from dob;    
 MAX(OBJECT_ID)
 --------------
 74959
 
 SQL_ID  36tj9136q6pxx, child number 1
 -------------------------------------
 select /*+ result_cache */ max(OBJECT_ID) from dob
 Plan hash value: 648181113
 ---------------------------------------------------------------------------------------------------------------------
 | Id  | Operation           | Name                       | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
 ---------------------------------------------------------------------------------------------------------------------
 |   1 |  RESULT CACHE       | bskcp8b45qj3q5ju5uwg5fuscg |      1 |        |      1 |00:00:00.12 |     976 |    209 |
 |   2 |   SORT AGGREGATE    |                            |      1 |      1 |      1 |00:00:00.12 |     976 |    209 |
 |   3 |    TABLE ACCESS FULL| DOB                        |      1 |  69871 |  65761 |00:00:00.07 |     976 |    209 |
 ---------------------------------------------------------------------------------------------------------------------
 
 select /*+ result_cache */ max(OBJECT_ID) from dob;
 MAX(OBJECT_ID)
 --------------
 74959
 
 SQL_ID 36tj9136q6pxx, child number 1
 -------------------------------------
 select /*+ result_cache */ max(OBJECT_ID) from dob
 Plan hash value: 648181113
 --------------------------------------------------------------------------------------------------
 | Id  | Operation           | Name                       | Starts | E-Rows | A-Rows |   A-Time   |
 --------------------------------------------------------------------------------------------------
 |   1 |  RESULT CACHE       | bskcp8b45qj3q5ju5uwg5fuscg |      1 |        |      1 |00:00:00.01 |
 |   2 |   SORT AGGREGATE    |                            |      0 |      1 |      0 |00:00:00.01 |
 |   3 |    TABLE ACCESS FULL| DOB                        |      0 |  69871 |      0 |00:00:00.01 |
 --------------------------------------------------------------------------------------------------

Session 2:

update dob set object_id=74999 where object_id=74959;
1 row updated.

Session 1:

select /*+ result_cache */ max(OBJECT_ID) from dob;
MAX(OBJECT_ID)
--------------
74959

SQL_ID  36tj9136q6pxx, child number 1
-------------------------------------
select /*+ result_cache */ max(OBJECT_ID) from dob
Plan hash value: 648181113
--------------------------------------------------------------------------------------------------
| Id  | Operation           | Name                       | Starts | E-Rows | A-Rows |   A-Time   |
--------------------------------------------------------------------------------------------------
|   1 |  RESULT CACHE       | bskcp8b45qj3q5ju5uwg5fuscg |      1 |        |      1 |00:00:00.01 |
|   2 |   SORT AGGREGATE    |                            |      0 |      1 |      0 |00:00:00.01 |
|   3 |    TABLE ACCESS FULL| DOB                        |      0 |  69871 |      0 |00:00:00.01 |
-------------------------------------------------------------------------------------------------- 

Session 2:

commit;
Commit complete.

Session 1:

select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

MAX(OBJECT_ID)
--------------
74999

SQL_ID  36tj9136q6pxx, child number 1
-------------------------------------
select /*+ result_cache */ max(OBJECT_ID) from dob

Plan hash value: 648181113

------------------------------------------------------------------------------------------------------------
| Id  | Operation           | Name                       | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------------------
|   1 |  RESULT CACHE       | bskcp8b45qj3q5ju5uwg5fuscg |      1 |        |      1 |00:00:00.06 |     976 |
|   2 |   SORT AGGREGATE    |                            |      1 |      1 |      1 |00:00:00.06 |     976 |
|   3 |    TABLE ACCESS FULL| DOB                        |      1 |  69871 |  65761 |00:00:00.01 |     976 |
------------------------------------------------------------------------------------------------------------

as expected - so far.

SET TRANSACTION ISOLATION LEVEL serializable;

flush result cache

Session 1:

select /*+ result_cache */ max(OBJECT_ID) from dob;

MAX(OBJECT_ID)
--------------
75003

SQL_ID  36tj9136q6pxx, child number 1
-------------------------------------
select /*+ result_cache */ max(OBJECT_ID) from dob

Plan hash value: 648181113

------------------------------------------------------------------------------------------------------------
| Id  | Operation           | Name                       | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------------------
|   1 |  RESULT CACHE       | bskcp8b45qj3q5ju5uwg5fuscg |      1 |        |      1 |00:00:00.06 |     976 |
|   2 |   SORT AGGREGATE    |                            |      1 |      1 |      1 |00:00:00.06 |     976 |
|   3 |    TABLE ACCESS FULL| DOB                        |      1 |  69871 |  65761 |00:00:00.01 |     976 |
------------------------------------------------------------------------------------------------------------

select /*+ result_cache */ max(OBJECT_ID) from dob;

MAX(OBJECT_ID)
--------------
75003

SQL_ID  36tj9136q6pxx, child number 1
-------------------------------------
select /*+ result_cache */ max(OBJECT_ID) from dob

Plan hash value: 648181113

--------------------------------------------------------------------------------------------------
| Id  | Operation           | Name                       | Starts | E-Rows | A-Rows |   A-Time   |
--------------------------------------------------------------------------------------------------
|   1 |  RESULT CACHE       | bskcp8b45qj3q5ju5uwg5fuscg |      1 |        |      1 |00:00:00.01 |
|   2 |   SORT AGGREGATE    |                            |      0 |      1 |      0 |00:00:00.01 |
|   3 |    TABLE ACCESS FULL| DOB                        |      0 |  69871 |      0 |00:00:00.01 |
--------------------------------------------------------------------------------------------------

Session 2:

update dob set object_id=75004 where object_id=75003;

Session 1:

select /*+ result_cache */ max(OBJECT_ID) from dob;

MAX(OBJECT_ID)
--------------
75003

SQL_ID  36tj9136q6pxx, child number 1
-------------------------------------
select /*+ result_cache */ max(OBJECT_ID) from dob

Plan hash value: 648181113

--------------------------------------------------------------------------------------------------
| Id  | Operation           | Name                       | Starts | E-Rows | A-Rows |   A-Time   |
--------------------------------------------------------------------------------------------------
|   1 |  RESULT CACHE       | bskcp8b45qj3q5ju5uwg5fuscg |      1 |        |      1 |00:00:00.01 |
|   2 |   SORT AGGREGATE    |                            |      0 |      1 |      0 |00:00:00.01 |
|   3 |    TABLE ACCESS FULL| DOB                        |      0 |  69871 |      0 |00:00:00.01 |
--------------------------------------------------------------------------------------------------

Session 2:

commit;Commit complete.

Session 1:

select /*+ result_cache */ max(OBJECT_ID) from dob;

MAX(OBJECT_ID)
--------------
75003
SQL_ID  36tj9136q6pxx, child number 1
-------------------------------------
select /*+ result_cache */ max(OBJECT_ID) from dob

Plan hash value: 648181113

------------------------------------------------------------------------------------------------------------
| Id  | Operation           | Name                       | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------------------
|   1 |  RESULT CACHE       | bskcp8b45qj3q5ju5uwg5fuscg |      1 |        |      1 |00:00:00.06 |     976 |
|   2 |   SORT AGGREGATE    |                            |      1 |      1 |      1 |00:00:00.06 |     976 |
|   3 |    TABLE ACCESS FULL| DOB                        |      1 |  69871 |  65761 |00:00:00.01 |     976 |
------------------------------------------------------------------------------------------------------------

select cache_id, CACHE_KEY, NAME, status, creation_timestamp, scn, build_time, row_count, scan_count from v$result_cache_objects;

CACHE_ID                       CACHE_KEY                      NAME                                               STATUS     CREATION_TIMEST        SCN BUILD_TIME  ROW_COUNT SCAN_COUNT

bskcp8b45qj3q5ju5uwg5fuscg     d8g29yuwtjtn9d2bcvaykm08fc     select /*+ result_cache */ max(OBJECT_ID) from dob Invalid    08-OCT-08          6102319          6          1          2

Ouch. Even the ISOLATION LEVEL serializable works fine for the Result itselve (75003 all the time), and the SCN of the result is stored somewhere, the result is global invalidated. I guess this is an area for improvement for Oracle.

Update: I got the same results with 11.1.0.7

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.

Oracle Performance books

I got 2 new books:
Cost-Based Oracle Fundamentals by Jonathan Lewis (ISBN:1-59059-636-6)
and
Troubleshooting Oracle Performance by Christian Antognini (ISBN:1-59059-917-9)
Both are worth reading.
I will try to read and understand both of them.
Maybe I will reproduce some of their tests and will try to do my own, and maybe maybe I will post the results here ;-)

interpreting CDP packages (the ugly way)

today I had to interpret CDP packages on our network.
I'm no shell-coder, but sometimes I'm curious and in such situations this script will come out.
In this particular case its output just gives the Switchname, Switchport, VLAN and Duplex settings, but everyone who wants can do a lot more (and better).
the script itselve:

#!/usr/bin/bash

snoopfile="/tmp/snoopy$$.bin"
snoopline="/tmp/snoopy$$.line"
snoop -d $1 -c 1 -vv  -o $snoopfile 'dst 01:00:0c:cc:cc:cc and length > 50'
snoop -i $snoopfile  -x 26 | nawk -F: ' { print $2 } ' | \
cut -b1-41|  sed -e 's/ //g' | nawk 'BEGIN { ORS="" } { print $1 } ' | \
tr [a-z] [A-Z] > $snoopline
instr=`cat $snoopline`
while  [ $instr ]
do
typ=`echo $instr | cut -b1-4`
lhex=`echo $instr | cut -b5-8`
length=$(echo "ibase=16; $lhex*2" | bc)
next=$(echo "ibase=16; $lhex*2+1" | bc)
if [ $length -gt 8 ]
then
texthex=`echo $instr | cut -b9-$length`
else
texthex=""
fi
#  echo "$typ $lhex $texthex"
if [ $typ == "0001" ]
then
printf "Switchname: "
while  [ $texthex ]
do
charhex=`echo $texthex | cut -b1-2`
chardec=$(echo "ibase=16; $charhex" | bc)
printf "%b" `printf '\x%x' $chardec 2>/dev/null`
texthex=`echo $texthex | cut -b3-`
done
echo " "
fi
if [ $typ == "0003" ]
then
printf "Switchport: "
while  [ $texthex ]
do
charhex=`echo $texthex | cut -b1-2`
chardec=$(echo "ibase=16; $charhex" | bc)
printf "%b" `printf '\x%x' $chardec 2>/dev/null`
texthex=`echo $texthex | cut -b3-`
done
echo " "
fi
if [ $typ == "000A" ]
then
echo "VLAN: 0x$texthex $(echo "ibase=16; $texthex" | bc)"
fi
if [ $typ == "000B" ]
then
echo "Duplex: $texthex"
fi
instr=`echo $instr | cut -b$next-`
done
rm $snoopfile $snoopline

Sorry for the line-breaks - you will have to reformat it a little bit (cut, paste & think).

OCM with or without proxy settings

I tried to install and customize OCM version 10.3.

Installation was quite easy on our development system.
Just as I tried to create a response-file (using emocmrsp) for our test and production-systems, I noticed I cannot add proxy-settings to this.

I have to notice the ability to connect directly to *.oracle.com from this development server.

After some searching I created SR:6969566.993 and got the answer:
Starting with OCM 10.3 setupCCR , configCCR and emocmrsp commands does not include a parameter to specify a proxy server and port. However, If the systems these commands run on does not have direct Internet access then you will be automatically prompted to enter the proxy information. So in case you run emocmrsp on a system that does not have direct Internet access then you will be prompted to enter the proxy information and that will be recorded in the responce file also. If now there is no Internet access then you can enter the word NONE when prompted for a proxy and that will instrall it in disconnected mode.

This is a little bit boring, as I will not run a configuration tool on a Prod-Env, but cannot create a working response-file on my development-node.

I created an Enhancement Request BUG:7258715 enable emocmrsp to add proxy-settings to response-file in any case

Shadow process leak on ASM instance when diskspace exhausted (ORA-20)

Once again one of 'my' bugs:
Facts:

• 10.2.0.3
• HP-UX
• archive_log_dest in ASM
• ASM-Diksgroup full

Symptoms:

• ORA-20
• archivelogs doesn't get archived, even the whole backup-subsystem works properly.

Explanation:
If the DG is full he database will try to connect to ASM for every retry of archiver to create archivelog. Even after the attempt to create archivelog fails, this process is not closed.
This will eat up all 'processes' in ASM, so also the DB-process which tries to backup (and delete) the archivelogs can not connect to ASM.
Even if you freed up space in the ASM-DG in the meantime, this problem still persists.

WorkAround:
find OS-process of archiver (ps -ef | grep arc | grep $ORACLE_SID) and kill this process. this will also release all its childs (which holds processes in ASM) and solve the problem.

Reproduce:

1. fill up your diskGroup
2. force archiver to respawn a lot of processes (alter system archive log current;) or wait at least for the factor of the REOPEN parameter of your log_archive_dest_x multiplied with PROCESSES from your ASM.
3. check if there are no more ASM-processes available (e.g. a select name, free_mb from v$asm_diskgroup; will return no rows selected even there are diskgroups available and in use!)
4. free some space in the DiskGroup
5. check again if there are ASM-processes available
6. kill archiver
7. check again

The good newas after all: there is no corruption, just a ugly hang situation.