Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Oracle Parallel Query Server Error

Posted on 2014-12-02
12
Medium Priority
?
634 Views
Last Modified: 2014-12-03
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
0
Comment
Question by:xoxomos
  • 6
  • 5
12 Comments
 

Author Comment

by:xoxomos
ID: 40477874
parallel_server is false again at vendor suggestion :-)
0
 
LVL 14

Expert Comment

by:Alexander Eßer [Alex140181]
ID: 40478022
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
 
LVL 78

Accepted Solution

by:
slightwv (䄆 Netminder) earned 2000 total points
ID: 40478339
>>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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

Author Comment

by:xoxomos
ID: 40478928
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
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40479023
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
 

Author Comment

by:xoxomos
ID: 40479639
Guess not.  Still running.  Usually abends in two minutes.
0
 

Author Closing Comment

by:xoxomos
ID: 40479643
"parallel_server isn't related to parallel query."
Looks like that was it.  Thanx again.
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40479692
>>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
 

Author Comment

by:xoxomos
ID: 40479813
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
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40479845
>>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
 

Author Comment

by:xoxomos
ID: 40479862
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
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40479958
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

Featured Post

Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
Suggested Courses

927 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question