Re: RAC Parallel Query Issues
Thanks Mogens, It works great with no parallelism set. But when parallelism is set to DEFAULT it fails (intermittently). The tables are imported from a Non-Rac production environment. Found a note on Metalink last night which says DEFAULT is calculated as : CPU_COUNT*PARALLEL_THREADS_PER_CPU and DOP is DEFAULT * INSTANCES. The DOP it was using was 48 (6 cpus*4 thr/cpu*2 instances) This is the same setting in Production(infact with more cpus and hence larger DOP) but has been running fine. I guess the issue is magnified due to slaves being spawned at the other node and the QC sync'ing with them. I see huge number 'IPC send completion sync' events during this time. I had to alter degree from 'Default' to 2 for some tables and disable parallelism to smaller lookup tables to get it working. These went unnoticed in a single instance box. Thanks, Ravi. --- Mogens_Nørgaard [EMAIL PROTECTED] wrote: I'm not sure I understand. Is this a query that should access a 470 record table and which uses Parallel Query in a RAC environment? And when it works, it executes in less than a second? If yes, have you tested this query without Parallel Query? If no, where did I misunderstand you? Best regards, Mogens Ravi Kulkarni wrote: We are on 2-Node Rac 9204/Solaris8. We are having intermittent issues with parallel queries (The table has only 470 records, executes 1sec otherwise): SQL / select count(*)from eod * ERROR at line 1: ORA-12805: parallel query server died unexpectedly Trace File has: kxfp_send_callback Send timed out to slave 65535 inst 1 (qref 0x63f85fd0) *** 2003-09-15 18:33:25.034 *** SESSION ID:(63.533) 2003-09-15 18:33:25.034 kxfp_send_callback Send timed out to slave 65535 inst 1 (qref 0x63fc2004) kxfplsig Could not signal error 10388 in server P032 with serial 371201 dp 0x61a822d0, q 0x60f286b0, pr 0x5f457168, cqr 0x63f97584, err 10387 Interrupt Info top=1 size=8 top err=-2147473260 Query May be hanging.Check V$PX_SESSION OPIRIP: Uncaught error 12800. Error stack: ORA-12800: system appears too busy for parallel query execution ORA-10387: parallel query server interrupt (normal) v$px_session : has number of Slave Processes hanging. Truss for process on Instance 1: door_return(0x, 0, 0x, 0) (sleeping...) lwp_cond_wait(0xFE7F5548, 0xFE7F5558, 0xFE7EEDB0) (sleeping...) lwp_cond_wait(0xFE7F5548, 0xFE7F5558, 0xFE7EEDB0) Err#62 ETIME read(0, 0xFE6C35E4, 1024) (sleeping...) signotifywait() (sleeping...) door_return(0x, 0, 0x, 0) (sleeping...) lwp_cond_wait(0xFE7F5548, 0xFE7F5558, 0xFE7EEDB0) (sleeping...) Instance 2: poll(0x02FF15CC, 2, 2500) (sleeping...) poll(0x02FF15CC, 2, 2500) = 0 poll(0x02FF15CC, 2, 0) = 0 poll(0x02FF15CC, 2, 2500) (sleeping...) poll(0x02FF15CC, 2, 2500) = 0 poll(0x02FF15CC, 2, 0) = 0 poll(0x02FF15CC, 2, 2500) (sleeping...) poll(0x02FF15CC, 2, 2500) = 0 Any inputs would be greatly appreciated. Thanks, Ravi. __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: =?ISO-8859-1?Q?Mogens_N=F8rgaard?= INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Ravi Kulkarni INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: RAC Parallel Query Issues
Shouldn't happen, but one would have to question the use of PQ on such a small table anyway. You may be hitting some lower boundary condition that the developers didn't even look at because the table's so small? Pete Controlling developers is like herding cats. Kevin Loney, Oracle DBA Handbook Oh no, it's not. It's much harder than that! Bruce Pihlamae, long-term Oracle DBA -Original Message- Kulkarni Sent: Tuesday, September 16, 2003 10:54 AM To: Multiple recipients of list ORACLE-L We are on 2-Node Rac 9204/Solaris8. We are having intermittent issues with parallel queries (The table has only 470 records, executes 1sec otherwise): SQL / select count(*)from eod * ERROR at line 1: ORA-12805: parallel query server died unexpectedly Trace File has: kxfp_send_callback Send timed out to slave 65535 inst 1 (qref 0x63f85fd0) *** 2003-09-15 18:33:25.034 *** SESSION ID:(63.533) 2003-09-15 18:33:25.034 kxfp_send_callback Send timed out to slave 65535 inst 1 (qref 0x63fc2004) kxfplsig Could not signal error 10388 in server P032 with serial 371201 dp 0x61a822d0, q 0x60f286b0, pr 0x5f457168, cqr 0x63f97584, err 10387 Interrupt Info top=1 size=8 top err=-2147473260 Query May be hanging.Check V$PX_SESSION OPIRIP: Uncaught error 12800. Error stack: ORA-12800: system appears too busy for parallel query execution ORA-10387: parallel query server interrupt (normal) v$px_session : has number of Slave Processes hanging. Truss for process on Instance 1: door_return(0x, 0, 0x, 0) (sleeping...) lwp_cond_wait(0xFE7F5548, 0xFE7F5558, 0xFE7EEDB0) (sleeping...) lwp_cond_wait(0xFE7F5548, 0xFE7F5558, 0xFE7EEDB0) Err#62 ETIME read(0, 0xFE6C35E4, 1024) (sleeping...) signotifywait() (sleeping...) door_return(0x, 0, 0x, 0) (sleeping...) lwp_cond_wait(0xFE7F5548, 0xFE7F5558, 0xFE7EEDB0) (sleeping...) Instance 2: poll(0x02FF15CC, 2, 2500) (sleeping...) poll(0x02FF15CC, 2, 2500) = 0 poll(0x02FF15CC, 2, 0) = 0 poll(0x02FF15CC, 2, 2500) (sleeping...) poll(0x02FF15CC, 2, 2500) = 0 poll(0x02FF15CC, 2, 0) = 0 poll(0x02FF15CC, 2, 2500) (sleeping...) poll(0x02FF15CC, 2, 2500) = 0 Any inputs would be greatly appreciated. Thanks, Ravi. __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Ravi Kulkarni INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Pete Sharman INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: RAC Parallel Query Issues
I'm not sure I understand. Is this a query that should access a 470 record table and which uses Parallel Query in a RAC environment? And when it works, it executes in less than a second? If yes, have you tested this query without Parallel Query? If no, where did I misunderstand you? Best regards, Mogens Ravi Kulkarni wrote: We are on 2-Node Rac 9204/Solaris8. We are having intermittent issues with parallel queries (The table has only 470 records, executes 1sec otherwise): SQL / select count(*)from eod * ERROR at line 1: ORA-12805: parallel query server died unexpectedly Trace File has: kxfp_send_callback Send timed out to slave 65535 inst 1 (qref 0x63f85fd0) *** 2003-09-15 18:33:25.034 *** SESSION ID:(63.533) 2003-09-15 18:33:25.034 kxfp_send_callback Send timed out to slave 65535 inst 1 (qref 0x63fc2004) kxfplsig Could not signal error 10388 in server P032 with serial 371201 dp 0x61a822d0, q 0x60f286b0, pr 0x5f457168, cqr 0x63f97584, err 10387 Interrupt Info top=1 size=8 top err=-2147473260 Query May be hanging.Check V$PX_SESSION OPIRIP: Uncaught error 12800. Error stack: ORA-12800: system appears too busy for parallel query execution ORA-10387: parallel query server interrupt (normal) v$px_session : has number of Slave Processes hanging. Truss for process on Instance 1: door_return(0x, 0, 0x, 0) (sleeping...) lwp_cond_wait(0xFE7F5548, 0xFE7F5558, 0xFE7EEDB0) (sleeping...) lwp_cond_wait(0xFE7F5548, 0xFE7F5558, 0xFE7EEDB0) Err#62 ETIME read(0, 0xFE6C35E4, 1024) (sleeping...) signotifywait() (sleeping...) door_return(0x, 0, 0x, 0) (sleeping...) lwp_cond_wait(0xFE7F5548, 0xFE7F5558, 0xFE7EEDB0) (sleeping...) Instance 2: poll(0x02FF15CC, 2, 2500) (sleeping...) poll(0x02FF15CC, 2, 2500) = 0 poll(0x02FF15CC, 2, 0) = 0 poll(0x02FF15CC, 2, 2500) (sleeping...) poll(0x02FF15CC, 2, 2500) = 0 poll(0x02FF15CC, 2, 0) = 0 poll(0x02FF15CC, 2, 2500) (sleeping...) poll(0x02FF15CC, 2, 2500) = 0 Any inputs would be greatly appreciated. Thanks, Ravi. __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: =?ISO-8859-1?Q?Mogens_N=F8rgaard?= INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).