statspack报告数据结果解释
本人将最近在学习性能调优时，所用笔记总结如下，欢迎批评指正
本文将不断更新，欢迎补充。 （所列数据仅用于便于说明，没有实
际意义）
一、statspack 输出结果中必须查看的十项内容
1、负载间档(Load profile)
2、实例效率点击率(Instance efficiency hit ratios)
3、首要的5个等待事件(Top 5 wait events)
4、等待事件(Wait events)
5、闩锁等待
6、首要的SQL(Top sql)
7、实例活动(Instance activity)
8、文件I/O(File I/O)
9、内存分配(Memory allocation)
10、缓冲区等待(Buffer waits)
二、输出结果解释
1、报表头信息
数据库实例相关信息，包括数据库名称、ID、版本号及主机等信息
2、负载间档
该部分提供每秒和每个事物的统计信息，是监控系统吞吐量和负载变化的重要部分
说明:
Redo size:每秒产生的日志大小(单位字节)，可标志数据库任务的繁重与否
Logical reads:平决每秒产生的逻辑读，单位是block
block changes:每秒block变化数量，数据库事物带来改变的块数量
Physical reads:平均每秒数据库从磁盘读取的block数
Physical writes:平均每秒数据库写磁盘的block数
User calls:每秒用户call次数
Parses:每秒解析次数，近似反应每秒语句的执行次数
软解析每秒超过300次意味着你的"应用程序"效
率不高，没有使用soft soft parse，调整session_cursor_cache
Hard parses:每秒产生的硬解析次数
Sorts:每秒产生的排序次数
Executes:每秒执行次数
Transactions:每秒产生的事务数，反映数据库任务繁重与否
3、实例命中率
该部分可以提前找出ORACLE潜在将要发生的性能问题，很重要
说明:
Buffer Nowait %:在缓冲区中获取Buffer的未等待比率
Redo NoWait %:在Redo缓冲区获取Buffer的未等待比率
Buffer Hit %:数据块在数据缓冲区中得命中率，通常应在90%以上，否则，需要调整
In-memory Sort %:在内存中的排序率
Library Hit %:主要代表sql在共享区的命中率，通常在95%以上，否，需要要考虑加
大共享池，绑定变量，修改cursor_sharing等参数。
Soft Parse %:近似看作sql在共享区的命中率，小于<95%,需要考虑到绑定，如果低于80%，
那么就可能sql基本没有被重用
Execute to Parse %:sql语句解析后被重复执行的次数，如果过低，可以考虑设置
session_cached_cursors参数
Parse CPU to Parse Elapsd %:解析实际运行事件/(解析实际运行时间+解析中等待资源时间)
越高越好
% Non-Parse CPU:查询实际运行时间/(查询实际运行时间+sql解析时间)，太低表示解析消耗时间过多。
Shared Pool相关统计数据
Memory Usage %:共享池内存使用率，应该稳定在75%-90%间，太小浪费内存，太大则内存不足。
% SQL with executions>1:执行次数大于1的sql比率，若太小可能是没有使用bind variables。
% Memory for SQL w/exec>1:也即是memory for sql with execution > 1:执行次数大于1的sql
消耗内存/所有sql消耗的内存
4、首要等待事件
常见等待事件说明:
oracle等待事件是衡量oracle运行状况的重要依据及指示，主要有空闲等待事件和非空闲等待事件
；空闲等待事件是oracle正等待某种工作，在诊断和优化数据库时候，不用过多注意这部分事件，
非空闲等待事件专门针对oracle的活动，指数据库任务或应用程序运行过程中发生的等待，这些等待事件是我们在调整数据库应该关注的。
比较影响性能常见等待事件
db file scattered read
该事件通常与全表扫描有关。因为全表扫描是被放入内存中进行的进行的，
通常情况下它不可能被放入连续的缓冲区中，所以就散布在缓冲区的缓存中。该指数的数量过大说明
缺少索引或者限制使用索引。这种情况也可能是正常的，因为执行全表扫描可能比索引扫描效率更高。
当系统存在这些等待时，需要通过检查来确定全表扫描是否必需的来调整。可以尝试将较小的表放入
缓存keep中，避免反复读取它们。
db file sequential read
该事件说明在单个数据块上大量等待，该值过高通常是由于表间连接顺序很糟糕，或者使用了非选
择性索引。通过将这种等待与statspack报表中已知其它问题联系起来(如效率不高的sql),通过检查确
保索引扫描是必须的，并确保多表连接的连接顺序来调整
buffer busy wait
当缓冲区以一种非共享方式或者如正在被读入到缓冲时,就会出现该等待.该值不应该大于1%,确认
是不是由于热点块造成(如果是可以用反转索引,或者用更小块大小)
latch free
闩锁是底层的队列机制(更加准确的名称应该是互斥机制)，用于保护系统全局区(SGA)共享内存结构
。闩锁用于防止对内存结构的并行访问。如果闩锁不可用，就会记录一次闩锁丢失。绝大多数得闩锁问题
都与使用绑定变量失败(库缓存闩锁)、生成重作问题(重执行分配闩锁)、缓存的争用问题(缓存LRU链) 以
及缓存的热数据宽块(缓存链)有关。当闩锁丢失率高于0.5%时，需要调整这个问题。
log buffer space
日志缓冲区写的速度快于LGWR写REDOFILE的速度,可以增大日志文件大小，增加日志缓冲区的大小，或
者使用更快的磁盘来写数据。
logfile switch
通常是因为归档速度不够快,需要增大重做日志
log file sync
当一个用户提交或回滚数据时，LGWR将会话得重做操作从日志缓冲区填充到日志文件中，用户的进程
必须等待这个填充工作完成。为减少这个等待事件，须一次提交更多记录，或者将重做日志REDO LOG 文件
访在不同的物理磁盘上.
分析一下STATSPACK report for
DB Name DB Id Instance Inst Num Release Cluster Host
------------ ----------- ------------ -------- ----------- ------- ------------
ORA8 1365122498 ora8 1 9.2.0.1.0 NO IBM02
Snap Id Snap Time Sessions Curs/Sess Comment
------- ------------------ -------- --------- -------------------
Begin Snap: 245 04-8月 -04 14:00:03 318 6.7
End Snap: 246 04-8月 -04 15:00:03 340 8.8
Elapsed: 60.00 (mins)
Cache Sizes (end)
~~~~~~~~~~~~~~~~~
Buffer Cache: 1,360M Std Block Size: 8K
Shared Pool Size: 208M Log Buffer: 2,000K
Load Profile
~~~~~~~~~~~~ Per Second Per Transaction
--------------- ---------------
Redo size: 11,294.73 11,934.55
Logical reads: 996.91 1,053.38
Block changes: 72.60 76.71
Physical reads: 12.86 13.59
Physical writes: 5.57 5.89
User calls: 63.52 67.12
Parses: 10.34 10.93
Hard parses: 3.64 3.84
Sorts: 2.63 2.78
Logons: 0.09 0.09
Executes: 28.06 29.65
Transactions: 0.95
% Blocks changed per Read: 7.28 Recursive Call %: 40.50
Rollback per transaction %: 13.50 Rows per Sort: 409.44
Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 100.00 Redo NoWait %: 100.00
Buffer Hit %: 98.73 In-memory Sort %: 99.99
Library Hit %: 91.37 Soft Parse %: 64.85
Execute to Parse %: 63.14 Latch Hit %: 100.00
Parse CPU to Parse Elapsd %: 85.16 % Non-Parse CPU: 92.19
Shared Pool Statistics Begin End
------ ------
Memory Usage %: 96.25 96.60
% SQL with executions>1: 27.55 15.98
% Memory for SQL w/exec>1: 32.45 18.32
Top 5 Timed Events
~~~~~~~~~~~~~~~~~~ % Total
Event Waits Time (s) Ela Time
-------------------------------------------- ------------ ----------- --------
enqueue 116 345 50.81
CPU time 288 42.45
db file scattered read 3,308 15 2.21
db file sequential read 3,526 14 1.99
log file sync 2,570 7 1.02
-------------------------------------------------------------
Wait Events for DB: ORA8 Instance: ora8 Snaps: 245 -246
-> 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
---------------------------- ------------ ---------- ---------- ------ --------
enqueue 116 114 345 2972 0.0
db file scattered read 3,308 0 15 5 1.0
db file sequential read 3,526 0 14 4 1.0
log file sync 2,570 1 7 3 0.8
db file parallel write 1,078 539 6 5 0.3
control file parallel write 1,179 0 2 2 0.3
SQL*Net more data to client 39,260 0 2 0 11.5
latch free 140 5 0 3 0.0
SQL*Net break/reset to clien 340 0 0 1 0.1
log file switch completion 3 0 0 48 0.0
process startup 3 0 0 23 0.0
control file sequential read 548 0 0 0 0.2
log file sequential read 4 0 0 5 0.0
buffer busy waits 4 0 0 4 0.0
direct path write 4 0 0 4 0.0
log file parallel write 3,397 3,395 0 0 1.0
LGWR wait for redo copy 5 0 0 2 0.0
log file single write 4 0 0 1 0.0
undo segment extension 348 348 0 0 0.1
direct path read 30 0 0 0 0.0
SQL*Net message from client 226,481 0 377,967 1669 66.5
wakeup time manager 113 113 3,389 29991 0.0
jobq slave wait 66 63 195 2956 0.0
SQL*Net more data from clien 6 0 0 60 0.0
SQL*Net message to client 226,503 0 0 0 66.5
-------------------------------------------------------------
Background Wait Events for DB: ORA8 Instance: ora8 Snaps: 245 -246
-> ordered by wait time desc, waits desc (idle events last)
Avg
Total Wait wait Waits
Event Waits Timeouts Time (s) (ms) /txn
---------------------------- ------------ ---------- ---------- ------ --------
db file parallel write 1,078 539 6 5 0.3
control file parallel write 1,179 0 2 2 0.3
rdbms ipc reply 29 0 0 4 0.0
process startup 3 0 0 23 0.0
control file sequential read 481 0 0 0 0.1
log file sequential read 4 0 0 5 0.0
log file parallel write 3,397 3,395 0 0 1.0
buffer busy waits 3 0 0 3 0.0
LGWR wait for redo copy 5 0 0 2 0.0
log file single write 4 0 0 1 0.0
latch free 4 0 0 1 0.0
rdbms ipc message 14,195 11,021 16,541 1165 4.2
smon timer 14 9 3,344 ###### 0.0
-------------------------------------------------------------
分析一下SQL ordered by Gets for DB: ORA8 Instance: ora8 Snaps: 245 -246
-> 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
--------------- ------------ -------------- ------ -------- --------- ----------
681,196 90 7,568.8 19.0 22.70 26.61 1188822893
SELECT /*+ first_rows */ 0 FROM CLDE WHERE ADD_JOB_NUM=:1
591,129 853 693.0 16.5 32.33 34.33 391637996
SELECT COUNT(*) FROM WG WHERE WG = '1' AND TC = :b1 AND DH
= :b2 AND XH = :b3 AND CJ = :b4
202,544 14 14,467.4 5.6 0.76 0.83 3957953059
select yuangh,xingm,bumdm from archives where (bumdm in (select
site from users_dept1 where userid=:Usersite)) --SYF order by bu
mdm,yuangh
165,066 2,662 62.0 4.6 8.15 8.45 336027820
SELECT count(*) from face_rk where job_num = :b1
104,996 484 216.9 2.9 15.35 16.06 1556371957
select nianf,yuef,gongzlx,bumdm,kedh,banzdm,yuangh,xingm,xingb,y
uanglx,gongznx, slr_003,slr_009,slr_010,slr_007,slr_023,s
lr_029,slr_024,slr_028, slr_022,shiyj,gongjj,yilj,yanglj,
slr_030,slr_045,slr_042,kouc,buf,slr_043,slr_044, tesjs,s
hifjs,gongzzh, slr_016,slr_017,slr_019,slr_018,slr_026,
98,031 3 32,677.0 2.7 1.73 2.06 459237807
SELECT "TOOL_MX"."JOB_NUM" , "TOOL_MX"."NAME" ,
"TOOL_MX"."USE_UNIT" , "TOOL_MX"."GYBZ_MAN" ,
"TOOL_MX"."BZ_DATE" , "TOOL_MX"."JH_NUM" ,
"TOOL_MX"."DH_DATE" , "TOOL_MX"."LT_DATE" ,
"TOOL_MX"."SC_NUM" , "TOOL_MX"."RK_DATE" ,
91,861 10 9,186.1 2.6 10.81 12.08 2355361288
UPDATE DSJ.LIST SET NANUM_LINE=:b1 WHERE JOB_NUM1 = :b2
86,808 6 14,468.0 2.4 0.45 0.51 1190886821
select t.* ,t.rowid from archives t --WHERE BUMDM=:Usersite wh
ere (bumdm in (select site from users_dept1 where userid=:Usersi
te)) --SYF order by bumdm,yuangh
86,474 385 224.6 2.4 24.42 26.85 3809895136
begin pack_salary.p_salary_singlecalc(:nianf,:yuef,:gongzlx,:yua
ngh); end;
85,257 6 14,209.5 2.4 0.30 0.29 2516101932
select count(*) from ( select t.* ,t.rowid from archives t whe
re (bumdm in (select site from users_dept1 where userid=:Usersit
e)))
77,345 22 3,515.7 2.2 99.53 106.46 3462562281
INSERT into package_contents(job_num1,case_no,job_num,goods_name
,JOB_NUM11,unit) select :b1,:b2,job_num,str_check(str_check(name
)),JOB_NUM1,'件' from dsj.list where job_num LIKE '%'||:b1||'%'
SQL ordered by Gets for DB: ORA8 Instance: ora8 Snaps: 245 -246
-> 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
--------------- ------------ -------------- ------ -------- --------- ----------
73,946 117 632.0 2.1 3.21 3.30 2564771303
UPDATE OLD_LIST SET TOTAL_P=:b1 WHERE JOB_NUM = :b2
71,855 117 614.1 2.0 2.84 3.02 2395855240
SELECT COUNT(*) FROM OLD_LIST_TEMP WHERE PART_NUM = :b1
64,938 2 32,469.0 1.8 1.28 2.67 3582756360
UPDATE OLD_LIST_TEMP SET N_UNIT_W=0,N_TOTAL_W=0,SPARE_P=0
54,269 614 88.4 1.5 5.04 6.52 145329864
UPDATE LIST SET MAT=:1,PIECE=:2,UNIT_W=:3,TYPE_CODE=:4,TYPE=:5,L
INE=:6,NANUM_LINE=:7 WHERE ROWID=:8
42,728 3 14,242.7 1.2 0.75 1.01 3169921088
UPDATE FACE SET DES_P=:1,MX_DONE_DATE=:2,LINE_WRITER=:3,LINE_DON
E_DATE=:4,LINE_CHECK=:5,NANUM_LINE=:6 WHERE ROWID=:7
41,690 102 408.7 1.2 0.33 0.35 709258517
select constraint_name, column_name from sys.all_cons_columns wh
ere owner = :owner and table_name = :table_name and constraint_n
ame not like 'SYS_C%' order by constraint_name, position
39,804 2 19,902.0 1.1 1.73 3.23 1492226261
select list.line LX, list.job_num1 glh, list.job_num dh, list.na
me mc, list.piece sl, list.mat cz, list.unit_w dz, list.des_size
fm, list.des_p zs, list.des_work a1, list.bj_code gzj , list.ym
d rq, list.m cs, list.man xgr, list.type clzb, list.type_code zb
, list_bak1.line, list_bak1.name, list_bak1.piece, list_bak1.mat
30,243 4,971 6.1 0.8 0.53 0.52 1404744636
SELECT * from salaryformula where gongsh = :b1 order by gongsh
28,399 6 4,733.2 0.8 1.21 4.31 877854086
SELECT * FROM salaryresult WHERE nianf=:b6 and yuef>=:
b5 AND yuef<=:b4 and gongzlx=:b3 and (BUMDM>=:b2 or :b
2 is null)and (BUMDM<=:b1 or :b1 is null)
21,549 5 4,309.8 0.6 0.29 0.39 803228315
begin pack_salary.p_update_work_Age(:bumdm,:yuangh1,:yuangh2,:yu
anglx1,:yuanglx2); end;
20,049 717 28.0 0.6 0.26 0.31 601639400
UPDATE Archives set gongznx = PACK_SALARY.F_WORK_AGE(:b3
,:b2) where yuangh= :b1
17,170 34 505.0 0.5 0.53 0.52 2722690622
SELECT NVL(MAX(XH),0) + 1 FROM WELD_RKTZ WHERE :b1 = JOB_NUM
SQL ordered by Gets for DB: ORA8 Instance: ora8 Snaps: 245 -246
-> 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
--------------- ------------ -------------- ------ -------- --------- ----------
16,182 261 62.0 0.5 0.15 0.19 2128744839
SELECT Key_ From Pdm_Temp_Tree Where Job_Num = :b1
15,566 79 197.0 0.4 0.88 1.48 3980150586
SELECT SCJH_04_QJ.JOB_NUM1, SCJH_04_QJ.PIECE, SCJH_04_QJ.NAME,
SCJH_04_QJ.MAT, SCJH_04_QJ.PIECE0, SCJH_04_QJ.UNIT_W, SCJH_04_
QJ.TC_NUM, SCJH_04_QJ.PRE_SHOP, SCJH_04_QJ.NEXT_SHOP, SCJH_04_Q
J.H1, SCJH_04_QJ.H2, SCJH_04_QJ.H3, SCJH_04_QJ.H4, SCJH_04_QJ.H5
, SCJH_04_QJ.H6, SCJH_04_QJ.TECH_REQ, SCJH_04_QJ.CLASS, SCJH_04
15,132 2 7,566.0 0.4 5.32 5.38 1269080801
select count(job_num1),mc,cz,ltrim(zg),sum(zs) zs1,sum(jz) jz1,b
a, sum(mz) mz1,sum(de) de1,lm from ybx.clde group by lb,job_nu
m1,mc,cz,zg,ba,lm,bj having (lm='c' or lm='C') and bj='jj' and (
lb='1'or lb='4') and job_num1=:job1 order by mc,cz,ba,decode(ins
-------------------------------------------------------------
SQL ordered by Reads for DB: ORA8 Instance: ora8 Snaps: 245 -246
-> End Disk Reads Threshold: 1000
CPU Elapsd
Physical Reads Executions Reads per Exec %Total Time (s) Time (s) Hash Value
--------------- ------------ -------------- ------ -------- --------- ----------
8,984 2 4,492.0 19.4 1.66 3.70 1933892864
SELECT "COOL_ZZP"."UNIT" , "COOL_ZZP"."SR_DATE" ,
"COOL_ZZP"."JOB_NUM" , "COOL_ZZP"."SD_ZGS" ,
"COOL_ZZP"."JG_CL" , "COOL_ZZP"."WT_JG" ,
"COOL_ZZP"."BZ_J" , "COOL_ZZP"."BZ_F" ,
"COOL_ZZP"."ZY_GJ" , "COOL_ZZP"."FP_SS" ,
7,556 90 84.0 16.3 22.70 26.61 1188822893
SELECT /*+ first_rows */ 0 FROM CLDE WHERE ADD_JOB_NUM=:1
6,407 1 6,407.0 13.8 1.31 2.62 2933674388
SELECT SUM(PLAN_TIME1) FROM WELDPR.NEW_PROC_REL,DSJ.LIST WHER
E NEW_PROC_REL.JOB_NUM1 = :b1 AND NEW_PROC_REL.JOB_NUM = LIST.J
OB_NUM AND LIST.LINE NOT LIKE '3000%'
5,319 1 5,319.0 11.5 0.61 3.02 2934925061
SELECT DISTINCT JSDW FROM dsj.tzd WHERE HDR='MX' and JSR IS NULL
3,902 2 1,951.0 8.4 1.73 3.23 1492226261
select list.line LX, list.job_num1 glh, list.job_num dh, list.na
me mc, list.piece sl, list.mat cz, list.unit_w dz, list.des_size
fm, list.des_p zs, list.des_work a1, list.bj_code gzj , list.ym
d rq, list.m cs, list.man xgr, list.type clzb, list.type_code zb
, list_bak1.line, list_bak1.name, list_bak1.piece, list_bak1.mat
1,562 2 781.0 3.4 0.22 0.76 132186308
SELECT COUNT(*) FROM LIST_TEMP_INDEX WHERE JOB_NUM1 = :b1
1,189 6 198.2 2.6 1.21 4.31 877854086
SELECT * FROM salaryresult WHERE nianf=:b6 and yuef>=:
b5 AND yuef<=:b4 and gongzlx=:b3 and (BUMDM>=:b2 or :b
2 is null)and (BUMDM<=:b1 or :b1 is null)
675 773 0.9 1.5 0.27 0.94 2650523022
SELECT SUM(DES_WORK) FROM DSJ.LIST WHERE JOB_NUM1 = :b1
161 614 0.3 0.3 5.04 6.52 145329864
UPDATE LIST SET MAT=:1,PIECE=:2,UNIT_W=:3,TYPE_CODE=:4,TYPE=:5,L
INE=:6,NANUM_LINE=:7 WHERE ROWID=:8
152 79 1.9 0.3 0.88 1.48 3980150586
SELECT SCJH_04_QJ.JOB_NUM1, SCJH_04_QJ.PIECE, SCJH_04_QJ.NAME,
SCJH_04_QJ.MAT, SCJH_04_QJ.PIECE0, SCJH_04_QJ.UNIT_W, SCJH_04_
QJ.TC_NUM, SCJH_04_QJ.PRE_SHOP, SCJH_04_QJ.NEXT_SHOP, SCJH_04_Q
J.H1, SCJH_04_QJ.H2, SCJH_04_QJ.H3, SCJH_04_QJ.H4, SCJH_04_QJ.H5
, SCJH_04_QJ.H6, SCJH_04_QJ.TECH_REQ, SCJH_04_QJ.CLASS, SCJH_04
109 111 1.0 0.2 0.04 1.05 4289400484
SELECT ROWID,JOB_NUM,JOB_NUM1,TABLE_N,PROMPTED,WELD_CODE,START_D
ATE,END_DATE,REJECT_MARK,INPUT_DATE,PROCESS,OPMH,BJ,MODIGY_OPM,T
F,ZP_DATE,CH_IP,CH_PROM,RK_DATE,WG_DATE,FL_DATE,TE_SPEC,OPM FROM
NEW_TECH_REL WHERE (JOB_NUM=:1)
SQL ordered by Reads for DB: ORA8 Instance: ora8 Snaps: 245 -246
-> End Disk Reads Threshold: 1000
CPU Elapsd
Physical Reads Executions Reads per Exec %Total Time (s) Time (s) Hash Value
--------------- ------------ -------------- ------ -------- --------- ----------
96 484 0.2 0.2 15.35 16.06 1556371957
select nianf,yuef,gongzlx,bumdm,kedh,banzdm,yuangh,xingm,xingb,y
uanglx,gongznx, slr_003,slr_009,slr_010,slr_007,slr_023,s
lr_029,slr_024,slr_028, slr_022,shiyj,gongjj,yilj,yanglj,
slr_030,slr_045,slr_042,kouc,buf,slr_043,slr_044, tesjs,s
hifjs,gongzzh, slr_016,slr_017,slr_019,slr_018,slr_026,
77 22 3.5 0.2 99.53 106.46 3462562281
INSERT into package_contents(job_num1,case_no,job_num,goods_name
,JOB_NUM11,unit) select :b1,:b2,job_num,str_check(str_check(name
)),JOB_NUM1,'件' from dsj.list where job_num LIKE '%'||:b1||'%'
75 94 0.8 0.2 0.24 0.91 1011885834
select job_num,job_num1,part_num,part_num1,des_num,des_size,des_
p,name,piece,mat,unit_w,line,type,bj_code,tz_order,M,LINE_M from
list where job_num1=upper(:job_num111) order by REPLACE(job_num
,'M')
74 20 3.7 0.2 0.16 1.05 4191266530
SELECT ALL LIST.NAME,LIST.MAT,list.m, LIST.PIECE,LIST
.UNIT_W, LIST.job_NUM,list.bj_code, substr
(list.job_num,1,9), substr(list.job_num,10,11),
substr(list.job_num,12,13), substr(list.job_num
,14,16), list.line, LIST.PART_NUM1,LIST.DE
67 107 0.6 0.1 0.06 0.89 2302688342
INSERT INTO CLDE(BJ,JOB_NUM1,RQ,JOB_NUM,ADD_JOB_NUM,CZ,ZS,JZ,DE,
MC,DG,ZG,LYL,LM,MK,TX,LB,MZ,BZ,BA,GG,AA,BB) VALUES (:BJ,:JOB_NUM
1,:RQ,:JOB_NUM,:ADD_JOB_NUM,:CZ,:ZS,:JZ,:DE,:MC,:DG,:ZG,:LYL,:LM
,:MK,:TX,:LB,:MZ,:BZ,:BA,:GG,:AA,:BB)
45 362 0.1 0.1 0.11 0.35 3190524044
SELECT ROWID,PLAN_TIME,WORK_SHOP,JOB_NUM,JOB_NUM1,TABLE_N,PROCES
S_COST,PROCESS_GROUP,PLAN_TIME1,PLAN_TIME_MODI_OPH,TABLE_TIME,TF
,BEGIN_DATE,FINISH_TIME,FINISH_PIECE,FINISH_DATE,FINISH_MARK,INP
UT_DATE,PROCESS_CODE FROM NEW_PROC_REL WHERE (JOB_NUM=:1)
41 36 1.1 0.1 0.02 0.24 1367683388
SELECT COUNT(*) FROM DSJ.LIST WHERE JOB_NUM1 = UPPER(:b1) AN
D (LINE LIKE '%01%' OR LINE LIKE '%02%' OR LINE LIKE '%03%'
OR LINE LIKE '%26%' OR LINE LIKE '%29%' OR LINE LIKE '%40
W%' OR LINE LIKE '%40B%' OR LINE LIKE '%230%' OR LINE LIKE
'%240%' OR LINE LIKE '%43W%' OR LINE LIKE '%42W%' OR LINE
36 372 0.1 0.1 0.33 1.10 1984204482
begin pack_salary.p_salaryresult_insert(:nianf,:yuef,:gongzlx,
:bumdm,:kedh,:banzdm,:yuangh,:xingm,:xingb,:yuanglx,:gongznx,:sl
-------------------------------------------------------------
SQL ordered by Executions for DB: ORA8 Instance: ora8 Snaps: 245 -246
-> End Executions Threshold: 100
CPU per Elap per
Executions Rows Processed Rows per Exec Exec (s) Exec (s) Hash Value
------------ --------------- ---------------- ----------- ---------- ----------
4,971 12,856 2.6 0.00 0.00 1404744636
SELECT * from salaryformula where gongsh = :b1 order by gongsh
4,725 4,718 1.0 0.00 0.00 4080986794
SELECT PROC_GROUP,PLAN_PRICE FROM ASSETS.V_ASSETS WHERE PROC_
GROUP = :b1
2,662 2,662 1.0 0.00 0.00 336027820
SELECT count(*) from face_rk where job_num = :b1
2,440 2,440 1.0 0.00 0.00 4121249898
INSERT into bbb(job_num,JOB_NUM_1,JOB_NUM1,JOB_NUM1_1,in_date,us
ee,machine,comm) select :b4 ,:b3 ,:b2 ,:
b1 ,sysdate,userNAME,machine,program from v$session
WHERE SID = (select sid from v$mystat where rownum = 1)
1,572 1,572 1.0 0.00 0.00 2163629200
SELECT COUNT(*) FROM DSJ.FACE WHERE JOB_NUM = :b1
1,570 1,570 1.0 0.00 0.00 2704824524
SELECT MX_WRITER,LINE_WRITER FROM DSJ.FACE WHERE JOB_NUM = :b
1
1,250 1,250 1.0 0.00 0.00 1560113101
select * from HR_C1 WHERE NIANF=:NIANF AND YUEF=:YUEF and BUMDM
=:BUMDM
1,128 0 0.0 0.00 0.00 278968606
SAVEPOINT FM_1
1,072 1,072 1.0 0.00 0.00 3784857994
SELECT Count(*) From Pdm.Protree Where Symbol = :b1
962 274 0.3 0.00 0.00 272842946
select YUANGH,BUF from salaryresult WHERE NIANF=:NIANF AND YUEF
=:YUEF AND BUMDM=:BUMDM AND YUANGH=:YUANGH
946 901 1.0 0.00 0.00 633456687
select yuangh,xingm,xingb,bumdm,kedh,banzdm,yuanglx,gongznx,rugs
r,zhengssy,zhic, jib,fengxxs,jindj,jijf,ganbbz,cjgg from
archives where bumdm=:usersite and yuangh=:yuangh
912 0 0.0 0.00 0.00 3615375148
COMMIT
853 853 1.0 0.04 0.04 391637996
SELECT COUNT(*) FROM WG WHERE WG = '1' AND TC = :b1 AND DH
= :b2 AND XH = :b3 AND CJ = :b4
800 800 1.0 0.00 0.00 3115407350
begin pack_salary.p_salaryresult_fetch(:lock,:nianf,:yuef,:gon
gzlx,:yuangh,:xingm); end;
793 793 1.0 0.00 0.00 4246749948
SQL ordered by Executions for DB: ORA8 Instance: ora8 Snaps: 245 -246
-> End Executions Threshold: 100
CPU per Elap per
Executions Rows Processed Rows per Exec Exec (s) Exec (s) Hash Value
------------ --------------- ---------------- ----------- ---------- ----------
INSERT into SALARY_APPEDX( nianf,
yuef, bumdm,
yuangh, xingm,
SLR_030, SL
R_100, SLR_101,
786 0 0.0 0.00 0.00 3758061761
savepoint DOA__LOCKPOINT
773 773 1.0 0.00 0.00 2650523022
SELECT SUM(DES_WORK) FROM DSJ.LIST WHERE JOB_NUM1 = :b1
725 725 1.0 0.00 0.00 1281731196
SELECT xingm from salaryresult where nianf=:b4
and yuef=:b3 and gongzlx=:b2 and yuangh=
:b1
717 715 1.0 0.00 0.00 601639400
UPDATE Archives set gongznx = PACK_SALARY.F_WORK_AGE(:b3
,:b2) where yuangh= :b1
715 715 1.0 0.00 0.00 3193665429
SELECT round(trunc(sysdate),'dd') from dual
715 715 1.0 0.00 0.00 3263940320
SELECT pyc_005 from PAYROLL_C
702 702 1.0 0.00 0.00 2964743345
select count(*) from sys.job$ where (next_date > sysdate) and (n
ext_date < (sysdate+5/86400))
691 1 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
654 654 1.0 0.00 0.00 1754174331
SELECT COUNT(*) FROM WELDPR.REL_TABL WHERE BJ = '工艺' AND J
OB_NUM = :b1
644 0 0.0 0.00 0.00 502510949
select privilege#,level from sysauth$ connect by grantee#=prior
privilege# and privilege#>0 start with grantee#=:1 and privilege
#>0
644 644 1.0 0.00 0.00 1498920852
select max(nvl(option$,0)) from sysauth$ where privilege#=:1 con
nect by grantee#=prior privilege# and privilege#>0 start with (g
rantee#=:2 or grantee#=1) and privilege#>0 group by privilege#
614 614 1.0 0.01 0.01 145329864
UPDATE LIST SET MAT=:1,PIECE=:2,UNIT_W=:3,TYPE_CODE=:4,TYPE=:5,L
INE=:6,NANUM_LINE=:7 WHERE ROWID=:8
SQL ordered by Executions for DB: ORA8 Instance: ora8 Snaps: 245 -246
-> End Executions Threshold: 100
CPU per Elap per
Executions Rows Processed Rows per Exec Exec (s) Exec (s) Hash Value
------------ --------------- ---------------- ----------- ---------- ----------
613 613 1.0 0.00 0.00 2285186583
SELECT MAT,PIECE,UNIT_W,TYPE_CODE,TYPE,LINE,NANUM_LINE FROM LIST
WHERE ROWID=:1 FOR UPDATE OF MAT NOWAIT
601 595 1.0 0.00 0.00 3693280073
SELECT PROC_GROUP FROM ASSETS.V_ASSETS WHERE PROC_GROUP = '13
H' || :b1
597 597 1.0 0.00 0.00 2962403619
SELECT NVL(MAX(PROCESS_CODE),0) + 1 FROM NEW_PROC_REL WHERE
:b1 = JOB_NUM
593 593 1.0 0.00 0.00 1192975401
INSERT INTO NEW_PROC_REL(WORK_SHOP,PLAN_TIME,JOB_NUM,JOB_NUM1,TA
BLE_N,TF,PROCESS_COST,PROCESS_GROUP,PLAN_TIME1,TABLE_TIME,BEGIN_
DATE,FINISH_TIME,FINISH_PIECE,FINISH_DATE,FINISH_MARK,INPUT_DATE
-------------------------------------------------------------
SQL ordered by Parse Calls for DB: ORA8 Instance: ora8 Snaps: 245 -246
-> End Parse Calls Threshold: 1000
% Total
Parse Calls Executions Parses Hash Value
------------ ------------ -------- ----------
4,725 4,725 12.69 4080986794
SELECT PROC_GROUP,PLAN_PRICE FROM ASSETS.V_ASSETS WHERE PROC_
GROUP = :b1
1,128 1,128 3.03 278968606
SAVEPOINT FM_1
800 800 2.15 3115407350
begin pack_salary.p_salaryresult_fetch(:lock,:nianf,:yuef,:gon
gzlx,:yuangh,:xingm); end;
786 786 2.11 3758061761
savepoint DOA__LOCKPOINT
644 644 1.73 502510949
select privilege#,level from sysauth$ connect by grantee#=prior
privilege# and privilege#>0 start with grantee#=:1 and privilege
#>0
644 644 1.73 1498920852
select max(nvl(option$,0)) from sysauth$ where privilege#=:1 con
nect by grantee#=prior privilege# and privilege#>0 start with (g
rantee#=:2 or grantee#=1) and privilege#>0 group by privilege#
601 601 1.61 3693280073
SELECT PROC_GROUP FROM ASSETS.V_ASSETS WHERE PROC_GROUP = '13
H' || :b1
597 597 1.60 2962403619
SELECT NVL(MAX(PROCESS_CODE),0) + 1 FROM NEW_PROC_REL WHERE
:b1 = JOB_NUM
407 407 1.09 595939768
begin pack_salary.P_SALARY_APPEDX_FETCH(:lock,:nianf,:yuef,:yu
angh,:bumdm,:xingm); end;
388 388 1.04 1882717930
select * from SALARYBASE where rowid = :doa__rowid
385 385 1.03 3809895136
begin pack_salary.p_salary_singlecalc(:nianf,:yuef,:gongzlx,:yua
ngh); end;
372 372 1.00 1984204482
begin pack_salary.p_salaryresult_insert(:nianf,:yuef,:gongzlx,
:bumdm,:kedh,:banzdm,:yuangh,:xingm,:xingb,:yuanglx,:gongznx,:sl
r_003,:slr_009,:slr_010,:slr_007,:slr_023,:slr_029,:slr_024,:slr
_028,:slr_022,:shiyj,:gongjj,:yilj,:yanglj,:slr_030,:slr_045,:sl
r_042,:kouc,:buf,:slr_043,:slr_044,:tesjs,:shifjs,:gongzzh,:slr_
247 247 0.66 2959273380
set role CONNECT,RES
241 241 0.65 2467811771
SQL ordered by Parse Calls for DB: ORA8 Instance: ora8 Snaps: 245 -246
-> End Parse Calls Threshold: 1000
% Total
Parse Calls Executions Parses Hash Value
------------ ------------ -------- ----------
COMMIT work comment ''
204 204 0.55 1010299517
begin pack_salary.P_SALARY_APPEDX_update(:nianf,:yuef,:gongzlx
,:bumdm,:yuangh,:xingm,:SLR_030,:SLR_100,:SLR_101,:SLR_102,:SLR_
103); end;
200 200 0.54 1323314622
UPDATE NEW_TECH_REL SET OPMH=:b1 WHERE JOB_NUM = :b2
196 196 0.53 3013728279
select privilege#,level from sysauth$ connect by grantee#=prior
privilege# and privilege#>0 start with (grantee#=:1 or grantee#=
1) and privilege#>0
158 158 0.42 1470906206
ROLLBACK
151 151 0.41 1656643811
ROLLBACK TO FM_1
147 147 0.39 319793062
alter session set nls_language= 'SIMPLIFIED CHINESE' nls_territo
ry= 'CHINA' nls_currency= '$' nls_iso_currency= 'CHINA' nls_nume
ric_characters= '.,' nls_date_format= 'DD-MON-YY' nls_date_langu
age= 'SIMPLIFIED CHINESE' nls_sort= 'BINARY' nls_calendar= 'GREG
ORIAN'
146 146 0.39 3397948939
ALTER SESSION SET REMOTE_DEPENDENCIES_MODE=SIGNATURE
142 142 0.38 3357351478
SELECT DEVICE_NAME,TOTAL_W FROM DSJ.FACE WHERE JOB_NUM = :b1
119 119 0.32 1141046533
SELECT * FROM DSJ.FACE where job_num=upper(:job_num111)
118 119 0.32 3809432628
SELECT COUNT(*) FROM DSJ.FACE_RK WHERE JOB_NUM = :b1
115 115 0.31 2576475893
select * from SALARYBASE where rowid = :doa__rowid for update no
wait
114 114 0.31 1259305139
UPDATE NEW_REL_TECH SET NAME_BZ=:b1 WHERE JOB_NUM = :b2
114 114 0.31 3744481045
UPDATE NEW_REL_TECH SET MATERIAL_SOURCE=:b1 WHERE JOB_NUM = :b2
113 113 0.30 238087931
select t.schema, t.name, t.flags, q.name from system.aq$_queue_t
ables t, sys.aq$_queue_table_affinities aft, system.aq$_que
ues q where aft.table_objno = t.objno and aft.owner_instance = :
SQL ordered by Parse Calls for DB: ORA8 Instance: ora8 Snaps: 245 -246
-> End Parse Calls Threshold: 1000
% Total
Parse Calls Executions Parses Hash Value
------------ ------------ -------- ----------
1 and q.table_objno = t.objno and q.usage = 0 and b
itand(t.flags, 4+16+32+64+128+256) = 0 for update of t.name, aft
113 113 0.30 2780709284
select q_name, state, delay, expiration, rowid, msgid, dequeue
_msgid, chain_no, local_order_no, enq_time, enq_tid, step_no,
priority, exception_qschema, exception_queue, retry_count, corri
d, time_manager_info, sender_name, sender_address, sender_prot
ocol from SYS.AQ_SRVNTFN_TABLE where time_manager_info <= :1
113 113 0.30 2979542350
select q_name, state, delay, expiration, rowid, msgid, dequeu
e_msgid, chain_no, local_order_no, enq_time, enq_tid, step_no,
priority, exception_qschema, exception_queue, retry_count, corr
id, time_manager_info from SYS.AQ_EVENT_TABLE where time
-------------------------------------------------------------
分析一下Instance Activity Stats for DB: ORA8 Instance: ora8 Snaps: 245 -246
Statistic Total per Second per Trans
--------------------------------- ------------------ -------------- ------------
CPU used by this session 28,801 8.0 8.5
CPU used when call started 28,798 8.0 8.5
CR blocks created 803 0.2 0.2
DBWR buffers scanned 15,823 4.4 4.6
DBWR checkpoint buffers written 8,552 2.4 2.5
DBWR checkpoints 1 0.0 0.0
DBWR free buffers found 15,727 4.4 4.6
DBWR lru scans 9 0.0 0.0
DBWR make free requests 9 0.0 0.0
DBWR revisited being-written buff 0 0.0 0.0
DBWR summed scan depth 15,823 4.4 4.6
DBWR transaction table writes 28 0.0 0.0
DBWR undo block writes 2,644 0.7 0.8
SQL*Net roundtrips to/from client 225,575 62.7 66.2
active txn count during cleanout 975 0.3 0.3
background checkpoints completed 0 0.0 0.0
background checkpoints started 1 0.0 0.0
background timeouts 4,234 1.2 1.2
branch node splits 2 0.0 0.0
buffer is not pinned count 2,650,098 736.1 777.8
buffer is pinned count 1,204,540 334.6 353.6
bytes received via SQL*Net from c 11,612,130 3,225.6 3,408.3
bytes sent via SQL*Net to client 120,585,036 33,495.8 35,393.3
calls to get snapshot scn: kcmgss 132,881 36.9 39.0
calls to kcmgas 7,656 2.1 2.3
calls to kcmgcs 587 0.2 0.2
change write time 196 0.1 0.1
cleanout - number of ktugct calls 1,084 0.3 0.3
cluster key scan block gets 34,529 9.6 10.1
cluster key scans 14,590 4.1 4.3
commit cleanout failures: block l 0 0.0 0.0
commit cleanout failures: callbac 3 0.0 0.0
commit cleanouts 15,685 4.4 4.6
commit cleanouts successfully com 15,682 4.4 4.6
commit txn count during cleanout 337 0.1 0.1
consistent changes 1,790 0.5 0.5
consistent gets 3,403,246 945.4 998.9
consistent gets - examination 632,808 175.8 185.7
cursor authentications 598 0.2 0.2
data blocks consistent reads - un 1,790 0.5 0.5
db block changes 261,365 72.6 76.7
db block gets 185,627 51.6 54.5
deferred (CURRENT) block cleanout 9,025 2.5 2.7
dirty buffers inspected 8 0.0 0.0
enqueue conversions 1,017 0.3 0.3
enqueue releases 13,488 3.8 4.0
enqueue requests 13,496 3.8 4.0
enqueue timeouts 0 0.0 0.0
enqueue waits 3 0.0 0.0
execute count 101,030 28.1 29.7
free buffer inspected 8 0.0 0.0
free buffer requested 49,399 13.7 14.5
hot buffers moved to head of LRU 1,955 0.5 0.6
immediate (CR) block cleanout app 562 0.2 0.2
immediate (CURRENT) block cleanou 1,883 0.5 0.6
index fast full scans (full) 11 0.0 0.0
Instance Activity Stats for DB: ORA8 Instance: ora8 Snaps: 245 -246
Statistic Total per Second per Trans
--------------------------------- ------------------ -------------- ------------
index fetch by key 467,102 129.8 137.1
index scans kdiixs1 86,292 24.0 25.3
leaf node 90-10 splits 3 0.0 0.0
leaf node splits 193 0.1 0.1
logons cumulative 320 0.1 0.1
messages received 3,788 1.1 1.1
messages sent 3,788 1.1 1.1
no buffer to keep pinned count 0 0.0 0.0
no work - consistent read gets 2,677,804 743.8 786.0
opened cursors cumulative 33,252 9.2 9.8
parse count (failures) 13 0.0 0.0
parse count (hard) 13,088 3.6 3.8
parse count (total) 37,236 10.3 10.9
parse time cpu 2,250 0.6 0.7
parse time elapsed 2,642 0.7 0.8
physical reads 46,291 12.9 13.6
physical reads direct 808 0.2 0.2
physical writes 20,067 5.6 5.9
physical writes direct 11,411 3.2 3.4
physical writes non checkpoint 13,846 3.9 4.1
prefetched blocks 38,649 10.7 11.3
process last non-idle time 213,953,086,549 59,431,412.9 ############
recovery blocks read 0 0.0 0.0
recursive calls 155,692 43.3 45.7
recursive cpu usage 15,041 4.2 4.4
redo blocks written 83,857 23.3 24.6
redo buffer allocation retries 3 0.0 0.0
redo entries 132,052 36.7 38.8
redo log space requests 3 0.0 0.0
redo log space wait time 14 0.0 0.0
redo size 40,661,028 11,294.7 11,934.6
redo synch time 695 0.2 0.2
redo synch writes 2,573 0.7 0.8
redo wastage 854,288 237.3 250.7
redo write time 907 0.3 0.3
redo writer latching time 0 0.0 0.0
redo writes 3,397 0.9 1.0
rollback changes - undo records a 415 0.1 0.1
rows fetched via callback 64,996 18.1 19.1
serializable aborts 3 0.0 0.0
session connect time 213,953,086,549 59,431,412.9 ############
session logical reads 3,588,873 996.9 1,053.4
session pga memory 7,049,728 1,958.3 2,069.2
session pga memory max 61,570,320 17,102.9 18,071.7
session uga memory 5,276,896 1,465.8 1,548.8
session uga memory max 128,351,464 35,653.2 37,672.9
shared hash latch upgrades - no w 87,961 24.4 25.8
shared hash latch upgrades - wait 0 0.0 0.0
sorts (disk) 1 0.0 0.0
sorts (memory) 9,466 2.6 2.8
sorts (rows) 3,876,175 1,076.7 1,137.7
summed dirty queue length 104 0.0 0.0
switch current to new buffer 2,482 0.7 0.7
table fetch by rowid 531,127 147.5 155.9
table fetch continued row 271 0.1 0.1
table scan blocks gotten 2,264,360 629.0 664.6
Instance Activity Stats for DB: ORA8 Instance: ora8 Snaps: 245 -246
Statistic Total per Second per Trans
--------------------------------- ------------------ -------------- ------------
table scan rows gotten 129,149,869 35,875.0 37,907.2
table scans (long tables) 124 0.0 0.0
table scans (short tables) 9,096 2.5 2.7
transaction rollbacks 20 0.0 0.0
user calls 228,687 63.5 67.1
user commits 2,947 0.8 0.9
user rollbacks 460 0.1 0.1
workarea executions - onepass 1 0.0 0.0
workarea executions - optimal 12,459 3.5 3.7
write clones created in foregroun 0 0.0 0.0
-------------------------------------------------------------
Tablespace IO Stats for DB: ORA8 Instance: ora8 Snaps: 245 -246
->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)
-------------- ------- ------ ------- ------------ -------- ---------- ------
MRP_3_DATA
1,866 1 1.7 3.6 3,340 1 0 0.0
MRP_3_IDX
2,207 1 5.1 7.9 887 0 0 0.0
UNDOTBS1
0 0 0.0 2,672 1 4 5.0
MRP_6_DATA
485 0 5.7 3.0 837 0 0 0.0
MRP_2_DATA
563 0 4.2 4.3 110 0 0 0.0
MRP_1_DATA
626 0 6.4 12.3 37 0 0 0.0
MRP_4_DATA
608 0 3.7 15.3 36 0 0 0.0
TEMP
30 0 172.0 26.9 404 0 0 0.0
MRP_2_IDX
234 0 7.4 1.0 138 0 0 0.0
MRP_1_IDX
143 0 8.0 1.0 120 0 0 0.0
MRP_5_IDX
64 0 5.6 1.0 90 0 0 0.0
PERFSTAT
0 0 0.0 151 0 0 0.0
TOOLS
17 0 9.4 1.0 121 0 0 0.0
YHY_PDM
14 0 7.9 1.0 46 0 0 0.0
MRP_5_DATA
2 0 10.0 1.0 33 0 0 0.0
SYSTEM
0 0 0.0 29 0 0 0.0
MRP_4_IDX
5 0 10.0 1.0 7 0 0 0.0
-------------------------------------------------------------
File IO Stats for DB: ORA8 Instance: ora8 Snaps: 245 -246
->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)
-------------- ------- ------ ------- ------------ -------- ---------- ------
MRP_1_DATA /database/mrp_1_data01.dbf
292 0 7.3 13.1 15 0 0
/database/mrp_1_data02.dbf
334 0 5.7 11.6 22 0 0
MRP_1_IDX /database/mrp_1_idx01.dbf
143 0 8.0 1.0 120 0 0
MRP_2_DATA /database/mrp_2_data01.dbf
305 0 3.4 3.6 69 0 0
/database/mrp_2_data02.dbf
258 0 5.0 5.1 41 0 0
MRP_2_IDX /database/mrp_2_idx01.dbf
234 0 7.4 1.0 138 0 0
MRP_3_DATA /database/mrp_3_data01.dbf
815 0 1.7 3.8 1,524 0 0
/database/mrp_3_data02.dbf
1,051 0 1.6 3.4 1,816 1 0
MRP_3_IDX /database/mrp_3_idx01.dbf
2,207 1 5.1 7.9 887 0 0
MRP_4_DATA /database/mrp_4_data01.dbf
294 0 3.2 15.2 34 0 0
/database/mrp_4_data02.dbf
304 0 3.9 15.5 2 0 0
/database/mrp_4_data03.dbf
10 0 10.0 11.9 0 0 0
MRP_4_IDX /database/mrp_4_idx01.dbf
5 0 10.0 1.0 7 0 0
MRP_5_DATA /database/mrp_5_data01.dbf
0 0 31 0 0
/database/mrp_5_data02.dbf
2 0 10.0 1.0 0 0 0
/database/mrp_5_data03.dbf
0 0 2 0 0
MRP_5_IDX /database/mrp_5_idx01.dbf
64 0 5.6 1.0 90 0 0
MRP_6_DATA /database/mrp_6_data01.dbf
485 0 5.7 3.0 837 0 0
PERFSTAT /database1/perfstat.dbf
0 0 151 0 0
SYSTEM /database/system01.dbf
0 0 29 0 0
File IO Stats for DB: ORA8 Instance: ora8 Snaps: 245 -246
->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)
-------------- ------- ------ ------- ------------ -------- ---------- ------
TEMP /database/temp01.dbf
30 0 172.0 26.9 404 0 0
TOOLS /database/tools01.dbf
17 0 9.4 1.0 121 0 0
UNDOTBS1 /database/undotbs01.dbf
0 0 2,672 1 4 5.0
YHY_PDM /database/YHY_PDM01.dbf
14 0 7.9 1.0 46 0 0
-------------------------------------------------------------
Buffer Pool Statistics for DB: ORA8 Instance: ora8 Snaps: 245 -246
-> Standard block size Pools D: default, K: keep, R: recycle
-> Default[@more@]
本人将最近在学习性能调优时，所用笔记总结如下，欢迎批评指正
本文将不断更新，欢迎补充。 （所列数据仅用于便于说明，没有实
际意义）
一、statspack 输出结果中必须查看的十项内容
1、负载间档(Load profile)
2、实例效率点击率(Instance efficiency hit ratios)
3、首要的5个等待事件(Top 5 wait events)
4、等待事件(Wait events)
5、闩锁等待
6、首要的SQL(Top sql)
7、实例活动(Instance activity)
8、文件I/O(File I/O)
9、内存分配(Memory allocation)
10、缓冲区等待(Buffer waits)
二、输出结果解释
1、报表头信息
数据库实例相关信息，包括数据库名称、ID、版本号及主机等信息
| Quote: | |
|
2、负载间档
该部分提供每秒和每个事物的统计信息，是监控系统吞吐量和负载变化的重要部分
| Quote: | |
|
说明:
Redo size:每秒产生的日志大小(单位字节)，可标志数据库任务的繁重与否
Logical reads:平决每秒产生的逻辑读，单位是block
block changes:每秒block变化数量，数据库事物带来改变的块数量
Physical reads:平均每秒数据库从磁盘读取的block数
Physical writes:平均每秒数据库写磁盘的block数
User calls:每秒用户call次数
Parses:每秒解析次数，近似反应每秒语句的执行次数
软解析每秒超过300次意味着你的"应用程序"效
率不高，没有使用soft soft parse，调整session_cursor_cache
Hard parses:每秒产生的硬解析次数
Sorts:每秒产生的排序次数
Executes:每秒执行次数
Transactions:每秒产生的事务数，反映数据库任务繁重与否
3、实例命中率
该部分可以提前找出ORACLE潜在将要发生的性能问题，很重要
| Quote: | |
|
说明:
Buffer Nowait %:在缓冲区中获取Buffer的未等待比率
Redo NoWait %:在Redo缓冲区获取Buffer的未等待比率
Buffer Hit %:数据块在数据缓冲区中得命中率，通常应在90%以上，否则，需要调整
In-memory Sort %:在内存中的排序率
Library Hit %:主要代表sql在共享区的命中率，通常在95%以上，否，需要要考虑加
大共享池，绑定变量，修改cursor_sharing等参数。
Soft Parse %:近似看作sql在共享区的命中率，小于<95%,需要考虑到绑定，如果低于80%，
那么就可能sql基本没有被重用
Execute to Parse %:sql语句解析后被重复执行的次数，如果过低，可以考虑设置
session_cached_cursors参数
Parse CPU to Parse Elapsd %:解析实际运行事件/(解析实际运行时间+解析中等待资源时间)
越高越好
% Non-Parse CPU:查询实际运行时间/(查询实际运行时间+sql解析时间)，太低表示解析消耗时间过多。
| Quote: | |
|
Shared Pool相关统计数据
Memory Usage %:共享池内存使用率，应该稳定在75%-90%间，太小浪费内存，太大则内存不足。
% SQL with executions>1:执行次数大于1的sql比率，若太小可能是没有使用bind variables。
% Memory for SQL w/exec>1:也即是memory for sql with execution > 1:执行次数大于1的sql
消耗内存/所有sql消耗的内存
4、首要等待事件
| Quote: | |
|
常见等待事件说明:
oracle等待事件是衡量oracle运行状况的重要依据及指示，主要有空闲等待事件和非空闲等待事件
；空闲等待事件是oracle正等待某种工作，在诊断和优化数据库时候，不用过多注意这部分事件，
非空闲等待事件专门针对oracle的活动，指数据库任务或应用程序运行过程中发生的等待，这些等待事件是我们在调整数据库应该关注的。
比较影响性能常见等待事件
db file scattered read
该事件通常与全表扫描有关。因为全表扫描是被放入内存中进行的进行的，
通常情况下它不可能被放入连续的缓冲区中，所以就散布在缓冲区的缓存中。该指数的数量过大说明
缺少索引或者限制使用索引。这种情况也可能是正常的，因为执行全表扫描可能比索引扫描效率更高。
当系统存在这些等待时，需要通过检查来确定全表扫描是否必需的来调整。可以尝试将较小的表放入
缓存keep中，避免反复读取它们。
db file sequential read
该事件说明在单个数据块上大量等待，该值过高通常是由于表间连接顺序很糟糕，或者使用了非选
择性索引。通过将这种等待与statspack报表中已知其它问题联系起来(如效率不高的sql),通过检查确
保索引扫描是必须的，并确保多表连接的连接顺序来调整
buffer busy wait
当缓冲区以一种非共享方式或者如正在被读入到缓冲时,就会出现该等待.该值不应该大于1%,确认
是不是由于热点块造成(如果是可以用反转索引,或者用更小块大小)
latch free
闩锁是底层的队列机制(更加准确的名称应该是互斥机制)，用于保护系统全局区(SGA)共享内存结构
。闩锁用于防止对内存结构的并行访问。如果闩锁不可用，就会记录一次闩锁丢失。绝大多数得闩锁问题
都与使用绑定变量失败(库缓存闩锁)、生成重作问题(重执行分配闩锁)、缓存的争用问题(缓存LRU链) 以
及缓存的热数据宽块(缓存链)有关。当闩锁丢失率高于0.5%时，需要调整这个问题。
log buffer space
日志缓冲区写的速度快于LGWR写REDOFILE的速度,可以增大日志文件大小，增加日志缓冲区的大小，或
者使用更快的磁盘来写数据。
logfile switch
通常是因为归档速度不够快,需要增大重做日志
log file sync
当一个用户提交或回滚数据时，LGWR将会话得重做操作从日志缓冲区填充到日志文件中，用户的进程
必须等待这个填充工作完成。为减少这个等待事件，须一次提交更多记录，或者将重做日志REDO LOG 文件
访在不同的物理磁盘上.
分析一下STATSPACK report for
DB Name DB Id Instance Inst Num Release Cluster Host
------------ ----------- ------------ -------- ----------- ------- ------------
ORA8 1365122498 ora8 1 9.2.0.1.0 NO IBM02
Snap Id Snap Time Sessions Curs/Sess Comment
------- ------------------ -------- --------- -------------------
Begin Snap: 245 04-8月 -04 14:00:03 318 6.7
End Snap: 246 04-8月 -04 15:00:03 340 8.8
Elapsed: 60.00 (mins)
Cache Sizes (end)
~~~~~~~~~~~~~~~~~
Buffer Cache: 1,360M Std Block Size: 8K
Shared Pool Size: 208M Log Buffer: 2,000K
Load Profile
~~~~~~~~~~~~ Per Second Per Transaction
--------------- ---------------
Redo size: 11,294.73 11,934.55
Logical reads: 996.91 1,053.38
Block changes: 72.60 76.71
Physical reads: 12.86 13.59
Physical writes: 5.57 5.89
User calls: 63.52 67.12
Parses: 10.34 10.93
Hard parses: 3.64 3.84
Sorts: 2.63 2.78
Logons: 0.09 0.09
Executes: 28.06 29.65
Transactions: 0.95
% Blocks changed per Read: 7.28 Recursive Call %: 40.50
Rollback per transaction %: 13.50 Rows per Sort: 409.44
Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 100.00 Redo NoWait %: 100.00
Buffer Hit %: 98.73 In-memory Sort %: 99.99
Library Hit %: 91.37 Soft Parse %: 64.85
Execute to Parse %: 63.14 Latch Hit %: 100.00
Parse CPU to Parse Elapsd %: 85.16 % Non-Parse CPU: 92.19
Shared Pool Statistics Begin End
------ ------
Memory Usage %: 96.25 96.60
% SQL with executions>1: 27.55 15.98
% Memory for SQL w/exec>1: 32.45 18.32
Top 5 Timed Events
~~~~~~~~~~~~~~~~~~ % Total
Event Waits Time (s) Ela Time
-------------------------------------------- ------------ ----------- --------
enqueue 116 345 50.81
CPU time 288 42.45
db file scattered read 3,308 15 2.21
db file sequential read 3,526 14 1.99
log file sync 2,570 7 1.02
-------------------------------------------------------------
Wait Events for DB: ORA8 Instance: ora8 Snaps: 245 -246
-> 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
---------------------------- ------------ ---------- ---------- ------ --------
enqueue 116 114 345 2972 0.0
db file scattered read 3,308 0 15 5 1.0
db file sequential read 3,526 0 14 4 1.0
log file sync 2,570 1 7 3 0.8
db file parallel write 1,078 539 6 5 0.3
control file parallel write 1,179 0 2 2 0.3
SQL*Net more data to client 39,260 0 2 0 11.5
latch free 140 5 0 3 0.0
SQL*Net break/reset to clien 340 0 0 1 0.1
log file switch completion 3 0 0 48 0.0
process startup 3 0 0 23 0.0
control file sequential read 548 0 0 0 0.2
log file sequential read 4 0 0 5 0.0
buffer busy waits 4 0 0 4 0.0
direct path write 4 0 0 4 0.0
log file parallel write 3,397 3,395 0 0 1.0
LGWR wait for redo copy 5 0 0 2 0.0
log file single write 4 0 0 1 0.0
undo segment extension 348 348 0 0 0.1
direct path read 30 0 0 0 0.0
SQL*Net message from client 226,481 0 377,967 1669 66.5
wakeup time manager 113 113 3,389 29991 0.0
jobq slave wait 66 63 195 2956 0.0
SQL*Net more data from clien 6 0 0 60 0.0
SQL*Net message to client 226,503 0 0 0 66.5
-------------------------------------------------------------
Background Wait Events for DB: ORA8 Instance: ora8 Snaps: 245 -246
-> ordered by wait time desc, waits desc (idle events last)
Avg
Total Wait wait Waits
Event Waits Timeouts Time (s) (ms) /txn
---------------------------- ------------ ---------- ---------- ------ --------
db file parallel write 1,078 539 6 5 0.3
control file parallel write 1,179 0 2 2 0.3
rdbms ipc reply 29 0 0 4 0.0
process startup 3 0 0 23 0.0
control file sequential read 481 0 0 0 0.1
log file sequential read 4 0 0 5 0.0
log file parallel write 3,397 3,395 0 0 1.0
buffer busy waits 3 0 0 3 0.0
LGWR wait for redo copy 5 0 0 2 0.0
log file single write 4 0 0 1 0.0
latch free 4 0 0 1 0.0
rdbms ipc message 14,195 11,021 16,541 1165 4.2
smon timer 14 9 3,344 ###### 0.0
-------------------------------------------------------------
分析一下SQL ordered by Gets for DB: ORA8 Instance: ora8 Snaps: 245 -246
-> 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
--------------- ------------ -------------- ------ -------- --------- ----------
681,196 90 7,568.8 19.0 22.70 26.61 1188822893
SELECT /*+ first_rows */ 0 FROM CLDE WHERE ADD_JOB_NUM=:1
591,129 853 693.0 16.5 32.33 34.33 391637996
SELECT COUNT(*) FROM WG WHERE WG = '1' AND TC = :b1 AND DH
= :b2 AND XH = :b3 AND CJ = :b4
202,544 14 14,467.4 5.6 0.76 0.83 3957953059
select yuangh,xingm,bumdm from archives where (bumdm in (select
site from users_dept1 where userid=:Usersite)) --SYF order by bu
mdm,yuangh
165,066 2,662 62.0 4.6 8.15 8.45 336027820
SELECT count(*) from face_rk where job_num = :b1
104,996 484 216.9 2.9 15.35 16.06 1556371957
select nianf,yuef,gongzlx,bumdm,kedh,banzdm,yuangh,xingm,xingb,y
uanglx,gongznx, slr_003,slr_009,slr_010,slr_007,slr_023,s
lr_029,slr_024,slr_028, slr_022,shiyj,gongjj,yilj,yanglj,
slr_030,slr_045,slr_042,kouc,buf,slr_043,slr_044, tesjs,s
hifjs,gongzzh, slr_016,slr_017,slr_019,slr_018,slr_026,
98,031 3 32,677.0 2.7 1.73 2.06 459237807
SELECT "TOOL_MX"."JOB_NUM" , "TOOL_MX"."NAME" ,
"TOOL_MX"."USE_UNIT" , "TOOL_MX"."GYBZ_MAN" ,
"TOOL_MX"."BZ_DATE" , "TOOL_MX"."JH_NUM" ,
"TOOL_MX"."DH_DATE" , "TOOL_MX"."LT_DATE" ,
"TOOL_MX"."SC_NUM" , "TOOL_MX"."RK_DATE" ,
91,861 10 9,186.1 2.6 10.81 12.08 2355361288
UPDATE DSJ.LIST SET NANUM_LINE=:b1 WHERE JOB_NUM1 = :b2
86,808 6 14,468.0 2.4 0.45 0.51 1190886821
select t.* ,t.rowid from archives t --WHERE BUMDM=:Usersite wh
ere (bumdm in (select site from users_dept1 where userid=:Usersi
te)) --SYF order by bumdm,yuangh
86,474 385 224.6 2.4 24.42 26.85 3809895136
begin pack_salary.p_salary_singlecalc(:nianf,:yuef,:gongzlx,:yua
ngh); end;
85,257 6 14,209.5 2.4 0.30 0.29 2516101932
select count(*) from ( select t.* ,t.rowid from archives t whe
re (bumdm in (select site from users_dept1 where userid=:Usersit
e)))
77,345 22 3,515.7 2.2 99.53 106.46 3462562281
INSERT into package_contents(job_num1,case_no,job_num,goods_name
,JOB_NUM11,unit) select :b1,:b2,job_num,str_check(str_check(name
)),JOB_NUM1,'件' from dsj.list where job_num LIKE '%'||:b1||'%'
SQL ordered by Gets for DB: ORA8 Instance: ora8 Snaps: 245 -246
-> 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
--------------- ------------ -------------- ------ -------- --------- ----------
73,946 117 632.0 2.1 3.21 3.30 2564771303
UPDATE OLD_LIST SET TOTAL_P=:b1 WHERE JOB_NUM = :b2
71,855 117 614.1 2.0 2.84 3.02 2395855240
SELECT COUNT(*) FROM OLD_LIST_TEMP WHERE PART_NUM = :b1
64,938 2 32,469.0 1.8 1.28 2.67 3582756360
UPDATE OLD_LIST_TEMP SET N_UNIT_W=0,N_TOTAL_W=0,SPARE_P=0
54,269 614 88.4 1.5 5.04 6.52 145329864
UPDATE LIST SET MAT=:1,PIECE=:2,UNIT_W=:3,TYPE_CODE=:4,TYPE=:5,L
INE=:6,NANUM_LINE=:7 WHERE ROWID=:8
42,728 3 14,242.7 1.2 0.75 1.01 3169921088
UPDATE FACE SET DES_P=:1,MX_DONE_DATE=:2,LINE_WRITER=:3,LINE_DON
E_DATE=:4,LINE_CHECK=:5,NANUM_LINE=:6 WHERE ROWID=:7
41,690 102 408.7 1.2 0.33 0.35 709258517
select constraint_name, column_name from sys.all_cons_columns wh
ere owner = :owner and table_name = :table_name and constraint_n
ame not like 'SYS_C%' order by constraint_name, position
39,804 2 19,902.0 1.1 1.73 3.23 1492226261
select list.line LX, list.job_num1 glh, list.job_num dh, list.na
me mc, list.piece sl, list.mat cz, list.unit_w dz, list.des_size
fm, list.des_p zs, list.des_work a1, list.bj_code gzj , list.ym
d rq, list.m cs, list.man xgr, list.type clzb, list.type_code zb
, list_bak1.line, list_bak1.name, list_bak1.piece, list_bak1.mat
30,243 4,971 6.1 0.8 0.53 0.52 1404744636
SELECT * from salaryformula where gongsh = :b1 order by gongsh
28,399 6 4,733.2 0.8 1.21 4.31 877854086
SELECT * FROM salaryresult WHERE nianf=:b6 and yuef>=:
b5 AND yuef<=:b4 and gongzlx=:b3 and (BUMDM>=:b2 or :b
2 is null)and (BUMDM<=:b1 or :b1 is null)
21,549 5 4,309.8 0.6 0.29 0.39 803228315
begin pack_salary.p_update_work_Age(:bumdm,:yuangh1,:yuangh2,:yu
anglx1,:yuanglx2); end;
20,049 717 28.0 0.6 0.26 0.31 601639400
UPDATE Archives set gongznx = PACK_SALARY.F_WORK_AGE(:b3
,:b2) where yuangh= :b1
17,170 34 505.0 0.5 0.53 0.52 2722690622
SELECT NVL(MAX(XH),0) + 1 FROM WELD_RKTZ WHERE :b1 = JOB_NUM
SQL ordered by Gets for DB: ORA8 Instance: ora8 Snaps: 245 -246
-> 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
--------------- ------------ -------------- ------ -------- --------- ----------
16,182 261 62.0 0.5 0.15 0.19 2128744839
SELECT Key_ From Pdm_Temp_Tree Where Job_Num = :b1
15,566 79 197.0 0.4 0.88 1.48 3980150586
SELECT SCJH_04_QJ.JOB_NUM1, SCJH_04_QJ.PIECE, SCJH_04_QJ.NAME,
SCJH_04_QJ.MAT, SCJH_04_QJ.PIECE0, SCJH_04_QJ.UNIT_W, SCJH_04_
QJ.TC_NUM, SCJH_04_QJ.PRE_SHOP, SCJH_04_QJ.NEXT_SHOP, SCJH_04_Q
J.H1, SCJH_04_QJ.H2, SCJH_04_QJ.H3, SCJH_04_QJ.H4, SCJH_04_QJ.H5
, SCJH_04_QJ.H6, SCJH_04_QJ.TECH_REQ, SCJH_04_QJ.CLASS, SCJH_04
15,132 2 7,566.0 0.4 5.32 5.38 1269080801
select count(job_num1),mc,cz,ltrim(zg),sum(zs) zs1,sum(jz) jz1,b
a, sum(mz) mz1,sum(de) de1,lm from ybx.clde group by lb,job_nu
m1,mc,cz,zg,ba,lm,bj having (lm='c' or lm='C') and bj='jj' and (
lb='1'or lb='4') and job_num1=:job1 order by mc,cz,ba,decode(ins
-------------------------------------------------------------
SQL ordered by Reads for DB: ORA8 Instance: ora8 Snaps: 245 -246
-> End Disk Reads Threshold: 1000
CPU Elapsd
Physical Reads Executions Reads per Exec %Total Time (s) Time (s) Hash Value
--------------- ------------ -------------- ------ -------- --------- ----------
8,984 2 4,492.0 19.4 1.66 3.70 1933892864
SELECT "COOL_ZZP"."UNIT" , "COOL_ZZP"."SR_DATE" ,
"COOL_ZZP"."JOB_NUM" , "COOL_ZZP"."SD_ZGS" ,
"COOL_ZZP"."JG_CL" , "COOL_ZZP"."WT_JG" ,
"COOL_ZZP"."BZ_J" , "COOL_ZZP"."BZ_F" ,
"COOL_ZZP"."ZY_GJ" , "COOL_ZZP"."FP_SS" ,
7,556 90 84.0 16.3 22.70 26.61 1188822893
SELECT /*+ first_rows */ 0 FROM CLDE WHERE ADD_JOB_NUM=:1
6,407 1 6,407.0 13.8 1.31 2.62 2933674388
SELECT SUM(PLAN_TIME1) FROM WELDPR.NEW_PROC_REL,DSJ.LIST WHER
E NEW_PROC_REL.JOB_NUM1 = :b1 AND NEW_PROC_REL.JOB_NUM = LIST.J
OB_NUM AND LIST.LINE NOT LIKE '3000%'
5,319 1 5,319.0 11.5 0.61 3.02 2934925061
SELECT DISTINCT JSDW FROM dsj.tzd WHERE HDR='MX' and JSR IS NULL
3,902 2 1,951.0 8.4 1.73 3.23 1492226261
select list.line LX, list.job_num1 glh, list.job_num dh, list.na
me mc, list.piece sl, list.mat cz, list.unit_w dz, list.des_size
fm, list.des_p zs, list.des_work a1, list.bj_code gzj , list.ym
d rq, list.m cs, list.man xgr, list.type clzb, list.type_code zb
, list_bak1.line, list_bak1.name, list_bak1.piece, list_bak1.mat
1,562 2 781.0 3.4 0.22 0.76 132186308
SELECT COUNT(*) FROM LIST_TEMP_INDEX WHERE JOB_NUM1 = :b1
1,189 6 198.2 2.6 1.21 4.31 877854086
SELECT * FROM salaryresult WHERE nianf=:b6 and yuef>=:
b5 AND yuef<=:b4 and gongzlx=:b3 and (BUMDM>=:b2 or :b
2 is null)and (BUMDM<=:b1 or :b1 is null)
675 773 0.9 1.5 0.27 0.94 2650523022
SELECT SUM(DES_WORK) FROM DSJ.LIST WHERE JOB_NUM1 = :b1
161 614 0.3 0.3 5.04 6.52 145329864
UPDATE LIST SET MAT=:1,PIECE=:2,UNIT_W=:3,TYPE_CODE=:4,TYPE=:5,L
INE=:6,NANUM_LINE=:7 WHERE ROWID=:8
152 79 1.9 0.3 0.88 1.48 3980150586
SELECT SCJH_04_QJ.JOB_NUM1, SCJH_04_QJ.PIECE, SCJH_04_QJ.NAME,
SCJH_04_QJ.MAT, SCJH_04_QJ.PIECE0, SCJH_04_QJ.UNIT_W, SCJH_04_
QJ.TC_NUM, SCJH_04_QJ.PRE_SHOP, SCJH_04_QJ.NEXT_SHOP, SCJH_04_Q
J.H1, SCJH_04_QJ.H2, SCJH_04_QJ.H3, SCJH_04_QJ.H4, SCJH_04_QJ.H5
, SCJH_04_QJ.H6, SCJH_04_QJ.TECH_REQ, SCJH_04_QJ.CLASS, SCJH_04
109 111 1.0 0.2 0.04 1.05 4289400484
SELECT ROWID,JOB_NUM,JOB_NUM1,TABLE_N,PROMPTED,WELD_CODE,START_D
ATE,END_DATE,REJECT_MARK,INPUT_DATE,PROCESS,OPMH,BJ,MODIGY_OPM,T
F,ZP_DATE,CH_IP,CH_PROM,RK_DATE,WG_DATE,FL_DATE,TE_SPEC,OPM FROM
NEW_TECH_REL WHERE (JOB_NUM=:1)
SQL ordered by Reads for DB: ORA8 Instance: ora8 Snaps: 245 -246
-> End Disk Reads Threshold: 1000
CPU Elapsd
Physical Reads Executions Reads per Exec %Total Time (s) Time (s) Hash Value
--------------- ------------ -------------- ------ -------- --------- ----------
96 484 0.2 0.2 15.35 16.06 1556371957
select nianf,yuef,gongzlx,bumdm,kedh,banzdm,yuangh,xingm,xingb,y
uanglx,gongznx, slr_003,slr_009,slr_010,slr_007,slr_023,s
lr_029,slr_024,slr_028, slr_022,shiyj,gongjj,yilj,yanglj,
slr_030,slr_045,slr_042,kouc,buf,slr_043,slr_044, tesjs,s
hifjs,gongzzh, slr_016,slr_017,slr_019,slr_018,slr_026,
77 22 3.5 0.2 99.53 106.46 3462562281
INSERT into package_contents(job_num1,case_no,job_num,goods_name
,JOB_NUM11,unit) select :b1,:b2,job_num,str_check(str_check(name
)),JOB_NUM1,'件' from dsj.list where job_num LIKE '%'||:b1||'%'
75 94 0.8 0.2 0.24 0.91 1011885834
select job_num,job_num1,part_num,part_num1,des_num,des_size,des_
p,name,piece,mat,unit_w,line,type,bj_code,tz_order,M,LINE_M from
list where job_num1=upper(:job_num111) order by REPLACE(job_num
,'M')
74 20 3.7 0.2 0.16 1.05 4191266530
SELECT ALL LIST.NAME,LIST.MAT,list.m, LIST.PIECE,LIST
.UNIT_W, LIST.job_NUM,list.bj_code, substr
(list.job_num,1,9), substr(list.job_num,10,11),
substr(list.job_num,12,13), substr(list.job_num
,14,16), list.line, LIST.PART_NUM1,LIST.DE
67 107 0.6 0.1 0.06 0.89 2302688342
INSERT INTO CLDE(BJ,JOB_NUM1,RQ,JOB_NUM,ADD_JOB_NUM,CZ,ZS,JZ,DE,
MC,DG,ZG,LYL,LM,MK,TX,LB,MZ,BZ,BA,GG,AA,BB) VALUES (:BJ,:JOB_NUM
1,:RQ,:JOB_NUM,:ADD_JOB_NUM,:CZ,:ZS,:JZ,:DE,:MC,:DG,:ZG,:LYL,:LM
,:MK,:TX,:LB,:MZ,:BZ,:BA,:GG,:AA,:BB)
45 362 0.1 0.1 0.11 0.35 3190524044
SELECT ROWID,PLAN_TIME,WORK_SHOP,JOB_NUM,JOB_NUM1,TABLE_N,PROCES
S_COST,PROCESS_GROUP,PLAN_TIME1,PLAN_TIME_MODI_OPH,TABLE_TIME,TF
,BEGIN_DATE,FINISH_TIME,FINISH_PIECE,FINISH_DATE,FINISH_MARK,INP
UT_DATE,PROCESS_CODE FROM NEW_PROC_REL WHERE (JOB_NUM=:1)
41 36 1.1 0.1 0.02 0.24 1367683388
SELECT COUNT(*) FROM DSJ.LIST WHERE JOB_NUM1 = UPPER(:b1) AN
D (LINE LIKE '%01%' OR LINE LIKE '%02%' OR LINE LIKE '%03%'
OR LINE LIKE '%26%' OR LINE LIKE '%29%' OR LINE LIKE '%40
W%' OR LINE LIKE '%40B%' OR LINE LIKE '%230%' OR LINE LIKE
'%240%' OR LINE LIKE '%43W%' OR LINE LIKE '%42W%' OR LINE
36 372 0.1 0.1 0.33 1.10 1984204482
begin pack_salary.p_salaryresult_insert(:nianf,:yuef,:gongzlx,
:bumdm,:kedh,:banzdm,:yuangh,:xingm,:xingb,:yuanglx,:gongznx,:sl
-------------------------------------------------------------
SQL ordered by Executions for DB: ORA8 Instance: ora8 Snaps: 245 -246
-> End Executions Threshold: 100
CPU per Elap per
Executions Rows Processed Rows per Exec Exec (s) Exec (s) Hash Value
------------ --------------- ---------------- ----------- ---------- ----------
4,971 12,856 2.6 0.00 0.00 1404744636
SELECT * from salaryformula where gongsh = :b1 order by gongsh
4,725 4,718 1.0 0.00 0.00 4080986794
SELECT PROC_GROUP,PLAN_PRICE FROM ASSETS.V_ASSETS WHERE PROC_
GROUP = :b1
2,662 2,662 1.0 0.00 0.00 336027820
SELECT count(*) from face_rk where job_num = :b1
2,440 2,440 1.0 0.00 0.00 4121249898
INSERT into bbb(job_num,JOB_NUM_1,JOB_NUM1,JOB_NUM1_1,in_date,us
ee,machine,comm) select :b4 ,:b3 ,:b2 ,:
b1 ,sysdate,userNAME,machine,program from v$session
WHERE SID = (select sid from v$mystat where rownum = 1)
1,572 1,572 1.0 0.00 0.00 2163629200
SELECT COUNT(*) FROM DSJ.FACE WHERE JOB_NUM = :b1
1,570 1,570 1.0 0.00 0.00 2704824524
SELECT MX_WRITER,LINE_WRITER FROM DSJ.FACE WHERE JOB_NUM = :b
1
1,250 1,250 1.0 0.00 0.00 1560113101
select * from HR_C1 WHERE NIANF=:NIANF AND YUEF=:YUEF and BUMDM
=:BUMDM
1,128 0 0.0 0.00 0.00 278968606
SAVEPOINT FM_1
1,072 1,072 1.0 0.00 0.00 3784857994
SELECT Count(*) From Pdm.Protree Where Symbol = :b1
962 274 0.3 0.00 0.00 272842946
select YUANGH,BUF from salaryresult WHERE NIANF=:NIANF AND YUEF
=:YUEF AND BUMDM=:BUMDM AND YUANGH=:YUANGH
946 901 1.0 0.00 0.00 633456687
select yuangh,xingm,xingb,bumdm,kedh,banzdm,yuanglx,gongznx,rugs
r,zhengssy,zhic, jib,fengxxs,jindj,jijf,ganbbz,cjgg from
archives where bumdm=:usersite and yuangh=:yuangh
912 0 0.0 0.00 0.00 3615375148
COMMIT
853 853 1.0 0.04 0.04 391637996
SELECT COUNT(*) FROM WG WHERE WG = '1' AND TC = :b1 AND DH
= :b2 AND XH = :b3 AND CJ = :b4
800 800 1.0 0.00 0.00 3115407350
begin pack_salary.p_salaryresult_fetch(:lock,:nianf,:yuef,:gon
gzlx,:yuangh,:xingm); end;
793 793 1.0 0.00 0.00 4246749948
SQL ordered by Executions for DB: ORA8 Instance: ora8 Snaps: 245 -246
-> End Executions Threshold: 100
CPU per Elap per
Executions Rows Processed Rows per Exec Exec (s) Exec (s) Hash Value
------------ --------------- ---------------- ----------- ---------- ----------
INSERT into SALARY_APPEDX( nianf,
yuef, bumdm,
yuangh, xingm,
SLR_030, SL
R_100, SLR_101,
786 0 0.0 0.00 0.00 3758061761
savepoint DOA__LOCKPOINT
773 773 1.0 0.00 0.00 2650523022
SELECT SUM(DES_WORK) FROM DSJ.LIST WHERE JOB_NUM1 = :b1
725 725 1.0 0.00 0.00 1281731196
SELECT xingm from salaryresult where nianf=:b4
and yuef=:b3 and gongzlx=:b2 and yuangh=
:b1
717 715 1.0 0.00 0.00 601639400
UPDATE Archives set gongznx = PACK_SALARY.F_WORK_AGE(:b3
,:b2) where yuangh= :b1
715 715 1.0 0.00 0.00 3193665429
SELECT round(trunc(sysdate),'dd') from dual
715 715 1.0 0.00 0.00 3263940320
SELECT pyc_005 from PAYROLL_C
702 702 1.0 0.00 0.00 2964743345
select count(*) from sys.job$ where (next_date > sysdate) and (n
ext_date < (sysdate+5/86400))
691 1 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
654 654 1.0 0.00 0.00 1754174331
SELECT COUNT(*) FROM WELDPR.REL_TABL WHERE BJ = '工艺' AND J
OB_NUM = :b1
644 0 0.0 0.00 0.00 502510949
select privilege#,level from sysauth$ connect by grantee#=prior
privilege# and privilege#>0 start with grantee#=:1 and privilege
#>0
644 644 1.0 0.00 0.00 1498920852
select max(nvl(option$,0)) from sysauth$ where privilege#=:1 con
nect by grantee#=prior privilege# and privilege#>0 start with (g
rantee#=:2 or grantee#=1) and privilege#>0 group by privilege#
614 614 1.0 0.01 0.01 145329864
UPDATE LIST SET MAT=:1,PIECE=:2,UNIT_W=:3,TYPE_CODE=:4,TYPE=:5,L
INE=:6,NANUM_LINE=:7 WHERE ROWID=:8
SQL ordered by Executions for DB: ORA8 Instance: ora8 Snaps: 245 -246
-> End Executions Threshold: 100
CPU per Elap per
Executions Rows Processed Rows per Exec Exec (s) Exec (s) Hash Value
------------ --------------- ---------------- ----------- ---------- ----------
613 613 1.0 0.00 0.00 2285186583
SELECT MAT,PIECE,UNIT_W,TYPE_CODE,TYPE,LINE,NANUM_LINE FROM LIST
WHERE ROWID=:1 FOR UPDATE OF MAT NOWAIT
601 595 1.0 0.00 0.00 3693280073
SELECT PROC_GROUP FROM ASSETS.V_ASSETS WHERE PROC_GROUP = '13
H' || :b1
597 597 1.0 0.00 0.00 2962403619
SELECT NVL(MAX(PROCESS_CODE),0) + 1 FROM NEW_PROC_REL WHERE
:b1 = JOB_NUM
593 593 1.0 0.00 0.00 1192975401
INSERT INTO NEW_PROC_REL(WORK_SHOP,PLAN_TIME,JOB_NUM,JOB_NUM1,TA
BLE_N,TF,PROCESS_COST,PROCESS_GROUP,PLAN_TIME1,TABLE_TIME,BEGIN_
DATE,FINISH_TIME,FINISH_PIECE,FINISH_DATE,FINISH_MARK,INPUT_DATE
-------------------------------------------------------------
SQL ordered by Parse Calls for DB: ORA8 Instance: ora8 Snaps: 245 -246
-> End Parse Calls Threshold: 1000
% Total
Parse Calls Executions Parses Hash Value
------------ ------------ -------- ----------
4,725 4,725 12.69 4080986794
SELECT PROC_GROUP,PLAN_PRICE FROM ASSETS.V_ASSETS WHERE PROC_
GROUP = :b1
1,128 1,128 3.03 278968606
SAVEPOINT FM_1
800 800 2.15 3115407350
begin pack_salary.p_salaryresult_fetch(:lock,:nianf,:yuef,:gon
gzlx,:yuangh,:xingm); end;
786 786 2.11 3758061761
savepoint DOA__LOCKPOINT
644 644 1.73 502510949
select privilege#,level from sysauth$ connect by grantee#=prior
privilege# and privilege#>0 start with grantee#=:1 and privilege
#>0
644 644 1.73 1498920852
select max(nvl(option$,0)) from sysauth$ where privilege#=:1 con
nect by grantee#=prior privilege# and privilege#>0 start with (g
rantee#=:2 or grantee#=1) and privilege#>0 group by privilege#
601 601 1.61 3693280073
SELECT PROC_GROUP FROM ASSETS.V_ASSETS WHERE PROC_GROUP = '13
H' || :b1
597 597 1.60 2962403619
SELECT NVL(MAX(PROCESS_CODE),0) + 1 FROM NEW_PROC_REL WHERE
:b1 = JOB_NUM
407 407 1.09 595939768
begin pack_salary.P_SALARY_APPEDX_FETCH(:lock,:nianf,:yuef,:yu
angh,:bumdm,:xingm); end;
388 388 1.04 1882717930
select * from SALARYBASE where rowid = :doa__rowid
385 385 1.03 3809895136
begin pack_salary.p_salary_singlecalc(:nianf,:yuef,:gongzlx,:yua
ngh); end;
372 372 1.00 1984204482
begin pack_salary.p_salaryresult_insert(:nianf,:yuef,:gongzlx,
:bumdm,:kedh,:banzdm,:yuangh,:xingm,:xingb,:yuanglx,:gongznx,:sl
r_003,:slr_009,:slr_010,:slr_007,:slr_023,:slr_029,:slr_024,:slr
_028,:slr_022,:shiyj,:gongjj,:yilj,:yanglj,:slr_030,:slr_045,:sl
r_042,:kouc,:buf,:slr_043,:slr_044,:tesjs,:shifjs,:gongzzh,:slr_
247 247 0.66 2959273380
set role CONNECT,RES
241 241 0.65 2467811771
SQL ordered by Parse Calls for DB: ORA8 Instance: ora8 Snaps: 245 -246
-> End Parse Calls Threshold: 1000
% Total
Parse Calls Executions Parses Hash Value
------------ ------------ -------- ----------
COMMIT work comment ''
204 204 0.55 1010299517
begin pack_salary.P_SALARY_APPEDX_update(:nianf,:yuef,:gongzlx
,:bumdm,:yuangh,:xingm,:SLR_030,:SLR_100,:SLR_101,:SLR_102,:SLR_
103); end;
200 200 0.54 1323314622
UPDATE NEW_TECH_REL SET OPMH=:b1 WHERE JOB_NUM = :b2
196 196 0.53 3013728279
select privilege#,level from sysauth$ connect by grantee#=prior
privilege# and privilege#>0 start with (grantee#=:1 or grantee#=
1) and privilege#>0
158 158 0.42 1470906206
ROLLBACK
151 151 0.41 1656643811
ROLLBACK TO FM_1
147 147 0.39 319793062
alter session set nls_language= 'SIMPLIFIED CHINESE' nls_territo
ry= 'CHINA' nls_currency= '$' nls_iso_currency= 'CHINA' nls_nume
ric_characters= '.,' nls_date_format= 'DD-MON-YY' nls_date_langu
age= 'SIMPLIFIED CHINESE' nls_sort= 'BINARY' nls_calendar= 'GREG
ORIAN'
146 146 0.39 3397948939
ALTER SESSION SET REMOTE_DEPENDENCIES_MODE=SIGNATURE
142 142 0.38 3357351478
SELECT DEVICE_NAME,TOTAL_W FROM DSJ.FACE WHERE JOB_NUM = :b1
119 119 0.32 1141046533
SELECT * FROM DSJ.FACE where job_num=upper(:job_num111)
118 119 0.32 3809432628
SELECT COUNT(*) FROM DSJ.FACE_RK WHERE JOB_NUM = :b1
115 115 0.31 2576475893
select * from SALARYBASE where rowid = :doa__rowid for update no
wait
114 114 0.31 1259305139
UPDATE NEW_REL_TECH SET NAME_BZ=:b1 WHERE JOB_NUM = :b2
114 114 0.31 3744481045
UPDATE NEW_REL_TECH SET MATERIAL_SOURCE=:b1 WHERE JOB_NUM = :b2
113 113 0.30 238087931
select t.schema, t.name, t.flags, q.name from system.aq$_queue_t
ables t, sys.aq$_queue_table_affinities aft, system.aq$_que
ues q where aft.table_objno = t.objno and aft.owner_instance = :
SQL ordered by Parse Calls for DB: ORA8 Instance: ora8 Snaps: 245 -246
-> End Parse Calls Threshold: 1000
% Total
Parse Calls Executions Parses Hash Value
------------ ------------ -------- ----------
1 and q.table_objno = t.objno and q.usage = 0 and b
itand(t.flags, 4+16+32+64+128+256) = 0 for update of t.name, aft
113 113 0.30 2780709284
select q_name, state, delay, expiration, rowid, msgid, dequeue
_msgid, chain_no, local_order_no, enq_time, enq_tid, step_no,
priority, exception_qschema, exception_queue, retry_count, corri
d, time_manager_info, sender_name, sender_address, sender_prot
ocol from SYS.AQ_SRVNTFN_TABLE where time_manager_info <= :1
113 113 0.30 2979542350
select q_name, state, delay, expiration, rowid, msgid, dequeu
e_msgid, chain_no, local_order_no, enq_time, enq_tid, step_no,
priority, exception_qschema, exception_queue, retry_count, corr
id, time_manager_info from SYS.AQ_EVENT_TABLE where time
-------------------------------------------------------------
分析一下Instance Activity Stats for DB: ORA8 Instance: ora8 Snaps: 245 -246
Statistic Total per Second per Trans
--------------------------------- ------------------ -------------- ------------
CPU used by this session 28,801 8.0 8.5
CPU used when call started 28,798 8.0 8.5
CR blocks created 803 0.2 0.2
DBWR buffers scanned 15,823 4.4 4.6
DBWR checkpoint buffers written 8,552 2.4 2.5
DBWR checkpoints 1 0.0 0.0
DBWR free buffers found 15,727 4.4 4.6
DBWR lru scans 9 0.0 0.0
DBWR make free requests 9 0.0 0.0
DBWR revisited being-written buff 0 0.0 0.0
DBWR summed scan depth 15,823 4.4 4.6
DBWR transaction table writes 28 0.0 0.0
DBWR undo block writes 2,644 0.7 0.8
SQL*Net roundtrips to/from client 225,575 62.7 66.2
active txn count during cleanout 975 0.3 0.3
background checkpoints completed 0 0.0 0.0
background checkpoints started 1 0.0 0.0
background timeouts 4,234 1.2 1.2
branch node splits 2 0.0 0.0
buffer is not pinned count 2,650,098 736.1 777.8
buffer is pinned count 1,204,540 334.6 353.6
bytes received via SQL*Net from c 11,612,130 3,225.6 3,408.3
bytes sent via SQL*Net to client 120,585,036 33,495.8 35,393.3
calls to get snapshot scn: kcmgss 132,881 36.9 39.0
calls to kcmgas 7,656 2.1 2.3
calls to kcmgcs 587 0.2 0.2
change write time 196 0.1 0.1
cleanout - number of ktugct calls 1,084 0.3 0.3
cluster key scan block gets 34,529 9.6 10.1
cluster key scans 14,590 4.1 4.3
commit cleanout failures: block l 0 0.0 0.0
commit cleanout failures: callbac 3 0.0 0.0
commit cleanouts 15,685 4.4 4.6
commit cleanouts successfully com 15,682 4.4 4.6
commit txn count during cleanout 337 0.1 0.1
consistent changes 1,790 0.5 0.5
consistent gets 3,403,246 945.4 998.9
consistent gets - examination 632,808 175.8 185.7
cursor authentications 598 0.2 0.2
data blocks consistent reads - un 1,790 0.5 0.5
db block changes 261,365 72.6 76.7
db block gets 185,627 51.6 54.5
deferred (CURRENT) block cleanout 9,025 2.5 2.7
dirty buffers inspected 8 0.0 0.0
enqueue conversions 1,017 0.3 0.3
enqueue releases 13,488 3.8 4.0
enqueue requests 13,496 3.8 4.0
enqueue timeouts 0 0.0 0.0
enqueue waits 3 0.0 0.0
execute count 101,030 28.1 29.7
free buffer inspected 8 0.0 0.0
free buffer requested 49,399 13.7 14.5
hot buffers moved to head of LRU 1,955 0.5 0.6
immediate (CR) block cleanout app 562 0.2 0.2
immediate (CURRENT) block cleanou 1,883 0.5 0.6
index fast full scans (full) 11 0.0 0.0
Instance Activity Stats for DB: ORA8 Instance: ora8 Snaps: 245 -246
Statistic Total per Second per Trans
--------------------------------- ------------------ -------------- ------------
index fetch by key 467,102 129.8 137.1
index scans kdiixs1 86,292 24.0 25.3
leaf node 90-10 splits 3 0.0 0.0
leaf node splits 193 0.1 0.1
logons cumulative 320 0.1 0.1
messages received 3,788 1.1 1.1
messages sent 3,788 1.1 1.1
no buffer to keep pinned count 0 0.0 0.0
no work - consistent read gets 2,677,804 743.8 786.0
opened cursors cumulative 33,252 9.2 9.8
parse count (failures) 13 0.0 0.0
parse count (hard) 13,088 3.6 3.8
parse count (total) 37,236 10.3 10.9
parse time cpu 2,250 0.6 0.7
parse time elapsed 2,642 0.7 0.8
physical reads 46,291 12.9 13.6
physical reads direct 808 0.2 0.2
physical writes 20,067 5.6 5.9
physical writes direct 11,411 3.2 3.4
physical writes non checkpoint 13,846 3.9 4.1
prefetched blocks 38,649 10.7 11.3
process last non-idle time 213,953,086,549 59,431,412.9 ############
recovery blocks read 0 0.0 0.0
recursive calls 155,692 43.3 45.7
recursive cpu usage 15,041 4.2 4.4
redo blocks written 83,857 23.3 24.6
redo buffer allocation retries 3 0.0 0.0
redo entries 132,052 36.7 38.8
redo log space requests 3 0.0 0.0
redo log space wait time 14 0.0 0.0
redo size 40,661,028 11,294.7 11,934.6
redo synch time 695 0.2 0.2
redo synch writes 2,573 0.7 0.8
redo wastage 854,288 237.3 250.7
redo write time 907 0.3 0.3
redo writer latching time 0 0.0 0.0
redo writes 3,397 0.9 1.0
rollback changes - undo records a 415 0.1 0.1
rows fetched via callback 64,996 18.1 19.1
serializable aborts 3 0.0 0.0
session connect time 213,953,086,549 59,431,412.9 ############
session logical reads 3,588,873 996.9 1,053.4
session pga memory 7,049,728 1,958.3 2,069.2
session pga memory max 61,570,320 17,102.9 18,071.7
session uga memory 5,276,896 1,465.8 1,548.8
session uga memory max 128,351,464 35,653.2 37,672.9
shared hash latch upgrades - no w 87,961 24.4 25.8
shared hash latch upgrades - wait 0 0.0 0.0
sorts (disk) 1 0.0 0.0
sorts (memory) 9,466 2.6 2.8
sorts (rows) 3,876,175 1,076.7 1,137.7
summed dirty queue length 104 0.0 0.0
switch current to new buffer 2,482 0.7 0.7
table fetch by rowid 531,127 147.5 155.9
table fetch continued row 271 0.1 0.1
table scan blocks gotten 2,264,360 629.0 664.6
Instance Activity Stats for DB: ORA8 Instance: ora8 Snaps: 245 -246
Statistic Total per Second per Trans
--------------------------------- ------------------ -------------- ------------
table scan rows gotten 129,149,869 35,875.0 37,907.2
table scans (long tables) 124 0.0 0.0
table scans (short tables) 9,096 2.5 2.7
transaction rollbacks 20 0.0 0.0
user calls 228,687 63.5 67.1
user commits 2,947 0.8 0.9
user rollbacks 460 0.1 0.1
workarea executions - onepass 1 0.0 0.0
workarea executions - optimal 12,459 3.5 3.7
write clones created in foregroun 0 0.0 0.0
-------------------------------------------------------------
Tablespace IO Stats for DB: ORA8 Instance: ora8 Snaps: 245 -246
->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)
-------------- ------- ------ ------- ------------ -------- ---------- ------
MRP_3_DATA
1,866 1 1.7 3.6 3,340 1 0 0.0
MRP_3_IDX
2,207 1 5.1 7.9 887 0 0 0.0
UNDOTBS1
0 0 0.0 2,672 1 4 5.0
MRP_6_DATA
485 0 5.7 3.0 837 0 0 0.0
MRP_2_DATA
563 0 4.2 4.3 110 0 0 0.0
MRP_1_DATA
626 0 6.4 12.3 37 0 0 0.0
MRP_4_DATA
608 0 3.7 15.3 36 0 0 0.0
TEMP
30 0 172.0 26.9 404 0 0 0.0
MRP_2_IDX
234 0 7.4 1.0 138 0 0 0.0
MRP_1_IDX
143 0 8.0 1.0 120 0 0 0.0
MRP_5_IDX
64 0 5.6 1.0 90 0 0 0.0
PERFSTAT
0 0 0.0 151 0 0 0.0
TOOLS
17 0 9.4 1.0 121 0 0 0.0
YHY_PDM
14 0 7.9 1.0 46 0 0 0.0
MRP_5_DATA
2 0 10.0 1.0 33 0 0 0.0
SYSTEM
0 0 0.0 29 0 0 0.0
MRP_4_IDX
5 0 10.0 1.0 7 0 0 0.0
-------------------------------------------------------------
File IO Stats for DB: ORA8 Instance: ora8 Snaps: 245 -246
->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)
-------------- ------- ------ ------- ------------ -------- ---------- ------
MRP_1_DATA /database/mrp_1_data01.dbf
292 0 7.3 13.1 15 0 0
/database/mrp_1_data02.dbf
334 0 5.7 11.6 22 0 0
MRP_1_IDX /database/mrp_1_idx01.dbf
143 0 8.0 1.0 120 0 0
MRP_2_DATA /database/mrp_2_data01.dbf
305 0 3.4 3.6 69 0 0
/database/mrp_2_data02.dbf
258 0 5.0 5.1 41 0 0
MRP_2_IDX /database/mrp_2_idx01.dbf
234 0 7.4 1.0 138 0 0
MRP_3_DATA /database/mrp_3_data01.dbf
815 0 1.7 3.8 1,524 0 0
/database/mrp_3_data02.dbf
1,051 0 1.6 3.4 1,816 1 0
MRP_3_IDX /database/mrp_3_idx01.dbf
2,207 1 5.1 7.9 887 0 0
MRP_4_DATA /database/mrp_4_data01.dbf
294 0 3.2 15.2 34 0 0
/database/mrp_4_data02.dbf
304 0 3.9 15.5 2 0 0
/database/mrp_4_data03.dbf
10 0 10.0 11.9 0 0 0
MRP_4_IDX /database/mrp_4_idx01.dbf
5 0 10.0 1.0 7 0 0
MRP_5_DATA /database/mrp_5_data01.dbf
0 0 31 0 0
/database/mrp_5_data02.dbf
2 0 10.0 1.0 0 0 0
/database/mrp_5_data03.dbf
0 0 2 0 0
MRP_5_IDX /database/mrp_5_idx01.dbf
64 0 5.6 1.0 90 0 0
MRP_6_DATA /database/mrp_6_data01.dbf
485 0 5.7 3.0 837 0 0
PERFSTAT /database1/perfstat.dbf
0 0 151 0 0
SYSTEM /database/system01.dbf
0 0 29 0 0
File IO Stats for DB: ORA8 Instance: ora8 Snaps: 245 -246
->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)
-------------- ------- ------ ------- ------------ -------- ---------- ------
TEMP /database/temp01.dbf
30 0 172.0 26.9 404 0 0
TOOLS /database/tools01.dbf
17 0 9.4 1.0 121 0 0
UNDOTBS1 /database/undotbs01.dbf
0 0 2,672 1 4 5.0
YHY_PDM /database/YHY_PDM01.dbf
14 0 7.9 1.0 46 0 0
-------------------------------------------------------------
Buffer Pool Statistics for DB: ORA8 Instance: ora8 Snaps: 245 -246
-> Standard block size Pools D: default, K: keep, R: recycle
-> Default[@more@]
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/257699/viewspace-814474/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/257699/viewspace-814474/
101
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
