Monday, 7 May 2007

sitemap of SimpleoOracleDBA

Here are the introductions to PdvFirstBlog and SimpleOracleDBA

Various Oracle Tips and Rants and Opinions, all to do with practicalities.

  • Simple Oracle DBA - Availability, Scalability, Manageability... It has to be Simple

  • Bitmap Indexes on an OLTP system - ORA-00060... Classic Deadlock

  • Backup on a different Level - in the SOA layer.

  • How To Pool Connections - One of the challenges

  • Databases Everywhwere - Systems and Databases, how many databases.

  • ILM, Information LifeCycle Management - another Three Letter Acronym.

  • Index Organized Tables - Many Benefits

  • txt - txt.

  • txt - txt.

  • Various Rants, all to do with practicalities.

  • In times of Crisis you call the DBA - Who you gonna call...

  • Rationalize TOAD - Toad will find a way...

  • M O O W - Miracle Oracle Open World...

  • My blog-plug for the Conference - UK Oracle USergroup...

  • Travel related stuff.

  • Angel of the North - My most visited leasure page...

  • The usual links to groups etc..

    My own linked in profile (shameless plug)

    my agenda (normally at the right hand side of the blog)

    UKOUG main url

    Miracle BV

    the home of Oracle-L : Best Mailing list

    link to Oracle Forum.

    Sunday, 6 May 2007

    Index Organized Tables IOTs, the forgotten benefits

    Check if your database can benefit from IOTs. You could be surprised.

    Index Organized tables are possibly the most under used feature after the clustered table (now there is another underused item …). IOT’s also have some additional benefits that the manual seems to forget.

    In brief: If you have tables with parent-child relations, or dependent tables where you often retrieve all related records at once, then you should read up on Index Organized Tables. Notice that this includes nearly every database out there.

    Wake up call and Warning
    This feature is available since 8i, and only now, recently, does it get a number of Bugs reported against itself (not in the last place because one of our major projects boldly went out using IOTs). Have those bugs been “dormant” all the time ? Check out the list of Expected fixes for and see for yourself (Note:358776.1, when I checked it 28-Mar-06). 3 out of the 7 fixes mentioned under IOT are bugs found by us, and a bug-search for IOT turns up even more issues. The information and the dates on the bugs and fixes suggest that most of them have also been around in 10.x. Be Careful.
    Despite all these issues, IOT remains a highly recommended feature.

    First a recap.

    Briefly described, the IOT stores all data of a table in the leaf-blocks of the PK-index. This means there is no table-segment anymore, only an index segment. You can verify this from user_segments.

    On a well defined IOT, all records with the same parent key are grouped together in one single block or in a low number of adjacent blocks. This is an advantage on retrieval. All related data is generally retrieved in just one IO action. This speeds up retrieval (less IO is needed), but also makes more efficient use of the buffer-cache because the related records do not get dispersed over many blocks. The overhead of unused cache space is reduced. So far the known advantages.

    Now the bonus features.

    Bonus Feature #1: More data in the secondary index. As with normal tables, You can define other indexes on the IOT table. But those indexes now have an additional feature: The secondary index doesn’t contain a physical rowid, instead it contains the actual values of the PK fields. This means the 2ndary index is often ideal for index-only-lookup or just to join additional tables after retrieving only the index-blocks. There is no need to visit the table to fetch the PK fields. For those who used to inflate the 2ndary indexes to obtain
    Index-Only-Lookups: Use of IOTs can automatically lead to more IOL.

    A good example is an m:n relation where the linking table contains just two id’s. A conventional design would call for a heap table with two indexes. A total of 3 segments. An IOT would result in two segments: the PK and one index. Again, there is no table-segment. The PK-segment contains both columns in the leaf-blocks. And the 2ndary index contains the PK-values as an alternative to the ROWID. Any query always needs to search only one (index-) segment, and in each case, the required data will be grouped together in a single or low number of blocks, resulting in minimal IO and efficient use of buffer cache.

    Bonus Feature #2: Reduced density, avoiding of hot-blocks. Because there is no separate PK-segment, and because data and index are together in one segment, the nr of records per block is lower. Lower Density.
    Normally, the PKs are the most dense segments in a system, and therefore most likely to cause contention on DML transactions most likely to show up in gv$cache_transfer on a RAC system. On an IOT, the PK automatically contains fewer records per block because of the extra space required to store the “data” columns. If paired with a higher then default value of pctfree, an IOT can be used to reduce the impact of a “hot” index. This is especially useful in a RAC environment where hot blocks need to be transferred between instances.

    Some of the downsides.

    Inserts and updates are slower. This is partly a myth. In like for like comparisons, you may find the difference is not that big. When people complain about the slow loading of data into IOTs they often compare loading an IOT against a table with no indexes at all. If you can do without indexes, you can also do without IOTs. A more honest comparison would be to include the additional time required to create the indexes after loading, or to compare against a table with a PK already defined.
    Another way to speed up loading is to use the “ordered” option.

    A quirk: the physical guess.
    The 2ndary index on a IOT contains a so called row-guess to allow fast access to the leaf block of the PK were the row was originally stored. These row-guesses can grow stale, and the index statistics reflect this in the pct_direct_access value. I have never been able to demonstrate the effect, let alone the benefit of this “row guess”.
    A Challenge to investigative minds out there: Can someone prove me the usage and benefit of the row-guess. Ideally, a 10049 trace should be able to show the impact of the row-guess, and of “migrated” rows and stale row-guesses. (Update in 2009, With thanks to Barry Jones, who actually demonstrated 2ndary index deteriorated over time. Impressive).

    Some Best Practices for IOT:

    1) bad news first: check the bugs, even on 10.2.
    Although we have reaped good benefits from IOTs on a multi-Terabyte database, we did stumble across a number of Nasty bugs. We had notable problems on the gathering of statistics (dbms_stats) and with the use of Function Based Indexes (FBIs). On IOTs you can use both dbms_stats and FBIs, but beware of the bugs. Metalink is your friend.

    2) Determine a good pctfree.
    Set this value higher then default. We tend to use 30 instead of the default 10. This will at least partly avoid the general problem of slower DML on IOTs. Your optimal value will vary, depending in the amount of updates and inserts you do.

    3) create your own overflow segments.
    If your table contains many columns or large records, consider defining an Overflow segment and use it to store the columns that you will need less-frequently. If you avoid creating overflow segments, you may find that oracle does this for you. You probably want control over this yourself to avoid surprises.

    4) Keep blevel down, use partitioning.
    As access to the table-data will always go via the PK, the table-access as shown in an explain-plan is actually another PK lookup (note that I’m very skeptical about the row-guess). Therefore, the blevel of the PK has an impact on the number of blocks that need visiting before a data-record is retrieved. By partitioning the IOT you can make sure that the blevel stays sufficiently low. IMHO, a blevel of 2 is generally achievable, 3 is acceptable, and 4 is too high.
    Nb: monitoring blevel seems an often overlooked item, and it still applies to conventional indexes as well. But the impact of a high blevel on the PK of an IOT can be especially devastating.

    Final words:

    Start by reading up on the IOT feature, but then.Test, Verify, Tweak, and re-Test for your specific situation. IOTs are useful in a specific set of circumstances: Retrieval of related sets, avoidance of hot-spots, RAC scalability.
    As always, Your Mileage May Vary.

    Further reading:
    - RTFM (a bit of a duh, but yep, those pdfs are very useful)
    - metalink (same duh, but there is some really good info out there too, bugs and all)
    - OTN :

    Friday, 4 May 2007

    Simple Oracle DBA

    Introduction ? Simple !

    This blog will try to keep life simple for Oracle DBA's.

    Because Simplicity is the Ultimate Sophistication (L. da Vinci)

    Because Complexity sells better (E.W. Dijkstra)

    Because life is complicated enough.

    Simple Oracle : Oxymoron ?

    This presenter had a Very Drastic way to keep the audience awake! have a look...Presentations

    Been there and done that, and got the presentations and papers to show for it.

    Below are the links to my most Favored Rants material. Most of my public presentations have grown from either client-pitches, project-blunders or "architectural" frustrations. Hence most of them are slightly missionary: I want to convince the audience of something. Something Simple.

    High Availability: How to keep it simple (ppt)
    Talk customers through the options for high-availability.
    Start with "What do you really need", going through "what can you afford", and then match that with the possibilities. Ever more Complex...
    When in doubt: Simplify!
    The link is to my presentation from the Miracle DBF in 2006, but the message still stands.

    Databases Everywhere (ppt)
    Everywhere indeed.
    What can we do to keep the systems running, and keep life simple for the DBA? DBA Two-dot-Oh even.

    Upgrade Nightmares (pdf)
    My down-to-earth vision on upgrades.
    And what you can do to keep life simple.

    Is my Backup Covered (pdf)
    It is about Recovery!
    Can you Recover? Can someone else do your recovery for you?
    The most boring subject, but surprisingly turned into my most asked-for ppt

    Backup, at a different level
    A paper/blogpost, aimed a SOA "architects".
    Rather then cover "backup" at the disk- or database level, you can use messaging systems (SOA, ESB, BEPL) to distribute your data over two or more systems.
    Redundancy at a higher level in the stack. Use at own Risk!

    Index Organized Tables, and all the benefits.
    Blogpost/paper, aimed at developers/implementors and ... DBAs.
    I have driven many to despair with a strong belief in the efficiency of IOTs and other seemingly excotic table- and index definitions to store data physically.

    And if you feel like presenting at a usergroup, but dont know what about:
    Remember your last big frustration or problem ?
    Try turning that into a presentation. Surprisingly, that worked for me.