Solved

Oracle Parallel Query Server Error

Posted on 2014-12-02
12
533 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
Comment Utility
parallel_server is false again at vendor suggestion :-)
0
 
LVL 13

Expert Comment

by:Alexander Eßer [Alex140181]
Comment Utility
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 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
Comment Utility
>>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
 

Author Comment

by:xoxomos
Comment Utility
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 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
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
Comment Utility
Guess not.  Still running.  Usually abends in two minutes.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Closing Comment

by:xoxomos
Comment Utility
"parallel_server isn't related to parallel query."
Looks like that was it.  Thanx again.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
>>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
Comment Utility
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 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
>>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
Comment Utility
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 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

744 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now