- 论坛徽章:
- 0
|
oracle登陆特别慢！
下面是我做的statsspack测试报告，谁能帮我看看有什么问题吗？
STATSPACK report for
DB Name DB Id Instance Inst Num Release Cluster Host
------------ ----------- ------------ -------- ----------- ------- ------------
WEBSERV 1505347823 WEBSERV 1 9.0.1.1.1 NO 123-GQQ6J6IC
QTV
Snap Id Snap Time Sessions Curs/Sess Comment
------- ------------------ -------- --------- -------------------
Begin Snap: 13 23-8月 -05 08:00:01 13 9.1
End Snap: 16 23-8月 -05 11:00:03 16 8.8
Elapsed: 180.03 (mins)
Cache Sizes (end)
~~~~~~~~~~~~~~~~~
Buffer Cache: 200M Std Block Size: 4K
Shared Pool Size: 336M Log Buffer: 512K
Load Profile
~~~~~~~~~~~~ Per Second Per Transaction
--------------- ---------------
Redo size: 1,565.94 14,143.24
Logical reads: 69.54 628.04
Block changes: 3.75 33.89
Physical reads: 19.04 171.98
Physical writes: 2.59 23.35
User calls: 0.42 3.81
Parses: 0.15 1.34
Hard parses: 0.02 0.17
Sorts: 0.26 2.32
Logons: 0.00 0.03
Executes: 0.69 6.19
Transactions: 0.11
% Blocks changed per Read: 5.40 Recursive Call %: 85.03
Rollback per transaction %: 0.00 Rows per Sort: 2355.69
Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 100.00 Redo NoWait %: 100.00
Buffer Hit %: 75.55 In-memory Sort %: 99.35
Library Hit %: 96.54 Soft Parse %: 87.59
Execute to Parse %: 78.34 Latch Hit %: 100.00
Parse CPU to Parse Elapsd %: 0.85 % Non-Parse CPU: 100.00
Shared Pool Statistics Begin End
------ ------
Memory Usage %: 10.40 11.05
% SQL with executions>;1: 46.66 47.69
% Memory for SQL w/exec>;1: 45.59 47.94
Top 5 Wait Events
~~~~~~~~~~~~~~~~~ Wait % Total
Event Waits Time (s) Wt Time
-------------------------------------------- ------------ ----------- -------
SQL*Net message from dblink 1,040 1,962 64.74
SQL*Net more data from dblink 35,810 866 28.58
control file parallel write 3,490 75 2.46
db file scattered read 23,395 28 .94
async disk IO 9,594 24 .80
-------------------------------------------------------------
Wait Events for DB: WEBSERV Instance: WEBSERV Snaps: 13 -16
->; 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
---------------------------- ------------ ---------- ---------- ------ --------
SQL*Net message from dblink 1,040 0 1,962 1887 0.9
SQL*Net more data from dblin 35,810 0 866 24 29.9
control file parallel write 3,490 0 75 21 2.9
db file scattered read 23,395 0 28 1 19.6
async disk IO 9,594 0 24 3 8.0
db file parallel write 386 386 20 51 0.3
SQL*Net break/reset to clien 40 0 14 358 0.0
log file parallel write 1,506 1,436 11 7 1.3
SQL*Net more data to client 84 0 10 116 0.1
direct path read 6,414 0 9 1 5.4
log file sync 1,154 0 8 7 1.0
db file sequential read 672 0 1 2 0.6
control file sequential read 1,170 0 1 1 1.0
direct path write 36 0 0 3 0.0
local write wait 5 0 0 21 0.0
SQL*Net break/reset to dblin 20 0 0 0 0.0
SQL*Net message to dblink 1,039 0 0 0 0.9
buffer busy waits 1 0 0 0 0.0
virtual circuit status 5,899 291 14,391 2440 4.9
jobq slave wait 212 206 650 3066 0.2
SQL*Net message from client 4,632 0 197 43 3.9
SQL*Net more data from clien 3,194 0 184 58 2.7
SQL*Net message to client 4,634 0 0 0 3.9
-------------------------------------------------------------
Background Wait Events for DB: WEBSERV Instance: WEBSERV Snaps: 13 -16
->; 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 3,490 0 75 21 2.9
db file parallel write 386 386 20 51 0.3
log file parallel write 1,506 1,436 11 7 1.3
control file sequential read 1,047 0 1 1 0.9
rdbms ipc reply 9 0 0 0 0.0
buffer busy waits 1 0 0 0 0.0
rdbms ipc message 13,435 12,041 51,398 3826 11.2
pmon timer 3,650 3,649 14,457 3961 3.1
smon timer 40 32 13,812 ###### 0.0
-------------------------------------------------------------
SQL ordered by Gets for DB: WEBSERV Instance: WEBSERV Snaps: 13 -16
->; 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
--------------- ------------ -------------- ------ -------- --------- ----------
156,013 1 156,013.0 20.8 0.73 0.74 4111834861
select s.synonym_name object_name, o.object_type from sys.all_sy
nonyms s, sys.all_objects o where s.owner in ('PUBLIC', use
r) and o.owner = s.table_owner and o.object_name = s.table_name
and o.object_type in ('TABLE', 'VIEW', 'PACKAGE','TYPE', 'PROCED
URE', 'FUNCTION', 'SEQUENCE')
115,386 2 57,693.0 15.4 20.92 26.69 2131183518
select count(*),sum(ceil(duration/60)),call_type from hlht_dxjs
where to_char(start_time,'yyyymmdd') between '20050815' and '200
50821' and substr(call_type,1,2) in ('N1','N2','N3','N5','N7','N
H','N4','NG','N8') group by call_type
113,161 2 56,580.5 15.1 2.16 10.30 401261191
select max(start_time) from hlht_dxjs
58,631 1 58,631.0 7.8 7.61 22.70 189471243
select calling_nbr,called_nbr,start_time from hlht_dxjs where to
_char(start_time,'yyyymmdd')>;= '20050801' group by calling_nbr,c
alled_nbr,start_time having count(*)>;1
57,693 1 57,693.0 7.7 3.66 6.91 1254860914
select * from hlht_dxjs where duration>;='86400' and to_char(star
t_time,'yyyymmdd')>;='20050810'
57,693 1 57,693.0 7.7 4.86 8.00 1543108807
select count(*),to_char(start_time,'yyyy-mm-dd') from hlht_dxjs
where to_char(start_time,'yyyymmdd')>;= '20050801' group by to_ch
ar(start_time,'yyyy-mm-dd')
57,693 1 57,693.0 7.7 1.00 5.33 4261770211
select * from hlht_dxjs where duration>;='86400' order by start_t
ime
25,024 1 25,024.0 3.3 0.17 0.17 1426110146
select object_name, owner object_owner, status, object_type, cre
ated, last_ddl_time from sys.all_objects where object_type =  b
ject_type order by decode(owner, user, 0, 1), owner, object_name
23,553 3 7,851.0 3.1 0.00 0.00 2301846564
DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate;
broken BOOLEAN := FALSE; BEGIN statspack.snap; :mydate := next_d
ate; IF broken THEN :b := 1; ELSE :b := 0; END IF; END;
21,431 1 21,431.0 2.9 0.00 0.00 1309091961
begin dq_hlhtjs_fx; end;
20,299 3 6,766.3 2.7 0.00 0.00 1474678425
INSERT into stats$sqltext ( hash_value
SQL ordered by Gets for DB: WEBSERV Instance: WEBSERV Snaps: 13 -16
->; 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
--------------- ------------ -------------- ------ -------- --------- ----------
, text_subset , piece , sql_text
, address , command_type
, last_snap_id ) select st1.hash_value
, ss.text_subset , st1.piece
14,608 978 14.9 1.9 0.00 0.00 3488059105
INSERT INTO HLHT_DXJS (CALLING_NBR,CALLED_NBR,FILE_ID,IC_TRUNK
,OG_TRUNK,START_TIME,DURATION,MINUTE,CHARGE,CALL_TYPE) VALUES (:
CALLING_NBR,:CALLED_NBR,:FILE_ID,:IC_TRUNK,:OG_TRUNK,TO_DATE(:ST
ART_TIME,'yyyy-mm-dd hh24:mi:ss'), URATION,:MINUTE,:CHARGE,:CAL
L_TYPE)
12,442 1 12,442.0 1.7 0.00 0.00 1243562350
DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate;
broken BOOLEAN := FALSE; BEGIN yujin_repare; :mydate := next_dat
e; IF broken THEN :b := 1; ELSE :b := 0; END IF; END;
11,168 1 11,168.0 1.5 0.00 0.03 2857835401
INSERT INTO DIANXIN81 SELECT CALLING_NBR,CALLED_NBR,START_TIME,D
URATION FROM CALL_TICKET@OBS WHERE CALLED_NBR LIKE '81%' AND TO_
CHAR(START_TIME,'YYYY-MM-DD')=:b1
7,420 8 927.5 1.0 2.88 8.94 3327435022
SELECT COUNT(*),NVL(SUM(COUNTS*:b2),0) FROM HLHT_YCHD_TMP WHERE
TICKET_ID=:b1 AND DURATION<=3 AND SUBSTR(CALLING_NBR,4,7) IN (S
ELECT DISTINCT(ACC_NBR) FROM HLHT_NBR_BY)
5,881 8 735.1 0.8 1.88 6.52 3215383989
SELECT COUNT(*),NVL(SUM(COUNTS*:b2),0) FROM HLHT_YCHD_TMP WHERE
TICKET_ID=:b1 AND DURATION>;3 AND SUBSTR(CALLING_NBR,4,7) IN (SE
LECT DISTINCT(ACC_NBR) FROM HLHT_NBR_BY)
4,753 2,097 2.3 0.6 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
2,739 3 913.0 0.4 0.00 0.00 1212505517
select i.obj#, i.flags, u.name, o.name from sys.obj$ o, sys.
user$ u, sys.ind$ i where (bitand(i.flags, 256) = 256 or bit
and(i.flags, 512) = 512) and (not((i.type# = 9) and bi
tand(i.flags, = ) and o.obj#=i.obj# and o.owner# =
u.user#
2,739 3 913.0 0.4 0.00 0.00 3326791696
select u.name, o.name, t.spare1, t.bobj# from sys.obj$ o, sys
.user$ u, sys.tab$ t where (bitand(t.trigflag, 1048576) = 10
48576) and o.obj#=t.obj# and o.owner# = u.user#
SQL ordered by Gets for DB: WEBSERV Instance: WEBSERV Snaps: 13 -16
->; 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
--------------- ------------ -------------- ------ -------- --------- ----------
2,104 2,104 1.0 0.3 0.00 0.00 2964743345
select count(*) from sys.job$ where (next_date >; sysdate) and (n
ext_date < (sysdate+5/86400))
-------------------------------------------------------------
SQL ordered by Reads for DB: WEBSERV Instance: WEBSERV Snaps: 13 -16
->; End Disk Reads Threshold: 1000
CPU Elapsd
Physical Reads Executions Reads per Exec %Total Time (s) Time (s) Hash Value
--------------- ------------ -------------- ------ -------- --------- ----------
46,533 2 23,266.5 22.6 20.92 26.69 2131183518
select count(*),sum(ceil(duration/60)),call_type from hlht_dxjs
where to_char(start_time,'yyyymmdd') between '20050815' and '200
50821' and substr(call_type,1,2) in ('N1','N2','N3','N5','N7','N
H','N4','NG','N8') group by call_type
43,343 2 21,671.5 21.1 2.16 10.30 401261191
select max(start_time) from hlht_dxjs
33,462 1 33,462.0 16.3 7.61 22.70 189471243
select calling_nbr,called_nbr,start_time from hlht_dxjs where to
_char(start_time,'yyyymmdd')>;= '20050801' group by calling_nbr,c
alled_nbr,start_time having count(*)>;1
23,271 1 23,271.0 11.3 4.86 8.00 1543108807
select count(*),to_char(start_time,'yyyy-mm-dd') from hlht_dxjs
where to_char(start_time,'yyyymmdd')>;= '20050801' group by to_ch
ar(start_time,'yyyy-mm-dd')
23,266 1 23,266.0 11.3 3.66 6.91 1254860914
select * from hlht_dxjs where duration>;='86400' and to_char(star
t_time,'yyyymmdd')>;='20050810'
23,266 1 23,266.0 11.3 1.00 5.33 4261770211
select * from hlht_dxjs where duration>;='86400' order by start_t
ime
9,785 1 9,785.0 4.8 0.00 0.00 1309091961
begin dq_hlhtjs_fx; end;
5,896 8 737.0 2.9 2.88 8.94 3327435022
SELECT COUNT(*),NVL(SUM(COUNTS*:b2),0) FROM HLHT_YCHD_TMP WHERE
TICKET_ID=:b1 AND DURATION<=3 AND SUBSTR(CALLING_NBR,4,7) IN (S
ELECT DISTINCT(ACC_NBR) FROM HLHT_NBR_BY)
3,884 8 485.5 1.9 1.88 6.52 3215383989
SELECT COUNT(*),NVL(SUM(COUNTS*:b2),0) FROM HLHT_YCHD_TMP WHERE
TICKET_ID=:b1 AND DURATION>;3 AND SUBSTR(CALLING_NBR,4,7) IN (SE
LECT DISTINCT(ACC_NBR) FROM HLHT_NBR_BY)
1,360 2 680.0 0.7 0.80 2.17 1681636584
select acc_nbr,mark_id,district_name,sum(charge) from hlht_ychd_
fx a ,hlht_nbr_by b where substr(a.calling_nbr,4,7)=b.acc_nbr gr
oup by acc_nbr,mark_id,district_name order by district_name
45 1 45.0 0.0 0.00 0.00 1243562350
DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate;
broken BOOLEAN := FALSE; BEGIN yujin_repare; :mydate := next_dat
e; IF broken THEN :b := 1; ELSE :b := 0; END IF; END;
5 1 5.0 0.0 0.00 0.03 2857835401
INSERT INTO DIANXIN81 SELECT CALLING_NBR,CALLED_NBR,START_TIME,D
URATION FROM CALL_TICKET@OBS WHERE CALLED_NBR LIKE '81%' AND TO_
CHAR(START_TIME,'YYYY-MM-DD')=:b1
SQL ordered by Reads for DB: WEBSERV Instance: WEBSERV Snaps: 13 -16
->; End Disk Reads Threshold: 1000
CPU Elapsd
Physical Reads Executions Reads per Exec %Total Time (s) Time (s) Hash Value
--------------- ------------ -------------- ------ -------- --------- ----------
4 6 0.7 0.0 0.00 0.03 3111103299
select /*+ index(idl_ub1$ i_idl_ub11) +*/ piece#,length,piece fr
om idl_ub1$ where obj#=:1 and part=:2 and version=:3 order by pi
ece#
2 33 0.1 0.0 0.00 0.00 411033441
insert into dependency$(d_obj#,d_timestamp,order#,p_obj#,p_times
tamp,d_owner#, property)values (:1,:2,:3,:4,:5,:6, :7)
1 1 1.0 0.0 0.00 0.00 365454555
select cols,audit$,textlength,intcols,property,flags,rowid from
view$ where obj#=:1
1 6 0.2 0.0 0.00 0.00 1428100621
select /*+ index(idl_ub2$ i_idl_ub21) +*/ piece#,length,piece fr
om idl_ub2$ where obj#=:1 and part=:2 and version=:3 order by pi
ece#
1 5 0.2 0.0 0.00 0.00 1819073277
select owner#,name,namespace,remoteowner,linkname,p_timestamp,p_
obj#, d_owner#, nvl(property,0),subname from dependency$,obj$ wh
ere d_obj#=:1 and p_obj#=obj#(+) order by order#
0 13 0.0 0.0 0.00 0.00 79934617
select ts#,file#,block#,nvl(bobj#,0),nvl(tab#,0),intcols,nvl(clu
cols,0),audit$,flags,pctfree$,pctused$,initrans,maxtrans,rowcnt,
blkcnt,empcnt,avgspc,chncnt,avgrln,analyzetime, samplesize,cols,
property,nvl(degree,1),nvl(instances,1),avgspc_flb,flbcnt,kernel
cols,nvl(trigflag, 0),nvl(spare1,0),nvl(spare2,0),spare4 from ta
0 12 0.0 0.0 0.00 0.00 114078687
select con#,obj#,rcon#,enabled,nvl(defer,0) from cdef$ where rob
j#=:1
0 6 0.0 0.0 0.00 0.00 181436173
select /*+ index(idl_sb4$ i_idl_sb41) +*/ max(version) from id
l_sb4$ where obj#=:1 and version<=:2 and (part=0 or part=2) an
d piece#=0
0 29 0.0 0.0 0.00 0.00 189272129
select o.owner#,o.name,o.namespace,o.remoteowner,o.linkname,o.su
bname,o.dataobj#,o.flags from obj$ o where o.obj#=:1
0 13 0.0 0.0 0.00 0.00 199702406
select i.obj#,i.ts#,i.file#,i.block#,i.intcols,i.type#,i.flags,
i.property,i.pctfree$,i.initrans,i.maxtrans,i.blevel,i.leafcnt,i
.distkey, i.lblkkey,i.dblkkey,i.clufac,i.cols,i.analyzetime,i.sa
mplesize,i.dataobj#, nvl(i.degree,1),nvl(i.instances,1),i.rowcnt
,mod(i.pctthres$,256),i.indmethod#,i.trunccnt,nvl(c.unicols,0),n
0 35 0.0 0.0 0.00 0.00 398896841
select count(*) from sys.job$ where next_date < :1 and (field1 =
:2 or (field1 = 0 and 'Y' = :3))
SQL ordered by Reads for DB: WEBSERV Instance: WEBSERV Snaps: 13 -16
->; End Disk Reads Threshold: 1000
CPU Elapsd
Physical Reads Executions Reads per Exec %Total Time (s) Time (s) Hash Value
--------------- ------------ -------------- ------ -------- --------- ----------
0 38 0.0 0.0 0.00 0.00 528349613
delete from uet$ where ts#=:1 and segfile#=:2 and segblock#=:3 a
-------------------------------------------------------------
SQL ordered by Executions for DB: WEBSERV Instance: WEBSERV Snaps: 13 -16
->; End Executions Threshold: 100
CPU per Elap per
Executions Rows Processed Rows per Exec Exec (s) Exec (s) Hash Value
------------ --------------- ---------------- ----------- ---------- ----------
2,104 2,104 1.0 0.00 0.00 2964743345
select count(*) from sys.job$ where (next_date >; sysdate) and (n
ext_date < (sysdate+5/86400))
2,097 3 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
978 62,320 63.7 0.00 0.00 3488059105
INSERT INTO HLHT_DXJS (CALLING_NBR,CALLED_NBR,FILE_ID,IC_TRUNK
,OG_TRUNK,START_TIME,DURATION,MINUTE,CHARGE,CALL_TYPE) VALUES (:
CALLING_NBR,:CALLED_NBR,:FILE_ID,:IC_TRUNK,:OG_TRUNK,TO_DATE(:ST
ART_TIME,'yyyy-mm-dd hh24:mi:ss'),URATION,:MINUTE,:CHARGE,:CAL
L_TYPE)
136 8,660 63.7 0.00 0.00 2469011443
INSERT INTO HLHT_YCHD_TMP (LATN_ID,TICKET_ID,CALLING_NBR,CALLE
D_NBR,START_TIME,DURATION,COUNTS,IC_TRUNK,OG_TRUNK) VALUES (:LAT
N_ID,:TICKET_ID,:CALLING_NBR,:CALLED_NBR,TO_DATE(:START_TIME,'yy
yy-mm-dd hh24:mi:ss'),URATION,:COUNTS,:IC_TRUNK,:OG_TRUNK)
102 102 1.0 0.00 0.00 4003880563
insert into seg$ (file#,block#,type#,ts#,blocks,extents,minexts,
maxexts,extsize,extpct,user#,iniexts,lists,groups,cachehint,bitm
apranges,scanhint, hwmincr, spare1) values (:1,:2,:3,:4,:5,:6,:7
,:8,:9,:10,:11,:12,:13,:14,:15,0,0,:16,DECODE(:17,0,NULL,:17))
78 0 0.0 0.00 0.00 2913840444
select length from fet$ where file#=:1 and block#=:2 and ts#=:3
64 140 2.2 0.00 0.00 1705880752
select file# from file$ where ts#=:1
60 60 1.0 0.00 0.00 1877781575
delete from fet$ where file#=:1 and block#=:2 and ts#=:3
60 60 1.0 0.00 0.00 3230982141
insert into fet$ (file#,block#,ts#,length) values (:1,:2,:3,:4)
58 20 0.3 0.00 0.00 2591785020
select obj#,type#,ctime,mtime,stime,status,dataobj#,flags,oid$,
spare1, spare2 from obj$ where owner#=:1 and name=:2 and namespa
ce=:3 and(remoteowner=:4 or remoteowner is null and :4 is null)a
nd(linkname=:5 or linkname is null and :5 is null)and(subname=:6
or subname is null and :6 is null)
42 2 0.0 0.00 0.00 1839874543
select file#,block#,length from uet$ where ts#=:1 and segfile#=:
2 and segblock#=:3 and ext#=:4
40 40 1.0 0.00 0.00 3687396716
insert into uet$ (segfile#,segblock#,ext#,ts#,file#,block#,lengt
SQL ordered by Executions for DB: WEBSERV Instance: WEBSERV Snaps: 13 -16
->; End Executions Threshold: 100
CPU per Elap per
Executions Rows Processed Rows per Exec Exec (s) Exec (s) Hash Value
------------ --------------- ---------------- ----------- ---------- ----------
h)values (:1, :2, :3, :4, :5, :6, :7)
39 42 1.1 0.00 0.00 904892542
select file#,block#,length from fet$ where length>;=:1 and
ts#=:2 and file#=:3
38 38 1.0 0.00 0.00 528349613
delete from uet$ where ts#=:1 and segfile#=:2 and segblock#=:3 a
nd ext#=:4
35 35 1.0 0.00 0.00 398896841
select count(*) from sys.job$ where next_date < :1 and (field1 =
:2 or (field1 = 0 and 'Y' = :3))
35 17 0.5 0.00 0.00 4059714361
select type#,blocks,extents,minexts,maxexts,extsize,extpct,user#
,iniexts,NVL(lists,65535),NVL(groups,65535),cachehint,hwmincr, N
VL(spare1,0) from seg$ where ts#=:1 and file#=:2 and block#=:3
34 34 1.0 0.00 0.00 1239889223
SELECT MIN(TIME_MP), COUNT(*) FROM SMON_SCN_TIME WHERE THREAD =
:1
34 34 1.0 0.00 0.00 3634611010
UPDATE SMON_SCN_TIME SET SCN_WRP=:1, SCN_BAS=:2, TIME_MP=:3, TIM
E_DP=:4 WHERE TIME_MP = :5 AND THREAD = :6 AND ROWNUM <= 1
33 33 1.0 0.00 0.00 411033441
insert into dependency$(d_obj#,d_timestamp,order#,p_obj#,p_times
tamp,d_owner#, property)values (:1,:2,:3,:4,:5,:6, :7)
32 928 29.0 0.00 0.00 1714733582
select f.file#, f.block#, f.ts#, f.length from fet$ f, ts$ t whe
re t.ts#=f.ts# and t.dflextpct!=0 and t.bitmapped=0
29 14 0.5 0.00 0.00 189272129
select o.owner#,o.name,o.namespace,o.remoteowner,o.linkname,o.su
bname,o.dataobj#,o.flags from obj$ o where o.obj#=:1
28 28 1.0 0.00 0.00 2249281901
update seg$ set type#=:4,blocks=:5,extents=:6,minexts=:7,maxexts
=:8,extsize=:9,extpct=:10,user#=:11,iniexts=:12,lists=decode(:13
, 65535, NULL, :13),groups=decode(:14, 65535, NULL, :14), cacheh
int=:15, hwmincr=:16, spare1=DECODE(:17,0,NULL,:17) where ts#=:1
and file#=:2 and block#=:3
23 0 0.0 0.00 0.00 787810128
select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt, tim
estamp#, sample_size, minimum, maximum, distcnt, lowval, hival,
density, col#, spare1, spare2, avgcln from hist_head$ where obj#
=:1 and intcol#=:2
17 328 19.3 0.00 0.00 3013728279
SQL ordered by Executions for DB: WEBSERV Instance: WEBSERV Snaps: 13 -16
->; End Executions Threshold: 100
CPU per Elap per
Executions Rows Processed Rows per Exec Exec (s) Exec (s) Hash Value
------------ --------------- ---------------- ----------- ---------- ----------
select privilege#,level from sysauth$ connect by grantee#=prior
privilege# and privilege#>;0 start with (grantee#=:1 or grantee#=
1) and privilege#>;0
-------------------------------------------------------------
SQL ordered by Parse Calls for DB: WEBSERV Instance: WEBSERV Snaps: 13 -16
->; End Parse Calls Threshold: 1000
% Total
Parse Calls Executions Parses Hash Value
------------ ------------ -------- ----------
102 102 0.06 4003880563
insert into seg$ (file#,block#,type#,ts#,blocks,extents,minexts,
maxexts,extsize,extpct,user#,iniexts,lists,groups,cachehint,bitm
apranges,scanhint, hwmincr, spare1) values (:1,:2,:3,:4,:5,:6,:7
,:8,:9,:10,:11,:12,:13,:14,:15,0,0,:16,DECODE(:17,0,NULL,:17))
78 78 0.05 2913840444
select length from fet$ where file#=:1 and block#=:2 and ts#=:3
64 64 0.04 1705880752
select file# from file$ where ts#=:1
60 60 0.04 1877781575
delete from fet$ where file#=:1 and block#=:2 and ts#=:3
60 60 0.04 3230982141
insert into fet$ (file#,block#,ts#,length) values (:1,:2,:3,:4)
42 42 0.03 1839874543
select file#,block#,length from uet$ where ts#=:1 and segfile#=:
2 and segblock#=:3 and ext#=:4
40 40 0.02 3687396716
insert into uet$ (segfile#,segblock#,ext#,ts#,file#,block#,lengt
h)values (:1, :2, :3, :4, :5, :6, :7)
39 39 0.02 904892542
select file#,block#,length from fet$ where length>;=:1 and
ts#=:2 and file#=:3
38 38 0.02 528349613
delete from uet$ where ts#=:1 and segfile#=:2 and segblock#=:3 a
nd ext#=:4
35 35 0.02 4059714361
select type#,blocks,extents,minexts,maxexts,extsize,extpct,user#
,iniexts,NVL(lists,65535),NVL(groups,65535),cachehint,hwmincr, N
VL(spare1,0) from seg$ where ts#=:1 and file#=:2 and block#=:3
34 34 0.02 1239889223
SELECT MIN(TIME_MP), COUNT(*) FROM SMON_SCN_TIME WHERE THREAD =
:1
34 34 0.02 3634611010
UPDATE SMON_SCN_TIME SET SCN_WRP=:1, SCN_BAS=:2, TIME_MP=:3, TIM
E_DP=:4 WHERE TIME_MP = :5 AND THREAD = :6 AND ROWNUM <= 1
32 32 0.02 1714733582
select f.file#, f.block#, f.ts#, f.length from fet$ f, ts$ t whe
re t.ts#=f.ts# and t.dflextpct!=0 and t.bitmapped=0
28 28 0.02 2249281901
update seg$ set type#=:4,blocks=:5,extents=:6,minexts=:7,maxexts
SQL ordered by Parse Calls for DB: WEBSERV Instance: WEBSERV Snaps: 13 -16
->; End Parse Calls Threshold: 1000
% Total
Parse Calls Executions Parses Hash Value
------------ ------------ -------- ----------
=:8,extsize=:9,extpct=:10,user#=:11,iniexts=:12,lists=decode(:13
, 65535, NULL, :13),groups=decode(:14, 65535, NULL, :14), cacheh
int=:15, hwmincr=:16, spare1=DECODE(:17,0,NULL,:17) where ts#=:1
and file#=:2 and block#=:3
20 58 0.01 2591785020
select obj#,type#,ctime,mtime,stime,status,dataobj#,flags,oid$,
spare1, spare2 from obj$ where owner#=:1 and name=:2 and namespa
ce=:3 and(remoteowner=:4 or remoteowner is null and :4 is null)a
nd(linkname=:5 or linkname is null and :5 is null)and(subname=:6
or subname is null and :6 is null)
17 17 0.01 3013728279
select privilege#,level from sysauth$ connect by grantee#=prior
privilege# and privilege#>;0 start with (grantee#=:1 or grantee#=
1) and privilege#>;0
15 15 0.01 1499458452
insert into obj$(owner#,name,namespace,obj#,type#,ctime,mtime,st
ime,status,remoteowner,linkname,subname,dataobj#,flags,oid$,spar
e1,spare2)values(:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,
:15,:16, :17)
13 13 0.01 3395484199
update obj$ set obj#=:6,type#=:7,ctime=:8,mtime=:9,stime=:10,sta
tus=:11,dataobj#=:13,flags=:14,oid$=:15,spare1=:16, spare2=:17 w
here owner#=:1 and name=:2 and namespace=:3 and(remoteowner=:4 o
r remoteowner is null and :4 is null)and(linkname=:5 or linkname
is null and :5 is null)and(subname=:12 or subname is null and :
11 11 0.01 1966425544
select text from view$ where rowid=:1
11 14 0.01 2385919346
select name,intcol#,segcol#,type#,length,nvl(precision#,0),decod
e(type#,2,nvl(scale,-127/*MAXSB1MINAL*/),178,scale,179,scale,180
,scale,181,scale,182,scale,183,scale,231,scale,0),null$,fixedsto
rage,nvl(deflength,0),default$,rowid,col#,property, nvl(charseti
d,0),nvl(charsetform,0),spare1,spare2,nvl(spare3,0) from col$ wh
10 13 0.01 79934617
select ts#,file#,block#,nvl(bobj#,0),nvl(tab#,0),intcols,nvl(clu
cols,0),audit$,flags,pctfree$,pctused$,initrans,maxtrans,rowcnt,
blkcnt,empcnt,avgspc,chncnt,avgrln,analyzetime, samplesize,cols,
property,nvl(degree,1),nvl(instances,1),avgspc_flb,flbcnt,kernel
cols,nvl(trigflag, 0),nvl(spare1,0),nvl(spare2,0),spare4 from ta
10 13 0.01 199702406
select i.obj#,i.ts#,i.file#,i.block#,i.intcols,i.type#,i.flags,
i.property,i.pctfree$,i.initrans,i.maxtrans,i.blevel,i.leafcnt,i
.distkey, i.lblkkey,i.dblkkey,i.clufac,i.cols,i.analyzetime,i.sa
mplesize,i.dataobj#, nvl(i.degree,1),nvl(i.instances,1),i.rowcnt
,mod(i.pctthres$,256),i.indmethod#,i.trunccnt,nvl(c.unicols,0),n
SQL ordered by Parse Calls for DB: WEBSERV Instance: WEBSERV Snaps: 13 -16
->; End Parse Calls Threshold: 1000
% Total
Parse Calls Executions Parses Hash Value
------------ ------------ -------- ----------
-------------------------------------------------------------
Instance Activity Stats for DB: WEBSERV Instance: WEBSERV Snaps: 13 -16
Statistic Total per Second per Trans
--------------------------------- ------------------ -------------- ------------
CPU used by this session 6,666 0.6 5.6
CPU used when call started 6,666 0.6 5.6
CR blocks created 172 0.0 0.1
DBWR checkpoint buffers written 5,361 0.5 4.5
DBWR checkpoints 0 0.0 0.0
DBWR transaction table writes 359 0.0 0.3
DBWR undo block writes 996 0.1 0.8
SQL*Net roundtrips to/from client 6,806 0.6 5.7
SQL*Net roundtrips to/from dblink 1,039 0.1 0.9
background timeouts 12,619 1.2 10.6
buffer is not pinned count 615,486 57.0 514.6
buffer is pinned count 92,071 8.5 77.0
bytes received via SQL*Net from c 7,531,196 697.2 6,297.0
bytes received via SQL*Net from d 72,411,585 6,703.5 60,544.8
bytes sent via SQL*Net to client 880,605 81.5 736.3
bytes sent via SQL*Net to dblink 83,479 7.7 69.8
calls to get snapshot scn: kcmgss 10,509 1.0 8.8
calls to kcmgas 1,780 0.2 1.5
calls to kcmgcs 6,664 0.6 5.6
change write time 22 0.0 0.0
cleanouts and rollbacks - consist 1 0.0 0.0
cleanouts only - consistent read 150 0.0 0.1
cluster key scan block gets 3,525 0.3 3.0
cluster key scans 944 0.1 0.8
commit cleanout failures: buffer 0 0.0 0.0
commit cleanout failures: callbac 1 0.0 0.0
commit cleanouts 5,026 0.5 4.2
commit cleanouts successfully com 5,025 0.5 4.2
consistent changes 209 0.0 0.2
consistent gets 712,867 66.0 596.0
consistent gets - examination 113,241 10.5 94.7
cursor authentications 80 0.0 0.1
data blocks consistent reads - un 173 0.0 0.1
db block changes 40,531 3.8 33.9
db block gets 38,273 3.5 32.0
deferred (CURRENT) block cleanout 1,525 0.1 1.3
dirty buffers inspected 0 0.0 0.0
enqueue conversions 1,053 0.1 0.9
enqueue releases 11,397 1.1 9.5
enqueue requests 11,406 1.1 9.5
enqueue timeouts 10 0.0 0.0
execute count 7,406 0.7 6.2
free buffer inspected 0 0.0 0.0
free buffer requested 187,545 17.4 156.8
immediate (CR) block cleanout app 151 0.0 0.1
immediate (CURRENT) block cleanou 471 0.0 0.4
index fast full scans (full) 0 0.0 0.0
leaf node splits 162 0.0 0.1
logons cumulative 37 0.0 0.0
messages received 1,665 0.2 1.4
messages sent 1,665 0.2 1.4
no buffer to keep pinned count 74,493 6.9 62.3
no work - consistent read gets 566,939 52.5 474.0
opened cursors cumulative 1,336 0.1 1.1
parse count (failures) 42 0.0 0.0
parse count (hard) 199 0.0 0.2
Instance Activity Stats for DB: WEBSERV Instance: WEBSERV Snaps: 13 -16
Statistic Total per Second per Trans
--------------------------------- ------------------ -------------- ------------
parse count (total) 1,604 0.2 1.3
parse time cpu 32 0.0 0.0
parse time elapsed 3,784 |
|
免费注册
发表于 2005-08-23 10:01
发表于 2005-08-23 16:59
