DB File Sequential Read

An Oracle session logs the db file sequential read wait event when it has to wait for a single-block I/O read request to complete. Oracle issues single-block I/O read requests when reading from indexes, rollback segments, sort segments, control files, datafile headers and tables (when tables are accessed via rowids). A sequential read is usually a single-block read, although it is possible to see sequential reads for more than one block (See P3).  To determine the actual object being waited can be checked by the p1, p2, p3 info in v$session_wait.

SELECT p1 "file#", p2 "block#", p3 "class#",event

FROM v$session_wait

WHERE event IN ('db file sequential read');

Select * from   v$session_event

where  event = 'db file sequential read'

order by time_waited;

Select segment_name, partition_name, segment_type, tablespace_name

from   dba_extents a, v$session_wait b

where  b.p2 between a.block_id and (a.block_id + a.blocks - 1)

and    a.file_id  = b.p1

and    b.event    = 'db file sequential read';

Select a.sid, a.serial#, a.username, a.osuser, b.sql_text

from   v$session a, v$sqltext b

where  a.sql_hash_value = b.hash_value

and    a.sql_address    = b.address and    a.sid in (select sid from   v$session_wait

where  event = 'db file sequential read')

order by a.sid, b.hash_value, b.piece;

Note: Where P1 = file#, P2 = block#, P3 = blocks 9 (should be 1)

Generally the entire database having some wait event doing IO for index scan usually. But if you see seconds in waiting greater then 0, you must tune index I/O.

To reduce this wait event follow the below points:

1.      Tuning SQL statements to reduce unnecessary I/O request is the only guaranteed way to reduce "db file sequential read" wait time.

2.      Distribute the index in different file system to reduce the contention for I/O
Tuning Physical devices, the data on different disk to reduce the I/O.

3.      Use of Faster disk reduces the unnecessary I/O request.
Increase db_block_buffers or larger buffer cache sometimes can help.

FAST_START_MTTR_TARGET

FAST_START_MTTR_TARGET enables you to specify the number of seconds the database takes to perform crash recovery of a single instance.

As we know the FAST_START_MTTR_TARGET initialization parameter specify the maximum no. of seconds for data to stay in memory before DBW0 can write this data to actual data file.

The default is 300 seconds (5 Minutes). and maximum we can give 3600 ( 1 Hour).

Also since there can be upt to 10 DBWn processes a DBA can start and default is only one. 

LOG_CHECKPOINT_INTERVAL (in oracle 8.0)

LOG_CHECKPOINT_INTERVAL specifies the frequency of checkpoints in terms of the number of

redo log file blocks that are written between consecutive checkpoints.

LOG_CHECKPOINT_INTERVAL (in oracle 8i)

LOG_CHECKPOINT_INTERVAL specifies the frequency of checkpoints in terms of the number of

redo log file blocks that can exist between an incremental checkpoint and the last block

written to the redo log. This number refers to physical operating system blocks, not

database blocks.

FAST_START_MTTR_TARGET:

The FAST_START_MTTR_TARGET initialization parameter simplifies the configuration of recovery time from instance or system failure. This parameter lets you specify the number of seconds crash or instance recovery is expected to take. The FAST_START_MTTR_TARGET is internally converted to a set of parameters that modify the operation of Oracle such that recovery time is as close to this estimate as possible.

Note: 
You should disable or remove the FAST_START_IO_TARGET, LOG_CHECKPOINT_INTERVAL, and LOG_CHECKPOINT_TIMEOUT 

initialization parameters when using FAST_START_MTTR_TARGET. Setting these parameters to active values interferes with FAST_START_MTTR_TARGET, resulting in a different than expected value in the TARGET_MTTR column of the V$INSTANCE_RECOVERY view.

Whenever you set FAST_START_MTTR_TARGET to a nonzero value, and while MTTR advisory is ON, Oracle Corporation recommends that you disable (set to 0) the following parameters:

LOG_CHECKPOINT_TIMEOUT 
LOG_CHECKPOINT_INTERVAL 
FAST_START_IO_TARGET 

Because these initialization parameters either override FAST_START_MTTR_TARGET or potentially drive checkpoints more aggressively than FAST_START_MTTR_TARGET does, they can interfere with the simulation.

if i remove LOG_CHECKPOINT_TIMEOUT from pfile the parameter appears with the value of 1800, so i set that to 0 in the pfile.

TNS Errors

Error: 32: Broken pipe

Error stack in listener log:

TNS-12518: TNS:listener could not hand off client connection

TNS-12547: TNS:lost contact

TNS-12560: TNS:protocol adapter error

TNS-00517: Lost contact

IBM/AIX RISC System/6000 Error: 32: Broken pipe

Cause:

The error 32 indicates the communication has been broken while the listener is trying to hand off the client connection to the server process or dispatcher process.

Action:

1. One of reason would be processes parameter being low, and can be verified by the v$resource_limit view.

2. In Shared Server mode, check the 'lsnrctl services' output and see if the dispatcher has refused any connections, if so, then consider increasing the number of dispatchers.

3. Check the alert log for any possible errors.

4. Memory resource is also another cause for this issue. Check the swap, memory usage of the OS.

______________________________________________________________________________

Error: 11: Resource temporarily unavailable

Error stack in listener log:

TNS-12518: TNS:listener could not hand off client connection

TNS-12549: TNS:operating system resource quota exceeded

TNS-12560: TNS:protocol adapter error

TNS-00519: Operating system resource quota exceeded

IBM/AIX RISC System/6000 Error: 11: Resource temporarily unavailable

Cause:

As the error indicates operating system resource has exceeded.

Action:

1. Increase the appropriate OS kernal parameters for 'maximum number of processes allowed per user'. For example for HP-UX the parameters are maxuprc and nproc.

_______________________________________________________________________________

Error: 12: Not enough space

Error stack in listener log:

TNS-12518: TNS:listener could not hand off client connection

TNS-12549: TNS:operating system resource quota exceeded

TNS-12560: TNS:protocol adapter error

TNS-00519: Operating system resource quota exceeded

IBM/AIX RISC System/6000 Error: 12: Not enough space

Cause:

This is a memory related issue. The error indicates that there is not enough memory available to spawn and hand off the client connections.

Typical problems are:

- Out of system memory / swap

- Out of process slots in the process table

- Streams resources depleted

- Out of File Handles

- sga memory usage

Action:

1. Check in the alert log for any possible memory related error.

2. Increase swap/Virtual memory if possible the available memory.

3. SGA and PGA can be reduced to address the memory consumption.

4. MTS mode can be used to reduce the amount of process and memory consumption.

________________________________________________________________________________

Error: Connection Pooling limit reached

Error stack in listener log:

TNS-12518 TNS:listener could not hand off client connection

TNS-12564 TNS:connection refused

TNS-12602 TNS: Connection Pooling limit reached

Action:

1. Try increasing initial number of dispatcher.

________________________________________________________________________________

Error: 2: No such file or directory

Error stack in listener log:

TNS-12518: TNS:listener could not hand off client connection

TNS-12560: TNS:proto adapter error

TNS-00530: Proto adapter error

32-bit Windows Error: 2: No such file or directory

Error Description:ERROR_FILE_NOT_FOUND

2The system cannot find the file specified.

Cause:

This indicates the database service is not actually available

Action:

1.Verify if the inteneded database really up and accepting local BEQ connections.

_________________________________________________________________________________

Section III: Errors Specific to Windows

It is also recommended that you refer the Section I above for a generic troubleshooting approach to the error TNS-12518.

This section briefly describes about the errors that are encountered on Windows Operating 

System. TNS-12518 most commonly occurs on 32-bit OS due to its memory constraint, 

however TNS-12518 can occur on 64-bit OS as well.

__________________________________________________________________________________

Error: 233: Unknown error

Error stack in listener log:

TNS-12518: TNS:listener could not hand off client connection

TNS-12560: TNS:protocol adapter error

TNS-00530: Protocol adapter error

32-bit Windows Error: 233: Unknown error

Error Description: ERROR_PIPE_NOT_CONNECTED

233 No process is on the other end of the pipe.

Cause:

The communication has been broken while the listener is trying to hand off the client connection to the server process or dispatcher process.

Action: Refer Note 371983.1

____________________________________________________________________________________

Error: 54: Unknown error

Error stack in listener log:

TNS-12518: TNS:listener could not hand off client connection

TNS-12571: TNS:packet writer failure

TNS-12560: TNS:protocol adapter error

TNS-00530: Protocol adapter error

32-bit Windows Error: 54: Unknown error

Error Description: ERROR_NETWORK_BUSY

540x36 The network is busy.

Cause: 

This indicates a bottleneck at the network layer(TCP/IP).

Action: 1.Try increasing dispatchers and shared servers.


TNS-12560-TNS-00583


TNS-12516 - TNS-12519