Oracle/筆記: Database Metrics

在 Oracle10g 以前，DBA 往往要計算大量統計資訊才能了解資料庫的效能及負載, 比如 database buffer hit ratio , library cache hit ratio , or physical reads per second.
為了要計算這些統計資訊, DBA 需要寫一些複雜的 SQL 來 join 那些 V$ 的 VIEW (i.e.  v$sysstat and v$statname), 而這些查詢都要消耗部份的 resource, 並且 DB 造成一定程度的負擔.

現在，Oracle 10g 資料庫引入了一種新型的統計指標稱為 DATABASE METRIC, 可以提供上述那些資訊, 甚至包括所有的重要資料庫統計資訊並且都由資料庫自動預先計算並可供立即使用.
DATABASE METRIC 提供數個 metric-related v$ views 使用, 並且分為兩類：Long duration 及 Short duration.
Long  duration 的 database metric 由Oralce每60秒計算一次
Short duration 的 database metric 由Oralce每15秒計算一次

此外, 所有 METRIC 由其含義進行分組, 可用的 metric groups 可查詢 v$metricgroup, 其中欄位 interval_size 表示採樣頻率, 單位是百分之一秒.

SQL> SELECT name,interval_size FROM v$metricgroup ORDER BY interval_size;
NAME                                     INTERVAL_SIZE
---------------------------------------- -------------
Service Metrics (Short)                            500
System Metrics Short Duration                     1500
Session Metrics Short Duration                    1500
Service Metrics                                   6000
System Metrics Long Duration                      6000
Event Class Metrics                               6000
Event Metrics                                     6000
Session Metrics Long Duration                     6000
Tablespace Metrics Long Duration                  6000
File Metrics Long Duration                       60000
10 rows selected.

可用的 metric name 可查詢 v$metricname, Oracle 資料庫使用 circular memory buffer 保存 database metrics 的歷史記錄
並且由 MMON 存到 AWR 中, 預設值 Oracle 保持歷史資料的持續時間為一小時

GROUP_NAME                               METRIC_NAME                                METRIC_UNIT
---------------------------------------- ------------------------------------------ ------------------------------
Event Class Metrics                      Average Users Waiting Counts               Users
Event Class Metrics                      Database Time Spent Waiting (%)            % (TimeWaited / DBTime)
Event Class Metrics                      Total Time Waited                          CentiSeconds
Event Class Metrics                      Total Wait Counts                          Waits
Event Metrics                            Number of Sessions Waiting (Event)         Sessions
Event Metrics                            Total Time Waited                          CentiSeconds
Event Metrics                            Total Wait Counts                          Waits
File Metrics Long Duration               Average File Read Time (Files-Long)        CentiSeconds Per Read
File Metrics Long Duration               Average File Write Time (Files-Long)       CentiSeconds Per Write
File Metrics Long Duration               Physical Block Reads (Files-Long)          Blocks
File Metrics Long Duration               Physical Block Writes (Files-Long)         Blocks
File Metrics Long Duration               Physical Reads (Files-Long)                Reads
File Metrics Long Duration               Physical Writes (Files-Long)               Writes
Service Metrics                          CPU Time Per User Call                     Microseconds Per Call
Service Metrics                          DB Time Per Second                         CentiSeconds
Service Metrics                          DB Time Per User Call                      Microseconds Per Call
Service Metrics                          Elapsed Time Per User Call                 Microseconds Per Call
Service Metrics                          User Calls Per Second                      User Calls Per Second
Service Metrics (Short)                  CPU Time Per User Call                     Microseconds Per Call
Service Metrics (Short)                  DB Time Per Second                         CentiSeconds
Service Metrics (Short)                  DB Time Per User Call                      Microseconds Per Call
Service Metrics (Short)                  Elapsed Time Per User Call                 Microseconds Per Call
Service Metrics (Short)                  User Calls Per Second                      User Calls Per Second
Session Metrics Long Duration            Blocked User Session Count                 Sessions
Session Metrics Short Duration           CPU Time (Session)                         CentiSeconds
Session Metrics Short Duration           Hard Parse Count (Session)                 Parses
Session Metrics Short Duration           Logical Read Count                         Reads
Session Metrics Short Duration           Logical Reads Ratio (Sess/Sys) %           % SessLogRead/SystemLogRead
Session Metrics Short Duration           PGA Memory (Session)                       Bytes
Session Metrics Short Duration           Physical Reads (Session)                   Reads
Session Metrics Short Duration           Physical Reads Ratio (Sess/Sys) %          % SessPhyRead/SystemPhyRead
Session Metrics Short Duration           Soft Parse Count                           Parses
Session Metrics Short Duration           Total Parse Count (Session)                Parses
Session Metrics Short Duration           User Transaction Count (Session)           Transactions
System Metrics Long Duration             Background Checkpoints Per Sec             Check Points Per Second
System Metrics Long Duration             Branch Node Splits Per Sec                 Splits Per Second
System Metrics Long Duration             Branch Node Splits Per Txn                 Splits Per Txn
System Metrics Long Duration             Buffer Cache Hit Ratio                     % (LogRead - PhyRead)/LogRead
System Metrics Long Duration             CPU Usage Per Sec                          CentiSeconds Per Second
System Metrics Long Duration             CPU Usage Per Txn                          CentiSeconds Per Txn
System Metrics Long Duration             CR Blocks Created Per Sec                  Blocks Per Second
System Metrics Long Duration             CR Blocks Created Per Txn                  Blocks Per Txn
System Metrics Long Duration             CR Undo Records Applied Per Sec            Undo Records Per Second
System Metrics Long Duration             CR Undo Records Applied Per Txn            Records Per Txn
System Metrics Long Duration             Consistent Read Changes Per Sec            Blocks Per Second
System Metrics Long Duration             Consistent Read Changes Per Txn            Blocks Per Txn
System Metrics Long Duration             Consistent Read Gets Per Sec               Blocks Per Second
System Metrics Long Duration             Consistent Read Gets Per Txn               Blocks Per Txn
System Metrics Long Duration             Current Logons Count                       Logons
System Metrics Long Duration             Current OS Load                            Number Of Processes
System Metrics Long Duration             Current Open Cursors Count                 Cursors
System Metrics Long Duration             Cursor Cache Hit Ratio                     % CursorCacheHit/SoftParse
System Metrics Long Duration             DB Block Changes Per Sec                   Blocks Per Second
System Metrics Long Duration             DB Block Changes Per Txn                   Blocks Per Txn
System Metrics Long Duration             DB Block Changes Per User Call             Blocks Per Call
System Metrics Long Duration             DB Block Gets Per Sec                      Blocks Per Second
System Metrics Long Duration             DB Block Gets Per Txn                      Blocks Per Txn
System Metrics Long Duration             DB Block Gets Per User Call                Blocks Per Call
System Metrics Long Duration             DBWR Checkpoints Per Sec                   Check Points Per Second
System Metrics Long Duration             Database CPU Time Ratio                    % Cpu/DB_Time
System Metrics Long Duration             Database Time Per Sec                      CentiSeconds Per Second
System Metrics Long Duration             Database Wait Time Ratio                   % Wait/DB_Time
System Metrics Long Duration             Disk Sort Per Sec                          Sorts Per Second
System Metrics Long Duration             Disk Sort Per Txn                          Sorts Per Txn
System Metrics Long Duration             Enqueue Deadlocks Per Sec                  Deadlocks Per Second
System Metrics Long Duration             Enqueue Deadlocks Per Txn                  Deadlocks Per Txn
System Metrics Long Duration             Enqueue Requests Per Sec                   Requests Per Second
System Metrics Long Duration             Enqueue Requests Per Txn                   Requests Per Txn
System Metrics Long Duration             Enqueue Timeouts Per Sec                   Timeouts Per Second
System Metrics Long Duration             Enqueue Timeouts Per Txn                   Timeouts Per Txn
System Metrics Long Duration             Enqueue Waits Per Sec                      Waits Per Second
System Metrics Long Duration             Enqueue Waits Per Txn                      Waits Per Txn
System Metrics Long Duration             Execute Without Parse Ratio                % (ExecWOParse/TotalExec)
System Metrics Long Duration             Executions Per Sec                         Executes Per Second
System Metrics Long Duration             Executions Per Txn                         Executes Per Txn
System Metrics Long Duration             Executions Per User Call                   Executes Per Call
System Metrics Long Duration             Full Index Scans Per Sec                   Scans Per Second
System Metrics Long Duration             Full Index Scans Per Txn                   Scans Per Txn
System Metrics Long Duration             GC CR Block Received Per Second            Blocks Per Second
System Metrics Long Duration             GC CR Block Received Per Txn               Blocks Per Txn
System Metrics Long Duration             GC Current Block Received Per Second       Blocks Per Second
System Metrics Long Duration             GC Current Block Received Per Txn          Blocks Per Txn
System Metrics Long Duration             Global Cache Average CR Get Time           CentiSeconds Per Get
System Metrics Long Duration             Global Cache Average Current Get Time      CentiSeconds Per Get
System Metrics Long Duration             Global Cache Blocks Corrupted              Blocks
System Metrics Long Duration             Global Cache Blocks Lost                   Blocks
System Metrics Long Duration             Hard Parse Count Per Sec                   Parses Per Second
System Metrics Long Duration             Hard Parse Count Per Txn                   Parses Per Txn
System Metrics Long Duration             Host CPU Utilization (%)                   % Busy/(Idle+Busy)
System Metrics Long Duration             Leaf Node Splits Per Sec                   Splits Per Second
System Metrics Long Duration             Leaf Node Splits Per Txn                   Splits Per Txn
System Metrics Long Duration             Library Cache Hit Ratio                    % Hits/Pins
System Metrics Long Duration             Library Cache Miss Ratio                   % Misses/Gets
System Metrics Long Duration             Logical Reads Per Sec                      Reads Per Second
System Metrics Long Duration             Logical Reads Per Txn                      Reads Per Txn
System Metrics Long Duration             Logical Reads Per User Call                Reads Per Call
System Metrics Long Duration             Logons Per Sec                             Logons Per Second
System Metrics Long Duration             Logons Per Txn                             Logons Per Txn
System Metrics Long Duration             Long Table Scans Per Sec                   Scans Per Second
System Metrics Long Duration             Long Table Scans Per Txn                   Scans Per Txn
System Metrics Long Duration             Memory Sorts Ratio                         % MemSort/(MemSort + DiskSort)
System Metrics Long Duration             Network Traffic Volume Per Sec             Bytes Per Second
System Metrics Long Duration             Open Cursors Per Sec                       Cursors Per Second
System Metrics Long Duration             Open Cursors Per Txn                       Cursors Per Txn
System Metrics Long Duration             PGA Cache Hit %                            % Bytes/TotalBytes
System Metrics Long Duration             PX downgraded 25% or more Per Sec          PX Operations Per Second
System Metrics Long Duration             PX downgraded 50% or more Per Sec          PX Operations Per Second
System Metrics Long Duration             PX downgraded 75% or more Per Sec          PX Operations Per Second
System Metrics Long Duration             PX downgraded Parallel Operation Per Sec   Operations Per Second
System Metrics Long Duration             PX downgraded to serial Per Sec            PX Operations Per Second
System Metrics Long Duration             Parse Failure Count Per Sec                Parses Per Second
System Metrics Long Duration             Parse Failure Count Per Txn                Parses Per Txn
System Metrics Long Duration             Physical Read Bytes Per Sec                Bytes Per Second
System Metrics Long Duration             Physical Read IO Requests Per Sec          Requests Per Second
System Metrics Long Duration             Physical Read Total Bytes Per Sec          Bytes Per Second
System Metrics Long Duration             Physical Read Total IO Requests Per Sec    Requests Per Second
System Metrics Long Duration             Physical Reads Direct Lobs Per Sec         Reads Per Second
System Metrics Long Duration             Physical Reads Direct Lobs Per Txn         Reads Per Txn
System Metrics Long Duration             Physical Reads Direct Per Sec              Reads Per Second
System Metrics Long Duration             Physical Reads Direct Per Txn              Reads Per Txn
System Metrics Long Duration             Physical Reads Per Sec                     Reads Per Second
System Metrics Long Duration             Physical Reads Per Txn                     Reads Per Txn
System Metrics Long Duration             Physical Write Bytes Per Sec               Bytes Per Second
System Metrics Long Duration             Physical Write IO Requests Per Sec         Requests Per Second
System Metrics Long Duration             Physical Write Total Bytes Per Sec         Bytes Per Second
System Metrics Long Duration             Physical Write Total IO Requests Per Sec   Requests Per Second
System Metrics Long Duration             Physical Writes Direct Lobs  Per Txn       Writes Per Txn
System Metrics Long Duration             Physical Writes Direct Lobs Per Sec        Writes Per Second
System Metrics Long Duration             Physical Writes Direct Per Sec             Writes Per Second
System Metrics Long Duration             Physical Writes Direct Per Txn             Writes Per Txn
System Metrics Long Duration             Physical Writes Per Sec                    Writes Per Second
System Metrics Long Duration             Physical Writes Per Txn                    Writes Per Txn
System Metrics Long Duration             Process Limit %                            % Processes/Limit
System Metrics Long Duration             Recursive Calls Per Sec                    Calls Per Second
System Metrics Long Duration             Recursive Calls Per Txn                    Calls Per Txn
System Metrics Long Duration             Redo Allocation Hit Ratio                  % (#Redo - RedoSpaceReq)/#Redo
System Metrics Long Duration             Redo Generated Per Sec                     Bytes Per Second
System Metrics Long Duration             Redo Generated Per Txn                     Bytes Per Txn
System Metrics Long Duration             Redo Writes Per Sec                        Writes Per Second
System Metrics Long Duration             Redo Writes Per Txn                        Writes Per Txn
System Metrics Long Duration             Response Time Per Txn                      CentiSeconds Per Txn
System Metrics Long Duration             Row Cache Hit Ratio                        % Hits/Gets
System Metrics Long Duration             Row Cache Miss Ratio                       % Misses/Gets
System Metrics Long Duration             Rows Per Sort                              Rows Per Sort
System Metrics Long Duration             SQL Service Response Time                  CentiSeconds Per Call
System Metrics Long Duration             Session Limit %                            % Sessions/Limit
System Metrics Long Duration             Shared Pool Free %                         % Free/Total
System Metrics Long Duration             Soft Parse Ratio                           % SoftParses/TotalParses
System Metrics Long Duration             Total Index Scans Per Sec                  Scans Per Second
System Metrics Long Duration             Total Index Scans Per Txn                  Scans Per Txn
System Metrics Long Duration             Total Parse Count Per Sec                  Parses Per Second
System Metrics Long Duration             Total Parse Count Per Txn                  Parses Per Txn
System Metrics Long Duration             Total Sorts Per User Call                  Sorts Per Call
System Metrics Long Duration             Total Table Scans Per Sec                  Scans Per Second
System Metrics Long Duration             Total Table Scans Per Txn                  Scans Per Txn
System Metrics Long Duration             Total Table Scans Per User Call            Tables Per Call
System Metrics Long Duration             Txns Per Logon                             Txns Per Logon
System Metrics Long Duration             User Calls Per Sec                         Calls Per Second
System Metrics Long Duration             User Calls Per Txn                         Calls Per Txn
System Metrics Long Duration             User Calls Ratio                           % UserCalls/AllCalls
System Metrics Long Duration             User Commits Per Sec                       Commits Per Second
System Metrics Long Duration             User Commits Percentage                    % (UserCommit/TotalUserTxn)
System Metrics Long Duration             User Limit %                               % Sessions/License_Limit
System Metrics Long Duration             User Rollback Undo Records Applied Per Txn Records Per Txn
System Metrics Long Duration             User Rollback UndoRec Applied Per Sec      Records Per Second
System Metrics Long Duration             User Rollbacks Per Sec                     Rollbacks Per Second
System Metrics Long Duration             User Rollbacks Percentage                  % (UserRollback/TotalUserTxn)
System Metrics Long Duration             User Transaction Per Sec                   Transactions Per Second
System Metrics Short Duration            Buffer Cache Hit Ratio                     % (LogRead - PhyRead)/LogRead
System Metrics Short Duration            Consistent Read Changes Per Sec            Blocks Per Second
System Metrics Short Duration            Consistent Read Changes Per Txn            Blocks Per Txn
System Metrics Short Duration            Consistent Read Gets Per Sec               Blocks Per Second
System Metrics Short Duration            Consistent Read Gets Per Txn               Blocks Per Txn
System Metrics Short Duration            DB Block Changes Per Sec                   Blocks Per Second
System Metrics Short Duration            DB Block Changes Per Txn                   Blocks Per Txn
System Metrics Short Duration            DB Block Gets Per Sec                      Blocks Per Second
System Metrics Short Duration            DB Block Gets Per Txn                      Blocks Per Txn
System Metrics Short Duration            Database CPU Time Ratio                    % Cpu/DB_Time
System Metrics Short Duration            Database Time Per Sec                      CentiSeconds Per Second
System Metrics Short Duration            Execute Without Parse Ratio                % (ExecWOParse/TotalExec)
System Metrics Short Duration            Executions Per Sec                         Executes Per Second
System Metrics Short Duration            Executions Per Txn                         Executes Per Txn
System Metrics Short Duration            Full Index Scans Per Sec                   Scans Per Second
System Metrics Short Duration            Full Index Scans Per Txn                   Scans Per Txn
System Metrics Short Duration            Host CPU Utilization (%)                   % Busy/(Idle+Busy)
System Metrics Short Duration            Library Cache Hit Ratio                    % Hits/Pins
System Metrics Short Duration            Logical Reads Per Sec                      Reads Per Second
System Metrics Short Duration            Logical Reads Per Txn                      Reads Per Txn
System Metrics Short Duration            Logons Per Sec                             Logons Per Second
System Metrics Short Duration            Logons Per Txn                             Logons Per Txn
System Metrics Short Duration            Memory Sorts Ratio                         % MemSort/(MemSort + DiskSort)
System Metrics Short Duration            Physical Reads Direct Per Sec              Reads Per Second
System Metrics Short Duration            Physical Reads Direct Per Txn              Reads Per Txn
System Metrics Short Duration            Physical Reads Per Sec                     Reads Per Second
System Metrics Short Duration            Physical Reads Per Txn                     Reads Per Txn
System Metrics Short Duration            Physical Writes Per Sec                    Writes Per Second
System Metrics Short Duration            Physical Writes Per Txn                    Writes Per Txn
System Metrics Short Duration            Redo Generated Per Sec                     Bytes Per Second
System Metrics Short Duration            Redo Generated Per Txn                     Bytes Per Txn
System Metrics Short Duration            Redo Writes Per Sec                        Writes Per Second
System Metrics Short Duration            Redo Writes Per Txn                        Writes Per Txn
System Metrics Short Duration            Shared Pool Free %                         % Free/Total
System Metrics Short Duration            Soft Parse Ratio                           % SoftParses/TotalParses
System Metrics Short Duration            Total Table Scans Per Sec                  Scans Per Second
System Metrics Short Duration            Total Table Scans Per Txn                  Scans Per Txn
System Metrics Short Duration            Txns Per Logon                             Txns Per Logon
System Metrics Short Duration            User Calls Per Sec                         Calls Per Second
System Metrics Short Duration            User Calls Per Txn                         Calls Per Txn
System Metrics Short Duration            User Transaction Per Sec                   Transactions Per Second
Tablespace Metrics Long Duration         Tablespace Bytes Space Usage               Tablespace KiloBytes
Tablespace Metrics Long Duration         Tablespace Space Usage                     Tablespace Blocks
211 rows selected.








。