hi Yury:
It's waiting on direct path read(lob). The database issue was related to application problem.
Just curious about why the lob access sql appeared in v$session.sql_hash_value.
This is the info I collected at that time.
The P1, P2 for direct path read is file#, block#. It belongs to the table's lob segment.
EVENT P1 P2 P3 W WT SQL_HASH_VALUE ------------------------------ ---------- ---------- ---- ---- ---- -------------- SQL*Net message from client 1952673792 1 0 0 6 0 SQL*Net message from client 1650815232 1 0 0 0 201505526
direct path read (lob) 50 221516 4 0 0 750036270 direct path read (lob) 49 124173 3 0 0 750036270 SQL*Net more data to client 1952673792 2001 0 -1 0 750036270 SQL*Net message from client 1952673792 1 0 0 0 750036270 SQL*Net message from client 1952673792 1 0 0 0 750036270 SQL*Net message from client 1952673792 1 0 0 0 750036270 SQL*Net message from client 1952673792 1 0 0 0 750036270
EVENT P1 P2 P3 W WT SQL_HASH_VALUE ------------------------------ ---------- ---------- ---- ---- ---- -------------- SQL*Net message from client 1952673792 1 0 0 0 750036270 SQL*Net message from client 1952673792 1 0 0 0 750036270 SQL*Net more data to client 1952673792 2001 0 0 0 1347728404 SQL*Net message from client 1650815232 1 0 -1 6 1857334316 SQL*Net message to client 1650815232 1 0 -1 0 2894211034 SQL*Net message from client 1952673792 1 0 0 0 3164742762 SQL*Net message from client 1952673792 1 0 1 0 3445726523
Eagle,
You wrote in you very fist post >> When database hang, I got the sql hash value from v$session_wait view. Just wonder what kind of EVENT you observed in a v$session_wait view at the time of hang? What were the parameters P1, P2, P3 ?
Yury //www.freelists.org/archives/ora-apps-dba/05-2006/msg00000.html
On 6/19/06, Laimutis Nedzinskas <Laimutis.Nedzinskas@xxxxxxxxxxxxx> wrote: > > > what about locks? > > > ________________________________ > From: oracle-l-bounce@xxxxxxxxxxxxx [mailto: oracle-l-bounce@xxxxxxxxxxxxx] > On Behalf Of eagle fan > Sent: 17. júní 2006 18:35 > To: oracle-l@xxxxxxxxxxxxx > Subject: Re: Can't find the sql text from v$sql via the hash value > > > > Thanks for your reply. > > > The hang just last several seconds, after several seconds, the application > client cancel the operation and mark the database as unavaliable. > > > Then we took the application clients back and connected to database again, > but after a while ,the same problem happened again. > > So I can't do snapshot when the database hung. We collected the statspack > every 15 minutes, but no useful info showed in it, because the hang just > last a very short period. > > I run a monitor script to collect some infomation such as machine name , > hash value , wait event from v$session_wait and v$session view tables. I got > the hash value from v$session, but I can't get the sql text. So I still > don't know what's the root cause. > > The hash value seems is realated to "table_4_2000_f4e_0_0_0" , but not > related to the original sql text. > > Why v$session view record this hash value? I think it should record the hash > value related to the original sql text. > > > -- > Eagle Fan > > Oracle DBA > > > Fyrirvari/Disclaimer > http://www.landsbanki.is/disclaimer
-- Yury +44 7738 013090 (GMT) ============================================ http://otn.oracle.com/ocm/jvelikanovs.html -- //www.freelists.org/webpage/oracle-l
-- Eagle Fan