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.

11 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.

  10. Tushar says:

    We tried below senario:-
    SQL> startup mount
    ORACLE instance started.

    Total System Global Area 221331456 bytes
    Fixed Size 2251856 bytes
    Variable Size 163578800 bytes
    Database Buffers 50331648 bytes
    Redo Buffers 5169152 bytes
    Database mounted.
    SQL> desc dba_tables
    ERROR:
    ORA-04043: object dba_tables does not exist

    SQL> desc dba_tables
    ERROR:
    ORA-04043: object dba_tables does not exist

    SQL> desc dba_tables
    ERROR:
    ORA-04043: object dba_tables does not exist

    SQL> desc dba_tables
    ERROR:
    ORA-04043: object dba_tables does not exist

    SQL> desc dba_tables
    ERROR:
    ORA-04043: object dba_tables does not exist

    SQL> alter database open;

    Database altered.

    SQL> desc dba_tables
    Name Null? Type
    —————————————– ——– —————————-
    OWNER NOT NULL VARCHAR2(30)
    TABLE_NAME NOT NULL VARCHAR2(30)
    TABLESPACE_NAME VARCHAR2(30)
    CLUSTER_NAME VARCHAR2(30)
    IOT_NAME VARCHAR2(30)
    STATUS VARCHAR2(8)
    PCT_FREE NUMBER
    PCT_USED NUMBER
    INI_TRANS NUMBER
    MAX_TRANS NUMBER
    INITIAL_EXTENT NUMBER
    NEXT_EXTENT NUMBER
    MIN_EXTENTS NUMBER
    MAX_EXTENTS NUMBER
    PCT_INCREASE NUMBER
    FREELISTS NUMBER
    FREELIST_GROUPS NUMBER
    LOGGING VARCHAR2(3)
    BACKED_UP VARCHAR2(1)
    NUM_ROWS NUMBER
    BLOCKS NUMBER
    EMPTY_BLOCKS NUMBER
    AVG_SPACE NUMBER
    CHAIN_CNT NUMBER
    AVG_ROW_LEN NUMBER
    AVG_SPACE_FREELIST_BLOCKS NUMBER
    NUM_FREELIST_BLOCKS NUMBER
    DEGREE VARCHAR2(10)
    INSTANCES VARCHAR2(10)
    CACHE VARCHAR2(5)
    TABLE_LOCK VARCHAR2(8)
    SAMPLE_SIZE NUMBER
    LAST_ANALYZED DATE
    PARTITIONED VARCHAR2(3)
    IOT_TYPE VARCHAR2(12)
    TEMPORARY VARCHAR2(1)
    SECONDARY VARCHAR2(1)
    NESTED VARCHAR2(3)
    BUFFER_POOL VARCHAR2(7)
    FLASH_CACHE VARCHAR2(7)
    CELL_FLASH_CACHE VARCHAR2(7)
    ROW_MOVEMENT VARCHAR2(8)
    GLOBAL_STATS VARCHAR2(3)
    USER_STATS VARCHAR2(3)
    DURATION VARCHAR2(15)
    SKIP_CORRUPT VARCHAR2(8)
    MONITORING VARCHAR2(3)
    CLUSTER_OWNER VARCHAR2(30)
    DEPENDENCIES VARCHAR2(8)
    COMPRESSION VARCHAR2(8)
    COMPRESS_FOR VARCHAR2(12)
    DROPPED VARCHAR2(3)
    READ_ONLY VARCHAR2(3)
    SEGMENT_CREATED VARCHAR2(3)
    RESULT_CACHE VARCHAR2(7)

    SQL>
    But we able to get the results.

  11. Simon says:

    Just happened to be working on a 12.1.0.2 system when I saw this “from the past” tweeted.

    At CDB level:

    [oracle@orcl12c ~]$ sq

    SQL*Plus: Release 12.1.0.2.0 Production on Thu Sep 4 10:17:57 2014

    Copyright (c) 1982, 2014, Oracle. All rights reserved.

    Connected to an idle instance.

    SQL> startup mount;
    ORACLE instance started.

    Total System Global Area 301989888 bytes
    Fixed Size 2923680 bytes
    Variable Size 243270496 bytes
    Database Buffers 50331648 bytes
    Redo Buffers 5464064 bytes
    Database mounted.
    SQL> desc dba_tables;
    ERROR:
    ORA-04043: object dba_tables does not exist

    SQL> alter database open;

    Database altered.

    SQL> desc dba_tables;
    Name Null? Type
    ----------------------------------------- -------- ----------------------------
    OWNER NOT NULL VARCHAR2(128)
    TABLE_NAME NOT NULL VARCHAR2(128)
    TABLESPACE_NAME VARCHAR2(30)
    CLUSTER_NAME VARCHAR2(128)
    IOT_NAME VARCHAR2(128)
    STATUS VARCHAR2(8)
    PCT_FREE NUMBER
    PCT_USED NUMBER

    --snip--

    SQL> select sys_context('USERENV','CON_NAME') from dual;

    select dbid, guid, name, open_mode from v$pdbs;
    SYS_CONTEXT('USERENV','CON_NAME')
    --------------------------------------------------------------------------------
    CDB$ROOT

    SQL> SQL>

    DBID GUID NAME
    ---------- -------------------------------- ------------------------------
    OPEN_MODE
    ----------
    1168718794 FEDD4BBEFD661AE5E0431500A8C0AB96 PDB$SEED
    READ ONLY

    241082563 FEDD577E45BB1D7EE0431500A8C07D2D PDBORCL
    READ WRITE

    and at PDB – you’d expect it to be consistent:


    SYS@192.168.56.20:1523/pdborcl> desc dba_objects;
    ERROR:
    ORA-04043: object dba_objects does not exist

    SYS@192.168.56.20:1523/pdborcl> alter pluggable database PDBORCL open;

    Operation 227 succeeded.

    SYS@192.168.56.20:1523/pdborcl> desc dba_objects;
    Name Null? Type
    ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ -------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    1 OWNER VARCHAR2(128)
    2 OBJECT_NAME VARCHAR2(128)
    3 SUBOBJECT_NAME VARCHAR2(128)
    4 OBJECT_ID NUMBER
    5 DATA_OBJECT_ID NUMBER
    6 OBJECT_TYPE VARCHAR2(23)
    7 CREATED DATE
    8 LAST_DDL_TIME

    –snip–

    So looks like the bug identified was fixed… :-)

    ….actually, just checking other envs, it was fixed in 11.2 as well… :-)

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>