I am using Oracle 9i for my database. I have several questions:
- 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?
- 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?
- 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?
- 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:
- INITRANS is an attribute of a table definition; you can find it in USER_TABLES.
- 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
- 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.