Home » RDBMS Server » Performance Tuning » statspack report (Oracle 9i)
statspack report [message #337645] Thu, 31 July 2008 08:40 Go to next message
manoj12
Messages: 185
Registered: March 2008
Location: India
Senior Member
Dear sir

Could you please check the statspack report and advise me what I should do to increase the performance of the Oracle Database





STATSPACK report for

DB Name         DB Id    Instance     Inst Num Release     Cluster Host
------------ ----------- ------------ -------- ----------- ------- ------------
cis          936888161 cis               1 9.2.0.4.0   NO      cis12

            Snap Id     Snap Time      Sessions Curs/Sess Comment
            ------- ------------------ -------- --------- -------------------
Begin Snap:     141 31-Jul-08 12:05:42       14      18.5
  End Snap:     143 31-Jul-08 15:13:02       20       8.1
   Elapsed:              187.33 (mins)

Cache Sizes (end)
~~~~~~~~~~~~~~~~~
               Buffer Cache:        24M      Std Block Size:         8K
           Shared Pool Size:       152M          Log Buffer:       512K

Load Profile
~~~~~~~~~~~~                            Per Second       Per Transaction
                                   ---------------       ---------------
                  Redo size:              2,128.70             15,536.75
              Logical reads:              3,020.53             22,045.94
              Block changes:                 13.56                 99.00
             Physical reads:              2,744.76             20,033.21
            Physical writes:                 38.11                278.18
                 User calls:                  3.96                 28.88
                     Parses:                  2.04                 14.86
                Hard parses:                  0.18                  1.33
                      Sorts:                  2.86                 20.88
                     Logons:                  0.01                  0.04
                   Executes:                  2.54                 18.56
               Transactions:                  0.14

  % Blocks changed per Read:    0.45    Recursive Call %:    62.13
 Rollback per transaction %:   85.06       Rows per Sort:  1466.05

Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
            Buffer Nowait %:  100.00       Redo NoWait %:  100.00
            Buffer  Hit   %:   10.43    In-memory Sort %:   99.25
            Library Hit   %:   96.13        Soft Parse %:   91.05
         Execute to Parse %:   19.97         Latch Hit %:  100.00
Parse CPU to Parse Elapsd %:   85.88     % Non-Parse CPU:   99.56

 Shared Pool Statistics        Begin   End
                               ------  ------
             Memory Usage %:   90.08   92.91
    % SQL with executions>1:   67.11   38.29
  % Memory for SQL w/exec>1:   64.72   43.13

Top 5 Timed Events
~~~~~~~~~~~~~~~~~~                                                     % Total
Event                                               Waits    Time (s) Ela Time
-------------------------------------------- ------------ ----------- --------
db file scattered read                          2,587,518       2,022    55.87
CPU time                                                        1,390    38.41
db file sequential read                           966,114         176     4.86
direct path read                                   38,989          23      .63
control file parallel write                         3,651           3      .09
          -------------------------------------------------------------
Wait Events for DB: cis  Instance: cis  Snaps: 141 -143
-> 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
---------------------------- ------------ ---------- ---------- ------ --------
db file scattered read          2,587,518          0      2,022      1  1,680.2
db file sequential read           966,114          0        176      0    627.3
direct path read                   38,989          0         23      1     25.3
control file parallel write         3,651          0          3      1      2.4
control file sequential read        1,233          0          2      1      0.8
log file parallel write            13,408     10,378          2      0      8.7
db file parallel write                546          0          1      2      0.4
SQL*Net break/reset to clien           46          0          0      6      0.0
buffer busy waits                     460          0          0      0      0.3
log file sync                         232          0          0      1      0.2
direct path write                     960          0          0      0      0.6
SQL*Net more data to client         1,033          0          0      0      0.7
latch free                             28         19          0      2      0.0
LGWR wait for redo copy               226          0          0      0      0.1
SQL*Net message from client        45,549          0     19,990    439     29.6
virtual circuit status                375        375     13,969  37252      0.2
wakeup time manager                   354        354     12,244  34588      0.2
SQL*Net more data from clien           52          0          0      5      0.0
SQL*Net message to client          45,555          0          0      0     29.6
          -------------------------------------------------------------
Background Wait Events for DB: cis  Instance: cis  Snaps: 141 -143
-> ordered by wait time desc, waits desc (idle events last)

                                                                   Avg
                                                     Total Wait   wait    Waits
Event                               Waits   Timeouts   Time (s)   (ms)     /txn
---------------------------- ------------ ---------- ---------- ------ --------
control file parallel write         3,651          0          3      1      2.4
log file parallel write            13,408     10,378          2      0      8.7
control file sequential read        1,098          0          2      2      0.7
db file scattered read                418          0          1      3      0.3
db file parallel write                546          0          1      2      0.4
db file sequential read               131          0          0      3      0.1
LGWR wait for redo copy               226          0          0      0      0.1
latch free                              1          0          0      0      0.0
rdbms ipc message                  35,194     21,651     76,207   2165     22.9
pmon timer                          3,786      3,786     14,057   3713      2.5
smon timer                             37         37     13,504 ######      0.0
          -------------------------------------------------------------
SQL ordered by Gets for DB: cis  Instance: cis  Snaps: 141 -143
-> End Buffer Gets Threshold:   10000
-> Note that resources reported for PL/SQL includes the resources used by
   all SQL statements called within the PL/SQL code.  As individual SQL
   statements are also reported, it is possible and valid for the summed
   total % to exceed 100

                                                     CPU      Elapsd
  Buffer Gets    Executions  Gets per Exec  %Total Time (s)  Time (s) Hash Value
--------------- ------------ -------------- ------ -------- --------- ----------
      2,439,170            4      609,792.5    7.2    96.22    207.60 1793571320
Module: w3wp.exe
select to_char(stmtdt, 'yyyy') as ID, to_char(stmtdt, 'yyyy') as
 year from statement where borrid=5832 and user_id=22 and coaid
=4 and to_char(stmtdt, 'yyyy') in (   select os.year from   (
  select borrid, year, count(*) as cntActuals     from subjectiv
e_prm_trans     where borrid=5832 and user_id=22 and mdlid =4 an

      2,436,900            4      609,225.0    7.2    92.36    194.52 1829253582
Module: w3wp.exe
select to_char(stmtdt, 'yyyy') as ID, to_char(stmtdt, 'yyyy') as
 year from statement where borrid=5688 and user_id=1674 and coai
d =4 and to_char(stmtdt, 'yyyy') in (   select os.year from   (
    select borrid, year, count(*) as cntActuals     from subject
ive_prm_trans     where borrid=5688 and user_id=1674 and mdlid =

      2,414,944            4      603,736.0    7.1   105.19    211.56 4118714993
Module: w3wp.exe
select to_char(stmtdt, 'yyyy') as ID, to_char(stmtdt, 'yyyy') as
 year from statement where borrid=3153 and user_id=22 and coaid
=1 and to_char(stmtdt, 'yyyy') in (   select os.year from   (
  select borrid, year, count(*) as cntActuals     from subjectiv
e_prm_trans     where borrid=3153 and user_id=22 and mdlid =1 an

      1,830,996            3      610,332.0    5.4    74.20    193.04 1481346004
Module: w3wp.exe
select to_char(stmtdt, 'yyyy') as ID, to_char(stmtdt, 'yyyy') as
 year from statement where borrid=5835 and user_id=210 and coaid
 =4 and to_char(stmtdt, 'yyyy') in (   select os.year from   (
   select borrid, year, count(*) as cntActuals     from subjecti
ve_prm_trans     where borrid=5835 and user_id=210 and mdlid =4

      1,471,177            5      294,235.4    4.3    32.89     97.98 2513593745
Module: w3wp.exe
select rownum as sr_no,t.*   from  (  select pfc.base_entity_id
as borr_id,  pfc.pw_id,  pfc.propfacname,  pfc.id as fac_id,  pf
c.parent_facility_id as parent_facility_id,  pfc.security_name,
 pfc.attached_value,  pfc.proposed_sanction_limit as proposed_sa
nction_limit  from rvw_cam_report_info pfc   where pfc.base_enti

      1,343,681            9      149,297.9    4.0   117.11    280.70 3364412532
Module: w3wp.exe
begin sp_riskscoregen_ybl_sme(:V00001,:V00002,:V00003,:V00004,:V
00005,:V00006,:V00007); end;

      1,255,281            5      251,056.2    3.7    53.59    151.23 2592635488
Module: w3wp.exe
begin RiskScoreGen_YBL_LC(:V00001,:V00002,:V00003,:V00004,:V0000
5,:V00006,:V00007); end;

SQL ordered by Gets for DB: cis  Instance: cis  Snaps: 141 -143
-> 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
--------------- ------------ -------------- ------ -------- --------- ----------
      1,220,630            2      610,315.0    3.6    45.61     97.52  306547636
Module: w3wp.exe
select to_char(stmtdt, 'yyyy') as ID, to_char(stmtdt, 'yyyy') as
 year from statement where borrid=5688 and user_id=24 and coaid
=4 and to_char(stmtdt, 'yyyy') in (   select os.year from   (
  select borrid, year, count(*) as cntActuals     from subjectiv
e_prm_trans     where borrid=5688 and user_id=24 and mdlid =4 an

      1,220,630            2      610,315.0    3.6    47.08     97.59  742978199
Module: w3wp.exe
select to_char(stmtdt, 'yyyy') as ID, to_char(stmtdt, 'yyyy') as
 year from statement where borrid=5835 and user_id=24 and coaid
=4 and to_char(stmtdt, 'yyyy') in (   select os.year from   (
  select borrid, year, count(*) as cntActuals     from subjectiv
e_prm_trans     where borrid=5835 and user_id=24 and mdlid =4 an

      1,220,630            2      610,315.0    3.6    47.47     97.51 4035011226
Module: w3wp.exe
select to_char(stmtdt, 'yyyy') as ID, to_char(stmtdt, 'yyyy') as
 year from statement where borrid=5835 and user_id=22 and coaid
=4 and to_char(stmtdt, 'yyyy') in (   select os.year from   (
  select borrid, year, count(*) as cntActuals     from subjectiv
e_prm_trans     where borrid=5835 and user_id=22 and mdlid =4 an

      1,219,540            2      609,770.0    3.6    40.70     90.90  781694832
Module: w3wp.exe
select to_char(stmtdt, 'yyyy') as ID, to_char(stmtdt, 'yyyy') as
 year from statement where borrid=5688 and user_id=22 and coaid
=4 and to_char(stmtdt, 'yyyy') in (   select os.year from   (
  select borrid, year, count(*) as cntActuals     from subjectiv
e_prm_trans     where borrid=5688 and user_id=22 and mdlid =4 an

      1,208,436            2      604,218.0    3.6    45.14    109.06 3173269226
Module: w3wp.exe
select to_char(stmtdt, 'yyyy') as ID, to_char(stmtdt, 'yyyy') as
 year from statement where borrid=3317 and user_id=132 and coaid
 =1 and to_char(stmtdt, 'yyyy') in (   select os.year from   (
   select borrid, year, count(*) as cntActuals     from subjecti
ve_prm_trans     where borrid=3317 and user_id=132 and mdlid =1

      1,208,356            2      604,178.0    3.6    43.13     92.92 2962969062
Module: w3wp.exe
select to_char(stmtdt, 'yyyy') as ID, to_char(stmtdt, 'yyyy') as
 year from statement where borrid=5834 and user_id=1760 and coai
d =1 and to_char(stmtdt, 'yyyy') in (   select os.year from   (
    select borrid, year, count(*) as cntActuals     from subject
ive_prm_trans     where borrid=5834 and user_id=1760 and mdlid =

      1,208,356            2      604,178.0    3.6    44.56     94.42 3618587409
Module: w3wp.exe
SQL ordered by Gets for DB: cis  Instance: cis  Snaps: 141 -143
-> 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 to_char(stmtdt, 'yyyy') as ID, to_char(stmtdt, 'yyyy') as
 year from statement where borrid=2579 and user_id=132 and coaid
 =2 and to_char(stmtdt, 'yyyy') in (   select os.year from   (
   select borrid, year, count(*) as cntActuals     from subjecti
ve_prm_trans     where borrid=2579 and user_id=132 and mdlid =2

          -------------------------------------------------------------
SQL ordered by Reads for DB: cis  Instance: cis  Snaps: 141 -143
-> End Disk Reads Threshold:    1000

                                                     CPU      Elapsd
 Physical Reads  Executions  Reads per Exec %Total Time (s)  Time (s) Hash Value
--------------- ------------ -------------- ------ -------- --------- ----------
      2,376,891            4      594,222.8    7.7    96.22    207.60 1793571320
Module: w3wp.exe
select to_char(stmtdt, 'yyyy') as ID, to_char(stmtdt, 'yyyy') as
 year from statement where borrid=5832 and user_id=22 and coaid
=4 and to_char(stmtdt, 'yyyy') in (   select os.year from   (
  select borrid, year, count(*) as cntActuals     from subjectiv
e_prm_trans     where borrid=5832 and user_id=22 and mdlid =4 an

      2,348,948            4      587,237.0    7.6   105.19    211.56 4118714993
Module: w3wp.exe
select to_char(stmtdt, 'yyyy') as ID, to_char(stmtdt, 'yyyy') as
 year from statement where borrid=3153 and user_id=22 and coaid
=1 and to_char(stmtdt, 'yyyy') in (   select os.year from   (
  select borrid, year, count(*) as cntActuals     from subjectiv
e_prm_trans     where borrid=3153 and user_id=22 and mdlid =1 an

      2,342,030            4      585,507.5    7.6    92.36    194.52 1829253582
Module: w3wp.exe
select to_char(stmtdt, 'yyyy') as ID, to_char(stmtdt, 'yyyy') as
 year from statement where borrid=5688 and user_id=1674 and coai
d =4 and to_char(stmtdt, 'yyyy') in (   select os.year from   (
    select borrid, year, count(*) as cntActuals     from subject
ive_prm_trans     where borrid=5688 and user_id=1674 and mdlid =

      1,783,950            3      594,650.0    5.8    74.20    193.04 1481346004
Module: w3wp.exe
select to_char(stmtdt, 'yyyy') as ID, to_char(stmtdt, 'yyyy') as
 year from statement where borrid=5835 and user_id=210 and coaid
 =4 and to_char(stmtdt, 'yyyy') in (   select os.year from   (
   select borrid, year, count(*) as cntActuals     from subjecti
ve_prm_trans     where borrid=5835 and user_id=210 and mdlid =4

      1,494,362            9      166,040.2    4.8   117.11    280.70 3364412532
Module: w3wp.exe
begin sp_riskscoregen_ybl_sme(:V00001,:V00002,:V00003,:V00004,:V
00005,:V00006,:V00007); end;

      1,212,004            5      242,400.8    3.9    53.59    151.23 2592635488
Module: w3wp.exe
begin RiskScoreGen_YBL_LC(:V00001,:V00002,:V00003,:V00004,:V0000
5,:V00006,:V00007); end;

      1,203,629            2      601,814.5    3.9    47.08     97.59  742978199
Module: w3wp.exe
select to_char(stmtdt, 'yyyy') as ID, to_char(stmtdt, 'yyyy') as
 year from statement where borrid=5835 and user_id=24 and coaid
=4 and to_char(stmtdt, 'yyyy') in (   select os.year from   (
  select borrid, year, count(*) as cntActuals     from subjectiv
e_prm_trans     where borrid=5835 and user_id=24 and mdlid =4 an

      1,203,321            2      601,660.5    3.9    40.70     90.90  781694832
Module: w3wp.exe
select to_char(stmtdt, 'yyyy') as ID, to_char(stmtdt, 'yyyy') as
 year from statement where borrid=5688 and user_id=22 and coaid
SQL ordered by Reads for DB: cis  Instance: cis  Snaps: 141 -143
-> End Disk Reads Threshold:    1000

                                                     CPU      Elapsd
 Physical Reads  Executions  Reads per Exec %Total Time (s)  Time (s) Hash Value
--------------- ------------ -------------- ------ -------- --------- ----------
=4 and to_char(stmtdt, 'yyyy') in (   select os.year from   (
  select borrid, year, count(*) as cntActuals     from subjectiv
e_prm_trans     where borrid=5688 and user_id=22 and mdlid =4 an

      1,203,317            2      601,658.5    3.9    47.47     97.51 4035011226
Module: w3wp.exe
select to_char(stmtdt, 'yyyy') as ID, to_char(stmtdt, 'yyyy') as
 year from statement where borrid=5835 and user_id=22 and coaid
=4 and to_char(stmtdt, 'yyyy') in (   select os.year from   (
  select borrid, year, count(*) as cntActuals     from subjectiv
e_prm_trans     where borrid=5835 and user_id=22 and mdlid =4 an

      1,203,241            2      601,620.5    3.9    45.61     97.52  306547636
Module: w3wp.exe
select to_char(stmtdt, 'yyyy') as ID, to_char(stmtdt, 'yyyy') as
 year from statement where borrid=5688 and user_id=24 and coaid
=4 and to_char(stmtdt, 'yyyy') in (   select os.year from   (
  select borrid, year, count(*) as cntActuals     from subjectiv
e_prm_trans     where borrid=5688 and user_id=24 and mdlid =4 an

      1,183,315            2      591,657.5    3.8    45.14    109.06 3173269226
Module: w3wp.exe
select to_char(stmtdt, 'yyyy') as ID, to_char(stmtdt, 'yyyy') as
 year from statement where borrid=3317 and user_id=132 and coaid
 =1 and to_char(stmtdt, 'yyyy') in (   select os.year from   (
   select borrid, year, count(*) as cntActuals     from subjecti
ve_prm_trans     where borrid=3317 and user_id=132 and mdlid =1

      1,182,708            2      591,354.0    3.8    44.56     94.42 3618587409
Module: w3wp.exe
select to_char(stmtdt, 'yyyy') as ID, to_char(stmtdt, 'yyyy') as
 year from statement where borrid=2579 and user_id=132 and coaid
 =2 and to_char(stmtdt, 'yyyy') in (   select os.year from   (
   select borrid, year, count(*) as cntActuals     from subjecti
ve_prm_trans     where borrid=2579 and user_id=132 and mdlid =2

      1,181,987            2      590,993.5    3.8    43.13     92.92 2962969062
Module: w3wp.exe
select to_char(stmtdt, 'yyyy') as ID, to_char(stmtdt, 'yyyy') as
 year from statement where borrid=5834 and user_id=1760 and coai
d =1 and to_char(stmtdt, 'yyyy') in (   select os.year from   (
    select borrid, year, count(*) as cntActuals     from subject
ive_prm_trans     where borrid=5834 and user_id=1760 and mdlid =

      1,095,808            2      547,904.0    3.6    46.94    114.33 1420449757
Module: w3wp.exe
select to_char(stmtdt, 'yyyy') as ID, to_char(stmtdt, 'yyyy') as
 year from statement where borrid=3153 and user_id=359 and coaid
 =1 and to_char(stmtdt, 'yyyy') in (   select os.year from   (
   select borrid, year, count(*) as cntActuals     from subjecti
ve_prm_trans     where borrid=3153 and user_id=359 and mdlid =1

          -------------------------------------------------------------
SQL ordered by Executions for DB: cis  Instance: cis  Snaps: 141 -143
-> End Executions Threshold:     100

                                                CPU per    Elap per
 Executions   Rows Processed   Rows per Exec    Exec (s)   Exec (s)  Hash Value
------------ --------------- ---------------- ----------- ---------- ----------
       7,788           7,788              1.0       0.00        0.00 3371479671
select t.name,  (select owner_instance from sys.aq$_queue_table_
affinities   where table_objno = t.objno)  from system.aq$_queue
_tables t where t.name = :1 and t.schema = :2 for update skip lo
cked

       2,193               0              0.0       0.00        0.00 1316169839
select job, nvl2(last_date, 1, 0) from sys.job$ where (((:1 <= n
ext_date) and (next_date < :2))    or  ((last_date is null) and
(next_date < :3))) and (field1 = :4 or (field1 = 0 and 'Y' = :5)
) and (this_date is null) order by next_date, job

       2,193           2,193              1.0       0.00        0.00 1693927332
select count(*) from sys.job$ where (next_date > sysdate) and (n
ext_date < (sysdate+5/86400))

       1,299               0              0.0       0.00        0.00 1064601204
Module: w3wp.exe
alter session set isolation_level = read committed

         965             965              1.0       0.00        0.00 3594945689
Module: w3wp.exe
SELECT count(id) from ulogins where id = :b1

         965             965              1.0       0.00        0.00 3656262341
Module: w3wp.exe
begin sp_loginIsValid(:V00001,:V00002); end;

         424             491              1.2       0.00        0.00 1254950678
select file# from file$ where ts#=:1

         354               0              0.0       0.00        0.00  633914867
select q_name, state, delay, expiration, rowid, msgid,    dequeu
e_msgid, chain_no, local_order_no, enq_time, enq_tid, step_no,
 priority, exception_qschema, exception_queue, retry_count, corr
id,     time_manager_info   from SYS.AQ_EVENT_TABLE   where time
_manager_info <= :1 and state != :2   for update skip locked

         354           7,788             22.0       0.00        0.01  815501214
select t.schema, t.name, t.flags, q.name from system.aq$_queue_t
ables t, sys.aq$_queue_table_affinities aft,      system.aq$_que
ues q where aft.table_objno = t.objno and aft.owner_instance = :
1 and        q.table_objno = t.objno and q.usage = 0 and       b
itand(t.flags, 4+16+32+64+128+256) = 0 for update of t.name, aft

         354               0              0.0       0.00        0.00  870116171
select q_name, state, delay, expiration, rowid, msgid,    dequeu
e_msgid, chain_no, local_order_no, enq_time, enq_tid, step_no,
 priority, exception_qschema, exception_queue, retry_count, corr
id,     time_manager_info   from SYSTEM.DEF$_AQERROR   where tim
e_manager_info <= :1 and state != :2   for update skip locked

         354               0              0.0       0.00        0.00  994140048
select q_name, state, delay, expiration, rowid, msgid,   dequeue
SQL ordered by Executions for DB: cis  Instance: cis  Snaps: 141 -143
-> End Executions Threshold:     100

                                                CPU per    Elap per
 Executions   Rows Processed   Rows per Exec    Exec (s)   Exec (s)  Hash Value
------------ --------------- ---------------- ----------- ---------- ----------
_msgid, chain_no, local_order_no, enq_time, enq_tid, step_no,
priority, exception_qschema, exception_queue, retry_count, corri
d,   time_manager_info, sender_name, sender_address, sender_prot
ocol   from QS_CBADM.QS_CBADM_ORDERS_SQTAB   where time_manager_

         354               0              0.0       0.00        0.00 1153132087
select q_name, state, delay, expiration, rowid, msgid,    dequeu
e_msgid, chain_no, local_order_no, enq_time, enq_tid, step_no,
 priority, exception_qschema, exception_queue, retry_count, corr
id,     time_manager_info   from SYSTEM.DEF$_AQCALL   where time
_manager_info <= :1 and state != :2   for update skip locked

         354               0              0.0       0.00        0.00 1824912791
select q_name, state, delay, expiration, rowid, msgid,   dequeue
_msgid, chain_no, local_order_no, enq_time, enq_tid, step_no,
priority, exception_qschema, exception_queue, retry_count, corri
d,   time_manager_info, sender_name, sender_address, sender_prot
ocol   from ODM.DMS_QUEUE_TABLE   where time_manager_info <= :1

         354               0              0.0       0.00        0.00 3637162254
select q_name, state, delay, expiration, rowid, msgid,  dequeue_
msgid, chain_no, local_order_no, enq_time, enq_tid, step_no,  pr
iority, exception_qschema, exception_queue, retry_count, corrid,
  time_manager_info, sender_name, sender_address, sender_protoco
l  from QS.QS_ORDERS_SQTAB  where time_manager_info <= :1 and st

         354               0              0.0       0.00        0.00 3920324236
select q_name, state, delay, expiration, rowid, msgid,   dequeue
_msgid, chain_no, local_order_no, enq_time, enq_tid, step_no,
priority, exception_qschema, exception_queue, retry_count, corri
d,   time_manager_info, sender_name, sender_address, sender_prot
ocol   from SYS.AQ_SRVNTFN_TABLE   where time_manager_info <= :1

         239             239              1.0       0.00        0.00 3716207873
update seq$ set increment$=:2,minvalue=:3,maxvalue=:4,cycle#=:5,
order$=:6,cache=:7,highwater=:8,audit$=:9,flags=:10 where obj#=:
1

         182             182              1.0       0.00        0.00 3468666020
select text from view$ where rowid=:1

         150               0              0.0       0.00        0.00 4274598960
select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt, tim
estamp#, sample_size, minimum, maximum, distcnt, lowval, hival,
density, col#, spare1, spare2, avgcln from hist_head$ where obj#
=:1 and intcol#=:2

         136              75              0.6       0.00        0.00 2703824309
select obj#,type#,ctime,mtime,stime,status,dataobj#,flags,oid$,
spare1, spare2 from obj$ where owner#=:1 and name=:2 and namespa

          -------------------------------------------------------------
SQL ordered by Parse Calls for DB: cis  Instance: cis  Snaps: 141 -143
-> End Parse Calls Threshold:      1000

                           % Total
 Parse Calls  Executions   Parses  Hash Value
------------ ------------ -------- ----------
       1,930          965     8.44 3656262341
Module: w3wp.exe
begin sp_loginIsValid(:V00001,:V00002); end;

       1,299        1,299     5.68 1064601204
Module: w3wp.exe
alter session set isolation_level = read committed

         424          424     1.85 1254950678
select file# from file$ where ts#=:1

         354            0     1.55  163922476
select q_name, state, delay, expiration, rowid, msgid,   dequeue
_msgid, chain_no, local_order_no, enq_time, enq_tid, step_no,
priority, exception_qschema, exception_queue, retry_count, corri
d,   time_manager_info, sender_name, sender_address, sender_prot
ocol   from QS.QS_ORDERS_PR_MQTAB   where msgid = :1

         354            0     1.55  378803933
select q_name, state, delay, expiration, rowid, msgid,   dequeue
_msgid, chain_no, local_order_no, enq_time, enq_tid, step_no,
priority, exception_qschema, exception_queue, retry_count, corri
d,   time_manager_info, sender_name, sender_address, sender_prot
ocol   from QS_ES.QS_ES_ORDERS_MQTAB   where msgid = :1

         354            0     1.55  613578973
select q_name, state, delay, expiration, rowid, msgid,   dequeue
_msgid, chain_no, local_order_no, enq_time, enq_tid, step_no,
priority, exception_qschema, exception_queue, retry_count, corri
d,   time_manager_info, sender_name, sender_address, sender_prot
ocol   from QS_ES.QS_ES_ORDERS_PR_MQTAB   where msgid = :1

         354          354     1.55  633914867
select q_name, state, delay, expiration, rowid, msgid,    dequeu
e_msgid, chain_no, local_order_no, enq_time, enq_tid, step_no,
 priority, exception_qschema, exception_queue, retry_count, corr
id,     time_manager_info   from SYS.AQ_EVENT_TABLE   where time
_manager_info <= :1 and state != :2   for update skip locked

         354          354     1.55  815501214
select t.schema, t.name, t.flags, q.name from system.aq$_queue_t
ables t, sys.aq$_queue_table_affinities aft,      system.aq$_que
ues q where aft.table_objno = t.objno and aft.owner_instance = :
1 and        q.table_objno = t.objno and q.usage = 0 and       b
itand(t.flags, 4+16+32+64+128+256) = 0 for update of t.name, aft

         354          354     1.55  870116171
select q_name, state, delay, expiration, rowid, msgid,    dequeu
e_msgid, chain_no, local_order_no, enq_time, enq_tid, step_no,
 priority, exception_qschema, exception_queue, retry_count, corr
id,     time_manager_info   from SYSTEM.DEF$_AQERROR   where tim
e_manager_info <= :1 and state != :2   for update skip locked

         354          354     1.55  994140048
SQL ordered by Parse Calls for DB: cis  Instance: cis  Snaps: 141 -143
-> End Parse Calls Threshold:      1000

                           % Total
 Parse Calls  Executions   Parses  Hash Value
------------ ------------ -------- ----------
select q_name, state, delay, expiration, rowid, msgid,   dequeue
_msgid, chain_no, local_order_no, enq_time, enq_tid, step_no,
priority, exception_qschema, exception_queue, retry_count, corri
d,   time_manager_info, sender_name, sender_address, sender_prot
ocol   from QS_CBADM.QS_CBADM_ORDERS_SQTAB   where time_manager_

         354          354     1.55 1153132087
select q_name, state, delay, expiration, rowid, msgid,    dequeu
e_msgid, chain_no, local_order_no, enq_time, enq_tid, step_no,
 priority, exception_qschema, exception_queue, retry_count, corr
id,     time_manager_info   from SYSTEM.DEF$_AQCALL   where time
_manager_info <= :1 and state != :2   for update skip locked

         354          354     1.55 1824912791
select q_name, state, delay, expiration, rowid, msgid,   dequeue
_msgid, chain_no, local_order_no, enq_time, enq_tid, step_no,
priority, exception_qschema, exception_queue, retry_count, corri
d,   time_manager_info, sender_name, sender_address, sender_prot
ocol   from ODM.DMS_QUEUE_TABLE   where time_manager_info <= :1

         354            0     1.55 1937207903
select q_name, state, delay, expiration, rowid, msgid,   dequeue
_msgid, chain_no, local_order_no, enq_time, enq_tid, step_no,
priority, exception_qschema, exception_queue, retry_count, corri
d,   time_manager_info, sender_name, sender_address, sender_prot
ocol   from QS_OS.QS_OS_ORDERS_PR_MQTAB   where msgid = :1

         354            0     1.55 2540375272
select q_name, state, delay, expiration, rowid, msgid,   dequeue
_msgid, chain_no, local_order_no, enq_time, enq_tid, step_no,
priority, exception_qschema, exception_queue, retry_count, corri
d,   time_manager_info, sender_name, sender_address, sender_prot
ocol   from QS_OS.QS_OS_ORDERS_MQTAB   where msgid = :1

         354            0     1.55 2833257892
select q_name, state, delay, expiration, rowid, msgid,   dequeue
_msgid, chain_no, local_order_no, enq_time, enq_tid, step_no,
priority, exception_qschema, exception_queue, retry_count, corri
d,   time_manager_info, sender_name, sender_address, sender_prot
ocol   from QS_CBADM.QS_CBADM_ORDERS_MQTAB   where msgid = :1

         354            0     1.55 3137402284
select q_name, state, delay, expiration, rowid, msgid,   dequeue

          -------------------------------------------------------------
Instance Activity Stats for DB: cis  Instance: cis  Snaps: 141 -143

Statistic                                      Total     per Second    per Trans
--------------------------------- ------------------ -------------- ------------
CPU used by this session                     138,986           12.4         90.3
CPU used when call started                   138,986           12.4         90.3
CR blocks created                                735            0.1          0.5
Cached Commit SCN referenced                       0            0.0          0.0
Commit SCN cached                                  0            0.0          0.0
DBWR buffers scanned                          18,165            1.6         11.8
DBWR checkpoint buffers written                1,247            0.1          0.8
DBWR checkpoints                                   0            0.0          0.0
DBWR free buffers found                       16,257            1.5         10.6
DBWR lru scans                                    53            0.0          0.0
DBWR make free requests                           58            0.0          0.0
DBWR revisited being-written buff                  0            0.0          0.0
DBWR summed scan depth                        18,165            1.6         11.8
DBWR transaction table writes                     60            0.0          0.0
DBWR undo block writes                         1,134            0.1          0.7
SQL*Net roundtrips to/from client             45,382            4.0         29.5
active txn count during cleanout                 552            0.1          0.4
background checkpoints completed                   0            0.0          0.0
background checkpoints started                     0            0.0          0.0
background timeouts                           13,380            1.2          8.7
branch node splits                                 0            0.0          0.0
buffer is not pinned count                33,274,338        2,960.4     21,606.7
buffer is pinned count                       369,925           32.9        240.2
bytes received via SQL*Net from c          4,749,266          422.5      3,083.9
bytes sent via SQL*Net to client           8,711,636          775.1      5,656.9
calls to get snapshot scn: kcmgss            418,195           37.2        271.6
calls to kcmgas                               33,294            3.0         21.6
calls to kcmgcs                                  453            0.0          0.3
change write time                                 22            0.0          0.0
cleanout - number of ktugct calls                986            0.1          0.6
cleanouts and rollbacks - consist                 19            0.0          0.0
cleanouts only - consistent read                 392            0.0          0.3
cluster key scan block gets                   51,256            4.6         33.3
cluster key scans                             39,894            3.6         25.9
commit cleanout failures: block l                 92            0.0          0.1
commit cleanout failures: buffer                   0            0.0          0.0
commit cleanout failures: callbac                  2            0.0          0.0
commit cleanout failures: cannot                   0            0.0          0.0
commit cleanouts                              26,028            2.3         16.9
commit cleanouts successfully com             25,934            2.3         16.8
commit txn count during cleanout                 604            0.1          0.4
consistent changes                            18,988            1.7         12.3
consistent gets                           33,841,234        3,010.8     21,974.8
consistent gets - examination                248,330           22.1        161.3
current blocks converted for CR                    0            0.0          0.0
cursor authentications                         1,720            0.2          1.1
data blocks consistent reads - un             18,988            1.7         12.3
db block changes                             152,464           13.6         99.0
db block gets                                109,507            9.7         71.1
deferred (CURRENT) block cleanout             16,091            1.4         10.5
dirty buffers inspected                          200            0.0          0.1
enqueue conversions                            3,705            0.3          2.4
enqueue deadlocks                                  0            0.0          0.0
enqueue releases                              60,963            5.4         39.6
enqueue requests                              60,963            5.4         39.6
enqueue timeouts                                   0            0.0          0.0
Instance Activity Stats for DB: cis  Instance: cis  Snaps: 141 -143

Statistic                                      Total     per Second    per Trans
--------------------------------- ------------------ -------------- ------------
enqueue waits                                      0            0.0          0.0
execute count                                 28,585            2.5         18.6
free buffer inspected                          1,051            0.1          0.7
free buffer requested                     30,420,802        2,706.5     19,753.8
hot buffers moved to head of LRU               2,669            0.2          1.7
immediate (CR) block cleanout app                411            0.0          0.3
immediate (CURRENT) block cleanou                376            0.0          0.2
index fast full scans (full)                       2            0.0          0.0
index fetch by key                           182,667           16.3        118.6
index scans kdiixs1                          102,262            9.1         66.4
leaf node 90-10 splits                            19            0.0          0.0
leaf node splits                                 158            0.0          0.1
logons cumulative                                 58            0.0          0.0
messages received                             13,937            1.2          9.1
messages sent                                 13,937            1.2          9.1
no buffer to keep pinned count                     0            0.0          0.0
no work - consistent read gets            33,169,444        2,951.0     21,538.6
opened cursors cumulative                     17,267            1.5         11.2
parse count (failures)                             4            0.0          0.0
parse count (hard)                             2,048            0.2          1.3
parse count (total)                           22,878            2.0         14.9
parse time cpu                                   608            0.1          0.4
parse time elapsed                               708            0.1          0.5
physical reads                            30,851,138        2,744.8     20,033.2
physical reads direct                        441,629           39.3        286.8
physical reads direct (lob)                        0            0.0          0.0
physical writes                              428,393           38.1        278.2
physical writes direct                       425,046           37.8        276.0
physical writes non checkpoint               428,003           38.1        277.9
pinned buffers inspected                         674            0.1          0.4
prefetched blocks                         26,855,964        2,389.3     17,438.9
prefetched blocks aged out before                  1            0.0          0.0
process last non-idle time            69,397,054,344    6,174,115.2 ############
recovery array read time                           0            0.0          0.0
recovery array reads                               0            0.0          0.0
recovery blocks read                               0            0.0          0.0
recursive calls                               72,976            6.5         47.4
recursive cpu usage                           27,233            2.4         17.7
redo blocks written                           52,072            4.6         33.8
redo buffer allocation retries                     0            0.0          0.0
redo entries                                  77,168            6.9         50.1
redo log space requests                            0            0.0          0.0
redo log space wait time                           0            0.0          0.0
redo ordering marks                                1            0.0          0.0
redo size                                 23,926,592        2,128.7     15,536.8
redo synch time                                    6            0.0          0.0
redo synch writes                                238            0.0          0.2
redo wastage                               1,943,008          172.9      1,261.7
redo write time                                   48            0.0          0.0
redo writer latching time                          3            0.0          0.0
redo writes                                   13,408            1.2          8.7
rollback changes - undo records a                  9            0.0          0.0
rollbacks only - consistent read               2,132            0.2          1.4
rows fetched via callback                     64,373            5.7         41.8
session connect time                  69,397,054,344    6,174,115.2 ############
session logical reads                     33,950,741        3,020.5     22,045.9
Instance Activity Stats for DB: cis  Instance: cis  Snaps: 141 -143

Statistic                                      Total     per Second    per Trans
--------------------------------- ------------------ -------------- ------------
session pga memory                         1,406,784          125.2        913.5
session pga memory max                     9,524,520          847.4      6,184.8
session uga memory                           838,664           74.6        544.6
session uga memory max                    68,307,752        6,077.2     44,355.7
shared hash latch upgrades - no w            103,097            9.2         67.0
shared hash latch upgrades - wait                  0            0.0          0.0
sorts (disk)                                     240            0.0          0.2
sorts (memory)                                31,912            2.8         20.7
sorts (rows)                              47,136,330        4,193.6     30,608.0
summed dirty queue length                          8            0.0          0.0
switch current to new buffer                   8,734            0.8          5.7
table fetch by rowid                         157,886           14.1        102.5
table fetch continued row                          5            0.0          0.0
table scan blocks gotten                  33,016,263        2,937.4     21,439.1
table scan rows gotten                 2,725,313,677      242,465.6  1,769,684.2
table scans (direct read)                          0            0.0          0.0
table scans (long tables)                     63,323            5.6         41.1
table scans (rowid ranges)                         0            0.0          0.0
table scans (short tables)                    38,654            3.4         25.1
transaction rollbacks                              3            0.0          0.0
user calls                                    44,473            4.0         28.9
user commits                                     230            0.0          0.2
user rollbacks                                 1,310            0.1          0.9
workarea executions - multipass                  154            0.0          0.1
workarea executions - onepass                    318            0.0          0.2
workarea executions - optimal                 40,190            3.6         26.1
write clones created in backgroun                  0            0.0          0.0
write clones created in foregroun                  0            0.0          0.0
          -------------------------------------------------------------
Tablespace IO Stats for DB: cis  Instance: cis  Snaps: 141 -143
->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)
-------------- ------- ------ ------- ------------ -------- ---------- ------
IRS2007_YBL_USER
     3,540,495     315    0.6     8.6        1,016        0        460    0.3
TEMP
        82,618       7    1.0     5.3       68,022        6          0    0.0
SYSTEM
         7,713       1    1.8     4.2           52        0          0    0.0
PERFSTAT
         1,527       0    5.4     1.1        1,083        0          0    0.0
XDB
         1,482       0    5.0     1.0            0        0          0    0.0
UNDOTBS1
             4       0    0.0     1.0        1,196        0          0    0.0
IRS2007_YBL_R_USER
           838       0    1.5     1.0            0        0          0    0.0
EXAMPLE
           750       0    2.9     1.0            0        0          0    0.0
DRSYS
           310       0    2.8     1.0            0        0          0    0.0
CWMLITE
           300       0    2.0     1.0            0        0          0    0.0
TOOLS
           194       0    3.2     1.0            0        0          0    0.0
ODM
           190       0    2.5     1.0            0        0          0    0.0
cis_YBL_USER
           136       0    3.3     1.0            0        0          0    0.0
INTCDR_YBL_USER
            20       0    4.5     1.0            0        0          0    0.0
cis_YBL_TEMP
             2       0    0.0     1.0            0        0          0    0.0
INDX
             2       0    5.0     1.0            0        0          0    0.0
INTCDR_YBL_TEMP
             2       0   10.0     1.0            0        0          0    0.0
IRS2007_YBL_R_TEMP
             2       0    5.0     1.0            0        0          0    0.0
IRS2007_YBL_TEMP
             2       0    5.0     1.0            0        0          0    0.0
USERS
             2       0    5.0     1.0            0        0          0    0.0
          -------------------------------------------------------------
File IO Stats for DB: cis  Instance: cis  Snaps: 141 -143
->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)
-------------- ------- ------ ------- ------------ -------- ---------- ------
CWMLITE                  C:\ORACLE\ORADATA\cis\CWMLITE01.DBF
           300       0    2.0     1.0            0        0          0

DRSYS                    C:\ORACLE\ORADATA\cis\DRSYS01.DBF
           310       0    2.8     1.0            0        0          0

EXAMPLE                  C:\ORACLE\ORADATA\cis\EXAMPLE01.DBF
           750       0    2.9     1.0            0        0          0

cis_YBL_TEMP           C:\ORACLE\ORADATA\TABLESPACE\cis_YBL_TEMP.ORA
             2       0    0.0     1.0            0        0          0

cis_YBL_USER           C:\ORACLE\ORADATA\TABLESPACE\cis_YBL_USER.ORA
           136       0    3.3     1.0            0        0          0

INDX                     C:\ORACLE\ORADATA\cis\INDX01.DBF
             2       0    5.0     1.0            0        0          0

INTCDR_YBL_TEMP          C:\ORACLE\ORADATA\TABLESPACE\INTCDR_YBL_TEMP.ORA
             2       0   10.0     1.0            0        0          0

INTCDR_YBL_USER          C:\ORACLE\ORADATA\TABLESPACE\INTCDR_YBL_USER.ORA
            18       0    3.9     1.0            0        0          0
                         D:\ORACLE\DATA\INTCDR_YBL_USER1.DBF
             2       0   10.0     1.0            0        0          0

IRS2007_YBL_R_TEMP       C:\ORACLE\ORADATA\TABLESPACE\IRS2007_YBL_R_TEMP.ORA
             2       0    5.0     1.0            0        0          0

IRS2007_YBL_R_USER       C:\ORACLE\ORADATA\TABLESPACE\IRS2007_YBL_R_USER.ORA
           838       0    1.5     1.0            0        0          0

IRS2007_YBL_TEMP         C:\ORACLE\ORADATA\TABLESPACE\IRS2007_YBL_TEMP.ORA
             2       0    5.0     1.0            0        0          0

IRS2007_YBL_USER         C:\ORACLE\ORADATA\TABLESPACE\IRS2007_YBL_USER.ORA
     1,724,891     153    0.5     7.0          370        0        199    0.4
                         D:\ORACLE\DATA\IRS2007_YBL_USER1.DBF
     1,815,604     162    0.7    10.1          646        0        261    0.3

ODM                      C:\ORACLE\ORADATA\cis\ODM01.DBF
           190       0    2.5     1.0            0        0          0

PERFSTAT                 C:\ORACLE\ORADATA\TABLESPACE\PERFSTAT01.DBF
         1,527       0    5.4     1.1        1,083        0          0

SYSTEM                   C:\ORACLE\ORADATA\cis\SYSTEM01.DBF
         7,675       1    1.8     4.2           52        0          0
                         C:\ORACLE\ORADATA\cis\SYSTEM02.DBF
            38       0    3.2     2.2            0        0          0

TEMP                     C:\ORACLE\ORADATA\cis\TEMP01.DBF
File IO Stats for DB: cis  Instance: cis  Snaps: 141 -143
->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)
-------------- ------- ------ ------- ------------ -------- ---------- ------
        82,618       7    1.0     5.3       68,022        6          0

TOOLS                    C:\ORACLE\ORADATA\cis\TOOLS01.DBF
           194       0    3.2     1.0            0        0          0

UNDOTBS1                 C:\ORACLE\ORADATA\cis\UNDOTBS01.DBF
             4       0    0.0     1.0        1,196        0          0

USERS                    C:\ORACLE\ORADATA\cis\USERS01.DBF
             2       0    5.0     1.0            0        0          0

XDB                      C:\ORACLE\ORADATA\cis\XDB01.DBF
         1,482       0    5.0     1.0            0        0          0

          -------------------------------------------------------------
Buffer Pool Statistics for DB: cis  Instance: cis  Snaps: 141 -143
-> 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        3,003  10.4  33,949,304  30,414,220      3,347       0        0     460
          -------------------------------------------------------------

Instance Recovery Stats for DB: cis  Instance: cis  Snaps: 141 -143
-> 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     0     0                 14395      13436     184320      13436
E     0     0                 10714      10215     184320      10215
          -------------------------------------------------------------

Buffer Pool Advisory for DB: cis  Instance: cis  End Snap: 143
-> Only rows with estimated physical reads >0 are displayed
-> ordered by Block Size, Buffers For Estimate

        Size for  Size      Buffers for  Est Physical          Estimated
P   Estimate (M) Factr         Estimate   Read Factor     Physical Reads
--- ------------ ----- ---------------- ------------- ------------------
D              8    .3            1,001          1.33      5,030,344,285
D             16    .7            2,002          1.09      4,111,401,303
D             24   1.0            3,003          1.00      3,785,412,620
D             32   1.3            4,004          0.88      3,325,874,690
D             40   1.7            5,005          0.73      2,778,756,798
D             48   2.0            6,006          0.59      2,224,515,464
D             56   2.3            7,007          0.41      1,540,741,333
D             64   2.7            8,008          0.26        995,586,778
D             72   3.0            9,009          0.19        709,557,460
D             80   3.3           10,010          0.16        591,536,046
D             88   3.7           11,011          0.12        448,525,720
D             96   4.0           12,012          0.11        399,329,837
D            104   4.3           13,013          0.10        364,511,029
D            112   4.7           14,014          0.08        312,321,922
D            120   5.0           15,015          0.07        260,147,348
D            128   5.3           16,016          0.06        211,367,285
D            136   5.7           17,017          0.05        175,634,866
D            144   6.0           18,018          0.04        142,382,812
D            152   6.3           19,019          0.03        104,428,938
D            160   6.7           20,020          0.02         61,150,579
          -------------------------------------------------------------


Buffer wait Statistics for DB: cis  Instance: cis  Snaps: 141 -143
-> ordered by wait time desc, waits desc

                                 Tot Wait    Avg
Class                    Waits   Time (s) Time (ms)
------------------ ----------- ---------- ---------
data block                 460          0         0
          -------------------------------------------------------------
PGA Aggr Target Stats for DB: cis  Instance: cis  Snaps: 141 -143
-> 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
--------------- ---------------- -------------------------
           31.1            5,001                    11,098

                                             %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        24         7       23.1        0.0     .0  100.0     .0      1,228
E        24         7       24.6        0.0     .0     .0     .0      1,228
          -------------------------------------------------------------

PGA Aggr Target Histogram for DB: cis  Instance: cis  Snaps: 141 -143
-> Optimal Executions are purely in-memory operations

    Low    High
Optimal Optimal    Total Execs Optimal Execs 1-Pass Execs M-Pass Execs
------- ------- -------------- ------------- ------------ ------------
     8K     16K         26,389        26,389            0            0
    16K     32K          4,932         4,932            0            0
    32K     64K          5,148         5,148            0            0
    64K    128K          1,960         1,960            0            0
   128K    256K            732           732            0            0
   256K    512K            411           411            0            0
   512K   1024K            479           479            0            0
     1M      2M            121            59           62            0
     2M      4M            188             0          188            0
     4M      8M             74             0           68            6
     8M     16M            112             0            0          112
    16M     32M             18             0            0           18
    32M     64M             18             0            0           18
          -------------------------------------------------------------

PGA Memory Advisory for DB: cis  Instance: cis  End Snap: 143
-> 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
---------- ------- ---------------- ---------------- -------- ----------
        12     0.5      1,482,904.6      1,221,885.5     55.0     37,135
        18     0.8      1,482,904.6      1,221,885.5     55.0     37,134
        24     1.0      1,482,904.6      1,146,032.4     56.0        334
        29     1.2      1,482,904.6      1,045,266.0     59.0         15
        34     1.4      1,482,904.6        979,328.1     60.0         14
        38     1.6      1,482,904.6        918,905.4     62.0         14
        43     1.8      1,482,904.6        844,923.3     64.0          7
        48     2.0      1,482,904.6        750,170.4     66.0          7
        72     3.0      1,482,904.6        294,527.4     83.0          0
        96     4.0      1,482,904.6        285,455.9     84.0          0
       144     6.0      1,482,904.6        274,908.8     84.0          0
       192     8.0      1,482,904.6        269,613.6     85.0          0
          -------------------------------------------------------------
Rollback Segment Stats for DB: cis  Instance: cis  Snaps: 141 -143
->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           40.0    0.00               0        0        0        0
     1        4,706.0    0.00         428,516        0        0        0
     2        3,988.0    0.00       1,338,990        4        0        1
     3        4,320.0    0.00         652,016        1        0        0
     4        4,095.0    0.00         373,810        3        0        1
     5        3,912.0    0.00         664,334        1        0        0
     6        4,432.0    0.00         306,288        0        0        0
     7        3,804.0    0.00         480,016        0        0        0
     8        4,270.0    0.00         311,800        2        0        1
     9        4,070.0    0.00         303,198        0        0        0
    10        4,296.0    0.00       2,189,274        5        0        2
          -------------------------------------------------------------
Rollback Segment Storage for DB: cis  Instance: cis  Snaps: 141 -143
->Optimal Size should be larger than Avg Active

RBS No    Segment Size      Avg Active    Optimal Size    Maximum Size
------ --------------- --------------- --------------- ---------------
     0         385,024           6,553                         385,024
     1       2,220,032         403,125                       3,268,608
     2       2,220,032         488,447                       4,317,184
     3       2,220,032         496,665                       3,268,608
     4       2,220,032         399,239                       4,317,184
     5       2,220,032         496,112                       4,317,184
     6       2,220,032         409,036                       4,317,184
     7       2,220,032         362,586                       3,268,608
     8       2,220,032         415,089                       3,268,608
     9       2,220,032         421,983                       4,317,184
    10       4,317,184       1,314,611                       4,317,184
          -------------------------------------------------------------
Undo Segment Summary for DB: cis  Instance: cis  Snaps: 141 -143
-> Undo segment block stats:
-> uS - unexpired Stolen,   your - 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            979 ##########      105          1        0      0 0/0/0/0/0/0
          -------------------------------------------------------------


Undo Segment Stats for DB: cis  Instance: cis  Snaps: 141 -143
-> 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
------------ ------------ -------- ------- -------- ------- ------ -------------
31-Jul 15:08           19 ########      19        1       0      0 0/0/0/0/0/0
31-Jul 14:58           17 ########      47        1       0      0 0/0/0/0/0/0
31-Jul 14:48           24 ########      49        1       0      0 0/0/0/0/0/0
31-Jul 14:38           18 ########      43        1       0      0 0/0/0/0/0/0
31-Jul 14:28           17 ########       3        1       0      0 0/0/0/0/0/0
31-Jul 14:18           17 ########       3        1       0      0 0/0/0/0/0/0
31-Jul 14:08           18 ########       3        1       0      0 0/0/0/0/0/0
31-Jul 13:58           16 ########       3        1       0      0 0/0/0/0/0/0
31-Jul 13:48           18 ########       4        1       0      0 0/0/0/0/0/0
31-Jul 13:38           19 ########      49        1       0      0 0/0/0/0/0/0
31-Jul 13:28           33 ########      60        1       0      0 0/0/0/0/0/0
31-Jul 13:18           36 ########      74        1       0      0 0/0/0/0/0/0
31-Jul 13:08           34 ########      64        1       0      0 0/0/0/0/0/0
31-Jul 12:58           20 ########       6        1       0      0 0/0/0/0/0/0
31-Jul 12:48           18 ########      49        1       0      0 0/0/0/0/0/0
31-Jul 12:38           34 ########      49        1       0      0 0/0/0/0/0/0
31-Jul 12:28           48 ########      58        1       0      0 0/0/0/0/0/0
31-Jul 12:18          438 ########      65        1       0      0 0/0/0/0/0/0
31-Jul 12:08          135 ########     105        1       0      0 0/0/0/0/0/0
          -------------------------------------------------------------
Latch Activity for DB: cis  Instance: cis  Snaps: 141 -143
->"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                   13,409    0.0             0            0
FIB s.o chain latch                   2    0.0             0            0
FOB s.o list latch                  296    0.0             0            0
SQL memory manager latch              2    0.0             0        3,651    0.0
SQL memory manager worka        248,881    0.0             0            0
active checkpoint queue           4,359    0.0             0            0
archive control                     371    0.0             0            0
archive process latch               219    0.0             0            0
cache buffer handles              1,504    0.0             0            0
cache buffers chains         98,632,793    0.0    0.0      0   58,719,289    0.0
cache buffers lru chain          14,637    0.4    0.0      0   63,016,916    0.1
channel handle pool latc            110    0.0             0            0
channel operations paren          7,507    0.0             0            0
checkpoint queue latch          411,236    0.0             0        3,346    0.0
child cursor hash table          16,999    0.0             0            0
dml lock allocation              70,204    0.0             0            0
dummy allocation                    110    0.0             0            0
enqueue hash chains             125,629    0.0             0            0
enqueues                         24,000    0.0             0            0
event group latch                    58    0.0             0            0
hash table column usage              42    0.0             0        1,488    0.0
job_queue_processes para            183    0.0             0            0
kmcptab latch                         1    0.0             0            0
kmcpvec latch                         0                    0            1    0.0
ktm global data                      37    0.0             0            0
kwqit: protect wakeup ti            354    0.0             0            0
lgwr LWN SCN                     16,290    0.0             0            0
library cache                   563,175    0.0    0.0      0        7,907    0.3
library cache load lock             896    0.0             0            0
library cache pin               231,505    0.0             0            0
library cache pin alloca        147,689    0.0             0            0
list of block allocation            221    0.0             0            0
loader state object free          1,416    0.0             0            0
longop free list parent               8    0.0             0            8    0.0
messages                         90,400    0.1    0.0      0            0
mostly latch-free SCN            16,343    0.1    0.0      0            0
multiblock read objects       6,832,318    0.0    0.0      0            0
ncodef allocation latch             180    0.0             0            0
object stats modificatio          3,409    0.0             0            0
post/wait queue                     336    0.0             0          232    0.0
process allocation                   58    0.0             0           58    0.0
process group creation              110    0.0             0            0
redo allocation                 106,867    0.1    0.0      0            0
redo copy                             0                    0       77,396    0.3
redo writing                     51,890    0.0             0            0
row cache enqueue latch          63,940    0.0             0            0
row cache objects                68,997    0.0             0        2,096    0.0
sequence cache                   35,854    0.0             0            0
session allocation               44,086    0.0             0            0
session idle bit                 94,703    0.0             0            0
Latch Activity for DB: cis  Instance: cis  Snaps: 141 -143
->"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 switching                   180    0.0             0            0
session timer                     3,786    0.0             0            0
shared pool                     285,945    0.0    0.3      0            0
simulator hash latch          4,103,915    0.0             0            0
simulator lru latch           1,983,566    0.0    0.0      0        9,730    0.1
sort extent pool                  3,770    0.0             0            0
spilled msgs queues list            354    0.0             0            0
transaction allocation              149    0.0             0            0
transaction branch alloc            180    0.0             0            0
undo global data                 69,325    0.0             0            0
user lock                           216    0.0             0            0
          -------------------------------------------------------------
Latch Sleep breakdown for DB: cis  Instance: cis  Snaps: 141 -143
-> ordered by misses desc

                                      Get                            Spin &
Latch Name                       Requests      Misses      Sleeps Sleeps 1->4
-------------------------- -------------- ----------- ----------- ------------
multiblock read objects         6,832,318       1,758           1 1757/1/0/0/0
cache buffers chains           98,632,793         918          19 0/0/0/0/0
redo allocation                   106,867          71           1 70/1/0/0/0
library cache                     563,175          34           1 33/1/0/0/0
shared pool                       285,945          18           6 12/6/0/0/0
          -------------------------------------------------------------
Latch Miss Sources for DB: cis  Instance: cis  Snaps: 141 -143
-> only latches with sleeps are shown
-> ordered by name, sleeps desc

                                                     NoWait              Waiter
Latch Name               Where                       Misses     Sleeps   Sleeps
------------------------ -------------------------- ------- ---------- --------
cache buffers chains     kcbrls: kslbegin                 0          7        3
cache buffers chains     kcbgtcr: kslbegin excl           0          6        6
cache buffers chains     kcbzib: multi-block read:        0          4        0
cache buffers chains     kcbzgb: scan from tail. no       0          1        0
cache buffers chains     kcbzib: finish free bufs         0          1        5
multiblock read objects  kcbzib: normal mbr free          0          1        1
redo allocation          kcrfwr                           0          1        0
shared pool              kghfrunp: alloc: wait            0          5        0
shared pool              kghalo                           0          1        0
shared pool              kghfrunp: clatch: wait           0          1        0
          -------------------------------------------------------------
Top 5 Logical Reads per Segment for DB: cis  Instance: cis  Snaps: 141 -143
-> End Segment Logical Reads Threshold:     10000

                                           Subobject  Obj.       Logical
Owner      Tablespace Object Name          Name       Type         Reads  %Total
---------- ---------- -------------------- ---------- ----- ------------ -------
IRS2007_YB IRS2007_YB SUBJECTIVE_PRM_TRANS            TABLE   24,481,184   72.17
IRS2007_YB IRS2007_YB TRN_PROP_FACILITY               TABLE    3,475,856   10.25
IRS2007_YB IRS2007_YB TRN_SANC_FACILITY               TABLE    1,885,696    5.56
IRS2007_YB IRS2007_YB COABALANCES                     TABLE    1,207,200    3.56
IRS2007_YB IRS2007_YB RAT_CALC_RISKSCORE              TABLE      640,528    1.89
          -------------------------------------------------------------


Top 5 Physical Reads per Segment for DB: cis  Instance: cis  Snaps: 141 -143
-> End Segment Physical Reads Threshold:    1000

                                           Subobject  Obj.      Physical
Owner      Tablespace Object Name          Name       Type         Reads  %Total
---------- ---------- -------------------- ---------- ----- ------------ -------
IRS2007_YB IRS2007_YB SUBJECTIVE_PRM_TRANS            TABLE   23,951,135   78.89
IRS2007_YB IRS2007_YB TRN_PROP_FACILITY               TABLE    1,847,436    6.08
IRS2007_YB IRS2007_YB TRN_SANC_FACILITY               TABLE    1,541,800    5.08
IRS2007_YB IRS2007_YB COABALANCES                     TABLE    1,190,053    3.92
IRS2007_YB IRS2007_YB RAT_CALC_RISKSCORE              TABLE      622,136    2.05
          -------------------------------------------------------------
Top 5 Buf. Busy Waits per Segment for DB: cis  Instance: cis  Snaps: 141 -14
-> End Segment Buffer Busy Waits Threshold:     100

                                                                  Buffer
                                           Subobject  Obj.          Busy
Owner      Tablespace Object Name          Name       Type         Waits  %Total
---------- ---------- -------------------- ---------- ----- ------------ -------
IRS2007_YB IRS2007_YB SUBJECTIVE_PRM_TRANS            TABLE          458   99.57
IRS2007_YB IRS2007_YB COARATIOS                       TABLE            1     .22
IRS2007_YB IRS2007_YB STATEMENT                       TABLE            1     .22
          -------------------------------------------------------------
Dictionary Cache Stats for DB: cis  Instance: cis  Snaps: 141 -143
->"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                            22    0.0       0              0         22
dc_histogram_defs                  684   21.9       0              0        276
dc_object_ids                    1,768    0.8       0              0        674
dc_objects                       8,478    2.5       0              0      2,525
dc_profiles                         57    0.0       0              0          2
dc_rollback_segments               840    0.0       0              0         22
dc_segments                     12,157    0.1       0              0      1,552
dc_sequences                       239    1.7       0            239         38
dc_tablespace_quotas                 1    0.0       0              1          1
dc_tablespaces                   2,519    0.0       0              0         22
dc_user_grants                   3,484    0.0       0              0         22
dc_usernames                       795    0.1       0              0         38
dc_users                         4,807    0.0       0              0         50
          -------------------------------------------------------------


Library Cache Activity for DB: cis  Instance: cis  Snaps: 141 -143
->"Pct Misses"  should be very low

                         Get  Pct        Pin        Pct               Invali-
Namespace           Requests  Miss     Requests     Miss     Reloads  dations
--------------- ------------ ------ -------------- ------ ---------- --------
BODY                      43    0.0             43    0.0          0        0
CLUSTER                  241    0.0            130    0.0          0        0
INDEX                  2,478    0.0          2,478    0.0          0        0
SQL AREA              21,051    8.8         75,088    5.5        183        2
TABLE/PROCEDURE       22,980    1.1         43,000    1.4        146        0
TRIGGER                  224    0.0            224    0.0          0        0
          -------------------------------------------------------------
Shared Pool Advisory for DB: cis  Instance: cis  End Snap: 143
-> 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
----------- ----- ---------- ------------ ------------ ------- ---------------
         88    .6         84       12,568    1,873,053     1.0     142,916,788
        104    .7         99       14,863    1,873,904     1.0     143,162,723
        120    .8        124       19,129    1,874,436     1.0     143,364,105
        136    .9        139       23,439    1,875,000     1.0     143,641,721
        152   1.0        154       27,411    1,875,719     1.0     144,001,698
        168   1.1        169       29,826    1,876,309     1.0     144,297,774
        184   1.2        184       32,090    1,876,764     1.0     144,549,145
        200   1.3        199       34,104    1,877,083     1.0     144,720,515
        216   1.4        214       36,322    1,877,255     1.0     144,818,301
        232   1.5        229       37,879    1,877,387     1.0     144,882,268
        248   1.6        244       40,237    1,877,416     1.0     144,901,654
        264   1.7        259       42,547    1,877,440     1.0     144,919,319
        280   1.8        274       46,396    1,877,463     1.0     144,933,934
        296   1.9        289       48,599    1,877,477     1.0     144,944,634
        312   2.1        327       53,355    1,877,497     1.0     144,952,808
          -------------------------------------------------------------
SGA Memory Summary for DB: cis  Instance: cis  Snaps: 141 -143

SGA regions                       Size in Bytes
------------------------------ ----------------
Database Buffers                     25,165,824
Fixed Size                              455,472
Redo Buffers                            667,648
Variable Size                       662,700,032
                               ----------------
sum                                 688,988,976
          -------------------------------------------------------------


SGA breakdown difference for DB: cis  Instance: cis  Snaps: 141 -143

Pool   Name                                Begin value        End value  % Diff
------ ------------------------------ ---------------- ---------------- -------
java   free memory                         155,848,704      155,848,704    0.00
java   memory in use                         3,534,848        3,534,848    0.00
large  free memory                           8,388,608        8,388,608    0.00
shared 1M buffer                             2,098,176        2,098,176    0.00
shared Checkpoint queue                        564,608          564,608    0.00
shared FileOpenBlock                           695,504          695,504    0.00
shared KGK heap                                  3,756            3,756    0.00
shared KGLS heap                            17,513,296        3,963,748  -77.37
shared KQR M PO                              2,633,304        2,654,808    0.82
shared KQR S PO                                654,916          654,916    0.00
shared KQR S SO                                  9,740            9,740    0.00
shared KSXR pending messages que               841,036          841,036    0.00
shared KSXR receive buffers                  1,033,000        1,033,000    0.00
shared PL/SQL DIANA                          1,217,812        1,043,520  -14.31
shared PL/SQL MPCODE                         2,048,336        2,042,224   -0.30
shared PLS non-lib hp                            2,068            2,068    0.00
shared character set object                    374,860          374,860    0.00
shared dictionary cache                      1,614,976        1,614,976    0.00
shared errors                                   49,216           49,216    0.00
shared event statistics per sess             1,899,240        1,899,240    0.00
shared fixed allocation callback                   284              284    0.00
shared free memory                          17,479,904       12,494,564  -28.52
shared joxlod: in ehe                          193,380          193,380    0.00
shared joxs heap init                            4,220            4,220    0.00
shared library cache                        32,626,100       34,080,588    4.46
shared message pool freequeue                  834,752          834,752    0.00
shared miscellaneous                        14,476,544       14,648,852    1.19
shared parameters                              123,108          104,552  -15.07
shared sessions                                410,720          410,720    0.00
shared sim memory hea                           42,344           42,344    0.00
shared sql area                             65,943,184       93,793,648   42.23
shared table definiti                            3,336            4,812   44.24
shared trigger defini                              748              748    0.00
shared trigger inform                            1,528            1,528    0.00
shared trigger source                              380              380    0.00
       buffer_cache                         25,165,824       25,165,824    0.00
       fixed_sga                               455,472          455,472    0.00
       log_buffer                              656,384          656,384    0.00
          -------------------------------------------------------------
init.ora Parameters for DB: cis  Instance: cis  Snaps: 141 -143

                                                                  End value
Parameter Name                Begin value                       (if different)
----------------------------- --------------------------------- --------------
aq_tm_processes               1
audit_trail                   TRUE
background_dump_dest          C:\oracle\admin\cis\bdump
compatible                    9.2.0.0.0
control_files                 C:\oracle\oradata\cis\CONTROL01
core_dump_dest                C:\oracle\admin\cis\cdump
db_block_size                 8192
db_cache_size                 25165824
db_domain
db_file_multiblock_read_count 16
db_name                       cis
dispatchers                   (PROTOCOL=TCP) (SERVICE=cisXDB)
fast_start_mttr_target        0
hash_join_enabled             TRUE
instance_name                 cis
java_pool_size                159383552
job_queue_processes           10
large_pool_size               8388608
log_archive_dest              D:\oracle\oradata\cis\Archive\
log_archive_format            cis_%t_%s.arc
log_archive_start             TRUE
open_cursors                  300
pga_aggregate_target          25165824
processes                     150
query_rewrite_enabled         FALSE
remote_login_passwordfile     EXCLUSIVE
sga_max_size                  688988976
shared_pool_size              159383552
sort_area_size                524288
star_transformation_enabled   FALSE
timed_statistics              TRUE
undo_management               AUTO
undo_retention                10800
undo_tablespace               UNDOTBS1
user_dump_dest                C:\oracle\admin\cis\udump
          -------------------------------------------------------------

End of Report




Regards
Re: statspack report [message #337652 is a reply to message #337645] Thu, 31 July 2008 08:51 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
./fa/449/0/

Regards
Michel
Re: statspack report [message #337657 is a reply to message #337652] Thu, 31 July 2008 09:22 Go to previous messageGo to next message
manoj12
Messages: 185
Registered: March 2008
Location: India
Senior Member
Dear Sir

Appreciate your help towards checking the statspack report and giving me advice

Regards
Re: statspack report [message #337749 is a reply to message #337657] Fri, 01 August 2008 00:45 Go to previous messageGo to next message
sunil_v_mishra
Messages: 506
Registered: March 2005
Senior Member
Laughing

Avg
Total Wait wait Waits
Event Waits Timeouts Time (s) (ms) /txn
---------------------------- ------------ ---------- ---------- ------ --------
db file scattered read 2,587,518 0 2,022 1 1,680.2

Need to tune this query

select to_char(stmtdt, 'yyyy') as ID, to_char(stmtdt, 'yyyy') as
year from statement where borrid=5832 and user_id=22 and coaid
=4 and to_char(stmtdt, 'yyyy') in ( select os.year from (
select borrid, year, count(*) as cntActuals from subjectiv
e_prm_trans where borrid=5832 and user_id=22 and mdlid =4 an


I/O tune Very Happy all the blocks are scattered in database buffer

Set proper value for DB_FILE_MULTIBLOCK_READ_COUNT
Re: statspack report [message #337751 is a reply to message #337645] Fri, 01 August 2008 00:50 Go to previous messageGo to next message
BlackSwan
Messages: 25041
Registered: January 2009
Location: SoCal
Senior Member
>Set proper value for DB_FILE_MULTIBLOCK_READ_COUNT
& what is "proper" value for ALL environments?
The real world wants to know your solutions for every Oracle DB.
Re: statspack report [message #337767 is a reply to message #337751] Fri, 01 August 2008 01:47 Go to previous messageGo to next message
manoj12
Messages: 185
Registered: March 2008
Location: India
Senior Member
Dear Sir

Well according to me Online transaction processing (OLTP) and batch environments typically have values in the range of 4 to 16 for this parameter for db_FILE_MULTIBLOCK_READ_COUNT.

As far as tuning of the query is concerned we will apply indexes on these queries so that would avoid the full table scans.

Also I would suggests the value for db_file_multiblock_read_count as 12

Since our application is not at all an OLTP NOR its batch environments.If you think its correct I would suggest this value

also I would recommend cursor_sharing parameter to set as force.
This is what my observation is all about

Please suggest I should do?

Regards


Re: statspack report [message #337781 is a reply to message #337767] Fri, 01 August 2008 03:44 Go to previous messageGo to next message
manoj12
Messages: 185
Registered: March 2008
Location: India
Senior Member
Dear Sir

Any update would appreciate

Regards
Re: statspack report [message #338050 is a reply to message #337781] Sat, 02 August 2008 15:12 Go to previous messageGo to next message
sunil_v_mishra
Messages: 506
Registered: March 2005
Senior Member
What do u mean by db file db file scattered read ?
It is a wait event which occurs when Oracle performs multiblock reads from disk into non-contiguous ('scattered') buffers in the Buffer Cache.

Seeing your statspack report.I came to know about this wait which is degrading your performance

Secondly most of the query doing select as per the statspack report.

Multiblock reads are issued for up to DB_FILE_MULTIBLOCK_READ_COUNT blocks at a time.

I/O is a function of the operating system and so there are operating system specific limits imposed on the setting of this parameter.

It is possible to tune the size of multiblock I/Os issued by Oracle by setting DB_FILE_MULTIBLOCK_READ_COUNT as

DB_BLOCK_SIZE x DB_FILE_MULTIBLOCK_READ_COUNT = max_io_size of system

In 10g DB_FILE_MULTIBLOCK_READ_COUNT is automatically tuned to use a default value when this parameter is not set explicitly.

If the value of db_file_multiblock_read_count is set too high then the server will consume additional memory

For OLTP oracle says having high value of DB_FILE_MULTIBLOCK_READ_COUNT most of time degrade the performance.

Note:-Db_file_multiblock_read_count cannot exceed:
(db_block_buffers/4)

Default value corresponds to the maximum I/O size that can be performed efficiently is 1MB for most platforms.

db_file_multiblock_read_count decide how many I/O calls will
be required to complete a table scan.

Manoj as your are suggesting db_file_multiblock_read_count value as 12 then

8X12= 96k I don't think it will improve performance.

Note :- As per statspack Block size is 8


Can you try after setting db_file_multiblock_read_count value as 64 ?

8 X 64 = 512K. Cool

[Updated on: Sat, 02 August 2008 15:14]

Report message to a moderator

Re: statspack report [message #338069 is a reply to message #337781] Sun, 03 August 2008 01:40 Go to previous message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Database Performance Tuning Guide
Chapter 10 Instance Tuning Using Performance Views
Section 10.3 Wait Events Statistics
Paragraph 10.3.3 db file scattered read

Regards
Michel
Previous Topic: HIGH no of LOCKS exclusive in tables for UPDATE Statement
Next Topic: CREATE TABLE using SEQUENCE
Goto Forum:
  


Current Time: Tue Dec 06 12:22:30 CST 2016

Total time taken to generate the page: 0.19063 seconds