重庆思庄Oracle、Redhat认证学习论坛

标题: Oracle 小工具--oratop [打印本页]

作者: mahan 时间: 7 天前
标题: Oracle 小工具--oratop
使用方法
[oracle@rac1 ~]$ $ORACLE_HOME/suptools/oratop/oratop / as sysdba
交互按键
Interactive Keys: [default]                                                       --交互键：[默认]
f : toggle between [standard] & detailed format.                         --在[标准]和详细格式之间切换。
r : toggle between [Cumulative (C)] & Real-Time (RT) (section 3)          --在[累积 (C)]和实时(RT)之间切换
s : switch to SQL mode (section 4)                                        --切换到SQL模式
p : switch to [process] mode (section 4)                                  --切换到[process]模式
k : toggle between [EVENT/LATCH] & object FILE#:BLOCK# (process section 4)  --在[EVENT/LATCH]和对象FILE#:BLOCK#之间切换
m : Toggle between [USERNAME/PROGRAM] & MODULE/ACTION (process section 4) --在[USERNAME/PROGRAM]和MODULE/ACTION之间切换
t : tablespace information                                                 --表空间信息
a : ASM diskgroup information                                              --ASM磁盘组信息
x : basic SQL plan table (requires sql_id input)                         --基本SQL计划表(需要输入sql_id)
i : refresh interval, requires value in seconds [5s]                      --刷新间隔，需要以秒为单位的值
q : quit/ exit program                                                    --退出/退出程序
按键示例
1. 默认情况下

Oracle 19c - orc 09:11:21 up: 7.9h,  2 ins,  103 sn, 1 us, 3.1G sga,  4.6k%db
ID %CPU %DCP LOAD  AAS  ASC  ASI  ASW  IDL  MBPS  %FR  PGA UTPS  RT/X DCTR DWTR
1 79.0 44.8 12.2 48.6 0 0 52 0 0.8 11 1.3G  179  256m 0 99
2 78.0 41.5 11.3 44.2 0 0 48 3 0.7 7 988M  159  248m 0 99

EVENT (C)                      TOT WAITS TIME(s)  AVG_MS  PCT WAIT_CLASS
log file sync                   28922800 1968489 53.5 50       Commit
resmgr:cpu quantum             23658345 1606959 45.5 41    Scheduler
enq: HW - contention                71046 170233  1278.5 4 Configuration
db file sequential read          10889918 104488    5.4 3    User I/O
buffer busy waits                871152    67396 65.1 2 Concurrency

ID SID    SPID USR PROG S  PGA SQLID/BLOCKER OPN  E/T STA STE EVENT/*LA  W/T
2 139 30297 SOE java D 3.4M 2pb2tzp63af86 INS 1.0s ACT WAI gc curren 1.0s
2 141 30305 SOE java D 3.2M       2:134 INS 1.0s ACT WAI gc buffer 985m
1 101 25695 SOE java D 3.1M 2pb2tzp63af86 INS 1.0s ACT WAI gc buffer 966m
2 120 30242 SOE java D 3.2M 2pb2tzp63af86 INS 1.0s ACT WAI gc buffer 966m
1 118 25625 SOE java D 3.2M 2pb2tzp63af86 INS 1.0s ACT WAI gc buffer 966m
1 121 25645 SOE java D 3.1M 2pb2tzp63af86 INS 1.0s ACT WAI gc buffer 965m
1 176 25635 SOE java D 3.1M 2pb2tzp63af86 INS 1.0s ACT WAI gc buffer 965m
1 135 25666 SOE java D 3.2M 2pb2tzp63af86 INS 1.0s ACT WAI gc buffer 965m
2. 交互键 f

Oracle 19c - Primary orcl 09:12:16 up: 7.9h,  2 ins,  103 sn, 1 us, 3.1G sga, 0% fra, 3 er, 1 pdb,          4.7k%db
ID CPU  %CPU %DCP LOAD  AAS  ASC  ASI  ASW  IDL  ASP  LAT  MBPS IOPS  R/S  W/S  LIO  GCPS %FR  PGA TEMP UTPS  UCPS  RT/X DCTR DWTR
1 1  82.9 45.9 11.3 49.0 0 0 50 2 0 952u 0.9 66 5 61 2.5k 266  11 1.3G 0  185  1.2k  275m 0 99
2 1  80.1 45.1 11.9 44.3 4 0 35 13 0 912u 0.8 58 4 54 2.5k 287 7 989M 0  174  1.1k  241m 0 98

EVENT (C)                                                       TOTAL WAITS TIME(s)  AVG_MS  PCT                   WAIT_CLASS
log file sync                                                       28940531 1969160 53.6 50                      Commit
resmgr:cpu quantum                                                 23674380 1607356 45.5 41                   Scheduler
enq: HW - contention                                                 71057 170234  1278.1 4                Configuration
db file sequential read                                           10889918 104488    5.4 3                   User I/O
buffer busy waits                                                    875502    67616 64.8 2                Concurrency

ID SID    SPID USERNAME  PROGRAM SRV  SERVICE  PGA  SQLID/BLOCKER OPN  E/T  STA  STE  WAIT_CLASS  EVENT/*LATCH          W/T
2 116 30240 SOE    javaw.exe  DED  pdb    3.3M  2pb2tzp63af86 INS 0  ACT  WAI  Cluster    gc buffer busy releas  39m
2 142 30285 SOE    javaw.exe  DED  pdb    3.3M  2pb2tzp63af86 INS 0  ACT  WAI  Cluster    gc buffer busy releas  39m
2 122 30246 SOE    javaw.exe  DED  pdb    3.2M  2pb2tzp63af86 INS 0  ACT  WAI  Cluster    gc buffer busy releas  39m
2 123 30248 SOE    javaw.exe  DED  pdb    3.2M  2pb2tzp63af86 INS 0  ACT  WAI  Cluster    gc buffer busy re leas  39m
2 124 30250 SOE    javaw.exe  DED  pdb    3.2M  2pb2tzp63af86 INS 0  ACT  WAI  Cluster    gc buffer busy releas  39m
2 125 30252 SOE    javaw.exe  DED  pdb    3.2M  2pb2tzp63af86 INS 0  ACT  WAI  Cluster    gc buffer busy releas  38m
3. 交互键 s

Oracle 19c - orc 09:19:21 up: 8.0h,  2 ins,  180 sn, 1 us, 3.1G sga,  4.5k%db
ID %CPU %DCP LOAD  AAS  ASC  ASI  ASW  IDL  MBPS  %FR  PGA UTPS  RT/X DCTR DWTR
1 92.6 47.5 17.3 47.7 0 0 52 36 0.8 10 1.4G  230  268m 1 99
2 88.5 47.1 13.3 41.9 0 0 48 44 0.8 7 1.4G  193  246m 1 98

EVENT (C)                      TOT WAITS TIME(s)  AVG_MS  PCT WAIT_CLASS
log file sync                   29102365 1975604 53.9 50       Commit
resmgr:cpu quantum             23808085 1610583 45.8 41    Scheduler
enq: HW - contention                71388 170381  1317.4 4 Configuration
db file sequential read          10889938 104488    5.4 3    User I/O
buffer busy waits                909379    69411 64.3 2 Concurrency

ID  CID  USER  SQL_ID       SQL_TEXT       X  ELAP  EXEC  ROWS  BH%  LOAD
1 3  SOE 2pb2tzp63af86 insert into stre  S  385m  117k    1  100 52
2 3  SOE 2pb2tzp63af86 insert into stre  S  384m  106k    1  100 34
2 3  SOE 1rr5wb4rg3qn5 delete from stre  S 54m 71k    0  100    1
2 3  SOE 78thn3afd414y update stressTes  S 15m  212k    0  100    5
2 3  SOE 60v4b09jfjz2m select * from st  S 13m  284k    0  100    8
4. 交互键 f + s

Oracle 19c - Primary orcl 09:20:21 up: 8.0h,  2 ins,  180 sn, 1 us, 3.1G sga, 0% fra, 3 er, 1 pdb,          4.6k%db
ID CPU  %CPU %DCP LOAD  AAS  ASC  ASI  ASW  IDL  ASP  LAT  MBPS IOPS  R/S  W/S  LIO  GCPS %FR  PGA TEMP UTPS  UCPS  RT/X DCTR DWTR
2 1  90.8 44.9 13.2 44.2 0 0 48 44 0 444u 0.8 66 4 63 3.2k 289 7 1.4G 0  191  1.2k  241m 1 98
1 1  90.6 49.1 20.4 48.0 0 0 52 36 0 604u 0.9 66 4 61 3.6k 297  10 1.5G 0  216  1.4k  239m 1 98

EVENT (C)                                                       TOTAL WAITS TIME(s)  AVG_MS  PCT                   WAIT_CLASS
log file sync                                                       29123662 1976485 53.9 50                      Commit
resmgr:cpu quantum                                                 23821723 1610832 45.8 41                   Scheduler
enq: HW - contention                                                 71393 170381  1317.1 4                Configuration
db file sequential read                                           10889939 104488    5.4 3                   User I/O
buffer busy waits                                                    913890    69704 64.7 2                Concurrency

ID  CID  USERNAME  MODULE ACTION  SQL_ID       SQL_TEXT       X  ELAP  CPUT  IOWT  WAIT  EXEC  ROWS  BUFG  DISK  BH%  LOAD
1 3  SOE    javaw.e       2pb2tzp63af86 insert into stre  S  387m  928u 28u  363m  121k    1 13    0  100 51
2 3  SOE    javaw.e       2pb2tzp63af86 insert into stre  S  385m  935u 49u  361m  110k    1 13    0  100 43
2 3  SOE    javaw.e       1rr5wb4rg3qn5 delete from stre  S 55m  664u 8u 43m 73k    0    6    0  100    4
1 3  SOE    javaw.e       78thn3afd414y update stressTes  S 13m  290u 5u  7.8m  243k    0    3    0  100    1
5. 交互键 k

Oracle 19c - orc 09:23:17 up: 8.1h,  2 ins,  180 sn, 1 us, 3.1G sga,  4.7k%db
ID %CPU %DCP LOAD  AAS  ASC  ASI  ASW  IDL  MBPS  %FR  PGA UTPS  RT/X DCTR DWTR
1 78.9 49.3 18.5 50.6 0 0 52 36 0.9 10 1.4G  173  234m 0 98
2 82.5 47.5 12.8 43.1 0 0 48 44 0.9 7 1.4G  175  228m 1 98

EVENT (C)                      TOT WAITS TIME(s)  AVG_MS  PCT WAIT_CLASS
log file sync                   29187908 1979107 53.8 50       Commit
resmgr:cpu quantum             23869310 1611846 45.7 41    Scheduler
enq: HW - contention                71421 170384  1315.9 4 Configuration
db file sequential read          10889967 104488    5.4 3    User I/O
buffer busy waits                927481    70454 65.1 2 Concurrency

ID SID    SPID USR PROG S  PGA SQLID/BLOCKER OPN  E/T STA STE FILE#:BLK  W/T
1 139 25707 SOE java D 3.2M 60v4b09jfjz2m SEL 1.0s ACT WAI 17:776968 552m
2 106 30225 SOE java D 3.5M 2pb2tzp63af86 INS 0 ACT WAI 17:776968 551m
2 121 30262 SOE java D 3.2M 2pb2tzp63af86 INS 0 ACT WAI 17:776968 551m
2 128 30258 SOE java D 3.3M 2pb2tzp63af86 INS 0 ACT WAI 17:776968 551m
1 93 25704 SOE java D 3.2M 2pb2tzp63af86 INS 1.0s ACT WAI 17:776968 538m
1 110 25664 SOE java D 3.2M 78thn3afd414y UPD 1.0s ACT WAI 17:776968 536m
1 191 25686 SOE java D 3.1M 2pb2tzp63af86 INS 1.0s ACT WAI 17:776968 533m
1 239 25688 SOE java D 3.2M 60v4b09jfjz2m SEL 1.0s ACT WAI 17:776968 532m
6. 交互键 f + k

Oracle 19c - Primary orcl 09:31:51 up: 8.2h,  2 ins,  180 sn, 1 us, 3.1G sga, 0% fra, 3 er, 1 pdb,          4.5k%db
ID CPU  %CPU %DCP LOAD  AAS  ASC  ASI  ASW  IDL  ASP  LAT  MBPS IOPS  R/S  W/S  LIO  GCPS %FR  PGA TEMP UTPS  UCPS  RT/X DCTR DWTR
1 1  84.7 42.8 16.2 47.7 0 0 52 36 0 581u 0.8 71 4 66 3.3k 252  10 1.5G 0  188  1.2k  277m 0 99
2 1  92.3 40.7 10.9 43.3 0 0 48 44 0 386u 0.8 67 4 63 3.6k 256 6 1.4G 0  224  1.4k  273m 1 99

EVENT (C)                                                       TOTAL WAITS TIME(s)  AVG_MS  PCT                   WAIT_CLASS
log file sync                                                       29379009 1986453 53.8 50                      Commit
resmgr:cpu quantum                                                 24039382 1616110 45.6 41                   Scheduler
enq: HW - contention                                                 71572 170414  1313.2 4                Configuration
db file sequential read                                           10890139 104491    5.4 3                   User I/O
buffer busy waits                                                    966306    73036 65.4 2                Concurrency

ID SID    SPID USERNAME  PROGRAM SRV  SERVICE  PGA  SQLID/BLOCKER OPN  E/T  STA  STE  WAIT_CLASS       FILE#:BLOCK# W/T
1 93 25704 SOE    javaw.exe  DED  pdb    3.3M  2pb2tzp63af86 INS 1.0s  ACT  WAI  Cluster             17:782443  835m
1 119 25649 SOE    javaw.exe  DED  pdb    3.2M  1rr5wb4rg3qn5 DEL 1.0s  ACT  WAI  Cluster             17:782443  833m
1 126 25721 SOE    javaw.exe  DED  pdb    3.3M  2pb2tzp63af86 INS 1.0s  ACT  WAI  Cluster             17:782443  822m
1 221 25717 SOE    javaw.exe  DED  pdb    3.1M  2pb2tzp63af86 INS 1.0s  ACT  WAI  Cluster             17:782443  803m
1 163 25623 SOE    javaw.exe  DED  pdb    3.2M  2pb2tzp63af86 INS 1.0s  ACT  WAI  Cluster             17:782443  800m
2 143 30283 SOE    javaw.exe  DED  pdb    3.2M  2pb2tzp63af86 INS 1.0s  ACT  WAI  Concurrenc          17:782443  799m
2 111 30223 SOE    javaw.exe  DED  pdb    3.7M  2pb2tzp63af86 INS 0  ACT  WAI  Concurrenc          17:782443  799m
2 151 30299 SOE    javaw.exe  DED  pdb    3.3M  2pb2tzp63af86 INS 0  ACT  WAI  Cluster             17:782443  799m
1 101 25695 SOE    javaw.exe  DED  pdb    3.2M  2pb2tzp63af86 INS 1.0s  ACT  WAI  Cluster             17:782443  797m
2 130 30268 SOE    javaw.exe  DED  pdb    3.2M  2pb2tzp63af86 INS 0  ACT  WAI  Cluster             17:782443  797m
7. 交互键 m

Oracle 19c - orc 09:24:37 up: 8.1h,  2 ins,  180 sn, 1 us, 3.1G sga,  4.7k%db
ID %CPU %DCP LOAD  AAS  ASC  ASI  ASW  IDL  MBPS  %FR  PGA UTPS  RT/X DCTR DWTR
1 88.6 47.4 15.3 49.5 0 0 52 36 0.8 10 1.4G  214  254m 1 99
2 91.4 46.0 21.0 44.5 0 0 48 44 0.9 7 1.4G  210  228m 1 98

EVENT (C)                      TOT WAITS TIME(s)  AVG_MS  PCT WAIT_CLASS
log file sync                   29219953 1980348 53.8 50       Commit
resmgr:cpu quantum             23893637 1612357 45.5 41    Scheduler
enq: HW - contention                71472 170394  1314.0 4 Configuration
db file sequential read          10889967 104488    5.4 3    User I/O
buffer busy waits                933721    70829 65.2 2 Concurrency

ID SID    SPID MOD ACT  S  PGA SQLID/BLOCKER OPN  E/T STA STE EVENT/*LA  W/T
2 111 30223 jav    D 3.7M 2pb2tzp63af86 INS 1.0s ACT WAI gc buffer 416m
2 106 30225 jav    D 3.5M 2pb2tzp63af86 INS 1.0s ACT WAI gc buffer 416m
2 113 30228 jav    D 3.1M 2pb2tzp63af86 INS 1.0s ACT WAI gc buffer 416m
2 115 30232 jav    D 3.3M 2pb2tzp63af86 INS 1.0s ACT WAI gc buffer 416m
2 114 30230 jav    D 3.2M 2pb2tzp63af86 INS 1.0s ACT WAI gc buffer 416m
2 117 30234 jav    D 3.3M 2pb2tzp63af86 INS 1.0s ACT WAI gc buffer 416m
2 112 30236 jav    D 3.2M 2pb2tzp63af86 INS 1.0s ACT WAI gc buffer 416m
8. 交互键 t 只显示CDB的表空间

TABLESPACE INFORMATION:

TABLESPACE_NAME             SIZE  USED  USED%  STATUS    BIG  LOGGING CID
-----------------------------  ----  ----  -----  ---------  ---  --------- ---
SYSAUX                         32G  974M 3.0  ONLINE    NO LOGGING    1
SYSTEM                         32G  1.0G 3.1  ONLINE    NO LOGGING    1
USERS                         32G  2.7M    0  ONLINE    NO LOGGING    1
UNDOTBS1                      32G 35M 0.1  ONLINE    NO LOGGING    1
TEMP                         32G 20M    0  ONLINE    NO NOLOGGING 1
9. 交互键 f + t 只显示CDB的表空间

TABLESPACE INFORMATION:

TABLESPACE_NAME             SIZE  USED  USED%  STATUS    BIG  LOGGING CONTENTS EXTENT_MAN  SEGMENT  ALLOC_TYPE CID
-----------------------------  ----  ----  -----  ---------  ---  ---------  ---------  ----------  -------  ---------- ---
SYSAUX                         32G  974M 3.0  ONLINE    NO LOGGING PERMANENT  LOCAL    AUTO    SYSTEM    1
SYSTEM                         32G  1.0G 3.1  ONLINE    NO LOGGING PERMANENT  LOCAL    MANUAL SYSTEM    1
USERS                         32G  2.7M    0  ONLINE    NO LOGGING PERMANENT  LOCAL    AUTO    SYSTEM    1
UNDOTBS1                      32G 35M 0.1  ONLINE    NO LOGGING UNDO    LOCAL    MANUAL SYSTEM    1
TEMP                         32G 20M    0  ONLINE    NO NOLOGGING  TEMPORARY  LOCAL    MANUAL UNIFORM    1
10. 交互键 a

ASM DISKGROUP INFORMATION:

INST_ID  DISKGROUP_NAME  SIZE  FREE  %USED  HOTU  COLD  STATE    TYPE OFF
-------  --------------  ----  ----  -----  ----  ----  ----------  ------  ---
   1  CRS          2.0G  1.6G 17.8    0  364M  MOUNTED    EXTERN 0
   1  DATA          100G 87G 12.7    0 13G  CONNECTED EXTERN 0
   2  CRS          2.0G  1.6G 17.8    0  364M  MOUNTED    EXTERN 0
   2  DATA          100G 87G 12.7    0 13G  CONNECTED EXTERN 0
11. 交互键 f + a

ASM DISKGROUP INFORMATION:

INST_ID  DISKGROUP_NAME                SIZE  FREE  %USED  HOTU  COLD  STATE    TYPE    OFF  UNIT_SIZE  V  ASM_COMP  DB_COMP
-------  -----------------------------  ----  ----  -----  ----  ----  ----------  ------  -----  ---------  -  --------  --------
   1  CRS                         2.0G  1.6G 17.8    0  364M  MOUNTED    EXTERN    0 4194304  Y  19.0.0.0  10.1.0.0
   1  DATA                         100G 87G 12.7    0 13G  CONNECTED EXTERN    0 4194304  N  19.0.0.0  10.1.0.0
   2  CRS                         2.0G  1.6G 17.8    0  364M  MOUNTED    EXTERN    0 4194304  Y  19.0.0.0  10.1.0.0
   2  DATA                         100G 87G 12.7    0 13G  CONNECTED EXTERN    0 4194304  N  19.0.0.0  10.1.0.0
12. 交互键 x

Enter sql_id: 60v4b09jfjz2m

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------
SQL_ID: 60v4b09jfjz2m, child number 1
--------------------------------------------------
SELECT * FROM STRESSTESTTABLE WHERE ID = :1

Plan hash value: 3445451842

------------------------------------------------------------------------------
| Id | Operation                      | Name    | Rows | Cost | Stale |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT                |          |    | 1 |    |
| 1 |  TABLE ACCESS BY INDEX ROWID    | STRESSTEST | 1 | 1 |    |
| 2 | INDEX UNIQUE SCAN             | SYS_C00800 | 1 | 1 |    |
------------------------------------------------------------------------------

欢迎光临重庆思庄Oracle、Redhat认证学习论坛 (http://bbs.cqsztech.com/)