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.
=> 'FOR ALL COLUMNS SIZE AUTO',
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!
The docs note this about the ORA-01115 error, the ORA-01115 is a transient
I/O error not related to block corruption:
IO error reading block from file string (block # string)
Device on which the file resides is probably offline
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
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:
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
- offline OR
- 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:
- restore access to the device OR
- take out files which are not needed in
order to gain more space