Tuesday, November 30, 2010

My Steps to Implement Exadata EHCC

Add to Technorati Favorites


Ver este articulo en EspañolSubscribe to Oracle Database Disected by Email

Last time I was engaged with an Exadata migration, customer asked me about EHCC and how to implement it for their datamarts.

My approach (at that time) consisted on the following:
1) Get a sample of very big objects to play with
2) Used DBMS_COMPRESSION in order to get estimations for compression rates
3) Try every compression type: Basic, Query Low, Query High, Archive Low, Archive High and recorded size reductions, time to import and time for FTS.
4) Recorded and compared timings for various batch processes and important queries

With all those statistics, went to the customer and then he was able to take a decision based on nature of data, popular timeframe of querying within the data window, parallel degree, partitioning defined (BTW customer already had it on place, best practice!), and so on.

I've used Datapump to import because it uses direct path load and Basic compression needs it to kick in.

Further thinking on this matter resulted on proposals to include table usage (V$SEGMENT_STATISTICS) & table data dynamics (Monitoring+DBA_TAB_MODIFICATIONS) in order to fine tune the compression mode selected. The next time I've the opportunity to include this on the process, I'll share with you the results...

Thank you very much for your time, and if you liked this post please share with others freely...

Follow IgnacioRuizMX on Twitter

Delicious Bookmark this on Delicious

Tuesday, November 23, 2010

From 3Tb to 1.5Tb Using standard COMPRESS and PCTFREE

Add to Technorati Favorites

Ver este articulo en Español

I've been working on a migration project, with very aggressive dates and a couple of requirements that increase difficulty: switch from filesystem to ASM, using 50% of new storage... yes, you got the right picture, we don't have full set of disks for ASM to fit all data, therefore I need to move data trying to release complete datafiles in order to free those logical volumes or LUNs, and later add them to ASM.

Anyway, I realized that given this is a DSS or Datawarehouse, there is some margin to increase "data density" per block, in other words decrease the amount of space within one data block that is wasted reducing PCTFREE.

Another opportunity to decrease storage usage came from the fact customer is upgrading from 10.2.0.4 to 11.1.0.7, so I can use STANDARD improved compression features on DATA!!!

The final touch came from a long time (and some times under-used feature), Index Key Compression. If you want more detail, can see my posts back from 2008 about this feature Index Key Compression - part I, Index Key Compression - part II and Index Key Compression - part III. Well, thanks the customer provided an environment dedicated to this project I was able to start doing som very expensive analysis on Index compression, with this strategy on mind: choose the biggest indexes to compress, then doing the ANALYZE INDEX ... VALIDATE STRUCTURE OFFLINE and SELECT * FROM INDEX_STATS for each candidate. (Please note you can consider not only massive indexes, but those heavily used too... for that you need to get usage info from V$SEGMENT_STATISTICS). Some indexes compressed very well, with 40%-50% space savings, while others didn't got that good rates on 4% or 6%, just depends on information nature/distribution.

First got all my tables with current allocated space, from DBA_SEGMENTS, filled one spreadsheet with this info and considered extra columns for PARALLEL degree for movement and compression flag, and so on. Using a formula was able to get 3000+ statements like this one:

ALTER TABLE ... MOVE PCTFREE 2 TABLESPACE DAT_ASM_HP NOLOGGING COMPRESS PARALLEL 8;

or like this one for indexes:

ALTER INDEX ... REBUILD TABLESPACE IDX_AHP COMPRESS 1 PARALLEL 8 NOLOGGING PCTFREE 2;


Well, final figures were:

Tables Starting:1.9Tb Ending:1Tb
Indexes Starting:1.0Tb Ending:0.5Tb

I have to remark that this space reductions:
1) Depend on this particular data distribution or patterns
2) After index rebuild, with time flowing and info gets inserted, Indexes will tend to "stabilize" and make block splits thus free space. (see this post on Index Dynamics)
3) Inserts and updates on tables will tend to "expand" some data, therefore next step will be to implement Partitioning to isolate some other regions of data blocks AND enf-of-period maintenance tasks in order to keep data compressed.

Well, if you want to follow this route, please share with us on the comments section how it went for your database or what your findings were, we will appreciate hearing (or better said, reading) from You!

Thanks for your time, have a good time...

Subscribe to Oracle Database Dissected by Email

Follow IgnacioRuizMX on Twitter
Delicious

Bookmark this on Delicious

Wednesday, November 10, 2010

Migration time: Oracle Database Machine/Exadata

Add to Technorati Favorites


Ver este articulo en Español
Guess what I've been doing lately??!! taking vacations? well, that was on September (and that's another post) watching movies? wish I could... none of that, but something more exciting: migrating multi-terabyte datamarts to some Oracle DBMs/Exadatas!!!

My first attempt back in March was Datapump, and it worked fine up-to database sizes around 2Tb using import over the network (DB Link) which is cool, but has the following caveat: cannot disable estimation when using DB Link and therefore much time is spent just measuring how much info Datapump is going to transfer.

However, lately I've been using something we call O2O migration (Oracle-to-Oracle) that is a set of tools and procedures developed by Oracle with SAP database systems in mind, which easily get into the Terabyte league and on top of that critical downtimes, then need to perform super-fast... and it does!!!

On my last migration, took about 36 hours to make a clone of 10Tb datamart ... results may vary according to source hardware config, and things like storage setup and maker or number of cpus factor in; of course on the other side you need to take into account the DBM/Exadata sizing. Summarizing, results may vary according to your hardware and database sizing.

This is an excellent alternative you may take into account, but remember this tool is not included with the database: is sold as a service from Oracle.

Subscribe to Oracle Database Disected by Email

Follow IgnacioRuizMX on Twitter
Delicious
Bookmark this on Delicious

Friday, September 10, 2010

Oracle patchset 11.2.0.2 released (and docs updated)

Add to Technorati Favorites
Ver este articulo en Español

***Sep 13th, Patchset has been released***

Today Oracle released latest patchset for Database Server 11g2: 11.2.0.2 which packs some very interesting improvements and new features; for now, release covers Linux x86 and x86_64, with download measuring near 5Gb(!) .

Also updated all documents for release 11.2, in order to reflect improvements introduced by 11.2.0.2. You can see on following picture a New Features addition
From now on you will see full distribution instead of a patchset (MyOracle Support Note 1189783.1 ), that's why seven ZIP files are listed for download. First two belong to database/RAC: p10098816_112020_{platform}_1of7.zip and p10098816_112020_{platform}_2of7.zip.

If you're looking for Grid Infrastructure then you must download file p10098816_112020_{platform}_3of7.zip.

For client and gateways, download p10098816_112020_{platform}_4of7.zip or p10098816_112020_{platform}_5of7.zip files respectively.

You want to update Examples? then file p10098816_112020_{platform}_6of7.zip contains what is needed.

Finally, you may want to de-install, then is needed file p10098816_112020_{platform}_7of7.zip.

See Oracle 11.2 documentation library

Cheers!



Subscribe to Oracle Database Disected by Email
Follow IgnacioRuizMX on Twitter
Delicious Bookmark this on Delicious

Thursday, September 2, 2010

Oracle database recovery with data unloading

Add to Technorati Favorites
Ver este articulo en Español


What I'm going to share with you is an example of how you can recover from an apparently dead-end situation when a database crashes due to storage failure and gets corrupted.

During past two days I was engaged with a customer where the disk array had a double failure on a RAID-5 arrangement, hence lossing information. When asking for backup... none!!!; unfortunately this database wasn't backed-up regularly (was managed by an application team, not customer's formal Storage-OS-DB areas), database was in NOARCHIVE mode and on top of that NOLOGGING for all objects, including objects (remember we can turnoff logging on indexes),

Well this seems like a terminal case, one that you cannot escape ... but Oracle provides a Data Unloading service for this cases when either corruption or hardware failure renders a database unable to open, I'm part of Support - Advanced Customer Services within Oracle Corporation and our team is entitled to realize those services, under a service contract. There is no guarantee of how much information can be recovered and our tool only recovers data within datafiles/blocks, but hey! that's good help. (Hint: database dictionary included)

But this case was difficult, since customer didn't had QA nor DEV environments, where we can get DDL for objects like stored procedures or indexes, I had to individually extract database dictionary objects, load them on new database distinct schema (of course not SYS) and modify some DBA_ views in order to point these objects. Of course wasn't able to use DBMS_METADATA.GET_DDL because it uses SYS objects, and I didn't wanted to mess with database's DD. So, I had to handcraft scripts to get DDL for sequences, indexes, constraints, views and stored procedures/functions ... lot of work!!! but customer got almost all data back, all structure and very happy, so worth the time invested ... this experience is something they won't forget, as a matter of fact they are going to transfer administration to operational departments.

Thank you for your time and see you later!
Subscribe to Oracle Database Disected by Email

Follow IgnacioRuizMX on Twitter

Delicious Bookmark this on Delicious

Monday, August 30, 2010

Back home and writing


After a few trips abroad and very interesting projects with vip-big customers & new features that demanded lot of time and concentration ... I'm back!

Want to share with you some goodies from my trips ... and advice.

Trinidad & Tobago: be aware of displicent and slow service, lot of chinese places.

Brazil, Rio de Janeiro: try Feijoada at Rondinella Rest. (Av. Atlantica, Copacabana Beach); if you go alone, ask for 1 person sizing ... you won't regret!!


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

Sunday, February 21, 2010

My first assignment on book reviews

Add to Technorati Favorites

Ver este articulo en Español

During the past week, Packt Publishing gave me a copy of his newest "Oracle 11g Streams Implementer's Guide" book, which you can find clicking the book cover image below.

There you may even get a free sample chapter (Ch 5 on N-Way Replication), for free!!!.

So, on days to come you may see here postings related to this endeavor... stay tuned!

See comments on the book, follow link to post Oracle 11g Streams Implementer's Guide - Highlights - Part I

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