oracle performance tuning效能優化學習系列(三)_補二

wisdomone1發表於2013-03-11

--說明是前後端所有的會話統計
SQL> select distinct sid from v$session_event;
 
       SID
----------
         1
        42
        43
        83
        87
       123
         6
        44
       128
         2
        84
        86
         4
        81
         8
        41
        45
         3
       124
         9
 
       SID
----------
        10
        12
        82
       122
       127
 
25 rows selected
 
SQL> select count(*) from v$session;
 
  COUNT(*)
----------
        25
       
--同理,基於上述資料,根據等待事件分類的彙總       
SQL> select * from v$session_wait_class;
 
       SID    SERIAL# WAIT_CLASS_ID WAIT_CLASS# WAIT_CLASS                                                       TOTAL_WAITS TIME_WAITED
---------- ---------- ------------- ----------- ---------------------------------------------------------------- ----------- -----------
         1          1    2723168908           6 Idle                                                                    7722     1270719
         2          1    2723168908           6 Idle                                                                   12707     1270420
         3          1    2723168908           6 Idle                                                                    6521     1261160
         3          1    1740759767           8 User I/O                                                                   2           6
         3          1    4108307767           9 System I/O                                                             15682        9378
         4          1    1893977003           0 Other                                                                      1           0
         4          1    2723168908           6 Idle                                                                   16101     1270540
         4          1    1740759767           8 User I/O                                                                   1           0
         6          5    1893977003           0 Other                                                                    326           1
         6          5    2723168908           6 Idle                                                                     459     1268750
         6          5    1740759767           8 User I/O                                                                  78          53
         8          9    1893977003           0 Other                                                                      1           0
         8          9    3386400367           5 Commit                                                                     1           0
         8          9    2723168908           6 Idle                                                                     274      205681
         8          9    2000153315           7 Network                                                                  143           0
         8          9    1740759767           8 User I/O                                                                  11          22
         9         14    4217450380           1 Application                                                                2           0
         9         14    2723168908           6 Idle                                                                     269      207971
         9         14    2000153315           7 Network                                                                  218           2
        10         37    2723168908           6 Idle                                                                      73       35998
 
       SID    SERIAL# WAIT_CLASS_ID WAIT_CLASS# WAIT_CLASS                                                       TOTAL_WAITS TIME_WAITED
---------- ---------- ------------- ----------- ---------------------------------------------------------------- ----------- -----------
        12         44    1893977003           0 Other                                                                     36           1
        41          5    1893977003           0 Other                                                                     13          78
        41          5    3875070507           4 Concurrency                                                                2           3
        41          5    3386400367           5 Commit                                                                     2           0
        41          5    2723168908           6 Idle                                                                       5         960
        41          5    2000153315           7 Network                                                                    6           0
        41          5    1740759767           8 User I/O                                                                1226         622
        41          5    4108307767           9 System I/O                                                               130          32
        42          1    2723168908           6 Idle                                                                   12707     1270857
        43          1    1893977003           0 Other                                                                      2           0
        43          1    2723168908           6 Idle                                                                    7723     1270726
        44          1    1893977003           0 Other                                                                      2           0
        44          1    3875070507           4 Concurrency                                                                1           2
        44          1    2723168908           6 Idle                                                                      48     1243239
        44          1    1740759767           8 User I/O                                                                 157         252
        45         16    3875070507           4 Concurrency                                                               15          29
        45         16    2723168908           6 Idle                                                                    7514     1239398
        81          1    3875070507           4 Concurrency                                                                1           2
        81          1    2723168908           6 Idle                                                                    4359     1270729
        82          1    1893977003           0 Other                                                                      4           0
        82          1    3875070507           4 Concurrency                                                                1           1
 
       SID    SERIAL# WAIT_CLASS_ID WAIT_CLASS# WAIT_CLASS                                                       TOTAL_WAITS TIME_WAITED
---------- ---------- ------------- ----------- ---------------------------------------------------------------- ----------- -----------
        82          1    2723168908           6 Idle                                                                    7724     1270717
        82          1    1740759767           8 User I/O                                                                  10           4
        83          1    1893977003           0 Other                                                                      1           0
        83          1    2723168908           6 Idle                                                                    7717     1270256
        83          1    1740759767           8 User I/O                                                                  44          34
        83          1    4108307767           9 System I/O                                                              1764         363
        84          1    2723168908           6 Idle                                                                      11     1266303
        84          1    1740759767           8 User I/O                                                                   3           8
        86          1    1893977003           0 Other                                                                      2           1
        86          1    3875070507           4 Concurrency                                                                2          18
        86          1    2723168908           6 Idle                                                                     609     1269784
        86          1    1740759767           8 User I/O                                                                   1           0
        87          3    1893977003           0 Other                                                                      3           1
        87          3    3875070507           4 Concurrency                                                               56          86
        87          3    2723168908           6 Idle                                                                    3618     1268114
        87          3    1740759767           8 User I/O                                                                  41          33
        87          3    4108307767           9 System I/O                                                               460         119
       122          1    1893977003           0 Other                                                                     14           0
       122          1    2723168908           6 Idle                                                                    7828     1270726
       123          1    1893977003           0 Other                                                                     26           0
       123          1    2723168908           6 Idle                                                                    8503     1270589
 
       SID    SERIAL# WAIT_CLASS_ID WAIT_CLASS# WAIT_CLASS                                                       TOTAL_WAITS TIME_WAITED
---------- ---------- ------------- ----------- ---------------------------------------------------------------- ----------- -----------
       123          1    1740759767           8 User I/O                                                                  33           7
       123          1    4108307767           9 System I/O                                                              1051         106
       124          1    1893977003           0 Other                                                                      3           2
       124          1    4166625743           3 Administrative                                                             1          49
       124          1    3875070507           4 Concurrency                                                               49          96
       124          1    2723168908           6 Idle                                                                    7640     1269496
       124          1    1740759767           8 User I/O                                                                 650         357
       124          1    4108307767           9 System I/O                                                               535         245
       127          3    2723168908           6 Idle                                                                       1           0
       128         11    1893977003           0 Other                                                                     23           2
 
72 rows selected       


時間模型統計
Time Model Statistics
1,定量化評估資料庫的壓力和負荷
2,用於對比效能;
3,v$system_time_model and v$session_time_model
4,是累計計算的;自資料庫啟動
5,比如db time,即cpu和用於等等非空閒會話的等待時間
6,因為db time,為上述的累積之和,可能大於資料庫啟動所花費的時間
  即:若共有5個會話;則db time為資料庫啟動時間*5

7,調優即減少db time,即減少等待每會話的時間; 

SQL> select * from v$sys_time_model;
 
   STAT_ID STAT_NAME                                                             VALUE
---------- ---------------------------------------------------------------- ----------
3649082374 DB time                                                           409434626
2748282437 DB CPU                                                             20794923
4157170894 background elapsed time                                          1087028883
2451517896 background cpu time                                               930015550
4127043053 sequence load elapsed time                                             1407
1431595225 parse time elapsed                                                  8845225
 372226525 hard parse elapsed time                                             8628409
2821698184 sql execute elapsed time                                           38459729
1990024365 connection management call elapsed time                              333584
1824284809 failed parse elapsed time                                              2702
4125607023 failed parse (out of shared memory) elapsed time                          0
3138706091 hard parse (sharing criteria) elapsed time                            14255
 268357648 hard parse (bind mismatch) elapsed time                                2976
2643905994 PL/SQL execution elapsed time                                        364675
 290749718 inbound PL/SQL rpc elapsed time                                           0
1311180441 PL/SQL compilation elapsed time                                     1139058
 751169994 Java execution elapsed time                                               0
1159091985 repeated bind elapsed time                                            16139
2411117902 RMAN cpu time (backup/restore)                                            0
 
19 rows selected
 
SQL> select * from v$sess_time_model where rownum<=3;
 
       SID    STAT_ID STAT_NAME                                                             VALUE
---------- ---------- ---------------------------------------------------------------- ----------
         1 3649082374 DB time                                                                   0
         2 3649082374 DB time                                                                   0
         3 3649082374 DB time                                                                   0
        
        
Active Session History
1,儲存整個例項的會話的取樣資料
2,活動會話每秒進行取樣,儲存在sga中
3,連續到資料庫且不屬於空閒等待分類的會話屬於活動會話


--此字典源於v$active_session_history,提供file_id and block_id及sql相關的
SQL> desc dba_hist_active_sess_history;
Name                        Type         Nullable Default Comments
--------------------------- ------------ -------- ------- --------
SNAP_ID                     NUMBER                                
DBID                        NUMBER                                
INSTANCE_NUMBER             NUMBER                                
SAMPLE_ID                   NUMBER                                
SAMPLE_TIME                 TIMESTAMP(3)                          
SESSION_ID                  NUMBER                                
SESSION_SERIAL#             NUMBER       Y                        
SESSION_TYPE                VARCHAR2(10) Y                        
FLAGS                       NUMBER       Y                        
USER_ID                     NUMBER       Y                        
SQL_ID                      VARCHAR2(13) Y                        
IS_SQLID_CURRENT            VARCHAR2(1)  Y                        
SQL_CHILD_NUMBER            NUMBER       Y                        
SQL_OPCODE                  NUMBER       Y                        
SQL_OPNAME                  VARCHAR2(64) Y                        
FORCE_MATCHING_SIGNATURE    NUMBER       Y                        
TOP_LEVEL_SQL_ID            VARCHAR2(13) Y                        
TOP_LEVEL_SQL_OPCODE        NUMBER       Y                        
SQL_PLAN_HASH_VALUE         NUMBER       Y                        
SQL_PLAN_LINE_ID            NUMBER       Y                        
SQL_PLAN_OPERATION          VARCHAR2(64) Y                        
SQL_PLAN_OPTIONS            VARCHAR2(64) Y                        
SQL_EXEC_ID                 NUMBER       Y                        
SQL_EXEC_START              DATE         Y                        
PLSQL_ENTRY_OBJECT_ID       NUMBER       Y                        
PLSQL_ENTRY_SUBPROGRAM_ID   NUMBER       Y                        
PLSQL_OBJECT_ID             NUMBER       Y                        
PLSQL_SUBPROGRAM_ID         NUMBER       Y                        
QC_INSTANCE_ID              NUMBER       Y                        
QC_SESSION_ID               NUMBER       Y                        
QC_SESSION_SERIAL#          NUMBER       Y                        
EVENT                       VARCHAR2(64) Y                        
EVENT_ID                    NUMBER       Y                        
SEQ#                        NUMBER       Y                        
P1TEXT                      VARCHAR2(64) Y                        
P1                          NUMBER       Y                        
P2TEXT                      VARCHAR2(64) Y                        
P2                          NUMBER       Y                        
P3TEXT                      VARCHAR2(64) Y                        
P3                          NUMBER       Y                        
WAIT_CLASS                  VARCHAR2(64) Y                        
WAIT_CLASS_ID               NUMBER       Y                        
WAIT_TIME                   NUMBER       Y                        
SESSION_STATE               VARCHAR2(7)  Y                        
TIME_WAITED                 NUMBER       Y                        
BLOCKING_SESSION_STATUS     VARCHAR2(11) Y                        
BLOCKING_SESSION            NUMBER       Y                        
BLOCKING_SESSION_SERIAL#    NUMBER       Y                        
BLOCKING_INST_ID            NUMBER       Y                        
BLOCKING_HANGCHAIN_INFO     VARCHAR2(1)  Y                        
CURRENT_OBJ#                NUMBER       Y                        
CURRENT_FILE#               NUMBER       Y                        
CURRENT_BLOCK#              NUMBER       Y                        
CURRENT_ROW#                NUMBER       Y                        
TOP_LEVEL_CALL#             NUMBER       Y                        
TOP_LEVEL_CALL_NAME         VARCHAR2(64) Y                        
CONSUMER_GROUP_ID           NUMBER       Y                        
XID                         RAW(8)       Y                        
REMOTE_INSTANCE#            NUMBER       Y                        
TIME_MODEL                  NUMBER       Y                        
IN_CONNECTION_MGMT          VARCHAR2(1)  Y                        
IN_PARSE                    VARCHAR2(1)  Y                        
IN_HARD_PARSE               VARCHAR2(1)  Y                        
IN_SQL_EXECUTION            VARCHAR2(1)  Y                        
IN_PLSQL_EXECUTION          VARCHAR2(1)  Y                        
IN_PLSQL_RPC                VARCHAR2(1)  Y                        
IN_PLSQL_COMPILATION        VARCHAR2(1)  Y                        
IN_JAVA_EXECUTION           VARCHAR2(1)  Y                        
IN_BIND                     VARCHAR2(1)  Y                        
IN_CURSOR_CLOSE             VARCHAR2(1)  Y                        
IN_SEQUENCE_LOAD            VARCHAR2(1)  Y                        
CAPTURE_OVERHEAD            VARCHAR2(1)  Y                        
REPLAY_OVERHEAD             VARCHAR2(1)  Y                        
IS_CAPTURED                 VARCHAR2(1)  Y                        
IS_REPLAYED                 VARCHAR2(1)  Y                        
SERVICE_HASH                NUMBER       Y                        
PROGRAM                     VARCHAR2(64) Y                        
MODULE                      VARCHAR2(48) Y                        
ACTION                      VARCHAR2(32) Y                        
CLIENT_ID                   VARCHAR2(64) Y                        
MACHINE                     VARCHAR2(64) Y                        
PORT                        NUMBER       Y                        
ECID                        VARCHAR2(64) Y                        
TM_DELTA_TIME               NUMBER       Y                        
TM_DELTA_CPU_TIME           NUMBER       Y                        
TM_DELTA_DB_TIME            NUMBER       Y                        
DELTA_TIME                  NUMBER       Y                        
DELTA_READ_IO_REQUESTS      NUMBER       Y                        
DELTA_WRITE_IO_REQUESTS     NUMBER       Y                        
DELTA_READ_IO_BYTES         NUMBER       Y                        
DELTA_WRITE_IO_BYTES        NUMBER       Y                        
DELTA_INTERCONNECT_IO_BYTES NUMBER       Y                        
PGA_ALLOCATED               NUMBER       Y                        
TEMP_SPACE_ALLOCATED        NUMBER       Y   


--如下說明ash每隔一秒取樣一次
SQL> select sample_id,to_char(sample_time,'yyyymmdd hh24:mi:ss') from v$active_session_history where session_id=8 order by to_char(sample_time,'yyyymmdd hh24:mi:ss');
 
 SAMPLE_ID TO_CHAR(SAMPLE_TIME,'YYYYMMDDH
---------- ------------------------------
   1895159 20130310 17:55:07
   1905652 20130310 20:50:30
   1905653 20130310 20:50:31
   1905654 20130310 20:50:32
   1905655 20130310 20:50:33
   1905715 20130310 20:51:33
   1905716 20130310 20:51:34
   1905717 20130310 20:51:35
   1905718 20130310 20:51:36
   1905719 20130310 20:51:37
   1905720 20130310 20:51:38
   1905721 20130310 20:51:39
   1905722 20130310 20:51:40
   1905723 20130310 20:51:41
   1905724 20130310 20:51:42
   1905725 20130310 20:51:43
   1905726 20130310 20:51:44
   1905727 20130310 20:51:46
   1909324 20130310 21:51:53    
  
  
System and Session Statistics
 系統和會話統計可由v$sysstat and v$sesstat獲知
 
SQL> select * from v$sysstat where rownum<=10;
 
STATISTIC# NAME                                                                  CLASS      VALUE    STAT_ID
---------- ---------------------------------------------------------------- ---------- ---------- ----------
         0 OS CPU Qt wait time                                                       1          0  576270482
         1 logons cumulative                                                         1        204 2666645286
         2 logons current                                                            1         25 3080465522
         3 opened cursors cumulative                                                 1      20214   85052502
         4 opened cursors current                                                    1         35 2301954928
         5 user commits                                                              1        125  582481098
         6 user rollbacks                                                            1          0 3671147913
         7 user calls                                                                1       2028 2882015696
         8 recursive calls                                                           1     332570 2656001462
         9 recursive cpu usage                                                       1      93009 4009879262
 
10 rows selected
 
SQL> select * from v$sesstat where rownum<=10;
 
       SID STATISTIC#      VALUE
---------- ---------- ----------
         1          0          0
         1          1          1
         1          2          1
         1          3          0
         1          4          0
         1          5          0
         1          6          0
         1          7          2
         1          8          0
         1          9          0
 
10 rows selected
 


Operating System Statistics
1,即硬體和作業系統本身的統計資訊
2,可診斷記憶體,cpu,io相關元件是否資源充足
3,包含:
--cpu
CPU Statistics
 
--記憶體
Virtual Memory Statistics
 
--io
Disk I/O Statistics
 
--網路
Network Statistics

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/9240380/viewspace-755790/,如需轉載,請註明出處,否則將追究法律責任。

相關文章