Oracle-L: RE: Client hangs on some key values

From: Reardon, Bruce (CALBBAY) <Bruce.Reardon_at_comalco.riotinto.com.au>
Date: Thu, 28 Aug 2003 20:29:26 -0800
Message-ID: <F001.005CDD75.20030828202926@fatcity.com>

Have a look at the view dba_lock_internal (?\rdbms\admin\catblock.sql) - maybe it is hanging on a pin / parse - perhaps from a session someone killed when it took too long? Have a look at Metalink note 122567.1 about an update to that view that greatly improves performance under 8i.

A suitable query for dba_lock_internal was posted today by Diego Cutrone:

select to_char(SESSION_ID,'999') sid ,

   substr(LOCK_TYPE,1,30) Type, 
   substr(lock_id1,1,45) Object_Name, 
   substr(mode_held,1,4) HELD, 
   substr(mode_requested,1,4) REQ,

   lock_id2 lock_addr
FROM dba_lock_internal
WHERE
   mode_requested <> 'None'
   and mode_requested <> mode_held
;

We had a situation where Forms 4.5 generated a "download" query that was a page or too long The session would hang and never parse
Then the stats collection would fail on an internal lock Then all access to the table would be blocked

As for view to see if the query is received, not a view but you could try Net8 tracing - on the client side and / or at the listener. Have a look at Metalink note 16658.1 on Tracing Net8 + associated parameters.

You could also try tracing of the Oracle session - eg sql_trace or event 10046.

HTH,
Bruce Reardon

-----Original Message-----
Sent: Friday, 29 August 2003 2:14 PM

New DBA on the block and already ran into my first problem:

We have an Oracle Database (8.1.7.0) running on Clustered HP-UX.

Oracle client running on a HP-UX (11) B2000 machine.

The query looks like

select col1, col2, col3, 0, col4, col5, 0, 0, ................col100

from table 1
where col1 = '123'

This query works for most everytime except certain selected col1 values. col1 a primary key.

For the unfortuante col1 values, the client just hangs. I looked under v$session_wait, don't see anthying unusual. Only following events I have noticed: Message to/from SQL*CLient, more data to SQL*Client, file open The values for the wait were not any different from the sessions when the SQL got executed. Although the query never makes to V$SQL view.

No locks on the table. verified with V$lock views. No corrupt blocks: verified with analyze ...cascade command. TNSPING normal

It is interesting that these queries only fail from this one client. It works fine on the server or any other client.

Network guru says he doesn't see any problem with network either. Changed the machine but still didn't help. Memory, CPU looks good on the client and server.

Do you have any idea what might be wrong here? Is there a view I can refrence to show that the sever never recived the client request?

TIA
SB
This is going to be a great day!!!
--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: Reardon, Bruce (CALBBAY)
INET: Bruce.Reardon_at_comalco.riotinto.com.au

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: ListGuru_at_fatcity.com (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). Received on Thu Aug 28 2003 - 23:29:26 CDT