Friday, October 30, 2009

How to innocently corrupt a data table block

Add to Technorati Favorites

Ver este articulo en Español

This really happened to me when trying to learn how the INITRANS
attribute works, was very surprising how easily you can have this
transitory error state.

Replicate the error is very straightforward, you just need one table and
4 transactions. It happened on a 10.2.0.4 database.


CREATE TABLESPACE UNIF
DATAFILE '/u01/MASTER/unif.dbf'
SIZE 20M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 64K
SEGMENT SPACE MANAGEMENT AUTO;

create table scott.t2 (c1 number, c2 char(10)) tablespace unif;

Table is created with storage defaults, therefore INITRANS is 2 initially.

insert into scott.t2 (c1, c2) values (0,'TEST me!');

commit;

delete from scott.t2 where c1 = 0;

commit;

See this block dump after the DELETE/COMMIT pair... well, that is also
something to think about too: seems that data is logically erased.
Please observe the 2 ITL (Interested Transaction List) slots, which
corresponds to INITRANS=2.

DFAA3E0 00000000 00000000 00000000 0102012C  [............,...]
DFAA3F0 45540A80 6D205453 20202165 1CE60601 [..TEST me! ....]
Block header dump: 0x01800017
Object id on Block? Y
seg/obj: 0xd580 csc: 0x00.e1c2e itc: 2 flg: E typ: 1 - DATA
brn: 0 bdba: 0x1800011 ver: 0x01 opc: 0
inc: 0 exflg: 0

Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0001.013.000001ac 0x0080b037.01c8.17 --U- 1 fsc
0x0000.000e1ce6
0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc
0x0000.00000000

Now we change the INITRANS attribute for T2, and increment to say 8.

alter table scott.t2 initrans 8;

Another block dump and you can see the new ITL slots are not allocated
right away (a detailed explanation of ITL may be seen at this place
http://advait.wordpress.com/2008/06/16/interested-transaction-list-itl/
and there you can see that this allocation is dynamic).

Now we are going to try something trivial, throw some transactions into
this table and see what happens. For that, we need at least 3 sqlplus
sessions, because ITLs seem to be allocated on a session/block basis
(this to be confirmed on a later article).

Session 1:

insert into scott.t2 (c1, c2) values (1,'TEST y0u!');
commit;

update scott.t2
set c2 = 'TeST You!'
where c1 = 1;
(no commit for the moment, we want all transactions in pending state)

Session 2:
insert into scott.t2 (c1, c2) values (2,'kiSS him!');
(no commit for the moment, same as above session)

Session 3:
insert into scott.t2 (c1, c2) values (4,'TeaCH DeY!');
(no commit for the moment, same as above session)

What our knowledge and logic may tell us, is that up to now the database
should have created a new ITL slot, counting 3.
However this data block dump shows us a distinct reality:
1) The database engine didn't create a new ITL slot
2) It did the 3rd insert on same block as the other transactions
3) A careful look to the dump, shows that the block appears to be
"corrupted"... this is the shocking part.

buffer tsn: 7 rdba: 0x01800016 (6/22)
scn: 0x0000.000e1cad seq: 0x01 flg: 0x04 tail: 0x1cad0601
frmt: 0x02 chkval: 0x89f7 type: 0x06=trans data
Hex dump of corrupt header 3 = CHKVAL
Dump of memory from 0x0DA66400 to 0x0DA66414
DA66400 0000A206 01800016 000E1CAD 04010000 [................]
DA66410 000089F7 [....]
Hex dump of corrupt block <<--
Dump of memory from 0x0DA66414 to 0x0DA683FC
DA66410 00000001 0000D580 000E1C2E [............]
DA66420 00000000 00320002 01800011 0001000A [......2.........]
DA66430 000001BC 00800AB7 001302B1 00000001 [................]
DA66440 00000000 00000000 00000000 00000000 [................]
Repeat 1 times
DA66460 00000000 00010100 0014FFFF 1F731F87 [..............s.]
DA66470 00001F73 1F870001 00000000 00000000 [s...............]
DA66480 00000000 00000000 00000000 00000000 [................]
Repeat 501 times
DA683E0 00000000 00000000 2C000000 C1020201 [...........,....]
DA683F0 65540A05 20484361 21596544 [..TeaCH DeY!]
Dump of memory from 0x0DA66414 to 0x0DA683FC
DA66410 00000001 0000D580 000E1C2E [............]
DA66420 00000000 00320002 01800011 0001000A [......2.........]
DA66430 000001BC 00800AB7 001302B1 00000001 [................]
DA66440 00000000 00000000 00000000 00000000 [................]
Repeat 1 times
DA66460 00000000 00010100 0014FFFF 1F731F87 [..............s.]
DA66470 00001F73 1F870001 00000000 00000000 [s...............]
DA66480 00000000 00000000 00000000 00000000 [................]
Repeat 501 times
DA683E0 00000000 00000000 2C000000 C1020201 [...........,....]
DA683F0 65540A05 20484361 21596544 [..TeaCH DeY!] -- 3rd Trx
buffer tsn: 7 rdba: 0x01800017 (6/23)
scn: 0x0000.000e219d seq: 0x01 flg: 0x00 tail: 0x219d0601
frmt: 0x02 chkval: 0x0000 type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x0DA66400 to 0x0DA68400
DA66400 0000A206 01800017 000E219D 00010000 [.........!......]
DA66410 00000000 00000001 0000D580 000E2163 [............c!..]
DA66420 00000000 00320002 01800011 000E0002 [......2.........]
DA66430 000001E5 0080001E 0009028F 00000001 [................]
DA66440 00000000 00000004 000001B0 008017B6 [................]
DA66450 000701D2 00000001 00000000 00000000 [................]
DA66460 00000000 00030100 00180000 1F5E1F66 [............f.^.]
DA66470 00001F5E FFFF0003 1F661F77 00000000 [^.......w.f.....]
DA66480 00000000 00000000 00000000 00000000 [................]
Repeat 499 times
DA683C0 00000000 00000000 022C0000 03C10202 [..........,.....]
DA683D0 53696B0A 69682053 2C20216D C1020201 [.kiSS him! ,....] -- 2nd Transaction
DA683E0 65540A02 59205453 2021756F 0102023C [..TeST You! <...] -- 1st Transaction
DA683F0 45540A80 6D205453 20202165 219D0601 [..TEST me! ...!] -- dead row
Block header dump: 0x01800017
Object id on Block? Y
seg/obj: 0xd580 csc: 0x00.e2163 itc: 2 flg: E typ: 1 - DATA
brn: 0 bdba: 0x1800011 ver: 0x01 opc: 0
inc: 0 exflg: 0

Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0002.00e.000001e5 0x0080001e.028f.09 ---- 1 fsc
0x0000.00000000
0x02 0x0004.000.000001b0 0x008017b6.01d2.07 ---- 1 fsc
0x0000.00000000

data_block_dump,data header at 0xda66464
===============

At this point, we may ask ourselves 'Where is the rdbms keeping track of
ITL for the last transaction?'
... we need to get that answer.

This needed a confirmation which tried to found with
DBMS_REPAIR.CHECK_OBJECT , however everything looked just fine

SQL> set serveroutput on
declare
v_corrupted_blocks number;
begin
dbms_repair.check_object('SCOTT','T2', object_type =>
dbms_repair.table_object,
repair_table_name=>'REPAIR_TABLEAU',corrupt_count=>v_corrupted_blocks);
dbms_output.put_line('Corrupted Blocks = '||v_corrupted_blocks);
end;
/
SQL> 2 3 4 5 6 7
Corrupted Blocks = 0

PL/SQL procedure successfully completed.


Confused I've tried a second block dump for same table, and surprisingly
this time no corruption was present, no new ITL slot was created but
instead database used another block for last transaction.

buffer tsn: 7 rdba: 0x01800016 (6/22)
scn: 0x0000.000e222b seq: 0x01 flg: 0x04 tail: 0x222b0601
frmt: 0x02 chkval: 0x3930 type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x0D26B400 to 0x0D26D400
...
D26B480 00000000 00000000 00000000 00000000 [................]
Repeat 501 times
D26D3E0 00000000 00000000 2C000000 C1020201 [...........,....]
D26D3F0 65540A05 20484361 21596544 222B0601 [..TeaCH DeY!..+"]
Block header dump: 0x01800016
Object id on Block? Y
seg/obj: 0xd580 csc: 0x00.e1c2e itc: 2 flg: E typ: 1 - DATA
brn: 0 bdba: 0x1800011 ver: 0x01 opc: 0
inc: 0 exflg: 0

Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x000a.001.000001bc 0x00800ab7.02b1.13 ---- 1 fsc
0x0000.00000000
0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc
0x0000.00000000

data_block_dump,data header at 0xd26b464
===============

buffer tsn: 7 rdba: 0x01800017 (6/23)
scn: 0x0000.000e219d seq: 0x01 flg: 0x04 tail: 0x219d0601
frmt: 0x02 chkval: 0x5eaa type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x0D26B400 to 0x0D26D400
...
D26B480 00000000 00000000 00000000 00000000 [................]
Repeat 499 times
D26D3C0 00000000 00000000 022C0000 03C10202 [..........,.....]
D26D3D0 53696B0A 69682053 2C20216D C1020201 [.kiSS him! ,....]
D26D3E0 65540A02 59205453 2021756F 0102023C [..TeST You! <...]
D26D3F0 45540A80 6D205453 20202165 219D0601 [..TEST me! ...!]
Block header dump: 0x01800017
Object id on Block? Y
seg/obj: 0xd580 csc: 0x00.e2163 itc: 2 flg: E typ: 1 - DATA
brn: 0 bdba: 0x1800011 ver: 0x01 opc: 0
inc: 0 exflg: 0

Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0002.00e.000001e5 0x0080001e.028f.09 ---- 1 fsc
0x0000.00000000
0x02 0x0004.000.000001b0 0x008017b6.01d2.07 ---- 1 fsc
0x0000.00000000

data_block_dump,data header at 0xd26b464
===============

Try to replicate this on your environments and share with us your
release and behavior. Note: I tried this on 11g2 and at least no block
corruption showed up.

Thank you for your time, and see you next time


Subscribe to Oracle Database Disected by Email

Follow IgnacioRuizMX on Twitter

Delicious
Bookmark this on Delicious

Monday, October 19, 2009

Latest storage Adventure

Add to Technorati Favorites

Ver este articulo en Español

Datamarts/Datawarehouse performance issues can be scaring events, taking into account the massive amount of data they handle; the tuning task is even more daunting if they're used for operational day-to-day decision making.

During the last week and days, I've been involved on a 15 Tb datamart with performance issues on dataloading, not querying. Tablespaces for indexes and data share filesystems, nowadays this is not a concern anymore, however I prefer to encourage customers to allocate independent storage for each type, that way they are able to discriminate the source of IO.

They have concurrent schedules for dataloading processes and parallelism degree for tables and indexes involved; a very good meassure to speed up data loading is to use /*+ APPEND */ hint on INSERT ... AS SELECT, which they're using also with PARALLEL. The parallelism has a multiplying factor and if you've experienced the downsides of it you may be suspecting when the wall is going to be hitted: at the storage level.

I generated a heat map for filesystems and focusing on write statistics found the correlation expected: very high average write times on filesystems starting at the time dataloading processes were scheduled... huge avg times around seconds!

After a quick chat with the Sysadmin and Storage guys, something very important arose: the striping factor for all those overheated filesystems was 2, said in other words: "storage you have, performance ... what's that?" That is a common mistake and results from working on almost isolated process, then is when I say: "Guys, you need to talk to each other, learn the jargon, the working of the "machines" the other teams deal with... end even the inner workings, why not?!"

After identified a set of objects that participated heavily on the filesystem overheating, brand new filesystems with striping factor of 8 were allocated and new tablespaces created to receive this objects. As expected, just the maintenance task of moving all tables/partitions/subpartitions took a breeze. Timings for uploading processes even on high load days improved dramatically... and all this for a subtle "configuration number" on the OS storage options.


Subscribe to Oracle Database Disected by Email
Delicious
Bookmark this on Delicious

Monday, June 15, 2009

Resuming and the search for the 4Mb lost

Add to Technorati Favorites

Ver este articulo en Español

It's been a while this place has been quiet, not anymore. I changed jobs on February and was on a stabilization process, a very long one if you like, or at least for me.

I missed very much the moment to face the keyboard and share something to you, however since today I'm committing to thoroughly give an update every 2 weeks, at least.

Have you been through the Oracle Server startup process, step by step? Here is an excercise for you:

As you may know, we already have an instance, background processes and memory, after having the database started up in NOMOUNT state. If you don't believe me do the test, best if you try this on a Unix/Linux environment.

1) startup nomount at SQL*Plus prompt.
2) on another OS shell, issue a ps -ef | grep ora_ you'll see all background processes started.
3) now issue an ipcs -m and you'll see all shared memory segments allocated to Oracle server.

Take note the amount of memory allocated for oracle, write down this figure, we'll need it.

Now go all the way and manually go thru all steps of database startup, issue following commands on SQL*Plus.
1) alter database mount
2) alter database open

Now you have all memory allocated to Oracle server, this internally reported set of information provides a grand total labeled "Total System Global Area". If you convert the memory reported by ipcs and this one to bytes, you'll see we have missing 4Mb.

It happens the same on your databases? Somebody has the answer for this result?
I did this test under 10.2.0.4 and on Linux, this behavior may not be present on another releases and/or platforms; please, share with us your results on a comment.

I want to thank you for giving me your attention, hope to see you real soon.

Subscribe to Oracle Database Disected by Email
Delicious
Bookmark this on Delicious
Custom Search