Oracle full table scans, direct path reads, object level checkpoints, ORA-8103s

Note: The video below is back online now! 

Here’s another hacking session video from last year:

Topics:

  • How do full table scans work?
  • How and why do the “ORA-8103: object no longer exists” errors happen
  • How does Oracle (11g) decide between a buffered full table scan and a direct path read scan (plus smart scan in Exadata)
  • What’s the difference between an object_id and data_object_id?

Comments

  1. Hi Tanel,
    During this hacking session,you had mentioned that how Oracle’s FTS works.
    root dba <— bootstrap$ <— seg$ <— table.I guess that you're searching for boot instead of root dba.

    Anyways i had traced the database before it's going from mount state to open mode,I noticed the fixed tables whose DDL found in bootstrap$ table are getting created.

    STORAGE ( OBJNO 12 TABNO 1) CLUSTER C_TS#(TS#) ….. Does it mean that a table with obj# 12 stored in this cluster C_TS#?

    and for some of the objects like UNDO$ a storage clause EXTENTS(FILE 1 BLOCK 220)…can you explain it?

    Also,I don't find corresponding INSERT into these tables.As soon as the database is opened,we can see that these fixed tables are populated.How??

    Thanks.
    Antony

  2. Hi Tanel,

    After watching through your session I am still trying to apprehend if there is a FTS and Oracle decides to do direct path read is it a good thing or bad thing from performance wise. I have recently got pulled into an issue where there is a very simple select query which is pulling 50 million rows and explain shows direct path read . I have to tune the query to run it faster and the only option for me was to use parallel option which still didn’t help much . Can you please show a scenario where there could be room for performance improvement if there is any atall.

    Thanks

Trackbacks

  1. […] the hacking session about Oracle full table scans and direct path reads I explained how the direct path read decision is not done by the optimizer, but instead during […]

  2. Full Table Scans and Direct Path Reads in Oracle…

    how do Oracle full table scans work and how does O…

Speak Your Mind