Ask The Real Tom

The last tree weeks i was hounting a ora-60 deadlock.

The Oracle Supoort claimed it was an application problem.

The Developer stated that it’s impossible an application problem.

Verfrifiend the developers statment proved me to believe him. A row lock was impossibel.

The Trace looked like this:


information may aid in determining the deadlock:
Deadlock graph:
---------Blocker(s)-------- ---------Waiter(s)---------
Resource Name process session holds waits process session holds waits
TX-001c0049-000614c5 92 196 X 85 413 S
TX-00140014-0006def8 85 413 X 92 196 S
session 196: DID 0001-005C-0000000E session 413: DID 0001-0055-00000015
session 413: DID 0001-0055-00000015 session 196: DID 0001-005C-0000000E
Rows waited on:
Session 413: obj - rowid = 000D1ED1 - AADR7RABFAAAxTmAAA
(dictionary objn - 859857, file - 69, block - 201958, slot - 0)
Session 196: obj - rowid = 000D1ED5 - AADR7YABQAAAn43AAA
(dictionary objn - 859861, file - 80, block - 163383, slot - 0)


This deadlock was caused by two update statements.

But if would be a row deadlock the session should wait for an X excluive row lock, not an S sahred lock.

If it would be a classic ITL lock the block would be in the undo tablespace.

On metalink (or the new bad my oracle support) did not help, oracle support could not help.

The nessesary infomation if found here:
http://pjsrandom.wordpress.com/2006/02/28/hunting-deadlocks-part-2/
http://oracledoug.com/serendipity/index.php?/archives/1014-Being-Open-minded.html



Solution:
The table had a initrans of 2 and pct_free of 0
Since I changed that and moved the table the deadlocks are gone.

     SELECT t.OWNER, t.OBJECT_NAME, t.OBJECT_TYPE, t.STATISTIC_NAME, t.VALUE
     FROM v$segment_statistics t
     WHERE t.STATISTIC_NAME = 'ITL waits'
     AND t.VALUE > 0;

igate partitionionirung
=========================

1. Zeitliche partitionierung nach monat
2. partitonierung nach status zum abhängen

Variante 1
============
# list-range partitioning was introduced in 11gR1 as the "Extended Composite Partitioning" new feature.
Open
Zeit
Closed
Zeit

# Tablespace konzept
* managment aufwand
* duplicate / restore
* disk location speed
* compression / reuse space

->
TBSP_OPEN
TBSP_CLOSED_200903
TBSP_CLOSED_200904

Nachteile:
* schlechtere update perfomance wegen row movement

Vorteil
* bessere query perfomance wegen reduziertem i/o
* es können rows mit status closed abgehängt werden

Variante 2
============
Zeit
Open
Closed

Test 1
=========
create user test identified by test;
grant connect to test;
grant resource to test;
connect test/test

create sequence ptest_seq ;
CREATE TABLE ptest
( id             NUMBER
, name           VARCHAR2(60)
, status        VARCHAR2(10)
) PARTITION BY LIST (status)
SUBPARTITION BY RANGE (id)
( PARTITION p_open VALUES ('open')
( SUBPARTITION p_open_small VALUES LESS THAN (8)
, SUBPARTITION p_open_medium VALUES LESS THAN (80)
, SUBPARTITION p_open_high VALUES LESS THAN (MAXVALUE)
)
, PARTITION p_closed VALUES ('closed')
( SUBPARTITION p_closed_small VALUES LESS THAN (5)
, SUBPARTITION p_closed_medium VALUES LESS THAN (50)
, SUBPARTITION p_closed_high VALUES LESS THAN (MAXVALUE)
)
, PARTITION p_default VALUES (DEFAULT)
( SUBPARTITION p_default_small VALUES LESS THAN (10)
, SUBPARTITION p_default_medium VALUES LESS THAN (100)
, SUBPARTITION p_default_high VALUES LESS THAN (MAXVALUE)
)
) ENABLE ROW MOVEMENT;

begin for x in 1..20 loop
insert into ptest (id,status) values (ptest_seq.nextval,'open');
insert into ptest (id,status) values (ptest_seq.nextval,'closed');
end loop;
end;
/
commit;

select count(1) from ptest partition (p_open);
select count(1) from ptest partition (p_closed);

update ptest set status='open' where mod(id,2)=0;
commit;

select count(1) from ptest partition (p_open);
select count(1) from ptest partition (p_closed);

update ptest set status='closed' where mod(id,2)=0;
commit;

-- add subpartition
alter table ptest SPLIT SUBPARTITION p_closed_high AT (120) into (subpartition p_closed_medium2, subpartition p_closed_high);
select count(1) from ptest subpartition (p_closed_medium2);

-- drop subpartition
alter table ptest drop SUBPARTITION p_closed_small;

-- compress partition
alter table ptest move SUBPARTITION p_closed_medium compress;

If you a import a dump on a database without partitioning enabled  you get the error

ORA-00439: feature not enabled: Partitioning

To enable Partitioning shutdown everything in your home and do this.

$ cd $ORACLE_HOME/rdbms/lib
$ make -f ins_rdbms.mk part_on
$ make -f ins_rdbms.mk ioracle

Check it with
 select * from v$option where parameter = 'Partitioning';

Note: This works for 10g and 11g (11.1, 11.2)