Mittwoch, 31. Dezember 2008

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

Sonntag, 28. Dezember 2008

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.

Mittwoch, 24. Dezember 2008

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 )

Donnerstag, 18. Dezember 2008

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().

Mittwoch, 17. Dezember 2008

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.

Dienstag, 16. Dezember 2008

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!

Mittwoch, 26. November 2008

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

Dienstag, 25. November 2008

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.

Freitag, 7. November 2008

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.

Freitag, 10. Oktober 2008

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.

Mittwoch, 8. Oktober 2008

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

Montag, 22. September 2008

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!

Freitag, 19. September 2008

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.

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

Mittwoch, 30. Juli 2008

Logitech Harmony Remote

Recently I was so bored by all the different remotes for all different devices laying around, I went off to my next store and bought a Logitech Harmony Remote 555. It's a quite nice device and helped me to put away all other remotes by programming this one.
Like all devices, there is also here a negative point: The Software on my PowerBook didn't start, it just complained: The application configuration was not loaded. Reinstall the software or contact customer service.
The problem is based on the case sensitive filesystem and as I doesn#t want to change my entire Harddisk into a case insensitive one, I followed the hint from the forum:
  • create a disk image with 'disk utility', make sure it's not case sensitive. About 100 megabyte is enough

  • move the '/Applications/remotecore.app' and '/Applications/Logitech Harmony Remote Software.app' applications to that disk image

  • create a symbolic link of remotecore.app to '/Applications/remotecore.app'
    So if you mounted your created volume 'Logitech' you can create a symbolic link with:
    $ ln -s /Volumes/Logitech/remotecore.app /Applications/remotecore.app

  • make sure the image is mounted and start the 'Logitech Harmony Remote Software' located in that created volume.

Update:
Version 7.5 is out, and it works well so far. (just tested minimal)

Dienstag, 29. Juli 2008

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...

Sonntag, 27. Juli 2008

Oracle Server Architecture - pdf

I've found a nice PDF (created by Lars Ditzel) which describes an Oracle Server from a file/process and memory point of view.
even it's not that new, it's more or less correct.
(maybe I will try to find the deltas to a current 11g instance with all its flavours)

Freitag, 25. Juli 2008

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.

Freitag, 18. Juli 2008

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

Donnerstag, 17. Juli 2008

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

Dienstag, 15. Juli 2008

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.

Montag, 14. Juli 2008

10g RDBMS vs. 11g ASM

I tried to create a small test-env to compare oracle 10G and 11G.
My setup:
11G ASM (11.1.0.6)
11G DB (11.1.0.6)
10G DB (10.2.0.4)

Both DBs a re using their own DiskGroup within ASM, called according to their SID.

now at the 'CREATE DATABASE' i get:
ORA-00200: control file could not be created
ORA-00202: control file: '+TEST10G_DG'
ORA-17502: ksfdcre:4 Failed to create file +TEST10G_DG
ORA-15001: diskgroup "TEST10G_DG" does not exist or is not mounted
ORA-15204: database version 10.2.0.3.0 is incompatible with diskgroup TEST10G_DG

I also checked the Known Issues from 10.2.0.4 Patch Set - Availability and Known Issues, but with no results.

Once again, this leads to a new ServiceRequest (only visible for my CSI - sorry).

Reason/Solution:

dbca sets 'compatible=10.2.0.3' in the init.ora it uses to create the DB. this was the source of the problem.
By changing this Value to 10.2.0.4, everything is fine now.

Freitag, 11. Juli 2008

ASM doesn't see block devices any longer since 10.2.0.4

according to BUG:7120987 - base.BUG:6454631 from Version 10.2.0.4 onwards ASM does no longer see any block devices. I also tested and reproduced this in 11.1.0.6. Use character devices instead.
Only on Linux, you can still use block devices.
this doesn't seem to be rated as bug, more a kind of feature. so don't expect this to change back again.
This is also described in Note:601591.1 - After Upgrading to 10.2.0.4 or 11.1.0.6 Unable to discover disks using block devices
and marked as expected behavior.

Oracle SIB

I found an intresting SIB in Oracle clusterware.
Why SIB? IT's not a BUG, it's not a Docu-BUG, nevertheless it's not working and I'm advised to use my WorkAround ( a simple patch to some files). So it must be SomethingInBetween. I wasn't aware Oracle has such a status, but yes, it's there.

Summary:
On an Oracle- RAC Cluster on Solaris, you can not bring up a VIP on an interface, if you can not reach the default-GW via this interface. (If there is enough traffic on that network, it might or might not work, depending on the traffic during the probe-phase).

The problem is based on the way oracle checks if the interface is healthy: it first checks the packages in/out on that If, then tries to create some network-traffic using ping -I (Linux) or ping -i (solaris) and checks the packages again. If the numbers differ, it assumes the interface is good.

Unfortunately the -I (Linux) and -i (Solaris) differ a little bit:

Linux:
-I interface address
Set source address to specified interface address. Argument may be numeric IP address or name of device. When pinging IPv6 link-local address this option is required.

Solaris:
-i interface_address
Specify the outgoing interface address to use for multicast packets for IPv4 and both multicast and unicast packets for IPv6. The default interface address for multicastpackets is determined from the (unicast) routing tables.
interface_address can be a literal IP address, for example, 10.123.100.99, or an interface name, for example, eri0, or an interface index, for example 2.

Links:
my BUG:6839000 - Status: Not a Bug. To Filer - Base BUG:6332776 - Status: Closed, Not a Bug
my enhancement-request:6969925- not published - Status-code 15 (To Internal (Oracle) Review)