Example of orastat usage to find problematic SQL on Oracle server

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.