How to innocently corrupt a data table block

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

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

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

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)

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

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!

Subscribe to Oracle Database Disected by Email


Bookmark this on Delicious

Index dynamics

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