Tip#17 How to find out 32/64 Bit

Find out whether OS is 32 bit or 64 bit

Linux : getconf LONG_BIT

Solaris : /usr/bin/isainfo -kv

AIX : getconf -a | grep KERN

HPUX : /usr/bin/getconf KERNEL_BITS

Windows : Start>All Programs>accessories> System Tools>System Information> System summary


Find out whether Oracle Software is 32 bit or 64 bit.

Method # 1
cd $ORACLE_HOME/bin/
file oracle

Method # 2
sqlplus / as sysdba (Look for "Connected to:")

Method # 3
If the two directories $ORACLE_HOME/lib32 and $ORACLE_HOME/lib are existing then it is 64 bit else 32 bit

Tip#16 Fix Migrated/Chained rows

What do they mean?

Row chaining is what happens when a row cannot fit into a single Oracle block ever, because it is just too big. Oracle therefore has to break it up into several smaller row pieces, store each piece in a separate block, and link (or “chain”) from one block to another so that the entire set of pieces can be retrieved when you query the table for the row.

Row migration is what happens when a row grows in size and can no longer fit into its original Oracle block: Oracle will move it (or ‘migrate’ it) into a completely new block into which it can fit.

How to Find them?

Assuming you have latest stats for the tables, run following SQL to find out if you have any tables with high percentage of chained or migrated rows in a table.

SELECT owner, table_name, num_rows, chain_cnt,
round((chain_cnt * 100 / num_rows),2) pct, pct_free, pct_used
FROM dba_tables WHERE chain_cnt > 0
AND owner NOT IN ('SYS', 'SYSTEM')
ORDER BY 5 desc ;

If you have any tables with large number of chained/migrated rows then follow the below steps to fix it,

How do I fix them?

1. connect to the database as the owner of the table having chained rows run utlchain.sql script which is in your ORACLE_HOME/rdbms/admin. It should create a table called 'chained_rows'.

2. Analyze the table suppose that the table having the issue is called: MBS

SQL>analyze table MBS list chained rows into chained_rows;

3. The above statement will load the chained row information into the table created in step 1

SQL>select * from chained_rows;

4. To fix the rows, put them in a temp table and remove it from original table and copy them from temp table to original table

create table my_tmp AS select * from mbs where rowid IN (select head_rowid from chained_rows where table_name = 'MBS');


DELETE FROM MBS WHERE rowid IN (select head_rowid from chained_rows where table_name = 'MBS');


INSERT INTO MBS SELECT * FROM my_tmp;

COMMIT;

Any Suggestion to avoid it?

Increase PCTFREE to avoid the future problem for those tables.

Tip#15 DBFile Sequential and Scattered Reads

Both "db file sequential read" and "db file scattered read" events signify time waited for I/O read requests to complete. Most people confuse these events with each other as they think of how data is read from disk. Instead they should think of how data is read into the SGA buffer cache.

db file sequential read:

A sequential read operation reads data into contiguous memory (usually a single-block read with p3=1, but can be multiple blocks). Single block I/Os are usually the result of using indexes. This event is also used for rebuilding the controlfile and reading datafile headers (P2=1). In general, this event is indicative of disk contention on index reads.

db file scattered read:

Similar to db file sequential reads, except that the session is reading multiple data blocks and scatters them into different discontinuous buffers in the SGA. This statistic is NORMALLY indicating disk contention on full table scans. Rarely, data from full table scans could be fitted into a contiguous buffer area, these waits would then show up as sequential reads instead of scattered reads.

The following query shows average wait time for sequential versus scattered reads:

select a.average_wait "SEQ READ", b.average_wait "SCAT READ"
from sys.v_$system_event a, sys.v_$system_event b
where a.event = 'db file sequential read'
and b.event = 'db file scattered read';