Oracle Cloud Infrastructure
Coming soon ...
Apps R12.2
  • R12.2 Architecture
  • Cloning Error (RC-50208)
  • Apps R12.1
  • Major changes from 11i
  • R12:HTTP Debug Logging
  • Compile Apps Schema invalid objects in R12
  • Apps 11i
  • Apps Components and Architecture
  • Concurrent managers
  • Patching
  • Using AD Patch
  • Using AD Control
  • FNDCPASS utility
  • Single-node Installation
  • Multi-node Installation
  • Find Apps Version
  • Cloning
  • Upgrade 11.5.9 to 11.5.10.2
  • Upgrade from 11.5.10.2 to R12
  • Upgrading 9i to 10g with 11i
  • 11i/R12 General Topics
  • AppsDBA System Management Utilities Guide
  • Identifying Long Idle Sessions
  • Identifying High Active Sessions
  • Change hostname for EBS
  • Oracle 12c Database
  • Oracle12c PSU Apply
  • Oracle12c Datafile moved Online
  • Oracle 11g Database
  • Upgrade 10g to 11g R1
  • Upgrade 11.2.0.2 to 11.2.0.3
  • Database 9i-10g
  • Top 99 Responsibilities of a DBA
  • General Info
  • Database Patching
  • 10g:ASM
  • 10g:Data Pump
  • 10g:Data Guard Installing
  • 10g:Rollback Monitoring
  • 10g:Flashback Table
  • Tablespace Management
  • Materialized Views
  • 10g:Enterprise Manager
  • 10g:Upgrade
  • Error:Ora-01631
  • DBA Scripts
  • Disk I/O,Events,Waits
  • Tablespace Information
  • Session Statistics
  • Hit/Miss Ratios
  • User Information
  • Rollback Segments
  • Full Table Scans
  • Contention/Locking
  • Redo Log Buffer
  • Data Dictionary Info
  • Oracle10g Application Server
  • Oracle10g Application Installation
  • (Re)Securing OAS Control
  • Oracle AS10g null protocol issue
  • Oracle Backup & Recovery
  • RMAN
  • RMAN Setup
  • Backup Recovery
  • Flash Recovery Area
  • Oracle10g Discoverer with Apps
    Coming soon ..............
    Troubleshooting
  • Discoverer Troubleshooting
  • Access EBS in mozile
  • Linux and Unix Platforms
  • How To configure NFS
  • Unix/Linux Command
  • Change hostname in Linux
  • SENDMAIL configuration
  • This Oracle Application DBA Portal is the biggest knowledge gateway for the people in the world of Oracle...
    Friday, June 19, 2009
    Session Statistics
    Session Statistics
    Here are some scripts related to Session Statistics .

    Session I/O By User
    SESSION I/O BY USER NOTES:


    Username - Name of the Oracle process user
    OS User - Name of the operating system user
    PID - Process ID of the session
    SID - Session ID of the session
    Serial# - Serial# of the session
    Physical Reads - Physical reads for the session
    Block Gets - Block gets for the session
    Consistent Gets - Consistent gets for the session
    Block Changes - Block changes for the session
    Consistent Changes - Consistent changes for the session

    select nvl(ses.USERNAME,'ORACLE PROC') username,
    OSUSER os_user,
    PROCESS pid,
    ses.SID sid,
    SERIAL#,
    PHYSICAL_READS,
    BLOCK_GETS,
    CONSISTENT_GETS,
    BLOCK_CHANGES,
    CONSISTENT_CHANGES
    from v$session ses,
    v$sess_io sio
    where ses.SID = sio.SID
    order by PHYSICAL_READS, ses.USERNAME

    CPU Usage By Session
    CPU USAGE BY SESSION NOTES:


    Username - Name of the user
    SID - Session id
    CPU Usage - CPU centiseconds used by this session (divide by 100 to get real CPU seconds)

    select nvl(ss.USERNAME,'ORACLE PROC') username,
    se.SID,
    VALUE cpu_usage
    from v$session ss,
    v$sesstat se,
    v$statname sn
    where se.STATISTIC# = sn.STATISTIC#
    and NAME like '%CPU used by this session%'
    and se.SID = ss.SID
    order by VALUE desc

    Resource Usage By User
    RESOURCE USAGE BY USER NOTES:


    SID - Session ID
    Username - Name of the user
    Statistic - Name of the statistic
    Value - Current value

    select ses.SID,
    nvl(ses.USERNAME,'ORACLE PROC') username,
    sn.NAME statistic,
    sest.VALUE
    from v$session ses,
    v$statname sn,
    v$sesstat sest
    where ses.SID = sest.SID
    and sn.STATISTIC# = sest.STATISTIC#
    and sest.VALUE is not null
    and sest.VALUE != 0
    order by ses.USERNAME, ses.SID, sn.NAME

    Session Stats By Session
    SESSION STAT NOTES:


    Username - Name of the user
    SID - Session ID
    Statistic - Name of the statistic
    Usage - Usage according to Oracle

    select nvl(ss.USERNAME,'ORACLE PROC') username,
    se.SID,
    sn.NAME stastic,
    VALUE usage
    from v$session ss,
    v$sesstat se,
    v$statname sn
    where se.STATISTIC# = sn.STATISTIC#
    and se.SID = ss.SID
    and se.VALUE > 0
    order by sn.NAME, se.SID, se.VALUE desc

    Cursor Usage By Session
    CURSOR USAGE BY SESSION NOTES:


    Username - Name of the user
    Recursive Calls - Total number of recursive calls
    Opened Cursors - Total number of opened cursors
    Current Cursors - Number of cursor currently in use

    select user_process username,
    "Recursive Calls",
    "Opened Cursors",
    "Current Cursors"
    from (
    select nvl(ss.USERNAME,'ORACLE PROC')||'('||se.sid||') ' user_process,
    sum(decode(NAME,'recursive calls',value)) "Recursive Calls",
    sum(decode(NAME,'opened cursors cumulative',value)) "Opened Cursors",
    sum(decode(NAME,'opened cursors current',value)) "Current Cursors"
    from v$session ss,
    v$sesstat se,
    v$statname sn
    where se.STATISTIC# = sn.STATISTIC#
    and (NAME like '%opened cursors current%'
    or NAME like '%recursive calls%'
    or NAME like '%opened cursors cumulative%')
    and se.SID = ss.SID
    and ss.USERNAME is not null
    group by nvl(ss.USERNAME,'ORACLE PROC')||'('||se.SID||') '
    )
    orasnap_user_cursors
    order by USER_PROCESS,"Recursive Calls"

    User Hit Ratios
    USER HIT RATIO NOTES:


    Username - Name of the user
    Consistent Gets - The number of accesses made to the block buffer to retrieve data in a consistent mode.
    DB Blk Gets - The number of blocks accessed via single block gets (i.e. not through the consistent get mechanism).
    Physical Reads - The cumulative number of blocks read from disk.

    Logical reads are the sum of consistent gets and db block gets.
    The db block gets statistic value is incremented when a block is read for update and when segment header blocks are accessed.
    Hit ratio should be > 90%

    select USERNAME,
    CONSISTENT_GETS,
    BLOCK_GETS,
    PHYSICAL_READS,
    ((CONSISTENT_GETS+BLOCK_GETS-PHYSICAL_READS) / (CONSISTENT_GETS+BLOCK_GETS)) Ratio
    from v$session, v$sess_io
    where v$session.SID = v$sess_io.SID
    and (CONSISTENT_GETS+BLOCK_GETS) > 0
    and USERNAME is not null
    order by ((CONSISTENT_GETS+BLOCK_GETS-PHYSICAL_READS) / (CONSISTENT_GETS+BLOCK_GETS))

    Labels:

    posted by Srinivasan .R @ 1:56 AM  
    0 Comments:
    Post a Comment
    << Home
     
    About Me

    Name: Srinivasan .R
    Home: Chennai, India

    About Me:
    I am working as an Oracle Applications DBA specializing in EBS 11i/R12 with Over 14+ years of experience, mainly in different versions of Oracle Database & Application administration on various platforms like HP-UX, SOLARIS, AIX, Red hat Linux & Windows
    See my complete profile
    High Availability
  • Oracle10g RAC Installation
  • A Quick Reference for Oracle Database 10g RAC on Linux and Unix Platforms
  • Implementing Oracle 10g RAC with ASM on AIX
  • Locked objects for whole RAC
  • Monitor Memory RAC
  • Sessions RAC
  • Install Oracle 11g RAC On Linux
  • Migrating Oracle10g DB to ASM
  • Helpful Links
  • Good Metalink Notes
  • Discoverer:Metalink Notes
  • Logs Scripts:Metalink Notes
  • Support:Metalink Notes
  • Previous Post
  • Tablespace Information
  • Disk I/O, Events, Waits
  • General Info
  • Migrating Oracle10g DB to ASM
  • Install Oracle 11g RAC On Linux
  • Sessions RAC
  • Monitor Memory RAC
  • Lists all locked objects for whole RAC
  • Concurrent Managers
  • Backup Recovery
  • Archives
    Download Software
  • Oracle 11g
  • Oracle 10g
  • 10g Express Edition
  • Oracle 9i
  • Oracle Apps
  • Oracle Linux
  • Oracle VM
  • App Server
  • Solaris
  • Fedora
  • Fedora
  • OpenSUSE
  • Ubuntu
  • Advertisement Links
    INTUIT Technology

    MACHS DATA

    Add Ons
    Locations of visitors to this page

    Add to Google Reader or Homepage

    Template by
    Sreene



    Oracle Application DBA Portal