oracle performance tuning效能優化學習系列(三)_補二
--說明是前後端所有的會話統計
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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- oracle performance tuning效能優化學習系列(三)_補一OracleORM優化
- oracle performance tuning效能優化學習系列(四)_補OracleORM優化
- oracle performance tuning效能優化學習系列(二)OracleORM優化
- oracle performance tuning效能優化學習系列(三)OracleORM優化
- oracle performance tuning效能優化學習系列(五)OracleORM優化
- oracle performance tuning效能優化學習系列(四)OracleORM優化
- oracle performance tuning效能優化學習系列(一)OracleORM優化
- Oracle效能優化視訊學習筆記－效能優化概念(二)Oracle優化筆記
- Oracle學習系列—資料庫優化—效能優化工具Oracle資料庫優化
- 效能優化 - Oracle Tuning 總結 1優化Oracle
- 效能優化 - Oracle Tuning 總結 3 優化統計優化Oracle
- 《java學習三》jvm效能優化-------調優JavaJVM優化
- 【OCM】Oracle Database 10g: Performance Tuning（二）OracleDatabaseORM
- 【OCM】Oracle Database 10g: Performance Tuning（三）OracleDatabaseORM
- oracle 學習總結（效能優化）Oracle優化
- oracle 學習筆記---效能優化學習(1)Oracle筆記優化
- 效能優化 - Oracle Tuning 總結 2-2優化Oracle
- Oracle效能優化視訊學習筆記－效能優化概念(一)Oracle優化筆記
- ORACLE學習筆記--效能優化FAQ。Oracle筆記優化
- oracle 學習筆記---效能優化（1）Oracle筆記優化
- oracle 學習筆記---效能優化（2）Oracle筆記優化
- oracle 學習筆記---效能優化（3）Oracle筆記優化
- oracle 學習筆記---效能優化（4）Oracle筆記優化
- oracle 學習筆記---效能優化（5）Oracle筆記優化
- oracle 學習筆記---效能優化（6）Oracle筆記優化
- oracle 學習筆記---效能優化（7）Oracle筆記優化
- Oracle效能優化-SQL優化(案例二)Oracle優化SQL
- 《java學習三》jvm效能優化------jconsulJavaJVM優化
- 效能優化篇 - Performance（工具 & api）優化ORMAPI
- Oracle效能優化-SQL優化(案例三)Oracle優化SQL
- 效能優化 - Oracle Tuning 總結 2-1 Statspack優化Oracle
- ORACLE SQL效能優化系列 (一)OracleSQL優化
- oracle.Performance.Tuning筆記OracleORM筆記
- Oracle Advanced Performance Tuning ScriptsOracleORM
- Oracle -- Common Performance Tuning IssuesOracleORM
- 《java學習二》jvm效能優化-----認識jvmJavaJVM優化
- ORACLE學習筆記--效能最佳化二Oracle筆記
- ORACLE學習筆記--效能最佳化三Oracle筆記