分类： Oracle
2007-11-12 22:02:21
内容包括
statspack安装
statspack报告的详细说明
Statspack 使用说明
一．statspack系统安装 1
二．Statspack介绍 2
三．Statspack使用 4
四．Statspack报告说明 5
一．statspack系统安装
statspack的安装程序在@?/rdbms/admin/下:
spcreate.sql 安装
spdrop.sql 卸载
spauto.sql: 生成自动采集数据任务
spreport.sql 生成分析报告
spuexp.sql 参数文件
sppurge.sql 清除不在需要的数据
sptrunc.sql 清除所有的数据
sprepsql.sql:用于根据给定的SQL HASH值生成SQL报告
为了能顺利地运行Statspack 工具，则需要设置以下参数：
job_queue_processes>0 (如果不采用自动采集数据则可不设)
timed_statistics=true (如果不采用自动采集数据则可不设)
安装
1.建一个单独的表空间用于Statspack 或建在tools表空间上(>100MB)
$sqlplus “/ as sysdba”
SQL>create tablespace perfstat
SQL>datafile ‘/oracle/oradata/perfstat.dbf’
SQL>size 500M extent management local;
2.建用户perfstat及表
SQL>@?/rdbms/admin/spcreate.sql
要求输入表空间及临时表空间.
建完后会在本目录下生成：
spauto.lis
spcpkg.lis
spctab.lis
spcusr.lis
spdtab.lis
spdusr.lis
grep –I “ora-“ *.lis 查看是否有错。
3．删除statspack表
SQL>@?/rdbms/admin/spdrop.sql
4.测试statspack
采样数据
SQL> exec statspack.snap
后隔几分钟后再次采样数据
SQL> exec statspack.snap
生成报表
SQL>@?/rdbms/admin/spreport.sql
二．Statspack介绍
Oracle9i调优顺序一般采用自顶向下的顺序：
Statspack源于utlbstat和utlestat工具，在执行快照时，statspack会从 SGA内部的RAW内存结构中来采样数据，并将结果存入相应表中。
RAW v$内存结构表 à statspack stats$内存结构表
V$sysstat stats$sysstat
V$sgastat stats$sgastat
V$parameter stats$parameter
V$librarycache stats$librarycache
1． 外部环境
内存
vmstat
bash-2.03$ vmstat 2 5
procs memory page disk faults cpu
r b w swap free re mf pi po fr de sr s0 s1 s1 -- in sy cs us sy id
0 0 0 9868032 2120968 14 3 159 8 8 0 0 0 0 24 0 306 455 1204 1 2 97
0 1 0 9813224 2154792 29 6 0 20 20 0 0 0 0 39 0 388 9587 2353 3 3 94
0 0 0 9813224 2154296 39 0 0 40 40 0 0 0 1 46 0 393 9529 2363 5 3 92
0 1 0 9813224 2153736 36 3 0 20 20 0 0 0 0 48 0 397 9387 2364 3 3 93
0 0 0 9813224 2153232 37 3 0 20 20 0 0 0 0 48 0 397 9483 2360 4 3 93
其中：
CPU瓶颈:
proc下：
r:正在运行的任务队列，当r>CPU数量时，则会出现CPU等待瓶颈
查看CPU个数：
psrinfo –v|grep –I “status of processor” | wc -l
RAM瓶颈:
Page下：
Pi:页导入次数：如果RAW短缺时，系统会产生pi操作
查看内存容量
prtconf|grep –i “mem”
2． 系统IO
sar –d
bash-2.03$ sar -d 2 2
SunOS mydb001 5.8 Generic_108528-13 sun4us 01/09/04
08:35:11 device %busy avque r+w/s blks/s avwait avserv
08:35:13 nfs1 0 0.0 0 0 0.0 0.0
sd0 0 0.0 0 0 0.0 0.0
sd1 0 0.0 0 40 0.0 6.3
sd1,a 0 0.0 0 40 0.0 6.3
sd1,c 0 0.0 0 0 0.0 0.0
sd1,d 0 0.0 0 0 0.0 0.0
sd1,e 0 0.0 0 0 0.0 0.0
sd1,f 0 0.0 0 0 0.0 0.0
sd16 28 0.5 51 817 0.0 10.2
sd16,a 28 0.5 51 817 0.0 10.2
sd16,c 0 0.0 0 0 0.0 0.0
ohci0,bu 0 0.0 0 0 0.0 0.0
ohci0,ct 0 0.0 0 0 0.0 0.0
ohci0,in 0 0.0 0 0 0.0 0.0
ohci0,is 0 0.0 0 0 0.0 0.0
ohci0,to 0 0.0 0 0 0.0 0.0
08:35:15 nfs1 0 0.0 0 0 0.0 0.0
sd0 0 0.0 0 0 0.0 0.0
sd1 0 0.0 1 79 0.0 4.9
sd1,a 0 0.0 1 79 0.0 4.9
sd1,c 0 0.0 0 0 0.0 0.0
sd1,d 0 0.0 0 0 0.0 0.0
sd1,e 0 0.0 0 0 0.0 0.0
sd1,f 0 0.0 0 0 0.0 0.0
sd16 27 0.5 47 745 0.0 10.4
sd16,a 27 0.5 47 745 0.0 10.4
sd16,c 0 0.0 0 0 0.0 0.0
ohci0,bu 0 0.0 0 0 0.0 0.0
ohci0,ct 0 0.0 0 0 0.0 0.0
ohci0,in 0 0.0 0 0 0.0 0.0
ohci0,is 0 0.0 0 0 0.0 0.0
ohci0,to 0 0.0 0 0 0.0 0.0
Average nfs1 0 0.0 0 0 0.0 0.0
sd0 0 0.0 0 0 0.0 0.0
sd1 0 0.0 1 59 0.0 5.4
sd1,a 0 0.0 1 59 0.0 5.4
sd1,c 0 0.0 0 0 0.0 0.0
sd1,d 0 0.0 0 0 0.0 0.0
sd1,e 0 0.0 0 0 0.0 0.0
sd1,f 0 0.0 0 0 0.0 0.0
sd16 27 0.5 49 781 0.0 10.3
sd16,a 27 0.5 49 781 0.0 10.3
sd16,c 0 0.0 0 0 0.0 0.0
ohci0,bu 0 0.0 0 0 0.0 0.0
ohci0,ct 0 0.0 0 0 0.0 0.0
ohci0,in 0 0.0 0 0 0.0 0.0
ohci0,is 0 0.0 0 0 0.0 0.0
ohci0,to 0 0.0 0 0 0.0 0.0
说明 ：
一般%busy高些，%avque低些，文件系统的效率会较高,目前系统文件系统效率已达到
三．Statspack使用
3． 手工采样数据
sqlplus perfstat/perfstat
SQL> exec statspack.snap
后隔几分钟后再次采样数据
SQL> exec statspack.snap
生成报表
SQL>@?/rdbms/admin/spreport.sql
4． 系统自动采样数据
定义定时任务
修改spauto.sql内容，定义采样数据的时间间隔
dbms_job.submit(:jobno,’statspack.snap;’,trunc(sysdate+1/24,”HH”),’trunc(sysdate+1/24,”HH”),TRUE,:instno);
一天24小时，1440分钟，则：
每小时一次： 1/24 (建议使用)
每30分钟一次： 1/48
每10分钟一次 1/144
每5分钟一次 1/288
后执行
SQL>＠?/rdbms/admin/spauto.sql
生成分析报告
SQL>＠?/rdbms/admin/spreport.sql
停止定时任务
sqlplus perfstat/perfstat
SQL>select job,interval from user_jobs;
SQL>exec dbms_job.remove(‘:job_no’);
删除历史数据
delete from stats$snapshot where snap_id删除全部数据
SQL>＠?/rdbms/admin/sptrunc.sql
四．Statspack报告说明
Statspack报告分为几个部分：
5． 数据库总体信息
6． 每秒每事务的资源消耗情况
7． 实例的各组件的命中率
8． 共享池总体情况
9． 前5个等待事件
10． DB所有等待事件
11． 后台进程等待事件
12． 根据BufferGets进行排序的SQL
13． 按物理IO进行排序的SQL
14． 按执行次数排序的SQL
15． 按分析次数排序的SQL
16． 实例的当前活动的统计数据
17． tablespace IO统计数据
18． 表空间文件 IO统计数据
19． buffer池统计数据
20． 实例恢复统计数据
21． Buffer池的参考数据
22． Buffer等待统计数据
23． PGA总体统计数据 1
24． PGA总体统计数据2
25． PGA内存参考数据
26． 回滚段统计
27． 回滚段存储统计
28． undo段总体情况
29． undo段统计
30． 锁存器的当前情况
31． 锁存器睡眠等待统计
32． 锁存器失败情况
33． 数据字典cache性能统计
34． 库cache 性能统计
35． 共享池性能统计
36． SGA区总体情况
37． SGA各组件的活动情况
38． 系统配置参数
STATSPACK report for
------------------------------------1.DB的总体信息----------------------------------------------------
DB Name DB Id Instance Inst Num Release Cluster Host
------------ ----------- ------------ -------- ----------- ------- ------------
MYDB 2125240762 mydb 1 9.2.0.1.0 NO VCS-SERVER1
Snap Id Snap Time Sessions Curs/Sess Comment
------- ------------------ ------- --------- -------------------
Begin Snap: 1 09-Aug-04 19:28:12 32 2.7
End Snap: 2 09-Aug-04 19:33:06 32 3.0
Elapsed: 4.90 (mins) (本次报告的间隔时间)
Cache Sizes (end)
~~~~~~~~~~~~~~~~~
Buffer Cache: 1,536M Std Block Size: 8K
Shared Pool Size: 112M Log Buffer: 16,000K
--------------------------2.每秒每事务的资源消耗情况---------------------
Load Profile
~~~~~~~~~~~~ Per Second (每秒) Per Transaction(每事务)
--------------- ---------------
Redo size: 38,498.93 6,733.30 –每秒/每事务产生的redo大小
Logical reads: 593.28 103.76 –每秒/每事务逻辑读
Block changes: 77.60 13.57 –每秒/每事务修改的块数
Physical reads: 2.65 0.46 -- 每秒/每事务物理读
Physical writes: 8.17 1.43 —每秒/每事务物理写
User calls: 38.32 6.70
Parses: 6.52 1.14 --SQL分析的次数
Hard parses: 0.05 0.01 –SQL硬分析的次数
Sorts: 0.73 0.13--
Logons: 0.01 0.00
Executes: 39.64 6.93
Transactions: 5.72
% Blocks changed per Read: 13.08 Recursive Call %: 24.84
Rollback per transaction %: 0.00 Rows per Sort: 138.04
说明：
硬分析：就是之前不存在此SQL，是第一次解析。 如果SQL重用度很高，则硬解析应保持很低。
% Blocks changed per Read：表示逻辑读用于只读而不是修改的块的比例
Recursive Call %:递归调用SQL的比例，在PL/SQL上执行的SQL称为递归的SQL
Rollback per transaction %: 事务的回滚率
Rows per Sort: 每次排序的记录行数
-----------------------------------3.实例的各组件的命中率-----------------------------------
Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 100.00 Redo NoWait %: 100.00
Buffer Hit %: 99.55 In-memory Sort %: 100.00
Library Hit %: 99.33 Soft Parse %: 99.16
Execute to Parse %: 83.56 Latch Hit %: 99.99
Parse CPU to Parse Elapsd %: % Non-Parse CPU:
说明：
Execute to Parse %: 是语句执行与分析的比例，如果要SQL重用率高，则这个比例会很高。
Soft Parse %:软分析：即在共享池中重复使用的SQL，系统应保持较高的软分析率，否则说明系统的SQL没有绑定变量。
Parse CPU to Parse Elapsd %: 用于分析每个CPU 花费的秒数，应该处于较高比例。如果=100%,说明CPU没有等待。
---------------------------------4.共享池总体情况---------------------------------------------------------
Shared Pool Statistics Begin End
------ ------
Memory Usage %: 89.91 90.55
% SQL with executions>1: 32.14 32.67
% Memory for SQL w/exec>1: 31.30 33.38
说明：
Memory Usage %: 正在使用的共享池的%,这个值应保持在75%~90%,如果这个值太低，就浪费内存，如果太高，会使共享池外部的组件老化，如果SQL语句被再次执行，则就会发生硬分析。
% SQL with executions>1:共享池中有多少执行次数大于一次的SQL语句的度量。
% Memory for SQL w/exec>1: 频繁使用的SQL语句消耗内存多少的比例。
------------------------------5.前5个等待事件------------------------------------------------
Top 5 Timed Events
~~~~~~~~~~~~~~~~~~ % Total
Event Waits Time (s) Ela Time
-------------------------------------------- ------------ ----------- --------
db file sequential read 623 3 46.70
log file sync 1,682 2 32.30
control file parallel write 95 1 8.16
db file parallel write 190 0 6.09
log file parallel write 1,674 0 5.77
-------------------------------------------------------------
说明：
log file sync:当一个用户的会话提交时，会话的重写信息需要刷新到重做日志文件中，这个用户会话将发送LGWR将日志缓冲写到重做日志文件，当LGWR已经完成写入操作时，它将发送这个用户会话。
Wait Time:等待时间包括日志缓冲的写入和发送操作。
--------------------------------------6.DB所有等待事件-------------------------------------------------
Wait Events for DB: MYDB Instance: mydb Snaps: 1 -2
-> s - second
-> cs - centisecond - 100th of a second
-> ms - millisecond - 1000th of a second
-> us - microsecond - 1000000th of a second
-> ordered by wait time desc, waits desc (idle events last)
Avg
Total Wait wait Waits
Event Waits Timeouts Time (s) (ms) /txn
---------------------------- ------------ ---------- ---------- ------ --------
db file sequential read 623 0 3 5 0.4
log file sync 1,682 0 2 1 1.0
control file parallel write 95 0 1 6 0.1
db file parallel write 190 95 0 2 0.1
log file parallel write 1,674 1,664 0 0 1.0
db file scattered read 25 0 0 2 0.0
control file sequential read 78 0 0 0 0.0
LGWR wait for redo copy 13 0 0 0 0.0
SQL*Net break/reset to clien 4 0 0 0 0.0
buffer busy waits 2 0 0 0 0.0
latch free 1 0 0 0 0.0
SQL*Net message from client 10,830 0 4,364 403 6.4
SQL*Net more data from clien 1,596 0 0 0 0.9
SQL*Net message to client 10,830 0 0 0 6.4
-------------------------------------------------------------
--------------------------------7.后台进程等待事件-----------------------------------------
Background Wait Events for DB: MYDB Instance: mydb Snaps: 1 -2
-> ordered by wait time desc, waits desc (idle events last)
Avg
Total Wait wait Waits
Event Waits Timeouts Time (s) (ms) /txn
---------------------------- ------------ ---------- ---------- ------ --------
control file parallel write 95 0 1 6 0.1
db file parallel write 190 95 0 2 0.1
log file parallel write 1,674 1,664 0 0 1.0
control file sequential read 36 0 0 0 0.0
LGWR wait for redo copy 13 0 0 0 0.0
rdbms ipc message 5,352 3,687 1,148 214 3.2
smon timer 1 1 281 ###### 0.0
-------------------------------------------------------------
------------------8.根据BufferGets进行排序的SQL-----------------------------------
SQL ordered by Gets for DB: MYDB Instance: mydb Snaps: 1 -2
-> End Buffer Gets Threshold: 10000
-> Note that resources reported for PL/SQL includes the resources used by
all SQL statements called within the PL/SQL code. As individual SQL
statements are also reported, it is possible and valid for the summed
total % to exceed 100
CPU Elapsd
Buffer Gets Executions Gets per Exec %Total Time (s) Time (s) Hash Value
--------------- ------------ -------------- ------ -------- --------- ----------
74,380 20 3,719.0 42.6 0.00 5.03 1027916473
select count(*) from myuser.userbaseinfo
10,920 1,291 8.5 6.3 0.00 0.71 1385081364
insert into Refence_tabvalues(:p1, :p2, :p3, :p4, :p5,:p6)
10,629 132 80.5 6.1 0.00 0.49 2785281485
update msginfo set Orig_Addr=:p1,Dest_Addr=:p2,service_type=:p3
,sub_serv_type=:p4,TransactionID=:p5,Priority=:p6,state=:p7,Send
Count=:p8,errorCode=:p9,finalDate=:p10,smFlag=:p11,tVaspId=:p12,
tVasId=:p13,tServiceCode=:p14,DateTime=:p15,DeliveryTime=:p16,Re
adReply=:p17,bAdaptations=:p18,ContentType=:p19,bMsgDistributeIn
9,751 2,435 4.0 5.6 0.00 0.70 2271041384
select * from msginfo where msg_id=:p1
9,625 2,907 3.3 5.5 0.00 0.93 1077832894
select * from userinfo where sub_isdn=:p1
5,824 1,962 3.0 3.3 0.00 1.72 2431777133
select * from destinfo where sub_isdn=:p1
5,787 1,156 5.0 3.3 0.00 0.55 3134087587
select * from msginfo_all where msg_id=:p1
4,648 90 51.6 2.7 0.00 0.51 1112211039
begin smsc_util.modify_destinfo(:p1,:p2,:p3,:p4,:p5,:p6,:p7,:p8,:p9,:p10,:p11,:p12,:p13,:p14); end;
4,031 90 44.8 2.3 0.00 0.23 3842824015
UPDATE destinfo SET sub_isdn = :b14, NonMmsEmailAddr = :b13, sub_state= :b12,
local_time = :b11, desiredTime =:b10, dest_inf = :b9, msg_num = :b8, done_msg_info = :b7, mailN
3,293 67 49.1 1.9 0.00 0.16 2175688974
insert into msginfo values(:p1,:p2,:p3,:p4,:p5,:p6,:p7,:p8,:p9,
:p10,:p11,:p12,:p13,:p14,:p15,:p16,:p17,:p18,:p19,:p20,:p21,:p22
,:p23,:p24,:p25,:p26,:p27,:p28,:p29,:p30,:p31,:p32,:p33,:p34,:p3
5,:p36,:p37,:p38,:p39,:p40,:p41,:p42,:p43,:p44,:p45,:p46,:p47,:p
48,:p49,:p50,:p51,:p52,:p53,:p54,:p55,:p56,:p57,:p58,:p59,:p60,:
2,149 28 76.8 1.2 0.00 0.21 3752979796
delete from Refence_tabwhere SrvMsgID=:p1 and dest_addr=:p2
1,888 118 16.0 1.1 0.00 0.08 2500993063
select ISDN,pass from smReq where isDel = '0'
1,888 118 16.0 1.1 0.00 0.11 4041890278
delete smReq where isDel = '1' and regType = '0'
1,842 56 32.9 1.1 0.00 0.27 1728016048
begin smsc_util.OriginDone(:p1, :p2); end;
1,679 23 73.0 1.0 0.00 0.05 156420249
select Refrence, orig_addr, MMType from Refence_tabwhere SrvMsgID=:p1 and dest_addr=:p2
930 198 4.7 0.5 0.00 0.06 3744392743
UPDATE origininfo set sendnum=:b3, MsgList=:b2 where sub_isdn=:b1
717 220 3.3 0.4 0.00 0.13 3466225112
SELECT sendnum, MsgList from origininfo where sub_isdn=:b1
640 40 16.0 0.4 0.00 0.03 776298407
select count(*) from smReq
527 42 12.5 0.3 0.00 0.12 3721822251
INSERT INTO destinfo VALUES(:b1, :b2, :b3, :b4, :b5, :b6, :b7, :b8, :b9,:b10, :b11, :b12, :b13, :b14)
187 48 3.9 0.1 0.00 0.23 4009906312
select bMonthBillState, bMsgStatus from monthbillinfo where wYear=:p1 and bMonth=:p2 and tBillAddr=:p3 and tVaspId=:p4 and tVasId=:p5 and tServiceCode=:p6 and tDestAddr=:p7
171 12 14.3 0.1 0.00 0.08 664582079
begin smsc_util.Auto_OpenSubServ(:p1,:p2,:p3,:p4,:p5,:p6,:p7,:p8,:p9,:p10,:p11,:p12,:p13,:p14,:p15,:p16,:p17,:p18,:p19,:p20,:p21,:p22); end;
140 20 7.0 0.1 0.00 0.01 2872798779
select count(*) from myuser.sys_var
139 22 6.3 0.1 0.00 0.01 714875384
INSERT into origininfo values(:b1, :b3, :b2)
114 57 2.0 0.1 0.00 0.02 2963598673
select job, nvl2(last_date, 1, 0) from sys.job$ where (((:1 <= next_date) and (next_date < :2)) or ((last_date is null) and (next_date < :3))) and (field1 = :4 or (field1 = 0 and 'Y' = :5) ) and (this_date is null) order by next_date, job
110 12 9.2 0.1 0.00 0.02 1315078830
INSERT INTO userinfo VALUES(:b22 ,:b21 ,:b20 ,:b19 ,:b18
-------------------------------------------------------------
说明：
就是通过执行了多少个逻辑IO来排序
--------------------------9.按物理IO进行排序的SQL------------------------------------------------
SQL ordered by Reads for DB: MYDB Instance: mydb Snaps: 1 -2
-> End Disk Reads Threshold: 1000
CPU Elapsd
Physical Reads Executions Reads per Exec %Total Time (s) Time (s) Hash Value
--------------- ------------ -------------- ------ -------- --------- ----------
246 1,962 0.1 31.5 0.00 1.72 2431777133
select * from destinfo where sub_isdn=:p1
149 20 7.5 19.1 0.00 5.03 1027916473
select count(*) from myuser.userbaseinfo
110 2,907 0.0 14.1 0.00 0.93 1077832894
select * from userinfo where sub_isdn=:p1
100 1,156 0.1 12.8 0.00 0.55 3134087587
select * from msginfo_all where msg_id=:p1
89 1 89.0 11.4 0.00 2.46 2700929825
Module: SQL*Plus BEGIN statspack.snap(); END;
31 2,435 0.0 4.0 0.00 0.70 2271041384
select * from msginfo where msg_id=:p1
31 48 0.6 4.0 0.00 0.23 4009906312
select bMonthBillState, bMsgStatus from monthbillinfo where wYear=:p1 and bMonth=:p2 and tBillAddr=:p3 and tVaspId=:p4 and tVasId=:p5 and tServiceCode=:p6 and tDestAddr=:p7
-------------------------------------------------------------
说明：
如果发现系统的IO有限制，则可检查上述的SQL语句。
--------------------------------10.按执行次数排序的SQL-----------------------------------------
SQL ordered by Executions for DB: MYDB Instance: mydb Snaps: 1 -2
-> End Executions Threshold: 100
CPU per Elap per
Executions Rows Processed Rows per Exec Exec (s) Exec (s) Hash Value
------------ --------------- ---------------- ----------- ---------- ----------
2,907 904 0.3 0.00 0.00 1077832894
select * from userinfo where sub_isdn=:p1
2,435 2,435 1.0 0.00 0.00 2271041384
select * from msginfo where msg_id=:p1
1,962 1,893 1.0 0.00 0.00 2431777133
select * from destinfo where sub_isdn=:p1
1,291 1,291 1.0 0.00 0.00 1385081364
insert into Refence_tabvalues(:p1, :p2, :p3, :p4, :p5,:p6)
1,156 1,156 1.0 0.00 0.00 3134087587
select * from msginfo_all where msg_id=:p1
220 198 0.9 0.00 0.00 3466225112
SELECT sendnum, MsgList from origininfo where sub_isdn=:b1
198 198 1.0 0.00 0.00 3744392743
UPDATE origininfo set sendnum=:b3, MsgList=:b2 where sub_isdn=:b1
132 3,171 24.0 0.00 0.00 2785281485
update msginfo set Orig_Addr=:p1,Dest_Addr=:p2,service_type=:p3
,sub_serv_type=:p4,TransactionID=:p5,Priority=:p6,state=:p7,Send
Count=:p8,errorCode=:p9,finalDate=:p10,smFlag=:p11,tVaspId=:p12,
tVasId=:p13,tServiceCode=:p14,DateTime=:p15,DeliveryTime=:p16,Re
adReply=:p17,bAdaptations=:p18,ContentType=:p19,bMsgDistributeIn
118 0 0.0 0.00 0.00 2500993063
select ISDN,pass from smReq where isDel = '0'
118 0 0.0 0.00 0.00 4041890278
delete smReq where isDel = '1' and regType = '0'
90 90 1.0 0.00 0.01 1112211039
begin smsc_util.modify_destinfo(:p1,:p2,:p3,:p4,:p5,:p6,:p7,:p8,:p9,:p10,:p11,:p12,:p13,:p14); end;
90 0 0.0 0.00 0.00 1912568914
COMMIT WORK
90 1,152 12.8 0.00 0.00 3842824015
UPDATE destinfo SET sub_isdn = :b14, NonMmsEmailAddr = :b13, sub_state = :b12,
local_time = :b11, desiredTime =:b10, dest_info = :b9, msg_num = :b8, done_msg_info = :b7, mailN
68 0 0.0 0.00 0.00 3615375148
COMMIT
67 1,206 18.0 0.00 0.00 2175688974
insert into msginfo values(:p1,:p2,:p3,:p4,:p5,:p6,:p7,:p8,:p9,
:p10,:p11,:p12,:p13,:p14,:p15,:p16,:p17,:p18,:p19,:p20,:p21,:p22
,:p23,:p24,:p25,:p26,:p27,:p28,:p29,:p30,:p31,:p32,:p33,:p34,:p3
5,:p36,:p37,:p38,:p39,:p40,:p41,:p42,:p43,:p44,:p45,:p46,:p47,:p
48,:p49,:p50,:p51,:p52,:p53,:p54,:p55,:p56,:p57,:p58,:p59,:p60,:
59 0 0.0 0.00 0.00 4064019595
select ISDN from syncsubReq where regType='D'
57 0 0.0 0.00 0.00 2963598673
select job, nvl2(last_date, 1, 0) from sys.job$ where (((:1 <= n
ext_date) and (next_date < :2)) or ((last_date is null) and
(next_date < :3))) and (field1 = :4 or (field1 = 0 and 'Y' = :5)
) and (this_date is null) order by next_date, job
57 57 1.0 0.00 0.00 2964743345
select count(*) from sys.job$ where (next_date > sysdate) and (n
ext_date < (sysdate+5/86400))
56 56 1.0 0.00 0.00 1728016048
begin smsc_util.OriginDone(:p1, :p2); end;
48 43 0.9 0.00 0.00 4009906312
select bMonthBillState, bMsgStatus from monthbillinfo where wYea
r=:p1 and bMonth=:p2 and tBillAddr=:p3 and tVaspId=:p4 and tVasI
d=:p5 and tServiceCode=:p6 and tDestAddr=:p7
42 81 1.9 0.00 0.00 3721822251
INSERT INTO destinfo VALUES(:b1, :b2, :b3,:b4, :b5, :b6, :b7, :b8, :b9,
:b10, :b11, :b12, :b13, :b14)
40 40 1.0 0.00 0.00 776298407
select count(*) from smReq
28 26 0.9 0.00 0.01 3752979796
delete from Refence_tabwhere SrvMsgID=:p1 and dest_addr=:p2
23 10 0.4 0.00 0.00 156420249
select Refrence, orig_addr, MMType from Refence_tabwhere SrvMsgID=:p1 and dest_addr=:p2
22 22 1.0 0.00 0.00 714875384
INSERT into origininfo values(:b1, :b3, :b2)
20 20 1.0 0.00 0.25 1027916473
select count(*) from myuser.userbaseinfo
20 20 1.0 0.00 0.00 2872798779
select count(*) from myuser.sys_var
12 12 1.0 0.00 0.01 664582079
begin smsc_util.Auto_OpenSubServ(:p1,:p2,:p3,:p4,:p5,:p6,:p7,:p8
,:p9,:p10,:p11,:p12,:p13,:p14,:p15,:p16,:p17,:p18,:p19,:p20,:p21
,:p22); end;
12 12 1.0 0.00 0.00 1315078830
INSERT INTO userinfo VALUES(:b22 ,:b21 ,:b20 ,:b19 ,:b18
, :b17 ,:b16 ,:b15 ,:b14 ,:b13 , :b12 ,:b11 ,:b10 ,:b9 ,:b8
, :b7 ,:b6 ,:b5 ,:b4 ,:b3 , :b2 ,:b1 ,to_date('19940101','yy
yymmdd'),to_date('19940101','yyyymmdd'))
12 12 1.0 0.00 0.00 3365174823
INSERT INTO userbaseinfo (sub_isdn) VALUES (:b1)
-------------------------------------------------------------
说明：
对于频繁执行的SQL语句，应注意跟踪。
----------------------------11.按分析次数排序的SQL-----------------------------------------
SQL ordered by Parse Calls for DB: MYDB Instance: mydb Snaps: 1 -2
-> End Parse Calls Threshold: 1000
% Total
Parse Calls Executions Parses Hash Value
------------ ------------ -------- ----------
1,291 1,291 67.38 1385081364
insert into Refence_tabvalues(:p1, :p2, :p3, :p4, :p5,:p6)
118 118 6.16 2500993063
select ISDN,pass from smReq where isDel = '0'
118 118 6.16 4041890278
delete smReq where isDel = '1' and regType = '0'
59 59 3.08 4064019595
select ISDN from syncsubReq where regType='D'
48 48 2.51 4009906312
select bMonthBillState, bMsgStatus from monthbillinfo where wYear=:p1 and bMonth=:p2 and tBillAddr=:p3 and tVaspId=:p4 and tVasId=:p5 and tServiceCode=:p6 and DestAddr=:p7
40 40 2.09 776298407
select count(*) from smReq
28 28 1.46 3752979796
delete from Refence_tabwhere SrvMsgID=:p1 and dest_addr=:p2
23 23 1.20 156420249
select Refrence, orig_addr, MMType from Refence_tabwhere SrvMsgID=:p1 and dest_addr=:p2
20 20 1.04 1027916473
select count(*) from myuser.userbaseinfo
20 20 1.04 2872798779
select count(*) from myuser.sys_var
12 12 0.63 664582079
begin smsc_util.Auto_OpenSubServ(:p1,:p2,:p3,:p4,:p5,:p6,:p7,:p8,:p9,:p10,:p11,:p12,:p13,:p14,:p15,:p16,:p17,:p18,:p19,:p20,:p21,:p22); end;
5 5 0.26 122554595
insert into monthbillinfo values(:p1, :p2, :p3, :p4, :p5, :p6, :p7, :p8, :p9)
2 2 0.10 3949568363
update userinfo set sub_property=:p1, sub_auth=:p2, profile_id=:p3, UaCapacity=:p4, cug_flag=:p5, cug_id=:p6, NonMmsEmailAddr=:p7, bForwardAddrFg=:p8, tForwardAddr=:p9, bBlackUserNum=:p10, tBlackUserList=:p11, MMAutoSave=:p12, MsgLenthLimit=:p13, MsgfilterTag=:p14, bMoCopyAddrFg=:p15, tMoCopyAddr=:p16, bWhiteUserN
-------------------------------------------------------------
--------------------------12.实例的当前活动的统计数据---------------------------------------------
Instance Activity Stats for DB: MYDB Instance: mydb Snaps: 1 -2
Statistic Total per Second per Trans
--------------------------------- ------------------ -------------- ------------
CR blocks created 8 0.0 0.0
DBWR buffers scanned 0 0.0 0.0
DBWR checkpoint buffers written 2,402 8.2 1.4
DBWR checkpoints 0 0.0 0.0
DBWR free buffers found 0 0.0 0.0
DBWR lru scans 0 0.0 0.0
DBWR make free requests 0 0.0 0.0
DBWR revisited being-written buff 0 0.0 0.0
DBWR summed scan depth 0 0.0 0.0
DBWR transaction table writes 7 0.0 0.0
DBWR undo block writes 347 1.2 0.2
SQL*Net roundtrips to/from client 10,823 36.8 6.4
active txn count during cleanout 229 0.8 0.1
background checkpoints completed 0 0.0 0.0
background checkpoints started 0 0.0 0.0
background timeouts 347 1.2 0.2
branch node splits 0 0.0 0.0
buffer is not pinned count 106,967 363.8 63.6
buffer is pinned count 16,784 57.1 10.0
bytes received via SQL*Net from c 4,449,217 15,133.4 2,646.8
bytes sent via SQL*Net to client 7,342,023 24,972.9 4,367.7
calls to get snapshot scn: kcmgss 22,055 75.0 13.1
calls to kcmgas 1,742 5.9 1.0
calls to kcmgcs 216 0.7 0.1
change write time 22 0.1 0.0
cleanout - number of ktugct calls 256 0.9 0.2
cleanouts and rollbacks - consist 3 0.0 0.0
cleanouts only - consistent read 6 0.0 0.0
cluster key scan block gets 0 0.0 0.0
cluster key scans 0 0.0 0.0
commit cleanout failures: buffer 0 0.0 0.0
commit cleanout failures: callbac 6 0.0 0.0
commit cleanout failures: cannot 1 0.0 0.0
commit cleanouts 7,237 24.6 4.3
commit cleanouts successfully com 7,230 24.6 4.3
commit txn count during cleanout 52 0.2 0.0
consistent changes 14 0.1 0.0
consistent gets 151,986 517.0 90.4
consistent gets - examination 34,097 116.0 20.3
current blocks converted for CR 0 0.0 0.0
cursor authentications 3 0.0 0.0
data blocks consistent reads - un 14 0.1 0.0
db block changes 22,813 77.6 13.6
db block gets 22,437 76.3 13.4
deferred (CURRENT) block cleanout 4,759 16.2 2.8
dirty buffers inspected 0 0.0 0.0
enqueue conversions 25 0.1 0.0
enqueue releases 6,184 21.0 3.7
enqueue requests 6,184 21.0 3.7
enqueue timeouts 0 0.0 0.0
enqueue waits 0 0.0 0.0
exchange deadlocks 0 0.0 0.0
execute count 11,653 39.6 6.9
free buffer inspected 0 0.0 0.0
free buffer requested 1,583 5.4 0.9
hot buffers moved to head of LRU 14 0.1 0.0
immediate (CR) block cleanout app 9 0.0 0.0
immediate (CURRENT) block cleanou 137 0.5 0.1
index fast full scans (full) 0 0.0 0.0
index fetch by key 9,759 33.2 5.8
index scans kdiixs1 19,989 68.0 11.9
leaf node 90-10 splits 13 0.0 0.0
leaf node splits 46 0.2 0.0
logons cumulative 2 0.0 0.0
messages received 1,769 6.0 1.1
messages sent 1,769 6.0 1.1
no buffer to keep pinned count 0 0.0 0.0
no work - consistent read gets 107,624 366.1 64.0
opened cursors cumulative 507 1.7 0.3
parse count (failures) 0 0.0 0.0
parse count (hard) 16 0.1 0.0
parse count (total) 1,916 6.5 1.1
physical reads 780 2.7 0.5
physical reads direct 0 0.0 0.0
physical writes 2,402 8.2 1.4
physical writes direct 0 0.0 0.0
physical writes non checkpoint 760 2.6 0.5
pinned buffers inspected 0 0.0 0.0
prefetch clients - default 0 0.0 0.0
prefetched blocks 132 0.5 0.1
prefetched blocks aged out before 35 0.1 0.0
recursive calls 3,723 12.7 2.2
redo blocks written 23,853 81.1 14.2
redo buffer allocation retries 0 0.0 0.0
redo entries 11,949 40.6 7.1
redo log space requests 0 0.0 0.0
redo log space wait time 0 0.0 0.0
redo ordering marks 0 0.0 0.0
redo size 11,318,684 38,498.9 6,733.3
redo synch time 155 0.5 0.1
redo synch writes 1,681 5.7 1.0
redo wastage 615,032 2,092.0 365.9
redo write time 129 0.4 0.1
redo writer latching time 0 0.0 0.0
redo writes 1,673 5.7 1.0
rollback changes - undo records a 0 0.0 0.0
rollbacks only - consistent read 6 0.0 0.0
rows fetched via callback 4,551 15.5 2.7
session logical reads 174,423 593.3 103.8
session pga memory max 0 0.0 0.0
session uga memory 26,080 88.7 15.5
session uga memory max 222,256 756.0 132.2
shared hash latch upgrades - no w 19,425 66.1 11.6
shared hash latch upgrades - wait 0 0.0 0.0
sorts (disk) 0 0.0 0.0
sorts (memory) 214 0.7 0.1
sorts (rows) 29,541 100.5 17.6
summed dirty queue length 0 0.0 0.0
switch current to new buffer 1 0.0 0.0
table fetch by rowid 7,403 25.2 4.4
table fetch continued row 78 0.3 0.1
table scan blocks gotten 89,731 305.2 53.4
table scan rows gotten 14,759,044 50,200.8 8,779.9
table scans (direct read) 0 0.0 0.0
table scans (long tables) 0 0.0 0.0
table scans (rowid ranges) 0 0.0 0.0
table scans (short tables) 442 1.5 0.3
transaction rollbacks 0 0.0 0.0
transaction tables consistent rea 0 0.0 0.0
transaction tables consistent rea 0 0.0 0.0
user calls 11,266 38.3 6.7
user commits 1,681 5.7 1.0
user rollbacks 0 0.0 0.0
workarea executions - onepass 0 0.0 0.0
workarea executions - optimal 119 0.4 0.1
write clones created in foregroun 0 0.0 0.0
-------------------------------------------------------------
说明：
可通过是面详细数据计算各种命中率等。
如：soft parse= (1-hard parse/total parse)*100%= (1-16/1916)*100%=99.16%就是报表开头的soft parse值。
---------------------------------13.tablespace IO统计数据------------------------------------------
Tablespace IO Stats for DB: MYDB Instance: mydb Snaps: 1 -2
->ordered by IOs (Reads + Writes) desc
Tablespace
------------------------------
Av Av Av Av Buffer Av Buf
Reads Reads/s Rd(ms) Blks/Rd Writes Writes/s Waits Wt(ms)
-------------- ------- ------ ------- ------------ -------- ---------- ------
USERS
444 2 5.0 1.3 874 3 0 0.0
SHTMSG
131 0 1.9 1.0 1,005 3 1 0.0
UNDOTBS
0 0 0.0 353 1 1 0 0.0
IDEX
73 0 3.7 1.0 147 1 0 0.0
SYSTEM
0 0 0.0 11 0 0 0.0
-------------------------------------------------------------
---------------------------------14.表空间文件 IO统计数据------------------------------------------
File IO Stats for DB: MYDB Instance: mydb Snaps: 1 -2
->ordered by Tablespace, File
Tablespace Filename
------------------------ ----------------------------------------------------
Av Av Av Av Buffer Av Buf
Reads Reads/s Rd(ms) Blks/Rd Writes Writes/s Waits Wt(ms)
-------------- ------- ------ ------- - ----------- -------- --------- ------
IDEX /dev/lvidex
73 0 3.7 1.0 147 1 0
SHTMSG /dev/lvshtmsg
131 0 1.9 1.0 1,005 3 1 0.0
SYSTEM /dev/lvsystem
0 0 11 0 0
UNDOTBS /dev/lvundotbs
0 0 353 1 1 0.0
USERS /dev/lvusers
444 2 5.0 1.3 874 3 0
-------------------------------------------------------------
---------------------------------15.buffer池统计数据------------------------------------------
Buffer Pool Statistics for DB: MYDB Instance: mydb Snaps: 1 -2
-> Standard block size Pools D: default, K: keep, R: recycle
-> Default Pools for other block sizes: 2k, 4k, 8k, 16k, 32k
Free Write Buffer
Number of Cache Buffer Physical Physical Buffer Complete Busy
P Buffers Hit % Gets Reads Writes Waits Waits Waits
--- ---------- ----- ----------- ----------- ---------- ------- -------- ------
D 190,560 99.7 258,404 780 2,390 0 0 2
-------------------------------------------------------------
----------------------------------16.实例恢复统计数据------------------------------------------
Instance Recovery Stats for DB: MYDB Instance: mydb Snaps: 1 -2
-> B: Begin snapshot, E: End snapshot
Targt Estd Log File Log Ckpt Log Ckpt
MTTR MTTR Recovery Actual Target Size Timeout Interval
(s) (s) Estd IOs Redo Blks Redo Blks Redo Blks Redo Blks Redo Blks
- ----- ----- ---------- ---------- ---------- ---------- ---------- ----------
B 161 28 11145 100666 100000 450000 207466 100000
E 161 28 11608 100043 100000 450000 199928 100000
-------------------------------------------------------------
----------------------------------17.Buffer池的参考数据-----------------------------------------
Buffer Pool Advisory for DB: MYDB Instance: mydb End Snap: 2
-> Only rows with estimated physical reads >0 are displayed
-> ordered by Block Size, Buffers For Estimate
Size for Size Buffers for Est Physical Estimated
P Estimate (M) Factr Estimate Read Factor Physical Reads
--- ------------ ----- ---------------- ------------- ------------------
D 160 .1 19,850 1.92 81,500,147
D 320 .2 39,700 1.69 71,754,201
D 480 .3 59,550 1.57 66,774,777
D 640 .4 79,400 1.49 63,154,327
D 800 .5 99,250 1.40 59,432,741
D 960 .6 119,100 1.22 52,009,931
D 1,120 .7 138,950 1.08 46,041,143
D 1,280 .8 158,800 1.04 44,306,471
D 1,440 .9 178,650 1.01 42,974,849
D 1,536 1.0 190,560 1.00 42,478,849
D 1,600 1.0 198,500 0.99 42,032,350
D 1,760 1.1 218,350 0.97 41,128,356
D 1,920 1.3 238,200 0.94 39,877,785
D 2,080 1.4 258,050 0.92 39,058,138
D 2,240 1.5 277,900 0.91 38,475,363
D 2,400 1.6 297,750 0.88 37,173,210
D 2,560 1.7 317,600 0.85 35,983,344
D 2,720 1.8 337,450 0.83 35,325,000
D 2,880 1.9 357,300 0.81 34,595,023
D 3,040 2.0 377,150 0.79 33,731,234
D 3,200 2.1 397,000 0.77 32,711,726
-------------------------------------------------------------
----------------------------------18.Buffer等待统计数据------------------------------------------
Buffer wait Statistics for DB: MYDB Instance: mydb Snaps: 1 -2
-> ordered by wait time desc, waits desc
Tot Wait Avg
Class Waits Time (s) Time (ms)
------------------ ----------- ---------- ---------
data block 1 0 0
undo block 1 0 0
-------------------------------------------------------------
----------------------------------19.PGA总体统计数据 1------------------------------------------
PGA Aggr Target Stats for DB: MYDB Instance: mydb Snaps: 1 -2
-> B: Begin snap E: End snap (rows dentified with B or E contain data
which is absolute i.e. not diffed over the interval)
-> PGA cache hit % - percentage of W/A (WorkArea) data processed only in-memory
-> Auto PGA Target - actual workarea memory target
-> W/A PGA Used - amount of memory used for all Workareas (manual + auto)
-> %PGA W/A Mem - percentage of PGA memory allocated to workareas
-> %Auto W/A Mem - percentage of workarea memory controlled by Auto Mem Mgmt
-> %Man W/A Mem - percentage of workarea memory under manual control
PGA Cache Hit % W/A MB Processed Extra W/A MB Read/Written
--------------- ---------------- - ------------------------
100.0 7 0
%PGA %Auto %Man
PGA Aggr Auto PGA PGA Mem W/A PGA W/A W/A W/A Global Mem
Target(M) Target(M) Alloc(M) Used(M) Mem Mem Mem Bound(K)
- --------- --------- ---------- ---------- ------ ------ ------ ----------
B 200 174 27.1 0.0 .0 .0 .0 10,240
E 200 174 27.1 0.0 .0 .0 .0 10,240
-------------------------------------------------------------
----------------------------------20.PGA统计数据 2------------------------------------------
PGA Aggr Target Histogram(柱状图) for DB: MYDB Instance: mydb Snaps: 1 -2
-> Optimal Executions are purely in-memory operations
Low High
Optimal Optimal Total Execs Optimal Execs 1-Pass Execs M-Pass Execs
------- ------- -------------- ------------- ----------- ------------
8K 16K 98 98 0 0
16K 32K 12 12 0 0
32K 64K 2 2 0 0
64K 128K 3 3 0 0
2M 4M 2 2 0 0
-------------------------------------------------------------
----------------------------------21.PGA内存参考数据 ------------------------------------------
PGA Memory Advisory for DB: MYDB Instance: mydb End Snap: 2
-> When using Auto Memory Mgmt, minimally choose a pga_aggregate_target value
where Estd PGA Overalloc Count is 0
Estd Extra Estd PGA Estd PGA
PGA Target Size W/A MB W/A MB Read/ Cache Overalloc
Est (MB) Factr Processed Written to Disk Hit % Count
---------- ------- ---------------- ---------------- -------- ----------
25 0.1 6,180.6 3,466.6 64.0 0
50 0.3 6,180.6 3,459.1 64.0 0
100 0.5 6,180.6 861.1 88.0 0
150 0.8 6,180.6 861.1 88.0 0
200 1.0 6,180.6 861.1 88.0 0
240 1.2 6,180.6 861.1 88.0 0
280 1.4 6,180.6 861.1 88.0 0
320 1.6 6,180.6 861.1 88.0 0
360 1.8 6,180.6 861.1 88.0 0
400 2.0 6,180.6 861.1 88.0 0
600 3.0 6,180.6 861.1 88.0 0
800 4.0 6,180.6 861.1 88.0 0
1,200 6.0 6,180.6 861.1 88.0 0
1,600 8.0 6,180.6 629.9 91.0 0
-------------------------------------------------------------
----------------------------------22.回滚段统计------------------------------------------
Rollback Segment Stats for DB: MYDB Instance: mydb Snaps: 1 -2
->A high value for "Pct Waits" suggests more rollback segments may be required
->RBS stats may not be accurate between begin and end snaps when using Auto Undo
managment, as RBS may be dynamically created and dropped as needed
Trans Table Pct Undo Bytes
RBS No Gets Waits Written Wraps Shrinks Extends
------ -------------- ------- --------------- ------- -------- --------
0 2.0 0.00 0 0 0 0
1 345.0 0.00 388,926 1 0 0
2 458.0 0.00 519,636 1 0 0
3 397.0 0.00 348,516 1 0 0
4 330.0 0.00 319,184 0 0 0
5 467.0 0.00 561,346 0 0 0
6 403.0 0.00 387,552 0 0 0
7 431.0 0.00 711,298 0 0 0
8 444.0 0.00 348,148 1 0 0
9 393.0 0.00 376,176 1 0 0
10 334.0 0.00 320,640 0 0 0
-------------------------------------------------------------
----------------------------------23.回滚段存储统计------------------------------------------
Rollback Segment Storage for DB: MYDB Instance: mydb Snaps: 1 -2
->Optimal Size should be larger than Avg Active
RBS No Segment Size Avg Active Optimal Size Maximum Size
------ --------------- --------------- --------------- ---------------
0 385,024 0 385,024
1 24,240,128 953,522 38,920,192
2 29,483,008 1,012,839 50,520,064
3 29,483,008 999,571 50,454,528
4 27,385,856 943,570 38,920,192
5 28,434,432 965,310 117,563,392
6 30,531,584 1,036,116 84,008,960
7 27,385,856 918,922 151,183,360
8 28,434,432 1,009,932 50,520,064
9 29,483,008 1,036,116 50,454,528
10 25,288,704 953,522 37,871,616
-------------------------------------------------------------
----------------------------------24.undo段总体情况----------------------------------------
Undo Segment Summary for DB: MYDB Instance: mydb Snaps: 1 -2
-> Undo segment block stats:
-> uS - unexpired Stolen, uR - unexpired Released, uU - unexpired reUsed
-> eS - expired Stolen, eR - expired Released, eU - expired reUsed
Undo Undo Num Max Qry Max Tx Snapshot Out of uS/uR/uU/
TS# Blocks Trans Len (s) Concurcy Too Old Space eS/eR/eU
---- - ------------- ---------- -------- ---------- -------- ------ -------------
1 2,970 ########## 357 2 0 0 0/0/0/0/0/0
-------------------------------------------------------------
----------------------------------25.undo段统计------------------------------------------
Undo Segment Stats for DB: MYDB Instance: mydb Snaps: 1 -2
-> ordered by Time desc
Undo Num Max Qry Max Tx Snap Out of uS/uR/uU/
End Time Blocks Trans Len (s) Concy Too Old Space eS/eR/eU
------------ ------------ -------- ------- -------- ------- ------ -------------
09-Aug 19:42 1,306 ######## 16 2 0 0 0/0/0/0/0/0
09-Aug 19:32 1,664 ######## 357 2 0 0 0/0/0/0/0/0
-------------------------------------------------------------
----------------------------------26.锁存器的当前情况------------------------------------------
Latch Activity for DB: MYDB Instance: mydb Snaps: 1 -2
->"Get Requests", "Pct Get Miss" and "Avg Slps/Miss" are statistics for
willing-to-wait latch get requests
->"NoWait Requests", "Pct NoWait Miss" are for no-wait latch get requests
->"Pct Misses" for both should be very close to 0.0
Pct Avg Wait Pct
Get Get Slps Time NoWait NoWait
Latch Requests Miss /Miss (s) Requests Miss
------------------------ -------------- ------ ------ ------ ------------ ------
Consistent RBA 1,673 0.0 0 0
SQL memory manager latch 1 0.0 0 96 0.0
SQL memory manager worka 6,565 0.0 0 0
active checkpoint queue 192 0.0 0 0
cache buffer handles 452 0.0 0 0
cache buffers chains 366,694 0.0 0.0 0 1,707 0.0
cache buffers lru chain 3,980 0.0 0 552 0.0
channel handle pool latc 4 0.0 0 0
channel operations paren 199 0.0 0 0
checkpoint queue latch 13,445 0.1 0.0 0 2,741 0.0
child cursor hash table 201 0.0 0 0
dml lock allocation 8,361 0.0 0 0
dummy allocation 4 0.0 0 0
enqueue hash chains 12,371 0.0 0.0 0 0
enqueues 2,662 0.0 0 0
event group latch 2 0.0 0 0
file number translation 14 0.0 0 0
hash table column usage 7 0.0 0 0
job_queue_processes para 5 0.0 0 0
ktm global data 1 0.0 0 0
lgwr LWN SCN 1,674 0.0 0 0
library cache 80,405 0.0 0.1 0 0
library cache load lock 46 0.0 0 0
library cache pin 28,408 0.0 0 0
library cache pin alloca 7,888 0.0 0 0
list of block allocation 8 0.0 0 0
messages 12,581 0.0 0.0 0 0
mostly latch-free SCN 1,733 2.4 0.0 0 0
multiblock read objects 54 0.0 0 0
ncodef allocation latch 5 0.0 0 0
post/wait queue 3,346 0.0 0 1,681 0.0
process allocation 2 0.0 0 2 0.0
process group creation 4 0.0 0 0
redo allocation 15,281
监控数据库性能的SQL语句
1. 监控事例的等待
select event,
sum(decode(wait_Time,0,0,1)) "Prev",
sum(decode(wait_Time,0,1,0)) "Curr",
count(*) "Tot"
from v$session_Wait
group by event
order by 4;
2. 回滚段的争用情况
select name, waits, gets, waits/gets "Ratio"
from v$rollstat a, v$rollname b
where a.usn = b.usn;
3. 监控表空间的 I/O 比例
select df.tablespace_name name,df.file_name "file",f.phyrds pyr,
f.phyblkrd pbr,f.phywrts pyw, f.phyblkwrt pbw
from v$filestat f, dba_data_files df
where f.file# = df.file_id
order by df.tablespace_name;
4. 监控文件系统的 I/O 比例
select substr(a.file#,1,2) "#", substr(a.name,1,30) "Name",
a.status, a.bytes, b.phyrds, b.phywrts
from v$datafile a, v$filestat b
where a.file# = b.file#;
5.在某个用户下找所有的索引
select user_indexes.table_name,
user_indexes.index_name,
uniqueness,
column_name
from user_ind_columns, user_indexes
where user_ind_columns.index_name = user_indexes.index_name and
user_ind_columns.table_name = user_indexes.table_name
order by user_indexes.table_type, user_indexes.table_name,
user_indexes.index_name, column_position;
6. 监控 SGA 的命中率
select a.value + b.value "logical_reads",
c.value "phys_reads",
round(100 * ((a.value+b.value)-c.value) / (a.value+b.value)) "BUFFER HIT RATIO"
from v$sysstat a, v$sysstat b, v$sysstat c
where a.statistic# = 38 and b.statistic# = 39 and
c.statistic# = 40;
7. 监控 SGA 中字典缓冲区的命中率
select parameter, gets,Getmisses ,
getmisses/(gets+getmisses)*100 "miss ratio",
(1-(sum(getmisses)/ (sum(gets)+sum(getmisses))))*100 "Hit ratio"
from v$rowcache
where gets+getmisses <>0
group by parameter, gets, getmisses;
8. 监控 SGA 中共享缓存区的命中率，应该小于1%
select sum(pins) "Total Pins", sum(reloads) "Total Reloads",
sum(reloads)/sum(pins) *100 libcache
from v$librarycache;
select sum(pinhits-reloads)/sum(pins) "hit radio",
sum(reloads)/sum(pins) "reload percent"
from v$librarycache;
9. 显示所有数据库对象的类别和大小
select type,
count(name) num_instances,
sum(source_size) source_size,
sum(parsed_size) parsed_size,
sum(code_size) code_size,
sum(error_size) error_size,
sum(source_size) +sum(parsed_size) +sum(code_size) +sum(error_size) size_required
from dba_object_size
group by type
order by 1;
10. 监控 SGA 中重做日志缓存区的命中率，应该小于1%
SELECT name, gets, misses, immediate_gets, immediate_misses,
Decode(gets,0,0,misses/gets*100) ratio1,
Decode(immediate_gets+immediate_misses,0,0,
immediate_misses/(immediate_gets+immediate_misses)*100) ratio2
FROM v$latch
WHERE name IN ('redo allocation', 'redo copy');
11. 监控内存和硬盘的排序比率，最好使它小于 .10，增加 sort_area_size
SELECT name, value
FROM v$sysstat
WHERE name IN ('sorts (memory)', 'sorts (disk)');
12. 监控当前数据库谁在运行什么SQL语句
SELECT osuser, username, sql_text
from v$session a, v$sqltext b
where a.sql_address =b.address order by address, piece;
13. 监控字典缓冲区
SELECT (SUM(PINS - RELOADS)) / SUM(PINS) "LIB CACHE"
FROM V$LIBRARYCACHE;
SELECT (SUM(GETS - GETMISSES - USAGE - FIXED)) / SUM(GETS) "ROW CACHE"
FROM V$ROWCACHE;
SELECT SUM(PINS) "EXECUTIONS", SUM(RELOADS) "CACHE MISSES WHILE EXECUTING"
FROM V$LIBRARYCACHE;
后者除以前者,此比率小于1%,接近0%为好。
SELECT SUM(GETS) "DICTIONARY GETS",SUM(GETMISSES) "DICTIONARY CACHE GET MISSES"
FROM V$ROWCACHE;
14. 找ORACLE字符集
select * from sys.props$ where name='NLS_CHARACTERSET';
15. 监控 MTS
select busy/(busy+idle) "shared servers busy" from v$dispatcher;
此值大于0.5时，参数需加大
select sum(wait)/sum(totalq) "dispatcher waits" from v$queue where type='dispatcher';
select count(*) from v$dispatcher;
select servers_highwater from v$mts;
servers_highwater接近mts_max_servers时，参数需加大
16. 碎片程度
select tablespace_name,count(tablespace_name)
from dba_free_space
group by tablespace_name
having count(tablespace_name)>10;
alter tablespace name coalesce;
alter table name deallocate unused;
create or replace view ts_blocks_v as
select tablespace_name,block_id,bytes,blocks,
segment_name
from dba_free_space
union all
select tablespace_name,block_id,bytes,blocks,
segment_name
from dba_extents;
select * from ts_blocks_v;
select tablespace_name,sum(bytes),max(bytes),count(block_id)
from dba_free_space
group by tablespace_name;
查看碎片程度高的表
SELECT segment_name table_name , COUNT(*) extents
FROM dba_segments
WHERE owner NOT IN ('SYS', 'SYSTEM')
GROUP BY segment_name
HAVING COUNT(*) = (SELECT MAX(COUNT(*)) FROM dba_segments GROUP BY segment_name);
17. 表、索引的存储情况检查
select segment_name,sum(bytes),count(*) ext_quan
from dba_extents
where tablespace_name='&tablespace_name' and
segment_type='TABLE'
group by tablespace_name,segment_name;
select segment_name,count(*)
from dba_extents
where segment_type='INDEX' and owner='&owner'
group by segment_name;
18、找使用CPU多的用户session
12是cpu used by this session
select a.sid,spid,status,substr(a.program,1,40) prog,
a.terminal,osuser,value/60/100 value
from v$session a,v$process b,v$sesstat c
where c.statistic#=12 and
c.sid=a.sid and
a.paddr=b.addr
order by value desc;
19.对可疑/性能不好的Server Process来进行Trace.,可以用tkprof来分析Trace的结果.比较方便.使用Unix的KSH.
(1) start_trc:
#!/bin/ksh
if (( $# != 1 ))
then
echo Usuage: start_trc pid
fi
sid_serial=$(print "
connect / as sysdba;
set heading off;
set feedback off;
select a.sid,a.serial# from v\$session a,v\$process b where a.paddr=b.addr and b.spid=$1;
exit;
" | sqlplus -s /nolog | grep -v 'Connected' | sed -e 's/\([0-9]\{1,\}\)/\1,/' -e '/^$/d' )
if [[ -z $sid_serial ]]
then
print "Seems that this process $1 is not an Oracle process!"
exit 1
fi
print "
connect / as sysdba;
execute dbms_system.set_sql_trace_in_session($sid_serial,true);
exit;
" | sqlplus -s /nolog
(2) stop_trc:
#!/bin/ksh
if (( $# != 1 ))
then
echo Usuage: stop_trc pid
fi
sid_serial=$(print "
connect / as sysdba;
set heading off;
set feedback off;
select a.sid,a.serial# from v\$session a,v\$process b where a.paddr=b.addr and b.spid=$1;
exit;
" | sqlplus -s /nolog | grep -v 'Connected' | sed -e 's/\([0-9]\{1,\}\)/\1,/' -e '/^$/d' )
if [[ -z $sid_serial ]]
then
print "Seems that this process $1 is not an Oracle process!"
exit 1
fi
print "
connect / as sysdba;
execute dbms_system.set_sql_trace_in_session($sid_serial,false);
exit;
" | sqlplus -s /nolog
20.查看Lock
SELECT sn.username, m.sid, m.type,
DECODE(m.lmode, 0, 'None',
1, 'Null',
2, 'Row Share',
3, 'Row Excl.',
4, 'Share',
5, 'S/Row Excl.',
6, 'Exclusive',
lmode, ltrim(to_char(lmode,'990'))) lmode,
DECODE(m.request,0, 'None',
1, 'Null',
2, 'Row Share',
3, 'Row Excl.',
4, 'Share',
5, 'S/Row Excl.',
6, 'Exclusive',
request, ltrim(to_char(m.request,'990'))) request,
m.id1, m.id2
FROM v$session sn, v$lock m
WHERE (sn.sid = m.sid AND m.request != 0) OR
( sn.sid = m.sid AND
m.request = 0 AND
lmode != 4 AND
(id1, id2) IN (
SELECT s.id1, s.id2
FROM v$lock s
WHERE request != 0 AND
s.id1 = m.id1 AND
s.id2 = m.id2
)
)
ORDER BY id1, id2, m.request;
select l.sid,s.serial#,s.username,s.terminal,
decode(l.type,'RW','RW - Row Wait Enqueue',
'TM','TM - DML Enqueue',
'TX','TX - Trans Enqueue',
'UL','UL - User',l.type||'System') res,
substr(t.name,1,10) tab,u.name owner,
l.id1,l.id2,
decode(l.lmode,1,'No Lock',
2,'Row Share',
3,'Row Exclusive',
4,'Share',
5,'Shr Row Excl',
6,'Exclusive',null) lmode,
decode(l.request,1,'No Lock',
2,'Row Share',
3,'Row Excl',
4,'Share',
5,'Shr Row Excl',
6,'Exclusive',null) request
from v$lock l, v$session s,
sys.user$ u,sys.obj$ t
where l.sid = s.sid and
s.type != 'BACKGROUND' and
t.obj# = l.id1 and
u.user# = t.owner#;
以下的方式可以監控登入登出的用戶:
創建如下的兩張表:
create table login_log -- 登入登出信息表
(
session_id int not null, -- sessionid
login_on_time date, -- 登入時間
login_off_time date, -- 登出時間
user_in_db varchar2(30), -- 登入的db user
machine varchar2(20), -- 機器名
ip_address varchar2(20), -- ip地址
run_program varchar2(20) -- 以何程序登入
);
create table allow_user -- 網域用戶表
(
ip_address varchar2(20), -- ip地址
login_user_name nvarchar2(20) -- 操作者姓名
);
創建如下的兩個觸發器:
create or replace trigger login_on_info -- 紀錄登入信息的觸發器
after logon on database
Begin
insert into login_log(session_id,login_on_time,login_off_time,user_in_db,machine,ip_address,run_program)
select AUDSID,sysdate,null,sys.login_user,machine,SYS_CONTEXT('USERENV','IP_ADDRESS'),program
from v$session where AUDSID = USERENV('SESSIONID'); --當前SESSION
END;
create or replace trigger login_off_info -- 紀錄登出信息的觸發器
before logoff on database
Begin
update login_log set login_off_time = sysdate
where session_id = USERENV('SESSIONID'); --當前SESSION
exception
when others then
null;
END;
方法二:
用如下的方式可以審計執行drop動作的事件:
/**
* drop語句的審計日誌表
*/
create table drop_log
(
session_id int not null, -- sessionid
drop_time date, -- drop的時間
ip_address varchar2(20), -- ip地址
object_owner varchar2(30), -- 對象的擁有者
object_name varchar2(30), -- 對象名稱
object_type varchar2(20), -- 對象類型
drop_by_user varchar2(30) -- 執行drop語句的用戶
);
create or replace trigger drop_info
after drop on mfg0513user.schema -- 在mfg0513user用戶上創建審計drop的觸發器
begin
insert into drop_log
(session_id,
drop_time,
ip_address,
object_owner,
object_name,
object_type,
drop_by_user)
values(USERENV('SESSIONID'),
sysdate,
SYS_CONTEXT('USERENV','IP_ADDRESS'),
sys.dictionary_obj_owner,
sys.dictionary_obj_name,
sys.dictionary_obj_type,
sys.login_user);
end;
监控数据库性能的SQL语句
1. 监控事例的等待
select event,
sum(decode(wait_Time,0,0,1)) "Prev",
sum(decode(wait_Time,0,1,0)) "Curr",
count(*) "Tot"
from v$session_Wait
group by event
order by 4;
2. 回滚段的争用情况
select name, waits, gets, waits/gets "Ratio"
from v$rollstat a, v$rollname b
where a.usn = b.usn;
3. 监控表空间的 I/O 比例
select df.tablespace_name name,df.file_name "file",f.phyrds pyr,
f.phyblkrd pbr,f.phywrts pyw, f.phyblkwrt pbw
from v$filestat f, dba_data_files df
where f.file# = df.file_id
order by df.tablespace_name;
4. 监控文件系统的 I/O 比例
select substr(a.file#,1,2) "#", substr(a.name,1,30) "Name",
a.status, a.bytes, b.phyrds, b.phywrts
from v$datafile a, v$filestat b
where a.file# = b.file#;
5.在某个用户下找所有的索引
select user_indexes.table_name,
user_indexes.index_name,
uniqueness,
column_name
from user_ind_columns, user_indexes
where user_ind_columns.index_name = user_indexes.index_name and
user_ind_columns.table_name = user_indexes.table_name
order by user_indexes.table_type, user_indexes.table_name,
user_indexes.index_name, column_position;
6. 监控 SGA 的命中率
select a.value + b.value "logical_reads",
c.value "phys_reads",
round(100 * ((a.value+b.value)-c.value) / (a.value+b.value)) "BUFFER HIT RATIO"
from v$sysstat a, v$sysstat b, v$sysstat c
where a.statistic# = 38 and b.statistic# = 39 and
c.statistic# = 40;
7. 监控 SGA 中字典缓冲区的命中率
select parameter, gets,Getmisses ,
getmisses/(gets+getmisses)*100 "miss ratio",
(1-(sum(getmisses)/ (sum(gets)+sum(getmisses))))*100 "Hit ratio"
from v$rowcache
where gets+getmisses <>0
group by parameter, gets, getmisses;
8. 监控 SGA 中共享缓存区的命中率，应该小于1%
select sum(pins) "Total Pins", sum(reloads) "Total Reloads",
sum(reloads)/sum(pins) *100 libcache
from v$librarycache;
select sum(pinhits-reloads)/sum(pins) "hit radio",
sum(reloads)/sum(pins) "reload percent"
from v$librarycache;
9. 显示所有数据库对象的类别和大小
select type,
count(name) num_instances,
sum(source_size) source_size,
sum(parsed_size) parsed_size,
sum(code_size) code_size,
sum(error_size) error_size,
sum(source_size) +sum(parsed_size) +sum(code_size) +sum(error_size) size_required
from dba_object_size
group by type
order by 1;
10. 监控 SGA 中重做日志缓存区的命中率，应该小于1%
SELECT name, gets, misses, immediate_gets, immediate_misses,
Decode(gets,0,0,misses/gets*100) ratio1,
Decode(immediate_gets+immediate_misses,0,0,
immediate_misses/(immediate_gets+immediate_misses)*100) ratio2
FROM v$latch
WHERE name IN ('redo allocation', 'redo copy');
11. 监控内存和硬盘的排序比率，最好使它小于 .10，增加 sort_area_size
SELECT name, value
FROM v$sysstat
WHERE name IN ('sorts (memory)', 'sorts (disk)');
12. 监控当前数据库谁在运行什么SQL语句
SELECT osuser, username, sql_text
from v$session a, v$sqltext b
where a.sql_address =b.address order by address, piece;
13. 监控字典缓冲区
SELECT (SUM(PINS - RELOADS)) / SUM(PINS) "LIB CACHE"
FROM V$LIBRARYCACHE;
SELECT (SUM(GETS - GETMISSES - USAGE - FIXED)) / SUM(GETS) "ROW CACHE"
FROM V$ROWCACHE;
SELECT SUM(PINS) "EXECUTIONS", SUM(RELOADS) "CACHE MISSES WHILE EXECUTING"
FROM V$LIBRARYCACHE;
后者除以前者,此比率小于1%,接近0%为好。
SELECT SUM(GETS) "DICTIONARY GETS",SUM(GETMISSES) "DICTIONARY CACHE GET MISSES"
FROM V$ROWCACHE;
14. 找ORACLE字符集
select * from sys.props$ where name='NLS_CHARACTERSET';
15. 监控 MTS
select busy/(busy+idle) "shared servers busy" from v$dispatcher;
此值大于0.5时，参数需加大
select sum(wait)/sum(totalq) "dispatcher waits" from v$queue where type='dispatcher';
select count(*) from v$dispatcher;
select servers_highwater from v$mts;
servers_highwater接近mts_max_servers时，参数需加大
16. 碎片程度
select tablespace_name,count(tablespace_name)
from dba_free_space
group by tablespace_name
having count(tablespace_name)>10;
alter tablespace name coalesce;
alter table name deallocate unused;
create or replace view ts_blocks_v as
select tablespace_name,block_id,bytes,blocks,
segment_name
from dba_free_space
union all
select tablespace_name,block_id,bytes,blocks,
segment_name
from dba_extents;
select * from ts_blocks_v;
select tablespace_name,sum(bytes),max(bytes),count(block_id)
from dba_free_space
group by tablespace_name;
查看碎片程度高的表
SELECT segment_name table_name , COUNT(*) extents
FROM dba_segments
WHERE owner NOT IN ('SYS', 'SYSTEM')
GROUP BY segment_name
HAVING COUNT(*) = (SELECT MAX(COUNT(*)) FROM dba_segments GROUP BY segment_name);
17. 表、索引的存储情况检查
select segment_name,sum(bytes),count(*) ext_quan
from dba_extents
where tablespace_name='&tablespace_name' and
segment_type='TABLE'
group by tablespace_name,segment_name;
select segment_name,count(*)
from dba_extents
where segment_type='INDEX' and owner='&owner'
group by segment_name;
18、找使用CPU多的用户session
12是cpu used by this session
select a.sid,spid,status,substr(a.program,1,40) prog,
a.terminal,osuser,value/60/100 value
from v$session a,v$process b,v$sesstat c
where c.statistic#=12 and
c.sid=a.sid and
a.paddr=b.addr
order by value desc;
19.对可疑/性能不好的Server Process来进行Trace.,可以用tkprof来分析Trace的结果.比较方便.使用Unix的KSH.
(1) start_trc:
#!/bin/ksh
if (( $# != 1 ))
then
echo Usuage: start_trc pid
fi
sid_serial=$(print "
connect / as sysdba;
set heading off;
set feedback off;
select a.sid,a.serial# from v\$session a,v\$process b where a.paddr=b.addr and b.spid=$1;
exit;
" | sqlplus -s /nolog | grep -v 'Connected' | sed -e 's/\([0-9]\{1,\}\)/\1,/' -e '/^$/d' )
if [[ -z $sid_serial ]]
then
print "Seems that this process $1 is not an Oracle process!"
exit 1
fi
print "
connect / as sysdba;
execute dbms_system.set_sql_trace_in_session($sid_serial,true);
exit;
" | sqlplus -s /nolog
(2) stop_trc:
#!/bin/ksh
if (( $# != 1 ))
then
echo Usuage: stop_trc pid
fi
sid_serial=$(print "
connect / as sysdba;
set heading off;
set feedback off;
select a.sid,a.serial# from v\$session a,v\$process b where a.paddr=b.addr and b.spid=$1;
exit;
" | sqlplus -s /nolog | grep -v 'Connected' | sed -e 's/\([0-9]\{1,\}\)/\1,/' -e '/^$/d' )
if [[ -z $sid_serial ]]
then
print "Seems that this process $1 is not an Oracle process!"
exit 1
fi
print "
connect / as sysdba;
execute dbms_system.set_sql_trace_in_session($sid_serial,false);
exit;
" | sqlplus -s /nolog
20.查看Lock
SELECT sn.username, m.sid, m.type,
DECODE(m.lmode, 0, 'None',
1, 'Null',
2, 'Row Share',
3, 'Row Excl.',
4, 'Share',
5, 'S/Row Excl.',
6, 'Exclusive',
lmode, ltrim(to_char(lmode,'990'))) lmode,
DECODE(m.request,0, 'None',
1, 'Null',
2, 'Row Share',
3, 'Row Excl.',
4, 'Share',
5, 'S/Row Excl.',
6, 'Exclusive',
request, ltrim(to_char(m.request,'990'))) request,
m.id1, m.id2
FROM v$session sn, v$lock m
WHERE (sn.sid = m.sid AND m.request != 0) OR
( sn.sid = m.sid AND
m.request = 0 AND
lmode != 4 AND
(id1, id2) IN (
SELECT s.id1, s.id2
FROM v$lock s
WHERE request != 0 AND
s.id1 = m.id1 AND
s.id2 = m.id2
)
)
ORDER BY id1, id2, m.request;
select l.sid,s.serial#,s.username,s.terminal,
decode(l.type,'RW','RW - Row Wait Enqueue',
'TM','TM - DML Enqueue',
'TX','TX - Trans Enqueue',
'UL','UL - User',l.type||'System') res,
substr(t.name,1,10) tab,u.name owner,
l.id1,l.id2,
decode(l.lmode,1,'No Lock',
2,'Row Share',
3,'Row Exclusive',
4,'Share',
5,'Shr Row Excl',
6,'Exclusive',null) lmode,
decode(l.request,1,'No Lock',
2,'Row Share',
3,'Row Excl',
4,'Share',
5,'Shr Row Excl',
6,'Exclusive',null) request
from v$lock l, v$session s,
sys.user$ u,sys.obj$ t
where l.sid = s.sid and
s.type != 'BACKGROUND' and
t.obj# = l.id1 and
u.user# = t.owner#;
以下的方式可以監控登入登出的用戶:
創建如下的兩張表:
create table login_log -- 登入登出信息表
(
session_id int not null, -- sessionid
login_on_time date, -- 登入時間
login_off_time date, -- 登出時間
user_in_db varchar2(30), -- 登入的db user
machine varchar2(20), -- 機器名
ip_address varchar2(20), -- ip地址
run_program varchar2(20) -- 以何程序登入
);
create table allow_user -- 網域用戶表
(
ip_address varchar2(20), -- ip地址
login_user_name nvarchar2(20) -- 操作者姓名
);
創建如下的兩個觸發器:
create or replace trigger login_on_info -- 紀錄登入信息的觸發器
after logon on database
Begin
insert into login_log(session_id,login_on_time,login_off_time,user_in_db,machine,ip_address,run_program)
select AUDSID,sysdate,null,sys.login_user,machine,SYS_CONTEXT('USERENV','IP_ADDRESS'),program
from v$session where AUDSID = USERENV('SESSIONID'); --當前SESSION
END;
create or replace trigger login_off_info -- 紀錄登出信息的觸發器
before logoff on database
Begin
update login_log set login_off_time = sysdate
where session_id = USERENV('SESSIONID'); --當前SESSION
exception
when others then
null;
END;
方法二:
用如下的方式可以審計執行drop動作的事件:
/**
* drop語句的審計日誌表
*/
create table drop_log
(
session_id int not null, -- sessionid
drop_time date, -- drop的時間
ip_address varchar2(20), -- ip地址
object_owner varchar2(30), -- 對象的擁有者
object_name varchar2(30), -- 對象名稱
object_type varchar2(20), -- 對象類型
drop_by_user varchar2(30) -- 執行drop語句的用戶
);
create or replace trigger drop_info
after drop on mfg0513user.schema -- 在mfg0513user用戶上創建審計drop的觸發器
begin
insert into drop_log
(session_id,
drop_time,
ip_address,
object_owner,
object_name,
object_type,
drop_by_user)
values(USERENV('SESSIONID'),
sysdate,
SYS_CONTEXT('USERENV','IP_ADDRESS'),
sys.dictionary_obj_owner,
sys.dictionary_obj_name,
sys.dictionary_obj_type,
sys.login_user);
end;
