Re: URGENT: Slow Performance after migration to 9.2.0.3 from 8.1.7

On 5 Sep 2003 20:33:32 -0700, Alex Vilner wrote:

> Dear all,
>
> We are having significant performance degradation (10 to 100 times) in
> the execution of the queries on the Oracle 9i server, after migrating
> the database from 8i to 9i. 9i has been installed on a new machine
> (not an upgrade, 9.2 & Patch Set 2), database dump loaded, statistics
> computed anew... The servers are Windows 2000 & Solaris, drop in
> performance is experienced on both 9i environments....
>
> Server info:
> 9i: Windows 2000 SP 4, 4x700MHz CPU, 2Gb RAM, 2 disk arrays 0+1 - All
> objects (tables & indexes in one tablespace on disk array 1) redo logs
> on disk array 2. Sorry, this could not be changed for now -- version
> control....
>
> 8i: Windows 2000 SP 2 2x600 MHz CPU 1Gb RAM - no disk array, all
> objects & redo logs on the same drive.
>
> 9i: Solaris - 5.8 Generic_108528-14 sun4u sparc SUNW,Sun-Fire-280R,
> 2x700Mz CPU, 2 Gb RAM Sun disk array.
> The results were the same as the Windows 2000 9i - 10+ times slower
> than 8i.
>
> Shown below (pardon the length) is the statspack report on the new
> box... Is there anything that raises an eyebrow?
>
> Thank you in advance!
>
> --Alex Vilner
>
> -----------------------
>
> STATSPACK report for
>
> DB Name DB Id Instance Inst Num Release Cluster
> Host
> ------------ ----------- ------------ -------- ----------- -------
> ------------
> EUROPA 740458958 europa 1 9.2.0.3.0 NO
> EUROPA
>
> Snap Id Snap Time Sessions Curs/Sess Comment
> ------- ------------------ -------- ---------
> -------------------
> Begin Snap: 5 19-Aug-03 16:34:09 27 2.0
> End Snap: 6 19-Aug-03 17:37:52 28 1.2
> Elapsed: 63.72 (mins)
>
> Cache Sizes (end)
> ~~~~~~~~~~~~~~~~~
> Buffer Cache: 512M Std Block Size:
> 8K
> Shared Pool Size: 128M Log Buffer:
> 1,024K
>
> Load Profile
> ~~~~~~~~~~~~ Per Second Per
> Transaction
> ---------------
> ---------------
> Redo size: 14,801.18
> 20,598.81
> Logical reads: 3,940.09
> 5,483.42
> Block changes: 73.04
> 101.65
> Physical reads: 1,346.75
> 1,874.28
> Physical writes: 1,317.28
> 1,833.26
> User calls: 5.39
> 7.50
> Parses: 2.01
> 2.80
> Hard parses: 0.00
> 0.01
> Sorts: 0.25
> 0.35
> Logons: 0.00
> 0.00
> Executes: 7.32
> 10.19
> Transactions: 0.72
>
> % Blocks changed per Read: 1.85 Recursive Call %: 68.91
> Rollback per transaction %: 0.00 Rows per Sort: 15.30
>
> Instance Efficiency Percentages (Target 100%)
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> Buffer Nowait %: 100.00 Redo NoWait %: 100.00
> Buffer Hit %: 99.21 In-memory Sort %: 100.00
> Library Hit %: 99.91 Soft Parse %: 99.82
> Execute to Parse %: 72.49 Latch Hit %: 100.00
> Parse CPU to Parse Elapsd %: 93.30 % Non-Parse CPU: 99.95
>
> Shared Pool Statistics Begin End
> ------ ------
> Memory Usage %: 25.24 25.48
> % SQL with executions>1: 55.31 55.14
> % Memory for SQL w/exec>1: 63.78 63.96
>
> Top 5 Timed Events
> ~~~~~~~~~~~~~~~~~~
> % Total
> Event Waits Time (s)
> Ela Time
> -------------------------------------------- ------------ -----------
> --------
> CPU time 3,498
> 94.42
> direct path write 719,425 78
> 2.10
> db file sequential read 36,490 62
> 1.69
> direct path read 718,621 48
> 1.30
> db file scattered read 5,621 12
> .32
> -------------------------------------------------------------
> Wait Events for DB: EUROPA Instance: europa Snaps: 5 -6
> -> 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
> ---------------------------- ------------ ---------- ---------- ------
> --------
> direct path write 719,425 0 78 0
> 261.9
> db file sequential read 36,490 0 62 2
> 13.3
> direct path read 718,621 0 48 0
> 261.6
> db file scattered read 5,621 0 12 2
> 2.0
> log file parallel write 6,816 3,334 2 0
> 2.5
> control file parallel write 1,241 0 2 2
> 0.5
> inactive session 1 1 1 1030
> 0.0
> log file sync 764 0 1 1
> 0.3
> control file sequential read 548 0 0 1
> 0.2
> LGWR wait for redo copy 384 0 0 0
> 0.1
> SQL*Net break/reset to clien 6 0 0 1
> 0.0
> SQL*Net more data to client 3 0 0 0
> 0.0
> buffer busy waits 1 0 0 0
> 0.0
> virtual circuit status 27,922 0 3,810 136
> 10.2
> wakeup time manager 120 120 3,662 30520
> 0.0
> SQL*Net message from client 24,337 0 3,157 130
> 8.9
> SQL*Net message to client 24,338 0 0 0
> 8.9
> -------------------------------------------------------------
> Background Wait Events for DB: EUROPA Instance: europa Snaps: 5 -6
> -> ordered by wait time desc, waits desc (idle events last)
>
> Avg
> Total Wait wait
> Waits
> Event Waits Timeouts Time (s) (ms)
> /txn
> ---------------------------- ------------ ---------- ---------- ------
> --------
> log file parallel write 6,816 3,334 2 0
> 2.5
> control file parallel write 1,241 0 2 2
> 0.5
> control file sequential read 496 0 0 1
> 0.2
> LGWR wait for redo copy 384 0 0 0
> 0.1
> rdbms ipc message 14,008 7,120 14,316 1022
> 5.1
> pmon timer 1,285 1,285 3,822 2974
> 0.5
> smon timer 12 12 3,524 ######
> 0.0
> -------------------------------------------------------------
> SQL ordered by Gets for DB: EUROPA Instance: europa Snaps: 5 -6
> -> 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
> --------------- ------------ -------------- ------ -------- ---------
> ----------
> 14,995,914 1 14,995,914.0 99.6 3489.55 3697.52
> 4219140144
> Module: SQL*Plus
> BEGIN ll_ttest(-1); END;
>
> 14,990,161 1,984 7,555.5 99.5 3487.58 3695.39
> 1168063042
> Module: SQL*Plus
> INSERT into LL_RESULTS_TABLE SELECT t0.SEQID, t0.SITEID,
> t0.ROLE, t1.LEADNAME, t2.COMPANYNAME, t3.PRODUCTNAM
> E, t4.FIRST_NAME, t4.LAST_NAME, t1.LASTMODIFIED, t5.
> CAMPAIGN_NAME, t0.STATUS, t0.RANKSUMMARY, t1.SIZE_OF_OP
> PORTUNITY, t1.SIZE_OF_OPPORTUNITY_CURRENCY, t0.SUGGESTED_A
>
> 50,014 120 416.8 0.3 1.80 2.61
> 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
>
> 18,514 2,640 7.0 0.1 0.47 0.68
> 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
>
> 3,771 3,771 1.0 0.0 0.47 0.60
> 1092438951
> Module: JDBC Thin Client
> SELECT ID, SUBCLASS, STATUS, LEASE_ID from COMMAND where ( STAT
> US = 1 or (STATUS = 2 and EXPIRATION < sysdate) ) and FIRINGTI
> ME <= :b2 and ( TYPE_MASK < :b1 or TYPE_MASK > :b1 ) order by
> FIRINGTIME
>
> 3,771 3,771 1.0 0.0 5.25 11.31
> 3918312570
> Module: JDBC Thin Client
> BEGIN GET_COMMAND(:1,:2,:3,:4,:5,:6,:7); END;
>
> 3,655 1 3,655.0 0.0 0.83 0.86
> 499570837
> Module: SQL*Plus
> begin :snap :=statspack.snap; end;
>
> 3,420 1,983 1.7 0.0 0.58 0.65
> 3724654729
> Module: SQL*Plus
> INSERT into USER_LL_TIME_TABLE (USERSEQID,USERNAME,LEADBOXSEQ
> ,HUND_SECS ) values (:b5, :b4, :b3, (:b2 - :b1))
>
> 2,291 760 3.0 0.0 0.41 0.40
> 77789303
> Module: JDBC Thin Client
> UPDATE HEARTBEATRECORD SET BEATS = :1, LAST_BEAT = :2 WHERE (ID
> = :3)
>
> SQL ordered by Gets for DB: EUROPA Instance: europa Snaps: 5 -6
> -> 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,983 5,754 0.3 0.0 0.92 0.90
> 1053795750
> Module: SQL*Plus
> COMMIT
>
> 1,520 760 2.0 0.0 0.22 0.31
> 190493219
> Module: JDBC Thin Client
> SELECT BEATS, ID, LAST_BEAT FROM HEARTBEATRECORD WHERE (ID = :1)
>
>
> 1,492 746 2.0 0.0 0.00 0.28
> 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
>
> 746 746 1.0 0.0 0.00 0.08
> 1693927332
> select count(*) from sys.job$ where (next_date > sysdate) and (n
> ext_date < (sysdate+5/86400))
>
> 360 120 3.0 0.0 0.00 0.02
> 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
>
> 360 120 3.0 0.0 0.00 0.02
> 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
>
> 120 120 1.0 0.0 0.00 0.03
> 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
>
> 120 120 1.0 0.0 0.06 0.03
> 994140048
> 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_
>
> 120 120 1.0 0.0 0.00 0.02
> 1824912791
> select q_name, state, delay, expiration, rowid, msgid, dequeue
> _msgid, chain_no, local_order_no, enq_time, enq_tid, step_no,
> SQL ordered by Gets for DB: EUROPA Instance: europa Snaps: 5 -6
> -> 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
> --------------- ------------ -------------- ------ -------- ---------
> ----------
> 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
>
> 120 120 1.0 0.0 0.00 0.04
> 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
>
> -------------------------------------------------------------
> SQL ordered by Reads for DB: EUROPA Instance: europa Snaps: 5 -6
> -> End Disk Reads Threshold: 1000
>
> CPU Elapsd
> Physical Reads Executions Reads per Exec %Total Time (s) Time (s)
> Hash Value
> --------------- ------------ -------------- ------ -------- ---------
> ----------
> 5,148,618 1 5,148,618.0 100.0 3489.55 3697.52
> 4219140144
> Module: SQL*Plus
> BEGIN ll_ttest(-1); END;
>
> 5,148,616 1,984 2,595.1 100.0 3487.58 3695.39
> 1168063042
> Module: SQL*Plus
> INSERT into LL_RESULTS_TABLE SELECT t0.SEQID, t0.SITEID,
> t0.ROLE, t1.LEADNAME, t2.COMPANYNAME, t3.PRODUCTNAM
> E, t4.FIRST_NAME, t4.LAST_NAME, t1.LASTMODIFIED, t5.
> CAMPAIGN_NAME, t0.STATUS, t0.RANKSUMMARY, t1.SIZE_OF_OP
> PORTUNITY, t1.SIZE_OF_OPPORTUNITY_CURRENCY, t0.SUGGESTED_A
>
> 7 1 7.0 0.0 0.83 0.86
> 499570837
> Module: SQL*Plus
> begin :snap :=statspack.snap; end;
>
> 1 1,983 0.0 0.0 0.58 0.65
> 3724654729
> Module: SQL*Plus
> INSERT into USER_LL_TIME_TABLE (USERSEQID,USERNAME,LEADBOXSEQ
> ,HUND_SECS ) values (:b5, :b4, :b3, (:b2 - :b1))
>
> 0 760 0.0 0.0 0.41 0.40
> 77789303
> Module: JDBC Thin Client
> UPDATE HEARTBEATRECORD SET BEATS = :1, LAST_BEAT = :2 WHERE (ID
> = :3)
>
> 0 3,771 0.0 0.0 0.39 0.59
> 94568782
> Module: JDBC Thin Client
> LOCK table COMMAND_LCK in exclusive mode
>
> 0 760 0.0 0.0 0.22 0.31
> 190493219
> Module: JDBC Thin Client
> SELECT BEATS, ID, LAST_BEAT FROM HEARTBEATRECORD WHERE (ID = :1)
>
>
> 0 3 0.0 0.0 0.00 0.00
> 204386021
> select col#, grantee#, privilege#,max(mod(nvl(option$,0),2)) fro
> m objauth$ where obj#=:1 and col# is not null group by privilege
> #, col#, grantee# order by col#, grantee#
>
> 0 120 0.0 0.0 0.00 0.03
> 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
>
> 0 120 0.0 0.0 1.80 2.61
> 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
>
> SQL ordered by Reads for DB: EUROPA Instance: europa Snaps: 5 -6
> -> End Disk Reads Threshold: 1000
>
> CPU Elapsd
> Physical Reads Executions Reads per Exec %Total Time (s) Time (s)
> Hash Value
> --------------- ------------ -------------- ------ -------- ---------
> ----------
> 0 3 0.0 0.0 0.00 0.00
> 839312984
> select name,intcol#,segcol#,type#,length,nvl(precision#,0),decod
> e(type#,2,nvl(scale,-127/*MAXSB1MINAL*/),178,scale,179,scale,180
> ,scale,181,scale,182,scale,183,scale,231,scale,0),null$,fixedsto
> rage,nvl(deflength,0),default$,rowid,col#,property, nvl(charseti
> d,0),nvl(charsetform,0),spare1,spare2,nvl(spare3,0) from col$ wh
>
> 0 120 0.0 0.0 0.00 0.02
> 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
>
> 0 120 0.0 0.0 0.06 0.03
> 994140048
> 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_
>
> 0 3 0.0 0.0 0.00 0.00
> 1006414593
> select type#,blocks,extents,minexts,maxexts,extsize,extpct,user#
> ,iniexts,NVL(lists,65535),NVL(groups,65535),cachehint,hwmincr, N
> VL(spare1,0) from seg$ where ts#=:1 and file#=:2 and block#=:3
>
> 0 5,754 0.0 0.0 0.92 0.90
> 1053795750
> Module: SQL*Plus
> COMMIT
>
> 0 3,771 0.0 0.0 0.47 0.60
> 1092438951
> Module: JDBC Thin Client
> SELECT ID, SUBCLASS, STATUS, LEASE_ID from COMMAND where ( STAT
> US = 1 or (STATUS = 2 and EXPIRATION < sysdate) ) and FIRINGTI
> ME <= :b2 and ( TYPE_MASK < :b1 or TYPE_MASK > :b1 ) order by
> FIRINGTIME
>
> 0 120 0.0 0.0 0.00 0.02
> 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
>
> 0 746 0.0 0.0 0.00 0.28
> 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
>
> 0 7 0.0 0.0 0.02 0.00
> 1433558559
> select intcol#,nvl(pos#,0),col# from ccol$ where con#=:1
>
> 0 3 0.0 0.0 0.00 0.00
> 1480155015
> SQL ordered by Reads for DB: EUROPA Instance: europa Snaps: 5 -6
> -> End Disk Reads Threshold: 1000
>
> CPU Elapsd
> Physical Reads Executions Reads per Exec %Total Time (s) Time (s)
> Hash Value
> --------------- ------------ -------------- ------ -------- ---------
> ----------
> select i.obj#,i.ts#,i.file#,i.block#,i.intcols,i.type#,i.flags,
> i.property,i.pctfree$,i.initrans,i.maxtrans,i.blevel,i.leafcnt,i
> .distkey, i.lblkkey,i.dblkkey,i.clufac,i.cols,i.analyzetime,i.sa
> mplesize,i.dataobj#, nvl(i.degree,1),nvl(i.instances,1),i.rowcnt
>
> -------------------------------------------------------------
> SQL ordered by Executions for DB: EUROPA Instance: europa Snaps: 5
> -6
> -> End Executions Threshold: 100
>
> CPU per Elap per
> Executions Rows Processed Rows per Exec Exec (s) Exec (s)
> Hash Value
> ------------ --------------- ---------------- ----------- ----------
> ----------
> 5,754 0 0.0 0.00 0.00
> 1053795750
> Module: SQL*Plus
> COMMIT
>
> 3,771 0 0.0 0.00 0.00
> 94568782
> Module: JDBC Thin Client
> LOCK table COMMAND_LCK in exclusive mode
>
> 3,771 0 0.0 0.00 0.00
> 1092438951
> Module: JDBC Thin Client
> SELECT ID, SUBCLASS, STATUS, LEASE_ID from COMMAND where ( STAT
> US = 1 or (STATUS = 2 and EXPIRATION < sysdate) ) and FIRINGTI
> ME <= :b2 and ( TYPE_MASK < :b1 or TYPE_MASK > :b1 ) order by
> FIRINGTIME
>
> 3,771 3,771 1.0 0.00 0.00
> 3918312570
> Module: JDBC Thin Client
> BEGIN GET_COMMAND(:1,:2,:3,:4,:5,:6,:7); END;
>
> 2,640 2,640 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
>
> 1,984 110,123 55.5 1.76 1.86
> 1168063042
> Module: SQL*Plus
> INSERT into LL_RESULTS_TABLE SELECT t0.SEQID, t0.SITEID,
> t0.ROLE, t1.LEADNAME, t2.COMPANYNAME, t3.PRODUCTNAM
> E, t4.FIRST_NAME, t4.LAST_NAME, t1.LASTMODIFIED, t5.
> CAMPAIGN_NAME, t0.STATUS, t0.RANKSUMMARY, t1.SIZE_OF_OP
> PORTUNITY, t1.SIZE_OF_OPPORTUNITY_CURRENCY, t0.SUGGESTED_A
>
> 1,983 1,983 1.0 0.00 0.00
> 3724654729
> Module: SQL*Plus
> INSERT into USER_LL_TIME_TABLE (USERSEQID,USERNAME,LEADBOXSEQ
> ,HUND_SECS ) values (:b5, :b4, :b3, (:b2 - :b1))
>
> 760 760 1.0 0.00 0.00
> 77789303
> Module: JDBC Thin Client
> UPDATE HEARTBEATRECORD SET BEATS = :1, LAST_BEAT = :2 WHERE (ID
> = :3)
>
> 760 760 1.0 0.00 0.00
> 190493219
> Module: JDBC Thin Client
> SELECT BEATS, ID, LAST_BEAT FROM HEARTBEATRECORD WHERE (ID = :1)
>
>
> 746 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
>
> SQL ordered by Executions for DB: EUROPA Instance: europa Snaps: 5
> -6
> -> End Executions Threshold: 100
>
> CPU per Elap per
> Executions Rows Processed Rows per Exec Exec (s) Exec (s)
> Hash Value
> ------------ --------------- ---------------- ----------- ----------
> ----------
> 746 746 1.0 0.00 0.00
> 1693927332
> select count(*) from sys.job$ where (next_date > sysdate) and (n
> ext_date < (sysdate+5/86400))
>
> 120 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
>
> 120 2,640 22.0 0.01 0.02
> 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
>
> 120 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
>
> 120 0 0.0 0.00 0.00
> 994140048
> 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_
>
> 120 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
>
> 120 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
>
> 120 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
>
> 120 0 0.0 0.00 0.00
> 3920324236
> SQL ordered by Executions for DB: EUROPA Instance: europa Snaps: 5
> -6
> -> End Executions Threshold: 100
>
> CPU per Elap per
> Executions Rows Processed Rows per Exec Exec (s) Exec (s)
> Hash Value
> ------------ --------------- ---------------- ----------- ----------
> ----------
> select q_name, state, delay, expiration, rowid, msgid, dequeue
>
> -------------------------------------------------------------
> SQL ordered by Parse Calls for DB: EUROPA Instance: europa Snaps: 5
> -6
> -> End Parse Calls Threshold: 1000
>
> % Total
> Parse Calls Executions Parses Hash Value
> ------------ ------------ -------- ----------
> 3,771 3,771 48.98 3918312570
> Module: JDBC Thin Client
> BEGIN GET_COMMAND(:1,:2,:3,:4,:5,:6,:7); END;
>
> 760 760 9.87 77789303
> Module: JDBC Thin Client
> UPDATE HEARTBEATRECORD SET BEATS = :1, LAST_BEAT = :2 WHERE (ID
> = :3)
>
> 760 760 9.87 190493219
> Module: JDBC Thin Client
> SELECT BEATS, ID, LAST_BEAT FROM HEARTBEATRECORD WHERE (ID = :1)
>
>
> 120 120 1.56 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
>
> 120 120 1.56 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
>
> 120 120 1.56 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
>
> 120 120 1.56 994140048
> 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_
>
> 120 120 1.56 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
>
> 120 120 1.56 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
> SQL ordered by Parse Calls for DB: EUROPA Instance: europa Snaps: 5
> -6
> -> End Parse Calls Threshold: 1000
>
> % Total
> Parse Calls Executions Parses Hash Value
> ------------ ------------ -------- ----------
> ocol from ODM.DMS_QUEUE_TABLE where time_manager_info <= :1
>
> 120 2,640 1.56 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
>
> 120 120 1.56 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
>
> 120 120 1.56 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
>
> 8 10 0.10 1644394536
> select obj#,type#,ctime,mtime,stime,status,dataobj#,flags,oid$,
> spare1, spare2 from obj$ where owner#=:1 and name=:2 and namespa
> ce=:3 and(remoteowner=:4 or remoteowner is null and :4 is null)a
> nd(linkname=:5 or linkname is null and :5 is null)and(subname=:6
> or subname is null and :6 is null)
>
> 8 29 0.10 3665763022
> update sys.col_usage$ set equality_preds = equality_preds
> + decode(bitand(:flag,1),0,0,1), equijoin_preds = equijoi
> n_preds + decode(bitand(:flag,2),0,0,1), nonequijoin_preds
> = nonequijoin_preds + decode(bitand(:flag,4),0,0,1), range_pre
> ds = range_preds + decode(bitand(:flag,8),0,0,1),
>
> 3 3 0.04 1006414593
> select type#,blocks,extents,minexts,maxexts,extsize,extpct,user#
> ,iniexts,NVL(lists,65535),NVL(groups,65535),cachehint,hwmincr, N
> VL(spare1,0) from seg$ where ts#=:1 and file#=:2 and block#=:3
>
> 2 3 0.03 204386021
> select col#, grantee#, privilege#,max(mod(nvl(option$,0),2)) fro
> m objauth$ where obj#=:1 and col# is not null group by privilege
>
> -------------------------------------------------------------
> Instance Activity Stats for DB: EUROPA Instance: europa Snaps: 5 -6
>
> Statistic Total per Second
> per Trans
> --------------------------------- ------------------ --------------
> ------------
> CPU used by this session 349,762 91.5
> 127.3
> CPU used when call started 349,762 91.5
> 127.3
> CR blocks created 146 0.0
> 0.1
> SQL*Net roundtrips to/from client 44,769 11.7
> 16.3
> active txn count during cleanout 2,365 0.6
> 0.9
> background timeouts 4,488 1.2
> 1.6
> buffer is not pinned count 14,853,749 3,885.4
> 5,407.3
> buffer is pinned count 117,874 30.8
> 42.9
> bytes received via SQL*Net from c 1,082,687 283.2
> 394.1
> bytes sent via SQL*Net to client 485,421 127.0
> 176.7
> calls to get snapshot scn: kcmgss 60,513 15.8
> 22.0
> calls to kcmgas 10,994 2.9
> 4.0
> calls to kcmgcs 2,580 0.7
> 0.9
> change write time 267 0.1
> 0.1
> cleanout - number of ktugct calls 2,367 0.6
> 0.9
> cleanouts only - consistent read 0 0.0
> 0.0
> cluster key scan block gets 206 0.1
> 0.1
> cluster key scans 65 0.0
> 0.0
> commit cleanout failures: callbac 0 0.0
> 0.0
> commit cleanouts 14,042 3.7
> 5.1
> commit cleanouts successfully com 14,042 3.7
> 5.1
> commit txn count during cleanout 14 0.0
> 0.0
> consistent changes 5,662 1.5
> 2.1
> consistent gets 14,899,982 3,897.5
> 5,424.1
> consistent gets - examination 26,126 6.8
> 9.5
> cursor authentications 5 0.0
> 0.0
> data blocks consistent reads - un 5,662 1.5
> 2.1
> db block changes 279,232 73.0
> 101.7
> db block gets 162,975 42.6
> 59.3
> deferred (CURRENT) block cleanout 7,458 2.0
> 2.7
> enqueue conversions 27 0.0
> 0.0
> enqueue releases 70,887 18.5
> 25.8
> enqueue requests 70,887 18.5
> 25.8
> execute count 27,986 7.3
> 10.2
> free buffer requested 124,774 32.6
> 45.4
> immediate (CR) block cleanout app 0 0.0
> 0.0
> immediate (CURRENT) block cleanou 26 0.0
> 0.0
> index fetch by key 10,249 2.7
> 3.7
> index scans kdiixs1 13,528 3.5
> 4.9
> leaf node 90-10 splits 2 0.0
> 0.0
> leaf node splits 11 0.0
> 0.0
> logons cumulative 3 0.0
> 0.0
> messages received 6,814 1.8
> 2.5
> messages sent 6,814 1.8
> 2.5
> no buffer to keep pinned count 0 0.0
> 0.0
> no work - consistent read gets 14,835,801 3,880.7
> 5,400.7
> opened cursors cumulative 7,694 2.0
> 2.8
> parse count (failures) 2 0.0
> 0.0
> parse count (hard) 14 0.0
> 0.0
> parse count (total) 7,699 2.0
> 2.8
> parse time cpu 181 0.1
> 0.1
> parse time elapsed 194 0.1
> 0.1
> physical reads 5,148,634 1,346.8
> 1,874.3
> physical reads direct 5,030,347 1,315.8
> 1,831.2
> physical writes 5,035,975 1,317.3
> 1,833.3
> physical writes direct 5,035,975 1,317.3
> 1,833.3
> Instance Activity Stats for DB: EUROPA Instance: europa Snaps: 5 -6
>
> Statistic Total per Second
> per Trans
> --------------------------------- ------------------ --------------
> ------------
> physical writes non checkpoint 5,035,975 1,317.3
> 1,833.3
> prefetched blocks 76,174 19.9
> 27.7
> process last non-idle time 3,183,980,249 832,848.6
> 1,159,075.5
> recursive calls 45,674 12.0
> 16.6
> recursive cpu usage 349,197 91.3
> 127.1
> redo blocks written 117,699 30.8
> 42.9
> redo entries 146,142 38.2
> 53.2
> redo size 56,584,928 14,801.2
> 20,598.8
> redo synch time 73 0.0
> 0.0
> redo synch writes 765 0.2
> 0.3
> redo wastage 1,815,868 475.0
> 661.0
> redo write time 386 0.1
> 0.1
> redo writes 6,816 1.8
> 2.5
> rollbacks only - consistent read 626 0.2
> 0.2
> rows fetched via callback 8,700 2.3
> 3.2
> session connect time 3,183,980,249 832,848.6
> 1,159,075.5
> session logical reads 15,062,957 3,940.1
> 5,483.4
> session pga memory 163,484 42.8
> 59.5
> session pga memory max 42,340 11.1
> 15.4
> session uga memory 34,664 9.1
> 12.6
> session uga memory max 1,343,912 351.5
> 489.2
> shared hash latch upgrades - no w 14,085 3.7
> 5.1
> sorts (memory) 964 0.3
> 0.4
> sorts (rows) 14,751 3.9
> 5.4
> switch current to new buffer 2,644 0.7
> 1.0
> table fetch by rowid 119,341 31.2
> 43.4
> table fetch continued row 0 0.0
> 0.0
> table scan blocks gotten 14,724,622 3,851.6
> 5,360.3
> table scan rows gotten 488,553,803 127,793.3
> 177,850.0
> table scans (long tables) 1,384 0.4
> 0.5
> table scans (short tables) 5,138 1.3
> 1.9
> user calls 20,604 5.4
> 7.5
> user commits 2,747 0.7
> 1.0
> workarea executions - onepass 692 0.2
> 0.3
> workarea executions - optimal 2,885 0.8
> 1.1
> -------------------------------------------------------------
> Tablespace IO Stats for DB: EUROPA Instance: europa Snaps: 5 -6
> ->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)
> -------------- ------- ------ ------- ------------ -------- ----------
> ------
> GENDB2_TMP
> 718,621 188 1.5 7.0 719,425 188 0
> 0.0
> GENDB2_PRIM
> 42,100 11 1.8 2.8 0 0 0
> 0.0
> USERS
> 7 0 5.7 1.0 0 0 0
> 0.0
> SYSTEM
> 4 0 10.0 1.0 0 0 0
> 0.0
> -------------------------------------------------------------
> File IO Stats for DB: EUROPA Instance: europa Snaps: 5 -6
> ->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)
> -------------- ------- ------ ------- ------------ -------- ----------
> ------
> GENDB2_PRIM F:\ORACLE\ORADATA\EUROPA\GENDB2_PRIM.ORA
> 42,100 11 1.8 2.8 0 0 0
>
> GENDB2_TMP F:\ORACLE\ORADATA\EUROPA\GENDB2_TMP.ORA
> 718,621 188 1.5 7.0 719,425 188 0
>
> SYSTEM F:\ORACLE\ORADATA\EUROPA\SYSTEM01.DBF
> 4 0 10.0 1.0 0 0 0
>
> USERS F:\ORACLE\ORADATA\EUROPA\USERS01.DBF
> 7 0 5.7 1.0 0 0 0
>
> -------------------------------------------------------------
> Buffer Pool Statistics for DB: EUROPA Instance: europa Snaps: 5 -6
> -> 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 64,064 99.2 15,062,873 118,285 0 0
> 0 1
> -------------------------------------------------------------
>
> Instance Recovery Stats for DB: EUROPA Instance: europa Snaps: 5 -6
> -> 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 13 503 13103 471852 471852
> E 0 24 4251 130802 471852 471852
> -------------------------------------------------------------
>
> Buffer Pool Advisory for DB: EUROPA Instance: europa End Snap: 6
> -> 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 48 .1 6,006 225.60
> 33,031,246
> D 96 .2 12,012 171.61
> 25,126,773
> D 144 .3 18,018 166.16
> 24,329,323
> D 192 .4 24,024 165.99
> 24,303,445
> D 240 .5 30,030 165.89
> 24,288,864
> D 288 .6 36,036 1.67
> 244,028
> D 336 .7 42,042 1.38
> 202,396
> D 384 .8 48,048 1.23
> 180,373
> D 432 .8 54,054 1.13
> 164,719
> D 480 .9 60,060 1.05
> 153,021
> D 512 1.0 64,064 1.00
> 146,417
> D 528 1.0 66,066 0.98
> 143,903
> D 576 1.1 72,072 0.95
> 139,344
> D 624 1.2 78,078 0.94
> 137,702
> D 672 1.3 84,084 0.94
> 137,702
> D 720 1.4 90,090 0.94
> 137,702
> D 768 1.5 96,096 0.94
> 137,702
> D 816 1.6 102,102 0.94
> 137,702
> D 864 1.7 108,108 0.94
> 137,702
> D 912 1.8 114,114 0.94
> 137,702
> D 960 1.9 120,120 0.94
> 137,702
> -------------------------------------------------------------
>
>
> Buffer wait Statistics for DB: EUROPA Instance: europa Snaps: 5 -6
> -> ordered by wait time desc, waits desc
>
> Tot Wait Avg
> Class Waits Time (s) Time (ms)
> ------------------ ----------- ---------- ---------
> undo block 1 0 0
> -------------------------------------------------------------
> PGA Aggr Target Stats for DB: EUROPA Instance: europa Snaps: 5 -6
> -> 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
> --------------- ---------------- -------------------------
> 54.1 12,723 10,817
>
> %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 26 17 10.5 0.0 .0 .0 .0
> 1,331
> E 26 17 10.7 0.0 .0 .0 .0
> 1,331
> -------------------------------------------------------------
>
> PGA Aggr Target Histogram for DB: EUROPA Instance: europa Snaps: 5
> -6
> -> Optimal Executions are purely in-memory operations
>
> Low High
> Optimal Optimal Total Execs Optimal Execs 1-Pass Execs M-Pass Execs
> ------- ------- -------------- ------------- ------------ ------------
> 8K 16K 105 105 0 0
> 16K 32K 9 9 0 0
> 32K 64K 1 1 0 0
> 64K 128K 1 1 0 0
> 256K 512K 3 3 0 0
> 512K 1024K 2,766 2,766 0 0
> 8M 16M 692 0 692 0
> -------------------------------------------------------------
>
> PGA Memory Advisory for DB: EUROPA Instance: europa End Snap: 6
> -> 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
> ---------- ------- ---------------- ---------------- --------
> ----------
> 13 0.5 12,739.8 50,881.4 20.0
> 0
> 20 0.8 12,739.8 43,266.2 23.0
> 0
> 26 1.0 12,739.8 10,816.6 54.0
> 0
> 31 1.2 12,739.8 10,816.6 54.0
> 0
> 36 1.4 12,739.8 10,816.6 54.0
> 0
> 42 1.6 12,739.8 10,816.6 54.0
> 0
> 47 1.8 12,739.8 10,816.6 54.0
> 0
> 52 2.0 12,739.8 10,816.6 54.0
> 0
> 78 3.0 12,739.8 10,816.6 54.0
> 0
> 104 4.0 12,739.8 10,816.6 54.0
> 0
> 156 6.0 12,739.8 10,816.6 54.0
> 0
> 208 8.0 12,739.8 10,816.6 54.0
> 0
> -------------------------------------------------------------
> Rollback Segment Stats for DB: EUROPA Instance: europa Snaps: 5 -6
> ->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 13.0 0.00 0 0 0
> 0
> 1 1,817.0 0.00 870,268 1 0
> 0
> 2 2,059.0 0.00 935,370 3 0
> 0
> 3 2,010.0 0.00 919,822 2 0
> 0
> 4 1,725.0 0.00 901,054 1 0
> 0
> 5 2,340.0 0.00 824,998 1 0
> 0
> 6 1,798.0 0.00 811,812 2 0
> 0
> 7 1,995.0 0.00 952,490 1 0
> 0
> 8 2,269.0 0.00 700,982 1 0
> 0
> 9 1,787.0 0.00 816,464 1 0
> 0
> 10 2,233.0 0.00 844,066 1 0
> 0
> -------------------------------------------------------------
> Rollback Segment Storage for DB: EUROPA Instance: europa Snaps: 5
> -6
> ->Optimal Size should be larger than Avg Active
>
> RBS No Segment Size Avg Active Optimal Size Maximum Size
> ------ --------------- --------------- --------------- ---------------
> 0 385,024 0 385,024
> 1 2,220,032 104,857 2,220,032
> 2 2,220,032 96,648 2,220,032
> 3 2,220,032 100,105 2,220,032
> 4 2,220,032 104,857 2,220,032
> 5 2,220,032 104,857 2,220,032
> 6 2,220,032 100,105 2,220,032
> 7 3,268,608 104,857 3,268,608
> 8 2,220,032 104,857 2,220,032
> 9 3,268,608 104,857 3,268,608
> 10 2,220,032 104,857 2,220,032
> -------------------------------------------------------------
> Undo Segment Summary for DB: EUROPA Instance: europa Snaps: 5 -6
> -> 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,065 73,805 3,612 1 0 0
> 0/0/0/0/0/0
> -------------------------------------------------------------
>
>
> Undo Segment Stats for DB: EUROPA Instance: europa Snaps: 5 -6
> -> 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
> ------------ ------------ -------- ------- -------- ------- ------
> -------------
> 19-Aug 17:35 169 14,436 3,612 1 0 0
> 0/0/0/0/0/0
> 19-Aug 17:25 169 13,160 3,013 1 0 0
> 0/0/0/0/0/0
> 19-Aug 17:15 173 11,853 2,411 1 0 0
> 0/0/0/0/0/0
> 19-Aug 17:05 169 10,542 1,812 1 0 0
> 0/0/0/0/0/0
> 19-Aug 16:55 170 9,256 1,210 1 0 0
> 0/0/0/0/0/0
> 19-Aug 16:45 168 7,940 611 1 0 0
> 0/0/0/0/0/0
> 19-Aug 16:35 47 6,618 12 1 0 0
> 0/0/0/0/0/0
> -------------------------------------------------------------
> Latch Activity for DB: EUROPA Instance: europa Snaps: 5 -6
> ->"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 6,816 0.0 0
> 0
> FOB s.o list latch 18 0.0 0
> 0
> SQL memory manager latch 1 0.0 0
> 1,242 0.0
> SQL memory manager worka 100,573 0.0 0
> 0
> active checkpoint queue 1,242 0.0 0
> 0
> archive control 1 0.0 0
> 0
> cache buffer handles 4,068 0.0 0
> 0
> cache buffers chains 35,802,867 0.0 0.0 0
> 202,351 0.0
> cache buffers lru chain 2,644 0.0 0.0 0
> 14,836,228 0.0
> channel handle pool latc 5 0.0 0
> 0
> channel operations paren 2,504 0.0 0
> 0
> checkpoint queue latch 82,132 0.0 0
> 3,729 0.0
> child cursor hash table 151 0.0 0
> 0
> dml lock allocation 40,035 0.0 0.0 0
> 0
> dummy allocation 5 0.0 0
> 0
> enqueue hash chains 141,803 0.0 0.0 0
> 0
> enqueues 94,752 0.0 0.0 0
> 0
> event group latch 3 0.0 0
> 0
> hash table column usage 24 0.0 0
> 25,879 0.0
> job_queue_processes para 62 0.0 0
> 0
> kmcptab latch 40 0.0 0
> 0
> kmcpvec latch 0 0
> 40 0.0
> ktm global data 12 0.0 0
> 0
> kwqit: protect wakeup ti 120 0.0 0
> 0
> lgwr LWN SCN 7,021 0.0 0.0 0
> 0
> library cache 185,697 0.0 0.0 0
> 0
> library cache load lock 42 0.0 0
> 0
> library cache pin 118,287 0.0 0.0 0
> 0
> library cache pin alloca 49,284 0.0 0
> 0
> list of block allocation 15 0.0 0
> 0
> messages 36,818 0.1 0.0 0
> 0
> mostly latch-free SCN 7,054 0.1 0.0 0
> 0
> multiblock read objects 17,078 0.0 0
> 0
> ncodef allocation latch 61 0.0 0
> 0
> object stats modificatio 57 0.0 0
> 0
> post/wait queue 1,152 0.0 0
> 764 0.0
> process allocation 3 0.0 0
> 3 0.0
> process group creation 5 0.0 0
> 0
> redo allocation 159,970 0.1 0.0 0
> 0
> redo copy 0 0
> 146,533 0.3
> redo writing 17,483 0.0 0
> 0
> row cache enqueue latch 74,479 0.0 0
> 0
> row cache objects 74,802 0.0 0
> 0
> sequence cache 12 0.0 0
> 0
> session allocation 29,769 0.0 0
> 0
> session idle bit 50,446 0.0 0
> 0
> session switching 61 0.0 0
> 0
> session timer 1,284 0.0 0
> 0
> shared pool 114,162 0.0 0.0 0
> 0
> simulator hash latch 957,780 0.0 0
> 0
> Latch Activity for DB: EUROPA Instance: europa Snaps: 5 -6
> ->"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
> ------------------------ -------------- ------ ------ ------
> ------------ ------
> simulator lru latch 10,314 0.0 0
> 7,000 0.0
> sort extent pool 45,040 0.0 0
> 0
> transaction allocation 9 0.0 0
> 0
> transaction branch alloc 61 0.0 0
> 0
> undo global data 33,033 0.0 0
> 0
> user lock 10 0.0 0
> 0
> virtual circuit buffers 221,628 0.0 0.0 0
> 0
> virtual circuit queues 89,288 0.0 0.0 0
> 0
> virtual circuits 40,872 0.0 0
> 0
> -------------------------------------------------------------
> Top 5 Logical Reads per Segment for DB: EUROPA Instance: europa
> Snaps: 5 -6
> -> End Segment Logical Reads Threshold: 10000
>
> Subobject Obj.
> Logical
> Owner Tablespace Object Name Name Type
> Reads %Total
> ---------- ---------- -------------------- ---------- -----
> ------------ -------
> GENDB2USER GENDB2_PRI CONTACT TABLE
> 8,516,416 57.35
> GENDB2USER GENDB2_PRI LEAD TABLE
> 6,206,224 41.80
> GENDB2USER GENDB2_PRI ASSIGNMENT TABLE
> 110,128 .74
> SYSTEM SYSTEM AQ$_QUEUE_TABLES TABLE
> 16,032 .11
> GENDB2USER GENDB2_PRI USERS TABLE
> 192 .00
> -------------------------------------------------------------
>
>
> Top 5 Physical Reads per Segment for DB: EUROPA Instance: europa
> Snaps: 5 -6
> -> End Segment Physical Reads Threshold: 1000
>
> Subobject Obj.
> Physical
> Owner Tablespace Object Name Name Type
> Reads %Total
> ---------- ---------- -------------------- ---------- -----
> ------------ -------
> GENDB2USER GENDB2_PRI LEAD TABLE
> 84,559 71.83
> GENDB2USER GENDB2_PRI ASSIGNMENT TABLE
> 33,005 28.04
> GENDB2USER GENDB2_PRI CONTACT TABLE
> 163 .14
> -------------------------------------------------------------
> Dictionary Cache Stats for DB: EUROPA Instance: europa Snaps: 5 -6
> ->"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_histogram_defs 84 39.3 0 0
> 847
> dc_object_ids 4,275 0.0 0 0
> 460
> dc_objects 886 1.1 0 0
> 485
> dc_profiles 3 0.0 0 0
> 1
> dc_rollback_segments 273 0.0 0 0
> 22
> dc_segments 2,519 0.1 0 0
> 466
> dc_tablespace_quotas 26 3.8 0 26
> 1
> dc_tablespaces 13,363 0.0 0 0
> 5
> dc_user_grants 112 0.9 0 0
> 16
> dc_usernames 19 5.3 0 0
> 13
> dc_users 15,795 0.0 0 0
> 26
> -------------------------------------------------------------
>
>
> Library Cache Activity for DB: EUROPA Instance: europa Snaps: 5 -6
> ->"Pct Misses" should be very low
>
> Get Pct Pin Pct
> Invali-
> Namespace Requests Miss Requests Miss Reloads
> dations
> --------------- ------------ ------ -------------- ------ ----------
> --------
> BODY 12 0.0 12 0.0 0
> 0
> CLUSTER 6 0.0 9 0.0 0
> 0
> INDEX 840 0.0 840 0.0 0
> 0
> SQL AREA 7,681 0.2 46,175 0.1 1
> 0
> TABLE/PROCEDURE 6,528 0.2 10,325 0.2 0
> 0
> TRIGGER 4 0.0 4 0.0 0
> 0
> -------------------------------------------------------------
> Shared Pool Advisory for DB: EUROPA Instance: europa End Snap: 6
> -> 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
> ----------- ----- ---------- ------------ ------------ -------
> ---------------
> 64 .5 9 2,408 352 1.0
> 65,687
> 80 .6 9 2,408 352 1.0
> 65,687
> 96 .8 9 2,408 352 1.0
> 65,687
> 112 .9 9 2,408 352 1.0
> 65,687
> 128 1.0 9 2,408 352 1.0
> 65,687
> 144 1.1 9 2,408 352 1.0
> 65,687
> 160 1.3 9 2,408 352 1.0
> 65,687
> 176 1.4 9 2,408 352 1.0
> 65,687
> 192 1.5 9 2,408 352 1.0
> 65,687
> 208 1.6 9 2,408 352 1.0
> 65,687
> 224 1.8 9 2,408 352 1.0
> 65,687
> 240 1.9 9 2,408 352 1.0
> 65,687
> 256 2.0 9 2,408 352 1.0
> 65,687
> -------------------------------------------------------------
> SGA Memory Summary for DB: EUROPA Instance: europa Snaps: 5 -6
>
> SGA regions Size in Bytes
> ------------------------------ ----------------
> Database Buffers 536,870,912
> Fixed Size 455,876
> Redo Buffers 1,191,936
> Variable Size 511,705,088
> ----------------
> sum 1,050,223,812
> -------------------------------------------------------------
>
>
> SGA breakdown difference for DB: EUROPA Instance: europa Snaps: 5 -6
>
> Pool Name Begin value End
> value % Diff
> ------ ------------------------------ ----------------
> ---------------- -------
> large free memory 32,690,040
> 32,690,040 0.00
> large session heap 864,392
> 864,392 0.00
> shared 1M buffer 2,098,176
> 2,098,176 0.00
> shared Checkpoint queue 564,608
> 564,608 0.00
> shared DML lock 294,480
> 294,480 0.00
> shared FileIdentificatonBlock 323,292
> 323,292 0.00
> shared FileOpenBlock 1,999,244
> 1,999,244 0.00
> shared KGK heap 3,756
> 3,756 0.00
> shared KGLS heap 2,152,284
> 2,174,744 1.04
> shared KQR M PO 912,404
> 934,932 2.47
> shared KQR S PO 129,348
> 130,628 0.99
> shared KQR S SO 3,840
> 4,096 6.67
> 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 445,468
> 453,020 1.70
> shared PL/SQL MPCODE 188,796
> 196,328 3.99
> shared PLS non-lib hp 2,068
> 2,068 0.00
> shared VIRTUAL CIRCUITS 774,680
> 774,680 0.00
> shared character set object 318,524
> 318,524 0.00
> shared db_handles 324,000
> 324,000 0.00
> shared dictionary cache 1,614,976
> 1,614,976 0.00
> shared enqueue 471,080
> 471,080 0.00
> shared event statistics per sess 5,558,000
> 5,558,000 0.00
> shared fixed allocation callback 264
> 264 0.00
> shared free memory 119,148,048
> 118,774,852 -0.31
> shared joxs heap init 4,220
> 4,220 0.00
> shared kgl simulator 614,792
> 623,264 1.38
> shared library cache 3,462,716
> 3,522,716 1.73
> shared message pool freequeue 834,752
> 834,752 0.00
> shared miscellaneous 7,256,756
> 7,276,304 0.27
> shared parameters 10,440
> 11,484 10.00
> shared processes 432,000
> 432,000 0.00
> shared sessions 1,208,000
> 1,208,000 0.00
> shared sim memory hea 286,940
> 286,940 0.00
> shared sql area 5,492,468
> 5,714,880 4.05
> shared table definiti 3,936
> 4,048 2.85
> shared transaction 570,020
> 570,020 0.00
> shared trigger defini 3,804
> 3,804 0.00
> shared trigger inform 1,176
> 1,176 0.00
> shared trigger source 160
> 160 0.00
> buffer_cache 536,870,912
> 536,870,912 0.00
> fixed_sga 455,876
> 455,876 0.00
> log_buffer 1,180,672
> 1,180,672 0.00
> -------------------------------------------------------------
> init.ora Parameters for DB: EUROPA Instance: europa Snaps: 5 -6
>
> End
> value
> Parameter Name Begin value (if
> different)
> ----------------------------- ---------------------------------
> --------------
> O7_DICTIONARY_ACCESSIBILITY TRUE
> aq_tm_processes 1
> background_dump_dest F:\oracle\admin\europa\bdump
> compatible 9.2.0.0.0
> control_files F:\oracle\oradata\europa\CONTROL0
> core_dump_dest F:\oracle\admin\europa\cdump
> db_block_size 8192
> db_cache_size 536870912
> db_domain
> db_file_multiblock_read_count 16
> db_name europa
> dispatchers (PROTOCOL=TCP) (SERVICE=europaXDB
> fast_start_mttr_target 0
> hash_join_enabled TRUE
> instance_name europa
> java_pool_size 0
> job_queue_processes 10
> large_pool_size 33554432
> log_buffer 1048576
> log_checkpoint_timeout 0
> open_cursors 300
> pga_aggregate_target 27262976
> processes 450
> query_rewrite_enabled FALSE
> remote_login_passwordfile EXCLUSIVE
> sga_max_size 1050223812
> shared_pool_size 134217728
> sort_area_size 5000000
> star_transformation_enabled FALSE
> timed_statistics TRUE
> undo_management AUTO
> undo_retention 10800
> undo_tablespace UNDOTBS1
> user_dump_dest F:\oracle\admin\europa\udump
> -------------------------------------------------------------
>
> End of Report

You can try for analyze all schema object (table & index)(DBMS.ANALYZE package). Received on Sat Sep 06 2003 - 09:48:28 CDT