oracle performance tuning效能優化學習系列(四)
CPU Statistics
1,獲取整個系統的cpu使用情況
2,獲取多處理器環境的每個獨立cpu的使用情況
3,大多os關於cpu usage用時在user space or mode and kernel space or mode(即使用者和核心模式)
4,當然還有一些額外的資訊體現，進一步分析cpu正在作什麼工作
5,在oracle執行的os中，以一個應用為例；系統活動基本執行在使用者模式下
6,為了處理資料庫請求的活動,比如排程，同步，io,記憶體管理,進執行緒分配和釋放，則執行於核心模式下
7,oracle建議對於一個充分利用cpu的系統，65-95% cpu執行於使用者模式
--示例：
---處於空閒和繁忙狀態的時間，注意：是累計;user_time為使用者模式;avg字首為平均空閒相關的時間；非累計;
--num_cpu_cores為cpu核數；實體記憶體大小;vm字首為交換產生頁出和頁入的大小
--調優原則：user_time/(user_time+sys_time) 位於65-95%之間,說明合理
-----------如果busy_time遠小於idle_time說明系統很空閒，否則說明系統很忙
-----------如果vm太多，說明交換活動太多，增加交換分割槽
SQL> select * from v$osstat;
STAT_NAME VALUE OSSTAT_ID COMMENTS CUMULATIVE
---------------------------------------------------------------- ---------- ---------- ---------------------------------------------------------------- ----------
NUM_CPUS 4 0 Number of active CPUs NO
IDLE_TIME 20361 1 Time (centi-secs) that CPUs have been in the idle state YES
BUSY_TIME 1368 2 Time (centi-secs) that CPUs have been in the busy state YES
USER_TIME 919 3 Time (centi-secs) spent in user code YES
SYS_TIME 449 4 Time (centi-secs) spent in the kernel YES
AVG_IDLE_TIME 5088 7 Average time (centi-secs) that CPUs have been in the idle state NO
AVG_BUSY_TIME 340 8 Average time (centi-secs) that CPUs have been in the busy state NO
AVG_USER_TIME 229 9 Average time (centi-secs) spent in user code NO
AVG_SYS_TIME 111 10 Average time (centi-secs) spent in the kernel NO
RSRC_MGR_CPU_WAIT_TIME 0 14 Time (centi-secs) processes spent in the runnable state waiting YES
NUM_CPU_CORES 2 16 Number of CPU cores NO
NUM_CPU_SOCKETS 1 17 Number of physical CPU sockets NO
PHYSICAL_MEMORY_BYTES 4196536320 1008 Physical memory size in bytes NO
VM_IN_BYTES 3.56224175 1009 Bytes paged in due to virtual memory swapping YES
VM_OUT_BYTES 1.84451431 1010 Bytes paged out due to virtual memory swapping YES
15 rows selected
--v$sysmetric_history儲存1小時的歷史資料，每1分鐘取樣一次；取樣各個度量的指標資料
SQL> select max(to_char(begin_time,'yyyymmdd hh24:mi:ss')),min(to_char(begin_time,'yyyymmdd hh24:mi:ss')) from v$sysmetric_history;
MAX(TO_CHAR(BEGIN_TIME,'YYYYMM MIN(TO_CHAR(BEGIN_TIME,'YYYYMM
------------------------------ ------------------------------
20130311 13:07:56 20130311 12:06:56
--度量分組
SQL> select count(*) from v$metricgroup;
COUNT(*)
----------
14
--度量隸屬於上述的度量分組
SQL> select count(distinct metric_name) from v$metricname;
COUNT(DISTINCTMETRIC_NAME)
--------------------------
220
--度量歷史表
SQL> select count(distinct metric_name) from v$sysmetric_history;
COUNT(DISTINCTMETRIC_NAME)
--------------------------
158
--查詢一個時間範圍主機cpu的使用率,如下說明cpu很idle
SQL> select * from v$sysmetric_history uh where uh.METRIC_NAME like '%Host CPU Utilization (%)%';
BEGIN_TIME END_TIME INTSIZE_CSEC GROUP_ID METRIC_ID METRIC_NAME VALUE METRIC_UNIT
----------- ----------- ------------ ---------- ---------- ---------------------------------------------------------------- ---------- ----------------------------------------------------------------
2013/3/11 1 2013/3/11 1 5983 2 2057 Host CPU Utilization (%) 3.71051312 % Busy/(Idle+Busy)
2013/3/11 1 2013/3/11 1 5983 2 2057 Host CPU Utilization (%) 2.47377878 % Busy/(Idle+Busy)
2013/3/11 1 2013/3/11 1 5982 2 2057 Host CPU Utilization (%) 3.60650257 % Busy/(Idle+Busy)
2013/3/11 1 2013/3/11 1 5983 2 2057 Host CPU Utilization (%) 2.61167523 % Busy/(Idle+Busy)
2013/3/11 1 2013/3/11 1 5983 2 2057 Host CPU Utilization (%) 3.70215610 % Busy/(Idle+Busy)
2013/3/11 1 2013/3/11 1 6084 2 2057 Host CPU Utilization (%) 2.27235371 % Busy/(Idle+Busy)
2013/3/11 1 2013/3/11 1 5982 2 2057 Host CPU Utilization (%) 4.87672377 % Busy/(Idle+Busy)
2013/3/11 1 2013/3/11 1 5983 2 2057 Host CPU Utilization (%) 1.96381565 % Busy/(Idle+Busy)
2013/3/11 1 2013/3/11 1 5983 2 2057 Host CPU Utilization (%) 3.53919438 % Busy/(Idle+Busy)
2013/3/11 1 2013/3/11 1 5982 2 2057 Host CPU Utilization (%) 2.71591526 % Busy/(Idle+Busy)
2013/3/11 1 2013/3/11 1 6064 2 2057 Host CPU Utilization (%) 4.06926406 % Busy/(Idle+Busy)
2013/3/11 1 2013/3/11 1 5980 2 2057 Host CPU Utilization (%) 3.23592123 % Busy/(Idle+Busy)
2013/3/11 1 2013/3/11 1 5960 2 2057 Host CPU Utilization (%) 6.91620907 % Busy/(Idle+Busy)
2013/3/11 1 2013/3/11 1 5983 2 2057 Host CPU Utilization (%) 4.48559842 % Busy/(Idle+Busy)
2013/3/11 1 2013/3/11 1 6084 2 2057 Host CPU Utilization (%) 2.35874255 % Busy/(Idle+Busy)
2013/3/11 1 2013/3/11 1 5983 2 2057 Host CPU Utilization (%) 2.17209690 % Busy/(Idle+Busy)
2013/3/11 1 2013/3/11 1 5982 2 2057 Host CPU Utilization (%) 2.41190486 % Busy/(Idle+Busy)
2013/3/11 1 2013/3/11 1 5983 2 2057 Host CPU Utilization (%) 0.51770207 % Busy/(Idle+Busy)
2013/3/11 1 2013/3/11 1 5983 2 2057 Host CPU Utilization (%) 0.48888517 % Busy/(Idle+Busy)
2013/3/11 1 2013/3/11 1 5982 2 2057 Host CPU Utilization (%) 0.45587620 % Busy/(Idle+Busy)
BEGIN_TIME END_TIME INTSIZE_CSEC GROUP_ID METRIC_ID METRIC_NAME
--如下查詢oracle使用的cpu比率,用如上和如下可以判斷到底是什麼原因導致cpu出現問題；即到底是主機活動問題或是oracle導致
SQL> select * from v$sys_time_model;
STAT_ID STAT_NAME VALUE
---------- ---------------------------------------------------------------- ----------
3649082374 DB time 5365952069
2748282437 DB CPU 12230459
4157170894 background elapsed time 166631594
2451517896 background cpu time 9812454
4127043053 sequence load elapsed time 66275
1431595225 parse time elapsed 18772863
372226525 hard parse elapsed time 18220556
2821698184 sql execute elapsed time 50648806
1990024365 connection management call elapsed time 205212
1824284809 failed parse elapsed time 160366
4125607023 failed parse (out of shared memory) elapsed time 0
3138706091 hard parse (sharing criteria) elapsed time 177374
268357648 hard parse (bind mismatch) elapsed time 5091
2643905994 PL/SQL execution elapsed time 3313276
290749718 inbound PL/SQL rpc elapsed time 0
1311180441 PL/SQL compilation elapsed time 4685801
751169994 Java execution elapsed time 0
1159091985 repeated bind elapsed time 27650
2411117902 RMAN cpu time (backup/restore) 0
19 rows selected
--查詢資料庫cpu time比率，即cpu/db_time;而db_time為cpu time+等待非空閒事件的等待時間
SQL> select * from v$sysmetric_history uh where uh.METRIC_NAME like '%Database CPU Time Ratio%';
BEGIN_TIME END_TIME INTSIZE_CSEC GROUP_ID METRIC_ID METRIC_NAME VALUE METRIC_UNIT
----------- ----------- ------------ ---------- ---------- ---------------------------------------------------------------- ---------- ----------------------------------------------------------------
2013/3/11 1 2013/3/11 1 5983 2 2108 Database CPU Time Ratio 132.944725 % Cpu/DB_Time
2013/3/11 1 2013/3/11 1 5983 2 2108 Database CPU Time Ratio 0 % Cpu/DB_Time
2013/3/11 1 2013/3/11 1 5982 2 2108 Database CPU Time Ratio 64.9215531 % Cpu/DB_Time
2013/3/11 1 2013/3/11 1 5981 2 2108 Database CPU Time Ratio 0 % Cpu/DB_Time
2013/3/11 1 2013/3/11 1 6084 2 2108 Database CPU Time Ratio 78.2051564 % Cpu/DB_Time
2013/3/11 1 2013/3/11 1 5983 2 2108 Database CPU Time Ratio 0 % Cpu/DB_Time
2013/3/11 1 2013/3/11 1 5983 2 2108 Database CPU Time Ratio 63.0991384 % Cpu/DB_Time
2013/3/11 1 2013/3/11 1 5982 2 2108 Database CPU Time Ratio 181.930029 % Cpu/DB_Time
2013/3/11 1 2013/3/11 1 5983 2 2108 Database CPU Time Ratio 52.3636363 % Cpu/DB_Time
2013/3/11 1 2013/3/11 1 5983 2 2108 Database CPU Time Ratio 80.7955251 % Cpu/DB_Time
2013/3/11 1 2013/3/11 1 6084 2 2108 Database CPU Time Ratio 126.985603 % Cpu/DB_Time
2013/3/11 1 2013/3/11 1 5982 2 2108 Database CPU Time Ratio 45.1412449 % Cpu/DB_Time
2013/3/11 1 2013/3/11 1 5983 2 2108 Database CPU Time Ratio 0 % Cpu/DB_Time
2013/3/11 1 2013/3/11 1 5983 2 2108 Database CPU Time Ratio 0 % Cpu/DB_Time
2013/3/11 1 2013/3/11 1 5982 2 2108 Database CPU Time Ratio 0 % Cpu/DB_Time
2013/3/11 1 2013/3/11 1 6064 2 2108 Database CPU Time Ratio 0 % Cpu/DB_Time
2013/3/11 1 2013/3/11 1 5980 2 2108 Database CPU Time Ratio 0 % Cpu/DB_Time
2013/3/11 1 2013/3/11 1 5960 2 2108 Database CPU Time Ratio 0 % Cpu/DB_Time
2013/3/11 1 2013/3/11 1 5983 2 2108 Database CPU Time Ratio 0 % Cpu/DB_Time
2013/3/11 1 2013/3/11 1 6084 2 2108 Database CPU Time Ratio 0 % Cpu/DB_Time
BEGIN_TIME END_TIME INTSIZE_CSEC GROUP_ID METRIC_ID METRIC_NAME VALUE METRIC_UNIT
----------- ----------- ------------ ---------- ---------- ---------------------------------------------------------------- ---------- ----------------------------------------------------------------
2013/3/11 1 2013/3/11 1 5983 2 2108 Database CPU Time Ratio 60.2107375 % Cpu/DB_Time
2013/3/11 1 2013/3/11 1 5982 2 2108 Database CPU Time Ratio 0 % Cpu/DB_Time
2013/3/11 1 2013/3/11 1 5983 2 2108 Database CPU Time Ratio 0 % Cpu/DB_Time
2013/3/11 1 2013/3/11 1 5983 2 2108 Database CPU Time Ratio 0 % Cpu/DB_Time
2013/3/11 1 2013/3/11 1 5982 2 2108 Database CPU Time Ratio 0 % Cpu/DB_Time
2013/3/11 1 2013/3/11 1 6084 2 2108 Database CPU Time Ratio 0 % Cpu/DB_Time
2013/3/11 1 2013/3/11 1 5983 2 2108 Database CPU Time Ratio 0 % Cpu/DB_Time
2013/3/11 1 2013/3/11 1 5982 2 2108 Database CPU Time Ratio 83.6541864 % Cpu/DB_Time
2013/3/11 1 2013/3/11 1 5983 2 2108 Database CPU Time Ratio 0 % Cpu/DB_Time
2013/3/11 1 2013/3/11 1 5983 2 2108 Database CPU Time Ratio 0 % Cpu/DB_Time
2013/3/11 1 2013/3/11 1 5982 2 2108 Database CPU Time Ratio 153.001176 % Cpu/DB_Time
2013/3/11 1 2013/3/11 1 6084 2 2108 Database CPU Time Ratio 0 % Cpu/DB_Time
2013/3/11 1 2013/3/11 1 5983 2 2108 Database CPU Time Ratio 0 % Cpu/DB_Time
2013/3/11 1 2013/3/11 1 5983 2 2108 Database CPU Time Ratio 0 % Cpu/DB_Time
2013/3/11 1 2013/3/11 1 5982 2 2108 Database CPU Time Ratio 0 % Cpu/DB_Time
2013/3/11 1 2013/3/11 1 5983 2 2108 Database CPU Time Ratio 0 % Cpu/DB_Time
2013/3/11 1 2013/3/11 1 6084 2 2108 Database CPU Time Ratio 90.2229548 % Cpu/DB_Time
2013/3/11 1 2013/3/11 1 5983 2 2108 Database CPU Time Ratio 0 % Cpu/DB_Time
2013/3/11 1 2013/3/11 1 5982 2 2108 Database CPU Time Ratio 0 % Cpu/DB_Time
2013/3/11 1 2013/3/11 1 5983 2 2108 Database CPU Time Ratio 0 % Cpu/DB_Time
2013/3/11 1 2013/3/11 1 5983 2 2108 Database CPU Time Ratio 151.883945 % Cpu/DB_Time
BEGIN_TIME END_TIME INTSIZE_CSEC GROUP_ID METRIC_ID METRIC_NAME VALUE METRIC_UNIT
----------- ----------- ------------ ---------- ---------- ---------------------------------------------------------------- ---------- ----------------------------------------------------------------
2013/3/11 1 2013/3/11 1 5982 2 2108 Database CPU Time Ratio 0 % Cpu/DB_Time
2013/3/11 1 2013/3/11 1 6084 2 2108 Database CPU Time Ratio 0 % Cpu/DB_Time
2013/3/11 1 2013/3/11 1 5983 2 2108 Database CPU Time Ratio 0 % Cpu/DB_Time
2013/3/11 1 2013/3/11 1 5983 2 2108 Database CPU Time Ratio 0 % Cpu/DB_Time
2013/3/11 1 2013/3/11 1 5982 2 2108 Database CPU Time Ratio 0 % Cpu/DB_Time
2013/3/11 1 2013/3/11 1 5983 2 2108 Database CPU Time Ratio 0 % Cpu/DB_Time
2013/3/11 1 2013/3/11 1 5982 2 2108 Database CPU Time Ratio 0 % Cpu/DB_Time
2013/3/11 1 2013/3/11 1 6084 2 2108 Database CPU Time Ratio 0 % Cpu/DB_Time
2013/3/11 1 2013/3/11 1 5983 2 2108 Database CPU Time Ratio 0 % Cpu/DB_Time
2013/3/11 1 2013/3/11 1 5983 2 2108 Database CPU Time Ratio 129.310344 % Cpu/DB_Time
2013/3/11 1 2013/3/11 1 5982 2 2108 Database CPU Time Ratio 0 % Cpu/DB_Time
2013/3/11 1 2013/3/11 1 5983 2 2108 Database CPU Time Ratio 0 % Cpu/DB_Time
2013/3/11 1 2013/3/11 1 5983 2 2108 Database CPU Time Ratio 0 % Cpu/DB_Time
2013/3/11 1 2013/3/11 1 6084 2 2108 Database CPU Time Ratio 0 % Cpu/DB_Time
2013/3/11 1 2013/3/11 1 5982 2 2108 Database CPU Time Ratio 0 % Cpu/DB_Time
2013/3/11 1 2013/3/11 1 5983 2 2108 Database CPU Time Ratio 0 % Cpu/DB_Time
2013/3/11 1 2013/3/11 1 5983 2 2108 Database CPU Time Ratio 0 % Cpu/DB_Time
2013/3/11 1 2013/3/11 1 5982 2 2108 Database CPU Time Ratio 0 % Cpu/DB_Time
2013/3/11 1 2013/3/11 1 5983 2 2108 Database CPU Time Ratio 0 % Cpu/DB_Time
2013/3/11 1 2013/3/11 1 6084 2 2108 Database CPU Time Ratio 134.401654 % Cpu/DB_Time
2013/3/11 1 2013/3/11 1 1521 3 2108 Database CPU Time Ratio 0 % Cpu/DB_Time
BEGIN_TIME END_TIME INTSIZE_CSEC GROUP_ID METRIC_ID METRIC_NAME VALUE METRIC_UNIT
----------- ----------- ------------ ---------- ---------- ---------------------------------------------------------------- ---------- ----------------------------------------------------------------
2013/3/11 1 2013/3/11 1 1521 3 2108 Database CPU Time Ratio 0 % Cpu/DB_Time
2013/3/11 1 2013/3/11 1 1521 3 2108 Database CPU Time Ratio 0 % Cpu/DB_Time
2013/3/11 1 2013/3/11 1 1521 3 2108 Database CPU Time Ratio 54.6831183 % Cpu/DB_Time
2013/3/11 1 2013/3/11 1 1420 3 2108 Database CPU Time Ratio 207.013236 % Cpu/DB_Time
2013/3/11 1 2013/3/11 1 1521 3 2108 Database CPU Time Ratio 0 % Cpu/DB_Time
2013/3/11 1 2013/3/11 1 1521 3 2108 Database CPU Time Ratio 0 % Cpu/DB_Time
2013/3/11 1 2013/3/11 1 1521 3 2108 Database CPU Time Ratio 0 % Cpu/DB_Time
2013/3/11 1 2013/3/11 1 1420 3 2108 Database CPU Time Ratio 0 % Cpu/DB_Time
2013/3/11 1 2013/3/11 1 1521 3 2108 Database CPU Time Ratio 0 % Cpu/DB_Time
2013/3/11 1 2013/3/11 1 1521 3 2108 Database CPU Time Ratio 0 % Cpu/DB_Time
2013/3/11 1 2013/3/11 1 1521 3 2108 Database CPU Time Ratio 66.3886288 % Cpu/DB_Time
2013/3/11 1 2013/3/11 1 1521 3 2108 Database CPU Time Ratio 0 % Cpu/DB_Time
74 rows selected
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/9240380/viewspace-755734/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 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 Tuning 總結 1優化Oracle
- 【OCM】Oracle Database 10g: Performance Tuning（四）OracleDatabaseORM
- 效能優化 - Oracle Tuning 總結 3 優化統計優化Oracle
- oracle 學習總結（效能優化）Oracle優化
- Oracle效能優化-SQL優化(案例四)Oracle優化SQL
- oracle 學習筆記---效能優化學習(1)Oracle筆記優化
- 效能優化 - Oracle Tuning 總結 2-2優化Oracle
- Oracle效能優化視訊學習筆記－效能優化概念(一)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學習筆記--效能最佳化四Oracle筆記
- 效能優化篇 - Performance（工具 & api）優化ORMAPI
- 效能優化 - Oracle Tuning 總結 2-1 Statspack優化Oracle
- ORACLE SQL效能優化系列 (一)OracleSQL優化
- oracle.Performance.Tuning筆記OracleORM筆記
- Oracle Advanced Performance Tuning ScriptsOracleORM
- Oracle -- Common Performance Tuning IssuesOracleORM
- Oracle SQL效能優化系列介紹OracleSQL優化
- Oracle Advanced Performance Tuning Scripts(轉)OracleORM
- ORACLE SQL效能最佳化系列 (四) (轉)OracleSQL
- Oracle學習系列—資料庫優化—Sort OperationOracle資料庫優化
- Oracle學習系列—資料庫優化—Statistics SummaryOracle資料庫優化
- Oracle學習系列—資料庫優化—Collect StatisticsOracle資料庫優化