| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> URGENT: Slow Performance after migration to 9.2.0.3 from 8.1.7
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
------------ ----------- ------------ -------- ----------- -------
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
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
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
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
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$,fixedstorage,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
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
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
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
------------ --------------- ---------------- ----------- ----------
% Total
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
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
Statistic Total per Secondper 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
Statistic Total per Secondper 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)
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 TimeoutInterval
(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
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
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
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:
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
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 00
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
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 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 Endvalue % 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)
----------------------------- ---------------------------------
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 Fri Sep 05 2003 - 22:33:32 CDT
 |
 |