Long Duration SYSMETRIC

select metric_name from  V$METRICNAME where group_name='System Metrics Long Duration'

METRIC_NAME
----------------------------------------------------------------
Total PGA Used by SQL Workareas - what's difference in this and Total PGA Allocated?
 MEM Total PGA Allocated
  IO    Temp Space Used
Cell Physical IO Interconnect Bytes
 CPU Host CPU Usage Per Sec
Background Time Per Sec
 CPU Background CPU Usage Per Sec
Workload Capture and Replay status
Replayed user calls
Captured user calls
  IO    I/O Requests per Second       = 11+?  Physical Write Total IO Requests Per Sec  + Physical Read Total IO Requests Per Sec ?
  IO    I/O Megabytes per Second  = 11+?  Physical Write Total Bytes Per Sec + Physical Read Total Bytes Per Sec ?
  IO    Average Synchronous Single-Block Read Latency - 11+?  how is this different than db file sequential read times ?
Streams Pool Usage Percentage
 CPU Current OS Load
Total Table Scans Per User Call
Total Sorts Per User Call
Logical Reads Per User Call
Executions Per User Call
DB Block Gets Per User Call
DB Block Changes Per User Call
 CPU Host CPU Utilization (%)
  IO    Physical Write Bytes Per Sec
  IO    Physical Write IO Requests Per Sec
  IO    Physical Read Bytes Per Sec
  IO    Physical Read IO Requests Per Sec
  IO    Physical Write Total Bytes Per Sec
 AAS  Average Active Sessions - 11+?
 AAS  Database Time Per Sec - should just be 100*AAS ie centisecs/sec
Txns Per Logon
Executions Per Sec
Executions Per Txn
 AAS  Active Parallel Sessions - 11+? AAS for PQO?, looks like it, check out https://sites.google.com/site/oraclemonitor/oem-parallel-execution-tab
 AAS  Active Serial Sessions - 11+?  ?? is this AAS for non PQO ?
PQ Slave Session Count
PQ QC Session Count
Session Count
Session Limit %
Process Limit %
PGA Cache Hit %
Shared Pool Free %
Library Cache Miss Ratio
Library Cache Hit Ratio
Row Cache Miss Ratio
Row Cache Hit Ratio
Response Time Per Txn
 AAS  Database CPU Time Ratio - if this is ratio of CPU to WAIT, that's cool along with AAS, % of time on CPU , rest is wait time
 AAS  Database Wait Time Ratio - looks like it , check out  http://www.oracle.com/technetwork/articles/schumacher-analysis-099313.html
SQL Service Response Time
User Limit %
Current Open Cursors Count
Current Logons Count
Global Cache Blocks Lost
Global Cache Blocks Corrupted
  IO    Physical Write Total IO Requests Per Sec
Global Cache Average Current Get Time
Global Cache Average CR Get Time
GC Current Block Received Per Txn
GC Current Block Received Per Second
GC CR Block Received Per Txn
GC CR Block Received Per Second
  IO    Physical Read Total Bytes Per Sec
  IO    Physical Read Total IO Requests Per Sec
DDL statements parallelized Per Sec
DML statements parallelized Per Sec
Queries parallelized Per Sec
PX operations not downgraded Per Sec
PX downgraded to serial Per Sec
PX downgraded 75 to 99% Per Sec
PX downgraded 50 to 75% Per Sec
PX downgraded 25 to 50% Per Sec
PX downgraded 1 to 25% Per Sec
Branch Node Splits Per Txn
Branch Node Splits Per Sec
Leaf Node Splits Per Txn
Leaf Node Splits Per Sec
User Rollback Undo Records Applied Per Txn
User Rollback UndoRec Applied Per Sec
CR Undo Records Applied Per Txn
CR Undo Records Applied Per Sec
CR Blocks Created Per Txn
CR Blocks Created Per Sec
CPU Usage Per Txn
 CPU CPU Usage Per Sec
Consistent Read Changes Per Txn
Consistent Read Changes Per Sec
DB Block Changes Per Txn
DB Block Changes Per Sec
Consistent Read Gets Per Txn
Consistent Read Gets Per Sec
DB Block Gets Per Txn
DB Block Gets Per Sec
Enqueue Requests Per Txn
Enqueue Requests Per Sec
Enqueue Deadlocks Per Txn
Enqueue Deadlocks Per Sec
Enqueue Waits Per Txn
Enqueue Waits Per Sec
Enqueue Timeouts Per Txn
Enqueue Timeouts Per Sec
Network Traffic Volume Per Sec
User Calls Ratio
Soft Parse Ratio
Execute Without Parse Ratio
Rows Per Sort
Disk Sort Per Txn
 IO      Disk Sort Per Sec - is this sort write? sort read, how does one get read writes to temp ?
Cursor Cache Hit Ratio
Parse Failure Count Per Txn
Parse Failure Count Per Sec
Hard Parse Count Per Txn
Hard Parse Count Per Sec
Total Parse Count Per Txn
Total Parse Count Per Sec
Total Index Scans Per Txn
Total Index Scans Per Sec
Full Index Scans Per Txn
Full Index Scans Per Sec
Total Table Scans Per Txn
Total Table Scans Per Sec
Long Table Scans Per Txn
Long Table Scans Per Sec
Redo Writes Per Txn
 IO      Redo Writes Per Sec
Background Checkpoints Per Sec
DBWR Checkpoints Per Sec
Logical Reads Per Txn
Logical Reads Per Sec
Recursive Calls Per Txn
Recursive Calls Per Sec
User Calls Per Txn
User Calls Per Sec
User Rollbacks Percentage
User Rollbacks Per Sec
User Commits Percentage
User Commits Per Sec
Open Cursors Per Txn
Open Cursors Per Sec
Logons Per Txn
Logons Per Sec
Redo Generated Per Txn
  IO    Redo Generated Per Sec
Physical Writes Direct Lobs  Per Txn
  IO    Physical Writes Direct Lobs Per Sec
Physical Reads Direct Lobs Per Txn
  IO    Physical Reads Direct Lobs Per Sec
Physical Writes Direct Per Txn
  IO    Physical Writes Direct Per Sec
Physical Reads Direct Per Txn
  IO    Physical Reads Direct Per Sec
Physical Writes Per Txn
  IO    Physical Writes Per Sec
Physical Reads Per Txn
  IO    Physical Reads Per Sec
User Transaction Per Sec
Redo Allocation Hit Ratio
Memory Sorts Ratio
Buffer Cache Hit Ratio


stats on 11.2.0.1 but not 10.2.0.4

V1                V2                METRIC_NAME
----------------- ----------------- ----------------------------------------------
                  11.2.0.1.0        Active Parallel Sessions
                  11.2.0.1.0        Active Serial Sessions
                  11.2.0.1.0        Average Active Sessions
                  11.2.0.1.0        Average Synchronous Single-Block Read Latency
                  11.2.0.1.0        Background CPU Usage Per Sec
                  11.2.0.1.0        Background Time Per Sec
                  11.2.0.1.0        Captured user calls
                  11.2.0.1.0        Cell Physical IO Interconnect Bytes
                  11.2.0.1.0        DDL statements parallelized Per Sec
                  11.2.0.1.0        DML statements parallelized Per Sec
                  11.2.0.1.0        Host CPU Usage Per Sec
                  11.2.0.1.0        I/O Megabytes per Second
                  11.2.0.1.0        I/O Requests per Second
                  11.2.0.1.0        PQ QC Session Count
                  11.2.0.1.0        PQ Slave Session Count
                  11.2.0.1.0        PX downgraded 1 to 25% Per Sec
                  11.2.0.1.0        PX downgraded 25 to 50% Per Sec
                  11.2.0.1.0        PX downgraded 50 to 75% Per Sec
                  11.2.0.1.0        PX downgraded 75 to 99% Per Sec
                  11.2.0.1.0        PX operations not downgraded Per Sec
                  11.2.0.1.0        Queries parallelized Per Sec
                  11.2.0.1.0        Replayed user calls
                  11.2.0.1.0        Session Count
                  11.2.0.1.0        Temp Space Used
                  11.2.0.1.0        Total PGA Allocated
                  11.2.0.1.0        Total PGA Used by SQL Workareas
                  11.2.0.1.0        Workload Capture and Replay status


I wanted to find out what the average multiblock I/O size.  Based on

bytes = "physical read bytes"
total data blocks read = bytes / database block size
single block reads = "physical read IO requests" - "physical read total multi block requests"
blocks read via multi-block reads = total data blocks read - single block reads
It seems like

average multi block read size
        =  blocks read via multi-block reads / multi block read requests
        = ( "physical read bytes"/ (db block size))- ("physical read IO requests" - " physical read total multi block requests"))
           ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
           " physical read total multi block requests"

 ( see statistics definitions on https://sites.google.com/site/oraclemonitor/oracle-statistics-defined-11-2 )
Comments