Statspack Report for
DB Name DB ID Instance Instal 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 Changd 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.99Parse 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.24top 5 Timed Events ~~ ~~~~~~~~~~~~~~~~% Totalevent Waits Time (s) ELA TIME ------------------------- ---------------------------------------------- CPU Time 2,564 96.55latch Free 3,436 46 1.73db File Scattered Read 463, 483 17.65Log File Sync 25,610 16.61db 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 - microsec - 1000000th of a second-> Ordered by Wait Time DESC, WAITS DESC (iDle Events Last)
Avg Total Wait Waitsevent Waits Timeouts Time (S) / TXN ---------------------------------- ------ ---------- ---------- ------ -------- Latch Free 3,436 914 46 13 0.1db File Scattered read 463,483 0 17 0 17.3log file sync 25,610 0 16 1 1.0db file sequential read 588,136 0 9 0 22.0control file sequential read 1,877 0 1 0 0.1SQL * Net more data to client 39,000 0 1 0 1.5control file parallel write 2,442 0 1 0 0.1Log File Sequential Read 52 0 0 9 0.0sql * Net Break / Reset To Clien 16 0 0 11 0.0log file switch completion 3 0 0 26 0.0log File Parallel Write 25,815 25,813 0 0 1.0db File Parallel Write 12,448 0 0 0.5direct path read (LOB) 12,
092 0 0 0 0 0.0 0 0 0.01Queue 2 0 0 11 0.0log file Single Write 8 0 0 0 0.0LGWR WAIT for Redo Copy 17 0 0 0 0.0ASync Disk IO 66 0 0 0 0.0Direct Path Write LOB) 8 0 0 0 0.0Direct Path READ 20 0 0 0 0.0Direct Path Write 20 0 0 0 0.0sql * Net Message from Clom Client 1,969,940 0 74,301 38 73.7sql * Net Message to Client 1,969,943 0 3 0 73.7sql * Net More Data From Clien 66 0 0 0 0.0 ----------------------------- ------------------ Background WAIT EV Ents for DB: IVPS Instance: Ivps Snaps: 4 -5-> Ordered by Wait Time DESC, WAITS DESC (iDle Events Last)
Avg Total Wait Waitsevent Waits Timeouts Time (S) / TXN ---------------------------------- -------------- ---------- ------ -------- Control File Sequential Read 1,408 0 1 1 0.1Control File Parallel Write 2,441 0 0 0.1log file sequential read 52 0 0 9 0.0log file parallel write 25,814 25,812 0 0 1.0db file parallel write 12,447 0 0 0 0.5LATCH Free 1 0 0 0 0.0log file sales 0 8 8 8 0 0 0 0 0 0.0rdbms IPC Reply 12 0 0 0 0.0lgwr Wait for Redo Copy 17 0 0 0 0.0async Disk IO 66 0 0 0 0.0buffer busy waits 1 0 0 0 0.0direct path read 20 0 0 0 0.0direct path write 20 0 0 0 0.0rdbms ipc message 102,495 73,130 91,429 892 3.8pmon timer 2,457 2,457 7,219 2938 0.1smon 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 100CPU 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 1675138424Module: JDBC Thin Clientselect 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 1269097881Module: 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_time) from tb_user_profile))
14,131,015 588 24,032.3 4.9 123.00 122.31 1989700802Module: JDBC Thin Clientselect pk, board_pk, thread_type, thread_title, thread_sendscore, pic_url, is_top, is_soul, create_time, create_user, click_num, is_lock, status, memo, thread_class, board_name, brief_name, lastreply_time from (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 1374273252Module: JDBC Thin Client select count (distinct user_name) from tb_user_mark where user_name not in (select user_name from editorID) and user_name in (select user_name from tb_user where pk in (select user_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 3401611908Module:. JDBC Thin Clientselect * from (select drawing *, rownum as my_rownum from (selectdistinct pk, drawing_title, drawing_author, commend_username, 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 Order by public_time
10,161,215 1,197 8,488.9 3.5 24.32 23.81 3933472146Module:. JDBC Thin Clientselect count (pk) from (select drawing *, rownum as my_rownum from (select distinct pk, drawing_title, drawing_author, commend_username, province, drawing_intro, rank, drawing_size, public_time, download_count, file_name , Key_Word, Drawing_Price, Memo, Week_Download_CountFrom Drawing_View1 Where Drawing_State = 2 And Way =: 1) Drawing)
7,255,010 797 9,102.9 2.5 17.67 17.22 3295982819Module:. JDBC Thin Clientselect count (pk) from (select paper *, rownum as my_rownum from (select distinct pk, paper_title, paper_intro, public_time, click_count, 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 100CPU 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 837701357Module:. JDBC Thin Clientselect * from (select paper *, rownum as my_rownum from (select distinct pk, paper_title, paper_intro, public_time, click_count, paper_author, copyright, commend_username from paper_view1 where paper_state = 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 1550292743Module:. JDBC Thin Clientselect * 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 Bypublic_time desc) Standard where rownum <=: 2) where my_rownum> =: 3
6,911,960 934 7,400.4 2.4 15.35 15.32 3775561653Module:. JDBC Thin Clientselect count (pk) 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) standard) 6,111,223 18 339,512.4 2.1 28.32 28.22 3837137728Module:. JDBC Thin Client select * from (select a *, rownum as rn from (select pk, BOARD_PK, thread_type, thread_title, thread_sendscore, pic_url, is_top, is_soul, create_time, create_user, click_num, is_lock, status, memo, thread_class, lastreply_time, lastreply_person, reply_num, forum_pk from forum_board_thread_view2 where pk in (select distinct (thr
5,054,148 3,750 1,347.8 1.7 35.69 35.37 3682418978Module: JDBC Thin Client select a.num b.re_num from (select count (*) as num from tb_forum_thread where board_pk =: "SYS_B_0" and status =: "SYS_B_1") a, (select sum (reply_num) as re_num from tb_forum_thread whereboard_pk =: "sys_b_2" and status =: "sys_b_3") B
5,046,638 697 7,240.5 1.7 11.39 11.16 803871542Module:. JDBC Thin Clientselect * 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 software_state = 2 and way =: 1 Order by public_time desc) Software where rownum <=: 2) where my_rownum> =: 3
5,010,437 692 7,240.5 1.7 11.34 10.91 85528283Module: 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 100CPU Elapsd Buffer Gets Executions Gets per Exec% Total Time (s) Time (s) Hash Value -------------------------------------------- ------ --------- ---------- Select Count (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_view1where software_state = 2 and way =: 1) Software)
4,670,772 404 11,561.3 1.6 34.09 33.44 1963425936Module: JDBC Thin ClientSelect 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 760269237Module: OraEdit.exe select head_img_url, signature, user_sex, regist_time, user_name 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_07",: "SYS_B_07",: "SYS_B_09",: "SYS_B_10",: "SYS_B_11",: "SYS_B_12",: "SYS_B_14",: "sys_b_15",: "sys_b_15" ,: "SYS_B_16",: "SYS_B260, 779 18 14, 487.7 12.0 19.49 19.21 1909470160Module: JDBC Thin ClientSelect 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 4045833718Module: JDBC Thin Client select head_img_url, signature, user_sex, regist_time, user_name from tb_user_profile where user_name in (: "SYS_B_0") orderby user_name
95,929 353 271.8 4.4 35.55 35.22 3728079592Module: JDBC Thin Client select head_img_url, signature, user_sex, regist_time, user_name 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 3503934362Module: JDBC Thin Client select head_img_url, signature, user_sex, regist_time, user_name 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 2710994462Module: JDBC Thin Client select head_img_url, signature, user_sex, regist_time, user_name from tb_user_profile where user_name in (: "SYS_B_0",: "SYS_B_1",: "SYS_B_2",: "SYS_B_3") order by user_name58 , 469 210 278.4 2.7 28.75 28.23 314705737Module: JDBC Thin Client select head_img_url, signature, user_sex, regist_time, user_name 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_Name
48,563 176 275.9 2.2 21.84 21.44 4042305192Module: JDBC Thin Client select head_img_url, signature, user_sex, regist_time, user_name 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 3958573495Module: JDBC Thin Client select head_img_url, signature, user_sex, regist_time, user_name 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_5 ",:" SYS_B_6 ",:" SYS_B_7 ") Order by user_name
38,694 131 295.4 1.8 22.91 22.62 2373701249Module: JDBC Thin Client select head_img_url, signature, user_sex, regist_time, user_name 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_7 ",:" SYS_B_8 ",:" SYS_B_9 ") ORDER BY User_name37,955 2 18,977.5 1.7 1.03 1.02 1306561092Module: JDBC Thin Clientdelete from Enterp_info_db where id =: 1
37,783 2 18,891.5 1.7 1.58 1.53 2587249602Module: JDBC Thin Clientinsert into enterp_info (id, FULL_NAME, COMP_LOCATION, COMP_POST, COMP_URL, way, relate_name, title, mobile, sex, password, question, answer, NAME, COMP_MAIL, COMP_MAIL2, COMP_PHONE, COMP_LOCATE1, Comp_locate2, Comp_locate3, Comp_Law, Comp_fax, Regdate, ClickTimes, NewEnterp, Auth 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 987341707Module: JDBC Thin Clientselect 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_info_db b where a.id = b .id and A.ID =: 1
34,454 122 282.4 1.6 21.32 21.07 1955826787Module: JDBC Thin Client select head_img_url, signature, user_sex, regist_time, user_name 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_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 3230219899Module: JDBC Thin Client select head_img_url, signature, user_sex, regist_time, user_name 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 ") order by user_name29,535 101 292.4 1.4 15.98 15.69 2871452022Module: JDBC Thin Client select head_img_url, signature, user_sex, regist_time, user_name from tb_user_profile where user_name in (:" SYS_B_0 ",:" SYS_B_1 ",:" SYS_B_2 ",:" 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 1916155889Module: JDBC Thin Clientselect 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 3758460219Module: JDBC Thin Clientselect 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 1121428772Module: JDBC Thin Clientselect count (*) from tb_user_profile where to_char (regist_time, 'yyyy-mm-dd hh-mm-ss') like '2004-10-03%' and user_career =: 128,910 96 301.1 1.3 10.67 10.65 376695217Module: JDBC Thin ClientSelect 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 883252759Module : JDBC Thin ClientSelect User_mark from tb_user_mark where user_name =: "sys_b_0" ORDER BY MARK_TYPE
15,079 12,768 0.8 0.00 0.00 723194081Module: 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 2150269977Module: 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 1885449141Module: JDBC Thin Clientselect 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 2689867434Module: JDBC Thin Clientselect PK, BOARD_PK, MASTER_NAME, OPTION_TIME, OPTION_USER, STATUS from tb_forum_master where BOARD_PK =: "SYS_B_0" and status =: "SYS_B_1" 7,759 22,856 2.9 0.00 0.00 3222431807Module: JDBC Thin Clientselect remark_id, topic_pk , Remark_Username, Rank, Remark, TO_CHAR (Remark_time, 'YYYY-MM-DD HH: MI: SS') AS Remark_time, Memo from Tb_remark where topic_pk =: 1 And Memo Like: 2 Order by Remark_Time DESC
7,082 83,194 11.7 0.00 0.00 1968834830Module: JDBC Thin Client select pk, forum_pk, board_name, board_desc, create_time, create_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 3207535377Module: JDBC Thin ClientSELECT distinct pk, paper_title, paper_author, rank, upload_time, click_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 2837400515Module: JDBC Thin ClientSelect * from sub_type where doc_type =: 1 and profession_type =: 2Rder by sub_type
4,828 4,287 0.9 0.00 0.00 1249154402Module: JDBC Thin Clientselect 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 2486814933Module: JDBC Thin ClientUpdate TB_forum_thread set click_num =: "SYS_B_0" Where PK =: "SYS_B_1"
4,580 4,580 1.0 0.00 0.00 4275259987Module: JDBC Thin Clientselect pk, thread_title, thread_content, create_user, create_time, thread_sendscore, click_num, is_soul, is_lock, is_top, pic_url, is_encourage, last_editor, last_edit_time, function_type, download_times, reply_num from tb_forum_thread where pk =: " SYS_B_0 "
4,578 42,157 9.2 0.00 0.00 3791129184Module: OraEdit.exeselect PK, thread_getscore, pic_url, reply_content, create_time, create_user, status, memo, is_encourage, last_editor, last_edit_time, download_times from (select rownum as num, PK, thread_getscore, pic_url, reply_content, create_time, Create_user, status, memo, is_encourage, last_editor, limited_edit_time, download_times from (SELECT
3,883 3,883 1.0 0.00 0.00 510864396Module: JDBC Thin ClientSelect To_Char (Max (Down_Date), 'YYYY-MM-DD HH24: MM: SS') from TB_Download_LOG WHERE PK =: 1 and username =: 2
3,883 152,718 39.3 0.00 0.00 2003553047Module: JDBC Thin ClientSelect * from subs_type where doc_type =: "sys_b_0"
3,750 3,750 1.0 0.01 0.01 3682418978Module: JDBC Thin Client select a.num b.re_num from (select count (*) as num from tb_forum_thread where board_pk =: "SYS_B_0" and status =: "SYS_B_1") a, (select sum ( reply_num) as re_num from tb_forum_thread whereboard_pk =: "SYS_B_2" and status =: "SYS_B_3") b3,735 4,295 1.1 0.00 0.00 218636415Module: JDBC Thin Clientselect user_mark from tb_user_mark where user_name =: "SYS_B_0" and mark_type =: "SYS_B_1"
3,515 4,275 1.2 0.00 0.00 481945932Module: JDBC Thin ClientSelect ID, SUB_TYPE, SUB_TYPE_NAME, PROFESSION_TYPE, DOC_TYPE_NAME, CAREERNAME from Drawing Where PK =: 1 And Drawing_State = 2
3,515 3,515 1.0 0.00 0.00 959971486Module: JDBC Thin ClientUpdate 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 ClientSelect Click_Count From tb_drawing where pk =: "sys_b_0"
3,515 3,515 1.0 0.00 0.00 1639465010Module: JDBC Thin ClientSELECT 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_count FROM tb_drawing WHERE pk =: 1 and drawing_state = 23,441 3,441 1.0 0.00 0.00 3833490558Module: JDBC Thin ClientInsert Into TB_DOWNLOAD_LOG (ID, PK, Username, Score, Down_Date) Values (seq_download.nextval,: 1,: 2,: 3, sysdate)
2,553 2,553 1.0 0.00 0.00 3258989780Module: JDBC Thin ClientSELECT drawing_title, drawing_author, commend_username, province, drawing_intro, drawing_size, upload_time, file_name, key_word, drawing_price, drawing_state, download_count, week_download_count, click_count, memo FROM tb_drawing WHERE pk =: 1
2,306 2,306 1.0 0.00 0.00 2069724299Module: JDBC Thin ClientSelect Is_Charge, ExpiRetime from Charge_info WHERE PK = (Select Max (PK) from charge_Info where id =: 1)
2,266 2,266 1.0 0.00 0.00 980868662Module: JDBC thin clientupdate 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 883252759module: JDBC Thin ClientSelect user_mark from tb_user_mark where user_name =: "sys_b_0" ORDER BY MARK_TYPE
15,078 15,079 5.39 2150269977Module: 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 723194081Module: 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 1885449141Module: JDBC Thin ClientSelect 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 2689867434Module: JDBC Thin Clientselect PK, BOARD_PK, MASTER_NAME, OPTION_TIME, OPTION_USER, STATUS from tb_forum_master where BOARD_PK =: "SYS_B_0" and status =: "SYS_B_1"
7,759 7,759 2.78 3222431807Module: JDBC Thin Clientselect remark_id, topic_pk, remark_username, rank, remark, TO_CHAR (remark_time, 'YYYY-MM-DD HH: MI: SS') as remark_time, memo from tb_remark where topic_pk =: 1 and memo like: 2 ORDER by remark_time desc
7,082 7,082 2.53 1968834830Module: JDBC Thin Client select pk, forum_pk, board_name, board_desc, create_time, create_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 3207535377Module: JDBC Thin ClientSELECT distinct pk, paper_title, paper_author, rank, upload_time, click_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 2837400515Module: JDBC Thin Clientselect * from sub_type where doc_type =: 1 and profession_type =: 2order by sub_type4,828 4,828 1.73 1249154402Module: JDBC Thin Clientselect 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 2486814933Module: JDBC Thin ClientUpdate TB_forum_thread set click_num =: "sys_b_0" where pk =: "sys_b_1"
4,580 4,580 1.64 4275259987Module: JDBC Thin Clientselect pk, thread_title, thread_content, create_user, create_time, thread_sendscore, click_num, is_soul, is_lock, is_top, pic_url, is_encourage, last_editor, last_edit_time, function_type, download_times, reply_num from tb_forum_thread where pk =: "SYS_B_0"
4,578 4,578 1.64 3791129184Module: OraEdit.exeselect PK, thread_getscore, pic_url, reply_content, create_time, create_user, status, memo, is_encourage, last_editor, last_edit_time, download_times from (select rownum as num, PK, thread_getscore, pic_url, reply_content, create_time, create_user, Status, Memo, IS_ENCOURAGE, LAST_EDITOR, LAST_EDIT_TIME, Download_TIMES FROM (SELECT
3,883 3,883 1.39 510864396Module: JDBC thin clientselect to_char (max (down_date), 'YYYY-MM-DD HH24: mm: ss') from tb_download_log where pk =: 1 and username =: 2
3,883 3,883 1.39 2003553047Module: JDBC Thin ClientSelect * from sub_type where doc_type =: "sys_b_0"
3,750 3,750 1.34 3682418978Module: JDBC Thin Client select a.num b.re_num from (select count (*) as num from tb_forum_thread where board_pk =: "SYS_B_0" and status =: "SYS_B_1") a, (select sum (reply_num) as re_num from tb_forum_thread whereboard_pk =: "SYS_B_2" and status =: "SYS_B_3") b3,735 3,735 1.34 218636415Module: JDBC Thin Clientselect user_mark from tb_user_mark where user_name =: "SYS_B_0" and mark_type =: "SYS_B_1"
3,515 3,515 1.26 481945932Module: JDBC Thin ClientSelect ID, SUB_TYPE, SUB_TYPE_NAME, PROFESSION_TYPE, DOC_TYPE_NAME, CAREERNAME from Drawing Where Pk =: 1 and Drawing_State = 2
3,515 3,515 1.26 959971486Module: JDBC Thin ClientUpdate 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 ClientSelect Click_Count from TB_Drawing where pk =: "SYS_B_0"
3,515 3,515 1.26 1639465010Module: JDBC Thin ClientSELECT 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_count FROM tb_drawing WHERE pk =: 1 and drawing_state = 2
3,441 3,441 1.23 3833490558Module: JDBC Thin ClientInsert INTO TB_DOWNLOAD_LOG (ID, PK, Username, Score, Down_Date) VALUES (seq_download.nextval,: 1,: 2,: 3, sysdate)
2,553 2,553 0.91 3258989780Module: JDBC Thin ClientSELECT drawing_title, drawing_author, commend_username, province, drawing_intro, drawing_size, upload_time, file_name, key_word, drawing_price, drawing_state, download_count, week_download_count, click_count, memo FROM tb_drawing WHERE pk =: 12,306 2,306 0.83 2069724299Module: JDBC Thin Clientselect is_charge, ExpiRetime from charge_info where pk = (SELECT MAX (PK) from arrival_info where id =: 1)
2,266 2,266 0.81 980868662module: jdbc thin clientupdate 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.6cpu used when Call Started 256, 414 34.7 9.6CR blocks created 587 0.1 0.0DBWR buffers scanned 0 0.0 0.0DBWR checkpoint buffers written 26,505 3.6 1.0DBWR checkpoints 2 0.0 0.0DBWR free buffers found 0 0.0 0.0DBWR lru scans 0 0.0 0.0DBWR make free requests 0 0.0 0.0DBWR summed scan depth 0 0.0 0.0 DBWR Transaction Table Writes 418 0.1 0.0DBWR undo block writes 1,476 0.2 0.1SQL * Net roundtrips to / from client 1,966,506 265.7 73.5active txn count during cleanout 369 0.1 0.0background checkpoints completed 2 0.0 0.0background checkpoints started 2 0.0 0.0background timeouts 25,738 3.5 1.0branch node splits 0 0.0 0.0Buffer IS Not Pinned Count 146,947,422 19,855.1 5,495.6buffer is Pinned Count 325, 453, 422 43,
974.3 12,171.5bytes received via SQL * Net from c 113,185,543 15,293.3 4,233.0bytes sent via SQL * Net to client 267,353,677 36,124.0 9,998.6calls to get snapshot scn: kcmgss 1,699,625 229.7 63.6calls to kcmgas 27,295 3.7 1.0calls to kcmgcs 124 0.0 0.0change write time 286 0.0 0.0cleanout - number of ktugct calls 537 0.1 0.0cleanouts and rollbacks - consist 69 0.0 0.0cleanouts only - consistent read 148 0.0 0.0cluster key scan block gets 6,116 0.8 0.2cluster key scans 4,418 0.6 0.2commit cleanout failures: buffer 20 0.0 0.0commit Cleanout Failur es: callbac 1 0.0 0.0commit cleanout failures: can not 5 0.0 0.0commit cleanouts 70,722 9.6 2.6commit cleanouts successfully com 70,696 9.6 2.6commit txn count during cleanout 4,143 0.6 0.2consistent changes 1,356 0.2 0.1consistent gets 290,139,061 39,202.7 10,850.8consistent gets - examination 143,707,541 19,417.3 5,
374.5cursor authentications 302 0.0 0.0data blocks consistent reads - un 625 0.1 0.0db block changes 226,130 30.6 8.5db block gets 234,140 31.6 8.8deferred (CURRENT) block cleanout 29,300 4.0 1.1dirty buffers inspected 0 0.0 0.0enqueue conversions 28 0.0 0.0enqueue releases 64,913 8.8 2.4enqueue Requests 64,913 8.8 2.4enqueue Timeouts 0 0.0 0.0Exchange Deadlocks 0 0.0 0.0execute Count 0.0 0.0execute Count 0.0 0.0execute Count 0.0 0.0execute Count 287,853 38.9 10.8free buffer inspected 96 0.0 0.0free buffer requested 2,158,823 291.7 80.7hot 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.0immediate (Current) Block Cleanou 8,233 1.1 0.3index fast full scans (full) 51 0.0 0.0index fetch by key 73,962,004 9,993.5 2,766.1index scans kdiixs1 214,725,796 29,013.1 8,030.4leaf node 90-10 splits 49 0.0 0.0leaf node splits 152 0.0 0.0logons cumulative 1,130 0.2 0.0messages received 38,294 5.2 1.4 Messages Sent 38,294 5.2 1.4No Buffer to Keep Pinned Count 0 0.0 0.0no work - consistent read gets 75,381,318 10,185.3 2,819.2opened cursors cumulative 279,570 37.8 10.5parse count (failures) 5 0.0 0.0parse count (hard) 809 0.1 0.0parse count (total) 279,493 37.8 10.5parse time cpu 3,823 0.5 0.1parse time ELAPSED 5,986 0.8 0.2physical reads 2,168,
872 293.1 81.1physical reads direct 20 0.0 0.0physical reads direct (lob) 12,216 1.7 0.5physical writes 26,533 3.6 1.0physical writes direct 20 0.0 0.0physical writes direct (lob) 8 0.0 0.0physical writes non checkpoint 969 0.1 0.0pinned buffers inspected 3 0.0 0.0prefetched blocks 1,105,017 149.3 41.3process last non-idle time 1,238,813,859,241 167,384,658.7 ############ recursive calls 309,650 41.8 11.6recursive cpu usage 3,009 0.4 0.1redo blocks written 78,842 10.7 3.0redo buff er allocation retries 3 0.0 0.0redo entries 129,644 17.5 4.9redo log space requests 3 0.0 0.0redo log space wait time 8 0.0 0.0redo ordering marks 0 0.0 0.0redo size 31,942,672 4,316.0 1,194.6redo synch time 1,679 0.2 0.1redo synch writes 25,686 3.5 1.0redo Wastage 7, 317,
464 988.7 273.7redo write time 67 0.0 0.0redo writer latching time 0 0.0 0.0redo writes 25,811 3.5 1.0rollback changes - undo records a 16 0.0 0.0rollbacks only - consistent read 528 0.1 0.0rows fetched via callback 32,257,551 4,358.5 1,206.4session connect time 1,238,813,788,488 167,384,649.2 ############ session cursor cache count 7 0.0 0.0session cursor cache hits 98,916 13.4 3.7session logical reads 290,360,985 39,232.7 10,859.1session pga memory 851,704 115.1 31.9session pga memory max 3,146,096 425.1 117.7session u ga memory 13,451,032 1,817.5 503.1session uga memory max 505,080,864 68,245.0 18,889.3shared hash latch upgrades - no w 70,358,870 9,506.7 2,631.3shared 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.0switch current to new buffer 657 0.1 0.0table fetch by rowid 148,929,631 20,122.9 5,569.8table fetch 1,327,579 179.4 49.7table scan blocks gotten 29,687,725 4,011.3 1,110.3table scan rows gotten 1,435,576,049 193,970.6 53,688.5table scans (long tables) continued row 6,189 0.8 0.2table SCANS (Short Tables) 256, 444 34.7 9.6Transaction Rollbacks 7 0.0 0.0transaction tables consistent rea 0 0.0 0.0transaction tables consistent rea 0 0.0 0.0user calls 1,968,897 266.0 73.6user commits 25,576 3.5 1.0user rollbacks 1,163 0.2 0.0workarea executions - optimal 197,
099 26.6 7.4Write Clones Created in Backgroun 0 0.0 0.0write 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) DESCTABLESPACE ------------------------------ AV AV av buffer av buf adj beff (MS) ) BLKS / RD WRITES WRITES / S WAITS WT (MS) -------------- ------------------- ---------------- ---------- ------ IVPS_DATA 1,010,280 137 0.0 1.8 7,598 1 762 0.0Data1 54,677 7 0.1 7.1 4,219 1 0 0.0INDX 153 0 5.9 1.0 12,347 2 1 0.0undoTBS 2 0 5.0 1.0 1,928 0 3 0.0perfstat 2 0 5.0 1.0 391 0 0 0.0System 23 0 6.1 1.0 96 0 0 0.0Users 4 0 5.0 1.0 52 0 0 0.0 2 0 0 0.0Temp 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 buffer av buf adj bes 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 0INDX /HOME/Omracle/Product/92/oradata/iVPS/indx01.dbf 153 0 5.9 1.0 12,347 2 1 0.0
IVPS_DATA /HOME/Orcle/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.0 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/Orcle/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 Busyp 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) Estd iOS Redo Blks Redo Blks Redo Blks Redo Blks Redo BLKS - ------------- ----------------------------------- ----- B 284 11 669 1864 1800 36864 1800E 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 Avgclass Waits Time (S) Time (MS) ----------------------------------- - --------- Data Block 760 0 0undo Header 2 0 0undo 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 IE Notified 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 Controlpga Cache Hit% W / A MB Processed Extra W / A MB r EAD / 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 Bound (K) - --------------------------------- - ------ ---------- B 360 311 34.3 0.0 .0.0 .0 18, 432E 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 HighOptimal 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 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 E STD PGA Overalloc Count Is 0
ESTD EXTRA ESTD PGA ESTD PGAPGA 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 34 890 0.3 111,730.7 640.3 99.0 33 180 0.5 111,730.7 0.0 100.0 0270 0.8 111,730.7 0.0 100.0 0360 1.0 111,730.7 0.0 100.0 0432 1.2 111,730.7 0.0 100.0 0504 1.4 111,730.7 0.0 100.0 0576 1.6 111,730.7 0.0 100.0 0648 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, 7 30.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 ---------------------------- -------------------------------- 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 Waiteq Requests Succ Gets Faied 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 managing, as rbs may be dynamically created and dropped as needed
TRANS TABLE PCT ANDO BYTESRBS 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,024 5,185.0 0.00 934,306 17,025 5,253.0 0.00 950,540 17,026 5,402.0 0.00 980,900 18,037 5,363.0 0.00 1,516,956 14,298 5,370.0 0.00 1,006,242 18,029 5,203.0 0.00 942,338 160,210 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 ------ ----------------------------- ---- ----------- --------------- 46,283 0 385,024 385,024 1 712,704 2 712,704 87,094 2,220,032 82,538 2,220,032 89,399 2,220,032 647,168 3 85,375 4 712,704 5 712,704 64,701 2,220,032 2,220,032 6 712, 704 526, 118 17, 948, 672 7 2,351,104 316,159 2,351,104 8 778,40 81,302 2,220,032,40,022,712,704 149,022 14,737,408 ---------------------------------------------------------------------------------------- ------------------------------ Undo Segment Summa Ry for DB: IVPS IVPS: 4 -5-> Undo Segment Block Stats: -> US - Unxpired Stolen, Ur - Unexpired Released, Uu - Unexpired Reused-> ES - Expired Stolen, Er - Expired Released, EU - EU - EXPIRED RELEASED Expired Reuseundo 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,300 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/020-OCT 11:15 123 599, 521 12 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 020 -OCT 11:05 98 596, 976 9 1 0 0 0 0/0/0/0/0/020-Oct 10:55 112 595,057 3 1 0 0 0/0/0/0/0/020-Oct 10:45 107 593, 109 12 1 0 0 0 0 0/0/0/0/020-OCT 10:35 107 590, 937 7 1 0 0 0 0/0/0/0/020-OCT 10:25 113 588, 831 3 1 0 0 0 / 0/0/0/0/020-OCT 10:15 121 586,597 14 1 0 0 0 0 0/0/0/0/020-OCT 10: 05 584, 311 3 1 0 0 0 0 0 0 0 0 0 / 0/020-oct 09:55 100 582, 287 2 1 0 0 0 0/0/0/0/020-OCT 09: 45 95 580, 468 3 2 0 0 0 0/0/0/0/0/020-OCT 09:35 183 578, 493 78 1 0 0 0/0/0/0/0/020-OCT 09:25 133 576, 475 5 3 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-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 NOWAITLATCH Requests Miss / Miss (S) Requests Miss ----------------------------- ------------ ------ ------ ------------ ------ CONSISTENT RBA 25, 816 0.0 0 0Fal Request queue 4 0.0 0 0FIB so chain latch 28 0.0 0 0FOB so list latch 3,884 0.0 0.0 0 0SQL memory manager latch 1 0.0 0 2,409 0.0SQL memory manager worka 180,228 0.0 0 0active checkpoint queue 31,736 5.7 0.0 0 0alert log latch 8 0.0 0 0archive control 250 0.0 0 0archive Process Latch 157 0.0 0 0begin backup scn array 8 0.0 0 0cache buffer handles 75,962 0.0 0.0 0 0cache buffers chains 439,724,840 0.0 0.0 0 4,297,961 0.0cache buffers lru chain 27,435 0.0 0.0 0 5,991,306 0.0channel handle pool latc 2,258 0.0 0.0 0 0channel operations paren 8,245 0.0 0.0 0 0checkpoint Queue Latch 1,384,855 0.0 0 26,430 0.0Child Cursor Hash Table 13,304 0.0 0 0dml Lock Allocation 58,975 0.0 0.0 0 0dummy Allocation 2,
261 0.0 0 0enqueue hash chains 129,872 0.0 0.0 0 0enqueues 285,793 0.1 0.0 0 0event group latch 1,130 0.0 0 0hash table column usage 48 0.0 0 2,736 0.0job_queue_processes para 120 0.0 0 0ktm global data 26 0.0 0 0lgwr LWN SCN 25,842 0.0 0.0 0 0library cache 4,382,895 0.1 0.4 42 2,734 1.0library cache load lock 156 0.0 0 0library cache pin 2,231,989 0.0 0.0 0 0library cache pin alloca 1,461,077 0.0 0.1 0 0list of block allocation 2,493 0.0 0 0loader state object free 17,842 0.0 0 0 messages 259,859 0.5 0.0 0 0mostly latch-free SCN 25,955 0.4 0.0 0 0multiblock read objects 2,083,316 0.0 0.0 0 0ncodef allocation latch 117 0.0 0 0object stats modificatio 4 0.0 0 0post / wait queue 38,375 0.0 0 25,617 0.0process allocation 1,130 0.0 0 1,130 0.0process GROUP CREATION 2, 258 0.0 0.0 0 0Redo Allocation 181, 306 0.1 0.0 0 0Redo Copy 0 0 129,
677 0.0redo writing 114,242 0.8 0.0 0 0row cache enqueue latch 5,707,674 0.0 0.0 0 0row cache objects 5,724,041 0.0 0.0 0 14 0.0sequence cache 25,582 0.0 0 0session allocation 537,237 0.1 0.0 0 0session idle bit 4,204,207 0.0 0.0 0 3 0.0session 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 NOWAITLATCH Requests Miss / Miss (S) Requests Miss ----------------------------- ------------ ------ ------ ------------ ------ Session Timer 2,457 0.0 0 0Shared Pool 2,222,370 0.1 0.1 1 0sim partition latch 0 0 415 0.0sort extent pool 239 0.0 0 0temp lob duration state 32 0.0 0 0transaction allocation 2,678 0.0 0 0transaction branch alloc 117 0.0 0 0undo global data 87,455 0.0 0.0 0 0user 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 / 0Library Cache 4,382,895 6,476 2,422 4093/2344/39/0/0shared pool 2,222,370 1,240 85 1155/85/0/0/041 663 3 660 / 3/0/041 663 3 660/3/0/07 281 2 0/0 / 0/0 / 0Library 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 WAITERLATCH Name Where Misses Sleeps Sleeps ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------ Cache Buffers CHAINS KCBGTCR: KSLBEGIN EXCL 0 569 407Cache Buffers CHAINS KCBGTCR: FAST PATH 0 278 227cache buffers chains kcbrls: kslbegin 0 42 261cache buffers chains kcbgcur: kslbegin 0 12 0cache buffers chains kcbget: pin buffer 0 6 2cache buffers chains kcbzib: multi-block read: 0 4 0cache buffers chains kcbnew 0 2 0cache buffers chains kcbchg: kslbegin: BUFS NOT 0 1 0Cache Buffers Chains KCBNLC 0 1 11Library Cache Kglic 0 1,730 8library Cache K glhdgn: child: 0 179 926library cache kgldte: child 0 0 138 454library cache kgllkdl: child: cleanup 0 130 426library cache kglpndl: child: before pro 0 82 344library cache kglpin: child: heap proces 0 57 180library cache kglpnc: child 0 25 11Library Cache Kglobpn: Child: 0 19 18library Cache Kglhdgc: Child: 0 18 30library Cache Kglupc: Child 0 11 15Library Cache KGLDTI:
2child 0 2 3library cache kglpndl: child: after proc 0 2 1library cache kglpin 0 2 4library cache kglget: child: KGLDSBYD 0 2 0library cache pin alloca kglpnal 0 6 2row cache objects kqrpfl: not dirty 0 2 0row cache objects kqrpre: find obj 0 1 3session idle bit ksupuc: if ksurcp 0 2 0shared pool kghalo 0 48 0shared pool kghupr1 0 29 85shared pool kghfrunp: clatch: nowait 0 17 0shared pool kghfrunp: clatch: wait 0 8 1shared pool kghfrunp: alloc: wait 0 7 0shared 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 FINALCACHE Requests Miss Reqs Miss Reqs Usage ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------- ---------- DC_FILES 420 0.0 0 0 10dc_histogram_defs 456 22.6 0 0 300DC_Object_IDS 706, 738 0.0 0 0 640dc_objects 3,602 0.7 0 0 1,375dc_profiles 2,245 0.0 0 0 2dc_rollback_segments 12,619 0.0 0 0 482dc_segments 1,056,431 0.0 0 0 699dc_sequences 263 0.0 0 263 27dc_tablespace_quotas 5 0.0 0 2 6dc_tablespaces 703,045 0.0 0 0 9dc_user_ GRANTS 12,929 0.0 0 0 16dc_usernames 2,576 0.0 0 0 10DC_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 Dational -------------------------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------- ---------------- -------- Body 11 9.1 11 9.1 0 0Cluster 154 0.0 82 0.0 0 0 0Index 104 0.0 104 0.0 0 0SQL Area 112, 360 0.7 742, 831 0.2 54 0Table / 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 Offen 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 (eg in v $ librarycache), with the number of Lib Cache M Emory Objects Is Invalid
ESTDSHARED 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 144.7 68,223,615 11,420 741,768 1.0 1.0 128 16,026 741,772 19,425 741,777 68,224,752 159 176.8 1.0 208 1.0 190 22,456 68,225,729 68,226,459 240 741,780 1.0 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, 6 63 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 -5SGA Regions Size In Bytes ----- ---------------------------------------- Database Buffers 704, 643, 072Fixed Size 452,688redo buffers 5,386,240 Variable size 369,098,752 ---------------- Sum 1,079,580,752 --------------------------- --------------------------------
SGA BREAKDOWN DIFERENCE 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.00lage Free Memory 67,108,864 67,108,864 0.00shared 1M Buffer 2,098,176 2,098,176 0.00shared Checkpoint queue 2,258,432 2,258,432 0.00shared DML lock 703,948 703,948 0.00shared FileOpenBlock 2,652,852 2,652,852 0.00shared KGK heap 3,756 3,756 0.00shared KGLS heap 2,858,404 2,645,328 -7.45shared KQR M PO 1,144,656 1,215,856 6.22shared KQR S PO 426,620 426,620 0.00shared KQR S SO 4 0 96 4,096 0.00shared KSXR pending messages que 840,636 840,636 0.00shared KSXR receive buffers 1,032,500 1,032,500 0.00shared MTTR advisory 97,308 97,308 0.00shared PL / SQL DIANA 3,029,520 3,106,548 2.54shared PL / SQL MPCODE 733,704 771,304 5.12shared PLS non-lib hp 2,068 2,068 0.00shared Dictionary Cache 1,614,976 1,614,976 0.00shared enqueue 1,030,284 1,030,284 0.00shared enqueue Resources 466,064 466,
064 0.00shared errors 16,188 25,344 56.56shared event statistics per sess 13,473,600 13,473,600 0.00shared fixed allocation callback 268 268 0.00shared free memory 13,758,228 13,487,404 -1.97shared joxs heap init 4,220 4,220 0.00shared ktlbk state objects 564,960 564,960 0.00shared library cache 39,058,020 39,228,204 0.44shared message pool freequeue 833,032 833,032 0.00shared miscellaneous 25,326,788 25,638,828 1.23shared parameters 22,964 26,696 16.25shared pl / sql source 76 76 0.00shared processes 566,400 566,400 0.00shared sessions 2,894,400 2,894,400 0.00shared sql area 149,545,360 149,348,432 -0.13shared table definiti 5,252 5,140 -2.13shared transaction 1,302,356 1,302,356 0.00shared trigger inform 528 528 0.00shared 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 NameCurrent Maximum InitialResource Name Utilization Utilization ALLOCATION LIMIT ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------- ---------- ---------- Max_rollback_segments 0 331 331 331Processes 36 552 600 600 ----------- -------------------------------------------------- Init.ora Parameters for DB: IVPS Instance: IVPS SNAPS: 4 -5
End ValueParameter Name Begin Value (if Different) --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------- Background_dump_dest / home / oracle / admin / ivps / bdumpblank_trimming truecompatible 9.2.0.0 .0control_file_record_keep_time 7control_files / home / oracle / oradata / ivps / controlcore_dump_dest / home / oracle / admin / ivps / cdumpcursor_sharing SIMILARcursor_space_for_time TRUEdb_block_size 8192db_cache_advice 704643072db_file_multiblock_read_count 16db_name ivpsdb_writer_processes 8fast_start_mttr_target 3600global_names TRUEinstance_name ivpsjob_queue_processes 2large_pool_size 67108864log_archive_dest_1 location OFFdb_cache_size =
/ Home / oracle / oradata / ivplog_archive_format arch_% t_% s.arclog_archive_start TRUElog_buffer 5242880log_checkpoint_interval 1800log_checkpoint_timeout 0max_enabled_roles 30open_cursors 300optimizer_features_enable 9.2.0os_authent_prefixpga_aggregate_target 377487360pre_page_sga TRUEprocesses 600query_rewrite_enabled trueremote_login_passwordfile EXCLUSIVEresource_limit TRUEservice_names ivpssession_cached_cursors 10sessions 1200shared_pool_reserved_size 10485760shared_pool_size 218103808sort_area_size 65536timed_statistics TRUEtransactions_per_rollback_seg 4undo_management AUTOundo_retention 3600undo_tablespace UNDOTBSuser_dump_dest / home / oracle / admin / ivps / UDumpworkarea_size_policy Auto --------------------------------------------------------------------------------- ------------ End of Report
