How to find CPU usage for a session

Below is the query to find High CPU utilizing Database sessions and their state.

SET LINESIZE 145

SET PAGESIZE 9999

COLUMN sid               FORMAT 9999             HEADING 'SID'

COLUMN serial_id         FORMAT 999999           HEADING 'Serial#'

COLUMN session_status    FORMAT a9               HEADING 'Status'          JUSTIFY right

COLUMN oracle_username   FORMAT a12              HEADING 'Oracle User'     JUSTIFY right

COLUMN os_username       FORMAT a9               HEADING 'O/S User'        JUSTIFY right

COLUMN os_pid            FORMAT 9999999          HEADING 'O/S PID'         JUSTIFY right

COLUMN session_program   FORMAT a20              HEADING 'Session Program' TRUNC

COLUMN session_machine   FORMAT a14              HEADING 'Machine'         JUSTIFY right TRUNC

COLUMN cpu_value         FORMAT 999,999,999,999  HEADING 'CPU'

prompt

prompt +----------------------------------------------------+

prompt | User Sessions Ordered by CPU                       |

prompt +----------------------------------------------------+

SELECT

    s.sid                sid

  , s.serial#            serial_id

  , lpad(s.status,9)     session_status

  , lpad(s.username,12)  oracle_username

  , lpad(s.osuser,9)     os_username

  , lpad(p.spid,7)       os_pid

  , s.program            session_program

  , lpad(s.machine,14)   session_machine

  , sstat.value          cpu_value

FROM

    v$process  p

  , v$session  s

  , v$sesstat  sstat

  , v$statname statname

WHERE

      p.addr (+)          = s.paddr

  AND s.sid               = sstat.sid

  AND statname.statistic# = sstat.statistic#

  AND statname.name       = 'CPU used by this session'

ORDER BY cpu_value DESC

/

How do you see how many memory segments are acquired by Oracle Instances?

Check on x$bh it show you what objects are in the buffer.

 Try this query:

 SELECT /*+ ordered use_hash(o) use_hash(bh) */ bp.NAME BUFFER_POOL u.NAME owner so.object_type segment_type o.NAME segment_name COUNT (*) cached_blocks FROM v$buffer_pool bp SYS.seg$ s SYS.sys_objects so SYS.obj$ o SYS.user$ u x$bh bh WHERE bh.obj o.dataobj# AND o.obj# so.object_id AND o.type# so.object_type_id AND o.owner# u.user# AND so.segment_type_id s.type# AND so.ts_number s.ts# AND so.header_file s.file# AND so.header_block s.block# AND DECODE (s.cachehint 0 3 s.cachehint) bp.ID GROUP BY u.NAME so.object_type o.NAME bp.NAME ORDER BY bp.NAME cached_blocks DESC;