STATSPACK report for
DB Name DB Id Instance Inst Num Release Cluster Host
------------ ----------- ------------ -------- ----------- ------- ------------
IVPS 2688994224 ivps 1 9.2.0.4.0 NO bj169
Snap Id Snap Time Sessions Curs/Sess Comment
------- ------------------ -------- --------- -------------------
Begin Snap: 4 20-Oct-04 09:24:30 33 1.7
End Snap: 5 20-Oct-04 11:27:51 35 2.1
Elapsed: 123.35 (mins)
Cache Sizes (end)
~~~~~~~~~~~~~~~~~
Buffer Cache: 672M Std Block Size:
8K
Shared Pool Size: 208M Log Buffer:
5,120K
Load Profile
~~~~~~~~~~~~ Per Second Per Transaction
--------------- ---------------
Redo size: 4,315.99 1,194.61
Logical reads: 39,232.67 10,859.08
Block changes: 30.55 8.46
Physical reads: 293.05 81.11
Physical writes: 3.59 0.99
User calls: 266.03 73.63
Parses: 37.76 10.45
Hard parses: 0.11 0.03
Sorts: 15.45 4.28
Logons: 0.15 0.04
Executes: 38.89 10.77
Transactions: 3.61
% Blocks changed per Read: 0.08 Recursive Call %: 13.59
Rollback per transaction %: 4.35 Rows per Sort: 486.23
Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 100.00 Redo NoWait %: 100.00
Buffer Hit %: 99.26 In-memory Sort %: 100.00
Library Hit %: 99.85 Soft Parse %: 99.71
Execute to Parse %: 2.90 Latch Hit %: 99.99
Parse CPU to Parse Elapsd %: 63.87 % Non-Parse CPU: 98.51
Shared Pool Statistics Begin End
------ ------
Memory Usage %: 94.87 94.98
% SQL with executions>1: 46.16 46.99
% Memory for SQL w/exec>1: 44.89 46.24
Top 5 Timed Events
~~~~~~~~~~~~~~~~~~ % Total
Event Waits Time (s) Ela Time
-------------------------------------------- ------------ ----------- --------
CPU time 2,564 96.55
latch free 3,436 46 1.73
db file scattered read 463,483 17 .65
log file sync 25,610 16 .61
db file sequential read 588,136 9 .34
-------------------------------------------------------------
Wait Events for DB: IVPS Instance: ivps Snaps: 4 -5
-> 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
---------------------------- ------------ ---------- ---------- ------ --------
latch free 3,436 914 46 13 0.1
db file scattered read 463,483 0 17 0 17.3
log file sync 25,610 0 16 1 1.0
db file sequential read 588,136 0 9 0 22.0
control file sequential read 1,877 0 1 0 0.1
SQL*Net more data to client 39,000 0 1 0 1.5
control file parallel write 2,442 0 1 0 0.1
log file sequential read 52 0 0 9 0.0
SQL*Net break/reset to clien 16 0 0 11 0.0
log file switch completion 3 0 0 26 0.0
log file parallel write 25,815 25,813 0 0 1.0
db file parallel write 12,448 0 0 0 0.5
direct path read (lob) 12,092 0 0 0 0.5
buffer busy waits 763 0 0 0 0.0
enqueue 2 0 0 11 0.0
log file single write 8 0 0 0 0.0
LGWR wait for redo copy 17 0 0 0 0.0
async disk IO 66 0 0 0 0.0
direct path write (lob) 8 0 0 0 0.0
direct path read 20 0 0 0 0.0
direct path write 20 0 0 0 0.0
SQL*Net message from client 1,969,940 0 74,301 38 73.7
SQL*Net message to client 1,969,943 0 3 0 73.7
SQL*Net more data from clien 66 0 0 0 0.0
-------------------------------------------------------------
Background Wait Events for DB: IVPS Instance: ivps Snaps: 4 -5
-> ordered by wait time desc, waits desc (idle events last)
Avg
Total Wait wait Waits
Event Waits Timeouts Time (s) (ms) /txn
---------------------------- ------------ ---------- ---------- ------ --------
control file sequential read 1,408 0 1 1 0.1
control file parallel write 2,441 0 1 0 0.1
log file sequential read 52 0 0 9 0.0
log file parallel write 25,814 25,812 0 0 1.0
db file parallel write 12,447 0 0 0 0.5
latch free 1 0 0 5 0.0
log file single write 8 0 0 0 0.0
rdbms ipc reply 12 0 0 0 0.0
LGWR wait for redo copy 17 0 0 0 0.0
async disk IO 66 0 0 0 0.0
buffer busy waits 1 0 0 0 0.0
direct path read 20 0 0 0 0.0
direct path write 20 0 0 0 0.0
rdbms ipc message 102,495 73,130 91,429 892 3.8
pmon timer 2,457 2,457 7,219 2938 0.1
smon timer 24 23 6,880 ###### 0.0
-------------------------------------------------------------
SQL ordered by Gets for DB: IVPS Instance: ivps Snaps: 4 -5
-> 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
--------------- ------------ -------------- ------ -------- --------- ----------
92,364,253 2,199 42,002.8 31.8 406.22 398.65 1675138424
Module: JDBC Thin Client
select count(b.pk) from tb_forum_thread a,tb_forum_reply b where
a.pk=b.thread_pk and a.status=:"SYS_B_0" and b.status=:"SYS_B_1
" and a.board_pk=:"SYS_B_2"
22,169,645 64 346,400.7 7.6 56.35 55.32 1269097881
Module: JDBC Thin Client
select user_name from tb_user where pk in ( select user_pk
from tb_user_profile where regist_time=( select max(regist_tim
e) from tb_user_profile ) )
14,131,015 588 24,032.3 4.9 123.00 122.31 1989700802
Module: JDBC Thin Client
select pk,board_pk,thread_type,thread_title,thread_sendscore, pi
c_url,is_top,is_soul,create_time,create_user,click_num,is_lock,
status,memo,thread_class,board_name,brief_name,lastreply_time fr
om (select pk,board_pk,thread_type,thread_title,thread_sendscore
, pic_url,is_top,is_soul,create_time,create_user,click_num,is_lo
11,839,350 5 2,367,870.0 4.1 49.40 48.68 1374273252
Module: JDBC Thin Client
select count(distinct user_name) from tb_user_mark where user
_name not in( select user_name from editorID ) and user_n
ame in ( select user_name from tb_user where pk in ( select u
ser_pk from tb_user_profile where user_career=:"SYS_B_0" ) )
10,169,687 1,198 8,488.9 3.5 24.16 23.86 3401611908
Module: JDBC Thin Client
select * from(select drawing.*, rownum as my_rownum from(select
distinct pk,drawing_title,drawing_author,commend_username,provin
ce,drawing_intro,rank,drawing_size,public_time,download_count,fi
le_name,key_word,drawing_price,memo,week_download_count from dra
wing_view1 where drawing_state=2 and way=:1 order by public_time
10,161,215 1,197 8,488.9 3.5 24.32 23.81 3933472146
Module: JDBC Thin Client
select count(pk) from(select drawing.*, rownum as my_rownum from
(select distinct pk,drawing_title,drawing_author,commend_usernam
e,province,drawing_intro,rank,drawing_size,public_time,download_
count,file_name,key_word,drawing_price,memo,week_download_count
from drawing_view1 where drawing_state=2 and way=:1 ) drawing)
7,255,010 797 9,102.9 2.5 17.67 17.22 3295982819
Module: JDBC Thin Client
select count(pk) from(select paper.*, rownum as my_rownum from(s
elect distinct pk,paper_title,paper_intro,public_time,click_coun
t,paper_author,copyright,commend_username from paper_view1 where
paper_state=2 and way=:1 ) paper)
SQL ordered by Gets for DB: IVPS Instance: ivps Snaps: 4 -5
-> 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
--------------- ------------ -------------- ------ -------- --------- ----------
7,100,185 780 9,102.8 2.4 17.95 17.66 837701357
Module: JDBC Thin Client
select * from(select paper.*, rownum as my_rownum from(select di
stinct pk,paper_title,paper_intro,public_time,click_count,paper_
author,copyright,commend_username from paper_view1 where paper_s
tate=2 and way=:1 order by public_time desc) paper where rownum<
=:2) where my_rownum>=:3
6,926,770 936 7,400.4 2.4 16.00 15.76 1550292743
Module: JDBC Thin Client
select * from(select standard.*, rownum as my_rownum from(select
distinct pk,standard_title,standard_intro,public_time,download_
count,week_download_count,file_name,standard_size,standard_price
from standard_view1 where standard_state=2 and way=:1 order by
public_time desc) standard where rownum<=:2) where my_rownum>=:3
6,911,960 934 7,400.4 2.4 15.35 15.32 3775561653
Module: JDBC Thin Client
select count(pk) from(select standard.*, rownum as my_rownum fro
m(select distinct pk,standard_title,standard_intro,public_time,d
ownload_count,week_download_count,file_name,standard_size,standa
rd_price from standard_view1 where standard_state=2 and way=:1 )
standard)
6,111,223 18 339,512.4 2.1 28.32 28.22 3837137728
Module: JDBC Thin Client
select * from ( select a.*, rownum as rn from ( select pk,BOAR
D_PK,thread_type,thread_title,thread_sendscore, pic_url,is_top,i
s_soul,create_time,create_user,click_num,is_lock, status,memo,th
read_class,lastreply_time,lastreply_person,reply_num,forum_pk f
rom forum_board_thread_view2 where pk in ( select distinct(thr
5,054,148 3,750 1,347.8 1.7 35.69 35.37 3682418978
Module: JDBC Thin Client
select a.num+b.re_num from (select count(*) as num from tb_fo
rum_thread where board_pk =:"SYS_B_0" and status=:"SYS_B_1" ) a
,(select sum(reply_num) as re_num from tb_forum_thread where
board_pk =:"SYS_B_2" and status=:"SYS_B_3" ) b
5,046,638 697 7,240.5 1.7 11.39 11.16 803871542
Module: JDBC Thin Client
select * from(select software.*, rownum as my_rownum from(select
distinct pk,software_title,software_intro,public_time,download_
count,week_download_count,file_name from software_view1 where so
ftware_state=2 and way=:1 order by public_time desc) software wh
ere rownum<=:2) where my_rownum>=:3
5,010,437 692 7,240.5 1.7 11.34 10.91 85528283
Module: JDBC Thin Client
SQL ordered by Gets for DB: IVPS Instance: ivps Snaps: 4 -5
-> 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
--------------- ------------ -------------- ------ -------- --------- ----------
select count(pk) from(select software.*, rownum as my_rownum fro
m(select distinct pk,software_title,software_intro,public_time,d
ownload_count,week_download_count,file_name from software_view1
where software_state=2 and way=:1 ) software)
4,670,772 404 11,561.3 1.6 34.09 33.44 1963425936
Module: JDBC Thin Client
select BOARD_DESC,BOARD_NAME,FORUM_PK,BOARD_PK,BOARD_STATUS,last
-------------------------------------------------------------
SQL ordered by Reads for DB: IVPS Instance: ivps Snaps: 4 -5
-> End Disk Reads Threshold: 1000
CPU Elapsd
Physical Reads Executions Reads per Exec %Total Time (s) Time (s) Hash Value
--------------- ------------ -------------- ------ -------- --------- ----------
337,513 1,188 284.1 15.6 324.30 319.18 760269237
Module: OraEdit.exe
select head_img_url, signature, user_sex, regist_time, user_nam
e from tb_user_profile where user_name in (:"SYS_B_00",:"SYS_B
_01",:"SYS_B_02",:"SYS_B_03",:"SYS_B_04",:"SYS_B_05",:"SYS_B_06"
,:"SYS_B_07",:"SYS_B_08",:"SYS_B_09",:"SYS_B_10",:"SYS_B_11",:"S
YS_B_12",:"SYS_B_13",:"SYS_B_14",:"SYS_B_15",:"SYS_B_16",:"SYS_B
260,779 18 14,487.7 12.0 19.49 19.21 1909470160
Module: JDBC Thin Client
select id,full_name from enterp_info_db where full_name like :
1 and rownum<2
122,841 426 288.4 5.7 38.84 38.72 4045833718
Module: JDBC Thin Client
select head_img_url, signature, user_sex, regist_time, user_nam
e from tb_user_profile where user_name in (:"SYS_B_0" ) order
by user_name
95,929 353 271.8 4.4 35.55 35.22 3728079592
Module: JDBC Thin Client
select head_img_url, signature, user_sex, regist_time, user_nam
e from tb_user_profile where user_name in (:"SYS_B_0",:"SYS_B_
1" ) order by user_name
83,840 305 274.9 3.9 34.18 33.79 3503934362
Module: JDBC Thin Client
select head_img_url, signature, user_sex, regist_time, user_nam
e from tb_user_profile where user_name in (:"SYS_B_0",:"SYS_B_
1",:"SYS_B_2" ) order by user_name
70,649 253 279.2 3.3 29.47 29.18 2710994462
Module: JDBC Thin Client
select head_img_url, signature, user_sex, regist_time, user_nam
e from tb_user_profile where user_name in (:"SYS_B_0",:"SYS_B_
1",:"SYS_B_2",:"SYS_B_3" ) order by user_name
58,469 210 278.4 2.7 28.75 28.23 314705737
Module: JDBC Thin Client
select head_img_url, signature, user_sex, regist_time, user_nam
e from tb_user_profile where user_name in (:"SYS_B_0",:"SYS_B_
1",:"SYS_B_2",:"SYS_B_3",:"SYS_B_4",:"SYS_B_5" ) order by user_n
ame
48,563 176 275.9 2.2 21.84 21.44 4042305192
Module: JDBC Thin Client
select head_img_url, signature, user_sex, regist_time, user_nam
e from tb_user_profile where user_name in (:"SYS_B_0",:"SYS_B_
1",:"SYS_B_2",:"SYS_B_3",:"SYS_B_4" ) order by user_name
42,723 154 277.4 2.0 23.14 22.83 3958573495
Module: JDBC Thin Client
select head_img_url, signature, user_sex, regist_time, user_nam
e from tb_user_profile where user_name in (:"SYS_B_0",:"SYS_B_
SQL ordered by Reads for DB: IVPS Instance: ivps Snaps: 4 -5
-> End Disk Reads Threshold: 1000
CPU Elapsd
Physical Reads Executions Reads per Exec %Total Time (s) Time (s) Hash Value
--------------- ------------ -------------- ------ -------- --------- ----------
1",:"SYS_B_2",:"SYS_B_3",:"SYS_B_4",:"SYS_B_5",:"SYS_B_6",:"SYS_
B_7" ) order by user_name
38,694 131 295.4 1.8 22.91 22.62 2373701249
Module: JDBC Thin Client
select head_img_url, signature, user_sex, regist_time, user_nam
e from tb_user_profile where user_name in (:"SYS_B_0",:"SYS_B_
1",:"SYS_B_2",:"SYS_B_3",:"SYS_B_4",:"SYS_B_5",:"SYS_B_6",:"SYS_
B_7",:"SYS_B_8",:"SYS_B_9" ) order by user_name
37,955 2 18,977.5 1.7 1.03 1.02 1306561092
Module: JDBC Thin Client
delete from enterp_info_db where id=:1
37,783 2 18,891.5 1.7 1.58 1.53 2587249602
Module: JDBC Thin Client
insert into enterp_info(id, FULL_NAME, COMP_LOCATION,COMP_POST,C
OMP_URL,way,relate_name,title,mobile,sex,password,question,answe
r, NAME,COMP_MAIL,COMP_MAIL2,COMP_PHONE,COMP_LOCATE1,COMP_LOCAT
E2,COMP_LOCATE3,COMP_LAW,COMP_FAX,REGDATE,CLICKTIMES,NEWENTERP,a
uth) select :1,:2,:3,:4,:5,:6,relate_name,title,mobile,sex,passw
37,736 2 18,868.0 1.7 1.45 1.47 987341707
Module: JDBC Thin Client
select a.full_name,a.way,a.comp_url,a.comp_post,a.comp_location,
b.product,b.comp_infor from enterp_info_db a,enterp_attribute_i
nfo_db b where a.id=b.id and a.id=:1
34,454 122 282.4 1.6 21.32 21.07 1955826787
Module: JDBC Thin Client
select head_img_url, signature, user_sex, regist_time, user_nam
e from tb_user_profile where user_name in (:"SYS_B_00",:"SYS_B
_01",:"SYS_B_02",:"SYS_B_03",:"SYS_B_04",:"SYS_B_05",:"SYS_B_06"
,:"SYS_B_07",:"SYS_B_08",:"SYS_B_09",:"SYS_B_10" ) order by user
_name
32,229 107 301.2 1.5 15.04 14.89 3230219899
Module: JDBC Thin Client
select head_img_url, signature, user_sex, regist_time, user_nam
e from tb_user_profile where user_name in (:"SYS_B_0",:"SYS_B_
1",:"SYS_B_2",:"SYS_B_3",:"SYS_B_4",:"SYS_B_5",:"SYS_B_6" ) orde
r by user_name
29,535 101 292.4 1.4 15.98 15.69 2871452022
Module: JDBC Thin Client
select head_img_url, signature, user_sex, regist_time, user_nam
e from tb_user_profile where user_name in (:"SYS_B_0",:"SYS_B_
1",:"SYS_B_2",:"SYS_B_3",:"SYS_B_4",:"SYS_B_5",:"SYS_B_6",:"SYS_
B_7",:"SYS_B_8" ) order by user_name
28,958 96 301.6 1.3 11.36 11.18 1916155889
Module: JDBC Thin Client
select count(*) from tb_user_profile where to_char(regist_time,'
yyyy-mm-dd hh-mm-ss') like '2004-10-04%' and user_career=:1
SQL ordered by Reads for DB: IVPS Instance: ivps Snaps: 4 -5
-> End Disk Reads Threshold: 1000
CPU Elapsd
Physical Reads Executions Reads per Exec %Total Time (s) Time (s) Hash Value
--------------- ------------ -------------- ------ -------- --------- ----------
28,946 96 301.5 1.3 12.13 11.88 3758460219
Module: JDBC Thin Client
select count(*) from tb_user_profile where to_char(regist_time,'
yyyy-mm-dd hh-mm-ss') like '2004-10-02%' and user_career=:1
28,939 96 301.4 1.3 11.63 11.40 1121428772
Module: JDBC Thin Client
select count(*) from tb_user_profile where to_char(regist_time,'
yyyy-mm-dd hh-mm-ss') like '2004-10-03%' and user_career=:1
28,910 96 301.1 1.3 10.67 10.65 376695217
Module: JDBC Thin Client
select count(*) from tb_user_profile where to_char(regist_time,'
yyyy-mm-dd hh-mm-ss') like '2004-10-06%' and user_career=:1
-------------------------------------------------------------
SQL ordered by Executions for DB: IVPS Instance: ivps Snaps: 4 -5
-> End Executions Threshold: 100
CPU per Elap per
Executions Rows Processed Rows per Exec Exec (s) Exec (s) Hash Value
------------ --------------- ---------------- ----------- ---------- ----------
15,266 45,734 3.0 0.00 0.00 883252759
Module: JDBC Thin Client
select user_mark from tb_user_mark where user_name=:"SYS_B_0" o
rder by mark_type
15,079 12,768 0.8 0.00 0.00 723194081
Module: JDBC Thin Client
select reply_num+thread_num from tb_forum_user_thread_num where
user_name=:"SYS_B_0"
15,079 15,083 1.0 0.00 0.00 2150269977
Module: JDBC Thin Client
select head_img_url, signature, user_sex, regist_time from tb_
user_profile where user_pk in ( select pk from tb_user where
user_name=:"SYS_B_0")
14,788 9,032 0.6 0.00 0.00 1885449141
Module: JDBC Thin Client
select pk,OPTION_PK,TYPE_CODE,THREAD_URL,AD_TITILE from tb_forum
_ad where TYPE_CODE=:"SYS_B_0" and OPTION_PK=:"SYS_B_1"
13,622 32,443 2.4 0.00 0.00 2689867434
Module: JDBC Thin Client
select PK,BOARD_PK,MASTER_NAME,OPTION_TIME,OPTION_USER,STATUS f
rom tb_forum_master where BOARD_PK=:"SYS_B_0" and status=:"SYS_
B_1"
7,759 22,856 2.9 0.00 0.00 3222431807
Module: JDBC Thin Client
select remark_id,topic_pk,remark_username,rank,remark,TO_CHAR(re
mark_time, 'YYYY-MM-DD HH:MI:SS') as remark_time,memo from tb_r
emark where topic_pk=:1 and memo like :2 order by remark_time de
sc
7,082 83,194 11.7 0.00 0.00 1968834830
Module: JDBC Thin Client
select pk ,forum_pk ,board_name, board_desc, create_time, creat
e_user,status, memo from tb_forum_board where forum_pk=:"SYS_B
_0" and status=:"SYS_B_1" order by create_time
5,178 17,771 3.4 0.00 0.00 3207535377
Module: JDBC Thin Client
SELECT distinct pk,paper_title,paper_author,rank,upload_time,cli
ck_count FROM tb_paper WHERE key_word like :"SYS_B_0" and paper
_state=:"SYS_B_1" and way=:"SYS_B_2"
5,140 32,137 6.3 0.00 0.00 2837400515
Module: JDBC Thin Client
select * from sub_type where doc_type=:1 and profession_type=:2
order by sub_type
4,828 4,287 0.9 0.00 0.00 1249154402
Module: JDBC Thin Client
select reply_number from forum_thread_reply_view2 where thread_p
SQL ordered by Executions for DB: IVPS Instance: ivps Snaps: 4 -5
-> End Executions Threshold: 100
CPU per Elap per
Executions Rows Processed Rows per Exec Exec (s) Exec (s) Hash Value
------------ --------------- ---------------- ----------- ---------- ----------
k=:"SYS_B_0"
4,585 4,585 1.0 0.00 0.00 2486814933
Module: JDBC Thin Client
update tb_forum_thread set click_num=:"SYS_B_0" where pk=:"SYS_B
_1"
4,580 4,580 1.0 0.00 0.00 4275259987
Module: JDBC Thin Client
select pk,thread_title,thread_content,create_user,create_time,th
read_sendscore,click_num,is_soul,is_lock,is_top,pic_url,is_encou
rage,last_editor,last_edit_time,function_type,download_times,rep
ly_num from tb_forum_thread where pk=:"SYS_B_0"
4,578 42,157 9.2 0.00 0.00 3791129184
Module: OraEdit.exe
select PK,thread_getscore,pic_url,reply_content,create_time, cre
ate_user,status,memo,is_encourage,last_editor,last_edit_time,dow
nload_times from ( select rownum as num,PK,thread_getscore,pic
_url,reply_content,create_time, create_user,status,memo,is_encou
rage,last_editor,last_edit_time,download_times from ( select
3,883 3,883 1.0 0.00 0.00 510864396
Module: JDBC Thin Client
select to_char(max(down_date),'yyyy-MM-dd HH24:mm:ss') from tb_d
ownload_log where pk=:1 and username=:2
3,883 152,718 39.3 0.00 0.00 2003553047
Module: JDBC Thin Client
select * from sub_type where doc_type=:"SYS_B_0"
3,750 3,750 1.0 0.01 0.01 3682418978
Module: JDBC Thin Client
select a.num+b.re_num from (select count(*) as num from tb_fo
rum_thread where board_pk =:"SYS_B_0" and status=:"SYS_B_1" ) a
,(select sum(reply_num) as re_num from tb_forum_thread where
board_pk =:"SYS_B_2" and status=:"SYS_B_3" ) b
3,735 4,295 1.1 0.00 0.00 218636415
Module: JDBC Thin Client
select user_mark from tb_user_mark where user_name=:"SYS_B_0" an
d mark_type=:"SYS_B_1"
3,515 4,275 1.2 0.00 0.00 481945932
Module: JDBC Thin Client
SELECT id,sub_type,sub_type_name,profession_type,doc_type_name,c
areername FROM drawing WHERE pk=:1 and drawing_state=2
3,515 3,515 1.0 0.00 0.00 959971486
Module: JDBC Thin Client
UPDATE tb_drawing SET click_count=:"SYS_B_0" WHERE pk=:"SYS_B_1"
3,515 3,515 1.0 0.00 0.00 1430089776
SQL ordered by Executions for DB: IVPS Instance: ivps Snaps: 4 -5
-> End Executions Threshold: 100
CPU per Elap per
Executions Rows Processed Rows per Exec Exec (s) Exec (s) Hash Value
------------ --------------- ---------------- ----------- ---------- ----------
Module: JDBC Thin Client
select click_count from tb_drawing where pk=:"SYS_B_0"
3,515 3,515 1.0 0.00 0.00 1639465010
Module: JDBC Thin Client
SELECT pk,drawing_title,drawing_author,commend_username,province
,drawing_intro,rank,drawing_size,upload_time,download_count,file
_name,key_word,drawing_price,memo,public_time,week_download_coun
t FROM tb_drawing WHERE pk=:1 and drawing_state=2
3,441 3,441 1.0 0.00 0.00 3833490558
Module: JDBC Thin Client
insert into tb_download_log (id,pk,username,score,down_date) VAL
UES(SEQ_DOWNLOAD.nextval,:1,:2,:3,sysdate)
2,553 2,553 1.0 0.00 0.00 3258989780
Module: JDBC Thin Client
SELECT drawing_title,drawing_author,commend_username,province,dr
awing_intro,drawing_size,upload_time,file_name,key_word,drawing_
price,drawing_state,download_count,week_download_count,click_cou
nt,memo FROM tb_drawing WHERE pk=:1
2,306 2,306 1.0 0.00 0.00 2069724299
Module: JDBC Thin Client
select is_charge,expiretime from charge_info where pk=(select m
ax(pk) from charge_info where id=:1)
2,266 2,266 1.0 0.00 0.00 980868662
Module: JDBC Thin Client
update tb_drawing t set t.download_count=t.download_count+1,t.we
-------------------------------------------------------------
SQL ordered by Parse Calls for DB: IVPS Instance: ivps Snaps: 4 -5
-> End Parse Calls Threshold: 1000
% Total
Parse Calls Executions Parses Hash Value
------------ ------------ -------- ----------
15,264 15,266 5.46 883252759
Module: JDBC Thin Client
select user_mark from tb_user_mark where user_name=:"SYS_B_0" o
rder by mark_type
15,078 15,079 5.39 2150269977
Module: JDBC Thin Client
select head_img_url, signature, user_sex, regist_time from tb_
user_profile where user_pk in ( select pk from tb_user where
user_name=:"SYS_B_0")
15,077 15,079 5.39 723194081
Module: JDBC Thin Client
select reply_num+thread_num from tb_forum_user_thread_num where
user_name=:"SYS_B_0"
14,788 14,788 5.29 1885449141
Module: JDBC Thin Client
select pk,OPTION_PK,TYPE_CODE,THREAD_URL,AD_TITILE from tb_forum
_ad where TYPE_CODE=:"SYS_B_0" and OPTION_PK=:"SYS_B_1"
13,622 13,622 4.87 2689867434
Module: JDBC Thin Client
select PK,BOARD_PK,MASTER_NAME,OPTION_TIME,OPTION_USER,STATUS f
rom tb_forum_master where BOARD_PK=:"SYS_B_0" and status=:"SYS_
B_1"
7,759 7,759 2.78 3222431807
Module: JDBC Thin Client
select remark_id,topic_pk,remark_username,rank,remark,TO_CHAR(re
mark_time, 'YYYY-MM-DD HH:MI:SS') as remark_time,memo from tb_r
emark where topic_pk=:1 and memo like :2 order by remark_time de
sc
7,082 7,082 2.53 1968834830
Module: JDBC Thin Client
select pk ,forum_pk ,board_name, board_desc, create_time, creat
e_user,status, memo from tb_forum_board where forum_pk=:"SYS_B
_0" and status=:"SYS_B_1" order by create_time
5,178 5,178 1.85 3207535377
Module: JDBC Thin Client
SELECT distinct pk,paper_title,paper_author,rank,upload_time,cli
ck_count FROM tb_paper WHERE key_word like :"SYS_B_0" and paper
_state=:"SYS_B_1" and way=:"SYS_B_2"
5,141 5,140 1.84 2837400515
Module: JDBC Thin Client
select * from sub_type where doc_type=:1 and profession_type=:2
order by sub_type
4,828 4,828 1.73 1249154402
Module: JDBC Thin Client
select reply_number from forum_thread_reply_view2 where thread_p
SQL ordered by Parse Calls for DB: IVPS Instance: ivps Snaps: 4 -5
-> End Parse Calls Threshold: 1000
% Total
Parse Calls Executions Parses Hash Value
------------ ------------ -------- ----------
k=:"SYS_B_0"
4,585 4,585 1.64 2486814933
Module: JDBC Thin Client
update tb_forum_thread set click_num=:"SYS_B_0" where pk=:"SYS_B
_1"
4,580 4,580 1.64 4275259987
Module: JDBC Thin Client
select pk,thread_title,thread_content,create_user,create_time,th
read_sendscore,click_num,is_soul,is_lock,is_top,pic_url,is_encou
rage,last_editor,last_edit_time,function_type,download_times,rep
ly_num from tb_forum_thread where pk=:"SYS_B_0"
4,578 4,578 1.64 3791129184
Module: OraEdit.exe
select PK,thread_getscore,pic_url,reply_content,create_time, cre
ate_user,status,memo,is_encourage,last_editor,last_edit_time,dow
nload_times from ( select rownum as num,PK,thread_getscore,pic
_url,reply_content,create_time, create_user,status,memo,is_encou
rage,last_editor,last_edit_time,download_times from ( select
3,883 3,883 1.39 510864396
Module: JDBC Thin Client
select to_char(max(down_date),'yyyy-MM-dd HH24:mm:ss') from tb_d
ownload_log where pk=:1 and username=:2
3,883 3,883 1.39 2003553047
Module: JDBC Thin Client
select * from sub_type where doc_type=:"SYS_B_0"
3,750 3,750 1.34 3682418978
Module: JDBC Thin Client
select a.num+b.re_num from (select count(*) as num from tb_fo
rum_thread where board_pk =:"SYS_B_0" and status=:"SYS_B_1" ) a
,(select sum(reply_num) as re_num from tb_forum_thread where
board_pk =:"SYS_B_2" and status=:"SYS_B_3" ) b
3,735 3,735 1.34 218636415
Module: JDBC Thin Client
select user_mark from tb_user_mark where user_name=:"SYS_B_0" an
d mark_type=:"SYS_B_1"
3,515 3,515 1.26 481945932
Module: JDBC Thin Client
SELECT id,sub_type,sub_type_name,profession_type,doc_type_name,c
areername FROM drawing WHERE pk=:1 and drawing_state=2
3,515 3,515 1.26 959971486
Module: JDBC Thin Client
UPDATE tb_drawing SET click_count=:"SYS_B_0" WHERE pk=:"SYS_B_1"
3,515 3,515 1.26 1430089776
SQL ordered by Parse Calls for DB: IVPS Instance: ivps Snaps: 4 -5
-> End Parse Calls Threshold: 1000
% Total
Parse Calls Executions Parses Hash Value
------------ ------------ -------- ----------
Module: JDBC Thin Client
select click_count from tb_drawing where pk=:"SYS_B_0"
3,515 3,515 1.26 1639465010
Module: JDBC Thin Client
SELECT pk,drawing_title,drawing_author,commend_username,province
,drawing_intro,rank,drawing_size,upload_time,download_count,file
_name,key_word,drawing_price,memo,public_time,week_download_coun
t FROM tb_drawing WHERE pk=:1 and drawing_state=2
3,441 3,441 1.23 3833490558
Module: JDBC Thin Client
insert into tb_download_log (id,pk,username,score,down_date) VAL
UES(SEQ_DOWNLOAD.nextval,:1,:2,:3,sysdate)
2,553 2,553 0.91 3258989780
Module: JDBC Thin Client
SELECT drawing_title,drawing_author,commend_username,province,dr
awing_intro,drawing_size,upload_time,file_name,key_word,drawing_
price,drawing_state,download_count,week_download_count,click_cou
nt,memo FROM tb_drawing WHERE pk=:1
2,306 2,306 0.83 2069724299
Module: JDBC Thin Client
select is_charge,expiretime from charge_info where pk=(select m
ax(pk) from charge_info where id=:1)
2,266 2,266 0.81 980868662
Module: JDBC Thin Client
update tb_drawing t set t.download_count=t.download_count+1,t.we
-------------------------------------------------------------
Instance Activity Stats for DB: IVPS Instance: ivps Snaps: 4 -5
Statistic Total per Second per Trans
--------------------------------- ------------------ -------------- ------------
CPU used by this session 256,415 34.7 9.6
CPU used when call started 256,414 34.7 9.6
CR blocks created 587 0.1 0.0
DBWR buffers scanned 0 0.0 0.0
DBWR checkpoint buffers written 26,505 3.6 1.0
DBWR checkpoints 2 0.0 0.0
DBWR free buffers found 0 0.0 0.0
DBWR lru scans 0 0.0 0.0
DBWR make free requests 0 0.0 0.0
DBWR summed scan depth 0 0.0 0.0
DBWR transaction table writes 418 0.1 0.0
DBWR undo block writes 1,476 0.2 0.1
SQL*Net roundtrips to/from client 1,966,506 265.7 73.5
active txn count during cleanout 369 0.1 0.0
background checkpoints completed 2 0.0 0.0
background checkpoints started 2 0.0 0.0
background timeouts 25,738 3.5 1.0
branch node splits 0 0.0 0.0
buffer is not pinned count 146,947,422 19,855.1 5,495.6
buffer is pinned count 325,453,422 43,974.3 12,171.5
bytes received via SQL*Net from c 113,185,543 15,293.3 4,233.0
bytes sent via SQL*Net to client 267,353,677 36,124.0 9,998.6
calls to get snapshot scn: kcmgss 1,699,625 229.7 63.6
calls to kcmgas 27,295 3.7 1.0
calls to kcmgcs 124 0.0 0.0
change write time 286 0.0 0.0
cleanout - number of ktugct calls 537 0.1 0.0
cleanouts and rollbacks - consist 69 0.0 0.0
cleanouts only - consistent read 148 0.0 0.0
cluster key scan block gets 6,116 0.8 0.2
cluster key scans 4,418 0.6 0.2
commit cleanout failures: buffer 20 0.0 0.0
commit cleanout failures: callbac 1 0.0 0.0
commit cleanout failures: cannot 5 0.0 0.0
commit cleanouts 70,722 9.6 2.6
commit cleanouts successfully com 70,696 9.6 2.6
commit txn count during cleanout 4,143 0.6 0.2
consistent changes 1,356 0.2 0.1
consistent gets 290,139,061 39,202.7 10,850.8
consistent gets - examination 143,707,541 19,417.3 5,374.5
cursor authentications 302 0.0 0.0
data blocks consistent reads - un 625 0.1 0.0
db block changes 226,130 30.6 8.5
db block gets 234,140 31.6 8.8
deferred (CURRENT) block cleanout 29,300 4.0 1.1
dirty buffers inspected 0 0.0 0.0
enqueue conversions 28 0.0 0.0
enqueue releases 64,913 8.8 2.4
enqueue requests 64,913 8.8 2.4
enqueue timeouts 0 0.0 0.0
enqueue waits 2 0.0 0.0
exchange deadlocks 0 0.0 0.0
execute count 287,853 38.9 10.8
free buffer inspected 96 0.0 0.0
free buffer requested 2,158,823 291.7 80.7
hot buffers moved to head of LRU 0 0.0 0.0
Instance Activity Stats for DB: IVPS Instance: ivps Snaps: 4 -5
Statistic Total per Second per Trans
--------------------------------- ------------------ -------------- ------------
immediate (CR) block cleanout app 217 0.0 0.0
immediate (CURRENT) block cleanou 8,233 1.1 0.3
index fast full scans (full) 51 0.0 0.0
index fetch by key 73,962,004 9,993.5 2,766.1
index scans kdiixs1 214,725,796 29,013.1 8,030.4
leaf node 90-10 splits 49 0.0 0.0
leaf node splits 152 0.0 0.0
logons cumulative 1,130 0.2 0.0
messages received 38,294 5.2 1.4
messages sent 38,294 5.2 1.4
no buffer to keep pinned count 0 0.0 0.0
no work - consistent read gets 75,381,318 10,185.3 2,819.2
opened cursors cumulative 279,570 37.8 10.5
parse count (failures) 5 0.0 0.0
parse count (hard) 809 0.1 0.0
parse count (total) 279,493 37.8 10.5
parse time cpu 3,823 0.5 0.1
parse time elapsed 5,986 0.8 0.2
physical reads 2,168,872 293.1 81.1
physical reads direct 20 0.0 0.0
physical reads direct (lob) 12,216 1.7 0.5
physical writes 26,533 3.6 1.0
physical writes direct 20 0.0 0.0
physical writes direct (lob) 8 0.0 0.0
physical writes non checkpoint 969 0.1 0.0
pinned buffers inspected 3 0.0 0.0
prefetched blocks 1,105,017 149.3 41.3
process last non-idle time 1,238,813,859,241 167,384,658.7 ############
recursive calls 309,650 41.8 11.6
recursive cpu usage 3,009 0.4 0.1
redo blocks written 78,842 10.7 3.0
redo buffer allocation retries 3 0.0 0.0
redo entries 129,644 17.5 4.9
redo log space requests 3 0.0 0.0
redo log space wait time 8 0.0 0.0
redo ordering marks 0 0.0 0.0
redo size 31,942,672 4,316.0 1,194.6
redo synch time 1,679 0.2 0.1
redo synch writes 25,686 3.5 1.0
redo wastage 7,317,464 988.7 273.7
redo write time 67 0.0 0.0
redo writer latching time 0 0.0 0.0
redo writes 25,811 3.5 1.0
rollback changes - undo records a 16 0.0 0.0
rollbacks only - consistent read 528 0.1 0.0
rows fetched via callback 32,257,551 4,358.5 1,206.4
session connect time 1,238,813,788,488 167,384,649.2 ############
session cursor cache count 7 0.0 0.0
session cursor cache hits 98,916 13.4 3.7
session logical reads 290,360,985 39,232.7 10,859.1
session pga memory 851,704 115.1 31.9
session pga memory max 3,146,096 425.1 117.7
session uga memory 13,451,032 1,817.5 503.1
session uga memory max 505,080,864 68,245.0 18,889.3
shared hash latch upgrades - no w 70,358,870 9,506.7 2,631.3
shared hash latch upgrades - wait 1,780 0.2 0.1
Instance Activity Stats for DB: IVPS Instance: ivps Snaps: 4 -5
Statistic Total per Second per Trans
--------------------------------- ------------------ -------------- ------------
sorts (memory) 114,340 15.5 4.3
sorts (rows) 55,595,260 7,511.9 2,079.2
summed dirty queue length 0 0.0 0.0
switch current to new buffer 657 0.1 0.0
table fetch by rowid 148,929,631 20,122.9 5,569.8
table fetch continued row 1,327,579 179.4 49.7
table scan blocks gotten 29,687,725 4,011.3 1,110.3
table scan rows gotten 1,435,576,049 193,970.6 53,688.5
table scans (long tables) 6,189 0.8 0.2
table scans (short tables) 256,444 34.7 9.6
transaction rollbacks 7 0.0 0.0
transaction tables consistent rea 0 0.0 0.0
transaction tables consistent rea 0 0.0 0.0
user calls 1,968,897 266.0 73.6
user commits 25,576 3.5 1.0
user rollbacks 1,163 0.2 0.0
workarea executions - optimal 197,099 26.6 7.4
write clones created in backgroun 0 0.0 0.0
write clones created in foregroun 1 0.0 0.0
-------------------------------------------------------------
Tablespace IO Stats for DB: IVPS Instance: ivps Snaps: 4 -5
->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)
-------------- ------- ------ ------- ------------ -------- ---------- ------
IVPS_DATA
1,010,280 137 0.0 1.8 7,598 1 762 0.0
DATA1
54,677 7 0.1 7.1 4,219 1 0 0.0
INDX
153 0 5.9 1.0 12,347 2 1 0.0
UNDOTBS
2 0 5.0 1.0 1,928 0 3 0.0
PERFSTAT
2 0 5.0 1.0 391 0 0 0.0
SYSTEM
23 0 6.1 1.0 96 0 0 0.0
USERS
4 0 5.0 1.0 52 0 0 0.0
TOOLS
3 0 3.3 1.0 2 0 0 0.0
TEMP
1 0 10.0 1.0 0 0 0 0.0
-------------------------------------------------------------
File IO Stats for DB: IVPS Instance: ivps Snaps: 4 -5
->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)
-------------- ------- ------ ------- ------------ -------- ---------- ------
DATA1 /home/oracle/oradata/ivps/data01.dbf
52,557 7 0.1 7.3 3,353 0 0
/home/oracle/oradata/ivps/data02.dbf
2,120 0 0.2 1.0 866 0 0
INDX /home/oracle/product/92/oradata/ivps/indx01.dbf
153 0 5.9 1.0 12,347 2 1 0.0
IVPS_DATA /home/oracle/oradata/ivps/data2.dbf
1,010,238 137 0.0 1.8 6,056 1 761 0.0
/home/oracle/oradata/ivps/data21.dbf
42 0 2.1 1.0 1,542 0 1 0.0
PERFSTAT /home/oracle/oradata/ivps/perfstat.dbf
2 0 5.0 1.0 391 0 0
SYSTEM /home/oracle/oradata/ivps/system01.dbf
23 0 6.1 1.0 96 0 0
TEMP /home/oracle/oradata/ivps/temp01.dbf
1 0 10.0 1.0 0 0 0
TOOLS /home/oracle/oradata/ivps/tools01.dbf
3 0 3.3 1.0 2 0 0
UNDOTBS /home/oracle/oradata/ivps/undotbs01.dbf
2 0 5.0 1.0 1,928 0 3 0.0
USERS /home/oracle/oradata/ivps/users01.dbf
4 0 5.0 1.0 52 0 0
-------------------------------------------------------------
Buffer Pool Statistics for DB: IVPS Instance: ivps Snaps: 4 -5
-> Standard block size Pools D: default, K: keep, R: recycle
-> Default Pools for other block sizes: 2k, 4k, 8k, 16k, 32k
Free Write Buffer
Number of Cache Buffer Physical Physical Buffer Complete Busy
P Buffers Hit % Gets Reads Writes Waits Waits Waits
--- ---------- ----- ----------- ----------- ---------- ------- -------- ------
D 84,084 99.3 290,399,811 2,159,149 26,606 0 0 766
-------------------------------------------------------------
Instance Recovery Stats for DB: IVPS Instance: ivps Snaps: 4 -5
-> B: Begin snapshot, E: End snapshot
Targt Estd Log File Log Ckpt Log Ckpt
MTTR MTTR Recovery Actual Target Size Timeout Interval
(s) (s) Estd IOs Redo Blks Redo Blks Redo Blks Redo Blks Redo Blks
- ----- ----- ---------- ---------- ---------- ---------- ---------- ----------
B 284 11 669 1864 1800 36864 1800
E 284 11 718 2620 1800 36864 1800
-------------------------------------------------------------
Buffer wait Statistics for DB: IVPS Instance: ivps Snaps: 4 -5
-> ordered by wait time desc, waits desc
Tot Wait Avg
Class Waits Time (s) Time (ms)
------------------ ----------- ---------- ---------
data block 760 0 0
undo header 2 0 0
undo block 1 0 0
-------------------------------------------------------------
PGA Aggr Target Stats for DB: IVPS Instance: ivps Snaps: 4 -5
-> B: Begin snap E: End snap (rows dentified with B or E contain data
which is absolute i.e. not diffed over the interval)
-> PGA cache hit % - percentage of W/A (WorkArea) data processed only in-memory
-> Auto PGA Target - actual workarea memory target
-> W/A PGA Used - amount of memory used for all Workareas (manual + auto)
-> %PGA W/A Mem - percentage of PGA memory allocated to workareas
-> %Auto W/A Mem - percentage of workarea memory controlled by Auto Mem Mgmt
-> %Man W/A Mem - percentage of workarea memory under manual control
PGA Cache Hit % W/A MB Processed Extra W/A MB Read/Written
--------------- ---------------- -------------------------
100.0 3,658 0
%PGA %Auto %Man
PGA Aggr Auto PGA PGA Mem W/A PGA W/A W/A W/A Global Mem
Target(M) Target(M) Alloc(M) Used(M) Mem Mem Mem Bound(K)
- --------- --------- ---------- ---------- ------ ------ ------ ----------
B 360 311 34.3 0.0 .0 .0 .0 18,432
E 360 311 37.8 0.0 .0 .0 .0 18,432
-------------------------------------------------------------
PGA Aggr Target Histogram for DB: IVPS Instance: ivps Snaps: 4 -5
-> Optimal Executions are purely in-memory operations
Low High
Optimal Optimal Total Execs Optimal Execs 1-Pass Execs M-Pass Execs
------- ------- -------------- ------------- ------------ ------------
8K 16K 166,761 166,761 0 0
16K 32K 15,192 15,192 0 0
32K 64K 9,510 9,510 0 0
64K 128K 3,717 3,717 0 0
128K 256K 1,358 1,358 0 0
256K 512K 386 386 0 0
512K 1024K 36 36 0 0
1M 2M 2 2 0 0
4M 8M 130 130 0 0
-------------------------------------------------------------
PGA Memory Advisory for DB: IVPS Instance: ivps End Snap: 5
-> When using Auto Memory Mgmt, minimally choose a pga_aggregate_target value
where Estd PGA Overalloc Count is 0
Estd Extra Estd PGA Estd PGA
PGA Target Size W/A MB W/A MB Read/ Cache Overalloc
Est (MB) Factr Processed Written to Disk Hit % Count
---------- ------- ---------------- ---------------- -------- ----------
45 0.1 111,730.7 6,143.2 95.0 348
90 0.3 111,730.7 640.3 99.0 33
180 0.5 111,730.7 0.0 100.0 0
270 0.8 111,730.7 0.0 100.0 0
360 1.0 111,730.7 0.0 100.0 0
432 1.2 111,730.7 0.0 100.0 0
504 1.4 111,730.7 0.0 100.0 0
576 1.6 111,730.7 0.0 100.0 0
648 1.8 111,730.7 0.0 100.0 0
720 2.0 111,730.7 0.0 100.0 0
1,080 3.0 111,730.7 0.0 100.0 0
1,440 4.0 111,730.7 0.0 100.0 0
2,160 6.0 111,730.7 0.0 100.0 0
2,880 8.0 111,730.7 0.0 100.0 0
-------------------------------------------------------------
Enqueue activity for DB: IVPS Instance: ivps Snaps: 4 -5
-> Enqueue stats gathered prior to 9i should not be compared with 9i data
-> ordered by Wait Time desc, Waits desc
Avg Wt Wait
Eq Requests Succ Gets Failed Gets Waits Time (ms) Time (s)
-- ------------ ------------ ----------- ----------- ------------- ------------
TX 26,080 26,080 0 2 11.00 0
-------------------------------------------------------------
Rollback Segment Stats for DB: IVPS Instance: ivps Snaps: 4 -5
->A high value for "Pct Waits" suggests more rollback segments may be required
->RBS stats may not be accurate between begin and end snaps when using Auto Undo
managment, as RBS may be dynamically created and dropped as needed
Trans Table Pct Undo Bytes
RBS No Gets Waits Written Wraps Shrinks Extends
------ -------------- ------- --------------- -------- -------- --------
0 25.0 0.00 0 0 0 0
1 5,397.0 0.00 997,182 17 0 2
2 5,281.0 0.00 1,022,640 18 0 3
3 5,342.0 0.00 968,608 17 0 2
4 5,185.0 0.00 934,306 17 0 2
5 5,253.0 0.00 950,540 17 0 2
6 5,402.0 0.00 980,900 18 0 3
7 5,363.0 0.00 1,516,956 14 2 9
8 5,370.0 0.00 1,006,242 18 0 2
9 5,203.0 0.00 942,338 16 0 2
10 5,257.0 0.00 1,013,006 18 0 3
-------------------------------------------------------------
Rollback Segment Storage for DB: IVPS Instance: ivps Snaps: 4 -5
->Optimal Size should be larger than Avg Active
RBS No Segment Size Avg Active Optimal Size Maximum Size
------ --------------- --------------- --------------- ---------------
0 385,024 46,283 385,024
1 712,704 82,538 2,220,032
2 712,704 87,094 2,220,032
3 647,168 89,399 2,220,032
4 712,704 85,375 2,220,032
5 712,704 64,701 2,220,032
6 712,704 526,118 17,948,672
7 2,351,104 316,159 2,351,104
8 778,240 81,302 2,220,032
9 712,704 85,012 2,220,032
10 712,704 149,022 14,737,408
-------------------------------------------------------------
Undo Segment Summary for DB: IVPS Instance: ivps Snaps: 4 -5
-> Undo segment block stats:
-> uS - unexpired Stolen, uR - unexpired Released, uU - unexpired reUsed
-> eS - expired Stolen, eR - expired Released, eU - expired reUsed
Undo Undo Num Max Qry Max Tx Snapshot Out of uS/uR/uU/
TS# Blocks Trans Len (s) Concurcy Too Old Space eS/eR/eU
---- -------------- ---------- -------- ---------- -------- ------ -------------
1 1,532 7,654,974 78 3 0 0 0/0/0/0/0/0
-------------------------------------------------------------
Undo Segment Stats for DB: IVPS Instance: ivps Snaps: 4 -5
-> ordered by Time desc
Undo Num Max Qry Max Tx Snap Out of uS/uR/uU/
End Time Blocks Trans Len (s) Concy Too Old Space eS/eR/eU
------------ ------------ -------- ------- -------- ------- ------ -------------
20-Oct 11:25 135 601,912 3 1 0 0 0/0/0/0/0/0
20-Oct 11:15 123 599,521 12 1 0 0 0/0/0/0/0/0
20-Oct 11:05 98 596,976 9 1 0 0 0/0/0/0/0/0
20-Oct 10:55 112 595,057 3 1 0 0 0/0/0/0/0/0
20-Oct 10:45 107 593,109 12 1 0 0 0/0/0/0/0/0
20-Oct 10:35 107 590,937 7 1 0 0 0/0/0/0/0/0
20-Oct 10:25 113 588,831 3 1 0 0 0/0/0/0/0/0
20-Oct 10:15 121 586,597 14 1 0 0 0/0/0/0/0/0
20-Oct 10:05 105 584,311 3 1 0 0 0/0/0/0/0/0
20-Oct 09:55 100 582,287 2 1 0 0 0/0/0/0/0/0
20-Oct 09:45 95 580,468 3 2 0 0 0/0/0/0/0/0
20-Oct 09:35 183 578,493 78 1 0 0 0/0/0/0/0/0
20-Oct 09:25 133 576,475 5 3 0 0 0/0/0/0/0/0
-------------------------------------------------------------
Latch Activity for DB: IVPS Instance: ivps Snaps: 4 -5
->"Get Requests", "Pct Get Miss" and "Avg Slps/Miss" are statistics for
willing-to-wait latch get requests
->"NoWait Requests", "Pct NoWait Miss" are for no-wait latch get requests
->"Pct Misses" for both should be very close to 0.0
Pct Avg Wait Pct
Get Get Slps Time NoWait NoWait
Latch Requests Miss /Miss (s) Requests Miss
------------------------ -------------- ------ ------ ------ ------------ ------
Consistent RBA 25,816 0.0 0 0
FAL request queue 4 0.0 0 0
FIB s.o chain latch 28 0.0 0 0
FOB s.o list latch 3,884 0.0 0.0 0 0
SQL memory manager latch 1 0.0 0 2,409 0.0
SQL memory manager worka 180,228 0.0 0 0
active checkpoint queue 31,736 5.7 0.0 0 0
alert log latch 8 0.0 0 0
archive control 250 0.0 0 0
archive process latch 157 0.0 0 0
begin backup scn array 8 0.0 0 0
cache buffer handles 75,962 0.0 0.0 0 0
cache buffers chains 439,724,840 0.0 0.0 0 4,297,961 0.0
cache buffers lru chain 27,435 0.0 0.0 0 5,991,306 0.0
channel handle pool latc 2,258 0.0 0.0 0 0
channel operations paren 8,245 0.0 0.0 0 0
checkpoint queue latch 1,384,855 0.0 0 26,430 0.0
child cursor hash table 13,304 0.0 0 0
dml lock allocation 58,975 0.0 0.0 0 0
dummy allocation 2,261 0.0 0 0
enqueue hash chains 129,872 0.0 0.0 0 0
enqueues 285,793 0.1 0.0 0 0
event group latch 1,130 0.0 0 0
hash table column usage 48 0.0 0 2,736 0.0
job_queue_processes para 120 0.0 0 0
ktm global data 26 0.0 0 0
lgwr LWN SCN 25,842 0.0 0.0 0 0
library cache 4,382,895 0.1 0.4 42 2,734 1.0
library cache load lock 156 0.0 0 0
library cache pin 2,231,989 0.0 0.0 0 0
library cache pin alloca 1,461,077 0.0 0.1 0 0
list of block allocation 2,493 0.0 0 0
loader state object free 17,842 0.0 0 0
messages 259,859 0.5 0.0 0 0
mostly latch-free SCN 25,955 0.4 0.0 0 0
multiblock read objects 2,083,316 0.0 0.0 0 0
ncodef allocation latch 117 0.0 0 0
object stats modificatio 4 0.0 0 0
post/wait queue 38,375 0.0 0 25,617 0.0
process allocation 1,130 0.0 0 1,130 0.0
process group creation 2,258 0.0 0.0 0 0
redo allocation 181,306 0.1 0.0 0 0
redo copy 0 0 129,677 0.0
redo writing 114,242 0.8 0.0 0 0
row cache enqueue latch 5,707,674 0.0 0.0 0 0
row cache objects 5,724,041 0.0 0.0 0 14 0.0
sequence cache 25,582 0.0 0 0
session allocation 537,237 0.1 0.0 0 0
session idle bit 4,204,207 0.0 0.0 0 3 0.0
session switching 117 0.0 0 0
Latch Activity for DB: IVPS Instance: ivps Snaps: 4 -5
->"Get Requests", "Pct Get Miss" and "Avg Slps/Miss" are statistics for
willing-to-wait latch get requests
->"NoWait Requests", "Pct NoWait Miss" are for no-wait latch get requests
->"Pct Misses" for both should be very close to 0.0
Pct Avg Wait Pct
Get Get Slps Time NoWait NoWait
Latch Requests Miss /Miss (s) Requests Miss
------------------------ -------------- ------ ------ ------ ------------ ------
session timer 2,457 0.0 0 0
shared pool 2,222,370 0.1 0.1 1 0
sim partition latch 0 0 415 0.0
sort extent pool 239 0.0 0 0
temp lob duration state 32 0.0 0 0
transaction allocation 2,678 0.0 0 0
transaction branch alloc 117 0.0 0 0
undo global data 87,455 0.0 0.0 0 0
user lock 4,514 0.0 0.0 0 0
-------------------------------------------------------------
Latch Sleep breakdown for DB: IVPS Instance: ivps Snaps: 4 -5
-> ordered by misses desc
Get Spin &
Latch Name Requests Misses Sleeps Sleeps 1->4
-------------------------- -------------- ----------- ----------- ------------
cache buffers chains 439,724,840 23,298 915 0/0/0/0/0
library cache 4,382,895 6,476 2,422 4093/2344/39
/0/0
shared pool 2,222,370 1,240 85 1155/85/0/0/
0
row cache objects 5,724,041 663 3 660/3/0/0/0
session idle bit 4,204,207 281 2 0/0/0/0/0
library cache pin allocati 1,461,077 116 6 110/6/0/0/0
-------------------------------------------------------------
Latch Miss Sources for DB: IVPS Instance: ivps Snaps: 4 -5
-> only latches with sleeps are shown
-> ordered by name, sleeps desc
NoWait Waiter
Latch Name Where Misses Sleeps Sleeps
------------------------ -------------------------- ------- ---------- --------
cache buffers chains kcbgtcr: kslbegin excl 0 569 407
cache buffers chains kcbgtcr: fast path 0 278 227
cache buffers chains kcbrls: kslbegin 0 42 261
cache buffers chains kcbgcur: kslbegin 0 12 0
cache buffers chains kcbget: pin buffer 0 6 2
cache buffers chains kcbzib: multi-block read: 0 4 0
cache buffers chains kcbnew 0 2 0
cache buffers chains kcbchg: kslbegin: bufs not 0 1 0
cache buffers chains kcbnlc 0 1 11
library cache kglic 0 1,730 8
library cache kglhdgn: child: 0 179 926
library cache kgldte: child 0 0 138 454
library cache kgllkdl: child: cleanup 0 130 426
library cache kglpndl: child: before pro 0 82 344
library cache kglpin: child: heap proces 0 57 180
library cache kglpnc: child 0 25 11
library cache kglobpn: child: 0 19 18
library cache kglhdgc: child: 0 18 30
library cache kglupc: child 0 11 15
library cache kgldti: 2child 0 2 3
library cache kglpndl: child: after proc 0 2 1
library cache kglpin 0 2 4
library cache kglget: child: KGLDSBYD 0 2 0
library cache pin alloca kglpnal 0 6 2
row cache objects kqrpfl: not dirty 0 2 0
row cache objects kqrpre: find obj 0 1 3
session idle bit ksupuc: if ksurcp 0 2 0
shared pool kghalo 0 48 0
shared pool kghupr1 0 29 85
shared pool kghfrunp: clatch: nowait 0 17 0
shared pool kghfrunp: clatch: wait 0 8 1
shared pool kghfrunp: alloc: wait 0 7 0
shared pool kghalp 0 1 0
-------------------------------------------------------------
Dictionary Cache Stats for DB: IVPS Instance: ivps Snaps: 4 -5
->"Pct Misses" should be very low (< 2% in most cases)
->"Cache Usage" is the number of cache entries being used
->"Pct SGA" is the ratio of usage to allocated size for that cache
Get Pct Scan Pct Mod Final
Cache Requests Miss Reqs Miss Reqs Usage
------------------------- ------------ ------ ------- ----- -------- ----------
dc_files 420 0.0 0 0 10
dc_histogram_defs 456 22.6 0 0 300
dc_object_ids 706,738 0.0 0 0 640
dc_objects 3,602 0.7 0 0 1,375
dc_profiles 2,245 0.0 0 0 2
dc_rollback_segments 12,619 0.0 0 0 482
dc_segments 1,056,431 0.0 0 0 699
dc_sequences 263 0.0 0 263 27
dc_tablespace_quotas 5 0.0 0 2 6
dc_tablespaces 703,045 0.0 0 0 9
dc_user_grants 12,929 0.0 0 0 16
dc_usernames 2,576 0.0 0 0 10
dc_users 368,590 0.0 0 0 16
-------------------------------------------------------------
Library Cache Activity for DB: IVPS Instance: ivps Snaps: 4 -5
->"Pct Misses" should be very low
Get Pct Pin Pct Invali-
Namespace Requests Miss Requests Miss Reloads dations
--------------- ------------ ------ -------------- ------ ---------- --------
BODY 11 9.1 11 9.1 0 0
CLUSTER 154 0.0 82 0.0 0 0
INDEX 104 0.0 104 0.0 0 0
SQL AREA 112,360 0.7 742,831 0.2 54 0
TABLE/PROCEDURE 357,973 0.0 373,187 0.0 19 0
-------------------------------------------------------------
Shared Pool Advisory for DB: IVPS Instance: ivps End Snap: 5
-> Note there is often a 1:Many correlation between a single logical object
in the Library Cache, and the physical number of memory objects associated
with it. Therefore comparing the number of Lib Cache objects (e.g. in
v$librarycache), with the number of Lib Cache Memory Objects is invalid
Estd
Shared Pool SP Estd Estd Estd Lib LC Time
Size for Size Lib Cache Lib Cache Cache Time Saved Estd Lib Cache
Estim (M) Factr Size (M) Mem Obj Saved (s) Factr Mem Obj Hits
----------- ----- ---------- ------------ ------------ ------- ---------------
112 .5 97 11,420 741,768 1.0 68,223,615
144 .7 128 16,026 741,772 1.0 68,224,752
176 .8 159 19,425 741,777 1.0 68,225,729
208 1.0 190 22,456 741,780 1.0 68,226,459
240 1.2 221 25,898 741,781 1.0 68,226,794
272 1.3 252 29,231 741,781 1.0 68,227,601
304 1.5 283 32,533 741,781 1.0 68,228,060
336 1.6 314 35,882 741,781 1.0 68,228,394
368 1.8 345 39,366 741,781 1.0 68,228,663
400 1.9 377 42,746 741,781 1.0 68,228,807
432 2.1 408 46,507 741,781 1.0 68,228,828
-------------------------------------------------------------
SGA Memory Summary for DB: IVPS Instance: ivps Snaps: 4 -5
SGA regions Size in Bytes
------------------------------ ----------------
Database Buffers 704,643,072
Fixed Size 452,688
Redo Buffers 5,386,240
Variable Size 369,098,752
----------------
sum 1,079,580,752
-------------------------------------------------------------
SGA breakdown difference for DB: IVPS Instance: ivps Snaps: 4 -5
Pool Name Begin value End value % Diff
------ ------------------------------ ---------------- ---------------- -------
java free memory 33,554,432 33,554,432 0.00
large free memory 67,108,864 67,108,864 0.00
shared 1M buffer 2,098,176 2,098,176 0.00
shared Checkpoint queue 2,258,432 2,258,432 0.00
shared DML lock 703,948 703,948 0.00
shared FileOpenBlock 2,652,852 2,652,852 0.00
shared KGK heap 3,756 3,756 0.00
shared KGLS heap 2,858,404 2,645,328 -7.45
shared KQR M PO 1,144,656 1,215,856 6.22
shared KQR S PO 426,620 426,620 0.00
shared KQR S SO 4,096 4,096 0.00
shared KSXR pending messages que 840,636 840,636 0.00
shared KSXR receive buffers 1,032,500 1,032,500 0.00
shared MTTR advisory 97,308 97,308 0.00
shared PL/SQL DIANA 3,029,520 3,106,548 2.54
shared PL/SQL MPCODE 733,704 771,304 5.12
shared PLS non-lib hp 2,068 2,068 0.00
shared dictionary cache 1,614,976 1,614,976 0.00
shared enqueue 1,030,284 1,030,284 0.00
shared enqueue resources 466,064 466,064 0.00
shared errors 16,188 25,344 56.56
shared event statistics per sess 13,473,600 13,473,600 0.00
shared fixed allocation callback 268 268 0.00
shared free memory 13,758,228 13,487,404 -1.97
shared joxs heap init 4,220 4,220 0.00
shared ktlbk state objects 564,960 564,960 0.00
shared library cache 39,058,020 39,228,204 0.44
shared message pool freequeue 833,032 833,032 0.00
shared miscellaneous 25,326,788 25,638,828 1.23
shared parameters 22,964 26,696 16.25
shared pl/sql source 76 76 0.00
shared processes 566,400 566,400 0.00
shared sessions 2,894,400 2,894,400 0.00
shared sql area 149,545,360 149,348,432 -0.13
shared table definiti 5,252 5,140 -2.13
shared transaction 1,302,356 1,302,356 0.00
shared trigger inform 528 528 0.00
shared type object de 64,816 64,816 0.00
buffer_cache 704,643,072 704,643,072 0.00
fixed_sga 452,688 452,688 0.00
log_buffer 5,374,976 5,374,976 0.00
-------------------------------------------------------------
Resource Limit Stats for DB: IVPS Instance: ivps End Snap: 5
-> only rows with Current or Maximum Utilization > 80% of Limit are shown
-> ordered by resource name
Current Maximum Initial
Resource Name Utilization Utilization Allocation Limit
------------------------------ ------------ ------------ ---------- ----------
max_rollback_segments 0 331 331 331
processes 36 552 600 600
-------------------------------------------------------------
init.ora Parameters for DB: IVPS Instance: ivps Snaps: 4 -5
End value
Parameter Name Begin value (if different)
----------------------------- --------------------------------- --------------
background_dump_dest /home/oracle/admin/ivps/bdump
blank_trimming TRUE
compatible 9.2.0.0.0
control_file_record_keep_time 7
control_files /home/oracle/oradata/ivps/control
core_dump_dest /home/oracle/admin/ivps/cdump
cursor_sharing SIMILAR
cursor_space_for_time TRUE
db_block_size 8192
db_cache_advice OFF
db_cache_size 704643072
db_file_multiblock_read_count 16
db_name ivps
db_writer_processes 8
fast_start_mttr_target 3600
global_names TRUE
instance_name ivps
job_queue_processes 2
large_pool_size 67108864
log_archive_dest_1 location=/home/oracle/oradata/ivp
log_archive_format arch_%t_%s.arc
log_archive_start TRUE
log_buffer 5242880
log_checkpoint_interval 1800
log_checkpoint_timeout 0
max_enabled_roles 30
open_cursors 300
optimizer_features_enable 9.2.0
os_authent_prefix
pga_aggregate_target 377487360
pre_page_sga TRUE
processes 600
query_rewrite_enabled true
remote_login_passwordfile EXCLUSIVE
resource_limit TRUE
service_names ivps
session_cached_cursors 10
sessions 1200
shared_pool_reserved_size 10485760
shared_pool_size 218103808
sort_area_size 65536
timed_statistics TRUE
transactions_per_rollback_seg 4
undo_management AUTO
undo_retention 3600
undo_tablespace UNDOTBS
user_dump_dest /home/oracle/admin/ivps/udump
workarea_size_policy AUTO
-------------------------------------------------------------
End of Report