Tuesday, April 27, 2010

Update under the hood ... and some extreme side effects

Add to Technorati Favorites

Ver este artículo en Español

At first glance, this topic may look trivial, but may be the source for severe performance issues, unnecessary maintenance tasks and/or storage waste.

Yes, you can say "update is so basic, why I should care about it?!", well it may be some cases when using update without care, things may get ugly. I found it playing with my old friend ALTER SYSTEM DUMP BLOCK and a few inserts and updates, over a table with a few columns, two of them VARCHAR2 type.

This was done on an Oracle 10.2.0.4 release database, let's see how it goes

create table table5 (col1 number, col2 varchar(10), col3 varchar(46));

insert into table5 (col1,col2,col3) values (1,'row one','This is the first row');

commit;

insert into table5 (col1,col2,col3) values (2,'row two','This is the 2nd row,updates will be Committed!');

commit;
alter session set tracefile_identifier='table5_04_b103';
alter system dump datafile 6 block 103;

I strongly recommend using parameter TRACEFILE_IDENTIFIER, because helps you differentiate your trace quickly.

Initial state dump for block where rows were inserted:

CF8F390 C1023320 01FFFF02 C102FF80 0203022C [ 3..........,...]
CF8F3A0 720703C1 7420776F 542D6F77 20736968 [...row two-This ]
CF8F3B0 74207369 32206568 7220646E 752C776F [is the 2nd row,u]
CF8F3C0 74616470 77207365 206C6C69 43206562 [pdates will be C]
CF8F3D0 696D6D6F 21646574 0203012C 720702C1 [ommitted!,.....r]
CF8F3E0 6F20776F 5415656E 20736968 74207369 [ow one.This is t]
CF8F3F0 66206568 74737269 776F7220 14A30601 [he first row....]
Block header dump: 0x01800067
...
0xe:pti[0] nrow=2 offs=0
0x12:pri[0] offs=0x1f74
0x14:pri[1] offs=0x1f38
block_row_dump:
tab 0, row 0, @0x1f74
tl: 36 fb: --H-FL-- lb: 0x1 cc: 3
col 0: [ 2] c1 02
col 1: [ 7] 72 6f 77 20 6f 6e 65
col 2: [21]
54 68 69 73 20 69 73 20 74 68 65 20 66 69 72 73 74 20 72 6f 77
tab 0, row 1, @0x1f38
tl: 60 fb: --H-FL-- lb: 0x2 cc: 3
col 0: [ 2] c1 03
col 1: [ 7] 72 6f 77 20 74 77 6f
col 2: [45]
54 68 69 73 20 69 73 20 74 68 65 20 32 6e 64 20 72 6f 77 2c 75 70 64 61 74
65 73 20 77 69 6c 6c 20 62 65 20 43 6f 6d 6d 69 74 65 64 21
end_of_block_dump
End dump data blocks tsn: 7 file#: 6 minblk 103 maxblk 103

It shows our two records: starting bottom with first row; on top of it, second row (that's why a 'regular' Oracle table is properly named HEAP ORGANIZED TABLE)

Now let's issue some updates on row #2, column 2 (col2) will change value from 'row two' and 'row two-02' to 'row two-25'

update table5b set col2 = 'row two-02' where col1 = 2;
...
update table5b set col2 = 'row two-25' where col1 = 2;


CF8F350 C3041100 FF373005 1702C102 03012C2F [.....07...../,..]
CF8F360 0A03C102 20776F72 2D6F7774 542D3532 [....row two-25-T]
CF8F370 20736968 74207369 32206568 7220646E [his is the 2nd r]
CF8F380 752C776F 74616470 77207365 206C6C69 [ow,updates will ]
CF8F390 43206562 696D6D6F 21646574 0203002C [be Commited!,...]
CF8F3A0 720703C1 7420776F 542D6F77 20736968 [...row two-This ]
CF8F3B0 74207369 32206568 7220646E 752C776F [is the 2nd row,u]
CF8F3C0 74616470 77207365 206C6C69 43206562 [pdates will be C]
CF8F3D0 696D6D6F 21646574 0203002C 720702C1 [ommited!,......r]
CF8F3E0 6F20776F 5415656E 20736968 74207369 [ow one.This is t]
CF8F3F0 66206568 74737269 776F7220 14F10602 [he first row....]
Block header dump: 0x01800067
...
0xe:pti[0] nrow=2 offs=0
0x12:pri[0] offs=0x1f74
0x14:pri[1] offs=0x1ef9
block_row_dump:
tab 0, row 0, @0x1f74
tl: 36 fb: --H-FL-- lb: 0x0 cc: 3
col 0: [ 2] c1 02
col 1: [ 7] 72 6f 77 20 6f 6e 65
col 2: [21]
54 68 69 73 20 69 73 20 74 68 65 20 66 69 72 73 74 20 72 6f 77
tab 0, row 1, @0x1ef9
tl: 63 fb: --H-FL-- lb: 0x1 cc: 3
col 0: [ 2] c1 03
col 1: [10] 72 6f 77 20 74 77 6f 2d 32 35
col 2: [45]
54 68 69 73 20 69 73 20 74 68 65 20 32 6e 64 20 72 6f 77 2c 75 70 64 61 74
65 73 20 77 69 6c 6c 20 62 65 20 43 6f 6d 6d 69 74 65 64 21
end_of_block_dump

Changing col2 from 'row two' to 'row two-02', required to duplicate the whole row... of course, the register with col2='row two' was logically deleted, remaining in the block.
Is this a dead record? yes, it's a dead record...

The 24 consecutive updates, didn't duplicate any information ... sorry to disappoint you if expected to see duplication in this case. But what happened? Seems that Oracle update algorithm has intelligence to identify that old and new values have the same length, then simply updating col2 on the spot. Update on CHAR columns (fixed length datatypes) behave the same way.

Ok, now let's try modifying length of updated value between transactions. First alternate updates on col2 and col3:

update table5 set col2 = 'row two-38' where col1 = 2;
commit;

update table5 set col3 = 'Now update the second row, shorter text!' where col1 = 2;
commit;

update table5 set col2 = 'row 2-39' where col1 = 2;
commit;

update table5 set col3 = 'Now re-update the second row, another text!' where col1 = 2;
commit;

update table5 set col2 = 'row two-40' where col1 = 2;
commit;

update table5 set col3 = 'Now re-re-update the second row, another text!' where col1 = 2;
commit;

update table5 set col2 = 'row 2-41' where col1 = 2;
commit;

update table5 set col3 = 'Now re-re-re-update the second row, will fit?' where col1 = 2;
commit;

update table5 set col2 = 'row two-42' where col1 = 2;
commit;

update table5 set col3 = 'Now re-re-re-re-update the 2nd row, changed?' where col1 = 2;
commit;

update table5 set col2 = 'row two-43' where col1 = 2;

and later, updates only on col2...

update table5 set col2 = 'row two-44' where col1 = 2;
commit;

update table5 set col2 = 'row 2-45' where col1 = 2;
commit;

update table5 set col2 = 'row two-46' where col1 = 2;
commit;

update table5 set col2 = 'row 2-47' where col1 = 2;
commit;

update table5 set col2 = 'row two-48' where col1 = 2;
commit;

update table5 set col2 = 'row 2-49' where col1 = 2;

And see how looks now the block dump, suddenly the block is filled like we issued inserts instead of updates, strange to say the least

D84D000 61623763 4E5F3163 2C6F6765 C1020302 [c7bac1_Nego,....]
D84D010 6F720803 2D322077 4E2C3934 7220776F [..row 2-49,Now r]
D84D020 65722D65 2D65722D 752D6572 74616470 [e-re-re-re-updat]
D84D030 68742065 6E322065 6F722064 63202C77 [e the 2nd row, c]
D84D040 676E6168 2C3F6465 C1020300 6F720A03 [hanged?,......ro]
D84D050 77742077 38342D6F 776F4E2C 2D657220 [w two-48,Now re-]
D84D060 722D6572 65722D65 6470752D 20657461 [re-re-re-update ]
D84D070 20656874 20646E32 2C776F72 61686320 [the 2nd row, cha]
...... 46 rows not shown .....
D84D360 0A03C102 20776F72 2D6F7774 542D3833 [....row two-25-T]
D84D370 20736968 74207369 32206568 7220646E [his is the 2nd r]
D84D380 752C776F 74616470 77207365 206C6C69 [ow,updates will ]
D84D390 43206562 696D6D6F 21646574 0203002C [be Commited!,...]
D84D3A0 720703C1 7420776F 542D6F77 20736968 [...row two-This ]
D84D3B0 74207369 32206568 7220646E 752C776F [is the 2nd row,u]
D84D3C0 74616470 77207365 206C6C69 43206562 [pdates will be C]
D84D3D0 696D6D6F 21646574 0203002C 720702C1 [ommited!,......r]
D84D3E0 6F20776F 5415656E 20736968 74207369 [ow one.This is t]
D84D3F0 66206568 74737269 776F7220 61470602 [he first row..Ga]
Block header dump: 0x01800067

Seems that the block contains a lot of records, but look at the block header nrow figures: block contains 2 live rows, and everything else is logically deleted, dead... space wasted.

ntab=1
nrow=2
frre=-1
fsbo=0x16
fseo=0x1ba7
avsp=0x1f20
tosp=0x1f22
0xe:pti[0] nrow=2 offs=0
0x12:pri[0] offs=0x1f74
0x14:pri[1] offs=0x1ba7

Yep, this is something you must take care of... conclusion: you cannot take lightly update operations on variable length fields.

Remember there is always a trade-off, in this case update algorithm for variable length update is optimized for performance, which is one of Oracle's virtues & pillar.

It's desirable to prevent this behavior at the design or programing stages, avoiding repeating updates over same record or making a weighted decision over CHAR, meaning a fixed and constant waste of space versus VARCHAR with this corner case.

If preventive solution is not possible, then Oracle provides ALTER TABLE instructions to cope with this and give maintenance to table, removing this "dead rows".

Quiz: ALTER TABLE {table} SHRINK SPACE or ALTER TABLE {table} SHRINK SPACE COMPACT are our saving sentences?
Which pre-requisite have in order to use any of them?
Are they interchangeable or what is the behavior distinction?

Thanks for your time reading today, I'll engage next time with updates on tables having INDEXES...

Subscribe to Oracle Database Dissected by Email

Follow IgnacioRuizMX on Twitter

Delicious Bookmark this on Delicious

Monday, April 12, 2010

Starting Oracle Coherence 3.5 book review

Add to Technorati Favorites

Ver este articulo en Español

Past Friday, Packt Publishing gave me a copy of his newest "Oracle Coherence 3.5" book, which you can find clicking the book cover image below.



Coherence is a Middleware product from Oracle which main purpose is being a high performance Cache on the 2nd tier, thus improving performance reducing unnecessary traffic or requests to the database server. On next weeks my comments on this book...




Subscribe to Oracle Database Disected by Email

Follow IgnacioRuizMX on Twitter

Delicious
Bookmark this on Delicious

Saturday, April 10, 2010

Oracle Exadata/Database Machine... nice to meet you

Add to Technorati Favorites

Ver este articulo en Español

During the last four or five weeks I've had the rare privilege of working with this amazing piece of engineering, the Oracle Database Machine.

Engaged during deployment of it, I've faced the setup steps on the SW side, applied upgrade patches & side-to-side with Oracle Sun engineers bringing this marvel to life... it's just exciting, watching all the slightly-bluish-green leds showing HD activity or admiring the most precise cabling I've ever seen... there is just one word: precision. I remember my first experience with an HP XP12000 SAN, five years ago, being not that exciting as this recent experience.

Few months ago, started my preparation taking a web seminar for Oracle Exadata, appeared to me quite revolutionary the way it works, had to admit that first perception quickly got surpassed once started working directly with this beauty.

A new paradigma is needed when working with Exadata, it's clear the tuning role will change, one (for instance) that may consider an index useless or even harmful.

Please stay with me on this exciting journey, I'll try to share my experience on this as much as I can. Thank you for your attention.

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

Friday, April 2, 2010

Oracle 11g Streams Implementer's Guide - Highlights - Part I

Add to Technorati Favorites

Ver este articulo en Español

I finished reading this very nice book on Oracle Streams... yes, the one I shared you weeks ago (See post My first assignment on book reviews). Now as promised, getting back to you and share my comments on the book, starting with those chapters that clearly make the difference against other Oracle Streams books on the market, those which are more like "rephrasing" the manual.

Chapter 2 and Chapter 3 provide the foundation for success on implementing Streams, this is the Analysis and Design phase that is often overlooked ... but pays high rewards to those who comply with it!!!

Particularly those chapters provide you with a practical ready-to-use framework, if you may tweak it to fit your needs, that is your call.

Chapter 8 is packed with a useful list of recipes for change, like adding a new replication site, detailed with full steps. Also recommendations to keep an eye on when changes are implemented. However, I liked most the troubleshooting cases, very handy BTW, which authors shown mapped against the replication process diagram... that is cool!!!

Wait for final part next week, this Transaction is not committed yet!

Subscribe to Oracle Database Disected by Email

Follow IgnacioRuizMX on Twitter

Delicious
Bookmark this on Delicious
Custom Search