Oracle Mix: Time Running Out To Vote For OOW 2008 Sessions (Time Will Crawl) June 28, 2008
Posted by Richard Foote in Oracle Mix.Tags: Add new tag
add a comment
Just a quick note to say time is running out for anyone interested in suggesting or nominating for a session at this year’s Oracle OpenWorld via Oracle Mix. Nominations close this coming Monday, 30 June 2008, so you don’t have much time left if you wish to nominate a presentation that might be of interest to folks attending OOW 2008. If you think you have something interesting to contribute, go on, give it a go !!
Voting closes on Sunday 13 July 2008. If you want to have a say and help select sessions that would be of interest to you at OOW 2008, this is a great opportunity to have direct input. The most popular 35 sessions as voted by you, the Oracle community, will be selected and will be on this year’s schedule at OOW 2008.
I’ve decided to nominate 3 sessions, all of which are currently in the top 35 (Note: you need an oracle.com account to view the sessions, to nominate or to vote):
5) Indexing Secrets: https://mix.oracle.com/ideas/27859-indexing-secrets-with-richard-foote
23) Bitmap Index Interanls: https://mix.oracle.com/ideas/27926-bitmap-index-internals-with-richard-foote
30) Index Organized Tables Internals: https://mix.oracle.com/ideas/27870-index-organized-tables—the-internals-with-richard-foote
If you’re planning on attending OOW this year and my sessions or any of the many other interesting nominated sessions are of interest to you, then I encourage you to vote and have a say in what will be on the agenda.
I think this is a great initiative by Oracle but it’s up to you to make it a successful one 🙂
Deleted Index Entries Part IV (Breaking Glass) June 25, 2008
Posted by Richard Foote in Oracle General, Oracle Indexes, Oracle Myths, Index Delete Operations.6 comments
Yet another method of cleaning out deleted space Oracle has up its sleeve is the recycling of index blocks that contain nothing but deleted index entries.
In some cases, it’s possible for an index block to contain no current index entries with all the corresponding index entries within the index block having been deleted. The index block may be totally empty of index entries or it may contain just deleted index entries.
Once an index block has no current index entries, Oracle places the block on the segment freelist and is now a candidate block to be recycled and reused elsewhere within the index structure after a subsequent index block split operation.
When recycled, the index block becomes “unattached” from its current location within the logical index structure and is reallocated elsewhere within the logical index structure as the new index block in an index block split operation.
Any previously deleted index entries are removed and the contents of the index block are replaced with new index entries associated with its new logical location within the index structure.
A simple little demo to illustrate this process.
First, I create a simple table and associated index and populate it with a 10000 rows:
SQL> CREATE TABLE test_empty_block (id NUMBER, name VARCHAR2(30));
Table created.
SQL> INSERT INTO test_empty_block SELECT rownum, ‘BOWIE’ FROM dual
CONNECT BY level <= 10000;
10000 rows created.
SQL> COMMIT;
SQL> CREATE INDEX test_empty_block_idx ON test_empty_block(id);
Index created.
I next delete the vast majority of the rows, leaving only a handful behind that are likely only found in the last one or maybe two leaf blocks within the index. All the other index leaf blocks therefore only contain nothing but deleted index entries:
SQL> DELETE test_empty_block WHERE id between 1 and 9990;
9990 rows deleted.
SQL> COMMIT;
Commit complete.
If we look at some statistics, we’ll find we have lots of deleted row entries that are all found in leaf blocks that are totally empty, except perhaps the right most leaf block within the index:
SQL> ANALYZE INDEX test_empty_block_idx VALIDATE STRUCTURE;
Index analyzed.
SQL> SELECT lf_blks, del_lf_rows FROM index_stats;
LF_BLKS DEL_LF_ROWS ------- ----------- 21 9990
We next insert a bunch of new rows into the table, but importantly, all these new rows have index entry values that are greater than the previous values. Therefore, all these new index entries will be inserted into the right most side of the index structure and not into the index where we have nothing but the previously deleted index entries.
Oracle will need to allocate new index leaf blocks to accommodate these new index entries, but from where will Oracle get these new index blocks ?
SQL> INSERT INTO test_empty_block SELECT rownum+20000, ‘ZIGGY’
FROM dual CONNECT BY level <= 10000;
10000 rows created.
SQL> COMMIT;
Commit complete.
If we now look at the index statistics, we notice something very interesting:
SQL> ANALYZE INDEX test_empty_block_idx VALIDATE STRUCTURE;
Index analyzed.
SQL> SELECT lf_blks, del_lf_rows FROM index_stats;
LF_BLKS DEL_LF_ROWS ------- ----------- 21 0
The number of deleted leaf entries has disappeared back to zero and the number of allocated leaf blocks has remained the same at 21.
Oracle has both removed the previously deleted index entries and has also recycled all the previously empty leaf blocks and reused them again to accommodate the new index entries. The index is effectively the same size as it was previously even though we’ve added new values that were greater than the previously deleted values.
So index blocks that are totally empty or contain nothing but deleted index entries become “free” again, are placed on the freelist within the index segment and can be reused or recycled again somewhere else within the logical index structure at some later point in time.
Again, yet another example of Oracle cleaning out these unwanted deleted index entries for us.
However, these empty index blocks can potentially be problematic and can cause performance issues until eventually they actually get reused and recycled.
But that’s a topic for another day.
Deleted Index Entries Part III (Slip Away) June 23, 2008
Posted by Richard Foote in Oracle General, Oracle Indexes, Oracle Myths.5 comments
Another little post while I look after some unwell munchkins …
I’ve already looked at the most common example of when Oracle will automatically clean out deleted index entries, that being any subsequent insert into a leaf block will clean out the deleted entries that may exist from the associated leaf block.
Another example of Oracle automatically removing deleted index entries is that associated with a variation of delayed block cleanout. If an Oracle index block with deleted index entries is written to disk before the associated transaction performing the index delete operation is committed, the next time the index block is accessed, Oracle will not only clean out the transaction details from the index block (such as the lock byte) but the deleted index entries themselves may also be cleaned out as well.
This scenario is most likely to occur during large or long running transaction operations (such as batch operations) where many rows are likely to be accessed and/or modified and the associated modified index blocks may get aged out of the buffer cache and written to disk before the transaction ends via the COMMIT.
Note this delayed clean out does not require the index block to be accessed via a subsequent DML operation, even a simple SELECT statement will be sufficient to perform the necessary clean out of deleted index entries.
To illustrate this behaviour, basically create a table with a bunch of rows, deleted some of them but flush the buffer cache prior to issuing the commit on the delete.
SQL> CREATE TABLE del_stuff (id NUMBER, name VARCHAR2(30));
Table created.
SQL> CREATE INDEX del_stuff_i ON del_stuff(id);
Index created.
SQL> INSERT INTO del_stuff SELECT rownum, ‘Bowie’ FROM dual CONNECT BY level <=1000;
1000 rows created.
SQL> COMMIT;
Commit complete.
Next, deleted say 1/2 of the rows from the table.
SQL> DELETE del_stuff WHERE mod(id,2) = 0;
500 rows deleted.
At this point, we flush the associated blocks to disk to simulate a large or long running transaction is which blocks may be aged from the buffer cache and written to disk before the COMMIT is performed.
SQL> ALTER SESSION SET EVENTS ‘immediate trace name flush_cache’;
Session altered.
in 9i, or since 10g:
SQL> ALTER SYSTEM FLUSH BUFFER_CACHE;
System altered.
It’s only at this point after the blocks are flushed to disk that the COMMIT is performed.
SQL> COMMIT;
Commit complete.
Once the index blocks are subsequently accessed, we notice the deleted index entries may have already been cleaned out …
NOTE: The following results do not consistently occur if the index consists of just a single block (the root block is a “special” case), but does appear to be more consistent if the index has a blevel of one or more (as in the demo) and as would be more typical with indexes involved in long running transactions.
A view of INDEX_STATS after a ANALYZE … VALIDATE STRUCTURE command will show the following:
LF_ROWS DEL_LF_ROWS DEL_LF_ROWS_LEN ------- ----------- --------------- 500 0 0
Note: There are no deleted index entries, none. They’ve already been cleaned out.
A treedump will show the following:
—– begin tree dump
leaf: 0x1402e4a 20983370 (0: nrow: 500 rrow: 500)
—– end tree dump
Note: It only shows 500 rrow and 500 nrow values, clearly highlighting there are no deleted index entries.
A partial index block dump will show the following:
kdxlende 0
The deleted index entry count kdxlende is 0, with no deleted index entries existing in the block. None.
All the deleted index entries have already been cleaned out, with not a subsequent DML operation in sight.
So yes, again Oracle can clean out deleted index entries as part of it’s general processing so that the need to do so manually via an index rebuild, coalesce or shrink is a somewhat rare occurrence.
But wait, there’s still more cases to come when Oracle will simply automatically remove deleted index entries entries …
Deleted Index Entries Part II (V-2 Schneider) June 18, 2008
Posted by Richard Foote in Index Delete Operations, Oracle Indexes.17 comments
I’m back !!
In Part I, we looked at how index entries are only marked as deleted during delete/update operations. This means the DML operation and resultant transaction doesn’t have to concern itself with physically cleaning out the deleted entries.
The question is therefore, are these deleted index entries “deadwood”, wasted space that makes the index less compact and less efficient over time requiring a potential periodic index rebuild, or can this deleted space be subsequently reused by Oracle ?
There’s a common perception that deleted space within an index is indeed “deadwood” or can only be reused in very specific circumstances, such as when an identical index value is subsequently re-inserted.
However, this is just another of Oracle’s infamous urban myths. In the vast majority of cases, deleted space within an index can indeed be recycled and can indeed be subsequently reused. Today, I’m just going to begin by focusing on the most common method by which deleted index entries are generally cleaned up.
All it takes to clean out all the deleted index entries within a specific index leaf block is a subsequent insert in the block. That’s it, that’s all it takes. Just one new index entry in a leaf block will automatically clean out all associated deleted index entries that may currently exist within the block. Even if there are hundreds of deleted entries with the leaf block, just the one new index entry will clean them all out. Note the new index value doesn’t have to be the same as any of the deleted index entries, it doesn’t even have to be within the range of any of the deleted index entries within the block. Any new index entry within the leaf block will do the job quite nicely.
A very simple demonstration I use to highlight this point. First, create a simple table and associated index with 10 rows.
SQL> CREATE TABLE del_stuff (id NUMBER, name VARCHAR2(30));
Table created.
SQL> CREATE INDEX del_stuff_i ON del_stuff(id);
Index created.
SQL> INSERT INTO del_stuff SELECT rownum, ‘Bowie’ FROM dual CONNECT BY level <=10;
10 rows created.
SQL> COMMIT;
Commit complete.
Next, deleted say 4 of the rows from the table.
SQL> DELETE del_stuff WHERE id in (2,4,6,8);
4 rows deleted.
SQL> COMMIT;
Commit complete.
A view of INDEX_STATS after a ANALYZE … VALIDATE STRUCTURE command will show the following:
LF_ROWS DEL_LF_ROWS DEL_LF_ROWS_LEN U_SPACE ------- ----------- --------------- ------- 10 4 56 140
Note: It clearly shows 4 index entries are indeed currently marked as deleted.
A treedump will show the following:
—– begin tree dump
leaf: 0x1402e3a 20983354 (0: nrow: 10 rrow: 6)
—– end tree dump
Note: It only shows 6 rrow but 10 nrow values, clearly suggesting there are currently 4 deleted index entries.
A partial index block dump will show the following:
kdxlende 4
row#1[7928] flag: —D–,lock: 2, len=12
col 0; len 2; (2): c1 03
col 1; len 6; (6): 01 40 2e 32 00 01
That indeed there are currently 4 index entries marked as deleted with the ‘D’ flag within the index block.
However, just a single subsequent insert will clean out all 4 of these deleted index entries. Note the new value (100) is not the same value as any of the previously deleted entries and is not even within the range of previously deleted index entries:
SQL> INSERT INTO del_stuff VALUES (100, ‘New Row’);
1 row created.
SQL> COMMIT;
Commit complete.
Now, if we look at the same leaf block, we find:
LF_ROWS DEL_LF_ROWS DEL_LF_ROWS_LEN U_SPACE ------- ----------- --------------- ------- 7 0 0 98
That indeed, INDEX_STATS no longer has any statistics of deleted index entries.
—– begin tree dump
leaf: 0x1402e3a 20983354 (0: nrow: 7 rrow: 7)
—– end tree dump
That the Index Tree dump no longer has any record of the deleted index entries.
That the index block dump no longer has any record of the deleted index entries and that:
kdxlende 0
the count of deleted index entries within the block has been reset to 0.
For most randomly inserted indexes, this means deleted entries will be eventually automatically cleaned out and the freed space reused by subsequent insert operations. Worrying about deleted space and rebuilding such indexes is typically unnecessarily.
I’ll next discuss another method by which Oracle will clean out and remove deleted index entries from its indexes …
OT: Stockholm and Utrecht June 17, 2008
Posted by Richard Foote in Travel.18 comments
I’ve just recently returned from a very hectic but enjoyable visit to Sweden and The Netherlands to do some training for Oracle University. I had two great classes and met a whole bunch of really nice people so a big thank you to all who attended and made me feel so welcome.
Stockholm is a really beautiful city that reminds me somewhat of Sydney with it’s magnificent harbour. However, as with most cities in Europe, it’s the history and amazing architecture of the place that made it a real joy to simply walk around and explore. The highlight for me though was visiting the Vasa Museum and seeing first hand the incredibly well preserved wreak of the massive but doomed Vasa, which sank in the harbour on it’s maiden voyage in 1628. Hidden for centuries, it was only rediscovered in the 1950’s and can now be seen in almost all her glory at the museum. Amazing. The highlight for the Swedes during my visit was no doubt beating Greece in Euro 2008 and it was fun being in the crowd and watching the match on a large outdoor screen.
Utrecht in the Netherlands was another lovely city to visit. You know the place has some history when the so-called “New” canal was built in the 1400s. Being Australian, anything older than a hundred years is deemed antique here so I soak up history whenever I’m lucky enough to get the opportunity. With it’s narrow cobbled streets and picturesque canals, it was another great place to simply walk around and enjoy. Being a keen cyclist, this was certainly my kind of town with people on bikes just everywhere and interestingly, I saw very few overweight locals. I must have that lucky touch as during my visit, the Dutch beat France 4-1 in Euro 2008. If any country is interested, there’s still time for my services if required, although perhaps not so for the host countries.
Will get back to writing about indexes and the such once my body stops wanting to sleep by midday …
Deleted Index Entries – Part I (Let It Be) June 8, 2008
Posted by Richard Foote in Oracle General, Oracle Indexes, Oracle Myths, Index Delete Operations.9 comments
Just before I hop on a plane to do some training in Europe, thought I might begin a little series on deleted space within an index. I’ll begin with a short piece on how we can determine what deleted space an index may currently have before beginning a discussion on whether this deleted space can indeed be reused by Oracle.
Generally speaking, when an index entry is deleted, Oracle doesn’t physically remove the index entry, it’s simply marked as deleted. It’s another case of Oracle putting off what could be an expensive operation for the current transaction and leaving any potential clean up operations to future processes. However, there’s often some confusion whether these deleted index entries remain “deadwood” within the index structure or whether they are somehow cleaned out later and the space potentially reused by subsequent inserts in the relevant index block.
To set the scene, we begin by creating a very simple scenario. Here we create a little table and associated index, insert a single row, commit it and then delete and commit the row afterwards. We can then have a bit of a look around to see how this deleted index entry is recorded by Oracle.
SQL> CREATE TABLE test_delete (id NUMBER, name VARCHAR2(10));
Table created.
SQL> CREATE INDEX test_delete_idx ON test_delete (name);
Index created.
SQL> INSERT INTO test_delete VALUES (1, ‘BOWIE’);
1 row created.
SQL> COMMIT;
Commit complete.
SQL> DELETE test_delete WHERE id = 1;
1 row deleted.
SQL> COMMIT;
Commit complete.
We begin by looking at statistics related to the deleted index entries within the INDEX_STATS view.
SQL> ANALYZE INDEX test_delete_idx VALIDATE STRUCTURE;
Index analyzed.
SQL> SELECT lf_rows, del_lf_rows, del_lf_rows_len FROM index_stats;
LF_ROWS DEL_LF_ROWS DEL_LF_ROWS_LEN ---------- ----------- --------------- 1 1 17
So yes, the one and only index entry is a deleted index entry.
We can also see how many current deleted entries we have by looking at an index tree dump of the index.
SQL> SELECT object_id FROM dba_objects WHERE object_name = ‘TEST_DELETE_IDX’;
OBJECT_ID ---------- 61198
SQL> ALTER SESSION SET EVENTS ‘immediate trace name treedump level 61198‘;
Session altered.
Following is the index tree dump generated by the above operation.
—– begin tree dump
leaf: 0x14008d2 20973778 (0: nrow: 1 rrow: 0)
—– end tree dump
We notice that the rrow count which is the number of non-deleted index row entries is 0 but the nrow count which is the total index row entries, including deleted entries is 1. Therefore, yes the index currently consists of just the one deleted index row entry.
We can also view the deleted index details by performing a dump of the associated index block.
SQL> SELECT file_id,block_id FROM dba_extents WHERE segment_name=’TEST_DELETE_IDX’;
FILE_ID BLOCK_ID ---------- ---------- 5 2257
SQL> ALTER SYSTEM DUMP DATAFILE 5 BLOCK 2257;
System altered.
Below is an extract from the above index block dump:
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0000.000.00000000 0x00000000.0000.00 —- 0 fsc 0x0000.00000000
0x02 0x0008.024.0000075b 0x00804e29.0078.0b –U- 1 fsc 0x0011.00000000
……
kdxlende 1
kdxlenxt 0=0x0
kdxleprv 0=0x0
kdxledsz 0
kdxlebksz 8036
row#0[8021] flag: —D–, lock: 2, len=15
col 0; len 5; (5): 42 4f 57 49 45
col 1; len 6; (6): 01 40 10 0a 00 00
From the above, kdxlende 1 is a count of the deleted index entries. The index entry has a D flag set, signifying that the index entry has been deleted. Also note that the index entry was locked and deleted by the ITL entry associated with ITL number 2.
So yes, when we perform a delete that results in the deletion of an index row entry, the deleted index entry is marked as deleted but is not physically cleaned out at the time of the delete. All the above checks confirm this current state of the index.
The key question is therefore, are these deleted index entries ever reused/removed, or are they forever “deadwood” that would require a periodic rebuild of the indexes to clean out ?
Answer coming soon …
Primary Keys and Non-Unique Indexes (What’s Really Happening ?) June 4, 2008
Posted by Richard Foote in Constraints, Oracle General, Oracle Indexes, Oracle Myths, Primary Key.34 comments
Based on this OTN thread, it appears there may still be folk out there that think Oracle uses Unique Indexes to police a Primary Key (or Unique Key) constraint. This is of course not necessarily true as since 8.0 and the introduction of Deferrable Constraints, Oracle can just as easily police a PK (or UK) constraint with a Non-Unique Index.
To determine whether a new PK value currently exists or not, Oracle can almost (although not quite) just as easily perform an index look-up using a Non-Unique Index as it can with a Unique Index. Simple index look-up, is the value there, yes or no, proceed as appropriate.
Indeed, I’ve already discussed on these here pages various differences between using a Unique and a Non-Unique Index:
However, for those that may never have seen a case where creating a PK constraint has not created a Unique index, I thought it might be an idea to just go through a number of different scenarios when this is all quite possible.
The first example is when there’s already an existing non-unique index that Oracle can use. Oracle will not (and indeed can not) create a Unique Index where an identical Non-Unique index already exists.
SQL> create table bowie as select rownum id, ‘BOWIE’ text from dual connect by level <= 10000;
Table created.
SQL> create index bowie_i on bowie(id);
Index created.
SQL> alter table bowie add constraint bowie_pk primary key(id);
Table altered.
SQL> select ic.index_name, ic.column_name, i.uniqueness, c.constraint_name, c.constraint_type from user_ind_columns ic, user_indexes i, user_constraints c where ic.index_name = i.index_name and i.index_name = c.index_name and i.index_name = ‘BOWIE_I’;
INDEX_NAME COL_NAME UNIQUENES CON_NAME C ---------- -------- --------- -------- - BOWIE_I ID NONUNIQUE BOWIE_PK P
A subtle difference to this scenario is where there’s an existing Non-Unique index but it only has the leading columns the same as the PK constraint to be. Again, even if there are additional columns within the index, the fact the leading columns match the PK constraint means Oracle can still use the index to police the constraint as the index must be in the same logical order of these leading columns.
For example:
SQL> alter table bowie drop primary key;
Table altered.
SQL> drop index bowie_i;
Index dropped.
SQL> create index bowie_i on bowie(id, text);
Index created.
SQL> alter table bowie add constraint bowie_pk primary key(id);
Table altered.
SQL> select ic.index_name, ic.column_name, i.uniqueness, c.constraint_name, c.constraint_type from user_ind_columns ic, user_indexes i, user_constraints c where ic.index_name = i.index_name and i.index_name = c.index_name and i.index_name = ‘BOWIE_I’;
INDEX_NAME COL_NAME UNIQUENES CON_NAME C ---------- -------- --------- -------- - BOWIE_I TEXT NONUNIQUE BOWIE_PK P BOWIE_I ID NONUNIQUE BOWIE_PK P
Another example is when the PK constraint is created as being DEFERRABLE. This means the constraint could be used to defer the policing of the constraint until the time of the COMMIT, rather than at the time of the DML statement. As such, for a period of time during a transaction, there could actually be duplicate values for the PK. Not only will Oracle create a Non-Unique index for such a Deferrable constraint, indeed Oracle MUST create a non-unique index. The existance of a previously created Unique Index will actually prevent Oracle from creating PK constraint as deferrable.
SQL> alter table bowie drop primary key;
Table altered.
SQL> drop index bowie_i;
Index dropped.
SQL> alter table bowie add constraint bowie_pk primary key(id) deferrable;
Table altered.
SQL> select ic.index_name, ic.column_name, i.uniqueness, c.constraint_name, c.constraint_type from user_ind_columns ic, user_indexes i, user_constraints c where ic.index_name = i.index_name and i.index_name = c.index_name and i.table_name = ‘BOWIE’;
INDEX_NAME COL_NAME UNIQUENES CON_NAME C ---------- -------- --------- -------- - BOWIE_PK ID NONUNIQUE BOWIE_PK P
Of course, another way to create a Non-Unique Index when defining a PK constraint is to simply create the specific index at the same time as the PK constraint. You can get pretty tricky with the create table syntax these days.
For example:
SQL> alter table bowie drop primary key;
Table altered.
SQL> drop index bowie_pk;
Index dropped.
SQL> alter table bowie add constraint bowie_pk primary key(id) using index (create index bowie_i on bowie(id));
Table altered.
SQL> select ic.index_name, ic.column_name, i.uniqueness, c.constraint_name, c.constraint_type from user_ind_columns ic, user_indexes i, user_constraints c where ic.index_name = i.index_name and i.index_name = c.index_name and i.table_name = ‘BOWIE’;
INDEX_NAME COL_NAME UNIQUENES CON_NAME C ---------- -------- --------- -------- - BOWIE_I ID NONUNIQUE BOWIE_PK P
The next time you read or hear mentioned Oracle uses a Unique Index to police a PK (or UK) constraint, you now know it might not necessarily be the case.
Richard Foote's Oracle Blog 