HI all,

I have a problem with a statement that normally execute in few second but after 50h the session is still active and is still waiting for null event.

V$session_wait

EVENT WAIT_TIME SECONDS_IN_WAIT STATE
---------------------------------------------------------------- ---------- --------------- -------------------
null event -1 178737 WAITED KNOWN TIME


V$session

SID STATUS PROGRAM LAST_CALL_ET
---------- -------- ------------------------------------------------ ------------
364 ACTIVE f90runm@CENTRAL1 (TNS V1-V3) 178737


My Oracle version : Oracle9i Enterprise Edition Release 9.2.0.2.0 - 64bit Production
System version : HP-UX V1

I tried tracing the session but no trace file is generated it same there is no activity.

Thanks for your help.

Hi Psycho,


Very first thing, run two AWR or STATSPACK snapshots, no more than one minute apart, create an elapsed-time report and post the results:

http://www.dba-oracle.com/t_taking_awr_sta...ck_snapshot.htm

Then paste the report into www.statspackanalyzer.com

****************************************
>> it same there is no activity.

Good clue!

That means that Oracle is waiting on some shared resource:

- a data block (lock, latch)
- a disk
- allocation of RAM

To see, read this:

http://www.dba-oracle.com/t_troubleshootin...ng_database.htm

************************************
>> Long waiting session

Aha! Oracle make a v$longops view:

http://www.dba-oracle.com/longops.htm

Let me know what you see!

Hi,

Thanks for your quick response.

The "v$session_longops" show no activity for the concerned session.

The v$session_wait and "oradebug hanganalyze 3" indiquate that the session is not waiting "STATE = WAITED KNOWN TIME "

command = 0 in v$session indiquate that the session is doing nothing.


So the session is waiting for nothing,is doing nothing and is still active haw could it be.


Any suggestion.

Hi,

>> Any suggestion.

Yeah, follow my instructions!!!



I guarantee that report will detect your waiting issue.

Also see here, on tracing session waits:

http://www.dba-oracle.com/art_dbazine_waits.htm

If you get the download of scripts, you can also solve this in minutes:

http://www.dba-oracle.com/oracle_scripts.htm

Hi Mr. burleson,

I have already run the statspack elapsed time report but it does not give me helpfull information about what these particular session was doing.


Session Id: 801 Serial#: 14356
-> ordered by wait time desc, waits desc (idle events last)

Avg
Total Wait wait Waits
Event Waits Timeouts Time (s) (ms) /txn
---------------------------- ------------ ---------- ---------- ------ --------
log file sync 2 0 0 1 0.0
SQL*Net message from client 134 0 316 2361 0.1
SQL*Net message to client 134 0 0 0 0.1
-------------------------------------------------------------


I think that Statspack report will capture information from "v$session_wait" or these view show that it's waiting for nothing.


PS: I can't put my statspack report in these forum because my boss won't let me do that sorry.

Thanks for your help i aperciate.

Hi,

>> I have already run the statspack elapsed time report but it does not give me helpfull information about what these particular session was doing.

The report was for statspack analyzer . . . .

****************************************
>> I can't put my statspack report in these forum because my boss won't let me do that sorry.

Smart move.

Did statspackanalyzer.com find anything? The report never leaves your desktop . . .

You need to start by ruling-out what the problem is not:

0 - Look for high I/O times for any data files

1 - In the statspack report, does the SQL appear in one of the top-10 sections?

2 - Get the PID from v$session and see what the process is doing on the server.