Problem solve Get help with specific problems with your technologies, process and projects.

Questions about INITRANS, chained rows, and data cache read efficiency

I am using Oracle 9i for my database. I have several questions:

  1. INITRANS is a parameter that indicates the concurrency level of transactions when working inside a database block. Where is this parameter stored? When and how can this be set or changed?
  2. I can find out if there are any chained rows in my database using the "select name, value from v$sysstat where 'table fetch by continued row'; if the number is not zero then there are rows chained. What I do not know is which table or tables have this problem? I am supposed to use ANALYZE TABLE tablename LIST CHAINED ROWS; to find out. But if I have 100 tables, I do not want to run this command one by one against all 100 tables. How do I figure out where my chained rows are?
  3. Data cache Read Efficiency (RE) is defined as percentage of data reads satisfied by the cache. If N is the total number of reads issued, M is the number of reads that were done against the disk, then N-M is the number that were satisfied by information in the cache. Therefore, RE=(N-M)/100%. How do I get these stats? Is there a view that contains this information? Do I have to do some type of analysis to get them?
  4. There is also a similar question on Procedure Cache efficiency. How do I get the RE for the procedure cache?

Here are responses to your questions:

  1. INITRANS is an attribute of a table definition; you can find it in USER_TABLES.
  2. I'm not aware of a way to find chained rows other than using ANALYZE TABLE...LIST CHAINED ROWS. You can easily generate a script (using SQL*Plus) to perform the command:
    set pages 0 feedb off trimspool on
    select 'analyze table '||table_name||' list chained rows;' from tabs
    spool antab.sql
    spool off
  3. As for read efficiency, the formula is usually given as Buffer Cache Hit Ratio:
    (1 - PIO/LIO) * 100
    where PIO = physical I/O and LIO = logical I/O. 

    However, the use of this ratio in performance optimization has been pretty well discredited. You can measure (at least indirectly) shared pool efficiency by observing the ratio of hard to soft parses in your system. See v$sesstat for the relevant statistics.

Dig Deeper on Oracle database performance problems and tuning

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.