Re: Can't find the sql text from v$sql via the hash value

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


On 6/19/06, Jurijs Velikanovs <j.velikanovs@xxxxxxxxx> wrote: 

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
http://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
--
http://www.freelists.org/webpage/oracle-l





--
Eagle Fan

Oracle DBA

Other related posts: