Case study on some rowcache internals, cached non-existent objects and a describe bug

I got a question regarding Metalink note 296235.1 about a describe bug which causes objects to “disappear” when they are described when database is not open.

It was an interesting case involving a bug, so I wrote a quite long analysis with test cases today. However when posting the entry to wordpress, it managed to completely mess up the formatting. After wasting half an hour trying to get the formatting correct I gave up and saved the article into a PDF instead.

You can access it here: http://www.tanelpoder.com/files/oracle_rowcache.pdf

By the way, I welcome more questions about Oracle internals or performance tuning.
Often when starting to explain something, I realize that I don’t really know the answer well enough, which is a valuable experience as it makes me figure it out and learn!

So, if you have a question, write it into a comment here or drop me an email to tanel@tanelpoder.com

This entry was posted in Oracle and tagged , , . Bookmark the permalink.

9 Responses to Case study on some rowcache internals, cached non-existent objects and a describe bug

  1. Very good read. Thanks for all the effort.

  2. Alessandro says:

    Thanks Tanel !!!

  3. tanelp says:

    Thanks Marco & special thanks to Alessandro for asking this interesting question :)

  4. Wow, great explanation Tanel. Now, lets explain why running “alter/**/session/**/set/**/nls_date_format=’dd/mm/yyyy hh24:mi:ss’” segfaults on me :-) Did you notice that you can run most commands without using whitespaces at all thus defeating a lot of security tools using pattern matching?

    Slavik

  5. Sokrates says:

    Tanel,

    very interesting, thanx.

    there is another bug: When you describe dual when database mounted, you won’t be able to open it afterwards !

    SQL> startup mount
    ORACLE instance started.

    Total System Global Area 922746880 bytes
    Fixed Size 1222624 bytes
    Variable Size 146802720 bytes
    Database Buffers 767557632 bytes
    Redo Buffers 7163904 bytes
    Database mounted.
    SQL> describe dual
    ERROR:
    ORA-04043: object dual does not exist

    SQL> alter database open;
    alter database open
    *
    ERROR at line 1:
    ORA-01092: ORACLE instance terminated. Disconnection forced

    It’s definitely the
    describe dual
    which causes the ORA-01092

    Could you shed some light on this ?

  6. tanelp says:

    Hi Slavik! This is a good one. I took a quick look and it looks sqlplus crashes when receiving the reply from the server, not during sending it. This makes the case even more interesting :) I think i need to write another blog entry ;)

    Btw, I once blogged about writing SQL without whitespace, including
    the /**/ approach..

    http://blog.tanelpoder.com/2008/01/14/can-you-write-a-working-sql-statement-without-using-any-whitespace/

    …yep it may break a lot of bad apps ;)

    Sokrates, good finding :)
    This crash seems to be because the database is opened by your current server process, which can not access DUAL table due the describe bug again. But there are some SQL statements which select from DUAL when opening the db, so if some of them unexpectedly fail, the server process has no other choice than abort he instance.

    Here’s a tail piece of my alert.log after this DUAL crash happened:

    Errors in file /u01/app/oracle/admin/LIN10G/udump/lin10g1_ora_3193.trc:
    ORA-00942: table or view does not exist
    Thu Aug 21 08:37:30 2008
    Error 942 happened during db open, shutting down database
    USER: terminating instance due to error 942
    Instance terminated by USER, pid = 3193
    ORA-1092 signalled during: alter database open…

  7. Freek says:

    Tanel,

    Could you shed some light on when a session might get an “ORA-08103: object no longer exists” error when selecting from a table that is truncated by another session?

    I have blogged (http://freekdhooge.wordpress.com/2007/12/25/can-a-select-block-a-truncate/) about this in the past and I understand that it is the increase of the dataobj# column in obj$ that causes this, but I have not been able to discover when a session get this error.

    regards,

    Freek

  8. Miladin says:

    SQL> startup mount;
    ORACLE instance started.

    Total System Global Area 1603411968 bytes
    Fixed Size 2144824 bytes
    Variable Size 922748360 bytes
    Database Buffers 671088640 bytes
    Redo Buffers 7430144 bytes
    Database mounted.
    SQL> desc dual;
    ERROR:
    ORA-04043: object dual does not exist

    SQL> alter database open;
    alter database open
    *
    ERROR at line 1:
    ORA-01092: ORACLE instance terminated. Disconnection forced
    Process ID: 17765
    Session ID: 170 Serial number: 5

    let’s try again:

    SQL> startup mount;
    ORACLE instance started.

    Total System Global Area 1603411968 bytes
    Fixed Size 2144824 bytes
    Variable Size 922748360 bytes
    Database Buffers 671088640 bytes
    Redo Buffers 7430144 bytes
    Database mounted.
    SQL> desc dual
    ERROR:
    ORA-04043: object dual does not exist

    now execute

    SQL> alter system flush shared_pool;

    System altered.

    SQL> alter database open;

    Database altered.

    SQL>

  9. tanelp says:

    Yes, this is because a shared pool flush flushes out also the “non-existent markers” from rowcache (I also demoed this in my paper).

    Another way is to try to create the “lost” table again, this creation will fail due a primary key violation on data dictionary tables, the recursive data dictionary transaction will be rolled back and corresponding rowcache entries invalidated. Flush shared pool is safer though.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>