Tuesday, November 11, 2008

Index Dynamics - Part 3

Add to Technorati Favorites

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?


* 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.


* 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

Follow IgnacioRuizMX on Twitter

Bookmark this on Delicious

Friday, October 31, 2008

Index Dynamics - Part 2 (Halloween on the Block)

Add to Technorati Favorites

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

Follow IgnacioRuizMX on Twitter

Bookmark this on Delicious

Tuesday, October 28, 2008

How to Flush your database caches

Add to Technorati Favorites

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

Follow IgnacioRuizMX on Twitter

Bookmark this on Delicious

Monday, October 27, 2008

Index dynamics

Add to Technorati Favorites

Sigue el link
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)

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

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

Follow IgnacioRuizMX on Twitter


Bookmark this on Delicious

Tuesday, October 21, 2008

Interview with a Pro: Dan Norris

Add to Technorati Favorites

Ver este articulo en Español

Today I will share with you a video interview, this section will be entitled: "Interview with a Pro", and it's aimed to all of you, IT professionals or not related to IT at all. We will present remarkable people, with notable activism for Oracle & IT, and high expertise level on their field.

This time, I'm glad to feature our interview with Dan Norris, he is a consultant (ESA Practice Manager actually) with Piocon, President of the Oracle RAC SIG, member of the Independent Oracle Users Group (IOUG) and a notable member of our IT community.

If you are unable to see this video on the embeded player (below), it's available from YouTube at this link Dan Norris Interview at Oracle Open World

I hope his knowledge and experiences would be useful for you.

Subscribe to Oracle Database Disected by Email

Monday, October 20, 2008

Statspack snapshot levels

Add to Technorati Favorites

You're going to start taking snapshots for Statspack reports, however do you really know what level will give you the detail you want? Valid values for snapshot level are 0,5,6,7 and 10, ordered from the less detailed to the most detailed.

Following are listed all the sections that may appear on a Statspack report and the snapshot level you need to run in order to get data for them. They are listed as their appearance order within the Statspack report. An 'X' mark means "present".

Snapshot Level

0 5 6 7 10
STATSPACK report for x x x x x
Load Profile x x x x x
Instance Efficiency Percentages
x x x x x
Top 5 Timed Events x x x x x
Wait Events for DB x x x x x
Background Wait Events for DB x x x x x
SQL ordered by Gets for DB
x x x x
SQL ordered by Reads for DB
x x x x
SQL ordered by Executions for DB
x x x x
SQL ordered by Parse Calls for DB
x x x x
SQL ordered by Sharable Memory for DB
x x x x
Instance Activity Stats for DB x x x x x
Tablespace IO Stats for DB x x x x x
File IO Stats for DB x x x x x
Buffer Pool Statistics for DB x x x x x
Instance Recovery Stats for DB x x x x x
Buffer Pool Advisory for DB x x x x x
Buffer wait Statistics for DB

x x
PGA Aggr Target Stats for DB x x x x x
PGA Aggr Target Histogram for DB x x x x x
PGA Memory Advisory for DB x x x x x
Enqueue activity for DB

Rollback Segment Stats for DB x x x x x
Rollback Segment Storage for DB x x x x x
Undo Segment Summary for DB
x x x x
Undo Segment Stats for DB
x x x x
Latch Activity for DB x x x x x
Latch Sleep breakdown for DB x x x x x
Latch Miss Sources for DB x x x x x
Parent Latch Statistics DB

Child Latch Statistics DB

Top 5 Logical Reads per Segment for DB

x x
Top 5 Physical Reads per Segment for DB

x x
Top 5 Buf. Busy Waits per Segment for DB

x x
Top 5 Row Lock Waits per Segment for DB

Top 5 ITL Waits per Segment for DB

Dictionary Cache Stats for DB x x x x x
Library Cache Activity for DB x x x x x
Shared Pool Advisory for DB x x x x x
SGA Memory Summary for DB x x x x x
SGA breakdown difference for DB x x x x x
Resource Limit Stats for DB x x x x x
init.ora Parameters for DB x x x x x

Now that you have decided which level are you going to use, you may read this article 2 minute guide to Statspack sampling, to start taking snaphots for your reports.

In case you want to know how to install Statspack quickly, read this 2 minute guide to Statspack installation.

Subscribe to Oracle Database Disected by Email

Follow IgnacioRuizMX on Twitter

Wednesday, September 24, 2008

Awesome, Oracle enters the Hardware arena!!!

Yeeeesss,!!! Larry Elison is releasing right now at OOW2008, which is the first attempt to enter the hardware market, in partnership with HP, offering which is the greatest improvement on Database technology, with improvements on performance that range from 10x minimum, Exadata establish a milestone for Oracle and the IT Industry.
This product, which he labels as a "Database Machine", has been under development (secretly of course) for 3 years, and proposes adding inteligence to the storage and multipling the storage capacity.
Packed with Oracle software, up to 168Tb on disk storage, 64 CPU cores provided and assembled by Hewlett Packard; results are astonishing, with benchmarks showing improvements on performance by x28 and hardware space savings that may make a hot-sell product, maybe as hot as the iPod, all proportions.
After this great surprise, I think this OOW2008 has been a breakthrough, I franquly need to recover the breath...
For DBAs, Storage Managers and SysAdmins... there is another toy we must learn, and master.
Stay tunned, here at OOW2008, San Francisco.

Tuesday, September 23, 2008

Intel offers Time travel at OOW2008

I'm writing online at the keynote with Paul Otellini, after the strong commitment signal expresed by Oracle that Intel is their preferred platform.

He is introducing us to a brief historic recount of CPU, Business (example FedEX) and Animation (example Dreamworks) milestones.

The core idea is a perspective of accelerated demands our computing systems are facing, and succesfully achieving through the improvements the chip makers are offering to the market, specially Intel. The summary shows that Intel is helping to do more within the same time, which in perspective may be interpreted as a "slowing" time effect.

Shared with us the benchmark results for the brand new Xeon 7400 which with his 6 cores and capabilities for working concurrently with up-to 16 sockets, overpasess the Xeon 5400 and Itanium products. The commercial spot is "Oracle runs better on Intel".

Revealed next innovation steps on Itaniun Tukwila and Xeon Nehalem EX, with count above thousands of millions of transistors.

Intel support to multicore exploit coulnd't be more encouraging, they've developed and are offering a tool to discover the parallel oportunity on current application's code.

Enterprise Cloud is a task that Oracle and Intel pretend to tackle in tandem. With deep cooperation among them, that goal seems already done, and we may see fruits from that tree quite soon I guess.

Stay tunned, I'll continue this OOW2008 covering

Monday, September 22, 2008

Oracle New Releases at OOW2008

Charles Philips (President) and Chuck Rozwat (EVP Product Development) have released what seems the next evolution step for Collaboration Suite: Oracle Beehive, which promises to tackle the collaboration fragmentation problem, once and for all. What means for us DBAs? that me may administer in the future:
1) more infrastructure that today is serviced by Microsoft or Lotus products
2) more data growing into our storage infrastructure

Also announced the availavility of a new update for 11g, which is internally labeled as for Linux, with this Oracle continues improvement of their flagship product.

Low at the tech stack, they showed improvements to Oracle VM and their commitment to Linux. Must say Oracle is pushing hard with their VM offer, even with 'orthodox' meassures like no-certification for non Oracle VM solutions; we honestly expect to see that strategy difuse over time.

Stay tunned

at Oracle Open World 2008

It is my first time, and must say this event Rocks!!! There is a lot of people from allover the world, here are the experts and gurus, sessions are great, the OTN lounge is by far the best one. Anyway this event is a must for anybody working with Oracle products.

I scheduled an Unconference session for Tuesday 22th 11:00am, was "Capacity Planing with Oracle tools". I've to say I got a small group of people, but they were very interested on the topic. It's a great experience if you conduct a session, but it's greater if you are the speaker in a language which isn't your mother tongue, is quite challenging.

Monday, July 21, 2008

Real Application Testing backported to 10g and 9i

This functionality has been such a success that Oracle has decided to backport it to previous supported releases. That includes, and up, and up.

The easiest path is because it includes the RAT functionality, any other option needs an additional patch in order to enable it.

Personally I consider Oracle has made a very smart move, this feature may wake upgrade desire everywhere and add one more reason to make the effort and upgrade to 10g... and who knows, even trigger a jump to 11g in some cases.

Sunday, July 13, 2008

My first podcast

Starting today, I will be releasing bi-weekly podcasts.

Today is just an "introduction" test (download podcast).

Hope you hear and like them...

Non tech post.... sunday!

I'm exhausted, today we had maintenance for a Peoplesoft database: boy, what a monster! lot of table moving and index rebuilds (were moved to another tablespaces), but if was faster than using export/import; did this same thing 2 years ago and it took 9 hours with exp/imp, now this baby has 6 times the size it had those days: 600Gb!!.
It required a 12 hour window, and fortunately all timings were accurately predicted, therefore we released the database on schedule...yes!!!

Tomorrow we will know if any of my proposals are accepted for Oracle Open World 2008 (crossing my fingers). San Francisco here we go!!!

Fortunately I've a couple of days off starting tomorrow, and you know? I'm going to rest a lot.

Have a nice week!

Monday, June 30, 2008

When your archive log destination is full

Have you experienced a sudden transaction blast, which fills your archive log destinations within minutes? Do you remember what your first reaction is? I'll try to help you:
a) Erase the archive logs and later crosscheck them
b) Move previous archive logs to free-up some space
c) Close your application and trigger an archive log backup

But, and this is an awareness-but: What if you're using Flashback recovery? What if this archive activity is the result of a very important process? think, that you may be in risk of losing a vital period of activity.

Well, you don't have to worry anymore: the archive log destinations provide the flexibility of dynamic change, therefore you may issue an ALTER SYSTEM and redirect the output generated by the archive writer process to another directory. The statement is really simple:

The best part of all, you don't have to worry about operations on new archive log generated files or previous ones: the database keeps track of every file, saving the full path at the time of writing, therefore recovery and backup operations will have the right information to proceed successfully. You only have to worry about space availability.

I hope this post help you, please leave your comments and suggestions.

Undo Problems?: please read Who is using your UNDO space? Improved Script"
Add to Technorati Favorites

Subscribe to Oracle Database Disected by Email

Follow IgnacioRuizMX on Twitter

Sunday, May 25, 2008

2 minute guide for Statspack sample scheduling

Now that you have installed the Statspack tool, you need to start taking samples or snapshots. This is a task you may easily automate, and is recommended to do so, because snapshots are evenly and uniformly spaced.

You may schedule this task using cron, at or any OS or 3rd party scheduler, but I would suggest a better way: database jobs.

-Database Contained
-Don't breach security exposing users/passwords

The Statspack set of tools provides a script that automatically creates the job for you: spauto.sql. It's located at $ORACLE_HOME/rdbms/admin and you may easily customize the NEXT_DATE parameter for dbms_job.submit (below, red color) and adjust the time interval . You will need to change TRUNC rounding precision if you go from hours to minutes.

dbms_job.submit(:jobno, 'statspack.snap;', trunc(sysdate+1/48,'MI'), 'trunc(SYSDATE+1/48,''MI'')', TRUE, :instno);

Then you need to run the spauto script as the user perfstat or the Statspack owner you have:

SQL> conn perfstat

SQL> @?/rdbms/admin/spauto

You will get an output like this, showing the job number created by spauto and information regarding the next execution time. At this point, snapshot taking has been scheduled succesfuly.

Job number for automated statistics collection for this instance
Note that this job number is needed when modifying or removing
the job:


Job queue process
Below is the current setting of the job_queue_processes init.ora
parameter - the value for this parameter must be greater
than 0 to use automatic statistics gathering:

------------------------------------ ----------- ------------------------------
job_queue_processes integer 10

Next scheduled run
The next scheduled run for this job is:

---------- --------- --------
23 23-MAY-08 17:52:00

How to stop taking snapshots

After you finish taking samples or if you ran out of space for the Statspack tablespace, you'll need to stop the snapshot job. This is very easy, as you may see next:

SQL> conn perfstat -- the Statspack owner

SQL> select job, what from user_jobs;

---------- ------------------------
23 statspack.snap(10);

SQL> exec rdbms_job.remove(23);

PL/SQL procedure successfully completed.

SQL> select job, what from user_jobs;

---------- ------------------------

I hope this tip is useful for you, as it has been very helpful for me.

Please leave your comments, your feedback is vital to improve this content

Add to Technorati Favorites

Subscribe to Oracle Database Disected by Email

Tuesday, May 20, 2008

I need your vote for OracleOpenWorld!!!

Hi folks!!!

I'm planning to attend Oracle OpenWorld 2008 as a speaker, therefore I will appreciate your kind support for these proposals Capacity Planning with Oracle products in 1 day and 11g Advanced Compression unleashed.

I'm counting on your vote to get one of my proposals approved.
Thank you very much!

Saturday, May 10, 2008

Honey, I shrunk the indexes - Part 3

Ver este articulo en Español

Index Key compression, evil or good?

I'm going to split this post in two sections, the first one will depict what goes on inside the index when we use key compression, in order to get details of this feature inner nature and establish tendency to good or bad; the second part will provide tools to measure index key compression effects.

What happens inside the index?

I've been working with indexes big enough to save gigabytes of space, unfortunately those are not good academic examples of what is going. Therefore I will show this through a small POC (Proof Of Concept), which is a reduced and very controlled test case.

First we setup a table and one index on that table.

SQL> create table scott.t2 ( c1 varchar2(10), c2 varchar2(15), c3 number);

Table created.

SQL> create index scott.indx2 on scott.t2 ( c1, c2 );

Index created.

Then we insert some test records that provide the "repeatability" nature of data required by index compression.

SQL> insert into scott.t2 (c1,c2,c3) values ('America','Brasil',1);
insert into scott.t2 (c1,c2,c3) values ('America','Colombia',2);
insert into scott.t2 (c1,c2,c3) values ('America','Canada',3);
insert into scott.t2 (c1,c2,c3) values ('America','Costa Rica',4);
insert into scott.t2 (c1,c2,c3) values ('America','Ecuador',5);
insert into scott.t2 (c1,c2,c3) values ('America','Dominica',6);
insert into scott.t2 (c1,c2,c3) values ('America','Dominicana',7);
insert into scott.t2 (c1,c2,c3) values ('America','Peru',8);
insert into scott.t2 (c1,c2,c3) values ('America','Chile',9);
insert into scott.t2 (c1,c2,c3) values ('America','Venezuela',10);
insert into scott.t2 (c1,c2,c3) values ('America','Paraguay',11);
insert into scott.t2 (c1,c2,c3) values ('America','Uruguay',12);
insert into scott.t2 (c1,c2,c3) values ('America','Guatemala',13);
insert into scott.t2 (c1,c2,c3) values ('America','Nicaragua',14);
insert into scott.t2 (c1,c2,c3) values ('America','Bolivia',15);
insert into scott.t2 (c1,c2,c3) values ('America','Suriname',16);
insert into scott.t2 (c1,c2,c3) values ('America','Panama',17);
insert into scott.t2 (c1,c2,c3) values ('America','El Salvador',18);
insert into scott.t2 (c1,c2,c3) values ('America','Honduras',19);
You may note that "America" is a common value for every record, that establish the following premise: index scott.indx2 may be compressed on the first column c1

Now lets see how it's stored our information inside the index blocks, we will need a couple of tools here: a script that shows which blocks belong to our index and a sentence to dump the content block to readable format.

With this script you may be able to see what datafile and block points the offset or displacement desired for a given object.

header_block + &disp header_block
FROM dba_segments
owner = UPPER('&owner')
AND segment_name = UPPER('&object');

When you run it at the sqlplus prompt, will give you a sequence like this:

Enter value for disp: 0
old 2: header_file, header_block + &disp header_block
new 2: header_file, header_block + 0 header_block
Enter value for owner: scott
old 6: owner = UPPER('&owner')
new 6: owner = UPPER('scott')
Enter value for object: indx2
old 7: and segment_name = UPPER('&object')
new 7: and segment_name = UPPER('indx2')

----------- ------------
4 8859

Now that you have exactly which datafile and which starting block you want to dump, it's time to pass the above values to a range ALTER SYSTEM DUMP statement like this:

ALTER SYSTEM DUMP DATAFILE 4 BLOCK min 8859 block max 8866;

That will generate an ascii text dump file at the location pointed by USER_DUMP_DEST parameter (use show parameter USER_DUMP_DEST to get present value).

Now within that file we can easily locate the index keys for the data we recently inserted, this is the content of offset block 0 (for this example, remember that results will depend on your own data).

B7D3CFD0 00000000 00000000 00000000 41070200 [...............A]
B7D3CFE0 6972656D 48086163 75646E6F 06736172 [merica.Honduras.]
B7D3CFF0 8F220001 02001200 656D4107 61636972 [.."......America]
B7D3D000 206C450B 766C6153 726F6461 22000106 [.El Salvador..."]
B7D3D010 0011008F 6D410702 63697265 61500661 [......America.Pa]
B7D3D020 616D616E 22000106 0010008F 6D410702 [nama..."......Am]
B7D3D030 63697265 75530861 616E6972 0106656D [erica.Suriname..]
B7D3D040 008F2200 0702000F 72656D41 07616369 [."......America.]
B7D3D050 696C6F42 06616976 8F220001 02000E00 [Bolivia...".....]
B7D3D060 656D4107 61636972 63694E09 67617261 [.America.Nicarag]
B7D3D070 01066175 008F2200 0702000D 72656D41 [ua..."......Amer]
B7D3D080 09616369 74617547 6C616D65 00010661 [ica.Guatemala...]
B7D3D090 0C008F22 41070200 6972656D 55076163 ["......America.U]
B7D3D0A0 75677572 01067961 008F2200 0702000B [ruguay..."......]
B7D3D0B0 72656D41 08616369 61726150 79617567 [America.Paraguay]
B7D3D0C0 22000106 000A008F 6D410702 63697265 [..."......Americ]
B7D3D0D0 65560961 757A656E 06616C65 8F220001 [a.Venezuela...".]
B7D3D0E0 02000900 656D4107 61636972 69684305 [.....America.Chi]
B7D3D0F0 0106656C 008F2200 07020008 72656D41 [le..."......Amer]
B7D3D100 04616369 75726550 22000106 0007008F [ica.Peru..."....]
B7D3D110 6D410702 63697265 6F440A61 696E696D [..America.Domini]
B7D3D120 616E6163 22000106 0006008F 6D410702 [cana..."......Am]
B7D3D130 63697265 6F440861 696E696D 01066163 [erica.Dominica..]
B7D3D140 008F2200 07020005 72656D41 07616369 [."......America.]
B7D3D150 61756345 06726F64 8F220001 02000400 [Ecuador...".....]
B7D3D160 656D4107 61636972 736F430A 52206174 [.America.Costa R]
B7D3D170 06616369 8F220001 02000300 656D4107 [ica..."......Ame]
B7D3D180 61636972 6E614306 06616461 8F220001 [rica.Canada...".]
B7D3D190 02000200 656D4107 61636972 6C6F4308 [.....America.Col]
B7D3D1A0 69626D6F 00010661 01008F22 41070200 [ombia..."......A]
B7D3D1B0 6972656D 42066163 69736172 0001066C [merica.Brasil...]
B7D3D1C0 00008F22 00000000 00000000 00000000 ["...............]
B7D3D1D0 00000000 00000000 00000000 00000000 [................]

Now it's time to see what happens when index key compression is applied to our index. Do you remember from previous posts the steps to determine the index rate, expected savings and issue the proper statement? It will look as follows:

SQL> analyze index scott.indx2 validate structure offline;

Index analyzed.

SQL> select opt_cmpr_count, opt_cmpr_pctsave from index_stats;

-------------- ----------------
1 26

SQL> alter index scott.indx2 rebuild compress 1;

Index altered.

Now that our index is compressed, let's see what happened at data block level. Before that, we need to determine datafile and block for the object, since the ALTER INDEX...REBUILD actually moves or changes the extents allocated to the index. Just run the script we used few paragraphs above and issue the ALTER SYSTEM DUMP with those new values. Warning: if you are using the same session for all the exercise, the resulting dump operation will append to the trace file you already reviewed.

The resulting dump will show us that "America" is no longer repeating on every index key but just once, let's see:

B7DB2060 00000000 00000000 00000000 6E655609 [.............Ven]
B7DB2070 65757A65 0106616C 008F2200 07000009 [ezuela..."......]
B7DB2080 67757255 06796175 8F220001 00000B00 [Uruguay...".....]
B7DB2090 72755308 6D616E69 00010665 0F008F22 [.Suriname..."...]
B7DB20A0 50040000 06757265 8F220001 00000700 [...Peru...".....]
B7DB20B0 72615008 61756761 00010679 0A008F22 [.Paraguay..."...]
B7DB20C0 50060000 6D616E61 00010661 10008F22 [...Panama..."...]
B7DB20D0 4E090000 72616369 61756761 22000106 [...Nicaragua..."]
B7DB20E0 000D008F 6F480800 7275646E 01067361 [......Honduras..]
B7DB20F0 008F2200 09000012 74617547 6C616D65 [."......Guatemal]
B7DB2100 00010661 0C008F22 450B0000 6153206C [a..."......El Sa]
B7DB2110 6461766C 0106726F 008F2200 07000011 [lvador..."......]
B7DB2120 61756345 06726F64 8F220001 00000400 [Ecuador...".....]
B7DB2130 6D6F440A 63696E69 06616E61 8F220001 [.Dominicana...".]
B7DB2140 00000600 6D6F4408 63696E69 00010661 [.....Dominica...]
B7DB2150 05008F22 430A0000 6174736F 63695220 ["......Costa Ric]
B7DB2160 00010661 03008F22 43080000 6D6F6C6F [a..."......Colom]
B7DB2170 06616962 8F220001 00000100 69684305 [bia..."......Chi]
B7DB2180 0106656C 008F2200 06000008 616E6143 [le..."......Cana]
B7DB2190 01066164 008F2200 06000002 73617242 [da..."......Bras]
B7DB21A0 01066C69 008F2200 07000000 696C6F42 [il..."......Boli]
B7DB21B0 06616976 8F220001 00080E00 656D4107 [via..."......Ame]
B7DB21C0 61636972 00000000 00000000 00000000 [rica............]
B7DB21D0 00000000 00000000 00000000 00000000 [................]

If you compare space usage between dumps, savings get near the 26% percent forecasted by the ANALYZE, and probes what Oracle promises in their documents about index key compression.

Measure index key compression gains...or loses

There are two approaches for this question:
1) Theoretical through estimations
2) Practical through testing

Estimations based on size reduction, are supported with results from ALL_INDEXES, DBA_SEGMENTS or INDEX_STATS. And it's recommended to take a snapshot before compression, and another after compression and then compare the number of total blocks, the number of branch blocks and leaf blocks among them.

Estimations based on execution plans, require that you collect all SQL that involves the table for the index in spot, take samples of execution plans before and after compression, and verify if the compressed index yields benefits or rises the cost. This task may seem tedious, however has the advantage that you will know better the queries that run over your database, even pinpoint current problems and suggest tuning performance solutions with greater returns than index key compression.

Practical testing or 'running tests' may better show the results, because it may consider activating trace, observe actual execution plans that may differ from those generated with EXPLAIN PLAN, sample precise statistics on index usage with queries on V$SEGMENT_STATISTICS, which is available since Oracle 9i.

Just remember, you must have a baseline to compare with; it may be some production sampling, or a test environment where you may compare before and after within the same database.

When is index key compression definitely evil? right at the time the ALTER INDEX ... REBUILD is issued. Yes, the very act of rebuilding our index will reorganize it, so blocks will be filled with index keys upto the PCTUSED value, that may highly increase the probability of leaf splitting, which is an overhead we as DBAs must avoid... do you figure how to avoid this? if you have a clue, please leave a comment, let's interact and discuss this...

Don't miss the next issue, we are going to show examples of practical measurement.

View previous post: Honey, I shrunk the indexes - Part 2

Add to Technorati Favorites

Subscribe to Oracle Database Disected by Email

Follow IgnacioRuizMX on Twitter

Tuesday, May 6, 2008

Who is using your UNDO space?

Ver este articulo en Español

Sure you've faced this situation: a growing undo tablespace, that seems it could engulf your entire disk space... until finally stops demanding additional space, and within some minutes (or hours, depends on your UNDO_RETENTION setting) you start to see more and more free space in your tablespace. If you scratched your head wondering 'what happened?' or 'who the User did this?', this post may be helpful.

There are some views that show information related to undo activity:

* V$UNDOSTAT: histogram-like view that shows statistics for 10-minute intervals.
* V$TRANSACTION: present time view providing information on current transactions.
* V$SESSTAT: individual session statistics, which includes one for undo usage.

V$UNDOSTAT will provide a who did hint, recording the longest running query for that 10-interval, through the MAXQUERYID column which may be linked to V$SQL and use columns PARSING_USER_ID or PARSING_SCHEMA_NAME the get a grip on the suspect.

V$TRANSACTION linked with V$SESSION will show current used undo blocks for ongoing transactions. This query may help:
SELECT  a.sid, a.username, b.used_urec, b.used_ublk
FROM v$session a, v$transaction b
WHERE a.saddr = b.ses_addr
ORDER BY b.used_ublk DESC

V$SESSTAT provides another view, a who uses the undo kind of view, but we must avoid to get lost in the maze of Oracle statistics and focus on just one: Undo change vector size, which will accumulate the bytes of undo used during the session lifetime. Following query is designed to pinpoint who is having a high undo activity.
SELECT a.sid, b.name, a.value
FROM v$sesstat a, v$statname b
WHERE a.statistic# = b.statistic#
AND a.statistic# = 176    <-- Which stands for undo change vector size
ORDER BY a.value DESC 

Good luck with your UNDO-eating monsters...

Nov 25th 2011: I have updated these scripts, please read Who is using your UNDO space? Improved Script"

You may be interested on Exadata related posts, if so then below is a list of them:

My steps to implement Exadata Hybrid Columnar Compression (EHCC)

Add to Technorati Favorites

Subscribe to Oracle Database Disected by Email

Follow IgnacioRuizMX on Twitter

Thursday, April 24, 2008

Honey, I shrunk the indexes - Part 2

Ver este articulo en Español

How to pick the perfect candidates

Warning: index compression may change execution plans and affect performance, try this on a test database and check if application SQL and PL/SQL code execution improves.

Not every index must be compressed, sometimes compression will give minimal space reductions, that don't compensate the overhead incurred. But how do we know that?

I may suggest two approaches:
1) Size oriented
2) Access frequency oriented

Size Oriented

Let's start with size oriented picking, saying: the bigger the elephant is, better results will get when on diet.

I've used a script like this to get my list of candidates for shrinking:

substr(segment_name,1,20) as index_name,
bytes, blocks, extents
FROM dba_segments
WHERE owner = '{write here the owner}'
AND segment_type = 'INDEX'
AND extents > 63 <---this you may change order by bytes desc;
After running above script, you'll get a listing like this:

-------------------- ---------- ---------- ----------
PROD_NAME_IX 524288 64 8
PRD_DESC_PK 327680 40 5
SYS_C009603 131072 16 2
SYS_C009607 65536 8 1
SYS_C009606 65536 8 1
SYS_C009602 65536 8 1

Now you have to forecast the best compression ratio for your index, and there is a feature very accurate for doing so: ANALYZE the index.

Despite the fact that analyzing tables or objects have deprecated the statistics purpose (one of them), we may use this sentence to test structure. Following command and a quick query to INDEX_STATS will show us if the selected index is a best fit, which compression order to choose and expected size reduction:


Index Analyzed

FROM index_stats
WHERE name = '{index_name}';

The resulting value OPT_CMPR_COUNT is the value you specify for COMPRESS {n} clause, and OPT_CMPR_PCTSAVE is the "expected" compression ratio for that value. All other values from INDEX_STATS are present figures.

Then your sentences may look like this:



SQL> CREATE INDEX owner.index_name ON {table_index_clause}
2: TABLESPACE {Tablespace Name}
4: {other storage clauses};

Second approach: Access Frequency

For this we're going to need the help of two important views: V$SEGSTAT(9i and up) and ALL_OBJECTS. We need V$SEGSTAT because that dynamic view will show us valuable statistics regarding logical reads/writes or physical reads/writes. Following script is proposed as an aid to find the top used indexes within a schema.

SELECT a.object_name, b.statistic_name, b.value
FROM all_objects a, v$segstat b
WHERE a.object_id = b.obj#
AND a.owner = '{your schema owner here}'
AND a.object_type = 'INDEX'
AND b.statistic_name = 'physical reads' <-- You may change this for physical reads direct ORDER by b.value desc
Above query will give you a list of candidates for compression, now you have to apply the ANALYZE and check if there are good space reductions that 'may' correspond to less IO.

Jump to Part III Honey, I shrunk the indexes - Part 3: Index Compression is good or evil?

View starting post: Honey, I shrunk the indexes - Part 1

Add to Technorati Favorites

Ver este articulo en Español

Subscribe to Oracle Database Disected by Email

Follow IgnacioRuizMX on Twitter

Thursday, April 10, 2008

Honey, I shrunk the indexes

Ver este articulo en Español


There is an Oracle feature that may provide savings in space and IO, have you heard of "index key compression"? well, this feature is with us since 8i, but for many, it's obscure or unknown.

Despite the 10g storage management optimizations, always there is gain from index maintenance. If you do index checks regularly you're a good DBA... but if you don't, better take care from now on.

Adding the gains from index rebuild or shrink, you may consider compressing some well-picked indexes, for which the space savings and IO reductions overcome the slight(?) cpu overhead it causes. I wrote a question mark after 'slight' because we will try to estimate that cost in the short term.

I'll propose this starting questions:
* How do you use index key compression?
* What are the first sight results?
* How to pick the best candidates for compression?
* Index compression is good or evil... or both?
* What is the benefit/cost after the shrinking?
* What are the "inside" results or how to analyze the effect on my present queries?

If you have more questions, please feel free to drop a comment and we (all of you and I, because as far as I know I don't have multiple personality disorder) will try to tackle and provide a satisfactory answer.

How do you use index key compression?

There are two ways to accomplish this:
1) drop the index, create it again with COMPRESS
2) rebuild the index with COMPRESS

I will try the second method, with this huge index I've on a test database. These are the starting figures:

---------- ------------ ------------ ------------- ------ -----------
7,331,706 459,210 155,648 1,275,068,416 3 149,394

Now that we have our baseline, it's time to issue the DDL sentence that will reorganize the index:

Index Rebuild

After that statement our figures are now the following:

---------- ------------ ------------ ------------- ------ -----------
7,331,706 459,210 139,904 1,146,093,568 3 133,682

A quick comparison yields less index blocks and leaf blocks (which is logical and obvious), accounting for 10.5% of space savings.

Let our imagination fly, we're showing our boss the way to extend the out-of-disk due date or justifying a well earned salary rise derived from storage savings. Back to reality... in this life everything has a price, don't rush and compress every index in your databases until we talk about pros and cons, and learn how to choose good candidates for compression.

Jump to Part II Honey, I shrunk the indexes - Part 2: How to pick the perfect candidates

Add to Technorati Favorites

Ver este articulo en Español/Look for this content in spanish

Subscribe to Oracle Database Disected by Email

Thursday, April 3, 2008

Recover that lost space

Do you know what happens when you issue a DELETE? Have you wondered what happen after COMMIT? ... well, I might say that there is life after delete for all those blocks that belonged to the data recently deleted.

I'll present the following test case to show you how this works:

First we are going to create our test environment, borrowing a table from the Examples OE schema, creating it in the SCOTT schema.

SQL> alter session set current_schema=SCOTT;

SQL> create table pd as select * from oe.PRODUCT_DESCRIPTIONS;

Let's see how many blocks our new table PD is using

SQL> column segment_name format a20
2: select segment_name, sum(blocks)
3: from dba_Segments where owner = 'SCOTT' and segment_name = 'PD'
4: group by segment_name
5: order by sum(blocks) desc;

-------------------- -----------
PD 384

Now we're going to see how it's structured the table:

SQL> select column_name,
2: substr(data_type,1,20) as dt,
3: data_length
4: from all_tab_columns
5: where table_name = 'PD';

------------------------------ -------------------- -----------

That structure yield us a theoretical row length of (22+3+100+4000) = 4125, which if used fully will give us just 1 row per block (see below for DB blocksize). But , rarelly a NVARCHAR2 is fully used.

SQL> show parameters db_block_size

------------------------------------ ----------- ------------------------------
db_block_size integer 8192

We know there is data and how it's structured, but we need something to erase... the following may be politically incorrect but is only for instructional purposes:

SQL> select language_id, count(*) from pd group by language_id;

--- ----------
US 288
IW 288
TR 288
S 288
SK 288
ZHS 288

30 rows selected.

We're going to delete all the 'S' language registers, I don't know what means 'S' and I'm sorry if I hurt nationalistic feelings, remember this is hypotetical.

SQL> delete from pd where LANGUAGE_ID = 'S';

288 rows deleted.

SQL> commit;

Commit complete.

That was the death sentence for those rows, now will see what happened with the blocks assigned to PD.

SQL> column segment_name format a20
2: select segment_name, sum(blocks)
3: from dba_Segments where owner = 'SCOTT' and segment_name = 'PD'
4: group by segment_name
5: order by sum(blocks) desc;

-------------------- -----------
PD 384

Wow! the table still has the blocks assigned, and will not return them until a TRUNCATE, DROP ... or ALTER TABLE is issued. Yes! an alter table will return the extents to the tablespace free pool.

SQL> alter table scott.pd deallocate unused;

Table altered.

SQL> column segment_name format a20
2: select segment_name, sum(blocks)
3: from dba_Segments where owner = 'SCOTT' and segment_name = 'PD'
4: group by segment_name
5: order by sum(blocks) desc;

-------------------- -----------
PD 376

There it is!!! the blocks that contained all the 'S' language registers, now don't belong to the table PD.

At first you may be tempted to include a periodic ALTER TABLE...DEALLOCATE UNUSED in order to recover all that 'wasted' space, you must be carefull when evaluating this, because there are tables that get data erased and never or rarelly get new data, them are perfect candidates for this.

On the other hand, tables with heavy delete activity won't benefit too much because of the overhead related to update the internal tables that keep track of blocks and extents. What is 'heavy' activity, depends on the deleted rows/total rows rate, and you must develop a maintenance criteria to exclude tables.

You're welcome to send your comments... I hope this help you

Please, before you go, don't forget to vote the poll regarding the content of this blog, thank you!
Subscribe to Oracle Database Disected by Email
Custom Search