Orapub System Monitor

Orapub System Monitor

老外编写的一个工具,貌似现在用处不大了,随着每个人工作经验的增长,都会形成自己的一套知识库,一套脚本.在此分享下.找个老的,有想要研究的拿去.

SQL> @osmi.sql


OraPub Online Institute - What any-where any-device any-time training should be!


OraPub System Monitor - Interactive Analysis Menu
(c)1998-2015 OraPub, Inc. - Free use, just keep company and author name


-- Object Growth Analysis
obcrobj Create object growth objects
ogbigld Load selected objects into Object Growth tables (will prompt)
ogobjset Set object(s) obj growth specifics
ogdoit Load growth management data for scheduled objects
ogwhich ID object owner and name in dba_objects and o$dba_objects
ogobjlst List objects in Object Growth schedule


-- Users
cu Create an Oracle user ( )
users Summary info about Oracle users
tp[8,9] Top Oracle users/processes (will be prompted)


-- Configuration and Size
rbs Rollback configuration details
tss Tablespace space details
dfl Database file listing
tsmap Tablespace map detail ()
stu Segment types and size per user summary
idx Index columns (

)


-- Fragmentation
fgtbl Table frag ( )
fgidx Index frag ( )
tbfsum Table block frag summary ( )
istat Index statistics ( )


-- Data Selectivity
ds[7] Data selectivity for a col(s) ( )
dsn Data selectivity for a numeric col(s) ( )


-- Instance Parameters
ip Instance parameters ()
ipx Instance parameters (hidden also) ()
ipcbc Instance params realted to the cache buffer chain latches


-- SGA Size and Activity
sga System global area summary
spspinfo Shared pool sub-pool information
chr SGA cache hit ratios (some of them)
clone Show clone buffers counts
lc Library cache details
dboc v$db_object_cache ( )
mts Multi-threaded activity details


-- Redo Logs
rlog Redo log activity (v$log)
rdohist Redo log switching history (v$loghist)


-- Realtime Session Sampler Tool
rss Realtime Session Sampler
. ( )


-- Instance Statistics
sessinfo[9] Oracle session details / ID (prompted...)
sesstat V$SESSTAT details ( )
sysstat V$SYSSTAT details ()


-- SQL Text
sqls1[8,9] List "top" SQL statements ( )
sqlgettext List SQL statement text
sqlelget Get individual SQL elapsed times ( )
sqlelget11 Get individual SQL elapsed times ( )
simsql1 List similar SQL ()
simsql2 Shows all SQL that start with... (<'text of similar SQL'>)
.
-- IO
dfio Oracle datafile I/O details
iosum Oracle IO summary since instance startup
iosumx Oracle IO summary for interval between runs, DELTA


-- Latching/Mutex: Serialization Control
latch[8] Latching statistics
latchx Latching statistics w/deltas
latchclass Latch class assignment (must be run as sys)
latchchild Gather child latch activity ( )


-- Oracle Time Based Analysis: TT:Total Time, RT:Response Time
ttpctx TT Profile : instance perspective with out categories (like swpctx.sql)
rtpctx RT Profile : instance perspective with out categories (like swpctx.sql)
rtc RT Profile : system perspective using Oracle categories
rtsysx[8] RT Profile : system perspective DELTA ( <# sim SQL chars>)
rtsess[9] RT Profile : session perspective (9:,10+:)


-- Oracle Wait Event Analysis
swpct SW system event percentage summary
swpctx[11] SW system event percentage summary DELTA
swpctidle Idle only SW system event percentage summary
swpctxidle Idle only SW system event percentage summary DELTA
swswc SW session wait level w/counts
swswp SW session wait level w/paramters
swsessid SW session event level for given SID ()
swsid SW session wait level for given SID ()
swsw SW session wait level; all sessions!
swenq SW enqueue details
swenqc SW enqueue details by count
swenqx SW enqueue detail ( )
swname SW event name and display name ()
-- wait time histogram related scripts and reports, see readmeEventHist.txt
swgettimes Get individual event occurrence times ( )
swEhChart.r Create 2 pages of nice histograms based on OSM report output
swEhVdGet Get v$event_histogram wait times and gen output for swEhChart.r
( )
swEhAwrGet Get v$event_histgroam times from AWR (DBA_HIST_) and gen output for swEhChart.r
swhist Get v$event_hist bin counts. Output can be used with swEhChart.r ()
swhistx Get delta v$event_hist bin counts. Output Can be used with swEhChart.r ()


-- OS CPU Utilization (very old scripts)
oscpux OS CPU consumption
oracpu Oracle CPU breakdown (since startup)
oracpux Oracle CPU breakdown (delta)
timechk x Check Oracle's CPU consumption time (v$sysstat based) reporting ()


-- Object Details
objloc Object location listing ( )
objfb Object details for a given file and block ( )
bcobjfb Object details for a ... in cache ( )


-- Research Helpers
mkodo Create the odometer (odo) table and indexes.
mkodo_ouch Same, but with a commit after each insert.
cycledb9tx Cycle the current db instance (O 9,10,11,12) - Be careful!
listeventcodes list all the Oracle event codes ()


OSM Management
osmprep Prepare the OSM Interactive environment
osmtitle Standard OSM Interactive title script (other similar scripts)
osmclear Standard OSM Interactive end of script clear stuff


OraPub System Monitor - Interactive Analysis Menu
(c)1998-2015 OraPub, Inc. - Free use, just keep company and author name


OraPub Online Institute - Because Oracle DBAs need great training NOW.


SQL> @swpct
Enter value for 1:
SP2-0137: DEFINE requires a value following equal sign
Enter value for filter:
from o$event_type
*
ERROR at line 12:
ORA-00942: table or view does not exist




SQL> @dfio
Enter value for osm_title2:


Database: ora11g 21-Apr 02:51am
Report: dfio.sql OSM by OraPub, Inc. Page 1
Oracle DB File I/O Information




Write Blk Bytes Read Blk Bytes
Data File Req(k) Writes(k) Writes(M) Req(k) Reads(k) Reads(M) MBR%
---------------------------------------- ---------- ---------- ---------- ----------- ----------- ----------- -----
Redo Log Files 48 -1 2,066 -1 -1 -1 -1.0
/u01/app/oradata/ora11g/users01.dbf 93 226,706 1,771 43 603 4,710 11.1
/u01/app/oradata/ora11g/sysaux01.dbf 38 80,750 631 399 445 3,478 0.9
/u01/app/oradata/ora11g/undotbs01.dbf 31 80,444 628 0 0 1 0.8
/u01/app/oradata/ora11g/system01.dbf 13 30,732 240 105 191 1,490 1.4
/u01/app/oradata/ora11g/examples.dbf 0 2,259 18 13 16 126 1.0
/u01/app/oradata/ora11g/temp01.dbf 0 1,986 16 0 2 13 10.9
/u01/app/oradata/ora11g/test.dbf 0 5 0 0 0 0 0.8
/u01/app/oradata/ora11g/big.dbf 0 0 0 0 1 10 18.8


9 rows selected.


SQL> @users


Database: ora11g 21-Apr 02:52am
Report: users.sql OSM by OraPub, Inc. Page 1
Basic Oracle Database User Information


Default Temporary
Username Tablespace Tablespace Profile Role (admin,grant)
------------ ------------ ------------ ------------------ ---------------------
APEX_030200 SYSAUX TEMP DEFAULT CONNECT-YN
SYSAUX TEMP DEFAULT RESOURCE-YN
BI EXAMPLE TEMP DEFAULT RESOURCE-NN
CTXSYS SYSAUX TEMP DEFAULT CTXAPP-YN
SYSAUX TEMP DEFAULT RESOURCE-NN
DBSNMP SYSAUX TEMP DEFAULT OEM_MONITOR-NN
ELVIS USERS TEMP DEFAULT DBA-NN
USERS TEMP DEFAULT EXP_FULL_DATABASE-NN
EXFSYS SYSAUX TEMP DEFAULT RESOURCE-NN
HR EXAMPLE TEMP DEFAULT DBA-NN
EXAMPLE TEMP DEFAULT RESOURCE-NN
IX EXAMPLE TEMP DEFAULT AQ_ADMINISTRATOR_ROLE
-NN


EXAMPLE TEMP DEFAULT AQ_USER_ROLE-NN
EXAMPLE TEMP DEFAULT CONNECT-NN
EXAMPLE TEMP DEFAULT RESOURCE-NN
EXAMPLE TEMP DEFAULT SELECT_CATALOG_ROLE-N
N


MDDATA USERS TEMP DEFAULT CONNECT-NN
USERS TEMP DEFAULT RESOURCE-NN
MDSYS SYSAUX TEMP DEFAULT CONNECT-NN
SYSAUX TEMP DEFAULT RESOURCE-NN
MGMT_VIEW SYSTEM TEMP DEFAULT MGMT_USER-NN
OE EXAMPLE TEMP DEFAULT RESOURCE-NN
EXAMPLE TEMP DEFAULT XDBADMIN-NN
OLAPSYS SYSAUX TEMP DEFAULT OLAP_DBA-NN
SYSAUX TEMP DEFAULT RESOURCE-NN
ORDSYS SYSAUX TEMP DEFAULT JAVAUSERPRIV-NN
OUTLN SYSTEM TEMP DEFAULT RESOURCE-NN
OWBSYS SYSAUX TEMP DEFAULT AQ_ADMINISTRATOR_ROLE
-NN


SYSAUX TEMP DEFAULT AQ_USER_ROLE-NN
SYSAUX TEMP DEFAULT CONNECT-YN
SYSAUX TEMP DEFAULT CWM_USER-YN
SYSAUX TEMP DEFAULT JAVAUSERPRIV-NN
SYSAUX TEMP DEFAULT JAVA_ADMIN-NN
SYSAUX TEMP DEFAULT OLAP_USER-YN
SYSAUX TEMP DEFAULT OWB$CLIENT-YN
SYSAUX TEMP DEFAULT OWB_DESIGNCENTER_VIEW
-YN


SYSAUX TEMP DEFAULT OWB_USER-YN
PM EXAMPLE TEMP DEFAULT CONNECT-NN
EXAMPLE TEMP DEFAULT RESOURCE-NN
SCOTT USERS TEMP DEFAULT CONNECT-NN
USERS TEMP DEFAULT CTXAPP-NN
USERS TEMP DEFAULT DBA-NN
USERS TEMP DEFAULT RESOURCE-NN
SH EXAMPLE TEMP DEFAULT CWM_USER-NN
EXAMPLE TEMP DEFAULT RESOURCE-NN


Database: ora11g 21-Apr 02:52am
Report: users.sql OSM by OraPub, Inc. Page 2
Basic Oracle Database User Information


Default Temporary
Username Tablespace Tablespace Profile Role (admin,grant)
------------ ------------ ------------ ------------------ ---------------------
SH EXAMPLE TEMP DEFAULT SELECT_CATALOG_ROLE-N
N


SPATIAL_CSW_ USERS TEMP DEFAULT CONNECT-NN
ADMIN_USR


USERS TEMP DEFAULT RESOURCE-NN
USERS TEMP DEFAULT SPATIAL_CSW_ADMIN-YN
SPATIAL_WFS_ USERS TEMP DEFAULT CONNECT-NN
ADMIN_USR


USERS TEMP DEFAULT RESOURCE-NN
USERS TEMP DEFAULT SPATIAL_WFS_ADMIN-YN
SYS SYSTEM TEMP DEFAULT ADM_PARALLEL_EXECUTE_
TASK-YN


SYSTEM TEMP DEFAULT APEX_ADMINISTRATOR_RO
LE-YN


SYSTEM TEMP DEFAULT AQ_ADMINISTRATOR_ROLE
-YN


SYSTEM TEMP DEFAULT AQ_USER_ROLE-YN
SYSTEM TEMP DEFAULT AUTHENTICATEDUSER-YN
SYSTEM TEMP DEFAULT CONNECT-YN
SYSTEM TEMP DEFAULT CSW_USR_ROLE-YN
SYSTEM TEMP DEFAULT CTXAPP-YN
SYSTEM TEMP DEFAULT CWM_USER-YN
SYSTEM TEMP DEFAULT DATAPUMP_EXP_FULL_DAT
ABASE-YN


SYSTEM TEMP DEFAULT DATAPUMP_IMP_FULL_DAT
ABASE-YN


SYSTEM TEMP DEFAULT DBA-YN
SYSTEM TEMP DEFAULT DBFS_ROLE-YN
SYSTEM TEMP DEFAULT DELETE_CATALOG_ROLE-Y
N


SYSTEM TEMP DEFAULT EJBCLIENT-YN
SYSTEM TEMP DEFAULT EXECUTE_CATALOG_ROLE-
YN


SYSTEM TEMP DEFAULT EXP_FULL_DATABASE-YN
SYSTEM TEMP DEFAULT GATHER_SYSTEM_STATIST
ICS-YN


SYSTEM TEMP DEFAULT HS_ADMIN_EXECUTE_ROLE
-YN


SYSTEM TEMP DEFAULT HS_ADMIN_ROLE-YN
SYSTEM TEMP DEFAULT HS_ADMIN_SELECT_ROLE-
YN


Database: ora11g 21-Apr 02:52am
Report: users.sql OSM by OraPub, Inc. Page 3
Basic Oracle Database User Information


Default Temporary
Username Tablespace Tablespace Profile Role (admin,grant)
------------ ------------ ------------ ------------------ ---------------------


SYS SYSTEM TEMP DEFAULT IMP_FULL_DATABASE-YN
SYSTEM TEMP DEFAULT JAVADEBUGPRIV-YN
SYSTEM TEMP DEFAULT JAVAIDPRIV-YN
SYSTEM TEMP DEFAULT JAVASYSPRIV-YN
SYSTEM TEMP DEFAULT JAVAUSERPRIV-YN
SYSTEM TEMP DEFAULT JAVA_ADMIN-YN
SYSTEM TEMP DEFAULT JAVA_DEPLOY-YN
SYSTEM TEMP DEFAULT JMXSERVER-YN
SYSTEM TEMP DEFAULT LOGSTDBY_ADMINISTRATO
R-YN


SYSTEM TEMP DEFAULT MGMT_USER-YN
SYSTEM TEMP DEFAULT OEM_ADVISOR-YN
SYSTEM TEMP DEFAULT OEM_MONITOR-YN
SYSTEM TEMP DEFAULT OLAP_DBA-YN
SYSTEM TEMP DEFAULT OLAP_USER-YN
SYSTEM TEMP DEFAULT OLAP_XS_ADMIN-YN
SYSTEM TEMP DEFAULT ORDADMIN-YN
SYSTEM TEMP DEFAULT OWB$CLIENT-YN
SYSTEM TEMP DEFAULT OWB_DESIGNCENTER_VIEW
-YN


SYSTEM TEMP DEFAULT OWB_USER-YN
SYSTEM TEMP DEFAULT RECOVERY_CATALOG_OWNE
R-YN


SYSTEM TEMP DEFAULT RESOURCE-YN
SYSTEM TEMP DEFAULT SCHEDULER_ADMIN-YN
SYSTEM TEMP DEFAULT SELECT_CATALOG_ROLE-Y
N


SYSTEM TEMP DEFAULT SPATIAL_CSW_ADMIN-YN
SYSTEM TEMP DEFAULT SPATIAL_WFS_ADMIN-YN
SYSTEM TEMP DEFAULT WFS_USR_ROLE-YN
SYSTEM TEMP DEFAULT XDBADMIN-YN
SYSTEM TEMP DEFAULT XDB_SET_INVOKER-YN
SYSTEM TEMP DEFAULT XDB_WEBSERVICES-YN
SYSTEM TEMP DEFAULT XDB_WEBSERVICES_OVER_
HTTP-YN


SYSTEM TEMP DEFAULT XDB_WEBSERVICES_WITH_
PUBLIC-YN


SYSMAN SYSAUX TEMP DEFAULT MGMT_USER-YN
SYSAUX TEMP DEFAULT RESOURCE-NN
SYSAUX TEMP DEFAULT SELECT_CATALOG_ROLE-Y
N


SYSTEM SYSTEM TEMP DEFAULT AQ_ADMINISTRATOR_ROLE
-YN


SYSTEM TEMP DEFAULT DBA-YN


Database: ora11g 21-Apr 02:52am
Report: users.sql OSM by OraPub, Inc. Page 4
Basic Oracle Database User Information


Default Temporary
Username Tablespace Tablespace Profile Role (admin,grant)
------------ ------------ ------------ ------------------ ---------------------
SYSTEM SYSTEM TEMP DEFAULT MGMT_USER-NN
WMSYS SYSAUX TEMP DEFAULT CONNECT-NN
SYSAUX TEMP DEFAULT RESOURCE-NN
SYSAUX TEMP DEFAULT WM_ADMIN_ROLE-YN
XDB SYSAUX TEMP DEFAULT CTXAPP-NN
SYSAUX TEMP DEFAULT JAVAUSERPRIV-NN
SYSAUX TEMP DEFAULT RESOURCE-NN


116 rows selected.


SQL>
SQL>