Oracle Parallel Query Server Error

Getting this error when attempting to run a query some wizard at the vendor's wrote:

ERROR at line 1:
ORA-12801: error signaled in parallel query server P002
ORA-01114: IO error writing block to file  (block # )
ORA-01114: IO error writing block to file 202 (block # 3545821)
ORA-27072: File I/O error
Additional information: 4
Additional information: 3545821
Additional information: 16384

At the time it aborted, it had used 140G temp space.  I had increased sort_area_size to 2G and was unable to get it any larger.  What kind of settings do i need to get this query to complete.  I've got SGA 24G PGA 48G on vendors recommendation.
dans-query.txt
xoxomosAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

xoxomosAuthor Commented:
parallel_server is false again at vendor suggestion :-)
0
Alexander Eßer [Alex140181]Software DeveloperCommented:
parallel_server is false again at vendor suggestion :-)
What does that mean?! Did you disable that feature then?! I assume you're running EE...
Did you look at the alert.log for more information:

from http://www.dba-oracle.com/t_ora_12801_parallel_query.htm
MOSC  note 184416.1 has details on diagnosing the ORA-12801 error.  It notes that the ORA-12801 is somewhat generic and that you should look for more details in your alert.log or in trace files in the USER_DUMP_DEST or BACKGROUND_DUMP_DEST
 location.

Hints:
http://www.dba-oracle.com/t_ora_1115_io_error_reading_block_from_file.htm
http://kris-oradba.blogspot.de/2008/05/ora-12801-ora-01114-ora-27072-and-linux.html
0
slightwv (䄆 Netminder) Commented:
>>ORA-01114: IO error writing block to file 202

What file is 202?

select file_name, tablespace_name from dba_data_files where file_id=202;

>>Did you disable that feature then?!

parallel_server isn't related to parallel query.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

xoxomosAuthor Commented:
What file is 202?
That's what I want to know :-)  Does not even show up when I select file_name, file_id from dba_data_files.
Nor when i

SQL> select file_name, tablespace_name from dba_data_files where file_id=202;

no rows selected

Now the "parallel_server isn't related to parallel query."  is beginning to be apparent.  About an hour ago i ran
SQL> ALTER session disable parallel query;

Session altered.

SQL> alter session set sort_area_size = 2097152000;

Session altered.

SQL> @dans_test_query.sql ;

This time very different behavior.  It's not grabbing and extending all that TEMP.
If this continues, i should be seeing something different at least in another hour or so.
0
slightwv (䄆 Netminder) Commented:
Probably a TEMP tablesapace file.  I keep forgetting TEMP files have a different view.

select file_name, tablespace_name from dba_temp_files where file_id=202;

My guess is the temp data file is set to auto extend and tried to extend larger than the OS would allow or you filled up the disk to where the temp file couldn't grow larger.
0
xoxomosAuthor Commented:
Guess not.  Still running.  Usually abends in two minutes.
0
xoxomosAuthor Commented:
"parallel_server isn't related to parallel query."
Looks like that was it.  Thanx again.
0
slightwv (䄆 Netminder) Commented:
>>Looks like that was it.  Thanx again.

I don't understand.  Setting parallel_server to true or false has nothing to do with the error or if the query runs in parallel or not.

I don't see how this could be the answer.

The reason it is likely still running is you disabled parallel query and it isn't using near the TEMP space.

The error leans towards you filled up either the disk or the file grew to large for the operating system.

I would like to unaccept this so it can be closed properly.
0
xoxomosAuthor Commented:
Sorry, I misspoke.  I should have said you are correct in that parallel_server has nothing to do with parallel query.   It was when i did the :
SQL> ALTER session disable parallel query;
that seems to have made the difference.
0
slightwv (䄆 Netminder) Commented:
>>that seems to have made the difference.

That stopped the filling up of TEMP but not sure it really allows things to run but time will tell...
0
xoxomosAuthor Commented:
mmmmmmm...has been going a long time, but, the query has been top activity pretty much all day.  
Are you thinking nothing is really happening?
0
slightwv (䄆 Netminder) Commented:
The query is running, just not in parallel.

Parallel queries need to store their results as they retrieve them so it uses TEMP.

Think of it this way:
You need 100 items and have 10 people to get them.  you give each one 10 items to go get.  You need 10 temp places to store the 10 items before you bring them all together.  This is parallel query and it uses TEMP space to store the work in progress.

What you are running now is:
You need 100 items but it is just YOU.  You are going around trying to get ALL 100 items.

Depending on your transactions and redo, you might end up getting a "ORA-01555 Snapshot Too Old".

Queries are read-consistent.  This means that the data MUST remain consistent from the time the query started.

If there are DML transactions going on while the query is running, Oracle uses the redo logs to look at the data as it existed back when you started the query.

If the query takes to long and the redo needed is no longer available, you'll get the ORA-01555.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.