RE: Hanging query puzzle
Kirti, I've been searching on the IBM site for aiostat without much luck. What kind of information does it give you? (if I'm going to convince my SA to call IBM about this I'll need a very good argument. I'm still working on getting the rights to use sar). Henry -Original Message- Kirti Sent: Wednesday, February 05, 2003 8:29 PM To: Multiple recipients of list ORACLE-L Jeff, If you see the SEQ# field in the v$session_wait view not incrementing, then something else, other than Oracle, is causing a hang up... On AIX, if you have AIO enabled, try running 'aiostat'. It will show if there are any pending AIO requests. The difficult part would be to relate those pending calls to your session. It is quite likely that a particular AIO server may show waits on I/O, if this hang up is IO related. You can try 'pstat' to find out AIO server processes and associated 'pid' to dig deeper. If you do not have 'aiostat', ring up IBM Support. It is a nice utility to have, and it is free. For those on AIX 5L, can you please check if 'aiostat' is available as a standard distribution? I was told by IBM Support that 'aiostat' was going to be available with AIX 5L. - Kirti -Original Message- Sent: Wednesday, February 05, 2003 3:20 PM To: Multiple recipients of list ORACLE-L It looks like your process really is stuck in a way that has nothing to do with the Oracle code directly. You might look at the CPU usage of your session and its shadow using an O/S utility, but I suspect it would show zero CPU. Perhaps truss (or the AIX equivalent) might show your process spinning on whatever call equates to 'has the async read completed yet'. You could try doing three processstate dumps with 5 second intervals to see if the processstate shows any changes which might give you a hint - but again I'd GUESS that you'll find nothing happening. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk Coming soon one-day tutorials: Cost Based Optimisation Trouble-shooting and Tuning Indexing Strategies (see http://www.jlcomp.demon.co.uk/tutorial.html ) UK___March 19th USA_(FL)_May 2nd Next Seminar dates: (see http://www.jlcomp.demon.co.uk/seminar.html ) USA_(CA, TX)_August The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html -Original Message- To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: 05 February 2003 21:05 Still sitting there, while we try to figure out exactly why it's waiting. SID Username EVENT WAIT_TIME STATE SECONDS_IN_WAIT - -- -- --- --- 1 pmon timer 0 WAITING 79579 5 smon timer 0 WAITING 80 12 slave wait 0 WAITING 199 13 slave wait 0 WAITING 199 14 slave wait 0 WAITING 262 15 slave wait 0 WAITING 199 28 NIK db file scattered read 0 WAITING 20119 -Original Message- Sent: Wednesday, February 05, 2003 2:58 PM To: [EMAIL PROTECTED] Cc: Thomas Jeff -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Deshpande, Kirti 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: Henry Poras 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: Hanging query puzzle
Henry, I got aiostat from IBM Support when I bugged them about how to monitor AIO. Not sure if aiostat is readily available as a download from their Web site or in/for the newer versions of AIX. It works similar to vmstat and iostat, showing number of pending AIO requests at the time of sampling. Following is an example from one of our Servers, showing 10 samples at 1 sec interval. Ideally, the count should be 0 at all times. If it stays high (relative term) consistently then one needs to increase the number of configured AIO servers, or check the I/O subsystem for other problems... By default, aiostat must be run as 'root'. HTH, - Kirti ibmRS50 [VS9XBP]# aiostat 1 10 AIO requestcount: 1 AIO requestcount: 0 AIO requestcount: 0 AIO requestcount: 1 AIO requestcount: 0 AIO requestcount: 2 AIO requestcount: 0 AIO requestcount: 3 AIO requestcount: 0 AIO requestcount: 0 -Original Message- Sent: Thursday, February 06, 2003 9:34 AM To: Multiple recipients of list ORACLE-L Kirti, I've been searching on the IBM site for aiostat without much luck. What kind of information does it give you? (if I'm going to convince my SA to call IBM about this I'll need a very good argument. I'm still working on getting the rights to use sar). Henry -Original Message- Kirti Sent: Wednesday, February 05, 2003 8:29 PM To: Multiple recipients of list ORACLE-L Jeff, If you see the SEQ# field in the v$session_wait view not incrementing, then something else, other than Oracle, is causing a hang up... On AIX, if you have AIO enabled, try running 'aiostat'. It will show if there are any pending AIO requests. The difficult part would be to relate those pending calls to your session. It is quite likely that a particular AIO server may show waits on I/O, if this hang up is IO related. You can try 'pstat' to find out AIO server processes and associated 'pid' to dig deeper. If you do not have 'aiostat', ring up IBM Support. It is a nice utility to have, and it is free. For those on AIX 5L, can you please check if 'aiostat' is available as a standard distribution? I was told by IBM Support that 'aiostat' was going to be available with AIX 5L. - Kirti -Original Message- Sent: Wednesday, February 05, 2003 3:20 PM To: Multiple recipients of list ORACLE-L It looks like your process really is stuck in a way that has nothing to do with the Oracle code directly. You might look at the CPU usage of your session and its shadow using an O/S utility, but I suspect it would show zero CPU. Perhaps truss (or the AIX equivalent) might show your process spinning on whatever call equates to 'has the async read completed yet'. You could try doing three processstate dumps with 5 second intervals to see if the processstate shows any changes which might give you a hint - but again I'd GUESS that you'll find nothing happening. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk Coming soon one-day tutorials: Cost Based Optimisation Trouble-shooting and Tuning Indexing Strategies (see http://www.jlcomp.demon.co.uk/tutorial.html ) UK___March 19th USA_(FL)_May 2nd Next Seminar dates: (see http://www.jlcomp.demon.co.uk/seminar.html ) USA_(CA, TX)_August The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Deshpande, Kirti 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: Hanging query puzzle
Kirti, Just took another look at our kproc processes. What is weird is that we have 26 of them, 9 are owned by oracle the rest are root (huh?). All of them started at system boot. Also, almost all of the CPU is used by the top four (current %CPU and total over the life of the process) with the rest of the CPU spread over another 12 (this includes all of the oracle kprocs). Looks like too many are being spawned at startup (I'll check the min number), but I didn't know oracle had its own. Looks like one for each dbw process plus one more. Does oracle io only happen through these? If so, why are 4 of the root processes much busier if almost all IO is from the databases? I'll keep digging and posting when I have time. Henry -Original Message- Kirti Sent: Thursday, February 06, 2003 12:40 PM To: Multiple recipients of list ORACLE-L Henry, I got aiostat from IBM Support when I bugged them about how to monitor AIO. Not sure if aiostat is readily available as a download from their Web site or in/for the newer versions of AIX. It works similar to vmstat and iostat, showing number of pending AIO requests at the time of sampling. Following is an example from one of our Servers, showing 10 samples at 1 sec interval. Ideally, the count should be 0 at all times. If it stays high (relative term) consistently then one needs to increase the number of configured AIO servers, or check the I/O subsystem for other problems... By default, aiostat must be run as 'root'. HTH, - Kirti ibmRS50 [VS9XBP]# aiostat 1 10 AIO requestcount: 1 AIO requestcount: 0 AIO requestcount: 0 AIO requestcount: 1 AIO requestcount: 0 AIO requestcount: 2 AIO requestcount: 0 AIO requestcount: 3 AIO requestcount: 0 AIO requestcount: 0 -Original Message- Sent: Thursday, February 06, 2003 9:34 AM To: Multiple recipients of list ORACLE-L Kirti, I've been searching on the IBM site for aiostat without much luck. What kind of information does it give you? (if I'm going to convince my SA to call IBM about this I'll need a very good argument. I'm still working on getting the rights to use sar). Henry -Original Message- Kirti Sent: Wednesday, February 05, 2003 8:29 PM To: Multiple recipients of list ORACLE-L Jeff, If you see the SEQ# field in the v$session_wait view not incrementing, then something else, other than Oracle, is causing a hang up... On AIX, if you have AIO enabled, try running 'aiostat'. It will show if there are any pending AIO requests. The difficult part would be to relate those pending calls to your session. It is quite likely that a particular AIO server may show waits on I/O, if this hang up is IO related. You can try 'pstat' to find out AIO server processes and associated 'pid' to dig deeper. If you do not have 'aiostat', ring up IBM Support. It is a nice utility to have, and it is free. For those on AIX 5L, can you please check if 'aiostat' is available as a standard distribution? I was told by IBM Support that 'aiostat' was going to be available with AIX 5L. - Kirti -Original Message- Sent: Wednesday, February 05, 2003 3:20 PM To: Multiple recipients of list ORACLE-L It looks like your process really is stuck in a way that has nothing to do with the Oracle code directly. You might look at the CPU usage of your session and its shadow using an O/S utility, but I suspect it would show zero CPU. Perhaps truss (or the AIX equivalent) might show your process spinning on whatever call equates to 'has the async read completed yet'. You could try doing three processstate dumps with 5 second intervals to see if the processstate shows any changes which might give you a hint - but again I'd GUESS that you'll find nothing happening. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk Coming soon one-day tutorials: Cost Based Optimisation Trouble-shooting and Tuning Indexing Strategies (see http://www.jlcomp.demon.co.uk/tutorial.html ) UK___March 19th USA_(FL)_May 2nd Next Seminar dates: (see http://www.jlcomp.demon.co.uk/seminar.html ) USA_(CA, TX)_August The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Deshpande, Kirti 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: Henry Poras INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051
Hanging query puzzle
Title: Hanging query puzzle We have a query from a 3rd-party tool that seems to either run very quick or crawls to a complete stop. We can find no patterns to this behavior. The hang can be experienced even when there are no other processes active in the database. Checking waits, we see a db file scattered read. SID EVENT P1TEXT P1 P2TEXT P2 P3TEXT P3 - -- -- -- -- -- -- 1 pmon timer duration 300 0 0 12 slave wait msg ptr 5.0440E+17 0 0 13 slave wait msg ptr 5.0440E+17 0 0 14 slave wait msg ptr 5.0440E+17 0 0 15 slave wait msg ptr 5.0440E+17 0 0 28 db file scattered read file# 12 block# 21047 blocks 2 5 smon timer sleep time 300 failed 0 0 Then going to v$sess_io, we see the process is comletely stuck, no activity going on at all, and it's the only active process in the database. SID BLOCK_GETS CONSISTENT_GETS PHYSICAL_READS BLOCK_CHANGES CONSISTENT_CHANGES - -- --- -- - -- 28 6233582 60812023 36589516 4076353 115 The query looks like this: SELECT PRAssignment.*, SRM_RESOURCES.RESOURCE_TYPE FROM PRAssignment, SRM_RESOURCES WHERE prModTime TIMESTAMP '2003-02-05 09:23:56.0' AND PRAssignment.prResourceID=SRM_RESOURCES.ID If I check the file/block values for the wait I get the prassignment table. Prassignment has 5K rows while srm_resources has 300 rows. Prassignment also has a LONG RAW column, consequently we see a high chain count, with the result that it's taking up 135 extents to cover those 5K rows. I'm at a loss to explain why we see such inconsistent results with this query. Thoughts? Thanks. Jeffery D Thomas DBA Thomson Information Services Thomson, Inc. Email: [EMAIL PROTECTED] Indy DBA Master Documentation available at: http://gkmqp.tce.com/tis_dba Select 'Indy DBA' then 'DBA Web Pages'
Re: Hanging query puzzle
What version are you running - I have a similar problem on 9.2.0.2 on Solaris 9 that I've had an open tar on since November - Support has finally called up and said other people are having the same kind of problem - especially in regards to parallel processes. I am supposedly getting a test scenario from them to check out. I'll post the results if and when I get any. [EMAIL PROTECTED] 02/05/03 11:59AM We have a query from a 3rd-party tool that seems to either run very quick or crawls to a complete stop. We can find no patterns to this behavior. The hang can be experienced even when there are no other processes active in the database. Checking waits, we see a db file scattered read. SID EVENT P1TEXT P1 P2TEXT P2 P3TEXT P3 - -- -- -- -- -- -- 1 pmon timer duration 300 0 0 12 slave wait msg ptr 5.0440E+17 0 0 13 slave wait msg ptr 5.0440E+17 0 0 14 slave wait msg ptr 5.0440E+17 0 0 15 slave wait msg ptr 5.0440E+17 0 0 28 db file scattered read file# 12 block# 21047 blocks 2 5 smon timer sleep time 300 failed 0 0 Then going to v$sess_io, we see the process is comletely stuck, no activity going on at all, and it's the only active process in the database. SID BLOCK_GETS CONSISTENT_GETS PHYSICAL_READS BLOCK_CHANGES CONSISTENT_CHANGES - -- --- -- - -- 28 6233582 60812023 36589516 4076353 115 The query looks like this: SELECT PRAssignment.*, SRM_RESOURCES.RESOURCE_TYPE FROM PRAssignment, SRM_RESOURCES WHERE prModTime TIMESTAMP '2003-02-05 09:23:56.0' AND PRAssignment.prResourceID=SRM_RESOURCES.ID If I check the file/block values for the wait I get the prassignment table. Prassignment has 5K rows while srm_resources has 300 rows. Prassignment also has a LONG RAW column, consequently we see a high chain count, with the result that it's taking up 135 extents to cover those 5K rows. I'm at a loss to explain why we see such inconsistent results with this query. Thoughts? Thanks. Jeffery D Thomas DBA Thomson Information Services Thomson, Inc. Email: [EMAIL PROTECTED] Indy DBA Master Documentation available at: http://gkmqp.tce.com/tis_dba Select 'Indy DBA' then 'DBA Web Pages'
RE: Hanging query puzzle
Sorry, running 8.1.7.2 on AIX 4.3.3. -Original Message-From: John Shaw [mailto:[EMAIL PROTECTED]]Sent: Wednesday, February 05, 2003 2:09 PMTo: Multiple recipients of list ORACLE-LSubject: Re: Hanging query puzzleWhat version are you running - I have a similar problem on 9.2.0.2 on Solaris 9 that I've had an open tar on since November - Support has finally called up and said other people are having the same kind of problem - especially in regards to parallel processes. I am supposedly getting a test scenario from them to check out. I'll post the results if and when I get any. [EMAIL PROTECTED] 02/05/03 11:59AM We have a query from a 3rd-party tool that seems to either run very quick or crawls to a complete stop. We can find no patterns to this behavior. The hang can be experienced even when there are no other processes active in the database. Checking waits, we see a db file scattered read. SID EVENT P1TEXT P1 P2TEXT P2 P3TEXT P3 - -- -- -- -- -- -- 1 pmon timer duration 300 0 0 12 slave wait msg ptr 5.0440E+17 0 0 13 slave wait msg ptr 5.0440E+17 0 0 14 slave wait msg ptr 5.0440E+17 0 0 15 slave wait msg ptr 5.0440E+17 0 0 28 db file scattered read file# 12 block# 21047 blocks 2 5 smon timer sleep time 300 failed 0 0 Then going to v$sess_io, we see the process is comletely stuck, no activity going on at all, and it's the only active process in the database. SID BLOCK_GETS CONSISTENT_GETS PHYSICAL_READS BLOCK_CHANGES CONSISTENT_CHANGES - -- --- -- - -- 28 6233582 60812023 36589516 4076353 115 The query looks like this: SELECT PRAssignment.*, SRM_RESOURCES.RESOURCE_TYPE FROM PRAssignment, SRM_RESOURCES WHERE prModTime TIMESTAMP '2003-02-05 09:23:56.0' AND PRAssignment.prResourceID=SRM_RESOURCES.ID If I check the file/block values for the wait I get the prassignment table. Prassignment has 5K rows while srm_resources has 300 rows. Prassignment also has a LONG RAW column, consequently we see a high chain count, with the result that it's taking up 135 extents to cover those 5K rows. I'm at a loss to explain why we see such inconsistent results with this query. Thoughts? Thanks. Jeffery D Thomas DBA Thomson Information Services Thomson, Inc. Email: [EMAIL PROTECTED] Indy DBA Master Documentation available at: http://gkmqp.tce.com/tis_dba Select 'Indy DBA' then 'DBA Web Pages'
Re: Hanging query puzzle
What is the value for WAIT_TIME ? This may not be an IO problem if 'WAIT_TIME' is not 0. A session is waiting only when 'WAIT_TIME' is 0. I would suggest running some utility like tusc (HP), truss (Sun), strace (Linux) and check it from the OS side. Since this is a third party tool, it could be performing some CPU only operation which is not reflected in v$session_wait. Regards, Denny Quoting Thomas Jeff [EMAIL PROTECTED]: We have a query from a 3rd-party tool that seems to either run very quick or crawls to a complete stop. We can find no patterns to this behavior.The hang can be experienced even when there are no other processes active in the database. Checking waits, we see a db file scattered read. SID EVENTP1TEXT P1 P2TEXT P2 P3TEXT P3 - -- -- -- -- -- -- 1 pmon timer duration 300 0 0 12 slave wait msg ptr5.0440E+17 0 0 13 slave wait msg ptr5.0440E+17 0 0 14 slave wait msg ptr5.0440E+17 0 0 15 slave wait msg ptr5.0440E+17 0 0 28 db file scattered read file# 12 block# 21047 blocks 2 5 smon timer sleep time300 failed 0 0 Then going to v$sess_io, we see the process is comletely stuck, no activity going on at all, and it's the only active process in the database. SID BLOCK_GETS CONSISTENT_GETS PHYSICAL_READS BLOCK_CHANGES CONSISTENT_CHANGES - -- --- -- - -- 28623358260812023 36589516 4076353 115 The query looks like this: SELECT PRAssignment.*, SRM_RESOURCES.RESOURCE_TYPE FROM PRAssignment, SRM_RESOURCES WHERE prModTime TIMESTAMP '2003-02-05 09:23:56.0' AND PRAssignment.prResourceID=SRM_RESOURCES.ID If I check the file/block values for the wait I get the prassignment table. Prassignment has 5K rows while srm_resources has 300 rows. Prassignment also has a LONG RAW column, consequently we see a high chain count, with the result that it's taking up 135 extents to cover those 5K rows. I'm at a loss to explain why we see such inconsistent results with this query.Thoughts? Thanks. Jeffery D Thomas DBA Thomson Information Services Thomson, Inc. Email: [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Denny Koovakattu 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: Hanging query puzzle
Title: RE: Hanging query puzzle Still sitting there, while we try to figure out exactly why it's waiting. SID Username EVENT WAIT_TIME STATE SECONDS_IN_WAIT - -- -- --- --- 1 pmon timer 0 WAITING 79579 5 smon timer 0 WAITING 80 12 slave wait 0 WAITING 199 13 slave wait 0 WAITING 199 14 slave wait 0 WAITING 262 15 slave wait 0 WAITING 199 28 NIK db file scattered read 0 WAITING 20119 -Original Message- From: Denny Koovakattu [mailto:[EMAIL PROTECTED]] Sent: Wednesday, February 05, 2003 2:58 PM To: [EMAIL PROTECTED] Cc: Thomas Jeff Subject: Re: Hanging query puzzle What is the value for WAIT_TIME ? This may not be an IO problem if 'WAIT_TIME' is not 0. A session is waiting only when 'WAIT_TIME' is 0. I would suggest running some utility like tusc (HP), truss (Sun), strace (Linux) and check it from the OS side. Since this is a third party tool, it could be performing some CPU only operation which is not reflected in v$session_wait. Regards, Denny Quoting Thomas Jeff [EMAIL PROTECTED]: We have a query from a 3rd-party tool that seems to either run very quick or crawls to a complete stop. We can find no patterns to this behavior. The hang can be experienced even when there are no other processes active in the database. Checking waits, we see a db file scattered read. SID EVENT P1TEXT P1 P2TEXT P2 P3TEXT P3 - -- -- -- -- -- -- 1 pmon timer duration 300 0 0 12 slave wait msg ptr 5.0440E+17 0 0 13 slave wait msg ptr 5.0440E+17 0 0 14 slave wait msg ptr 5.0440E+17 0 0 15 slave wait msg ptr 5.0440E+17 0 0 28 db file scattered read file# 12 block# 21047 blocks 2 5 smon timer sleep time 300 failed 0 0 Then going to v$sess_io, we see the process is comletely stuck, no activity going on at all, and it's the only active process in the database. SID BLOCK_GETS CONSISTENT_GETS PHYSICAL_READS BLOCK_CHANGES CONSISTENT_CHANGES - -- --- -- - -- 28 6233582 60812023 36589516 4076353 115 The query looks like this: SELECT PRAssignment.*, SRM_RESOURCES.RESOURCE_TYPE FROM PRAssignment, SRM_RESOURCES WHERE prModTime TIMESTAMP '2003-02-05 09:23:56.0' AND PRAssignment.prResourceID=SRM_RESOURCES.ID If I check the file/block values for the wait I get the prassignment table. Prassignment has 5K rows while srm_resources has 300 rows. Prassignment also has a LONG RAW column, consequently we see a high chain count, with the result that it's taking up 135 extents to cover those 5K rows. I'm at a loss to explain why we see such inconsistent results with this query. Thoughts? Thanks. Jeffery D Thomas DBA Thomson Information Services Thomson, Inc. Email: [EMAIL PROTECTED]
RE: Hanging query puzzle
Did you try tracing it from the OS ? Any error messages in the OS system log ? Is it always one datafile/mount point ? A while back, I had seen a similar problem when an array had gone bad. You may want to try using dd to read and write some files and check the timing. Regards, Denny Quoting Thomas Jeff [EMAIL PROTECTED]: Still sitting there, while we try to figure out exactly why it's waiting. SID Username EVENT WAIT_TIME STATE SECONDS_IN_WAIT - -- -- --- --- 1 pmon timer 0 WAITING 79579 5 smon timer 0 WAITING 80 12 slave wait 0 WAITING 199 13 slave wait 0 WAITING 199 14 slave wait 0 WAITING 262 15 slave wait 0 WAITING 199 28 NIK db file scattered read 0 WAITING 20119 -Original Message- From: Denny Koovakattu [mailto:[EMAIL PROTECTED]] Sent: Wednesday, February 05, 2003 2:58 PM To: [EMAIL PROTECTED] Cc: Thomas Jeff Subject: Re: Hanging query puzzle What is the value for WAIT_TIME ? This may not be an IO problem if 'WAIT_TIME' is not 0. A session is waiting only when 'WAIT_TIME' is 0. I would suggest running some utility like tusc (HP), truss (Sun), strace (Linux) and check it from the OS side. Since this is a third party tool, it could be performing some CPU only operation which is not reflected in v$session_wait. Regards, Denny Quoting Thomas Jeff [EMAIL PROTECTED]: We have a query from a 3rd-party tool that seems to either run very quick or crawls to a complete stop. We can find no patterns to this behavior.The hang can be experienced even when there are no other processes active in the database. Checking waits, we see a db file scattered read. SID EVENTP1TEXT P1 P2TEXT P2 P3TEXT P3 - -- -- -- -- -- -- 1 pmon timer duration 300 0 0 12 slave wait msg ptr5.0440E+17 0 0 13 slave wait msg ptr5.0440E+17 0 0 14 slave wait msg ptr5.0440E+17 0 0 15 slave wait msg ptr5.0440E+17 0 0 28 db file scattered read file# 12 block# 21047 blocks 2 5 smon timer sleep time300 failed 0 0 Then going to v$sess_io, we see the process is comletely stuck, no activity going on at all, and it's the only active process in the database. SID BLOCK_GETS CONSISTENT_GETS PHYSICAL_READS BLOCK_CHANGES CONSISTENT_CHANGES - -- --- -- - -- 28623358260812023 36589516 4076353 115 The query looks like this: SELECT PRAssignment.*, SRM_RESOURCES.RESOURCE_TYPE FROM PRAssignment, SRM_RESOURCES WHERE prModTime TIMESTAMP '2003-02-05 09:23:56.0' AND PRAssignment.prResourceID=SRM_RESOURCES.ID If I check the file/block values for the wait I get the prassignment table. Prassignment has 5K rows while srm_resources has 300 rows. Prassignment also has a LONG RAW column, consequently we see a high chain count, with the result that it's taking up 135 extents to cover those 5K rows. I'm at a loss to explain why we see such inconsistent results with this query.Thoughts? Thanks. Jeffery D Thomas DBA Thomson Information Services Thomson, Inc. Email: [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Denny Koovakattu 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: Hanging query puzzle
Just a warning for the future - this is no longer true on all versions of Oracle 9 because the wait_time column is a copy of the underlying x$ timing column rounded from microseconds to hundredths. Hence the wait_time can show a zero when the actual time is non-zero. You should depend only on the STATE column which is a decode of the basic microsecond value, and shows 'WAITING' if and only if the microsecond time is zero. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk Coming soon one-day tutorials: Cost Based Optimisation Trouble-shooting and Tuning Indexing Strategies (see http://www.jlcomp.demon.co.uk/tutorial.html ) UK___March 19th USA_(FL)_May 2nd Next Seminar dates: (see http://www.jlcomp.demon.co.uk/seminar.html ) USA_(CA, TX)_August The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html -Original Message- To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: 05 February 2003 20:50 What is the value for WAIT_TIME ? This may not be an IO problem if 'WAIT_TIME' is not 0. A session is waiting only when 'WAIT_TIME' is 0. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis 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: Hanging query puzzle
It looks like your process really is stuck in a way that has nothing to do with the Oracle code directly. You might look at the CPU usage of your session and its shadow using an O/S utility, but I suspect it would show zero CPU. Perhaps truss (or the AIX equivalent) might show your process spinning on whatever call equates to 'has the async read completed yet'. You could try doing three processstate dumps with 5 second intervals to see if the processstate shows any changes which might give you a hint - but again I'd GUESS that you'll find nothing happening. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk Coming soon one-day tutorials: Cost Based Optimisation Trouble-shooting and Tuning Indexing Strategies (see http://www.jlcomp.demon.co.uk/tutorial.html ) UK___March 19th USA_(FL)_May 2nd Next Seminar dates: (see http://www.jlcomp.demon.co.uk/seminar.html ) USA_(CA, TX)_August The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html -Original Message- To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: 05 February 2003 21:05 Still sitting there, while we try to figure out exactly why it's waiting. SID Username EVENT WAIT_TIME STATE SECONDS_IN_WAIT - -- -- --- --- 1 pmon timer 0 WAITING 79579 5 smon timer 0 WAITING 80 12 slave wait 0 WAITING 199 13 slave wait 0 WAITING 199 14 slave wait 0 WAITING 262 15 slave wait 0 WAITING 199 28 NIK db file scattered read 0 WAITING 20119 -Original Message- Sent: Wednesday, February 05, 2003 2:58 PM To: [EMAIL PROTECTED] Cc: Thomas Jeff -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis 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: Hanging query puzzle
Thanks. Didn't know that. See you in Dallas next Sunday ;) Regards, Denny Quoting Jonathan Lewis [EMAIL PROTECTED]: Just a warning for the future - this is no longer true on all versions of Oracle 9 because the wait_time column is a copy of the underlying x$ timing column rounded from microseconds to hundredths. Hence the wait_time can show a zero when the actual time is non-zero. You should depend only on the STATE column which is a decode of the basic microsecond value, and shows 'WAITING' if and only if the microsecond time is zero. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk Coming soon one-day tutorials: Cost Based Optimisation Trouble-shooting and Tuning Indexing Strategies (see http://www.jlcomp.demon.co.uk/tutorial.html ) UK___March 19th USA_(FL)_May 2nd Next Seminar dates: (see http://www.jlcomp.demon.co.uk/seminar.html ) USA_(CA, TX)_August The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html -Original Message- To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: 05 February 2003 20:50 What is the value for WAIT_TIME ? This may not be an IO problem if 'WAIT_TIME' is not 0. A session is waiting only when 'WAIT_TIME' is 0. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis INET: [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Denny Koovakattu 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: Hanging query puzzle
Title: RE: Hanging query puzzle Jonathan, Charlie, Denny: Thanks for the replies. Our SA's claim that there's nothing wrong with the EMC SAN/disk. Finally, made them give me errpt access (they had shut off execute privs from all but them) and got a lot of output like below. The SAs are saying that this is only a sporadic 'repathing' error, as we have 4 channels to the SAN, and does not affect the data in anyway, except for possibly some 'speed'. Now, 'sporadic' exactly describes the problem. I tracked down the dates on this errpt and they do correlate to the dates when we encountered hang problems in the database. What exactly would they mean by 'repathing'? Possibly related to Jonathan's hint on spinning on 'has the async read completed yet'? [indysdb02][/home/tmm_dbas/u26905]errpt IDENTIFIER TIMESTAMP T C RESOURCE_NAME DESCRIPTION 79B0DF89 0205091603 P H hdisk7 DISK OPERATION ERROR 79B0DF89 0205091603 P H hdisk8 DISK OPERATION ERROR 79B0DF89 0205091603 P H hdisk13 DISK OPERATION ERROR 79B0DF89 0205091603 P H hdisk13 DISK OPERATION ERROR 79B0DF89 0205091603 P H hdisk12 DISK OPERATION ERROR 79B0DF89 0205091603 P H hdisk11 DISK OPERATION ERROR 79B0DF89 0205091603 P H hdisk9 DISK OPERATION ERROR 79B0DF89 0205091603 P H hdisk5 DISK OPERATION ERROR 79B0DF89 0205091603 P H hdisk3 DISK OPERATION ERROR 79B0DF89 0205091603 P H hdisk6 DISK OPERATION ERROR -Original Message- From: Jonathan Lewis [mailto:[EMAIL PROTECTED]] Sent: Wednesday, February 05, 2003 4:20 PM To: Multiple recipients of list ORACLE-L Subject: Re: Hanging query puzzle It looks like your process really is stuck in a way that has nothing to do with the Oracle code directly. You might look at the CPU usage of your session and its shadow using an O/S utility, but I suspect it would show zero CPU. Perhaps truss (or the AIX equivalent) might show your process spinning on whatever call equates to 'has the async read completed yet'. You could try doing three processstate dumps with 5 second intervals to see if the processstate shows any changes which might give you a hint - but again I'd GUESS that you'll find nothing happening. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk Coming soon one-day tutorials: Cost Based Optimisation Trouble-shooting and Tuning Indexing Strategies (see http://www.jlcomp.demon.co.uk/tutorial.html ) UK___March 19th USA_(FL)_May 2nd Next Seminar dates: (see http://www.jlcomp.demon.co.uk/seminar.html ) USA_(CA, TX)_August The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html -Original Message- To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: 05 February 2003 21:05 Still sitting there, while we try to figure out exactly why it's waiting. SID Username EVENT WAIT_TIME STATE SECONDS_IN_WAIT - -- -- --- --- 1 pmon timer 0 WAITING 79579 5 smon timer 0 WAITING 80 12 slave wait 0 WAITING 199 13 slave wait 0 WAITING 199 14 slave wait 0 WAITING 262 15 slave wait 0 WAITING 199 28 NIK db file scattered read 0 WAITING 20119 -Original Message- Sent: Wednesday, February 05, 2003 2:58 PM To: [EMAIL PROTECTED] Cc: Thomas Jeff -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis 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: Hanging query puzzle
Jeff, If you see the SEQ# field in the v$session_wait view not incrementing, then something else, other than Oracle, is causing a hang up... On AIX, if you have AIO enabled, try running 'aiostat'. It will show if there are any pending AIO requests. The difficult part would be to relate those pending calls to your session. It is quite likely that a particular AIO server may show waits on I/O, if this hang up is IO related. You can try 'pstat' to find out AIO server processes and associated 'pid' to dig deeper. If you do not have 'aiostat', ring up IBM Support. It is a nice utility to have, and it is free. For those on AIX 5L, can you please check if 'aiostat' is available as a standard distribution? I was told by IBM Support that 'aiostat' was going to be available with AIX 5L. - Kirti -Original Message- Sent: Wednesday, February 05, 2003 3:20 PM To: Multiple recipients of list ORACLE-L It looks like your process really is stuck in a way that has nothing to do with the Oracle code directly. You might look at the CPU usage of your session and its shadow using an O/S utility, but I suspect it would show zero CPU. Perhaps truss (or the AIX equivalent) might show your process spinning on whatever call equates to 'has the async read completed yet'. You could try doing three processstate dumps with 5 second intervals to see if the processstate shows any changes which might give you a hint - but again I'd GUESS that you'll find nothing happening. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk Coming soon one-day tutorials: Cost Based Optimisation Trouble-shooting and Tuning Indexing Strategies (see http://www.jlcomp.demon.co.uk/tutorial.html ) UK___March 19th USA_(FL)_May 2nd Next Seminar dates: (see http://www.jlcomp.demon.co.uk/seminar.html ) USA_(CA, TX)_August The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html -Original Message- To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: 05 February 2003 21:05 Still sitting there, while we try to figure out exactly why it's waiting. SID Username EVENT WAIT_TIME STATE SECONDS_IN_WAIT - -- -- --- --- 1 pmon timer 0 WAITING 79579 5 smon timer 0 WAITING 80 12 slave wait 0 WAITING 199 13 slave wait 0 WAITING 199 14 slave wait 0 WAITING 262 15 slave wait 0 WAITING 199 28 NIK db file scattered read 0 WAITING 20119 -Original Message- Sent: Wednesday, February 05, 2003 2:58 PM To: [EMAIL PROTECTED] Cc: Thomas Jeff -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Deshpande, Kirti 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).
