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

Marco <brilli.m_at_tiscali.it> wrote in message news:<4c0t25b0wopm.1oybe5b6qtocp.dlg_at_40tude.net>...
>
> You can try for analyze all schema object (table & index)(DBMS.ANALYZE
> package).

The schema is being analyzed in its entirety prior to running performance tests... Data is static -- these are all queries.

> 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

> Received on Sat Sep 06 2003 - 13:04:07 CDT