In Short:
orastat -ser | egrep -v INACT\|BG
# grab PID number of process , for example 9
orastat -sal 9
Here is logs of steps:
[oracle@server ~]$ orastat -ser | egrep -v INACT\|BG 2013/11/07-17:56:50 orastat | oraver=11.2. oraver_short=11.2 oraver_num=11.2 sqlcmd=sp arg=-ser arg2= 2013/11/07-17:56:50 orastat | ORACLE_SID=MYSID ORACLE_HOME=/opt/oracle/product/11.2.0.3 - SunOS hana 5.10 Generic_147441-12 i86pc i386 i86pc 2013/11/07-17:56:50 orastat | Version=3.53 Host=hana Company=some-company 2013/11/07-17:56:50 orastat | -ser: Session Details Session Ses Ses Wait Wait DB OS Session Last Last +-UserCommits-+ Rollback Program SID-Ser# Type Status Class / Seq Userid-Userid-PID-SPID-Term@Server Start Time Call ET SQL SQL_ID Count /Sec Count Identifier ------------ ------ -------- ------------------ -------------------------------------- ------------------- --------- ------ ------------- -------- ------ -------- ------------------------ 9,33801 USER ACTIVE User_I/O/51809 SCHEMA2-sb-1234-7525-unknown@other-server.rev 2013/11/07-17:10:05 8_Sec SELECT 1wrbrdf8qrrx8 0 ? 0 JDBC_Thin_Client 482,56061 USER* ACTIVE Network/13 SYS-oracle-14955-14956-?@localhost 2013/11/07-17:56:50 0_Sec SELECT 9d688j2s309du 0 ? 0 sqlplus@localhost_(TNS_V1-V3) 2013/11/07-17:56:50 orastat | Count of USER session(s) in ACTIVE status: 2 2013/11/07-17:56:50 orastat | There are 50 total sessions (mysid=482 - denoted by *)
[oracle@server ~]$ orastat -sal 9
2013/11/07-17:57:23 orastat | oraver=11.2. oraver_short=11.2 oraver_num=11.2 sqlcmd=sp arg=-sal arg2=9
2013/11/07-17:57:23 orastat | ORACLE_SID=MYSID ORACLE_HOME=/opt/oracle/product/11.2.0.3 - SunOS hana 5.10 Generic_147441-12 i86pc i386 i86pc
2013/11/07-17:57:23 orastat | Version=3.53 Host=hana Company=some-company
Session SQL:
--------------------------------------------------------------------------------
SELECT
cc.company_id .....
WHERE
cc.start$ <= sysdate and sysdate < cc.end$
--------------------------------------------------------------------------------
Session Current Wait State:
SID DB-User WaitType St WTSeq Tmr WTTimSec Wait Event Details
------ ---------------- -------- -- ------ --- -------- -------------------------------------
9 SCHEMA2 RealWait A 51976 -1 1 P1Raw=0000000000000021 P2Raw=00000000000233F5 P3Raw=0000000000000001 db file sequential read WAITED SHORT TIME file# 33 block# 144373 blocks 1
Session Stats:
------------------------------------------Session------------------------------------------
SID-Ser# OS:Userid-PID-Server DB:User Program Hours CPUTm Commit PFBlks TScans MemSorts DiskSorts Con+BuGet PhysReads RHRat LokWaitTm
--------- ------------------------ ---------- ------------ ------ ------ ------ ------ ------ --------- --------- --------- --------- ------ ---------
9,33801 sb-1234-other-server SCHEMA2 JDBCThinClie 0.8 277617 0 0 41609 175 0 118634411 12000 100.0 0
2013/11/07-17:57:25 orastat | orastat | -sal Done
[oracle@server ~]$ orastat -wr
2013/11/07-17:57:48 orastat | oraver=11.2. oraver_short=11.2 oraver_num=11.2 sqlcmd=sp arg=-wr arg2=
2013/11/07-17:57:48 orastat | ORACLE_SID=MYSID ORACLE_HOME=/opt/oracle/product/11.2.0.3 - SunOS hana 5.10 Generic_147441-12 i86pc i386 i86pc
2013/11/07-17:57:48 orastat | Version=3.53 Host=hana Company=some-company
2013/11/07-17:57:48 orastat | -wr: Wait Reasons - By User and Event Type (Idle Waits Excluded)
DB User Wait Event Type Sessions Waiting
---------- ---------------------------------------- ----------------
SCHEMA2 db file sequential read 1
2013/11/07-17:57:48 orastat | Total Session(s) Waiting: 1
[oracle@server ~]$ orastat -wr
2013/11/07-17:57:56 orastat | oraver=11.2. oraver_short=11.2 oraver_num=11.2 sqlcmd=sp arg=-wr arg2=
2013/11/07-17:57:56 orastat | ORACLE_SID=MYSID ORACLE_HOME=/opt/oracle/product/11.2.0.3 - SunOS hana 5.10 Generic_147441-12 i86pc i386 i86pc
2013/11/07-17:57:56 orastat | Version=3.53 Host=hana Company=some-company
2013/11/07-17:57:56 orastat | -wr: Wait Reasons - By User and Event Type (Idle Waits Excluded)
DB User Wait Event Type Sessions Waiting
---------- ---------------------------------------- ----------------
SCHEMA2 db file sequential read 1
So problem is found, inadequate sequential read that was caused by problems with missed oracle statistics recalculation.
