10.Oracle深度学习笔记——Oracle统计数据自动采集脚本
欢迎转载，转载请标明出处：http://blog.csdn.net/notbaron/article/details/50631514
根据上一篇的命令，蛤蟆生成了一个自动收集脚本，可以放到数据库机器上直接执行。
下载路径如下：
http://download.csdn.net/detail/notbaron/9432443
执行命令：
$ ./select_dbinfo.exe
会在当前命令下生成dbinfo.log
内容如下，列出了
l 数据字典命中率，
l 库缓存命中率，
l 库缓存执行效率，
l 硬分析次数，
l 数据缓存命中，
l 系统IO分布，
l 数据库等待时间相对数据库时间的比例，
l 超过一秒的等待事件，
l 最长等待的SQL文本，
l 最后5分钟的等待事件，
l 最后5分钟中最繁忙的SQL语句。
后续根据需要还会加入相关
---------------------------------------------------
---------------------------------------------------
data dictionary hit ratio,should be between95%~99%
---------------------------------------------------
---------------------------------------------------
data dictionary hit ratio
-------------------------
.938511799
---------------------------------------------------
---------------------------------------------------
library cache hit ratio,should be 99% or higer
---------------------------------------------------
---------------------------------------------------
LIBRARY_CACHE_HIT_RATIO
-----------------------
.893835195
---------------------------------------------------
---------------------------------------------------
efficiency of library cache ,less reloadsand more hits is requred
---------------------------------------------------
---------------------------------------------------
NAMESPACE PINS PINHITS RELOADS
-------------------------------------------------------------------------- ---------- ----------
AUDIT POLICY 408 404 0
BODY 4313 4175 3
CLUSTER 426 413 0
DBINSTANCE 0 0 0
DBLINK 0 0 0
DIRECTORY 9 6 0
EDITION 1584 1580 0
INDEX 269 139 45
OBJECT ID 0 0 0
PDB 0 0 0
QUEUE 10 6 0
RULESET 3 2 0
SCHEMA 0 0 0
SQL AREA 85178 78252 601
SQL AREA BUILD 0 0 0
SQL AREA STATS 2268 212 0
TABLE/PROCEDURE 43367 38015 1054
TRIGGER 219 200 0
---------------------------------------------------
---------------------------------------------------
after instance start ,the number of parsesand hard parses associate with the session id
---------------------------------------------------
---------------------------------------------------
SID Hard Parses Executions Count
---------- ----------- ----------------
1 560 4386
9 15 73
15 15 190
16 2 51
21 220 5078
26 47 1584
29 1 36
37 1 1
75 12 118
79 1 2
---------------------------------------------------
---------------------------------------------------
data buffer hit ratio,the higer the better
---------------------------------------------------
---------------------------------------------------
NAME PHYSICAL_READS DB_BLOCK_GETSCONSISTENT_GETS HitRatio
-------------------- --------------------------- --------------- ----------
DEFAULT 17453 50587 1312207 .987193222
---------------------------------------------------
---------------------------------------------------
the io distribution
---------------------------------------------------
---------------------------------------------------
NAME READS WRTS READTIME WRITETIME
--------------------------------------------- ---------- ---------- ----------
+DATA/TOADDB/DATAFILE/mgmt_ad4j_ts. 4 1 3.5 0
276.902690109
+DATA/TOADDB/DATAFILE/mgmt_ecm_depo 4 1 2.75 0
t_ts.256.902690105
+DATA/TOADDB/DATAFILE/mgmt_tablespa 4 1 3 0
ce.257.902690107
+DATA/toaddb/example01.dbf 9 1 1.55555556 0
+DATA/toaddb/sysaux01.dbf 2241 1026 .808121374 .322612086
+DATA/toaddb/system01.dbf 9257 424 1.34914119 .146226415
+DATA/toaddb/undotbs01.dbf 23 540 1.04347826 .12962963
+DATA/toaddb/users01.dbf 4 1 2.5 0
+DATA/tpcc1.dbf 4 1 1.75 0
---------------------------------------------------
---------------------------------------------------
the ratio of database Wait Time
---------------------------------------------------
---------------------------------------------------
METRIC_NAME VALUE
--------------------------------------------------------------------------
Database Wait Time Ratio 0
Database CPU Time Ratio 175.827536
---------------------------------------------------
---------------------------------------------------
time waited event which is more than 1second
---------------------------------------------------
---------------------------------------------------
EVENT TIME_WAITED AVERAGE_WAIT
--------------------------------------------------------------------------- ------------
rdbms ipc message 16995425 258
Space Manager: slave idle wait 2657072 492.42
DIAG idle wait 2275498 100.02
lreg timer 1138055 300.04
pmon timer 1138004 299.79
wait for unread message on broadcast channel 1137864 299.99
VKRM Idle 1137494 1137493.59
ASM background timer 1137436 475.32
heartbeat redo informer 1137126 100.06
shared server idle wait 1135325 2995.58
AQPC idle 1135148 2987.23
Streams AQ: qmn coordinator idle wait 1134314 1398.66
dispatcher timer 1134142 6000.75
Streams AQ: qmn slave idle wait 1133687 2799.23
smon timer 1111726 13394.29
class slave wait 881211 1128.31
SQL*Net message from client 865234 199.5
jobq slave wait 523024 49.87
db file sequential read 13167 1.58
Streams AQ: load balancer idle 12000 12000.08
control file parallel write 2753 .69
oracle thread bootstrap 1968 2.96
external table read 1777 1776.76
library cache load lock 1383 25.62
os thread creation 748 1.13
db file scattered read 647 1.47
control file sequential read 614 .02
log file parallel write 612 .56
ASM background starting 580 48.37
db file parallel read 524 8.07
control file heartbeat 400 399.96
db file parallel write 383 .19
cursor: pin S wait on X 366 11.44
enq: TM - contention 327 327.37
read by other session 306 2.81
rdbms ipc reply 275 16.17
log file sync 171 1.17
direct path write temp 141 .99
ASM file metadata operation 140 .04
Disk file operations I/O 115 .02
---------------------------------------------------
---------------------------------------------------
users sqltext and his event
---------------------------------------------------
---------------------------------------------------
USERNAME SQL_TEXT
----------------------------------------------------------------------------------------------
EVENT
----------------------------------------------------------------
SYS selects.username,t.sql_text,s.event from v$session s,v$sqltext
SQL*Net message to client
SYS t wheres.sql_hash_value = t.hash_value and s.sql_address=t.addr
SQL*Net message to client
SYS ess and s.type<> 'BACKGROUD' order by s.sid,t.hash_value,t.piec
SQL*Net message to client
SYS e
SQL*Net message to client
---------------------------------------------------
---------------------------------------------------
the wait event of the last 5 min
---------------------------------------------------
---------------------------------------------------
OBJECT_NAME OBJECT_TYPE EVENT TOTAL_WAIT_TIME
--------------- ----------------------------------------------------- ---------------
CDB_SERVICE$ TABLE oracle thread bootstrap 309475
---------------------------------------------------
---------------------------------------------------
the most busy sql of the last 5 min
---------------------------------------------------
---------------------------------------------------
USER_ID USERNAME
---------- ---------------
SQL_TEXT
----------------------------------------------------------------------------------------------------
TOTAL_WAIT_TIME
---------------
0 SYS
select s.sid,s.value "HardParses", t.value "Executions Count" from v$sesstat s,v$sesstat twhere s.
sid=t.sid and s.statistic#=(selectstatistic# from v$statname where name='parse count (hard)') and t
.statistic#=(select statistic# fromv$statname where name='execute count') and s.value >0
2
0 SYS
SELECT VALUE FROM V$PARAMETER WHERENAME='compatible'
440879
0 SYS
call WWV_FLOW_MAIL.PUSH_QUEUE_IMMEDIATE( )
440954
扫一扫
205
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
