ORA-1115 IO error reading block from file tips

Question:  I am running a dbms_stats job with default_degree and I'm getting a ORA-01115 error.  This is an 8 CPU server running 10g on HP/UX.

BEGIN DBMS_STATS.GATHER_DICTIONARY_STATS
   (ESTIMATE_PERCENT => DBMS_STATS.AUTO_SAMPLE_SIZE,
    METHOD_OPT       => 'FOR ALL COLUMNS SIZE AUTO',
    DEGREE           => DBMS_STATS.DEFAULT_DEGREE,
    GRANULARITY      => 'ALL',
    CASCADE          => TRUE,
    OPTIONS          => 'GATHER',
    NO_INVALIDATE    => FALSE);
END;

*
ERROR at line 1:
ORA-12801: error signaled in parallel query server P012
ORA-01115: IO error reading block from file 22 (block # 969)
ORA-27072: File I/O error

I only have 8 CPU's and I don't understand why Oracle fired-off 12 parallel query slaves (parallel query server P012).  What causes this ORA-01115 error in automatic parallel query?  When I manually reduce the parallelism, it runs fine!

Answer:  The docs note this about the ORA-01115 error, the ORA-01115 is a transient I/O error not related to block corruption:

ORA-01115: IO error reading block from file string (block # string)

Cause: Device on which the file resides is probably offline

Action: Restore access to the device

Using 12 parallel query slaves probably not optimal for an 8 CPU server, and Oracle may have over-estimated the optimal degree of parallelism.  Here are the rules I use for optimizing the degree of parallelism.

Also note that using parallel automatic tuning can HURT your performance, and I prefer to set parallelism manually, on a case-by-case basis.

I suspect that your error is caused by firing off too many parallel query slaves, but you should also check for a bug on MOSC.

You can pinpoint the tablespace and segment for an ORA-01444 error by plugging-in the file_id and block_id into this query on dba_extents:

select
owner,
tablespace_name,
segment_type,
segment_name
from
dba_extents
where
file_id = 1030
and
block_id = 602122;

In some cases, the ORA-01114 error occurs when a whole disk or mount point is full. ORA-01115 can occur when you attempt to write to a file and the device with the file is either:

1. offline OR
2. has run out of space, possibly because it is a temporary file which was not allocated at creation time.

To resolve ORA-01114, you should either:

1. restore access to the device OR
2. take out files which are not needed in order to gain more space