Oracle-MySQL Databases Dissected

I am back

noreply@blogger.com (Ignacio Ruiz) — Mon, 24 Nov 2014 14:10:00 +0000

It's been a very long time not coming here, about 2 years and half ... and I missed it big time.

Now I moved to Oracle Systems (formerly Sun Microsystems) on a role support-programmer mixed, within RPE (Revenue Product Engineering) on the Extra Care team, with focus on Solaris, Engineered Systems with Solaris, ZFS Storage Appliance and Ops Center. I'm very happy since my job is closer to development, something was looking for since some years now.

A cycle closes, full of good experiences, learnings and miles!!! lots of miles, hotel nights, Engineered Systems installations and migrations!

Went up and down, all over the continent, helping customers to adopt Exadata, Exalytics and SuperCluster . Was a very exciting time, meeting very interesting people within Oracle and outside!

What's ahead of the road? since less travel will mean more time available, you may expect more new content here! fresh, maybe with different perspective now that I'm getting into the inner workings of Solaris/Linux and storage.

I'm very excited and looking forward to resume contributing a little to this huge sea of knowledge!!!

Stay tuned!

Subscribe to Oracle Database Disected by Email Bookmark this on Delicious

Book Review - High Performance MySQL 3rd Edition

noreply@blogger.com (Ignacio Ruiz) — Mon, 02 Apr 2012 16:50:00 +0000

Ver este articulo en Español

This is THE MySQL performance book. Period!

Every chapter is very well crafted, with a precise balance between theory and practice, and full of invaluable nuggets, sometimes transcending the MySQL arena and applicable to any database! Such cases are Chapter 2-Benchmarking MySQL and Chapter 3-Profiling Server Performance, very solid foundations for the reading ahead.

All over the text, authors propose tools, examples of use and proven diagnostic techniques, that will greatly improve your performance firefighter skills and enhance your knowledge of MySQL internals. Nevertheless, what I liked the most from this book is taking into consideration the physical part of database structures in play when speaking about performance, which is something most authors don't include; plus treatment on MySQL high availability and cloud features, which we'll increasingly see on customers.

As you may know, MySQL architecture relies on what is called “Storage Engines” and this book provides resolution down to that level, describing behavior, pros and cons among each major storage engine, plus some improvements coming for MySQL 5.6 (now in beta stage). That is cool!!!

However, there is something that kind of bugged me at times and was references to a specific commercial MySQL offering and tools, but after taking a look to that company web-site, found that is a great contributor to the MySQL community and you can actually use the tools!

At the O'Reilly website you can take a look to the book's TOC, bet you will find it very complete; hey! you can even peek the content with Google Preview, just follow the link: High Performance MySQL

If you are interested on Mathematical Rigorous methods for performance tuning see Performance Enlightening - The Craig Effect (Tropa de Elite)

Subscribe to Oracle Database Disected by Email Bookmark this on Delicious

Performance Enlightening - Craig Effect (Tropa de Elite)

noreply@blogger.com (Ignacio Ruiz) — Fri, 30 Mar 2012 01:55:00 +0000

Ver este articulo en Español

During the past week I had the opportunity to assist both the Oracle Performance Firefighting and the

Advanced Oracle Performance Analysis and only can say: awesome!!!

Craig Shallahamer is a great teacher and has a lot of resources to effectively share knowledge, even if

the subject is complex like buffer cache structures or arid like the mathematical foundation required for

perf analysis. That is not all, he provides valuable tips of the trade or anecdote nuggets, dipped on a

very fresh and sometimes humorist perspective.

The result: we started searching for those AWRs and Statspack reports that were difficult to analyze, I even

recalled some unsolved performance cases that stained my record; difference now, is we all have the

analytical elements to properly handle these and new challenges.

Here a group picture of "The Elite Squad" (Tropa de Élite)

Sao Paulo, Brazil / March 19th-23th 2012

 If you want to know more about these trainings or get more insight on Craig's methodologies and research, please click

the image below (a new window will show up, you may need to allow this pop-up).

Go to OraPub.com

Subscribe to Oracle Database Disected by Email Bookmark this on Delicious

Book Review - iOS 5 Programming Cookbook

noreply@blogger.com (Ignacio Ruiz) — Sun, 04 Mar 2012 18:25:00 +0000

Ver este articulo en Español

You will write iOS apps in less than you think

Being a C/BASIC hardcore programmer for mobile devices in a past life, I enjoyed very much this book because it really helped me grasp quickly many basic and intermediate topics on iOS 5 Objective-C programming, would say after reading the full book I have all needed to program good basic applications; and even after getting practice and experience, guess I will eventually resort on this book as reference. In my opinion this book is awesome and a must for beginners and intermediates.

Honestly, I consider myself not very much fan to the Cookbook format, however this is one case where the author, Vandad Nahavandipoor, exploits it wonderfully to show and develop case after case of what you will face as an iOS programmer, both for iPad and iPhone! He misses nothing and includes good explanations and concise screenshots when needed.

Now, the table of contents covers almost everything is needed to develop any type of application: Location & Maps, Networking, Audio & Video, plus some “must-have” topics like Multitasking, Graphics & Animations or Core Motion. My favorite chapter is #2, because it provides the building blocks for GUI programming, including corresponding source code examples!

Something I would like to see is bluetooth or network programming basics, but maybe I'm overreaching and those are advanced topics, out of the scope if this book.

If you want to take a closer look go to the Product Page at O'Reilly website: iOS Programming Cookbook - Book I'm sure you will get interested!

Subscribe to Oracle Database Disected by Email Bookmark this on Delicious

Who is using your Undo space? - Improved Script

noreply@blogger.com (Ignacio Ruiz) — Sun, 27 Nov 2011 23:08:00 +0000

Ver este articulo en Español

Hi folks!
I have extended the Undo usage scripts to include two additional indicators:
1) undo change vector size statistics
2) Used undo records/blocks

and support for RAC infrastructure, so you can spot the hungriest UNDO eaters for any given instance.

Then the script for Oracle 11g is as follows:

set pagesize 400
set linesize 140
col name for a25
col program for a50
col username for a12
col osuser for a12
SELECT a.inst_id, a.sid, c.username, c.osuser, c.program, b.name,
a.value, d.used_urec, d.used_ublk
FROM gv$sesstat a, v$statname b, gv$session c, gv$transaction d
WHERE a.statistic# = b.statistic#
AND a.inst_id = c.inst_id
AND a.sid = c.sid
AND c.inst_id = d.inst_id
AND c.saddr = d.ses_addr
AND a.statistic# = 284
AND a.value > 0
ORDER BY a.value DESC

If you want to run this script on versions 10g1 and 10g2, just replace the statistic# with 176; 216 if your database is 11gR1... or use the following version independent script!!! (Hope we don't change the statistic name).

set pagesize 400
set linesize 140
col name for a25
col program for a50
col username for a12
col osuser for a12
SELECT a.inst_id, a.sid, c.username, c.osuser, c.program, b.name,
a.value, d.used_urec, d.used_ublk
FROM gv$sesstat a, v$statname b, gv$session c, gv$transaction d
WHERE a.statistic# = b.statistic#
AND a.inst_id = c.inst_id
AND a.sid = c.sid
AND c.inst_id = d.inst_id
AND c.saddr = d.ses_addr
AND b.name = 'undo change vector size'
AND a.value > 0
ORDER BY a.value DESC

Read the popular 2008 article Who is using your UNDO space?

Interested on MySQL Performance? Read Book Review - High Performance MySQL

Subscribe to Oracle Database Disected by Email

Bookmark this on Delicious

First pervasive-post

noreply@blogger.com (Ignacio Ruiz) — Thu, 24 Nov 2011 03:10:00 +0000

Today I'm writing this post from a Samsung Galaxy, my hand sized tablet, camera included, microphone and lots of fun.

I have been busy this year and that is thanks to the tremendous success of the Database Machine/Exadata.

Traveled all over the caribbean and south america, plus my first OOW experience as Oracle employee: always exciting!

Bandeja Paisa / Paisa "tray" - Medellin, Colombia

Sao Paulo Subway System

Oracle Open World 2011

The Oracle Exadata: latest Business Weapon

noreply@blogger.com (Ignacio Ruiz) — Fri, 15 Jul 2011 14:46:00 +0000

Ver este articulo en Español

Sorry I’ve been having a lot of work installing, maintaining and migrating to Database Machines all over Latinamerica for the latest 10 months; that means lot of travels abroad, all of them very exciting, but the real excitement comes from my job working with this wonderful Machine.

Was on the last project when realized how important is this investment for our customers, having this big caribbean Telco installed their first Exadata and later migrated their databases meant for them dramatic performance improvements and the ability to get more up-to-date analytical information and compete better and more agile.

Of course the Database Machine fulfilled expectations when we achieved a processing reduction of 6x on their nightly batch processes, going from 8-9hrs processing time, down-to 1.5-2hrs processing time. What this means: now they’re able to increase the refreshing time of their analysis tools 12 times a day and on demand, instead of having latest information one day behind.

Read my related article on Exadata Hybrid Columnar Compression

My steps to implement EHCC

Recommended reading on My Oracle Support notes that helped make this happen:

Migrating to Oracle Exadata Storage Server Paper (PDF)

Oracle Sun Database Machine Performance Best Practices [ID 1067520.1]

Subscribe to Oracle Database Dissected by Email

Bookmark this on Delicious

My Steps to Implement Exadata EHCC

noreply@blogger.com (Ignacio Ruiz) — Tue, 30 Nov 2010 15:08:00 +0000

Ver este articulo en Español Subscribe 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...

Bookmark this on Delicious

From 3Tb to 1.5Tb Using standard COMPRESS and PCTFREE

noreply@blogger.com (Ignacio Ruiz) — Tue, 23 Nov 2010 13:58:00 +0000

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

Bookmark this on Delicious

Migration time: Oracle Database Machine/Exadata

noreply@blogger.com (Ignacio Ruiz) — Wed, 10 Nov 2010 07:16:00 +0000

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

Bookmark this on Delicious

Oracle patchset 11.2.0.2 released (and docs updated)

noreply@blogger.com (Ignacio Ruiz) — Fri, 10 Sep 2010 17:34:00 +0000

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

Bookmark this on Delicious

Oracle database recovery with data unloading

noreply@blogger.com (Ignacio Ruiz) — Fri, 03 Sep 2010 01:18:00 +0000

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

Bookmark this on Delicious

Back home and writing

noreply@blogger.com (Ignacio Ruiz) — Mon, 30 Aug 2010 18:44:00 +0000

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

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

noreply@blogger.com (Ignacio Ruiz) — Tue, 27 Apr 2010 07:47:00 +0000

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

Bookmark this on Delicious

Starting Oracle Coherence 3.5 book review

noreply@blogger.com (Ignacio Ruiz) — Mon, 12 Apr 2010 14:41:00 +0000

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

Bookmark this on Delicious

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

noreply@blogger.com (Ignacio Ruiz) — Sat, 10 Apr 2010 07:00:00 +0000

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

Bookmark this on Delicious

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

noreply@blogger.com (Ignacio Ruiz) — Fri, 02 Apr 2010 08:10:00 +0000

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

Bookmark this on Delicious

My first assignment on book reviews

noreply@blogger.com (Ignacio Ruiz) — Sun, 21 Feb 2010 08:07:00 +0000

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

Bookmark this on Delicious

How to innocently corrupt a data table block

noreply@blogger.com (Ignacio Ruiz) — Fri, 30 Oct 2009 16:31:00 +0000

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

Bookmark this on Delicious

Latest storage Adventure

noreply@blogger.com (Ignacio Ruiz) — Mon, 19 Oct 2009 15:43:00 +0000

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

Bookmark this on Delicious

Resuming and the search for the 4Mb lost

noreply@blogger.com (Ignacio Ruiz) — Mon, 15 Jun 2009 10:38:00 +0000

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

Bookmark this on Delicious

Index Dynamics - Part 3

noreply@blogger.com (Ignacio Ruiz) — Tue, 11 Nov 2008 11:00:00 +0000

Ver este articulo en Español

See previous post Index Dynamics - Part II or first part Index Dynamics - Part I

On this part, I’m going to share with you the final results for my 2 week experiment with indexes on their habitat, a production environment.

I’ve to forewarn that some data was not available, as the ANALYZE method fails for objects currently locked, given that our environment is a “live” production database and for consistency we scheduled all tests for a given time, which some days collided with business process. The Used Space graph shows those NA data points; however for INDEX1 and INDEX4 the Nov 7th data for Deltas was interpolated, the only index for which we have all samples is INDEX2, coincidentally our research subject.

First let’s see the Used Space graph, here you may see how the Used Space percentage continues the decay trend, which means that free space within the index nodes is increasing.

Compare the current Used Space figures to those sampled before the rebuild, observe that after 15 days some indexes are midway to have the same amount of free space. That recalls the frequent doubts regarding index maintenance: “Must rebuild indexes? How long will last the structure ‘optimus’ state?”


Used Space    First    Now
INDEX1(10c)    69%     99%
INDEX4(6c)     63%     74%
INDEX2(4c)     53%     77%
INDEX3(2c)     45%     92%

Given our partial-conclusion stated last post (Index Dynamics - Part II)
”The branch blocks are the stressed part of the B-Tree right after the rebuild”
We now might see less activity for Branch Blocks during the following days, which actually occurred.

One remark: observe the Nov 6th high for INDEX1 and the previous behavior, was on that day the transaction rate motivated a higher reorganization within the index, showing us the impact business patterns have on data structures.

I have to mention that my production environment went through a period close week, therefore increased activity and closing processes made their mark on observations. An example of this is the resulting graphs for Leaf Blocks, with noticeable inter-day spikes.

Let’s pay a visit to our research subject, INDEX2. This chart is online with everything previously stated, no surprises here.

This study motivated the following questions (that’s the problem with research, you answer one question and many new take the place):
1) How PCT_FREE (or PCT_USED) impacts the decay curve?
2) Are the splitting constants embedded in the code, optimal figures?
3) Do we need a feature for PCT_FREE change after rebuild has been done?

Conclusions

* High stress within the branch blocks may be observed as soon as transactions start to modify the index structure; the stress period or intensity, will depend on transaction rate and index attributes.

Recommendation

* Do not rebuild indexes right before processes or high load days, if heavy writing is expected: the index leaf block split overhead may impact performance.

I’ve the pending assignment of proposing a mathematical model for the Index Decay Rate… I’m working on that, with the help of Calculus. My next delivery will talk about that, I’m pretty close…

Thank you for reading, don’t forget to leave your comments.

See previous post Index Dynamics - Part II or first part Index Dynamics - Part I

Subscribe to Oracle Database Disected by Email

Bookmark this on Delicious

Index Dynamics - Part 2 (Halloween on the Block)

noreply@blogger.com (Ignacio Ruiz) — Fri, 31 Oct 2008 06:20:00 +0000

Ver este artículo en Español

View starting post: Index Dynamics - Part 1

As I've promised, today will share with you mid-term results for my index observations.

First we may see a graph of Used Space, as reported by column PCT_USED of table INDEX_STATS (right after an ANALYZE over each index). This percentage accounts the space allocated to the B-Tree that is used.

There is one line for every index we are considering, and note the legend on the graph showing the index name and the number of columns inside parenthesis.

What can be observed in this chart?
1) After rebuild (done on saturday), every index starts with 95% used space.
2) First day is sunday, the system had almost 0 activity, therefore our indexes' space usage show slight changes.
3) Starting on monday, INDEX1 reported wrong data for PCT_USED and the other indexes began their "decay" trend.
4) After a whole week of activity, indexes gained free space, some of them faster than others (for instance INDEX4 went from 95% to 84% used space, that is 11% on 5 days).

Point 3 raised a service request with Oracle.
Point 4 may be explained in terms of:
a) Table transactionality (how many insertions/deletes/updates it had)
b) Index type, if unique or non-unique.
c) Number of columns conforming the index.
d) Type of every indexed column.

I may propose the following hypothesis: the index used space decay rate is directly proportional to the table's transactionality, to the number of columns and types of them, and inversely proportional to the type of the index (Unique or non-Unique) and block size.

As you already know, B-Tree indexes have two types of nodes: called Branches and Leafs.

Now, let's see where is that space allocated, look this chart that shows increment or delta on daily samples taken from column LF_BLKS.

You may see a great saving due to rebuild, however that saving fades slowly during the following days. Next chart makes a zoom, in order to watch closely the variation rate experimented during those days.

Keep in mind the rate at which leaf blocks are incremented, later you'll see how it's related to new key insertions (transactionality).

What about the branches? ... that's what we're going to see on next chart: the behavior of those indexes for branch blocks. I've taken the sampled value for BR_BLKS and got the variation rate versus the previous day.

Please observe, the stepped peak for INDEX2 and INDEX4, during the first day of activity the number of branch blocks almost doubled, that means an intense reorganization within the index. That may be caused due to the nature of these indexes (all are UNIQUE) and a high transactionality rate.

For the INDEX3, we observe that the increase is splited between 2 days, Sunday and Monday. This table presented activity the day after the rebuild was done.

We have to ignore the INDEX4, cause their figures are not reliable.

Why is there a higher increase on the number of branch blocks? We may answer that question recalling the percentage of free space (5%) we had after the rebuild, that is a very small margin for a leaf block, and the chances of split increase if we have a UNIQUE index. We must remember that a leaf block split, may involve a branch block split.

Our partial conclusions may be stated as follow:
1) Depending on the percentage of free space, after rebuilding indexes, their state becomes "less" stable.
2) Indexes tend to take a "stable" form, with the pass of time.
3) The branch blocks are the stressed part of the B-Tree right after the rebuild.

I will finish this experiment next Saturday, and share with you all remaining findings next Tuesday; yes, seven days from now... or eight days? anyway...
I hope to get near a mathematical model for the Index Decay Rate, cross fingers.

Thank you for reading, keep in touch!

Follow to next post Index Dynamics - Part 3

Subscribe to Oracle Database Disected by Email

Bookmark this on Delicious

How to Flush your database caches

noreply@blogger.com (Ignacio Ruiz) — Wed, 29 Oct 2008 03:36:00 +0000

Ver este articulo en Español

Flushing the SGA memory areas, Shared Pool and Buffer Cache, it's an uncommon task, however, it gets useful when you're doing some tests and want to override the memory and go direct to disk, or when you have shared pool issues (here is a workaround, however I encourage you to find the root cause).

Shared Pool flush
This is the only sentence you have to know for releases 9i and up...

alter system flush shared_pool

Buffer Cache flush
For Oracle 9i I didn't know how to do this, fortunately found it today on Rahat Agivetova's blog

alter session set events = 'immediate trace name flush_cache';

I've tested it and does the job well...

And for Oracle 10g and up, the syntaxis is as follows:

alter system flush buffer_cache;

But you already knew that, isn't it? ...well, this was a "snack" post until Friday's follow up to Index Dynamics, which I may anticipate interesting results...

Thanks for reading... and for your comments, too!

You have UNDO space issues?: please read Who is using your UNDO space? Improved Script"

Subscribe to Oracle Database Disected by Email

Bookmark this on Delicious

Index dynamics

noreply@blogger.com (Ignacio Ruiz) — Mon, 27 Oct 2008 05:38:00 +0000

Ver este articulo en Español

Today I will start with a series of 3 posts depicting the follow up of 4 index behavior on a Production database.

The purpose of this exercise is try to unveil the morphology these four indexes take during a given period of time, under what kind of load, model it in a graphical way. Since indexes are quite a black box, this exercises also proposes a complementary procedure for index quality measure.

Experiment subjects were selected using information on DBA_SEGMENTS+DBA_HIST_SEGMENTS, with focus on size and activity, indexes were choosen within the 400Mb-500Mb range thinking on fast ALTER INDEX REBUILD and fast ANALYZE VALIDATE STRUCTURE.

Our four candidates are (names were replaced in order to protect their identity)


OBJNAME                  BLOCKS      BYTES HEIGHT PCTUSED
-------------------- ---------- ---------- ------ -------
INDEX1                    62464  511705088      4      69
INDEX2                    60416  494927872      4      53
INDEX3                    61056  500170752      4      45
INDEX4                    54272  444596224      4      63

We have our "Before" snapshots, now we need to rebuild all four indexes and take "Initial" snapshots to start the experiment. After this operation our figures are:


OBJNAME                  BLOCKS      BYTES HEIGHT PCT_USED
-------------------- ---------- ---------- ------ --------
INDEX1                    41600  340787200     4       95
INDEX2                    34176  279969792     4       95
INDEX3                    28032  229638144     3       95
INDEX4                    37888  270445017     4       95

Note the amount of space "empty" the indexes had before rebuild, for INDEX2 and INDEX2 we may ask ourselves "How are the tables used in order to get index keys so dispersed?". INDEX1 and INDEX4 seem within normal parameters. All four indexes now have the same initial condition on 5% free space.

On October 31th, Friday ... Hallowen, we are going to see what has been happening with our indexes;and who knows, you may testify witchcraft... or, is it science? What do you think?

Keep in touch...

Jump to Part II Index Dynamics - Part 2

Subscribe to Oracle Database Disected by Email

Bookmark this on Delicious