数据库

img llmmysun

STATSPACK report

发表于2004/10/20 11:38:00  1089人阅读

分类: ORACLE Performance Tuning


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

 

阅读全文
0 0

相关文章推荐

img
取 消
img