STATSPACK report for
Database DB Id Instance Inst Num Startup Time Release RAC
~~~~~~~~ ----------- ------------ -------- --------------- ----------- ---
1568982754 november 1 29-Nov-09 12:37 11.1.0.6.0 NO
Host Name Platform CPUs Cores Sockets Memory (G)
~~~~ ---------------- ---------------------- ----- ----- ------- ------------
MY Microsoft Windows IA ( 2 0 0 1.0
Snapshot Snap Id Snap Time Sessions Curs/Sess Comment
~~~~~~~~ ---------- ------------------ -------- --------- -------------------
Begin Snap: 1 29-Nov-09 13:22:24 45 3.0
End Snap: 2 29-Nov-09 13:38:40 45 3.3
Elapsed: 16.27 (mins)
DB time: 152.67 (mins) DB CPU: 0.55 (mins)
Cache Sizes Begin End
~~~~~~~~~~~ ---------- ----------
Buffer Cache: 136M Std Block Size: 8K
Shared Pool: 136M Log Buffer: 5,754K
Load Profile Per Second Per Transaction Per Exec Per Call
~~~~~~~~~~~~ ------------------ ----------------- ----------- -----------
DB time(s): 9.4 34.4 0.80 5.38
DB CPU(s): 0.0 0.1 0.00 0.02
Redo size: 29,927.2 109,808.2
Logical reads: 622.4 2,283.6
Block changes: 368.0 1,350.3
Physical reads: 52.2 191.7
Physical writes: 52.9 194.0
User calls: 1.7 6.4
Parses: 5.4 19.7
Hard parses: 0.6 2.2
W/A MB processed: 0.1 0.5
Logons: 0.1 0.3
Executes: 11.7 43.0
Rollbacks: 0.0 0.1
Transactions: 0.3
Instance Efficiency Indicators
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 99.57 Redo NoWait %: 100.00
Buffer Hit %: 91.61 Optimal W/A Exec %: 100.00
Library Hit %: 89.65 Soft Parse %: 88.88
Execute to Parse %: 54.20 Latch Hit %: 99.95
Parse CPU to Parse Elapsd %: 3.97 % Non-Parse CPU: 92.20
Shared Pool Statistics Begin End
------ ------
Memory Usage %: 89.03 85.43
% SQL with executions>1: 60.64 66.71
% Memory for SQL w/exec>1: 68.06 69.50
Top 5 Timed Events Avg %Total
~~~~~~~~~~~~~~~~~~ wait Call
Event Waits Time (s) (ms) Time
----------------------------------------- ------------ ----------- ------ ------
db file sequential read 47,704 6,073 127 65.0
wait for a undo record 8,648 943 109 10.1
wait for stopper event to be increased 8,561 931 109 10.0
db file parallel write 12,707 467 37 5.0
read by other session 1,946 201 103 2.2
-------------------------------------------------------------
Host CPU (CPUs: 2 Cores: 0 Sockets: 0)
~~~~~~~~ Load Average
Begin End User System Idle WIO WCPU
------- ------- ------- ------- ------- ------- --------
4.17 4.69 91.15
Instance CPU
~~~~~~~~~~~~
% of total CPU for Instance: 1.88
% of busy CPU for Instance: 21.28
%DB time waiting for CPU - Resource Mgr: 0.00
Memory Statistics Begin End
~~~~~~~~~~~~~~~~~ ------------ ------------
Host Mem (MB): 1,022.1 1,022.1
SGA use (MB): 407.1 407.1
PGA use (MB): 93.4 93.4
% Host Mem used for SGA+PGA: 49.0 49.0
-------------------------------------------------------------
Time Model System Stats DB/Inst: NOVEMBER/november Snaps: 1-2
-> Ordered by % of DB time desc, Statistic name
Statistic Time (s) % DB time
----------------------------------- -------------------- ---------
sql execute elapsed time 5,186.4 56.6
PL/SQL execution elapsed time 1,057.6 11.5
parse time elapsed 193.2 2.1
hard parse elapsed time 178.0 1.9
hard parse (sharing criteria) elaps 40.6 .4
DB CPU 33.2 .4
hard parse (bind mismatch) elapsed 24.3 .3
PL/SQL compilation elapsed time 14.4 .2
connection management call elapsed 4.4 .0
sequence load elapsed time 0.2 .0
repeated bind elapsed time 0.0 .0
DB time 9,160.5
background elapsed time 2,210.4
background cpu time 4.2
-------------------------------------------------------------
Wait Events DB/Inst: NOVEMBER/november Snaps: 1-2
-> s - second, cs - centisecond, ms - millisecond, us - microsecond
-> %Timeouts: value of 0 indicates value was < .5%. Value of null is truly 0
-> Only events with Total Wait Time (s) >= .001 are shown
-> ordered by Total Wait Time desc, Waits desc (idle events last)
Avg %Total
%Tim Total Wait wait Waits Call
Event Waits out Time (s) (ms) /txn Time
---------------------------- ------------ ---- ---------- ------ -------- ------
db file sequential read 47,704 0 6,073 127 179.3 65.0
wait for a undo record 8,648 100 943 109 32.5 10.1
wait for stopper event to be 8,561 100 931 109 32.2 10.0
db file parallel write 12,707 0 467 37 47.8 5.0
read by other session 1,946 1 201 103 7.3 2.2
control file sequential read 1,071 0 183 171 4.0 2.0
resmgr:cpu quantum 518 0 82 159 1.9 .9
enq: PR - contention 1 0 81 80755 0.0 .9
log file parallel write 651 0 80 122 2.4 .9
os thread startup 82 74 63 770 0.3 .7
control file parallel write 385 0 60 156 1.4 .6
db file scattered read 191 0 28 144 0.7 .3
enq: TX - row lock contentio 1 0 24 24279 0.0 .3
log file sync 143 0 24 168 0.5 .3
latch: shared pool 33 0 24 725 0.1 .3
buffer busy waits 624 2 22 35 2.3 .2
enq: PS - contention 7 0 19 2718 0.0 .2
enq: UL - contention 1 0 6 5668 0.0 .1
cursor: pin S wait on X 194 100 3 16 0.7 .0
rdbms ipc reply 1 0 1 1347 0.0 .0
Data file init write 3 0 1 296 0.0 .0
SQL*Net break/reset to clien 218 0 1 2 0.8 .0
PX Deq: Txn Recovery Start 24 0 0 20 0.1 .0
enq: CF - contention 1 0 0 388 0.0 .0
latch free 26 0 0 12 0.1 .0
library cache load lock 1 0 0 243 0.0 .0
db file single write 1 0 0 193 0.0 .0
direct path write 2 0 0 93 0.0 .0
PX Deq: Join ACK 13 0 0 14 0.0 .0
latch: row cache objects 33 0 0 3 0.1 .0
kksfbc child completion 2 50 0 31 0.0 .0
latch: cache buffers chains 39 0 0 0 0.1 .0
latch: cache buffers lru cha 8 0 0 0 0.0 .0
rdbms ipc message 4,395 79 10,574 2406 16.5
SQL*Net message from client 1,206 0 3,866 3206 4.5
Streams AQ: waiting for time 3 67 2,742 ###### 0.0
DIAG idle wait 1,965 100 1,964 1000 7.4
Space Manager: slave idle wa 377 98 1,861 4937 1.4
SQL*Net more data from clien 43 0 1,342 31200 0.2
virtual circuit status 33 100 990 30013 0.1
Streams AQ: qmn coordinator 59 59 981 16619 0.2
Streams AQ: qmn slave idle w 37 0 980 26486 0.1
dispatcher timer 16 100 960 60013 0.1
pmon timer 378 90 912 2413 1.4
fbar timer 3 100 899 ###### 0.0
jobq slave wait 152 98 451 2967 0.6
wait for unread message on b 101 100 101 999 0.4
PX Idle Wait 60 37 60 997 0.2
Streams AQ: waiting for mess 12 100 56 4660 0.0
class slave wait 8 25 11 1431 0.0
Wait Events DB/Inst: NOVEMBER/november Snaps: 1-2
-> s - second, cs - centisecond, ms - millisecond, us - microsecond
-> %Timeouts: value of 0 indicates value was < .5%. Value of null is truly 0
-> Only events with Total Wait Time (s) >= .001 are shown
-> ordered by Total Wait Time desc, Waits desc (idle events last)
Avg %Total
%Tim Total Wait wait Waits Call
Event Waits out Time (s) (ms) /txn Time
---------------------------- ------------ ---- ---------- ------ -------- ------
SQL*Net message to client 1,205 0 0 0 4.5
-------------------------------------------------------------
Background Wait Events DB/Inst: NOVEMBER/november Snaps: 1-2
-> Only events with Total Wait Time (s) >= .001 are shown
-> ordered by Total Wait Time desc, Waits desc (idle events last)
Avg %Total
%Tim Total Wait wait Waits Call
Event Waits out Time (s) (ms) /txn Time
---------------------------- ------------ ---- ---------- ------ -------- ------
wait for stopper event to be 8,561 100 931 109 32.2 10.0
db file parallel write 12,707 0 467 37 47.8 5.0
control file sequential read 786 0 126 161 3.0 1.4
enq: PR - contention 1 0 81 80755 0.0 .9
log file parallel write 651 0 80 122 2.4 .9
os thread startup 82 74 63 770 0.3 .7
control file parallel write 385 0 60 156 1.4 .6
db file sequential read 258 0 42 162 1.0 .4
buffer busy waits 41 29 21 510 0.2 .2
enq: PS - contention 7 0 19 2718 0.0 .2
latch: shared pool 16 0 12 761 0.1 .1
db file scattered read 39 0 7 177 0.1 .1
read by other session 17 24 7 386 0.1 .1
rdbms ipc reply 1 0 1 1347 0.0 .0
Data file init write 3 0 1 296 0.0 .0
enq: CF - contention 1 0 0 388 0.0 .0
db file single write 1 0 0 193 0.0 .0
direct path write 2 0 0 93 0.0 .0
PX Deq: Join ACK 13 0 0 14 0.0 .0
log file sync 3 0 0 41 0.0 .0
latch free 21 0 0 5 0.1 .0
kksfbc child completion 2 50 0 31 0.0 .0
resmgr:cpu quantum 1 0 0 2 0.0 .0
rdbms ipc message 4,395 79 10,574 2406 16.5
Streams AQ: waiting for time 3 67 2,742 ###### 0.0
DIAG idle wait 1,965 100 1,964 1000 7.4
Space Manager: slave idle wa 377 98 1,861 4937 1.4
virtual circuit status 33 100 990 30013 0.1
Streams AQ: qmn coordinator 59 59 981 16619 0.2
Streams AQ: qmn slave idle w 37 0 980 26486 0.1
dispatcher timer 16 100 960 60013 0.1
pmon timer 378 90 912 2413 1.4
fbar timer 3 100 899 ###### 0.0
PX Idle Wait 60 37 60 997 0.2
class slave wait 8 25 11 1431 0.0
SQL*Net message from client 33 0 3 80 0.1
-------------------------------------------------------------
Foreground Wait Events DB/Inst: NOVEMBER/november Snaps: 1-2
-> Only events with Total Wait Time (s) >= .001 are shown
-> ordered by Total Wait Time desc, Waits desc (idle events last)
Avg %Total
%Tim Total Wait wait Waits Call
Event Waits out Time (s) (ms) /txn Time
---------------------------- ------------ ---- ---------- ------ -------- ------
db file sequential read 47,446 0 6,031 127 178.4 64.5
wait for a undo record 8,648 100 943 109 32.5 10.1
read by other session 1,929 0 195 101 7.3 2.1
resmgr:cpu quantum 517 0 82 159 1.9 .9
control file sequential read 285 0 57 199 1.1 .6
enq: TX - row lock contentio 1 0 24 24279 0.0 .3
log file sync 140 0 24 171 0.5 .3
db file scattered read 152 0 21 136 0.6 .2
latch: shared pool 17 0 12 691 0.1 .1
enq: UL - contention 1 0 6 5668 0.0 .1
cursor: pin S wait on X 194 100 3 16 0.7 .0
buffer busy waits 583 0 1 2 2.2 .0
SQL*Net break/reset to clien 218 0 1 2 0.8 .0
PX Deq: Txn Recovery Start 24 0 0 20 0.1 .0
library cache load lock 1 0 0 243 0.0 .0
latch free 5 0 0 43 0.0 .0
latch: row cache objects 33 0 0 3 0.1 .0
latch: cache buffers chains 38 0 0 0 0.1 .0
latch: cache buffers lru cha 4 0 0 1 0.0 .0
SQL*Net message from client 1,173 0 3,864 3294 4.4
SQL*Net more data from clien 43 0 1,342 31200 0.2
jobq slave wait 152 98 451 2967 0.6
wait for unread message on b 101 100 101 999 0.4
Streams AQ: waiting for mess 12 100 56 4660 0.0
SQL*Net message to client 1,172 0 0 0 4.4
-------------------------------------------------------------
Wait Event Histogram DB/Inst: NOVEMBER/november Snaps: 1-2
-> Total Waits - units: K is 1000, M is 1000000, G is 1000000000
-> % of Waits - column heading: <=1s is truly <1024ms, >1s is truly >=1024ms
-> % of Waits - value: .0 indicates value was <.05%, null is truly 0
-> Ordered by Event (idle events last)
Total ----------------- % of Waits ------------------
Event Waits <1ms <2ms <4ms <8ms <16ms <32ms <=1s >1s
-------------------------- ----- ----- ----- ----- ----- ----- ----- ----- -----
Data file init write 3 33.3 66.7
LGWR wait for redo copy 10 100.0
PX Deq: Join ACK 13 76.9 7.7 15.4
PX Deq: Txn Recovery Start 24 66.7 20.8 12.5
SQL*Net break/reset to cli 218 91.7 5.0 1.4 .5 1.4
buffer busy waits 624 93.4 .2 .2 .3 .2 5.8
control file parallel writ 388 .5 1.5 3.4 94.3 .3
control file sequential re 1072 .8 .1 .2 .7 3.5 94.2 .5
cursor: pin S 1 100.0
cursor: pin S wait on X 194 97.9 2.1
db file parallel write 12K 37.3 6.6 2.4 4.6 10.5 12.2 26.3 .0
db file scattered read 191 3.1 96.3 .5
db file sequential read 47K .0 .0 .0 .2 1.5 6.5 91.5 .2
db file single write 1 100.0
direct path write 2 50.0 50.0
enq: CF - contention 1 100.0
enq: PR - contention 1 100.0
enq: PS - contention 7 14.3 85.7
enq: TX - row lock content 1 100.0
enq: UL - contention 1 100.0
kksfbc child completion 2 50.0 50.0
latch free 26 76.9 3.8 3.8 15.4
latch: cache buffers chain 39 100.0
latch: cache buffers lru c 8 87.5 12.5
latch: enqueue hash chains 1 100.0
latch: object queue header 1 100.0
latch: redo allocation 1 100.0
latch: row cache objects 33 97.0 3.0
latch: session allocation 2 100.0
latch: shared pool 33 12.1 3.0 3.0 51.5 30.3
library cache load lock 1 100.0
log file parallel write 650 .3 .5 .6 1.5 4.6 9.4 83.1
log file sync 143 7.7 .7 4.2 2.1 85.3
os thread startup 81 1.2 98.8
rdbms ipc reply 1 100.0
read by other session 1960 27.6 .1 .1 .6 2.6 4.6 64.5
resmgr:cpu quantum 518 44.0 4.4 2.1 3.7 3.9 6.2 30.9 4.8
wait for a undo record 8692 .2 .0 .0 99.7
wait for stopper event to 8605 .1 .0 99.9
DIAG idle wait 1967 99.8 .2
PX Idle Wait 60 28.3 1.7 1.7 3.3 16.7 48.3
SQL*Net message from clien 1223 55.3 23.5 5.7 2.3 3.3 1.0 3.6 5.3
SQL*Net message to client 1221 100.0
SQL*Net more data from cli 43 97.7 2.3
Space Manager: slave idle 377 .3 .3 .3 99.2
Streams AQ: qmn coordinato 59 37.3 3.4 59.3
Streams AQ: qmn slave idle 37 5.4 94.6
Streams AQ: waiting for me 12 8.3 91.7
Streams AQ: waiting for ti 3 33.3 66.7
class slave wait 8 50.0 12.5 37.5
dispatcher timer 16 100.0
Wait Event Histogram DB/Inst: NOVEMBER/november Snaps: 1-2
-> Total Waits - units: K is 1000, M is 1000000, G is 1000000000
-> % of Waits - column heading: <=1s is truly <1024ms, >1s is truly >=1024ms
-> % of Waits - value: .0 indicates value was <.05%, null is truly 0
-> Ordered by Event (idle events last)
Total ----------------- % of Waits ------------------
Event Waits <1ms <2ms <4ms <8ms <16ms <32ms <=1s >1s
-------------------------- ----- ----- ----- ----- ----- ----- ----- ----- -----
fbar timer 3 100.0
jobq slave wait 152 .7 99.3
pmon timer 379 16.6 1.3 .8 .5 .3 80.5
rdbms ipc message 4393 5.9 .1 .1 .5 .3 .8 33.0 59.3
virtual circuit status 33 100.0
wait for unread message on 104 100.0
-------------------------------------------------------------
SQL ordered by CPU DB/Inst: NOVEMBER/november Snaps: 1-2
-> Total DB CPU (s): 33
-> Captured SQL accounts for 39.5% of Total DB CPU
-> SQL reported below exceeded 1.0% of Total DB CPU
CPU CPU per Elapsd Old
Time (s) Executions Exec (s) %Total Time (s) Buffer Gets Hash Value
---------- ------------ ---------- ------ ---------- --------------- ----------
6.20 1 6.20 18.7 266.72 19,014 1398863989
Module: PL/SQL Developer
begin statspack.snap; end;
2.17 15 0.14 6.5 66.95 9,244 2689373535
DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate;
broken BOOLEAN := FALSE; BEGIN EMD_MAINTENANCE.EXECUTE_EM_DBMS_J
OB_PROCS(); :mydate := next_date; IF broken THEN :b := 1; ELSE :
b := 0; END IF; END;
1.81 0 5.5 995.29 133,986 125601442
Module: DBMS_SCHEDULER
call dbms_stats.gather_database_stats_job_proc ( )
1.59 0 4.8 993.99 12,491 381273648
begin dbms_logmnr_d.build(options => 268435456,
dictionary_filename => 'offline_dictionary.ir'
, dictionary_location => 'g:\app\alex\diag\rdb
ms\november\november\ir'); end;
1.59 0 4.8 993.99 12,491 2486226475
Module: DBMS_SCHEDULER
call dbms_hm.create_offline_dictionary ( )
1.47 1 1.47 4.4 1071.12 14,278 2037013970
SELECT KTFBUESEGTSN, KTFBUESEGFNO, KTFBUESEGBNO, KTFBUEEXTNO, KT
FBUEFNO, KTFBUEBNO, KTFBUEBLKS, KTFBUECTM, KTFBUESTT, KTFBUESTA
FROM SYS.X$KTFBUE AS OF SCN :B1
1.47 0 4.4 992.59 77,503 4023405807
Module: DBMS_SCHEDULER
call dbms_space.auto_space_advisor_job_proc ( )
1.28 2 0.64 3.9 978.46 76,523 3778941046
Module: DBMS_SCHEDULER
insert into wri$_adv_objspace_trend_data select timepoint, spac
e_usage, space_alloc, quality from table(dbms_space.object_grow
th_trend(:1, :2, :3, :4, NULL, NULL, NULL, 'FALSE', :5, 'FALSE'
))
0.80 151 0.01 2.4 13.61 2,860 2404475994
Module: OEM.SystemPool
INSERT INTO MGMT_METRICS_RAW(COLLECTION_TIMESTAMP, KEY_VALUE, ME
TRIC_GUID, TARGET_GUID, VALUE) VALUES ( :1, NVL(:2, ' '), :3, :4
, :5)
0.67 1 0.67 2.0 267.40 90,471 658709268
Module: DBMS_SCHEDULER
select substrb(dump(val,16,0,32),1,120) ep, cnt from (select /*+
no_parallel(t) no_parallel_index(t) dbms_stats cursor_sharing_e
xact use_weak_name_resl dynamic_sampling(0) no_monitoring */"NEW
STATE" val,count(*) cnt from "SYSMAN"."MGMT_JOB_STATE_CHANGES"
SQL ordered by CPU DB/Inst: NOVEMBER/november Snaps: 1-2
-> Total DB CPU (s): 33
-> Captured SQL accounts for 39.5% of Total DB CPU
-> SQL reported below exceeded 1.0% of Total DB CPU
CPU CPU per Elapsd Old
Time (s) Executions Exec (s) %Total Time (s) Buffer Gets Hash Value
---------- ------------ ---------- ------ ---------- --------------- ----------
0.50 0 1.5 447.24 28,849 2222230779
Module: DBMS_SCHEDULER
select min(minbkt),maxbkt,substrb(dump(min(val),16,0,32),1,120)
minval,substrb(dump(max(val),16,0,32),1,120) maxval,sum(rep) sum
rep, sum(repsq) sumrepsq, max(rep) maxrep, count(*) bktndv, sum(
case when rep=1 then 1 else 0 end) unqrep from (select val,min(b
0.36 0 1.1 190.58 31,026 1013630726
Module: OEM.CacheModeWaitPool
BEGIN EMDW_LOG.set_context(MGMT_JOB_ENGINE.MODULE_NAME, :1); BEG
IN MGMT_JOB_ENGINE.process_wait_step(:2);END; EMDW_LOG.set_conte
xt; END;
-------------------------------------------------------------
SQL ordered by Elapsed time for DB: NOVEMBER Instance: november Snaps: 1 -2
-> Total DB Time (s): 9,160
-> Captured SQL accounts for 24.2% of Total DB Time
-> SQL reported below exceeded 1.0% of Total DB Time
Elapsed Elap per CPU Old
Time (s) Executions Exec (s) %Total Time (s) Physical Reads Hash Value
---------- ------------ ---------- ------ ---------- --------------- ----------
1071.12 1 1071.12 11.7 1.47 7,253 2037013970
SELECT KTFBUESEGTSN, KTFBUESEGFNO, KTFBUESEGBNO, KTFBUEEXTNO, KT
FBUEFNO, KTFBUEBNO, KTFBUEBLKS, KTFBUECTM, KTFBUESTT, KTFBUESTA
FROM SYS.X$KTFBUE AS OF SCN :B1
995.29 0 10.9 1.81 9,640 125601442
Module: DBMS_SCHEDULER
call dbms_stats.gather_database_stats_job_proc ( )
993.99 0 10.9 1.59 6,435 381273648
begin dbms_logmnr_d.build(options => 268435456,
dictionary_filename => 'offline_dictionary.ir'
, dictionary_location => 'g:\app\alex\diag\rdb
ms\november\november\ir'); end;
993.99 0 10.9 1.59 6,435 2486226475
Module: DBMS_SCHEDULER
call dbms_hm.create_offline_dictionary ( )
992.59 0 10.8 1.47 8,254 4023405807
Module: DBMS_SCHEDULER
call dbms_space.auto_space_advisor_job_proc ( )
978.46 2 489.23 10.7 1.28 8,143 3778941046
Module: DBMS_SCHEDULER
insert into wri$_adv_objspace_trend_data select timepoint, spac
e_usage, space_alloc, quality from table(dbms_space.object_grow
th_trend(:1, :2, :3, :4, NULL, NULL, NULL, 'FALSE', :5, 'FALSE'
))
447.24 0 4.9 0.50 4,166 2222230779
Module: DBMS_SCHEDULER
select min(minbkt),maxbkt,substrb(dump(min(val),16,0,32),1,120)
minval,substrb(dump(max(val),16,0,32),1,120) maxval,sum(rep) sum
rep, sum(repsq) sumrepsq, max(rep) maxrep, count(*) bktndv, sum(
case when rep=1 then 1 else 0 end) unqrep from (select val,min(b
267.40 1 267.40 2.9 0.67 3,438 658709268
Module: DBMS_SCHEDULER
select substrb(dump(val,16,0,32),1,120) ep, cnt from (select /*+
no_parallel(t) no_parallel_index(t) dbms_stats cursor_sharing_e
xact use_weak_name_resl dynamic_sampling(0) no_monitoring */"NEW
STATE" val,count(*) cnt from "SYSMAN"."MGMT_JOB_STATE_CHANGES"
266.72 1 266.72 2.9 6.20 809 1398863989
Module: PL/SQL Developer
begin statspack.snap; end;
260.98 1 260.98 2.8 0.27 1,912 3037959895
Module: DBMS_SCHEDULER
select /*+ no_parallel_index(t, "PK_MGMT_JOB_ST_CHGS") dbms_stat
s cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no
SQL ordered by Elapsed time for DB: NOVEMBER Instance: november Snaps: 1 -2
-> Total DB Time (s): 9,160
-> Captured SQL accounts for 24.2% of Total DB Time
-> SQL reported below exceeded 1.0% of Total DB Time
Elapsed Elap per CPU Old
Time (s) Executions Exec (s) %Total Time (s) Physical Reads Hash Value
---------- ------------ ---------- ------ ---------- --------------- ----------
_monitoring no_expand index(t,"PK_MGMT_JOB_ST_CHGS") */ count(*)
as nrw,count(distinct sys_op_lbid(65208,'L',t.rowid)) as nlb,nu
224.35 207 1.08 2.4 0.23 900 986338823
select /*+ index(idl_ub1$ i_idl_ub11) +*/ piece#,length,piece fr
om idl_ub1$ where obj#=:1 and part=:2 and version=:3 order by pi
ece#
190.58 0 2.1 0.36 514 1013630726
Module: OEM.CacheModeWaitPool
BEGIN EMDW_LOG.set_context(MGMT_JOB_ENGINE.MODULE_NAME, :1); BEG
IN MGMT_JOB_ENGINE.process_wait_step(:2);END; EMDW_LOG.set_conte
xt; END;
174.90 1 174.90 1.9 0.33 582 2153462684
Module: OEM.CacheModeWaitPool
SELECT x.STATE_GUID, x.PAF_JOB_STATUS, x.JOB_STATU
S, y.INSTANCE_GUID FROM ( SELECT pj.STATE_GUID,
pj.JOB_ID, pj.JOB_STATUS AS PAF_JOB_ST
ATUS, je.STATUS AS JOB_STATUS, je.ST
157.79 1 157.79 1.7 0.22 414 1229574971
Module: OEM.CacheModeWaitPool
SELECT EXECUTION_ID, STATUS, STATUS_DETAIL FROM MGMT_JOB_EXEC_SU
MMARY WHERE JOB_ID = :B3 AND TARGET_LIST_INDEX = :B2 AND EXPECTE
D_START_TIME = :B1
-------------------------------------------------------------
SQL ordered by Gets DB/Inst: NOVEMBER/november Snaps: 1-2
-> End Buffer Gets Threshold: 10000 Total Buffer Gets: 607,445
-> Captured SQL accounts for 30.1% of Total Buffer Gets
-> SQL reported below exceeded 1.0% of Total Buffer Gets
CPU Elapsd Old
Buffer Gets Executions Gets per Exec %Total Time (s) Time (s) Hash Value
--------------- ------------ -------------- ------ -------- --------- ----------
133,986 0 22.1 1.81 995.29 125601442
Module: DBMS_SCHEDULER
call dbms_stats.gather_database_stats_job_proc ( )
90,471 1 90,471.0 14.9 0.67 267.40 658709268
Module: DBMS_SCHEDULER
select substrb(dump(val,16,0,32),1,120) ep, cnt from (select /*+
no_parallel(t) no_parallel_index(t) dbms_stats cursor_sharing_e
xact use_weak_name_resl dynamic_sampling(0) no_monitoring */"NEW
STATE" val,count(*) cnt from "SYSMAN"."MGMT_JOB_STATE_CHANGES"
77,503 0 12.8 1.47 992.59 4023405807
Module: DBMS_SCHEDULER
call dbms_space.auto_space_advisor_job_proc ( )
76,523 2 38,261.5 12.6 1.28 978.46 3778941046
Module: DBMS_SCHEDULER
insert into wri$_adv_objspace_trend_data select timepoint, spac
e_usage, space_alloc, quality from table(dbms_space.object_grow
th_trend(:1, :2, :3, :4, NULL, NULL, NULL, 'FALSE', :5, 'FALSE'
))
31,039 1 31,039.0 5.1 0.33 174.90 2153462684
Module: OEM.CacheModeWaitPool
SELECT x.STATE_GUID, x.PAF_JOB_STATUS, x.JOB_STATU
S, y.INSTANCE_GUID FROM ( SELECT pj.STATE_GUID,
pj.JOB_ID, pj.JOB_STATUS AS PAF_JOB_ST
ATUS, je.STATUS AS JOB_STATUS, je.ST
31,026 0 5.1 0.36 190.58 1013630726
Module: OEM.CacheModeWaitPool
BEGIN EMDW_LOG.set_context(MGMT_JOB_ENGINE.MODULE_NAME, :1); BEG
IN MGMT_JOB_ENGINE.process_wait_step(:2);END; EMDW_LOG.set_conte
xt; END;
29,132 1 29,132.0 4.8 0.22 157.79 1229574971
Module: OEM.CacheModeWaitPool
SELECT EXECUTION_ID, STATUS, STATUS_DETAIL FROM MGMT_JOB_EXEC_SU
MMARY WHERE JOB_ID = :B3 AND TARGET_LIST_INDEX = :B2 AND EXPECTE
D_START_TIME = :B1
28,849 0 4.7 0.50 447.24 2222230779
Module: DBMS_SCHEDULER
select min(minbkt),maxbkt,substrb(dump(min(val),16,0,32),1,120)
minval,substrb(dump(max(val),16,0,32),1,120) maxval,sum(rep) sum
rep, sum(repsq) sumrepsq, max(rep) maxrep, count(*) bktndv, sum(
case when rep=1 then 1 else 0 end) unqrep from (select val,min(b
19,014 1 19,014.0 3.1 6.20 266.72 1398863989
Module: PL/SQL Developer
begin statspack.snap; end;
SQL ordered by Gets DB/Inst: NOVEMBER/november Snaps: 1-2
-> End Buffer Gets Threshold: 10000 Total Buffer Gets: 607,445
-> Captured SQL accounts for 30.1% of Total Buffer Gets
-> SQL reported below exceeded 1.0% of Total Buffer Gets
CPU Elapsd Old
Buffer Gets Executions Gets per Exec %Total Time (s) Time (s) Hash Value
--------------- ------------ -------------- ------ -------- --------- ----------
14,278 1 14,278.0 2.4 1.47 1071.12 2037013970
SELECT KTFBUESEGTSN, KTFBUESEGFNO, KTFBUESEGBNO, KTFBUEEXTNO, KT
FBUEFNO, KTFBUEBNO, KTFBUEBLKS, KTFBUECTM, KTFBUESTT, KTFBUESTA
FROM SYS.X$KTFBUE AS OF SCN :B1
12,537 1 12,537.0 2.1 0.27 260.98 3037959895
Module: DBMS_SCHEDULER
select /*+ no_parallel_index(t, "PK_MGMT_JOB_ST_CHGS") dbms_stat
s cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no
_monitoring no_expand index(t,"PK_MGMT_JOB_ST_CHGS") */ count(*)
as nrw,count(distinct sys_op_lbid(65208,'L',t.rowid)) as nlb,nu
12,491 0 2.1 1.59 993.99 381273648
begin dbms_logmnr_d.build(options => 268435456,
dictionary_filename => 'offline_dictionary.ir'
, dictionary_location => 'g:\app\alex\diag\rdb
ms\november\november\ir'); end;
12,491 0 2.1 1.59 993.99 2486226475
Module: DBMS_SCHEDULER
call dbms_hm.create_offline_dictionary ( )
9,244 15 616.3 1.5 2.17 66.95 2689373535
DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate;
broken BOOLEAN := FALSE; BEGIN EMD_MAINTENANCE.EXECUTE_EM_DBMS_J
OB_PROCS(); :mydate := next_date; IF broken THEN :b := 1; ELSE :
b := 0; END IF; END;
7,020 1,393 5.0 1.2 0.14 2.02 2482976222
select intcol#,nvl(pos#,0),col#,nvl(spare1,0) from ccol$ where c
on#=:1
-------------------------------------------------------------
SQL ordered by Reads DB/Inst: NOVEMBER/november Snaps: 1-2
-> End Disk Reads Threshold: 1000 Total Disk Reads: 50,978
-> Captured SQL accounts for 32.2% of Total Disk Reads
-> SQL reported below exceeded 1.0% of Total Disk Reads
CPU Elapsd
Physical Rds Executions Rds per Exec %Total Time (s) Time (s) Hash Value
--------------- ------------ -------------- ------ -------- --------- ----------
9,640 0 18.9 1.81 995.29 125601442
Module: DBMS_SCHEDULER
call dbms_stats.gather_database_stats_job_proc ( )
8,254 0 16.2 1.47 992.59 4023405807
Module: DBMS_SCHEDULER
call dbms_space.auto_space_advisor_job_proc ( )
8,143 2 4,071.5 16.0 1.28 978.46 3778941046
Module: DBMS_SCHEDULER
insert into wri$_adv_objspace_trend_data select timepoint, spac
e_usage, space_alloc, quality from table(dbms_space.object_grow
th_trend(:1, :2, :3, :4, NULL, NULL, NULL, 'FALSE', :5, 'FALSE'
))
7,253 1 7,253.0 14.2 1.47 1071.12 2037013970
SELECT KTFBUESEGTSN, KTFBUESEGFNO, KTFBUESEGBNO, KTFBUEEXTNO, KT
FBUEFNO, KTFBUEBNO, KTFBUEBLKS, KTFBUECTM, KTFBUESTT, KTFBUESTA
FROM SYS.X$KTFBUE AS OF SCN :B1
6,435 0 12.6 1.59 993.99 381273648
begin dbms_logmnr_d.build(options => 268435456,
dictionary_filename => 'offline_dictionary.ir'
, dictionary_location => 'g:\app\alex\diag\rdb
ms\november\november\ir'); end;
6,435 0 12.6 1.59 993.99 2486226475
Module: DBMS_SCHEDULER
call dbms_hm.create_offline_dictionary ( )
4,166 0 8.2 0.50 447.24 2222230779
Module: DBMS_SCHEDULER
select min(minbkt),maxbkt,substrb(dump(min(val),16,0,32),1,120)
minval,substrb(dump(max(val),16,0,32),1,120) maxval,sum(rep) sum
rep, sum(repsq) sumrepsq, max(rep) maxrep, count(*) bktndv, sum(
case when rep=1 then 1 else 0 end) unqrep from (select val,min(b
3,438 1 3,438.0 6.7 0.67 267.40 658709268
Module: DBMS_SCHEDULER
select substrb(dump(val,16,0,32),1,120) ep, cnt from (select /*+
no_parallel(t) no_parallel_index(t) dbms_stats cursor_sharing_e
xact use_weak_name_resl dynamic_sampling(0) no_monitoring */"NEW
STATE" val,count(*) cnt from "SYSMAN"."MGMT_JOB_STATE_CHANGES"
1,912 1 1,912.0 3.8 0.27 260.98 3037959895
Module: DBMS_SCHEDULER
select /*+ no_parallel_index(t, "PK_MGMT_JOB_ST_CHGS") dbms_stat
s cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no
_monitoring no_expand index(t,"PK_MGMT_JOB_ST_CHGS") */ count(*)
as nrw,count(distinct sys_op_lbid(65208,'L',t.rowid)) as nlb,nu
900 207 4.3 1.8 0.23 224.35 986338823
SQL ordered by Reads DB/Inst: NOVEMBER/november Snaps: 1-2
-> End Disk Reads Threshold: 1000 Total Disk Reads: 50,978
-> Captured SQL accounts for 32.2% of Total Disk Reads
-> SQL reported below exceeded 1.0% of Total Disk Reads
CPU Elapsd
Physical Rds Executions Rds per Exec %Total Time (s) Time (s) Hash Value
--------------- ------------ -------------- ------ -------- --------- ----------
select /*+ index(idl_ub1$ i_idl_ub11) +*/ piece#,length,piece fr
om idl_ub1$ where obj#=:1 and part=:2 and version=:3 order by pi
ece#
809 1 809.0 1.6 6.20 266.72 1398863989
Module: PL/SQL Developer
begin statspack.snap; end;
582 1 582.0 1.1 0.33 174.90 2153462684
Module: OEM.CacheModeWaitPool
SELECT x.STATE_GUID, x.PAF_JOB_STATUS, x.JOB_STATU
S, y.INSTANCE_GUID FROM ( SELECT pj.STATE_GUID,
pj.JOB_ID, pj.JOB_STATUS AS PAF_JOB_ST
ATUS, je.STATUS AS JOB_STATUS, je.ST
514 0 1.0 0.36 190.58 1013630726
Module: OEM.CacheModeWaitPool
BEGIN EMDW_LOG.set_context(MGMT_JOB_ENGINE.MODULE_NAME, :1); BEG
IN MGMT_JOB_ENGINE.process_wait_step(:2);END; EMDW_LOG.set_conte
xt; END;
-------------------------------------------------------------
SQL ordered by Executions DB/Inst: NOVEMBER/november Snaps: 1-2
-> End Executions Threshold: 100 Total Executions: 11,431
-> Captured SQL accounts for 54.0% of Total Executions
-> SQL reported below exceeded 1.0% of Total Executions
CPU per Elap per Old
Executions Rows Processed Rows per Exec Exec (s) Exec (s) Hash Value
------------ --------------- ---------------- ----------- ---------- ----------
1,603 550 0.3 0.00 0.01 4274598960
select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt, tim
estamp#, sample_size, minimum, maximum, distcnt, lowval, hival,
density, col#, spare1, spare2, avgcln from hist_head$ where obj#
=:1 and intcol#=:2
1,393 2,115 1.5 0.00 0.00 2482976222
select intcol#,nvl(pos#,0),col#,nvl(spare1,0) from ccol$ where c
on#=:1
1,179 1,179 1.0 0.00 0.00 1254950678
select file# from file$ where ts#=:1
992 992 1.0 0.00 0.08 3468666020
select text from view$ where rowid=:1
717 1,482 2.1 0.00 0.00 2030260024
select pos#,intcol#,col#,spare1,bo#,spare2,spare3 from icol$ whe
re obj#=:1
627 581 0.9 0.00 0.01 2669392844
Module: OEM.SystemPool
UPDATE MGMT_CURRENT_METRICS SET COLLECTION_TIMESTAMP = :B1 , VAL
UE = :B6 , STRING_VALUE = :B5 WHERE TARGET_GUID = :B4 AND METRIC
_GUID = :B3 AND KEY_VALUE = :B2 AND COLLECTION_TIMESTAMP < :B1
527 0 0.0 0.00 0.01 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#
520 458 0.9 0.00 0.00 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
480 227 0.5 0.00 0.01 2065408759
select con#,obj#,rcon#,enabled,nvl(defer,0) from cdef$ where rob
j#=:1
465 1,592 3.4 0.00 0.00 1937775682
select con#,type#,condlength,intcols,robj#,rcon#,match#,refact,n
vl(enabled,0),rowid,cols,nvl(defer,0),mtime,nvl(spare1,0) from c
def$ where obj#=:1
358 17 0.0 0.00 0.01 1980305124
select grantee#,privilege#,nvl(col#,0),max(mod(nvl(option$,0),2)
)from objauth$ where obj#=:1 group by grantee#,privilege#,nvl(co
l#,0) order by grantee#
280 185 0.7 0.00 0.01 214372507
SQL ordered by Executions DB/Inst: NOVEMBER/november Snaps: 1-2
-> End Executions Threshold: 100 Total Executions: 11,431
-> Captured SQL accounts for 54.0% of Total Executions
-> SQL reported below exceeded 1.0% of Total Executions
CPU per Elap per Old
Executions Rows Processed Rows per Exec Exec (s) Exec (s) Hash Value
------------ --------------- ---------------- ----------- ---------- ----------
select o.owner#,o.name,o.namespace,o.remoteowner,o.linkname,o.su
bname from obj$ o where o.obj#=:1
278 278 1.0 0.00 0.00 515613257
Module: OEM.BoundedPool
SELECT CONTEXT_TYPE_ID,CONTEXT_TYPE,TRACE_LEVEL,NULL,NULL FROM E
MDW_TRACE_CONFIG WHERE CONTEXT_TYPE = UPPER(:B1 )
272 1,116 4.1 0.00 0.02 1749333492
select position#,sequence#,level#,argument,type#,charsetid,chars
etform,properties,nvl(length, 0), nvl(precision#, 0),nvl(scale,
0),nvl(radix, 0), type_owner,type_name,type_subname,type_linknam
e,pls_type from argument$ where obj#=:1 and procedure#=:2 order
207 655 3.2 0.00 1.08 986338823
select /*+ index(idl_ub1$ i_idl_ub11) +*/ piece#,length,piece fr
om idl_ub1$ where obj#=:1 and part=:2 and version=:3 order by pi
ece#
206 0 0.0 0.00 0.00 2554034351
lock table sys.col_usage$ in exclusive mode nowait
206 269 1.3 0.00 0.16 2954231783
select /*+ index(idl_sb4$ i_idl_sb41) +*/ piece#,length,piece fr
om idl_sb4$ where obj#=:1 and part=:2 and version=:3 order by pi
ece#
204 77 0.4 0.00 0.09 336764478
select /*+ index(idl_char$ i_idl_char1) +*/ piece#,length,piece
from idl_char$ where obj#=:1 and part=:2 and version=:3 order by
piece#
204 227 1.1 0.00 0.30 386388955
select /*+ index(idl_ub2$ i_idl_ub21) +*/ piece#,length,piece fr
om idl_ub2$ where obj#=:1 and part=:2 and version=:3 order by pi
ece#
204 282 1.4 0.00 0.08 1198893840
select order#,columns,types from access$ where d_obj#=:1
192 2,615 13.6 0.00 0.09 199663413
select /*+ rule */ bucket, endpoint, col#, epvalue from histgrm$
where obj#=:1 and intcol#=:2 and row#=:3 order by bucket
189 38 0.2 0.00 0.00 1592133362
Module: OEM.SystemPool
SELECT JOB_ID, EXECUTION_ID, STEP_ID, STEP_NAME, STEP_TYPE, ITER
ATE_PARAM, ITERATE_PARAM_INDEX, COMMAND_TYPE, TIMEZONE_REGION FR
OM MGMT_JOB_EXECUTION J WHERE STEP_TYPE IN (:B9 , :B8 , :B7 , :B
6 , :B5 ) AND STEP_STATUS = :B4 AND COMMAND_TYPE = :B3 AND START
188 0 0.0 0.00 0.00 2396279102
SQL ordered by Executions DB/Inst: NOVEMBER/november Snaps: 1-2
-> End Executions Threshold: 100 Total Executions: 11,431
-> Captured SQL accounts for 54.0% of Total Executions
-> SQL reported below exceeded 1.0% of Total Executions
CPU per Elap per Old
Executions Rows Processed Rows per Exec Exec (s) Exec (s) Hash Value
------------ --------------- ---------------- ----------- ---------- ----------
lock table sys.mon_mods$ in exclusive mode nowait
185 182 1.0 0.00 0.10 2703824309
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 is null and linkname is null and subname i
s null
151 561 3.7 0.01 0.09 2404475994
Module: OEM.SystemPool
INSERT INTO MGMT_METRICS_RAW(COLLECTION_TIMESTAMP, KEY_VALUE, ME
TRIC_GUID, TARGET_GUID, VALUE) VALUES ( :1, NVL(:2, ' '), :3, :4
, :5)
149 1,165 7.8 0.00 0.00 4143084494
select privilege#,level from sysauth$ connect by grantee#=prior
privilege# and privilege#>0 start with grantee#=:1 and privilege
#>0
145 145 1.0 0.00 0.05 2926525797
select type#,blocks,extents,minexts,maxexts,extsize,extpct,user#
,iniexts,NVL(lists,65535),NVL(groups,65535),cachehint,hwmincr, N
VL(spare1,0),NVL(scanhint,0),NVL(bitmapranges,0) from seg$ where
ts#=:1 and file#=:2 and block#=:3
135 196 1.5 0.00 0.14 3694268570
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.samp
lesize,i.dataobj#,nvl(i.degree,1),nvl(i.instances,1),i.rowcnt,mo
130 0 0.0 0.00 0.00 1759257762
Module: OEM.DefaultPool
delete from sdo_geor_ddl__table$$
123 123 1.0 0.00 0.00 688250911
SELECT DISTINCT METRIC_GUID FROM MGMT_METRICS WHERE TARGET_TYPE
= :B3 AND METRIC_NAME = :B2 AND METRIC_COLUMN = :B1
123 123 1.0 0.00 0.00 4203089485
Module: OEM.BoundedPool
SELECT TARGET_GUID FROM MGMT_TARGETS WHERE TARGET_NAME = :B2 AND
TARGET_TYPE = :B1
122 122 1.0 0.00 0.00 3047156589
select t.ts#,t.file#,t.block#,nvl(t.bobj#,0),nvl(t.tab#,0),t.int
cols,nvl(t.clucols,0),t.audit$,t.flags,t.pctfree$,t.pctused$,t.i
nitrans,t.maxtrans,t.rowcnt,t.blkcnt,t.empcnt,t.avgspc,t.chncnt,
t.avgrln,t.analyzetime,t.samplesize,t.cols,t.property,nvl(t.degr
-------------------------------------------------------------
SQL ordered by Parse Calls DB/Inst: NOVEMBER/november Snaps: 1-2
-> End Parse Calls Threshold: 1000 Total Parse Calls: 5,235
-> Captured SQL accounts for 45.8% of Total Parse Calls
-> SQL reported below exceeded 1.0% of Total Parse Calls
% Total Old
Parse Calls Executions Parses Hash Value
------------ ------------ -------- ----------
1,178 1,179 22.50 1254950678
select file# from file$ where ts#=:1
992 992 18.95 3468666020
select text from view$ where rowid=:1
207 207 3.95 986338823
select /*+ index(idl_ub1$ i_idl_ub11) +*/ piece#,length,piece fr
om idl_ub1$ where obj#=:1 and part=:2 and version=:3 order by pi
ece#
206 206 3.94 2554034351
lock table sys.col_usage$ in exclusive mode nowait
206 206 3.94 2954231783
select /*+ index(idl_sb4$ i_idl_sb41) +*/ piece#,length,piece fr
om idl_sb4$ where obj#=:1 and part=:2 and version=:3 order by pi
ece#
206 520 3.94 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
204 204 3.90 336764478
select /*+ index(idl_char$ i_idl_char1) +*/ piece#,length,piece
from idl_char$ where obj#=:1 and part=:2 and version=:3 order by
piece#
204 204 3.90 386388955
select /*+ index(idl_ub2$ i_idl_ub21) +*/ piece#,length,piece fr
om idl_ub2$ where obj#=:1 and part=:2 and version=:3 order by pi
ece#
204 204 3.90 1198893840
select order#,columns,types from access$ where d_obj#=:1
188 188 3.59 2396279102
lock table sys.mon_mods$ in exclusive mode nowait
167 358 3.19 1980305124
select grantee#,privilege#,nvl(col#,0),max(mod(nvl(option$,0),2)
)from objauth$ where obj#=:1 group by grantee#,privilege#,nvl(co
l#,0) order by grantee#
165 480 3.15 2065408759
select con#,obj#,rcon#,enabled,nvl(defer,0) from cdef$ where rob
j#=:1
161 465 3.08 1937775682
select con#,type#,condlength,intcols,robj#,rcon#,match#,refact,n
SQL ordered by Parse Calls DB/Inst: NOVEMBER/november Snaps: 1-2
-> End Parse Calls Threshold: 1000 Total Parse Calls: 5,235
-> Captured SQL accounts for 45.8% of Total Parse Calls
-> SQL reported below exceeded 1.0% of Total Parse Calls
% Total Old
Parse Calls Executions Parses Hash Value
------------ ------------ -------- ----------
vl(enabled,0),rowid,cols,nvl(defer,0),mtime,nvl(spare1,0) from c
def$ where obj#=:1
149 149 2.85 4143084494
select privilege#,level from sysauth$ connect by grantee#=prior
privilege# and privilege#>0 start with grantee#=:1 and privilege
#>0
145 145 2.77 2926525797
select type#,blocks,extents,minexts,maxexts,extsize,extpct,user#
,iniexts,NVL(lists,65535),NVL(groups,65535),cachehint,hwmincr, N
VL(spare1,0),NVL(scanhint,0),NVL(bitmapranges,0) from seg$ where
ts#=:1 and file#=:2 and block#=:3
130 130 2.48 1759257762
Module: OEM.DefaultPool
delete from sdo_geor_ddl__table$$
127 717 2.43 2030260024
select pos#,intcol#,col#,spare1,bo#,spare2,spare3 from icol$ whe
re obj#=:1
117 280 2.23 214372507
select o.owner#,o.name,o.namespace,o.remoteowner,o.linkname,o.su
bname from obj$ o where o.obj#=:1
111 1,603 2.12 4274598960
select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt, tim
estamp#, sample_size, minimum, maximum, distcnt, lowval, hival,
density, col#, spare1, spare2, avgcln from hist_head$ where obj#
=:1 and intcol#=:2
109 109 2.08 1730223458
Module: OEM.DefaultPool
BEGIN MGMT_HTTP_SESSION.session_expired(:1); END;
109 109 2.08 4123566729
select parttype, partcnt, partkeycols, flags, defts#, defpctfree
, defpctused, definitrans, defmaxtrans, deftiniexts, defextsize,
defminexts, defmaxexts, defextpct, deflists, defgroups, deflogg
ing, spare1, mod(spare2, 256) subparttype, mod(trunc(spare2/256)
108 108 2.06 3895686941
Module: OMS
begin execute immediate 'alter session set NLS_NUMERIC_CHARACTER
S = ''.,'''; end;
106 106 2.02 1134991663
Module: OMS
begin dbms_application_info.set_module(:1, :2); dbms_application
_info.set_client_info(:3); dbms_session.set_identifier(:4); end;
SQL ordered by Parse Calls DB/Inst: NOVEMBER/november Snaps: 1-2
-> End Parse Calls Threshold: 1000 Total Parse Calls: 5,235
-> Captured SQL accounts for 45.8% of Total Parse Calls
-> SQL reported below exceeded 1.0% of Total Parse Calls
% Total Old
Parse Calls Executions Parses Hash Value
------------ ------------ -------- ----------
106 106 2.02 3286148528
select c.name, u.name from con$ c, cdef$ cd, user$ u where c.co
n# = cd.con# and cd.enabled = :1 and c.owner# = u.user#
103 103 1.97 2803285
update sys.mon_mods$ set inserts = inserts + :ins, updates = upd
ates + :upd, deletes = deletes + :del, flags = (decode(bitand(fl
ags, :flag), :flag, flags, flags + :flag)), drop_segments = drop
_segments + :dropseg, timestamp = :time where obj# = :objn
101 101 1.93 112088850
Module: OMS
SELECT 'x' FROM DUAL
96 96 1.83 2417075700
Module: OMS
SELECT PARAMETER_VALUE FROM MGMT_PARAMETERS WHERE PARAMETER_NAME
= :B1
86 1,393 1.64 2482976222
select intcol#,nvl(pos#,0),col#,nvl(spare1,0) from ccol$ where c
on#=:1
84 84 1.60 1413792485
Module: OEM.DefaultPool
declare m_stmt varchar2(512); begin m_stmt:='delete from s
do_geor_ddl__table$$'; EXECUTE IMMEDIATE m_stmt; EXCEPTION
WHEN OTHERS THEN NULL; end;
70 70 1.34 1232162599
select l.col#, l.intcol#, l.lobj#, l.ind#, l.ts#, l.file#, l.blo
ck#, l.chunk, l.pctversion$, l.flags, l.property, l.retention, l
.freepools from lob$ l where l.obj# = :1 order by l.intcol# asc
62 70 1.18 120978412
select col#,intcol#,ntab# from ntab$ where obj#=:1 order by intc
ol# asc
62 70 1.18 162044583
select col#,intcol#,charsetid,charsetform from col$ where obj#=:
1 order by intcol# asc
62 70 1.18 1520971034
select intcol#, toid, version#, intcols, intcol#s, flags, synobj
# from subcoltype$ where obj#=:1 order by intcol# asc
62 70 1.18 3302995849
select col#,intcol#,reftyp,stabid,expctoid from refcon$ where ob
j#=:1 order by intcol# asc
62 70 1.18 3309219733
SQL ordered by Parse Calls DB/Inst: NOVEMBER/november Snaps: 1-2
-> End Parse Calls Threshold: 1000 Total Parse Calls: 5,235
-> Captured SQL accounts for 45.8% of Total Parse Calls
-> SQL reported below exceeded 1.0% of Total Parse Calls
% Total Old
Parse Calls Executions Parses Hash Value
------------ ------------ -------- ----------
select intcol#,type,flags,lobcol,objcol,extracol,schemaoid, ele
mnum from opqtype$ where obj# = :1 order by intcol# asc
59 61 1.13 2364027057
select col#,intcol#,toid,version#,packed,intcols,intcol#s,flags,
synobj#, nvl(typidcol#, 0) from coltype$ where obj#=:1 order by
intcol# desc
56 56 1.07 794436051
Module: OEM.BoundedPool
SELECT INSTANTIABLE, supertype_owner, supertype_name, LOCAL_ATTR
IBUTES FROM all_types WHERE type_name = :1 AND owner = :2
-------------------------------------------------------------
truncate table STATS$TEMP_SQLSTATS
*
ERROR at line 1:
ORA-00942: table or view does not exist
Instance Activity Stats DB/Inst: NOVEMBER/november Snaps: 1-2
Statistic Total per Second per Trans
--------------------------------- ------------------ -------------- ------------
Batched IO (bound) vector count 0 0.0 0.0
Batched IO (full) vector count 0 0.0 0.0
Batched IO block miss count 4 0.0 0.0
Batched IO buffer defrag count 0 0.0 0.0
Batched IO double miss count 2 0.0 0.0
Batched IO same unit count 0 0.0 0.0
Batched IO single block count 0 0.0 0.0
Batched IO slow jump count 4 0.0 0.0
Batched IO vector block count 4 0.0 0.0
Batched IO vector read count 2 0.0 0.0
CCursor + sql area evicted 175 0.2 0.7
CPU used by this session 3,129 3.2 11.8
CPU used when call started 2,356 2.4 8.9
CR blocks created 1,923 2.0 7.2
DB time 668,987 685.4 2,515.0
DBWR checkpoint buffers written 17,729 18.2 66.7
DBWR checkpoints 0 0.0 0.0
DBWR revisited being-written buff 0 0.0 0.0
DBWR tablespace checkpoint buffer 0 0.0 0.0
DBWR transaction table writes 26 0.0 0.1
DBWR undo block writes 48,395 49.6 181.9
Effective IO time 0 0.0 0.0
HSC Heap Segment Block Changes 1,424 1.5 5.4
Heap Segment Array Inserts 93 0.1 0.4
Heap Segment Array Updates 23 0.0 0.1
IMU CR rollbacks 98 0.1 0.4
IMU Flushes 126 0.1 0.5
IMU Redo allocation size 251,996 258.2 947.4
IMU commits 172 0.2 0.7
IMU contention 52 0.1 0.2
IMU ktichg flush 0 0.0 0.0
IMU pool not allocated 0 0.0 0.0
IMU recursive-transaction flush 0 0.0 0.0
IMU undo allocation size 514,772 527.4 1,935.2
IMU- failed to get a private stra 0 0.0 0.0
LOB table id lookup cache misses 0 0.0 0.0
Number of read IOs issued 0 0.0 0.0
PX local messages recv'd 20 0.0 0.1
PX local messages sent 20 0.0 0.1
RowCR - row contention 67 0.1 0.3
RowCR attempts 367 0.4 1.4
RowCR hits 305 0.3 1.2
SMON posted for undo segment reco 5 0.0 0.0
SQL*Net roundtrips to/from client 1,189 1.2 4.5
TBS Extension: files extended 1 0.0 0.0
TBS Extension: tasks created 4 0.0 0.0
TBS Extension: tasks executed 4 0.0 0.0
active txn count during cleanout 2,150 2.2 8.1
application wait time 3,049 3.1 11.5
background checkpoints completed 0 0.0 0.0
background checkpoints started 0 0.0 0.0
background timeouts 3,582 3.7 13.5
buffer is not pinned count 35,968 36.9 135.2
buffer is pinned count 7,872 8.1 29.6
bytes received via SQL*Net from c 304,172 311.7 1,143.5
bytes sent via SQL*Net to client 133,455 136.7 501.7
Instance Activity Stats DB/Inst: NOVEMBER/november Snaps: 1-2
Statistic Total per Second per Trans
--------------------------------- ------------------ -------------- ------------
calls to get snapshot scn: kcmgss 19,692 20.2 74.0
calls to kcmgas 24,760 25.4 93.1
calls to kcmgcs 145 0.2 0.6
calls to kcmgrs 40,076 41.1 150.7
change write time 169 0.2 0.6
cleanout - number of ktugct calls 2,139 2.2 8.0
cleanouts and rollbacks - consist 2,040 2.1 7.7
cleanouts only - consistent read 7 0.0 0.0
cluster key scan block gets 1,838 1.9 6.9
cluster key scans 1,256 1.3 4.7
commit batch performed 0 0.0 0.0
commit batch requested 0 0.0 0.0
commit batch/immediate performed 0 0.0 0.0
commit batch/immediate requested 0 0.0 0.0
commit cleanout failures: block l 11 0.0 0.0
commit cleanout failures: buffer 0 0.0 0.0
commit cleanout failures: callbac 6 0.0 0.0
commit cleanout failures: cannot 0 0.0 0.0
commit cleanouts 1,778 1.8 6.7
commit cleanouts successfully com 1,761 1.8 6.6
commit immediate performed 0 0.0 0.0
commit immediate requested 0 0.0 0.0
commit txn count during cleanout 45 0.1 0.2
concurrency wait time 11,104 11.4 41.7
consistent changes 246,414 252.5 926.4
consistent gets 312,322 320.0 1,174.1
consistent gets - examination 266,915 273.5 1,003.4
consistent gets direct 0 0.0 0.0
consistent gets from cache 312,322 320.0 1,174.1
consistent gets from cache (fastp 32,924 33.7 123.8
current blocks converted for CR 1 0.0 0.0
cursor authentications 179 0.2 0.7
data blocks consistent reads - un 247,032 253.1 928.7
db block changes 359,190 368.0 1,350.3
db block gets 295,123 302.4 1,109.5
db block gets direct 36 0.0 0.1
db block gets from cache 295,087 302.3 1,109.4
db block gets from cache (fastpat 153,721 157.5 577.9
deferred (CURRENT) block cleanout 985 1.0 3.7
dirty buffers inspected 16,795 17.2 63.1
enqueue conversions 217 0.2 0.8
enqueue releases 10,078 10.3 37.9
enqueue requests 10,111 10.4 38.0
enqueue timeouts 21 0.0 0.1
enqueue waits 11 0.0 0.0
execute count 11,431 11.7 43.0
failed probes on index block recl 1 0.0 0.0
free buffer inspected 61,009 62.5 229.4
free buffer requested 53,426 54.7 200.9
heap block compress 55 0.1 0.2
hot buffers moved to head of LRU 75,563 77.4 284.1
immediate (CR) block cleanout app 2,047 2.1 7.7
immediate (CURRENT) block cleanou 419 0.4 1.6
index crx upgrade (found) 0 0.0 0.0
index crx upgrade (positioned) 224 0.2 0.8
index crx upgrade (prefetch) 0 0.0 0.0
Instance Activity Stats DB/Inst: NOVEMBER/november Snaps: 1-2
Statistic Total per Second per Trans
--------------------------------- ------------------ -------------- ------------
index fast full scans (full) 37 0.0 0.1
index fetch by key 7,529 7.7 28.3
index scans kdiixs1 8,476 8.7 31.9
leaf node 90-10 splits 9 0.0 0.0
leaf node splits 29 0.0 0.1
lob reads 12 0.0 0.1
lob writes 8 0.0 0.0
lob writes unaligned 8 0.0 0.0
logons cumulative 67 0.1 0.3
messages received 13,571 13.9 51.0
messages sent 13,571 13.9 51.0
no buffer to keep pinned count 0 0.0 0.0
no work - consistent read gets 30,177 30.9 113.5
opened cursors cumulative 10,491 10.8 39.4
parse count (failures) 1 0.0 0.0
parse count (hard) 582 0.6 2.2
parse count (total) 5,235 5.4 19.7
parse time cpu 244 0.3 0.9
parse time elapsed 6,149 6.3 23.1
physical read IO requests 48,101 49.3 180.8
physical read bytes 417,611,776 427,880.9 1,569,969.1
physical read total IO requests 49,176 50.4 184.9
physical read total bytes 435,208,192 445,910.0 1,636,121.0
physical read total multi block r 57 0.1 0.2
physical reads 50,978 52.2 191.7
physical reads cache 50,978 52.2 191.7
physical reads cache prefetch 2,877 3.0 10.8
physical reads direct 0 0.0 0.0
physical reads direct temporary t 0 0.0 0.0
physical write IO requests 20,124 20.6 75.7
physical write bytes 422,789,120 433,185.6 1,589,432.8
physical write total IO requests 23,227 23.8 87.3
physical write total bytes 492,718,592 504,834.6 1,852,325.5
physical write total multi block 197 0.2 0.7
physical writes 51,610 52.9 194.0
physical writes direct 36 0.0 0.1
physical writes direct (lob) 0 0.0 0.0
physical writes direct temporary 0 0.0 0.0
physical writes from cache 51,574 52.8 193.9
physical writes non checkpoint 26,040 26.7 97.9
pinned buffers inspected 54 0.1 0.2
prefetched blocks aged out before 111 0.1 0.4
process last non-idle time 972 1.0 3.7
recovery blocks read 0 0.0 0.0
recursive calls 111,998 114.8 421.1
recursive cpu usage 1,671 1.7 6.3
redo blocks checksummed by FG (ex 2,853 2.9 10.7
redo blocks read for recovery 0 0.0 0.0
redo blocks read total 0 0.0 0.0
redo blocks written 59,877 61.4 225.1
redo blocks written for direct wr 208 0.2 0.8
redo buffer allocation retries 0 0.0 0.0
redo entries 192,743 197.5 724.6
redo log space requests 0 0.0 0.0
redo log space wait time 0 0.0 0.0
redo ordering marks 132 0.1 0.5
Instance Activity Stats DB/Inst: NOVEMBER/november Snaps: 1-2
Statistic Total per Second per Trans
--------------------------------- ------------------ -------------- ------------
redo size 29,208,980 29,927.2 109,808.2
redo subscn max counts 2,123 2.2 8.0
redo synch time 2,409 2.5 9.1
redo synch writes 142 0.2 0.5
redo wastage 191,152 195.9 718.6
redo write time 7,897 8.1 29.7
redo writer latching time 0 0.0 0.0
redo writes 654 0.7 2.5
rollback changes - undo records a 0 0.0 0.0
rollbacks only - consistent read 296 0.3 1.1
rows fetched via callback 3,091 3.2 11.6
session connect time 311 0.3 1.2
session cursor cache hits 8,025 8.2 30.2
session logical reads 607,445 622.4 2,283.6
session pga memory 29,739,064 30,470.4 111,801.0
session pga memory max 32,622,648 33,424.8 122,641.5
session uga memory 68,724,248,184 70,414,188.7 ############
session uga memory max 79,268,608 81,217.8 298,002.3
shared hash latch upgrades - no w 18,385 18.8 69.1
shared hash latch upgrades - wait 24 0.0 0.1
sorts (memory) 2,464 2.5 9.3
sorts (rows) 162,621 166.6 611.4
sql area evicted 484 0.5 1.8
sql area purged 1 0.0 0.0
summed dirty queue length 246,528 252.6 926.8
switch current to new buffer 49 0.1 0.2
table fetch by rowid 12,074 12.4 45.4
table fetch continued row 4 0.0 0.0
table scan blocks gotten 9,099 9.3 34.2
table scan rows gotten 366,065 375.1 1,376.2
table scans (direct read) 0 0.0 0.0
table scans (long tables) 1 0.0 0.0
table scans (short tables) 1,711 1.8 6.4
total number of times SMON posted 5 0.0 0.0
transaction rollbacks 0 0.0 0.0
transaction tables consistent rea 0 0.0 0.0
transaction tables consistent rea 0 0.0 0.0
undo change vector size 950,548 973.9 3,573.5
user I/O wait time 635,615 651.2 2,389.5
user calls 1,702 1.7 6.4
user commits 250 0.3 0.9
user rollbacks 16 0.0 0.1
workarea executions - optimal 1,659 1.7 6.2
write clones created in backgroun 3 0.0 0.0
write clones created in foregroun 69 0.1 0.3
-------------------------------------------------------------
Instance Activity Stats DB/Inst: NOVEMBER/november Snaps: 1-2
-> Statistics with absolute values (should not be diffed)
Statistic Begin Value End Value
--------------------------------- --------------- ---------------
logons current 45 45
opened cursors current 135 150
session cursor cache count 5,723 7,444
-------------------------------------------------------------
Instance Activity Stats DB/Inst: NOVEMBER/november Snaps: 1-2
-> Statistics identified by '(derived)' come from sources other than SYSSTAT
Statistic Total per Hour
--------------------------------- ------------------ ---------
log switches (derived) 0 .00
-------------------------------------------------------------
OS Statistics DB/Inst: NOVEMBER/november Snaps: 1-2
-> ordered by statistic type (CPU use, Virtual Memory, Hardware Config), Name
Statistic Total
------------------------- ----------------------
BUSY_TIME 17,573
IDLE_TIME 180,908
SYS_TIME 9,300
USER_TIME 8,273
RSRC_MGR_CPU_WAIT_TIME 8,022
VM_IN_BYTES 156,905,472
VM_OUT_BYTES 110,821,376
PHYSICAL_MEMORY_BYTES 1,071,755,264
NUM_CPUS 2
-------------------------------------------------------------
OS Statistics - detail DB/Inst: NOVEMBER/november Snaps: 1-2
Snap Snapshot
Id Day Time Load %Busy %User %System %WIO %WCPU
------ --------------- ------ ------ ------ ------- ------ ------
1 Sun 29 13:22:24
2 Sun 29 13:38:40 8.9 4.2 4.7
-------------------------------------------------------------
IO Stat by Function - summary DB/Inst: NOVEMBER/november Snaps: 1-2
->Data Volume values suffixed with M,G,T,P are in multiples of 1024,
other values suffixed with K,M,G,T,P are in multiples of 1000
->ordered by Data Volume (Read+Write) desc
---------- Read --------- --------- Write -------- --- Wait ----
Data Requests Data Data Requests Data Avg
Function Volume /sec Vol/sec Volume /sec Vol/sec Count Tm(ms)
--------------- ------ -------- -------- ------ -------- -------- ------ ------
DBWR .0 389M 20.0 .4M 6 0.0
Buffer Cache Re 389M 48.2 .4M 47K 43.5
Others 17M 1.1 .0M 37M 2.4 .0M 1083 0.1
Streams AQ .0 33 0.2
-------------------------------------------------------------
IO Stat by Function - detail DB/Inst: NOVEMBER/november Snaps: 1-2
->ordered by Data Volume (Read+Write) desc
----------- Read ---------- ----------- Write ---------
Small Large Small Large Small Large Small Large
Read Read Data Data Write Write Data Data
Function Reqs Reqs Read Read Reqs Reqs Writn Writn
------------------ ------ ------ ------ ------ ------ ------ ------ ------
DBWR 6 19K 146 371M 18M
Buffer Cache Reads 47K 56 373M 16M
Others 1082 17M 2295 2 36M 1M
Streams AQ 33
-------------------------------------------------------------
Tablespace IO Stats DB/Inst: NOVEMBER/november Snaps: 1-2
->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)
-------------- ------- ------ ------- ------------ -------- ---------- ------
UNDOTBS1
36,287 37 121.8 1.0 17,186 18 2,212 68.2
SYSAUX
8,022 8 137.4 1.2 2,090 2 51 124.9
SYSTEM
2,620 3 182.8 1.5 79 0 27 301.1
USERS
201 0 201.4 1.1 192 0 0 0.0
TEMP
16 0 82.5 1.0 10 0 0 0.0
-------------------------------------------------------------
File IO Stats DB/Inst: NOVEMBER/november Snaps: 1-2
->Mx Rd Bkt: Max bucket time for single block read
->ordered by Tablespace, File
Tablespace Filename
------------------------ ----------------------------------------------------
Av Mx Av
Av Rd Rd Av Av Buffer BufWt
Reads Reads/s (ms) Bkt Blks/Rd Writes Writes/s Waits (ms)
-------------- ------- ----- --- ------- ------------ -------- ---------- ------
SYSAUX G:\APP\ALEX\ORADATA\NOVEMBER\SYSAUX01.DBF
8,022 8 ##### ### 1.2 2,090 2 51 124.9
SYSTEM G:\APP\ALEX\ORADATA\NOVEMBER\SYSTEM01.DBF
2,620 3 ##### ### 1.5 79 0 27 301.1
TEMP G:\APP\ALEX\ORADATA\NOVEMBER\TEMP01.DBF
16 0 82.5 1.0 10 0 0
UNDOTBS1 G:\APP\ALEX\ORADATA\NOVEMBER\UNDOTBS01.DBF
36,287 37 ##### ### 1.0 17,186 18 2,212 68.2
USERS G:\APP\ALEX\ORADATA\NOVEMBER\USERS01.DBF
201 0 ##### ### 1.1 192 0 0
-------------------------------------------------------------
File Read Histogram Stats DB/Inst: NOVEMBER/november Snaps: 1-2
->Number of single block reads in each time range
->Tempfiles are not included
->ordered by Tablespace, File
Tablespace Filename
------------------------ ----------------------------------------------------
0 - 2 ms 2 - 4 ms 4 - 8 ms 8 - 16 ms 16 - 32 ms 32+ ms
------------ ------------ ------------ ------------ ------------ ------------
UNDOTBS1 G:\APP\ALEX\ORADATA\NOVEMBER\UNDOTBS01.DBF
22 6 88 677 2,817 32,859
USERS G:\APP\ALEX\ORADATA\NOVEMBER\USERS01.DBF
0 0 0 0 1 198
SYSAUX G:\APP\ALEX\ORADATA\NOVEMBER\SYSAUX01.DBF
4 2 3 36 187 7,907
SYSTEM G:\APP\ALEX\ORADATA\NOVEMBER\SYSTEM01.DBF
0 0 3 23 84 2,336
-------------------------------------------------------------
Instance Recovery Stats DB/Inst: NOVEMBER/november Snaps: 1-2
-> 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 53 6431 11841 97867 184320 97867
E 0 56 8646 24327 107935 184320 107935
-------------------------------------------------------------
Memory Target Advice DB/Inst: NOVEMBER/november Snaps: 1-2
-> Advice Reset: if this is null, the data shown has been diffed between
the Begin and End snapshots. If this is 'Y', the advisor has been
reset during this interval due to memory resize operations, and
the data shown is since the reset operation.
Memory Size Est. Advice
Memory Size (M) Factor DB time (s) Reset
--------------- ----------- ------------ ------
204 .5 55,227
306 .8 20,311
408 1.0 9,110
510 1.3 9,110
612 1.5 7,359
714 1.8 7,346
816 2.0 7,350
-------------------------------------------------------------
Memory Dynamic Components DB/Inst: NOVEMBER/november Snaps: 1-2
-> Op - memory resize Operation
-> Cache: D: Default, K: Keep, R: Recycle
-> Mode: DEF: DEFerred mode, IMM: IMMediate mode
Begin Snap End Snap Op Last Op
Cache Size (M) Size (M) Count Type/Mode Last Op Time
---------------------- ---------- -------- ------- ---------- ---------------
D:buffer cache 136 0 GROW/DEF 29-Nov 13:13:56
PGA Target 108 0 GROW/DEF 29-Nov 13:09:44
SGA Target 296 0 SHRINK/DEF 29-Nov 13:11:46
java pool 12 0 GROW/IMM 29-Nov 12:53:22
large pool 4 0 STATIC
shared pool 136 0 SHRINK/DEF 29-Nov 13:13:56
-------------------------------------------------------------
Buffer Pool Advisory DB/Inst: NOVEMBER/november End Snap: 2
-> Only rows with estimated physical reads >0 are displayed
-> ordered by Pool, Block Size, Buffers For Estimate
Est
Phys Estimated Est
Size for Size Buffers Read Phys Reads Est Phys % dbtime
P Est (M) Factr (thousands) Factr (thousands) Read Time for Rds
--- -------- ----- ------------ ------ -------------- ------------ --------
D 12 .1 1 3.3 649 92,811 251.6
D 24 .2 3 2.8 552 77,912 211.2
D 36 .3 4 2.5 487 68,023 184.4
D 48 .4 6 2.2 435 59,986 162.6
D 60 .4 7 2.0 392 53,394 144.7
D 72 .5 9 1.8 355 47,630 129.1
D 84 .6 10 1.6 318 41,962 113.8
D 96 .7 12 1.4 282 36,527 99.0
D 108 .8 13 1.3 251 31,702 85.9
D 120 .9 15 1.1 224 27,655 75.0
D 132 1.0 16 1.0 203 24,335 66.0
D 136 1.0 17 1.0 196 23,348 63.3
D 144 1.1 18 0.9 186 21,709 58.8
D 156 1.1 19 0.9 175 20,040 54.3
D 168 1.2 21 0.8 165 18,556 50.3
D 180 1.3 22 0.8 157 17,312 46.9
D 192 1.4 24 0.8 150 16,274 44.1
D 204 1.5 25 0.7 145 15,434 41.8
D 216 1.6 27 0.7 141 14,889 40.4
D 228 1.7 28 0.7 138 14,408 39.1
D 240 1.8 30 0.7 135 13,983 37.9
-------------------------------------------------------------
Buffer Pool Statistics DB/Inst: NOVEMBER/november Snaps: 1-2
-> Standard block size Pools D: default, K: keep, R: recycle
-> Default Pools for other block sizes: 2k, 4k, 8k, 16k, 32k
-> Buffers: the number of buffers. Units of K, M, G are divided by 1000
Free Writ Buffer
Pool Buffer Physical Physical Buffer Comp Busy
P Buffers Hit% Gets Reads Writes Waits Wait Waits
--- ------- ---- -------------- ------------ ----------- ------- ---- ----------
D 16K 92 598,442 50,555 50,862 0 0 2,507
-------------------------------------------------------------
Buffer wait Statistics DB/Inst: NOVEMBER/november Snaps: 1-2
-> ordered by wait time desc, waits desc
Class Waits Total Wait Time (s) Avg Time (ms)
---------------------- ----------- ------------------- -------------
undo block 2,433 190 78
undo header 46 21 452
data block 145 17 120
segment header 7 5 671
-------------------------------------------------------------
PGA Aggr Target Stats DB/Inst: NOVEMBER/november Snaps: 1-2
-> B: Begin snap E: End snap (rows identified with B or E contain data
which is absolute i.e. not diffed over the interval)
-> PGA cache hit % - percentage of W/A (WorkArea) data processed only in-memory
-> Auto PGA Target - actual workarea memory target
-> W/A PGA Used - amount of memory used for all WorkAreas (manual + auto)
-> %PGA W/A Mem - percentage of PGA memory allocated to WorkAreas
-> %Auto W/A Mem - percentage of WorkArea memory controlled by Auto Mem Mgmt
-> %Man W/A Mem - percentage of WorkArea memory under Manual control
PGA Cache Hit % W/A MB Processed Extra W/A MB Read/Written
--------------- ---------------- -------------------------
100.0 134 0
PGA Aggr Target Histogram DB/Inst: NOVEMBER/november Snaps: 1-2
-> Optimal Executions are purely in-memory operations
Low High
Optimal Optimal Total Execs Optimal Execs 1-Pass Execs M-Pass Execs
------- ------- -------------- ------------- ------------ ------------
2K 4K 1,575 1,575 0 0
64K 128K 9 9 0 0
128K 256K 4 4 0 0
512K 1024K 121 121 0 0
1M 2M 2 2 0 0
2M 4M 4 4 0 0
-------------------------------------------------------------
PGA Memory Advisory DB/Inst: NOVEMBER/november End Snap: 2
-> When using Auto Memory Mgmt, minimally choose a pga_aggregate_target value
where Estd PGA Overalloc Count is 0
Estd Extra Estd
PGA Aggr W/A MB Estd Time PGA Estd PGA
Target Size W/A MB Read/Written to Process Cache Overalloc
Est (MB) Factr Processed to Disk Bytes (s) Hit % Count
---------- ------ -------------- -------------- ---------- ------ ----------
14 0.1 277 160 0.0 63.0 21
27 0.3 277 160 0.0 63.0 21
54 0.5 277 143 0.0 66.0 17
81 0.8 277 0 0.0 100.0 0
108 1.0 277 0 0.0 100.0 0
130 1.2 277 0 0.0 100.0 0
151 1.4 277 0 0.0 100.0 0
173 1.6 277 0 0.0 100.0 0
194 1.8 277 0 0.0 100.0 0
216 2.0 277 0 0.0 100.0 0
324 3.0 277 0 0.0 100.0 0
432 4.0 277 0 0.0 100.0 0
648 6.0 277 0 0.0 100.0 0
864 8.0 277 0 0.0 100.0 0
-------------------------------------------------------------
Process Memory Summary Stats DB/Inst: NOVEMBER/november Snaps: 1-2
-> B: Begin snap E: End snap
-> All rows below contain absolute values (i.e. not diffed over the interval)
-> Max Alloc is Maximum PGA Allocation size at snapshot time
Hist Max Alloc is the Historical Max Allocation for still-connected processes
-> Num Procs or Allocs: For Begin/End snapshot lines, it is the number of
processes. For Category lines, it is the number of allocations
-> ordered by Begin/End snapshot, Alloc (MB) desc
Hist Num
Avg Std Dev Max Max Procs
Alloc Used Freeabl Alloc Alloc Alloc Alloc or
Category (MB) (MB) (MB) (MB) (MB) (MB) (MB) Allocs
- -------- --------- --------- -------- -------- ------- ------- ------ ------
B -------- 92.8 54.8 .0 1.9 2.3 13 25 48
Other 88.9 1.9 2.2 13 16 48
SQL 3.3 1.6 .1 .1 0 8 29
PL/SQL .9 .6 .0 .0 0 7 46
E -------- 93.4 56.4 .0 1.9 2.3 13 25 48
Other 89.0 1.9 2.2 13 16 48
SQL 3.4 1.5 .1 .1 0 8 29
PL/SQL 1.0 .6 .0 .0 0 7 46
-------------------------------------------------------------
Top Process Memory (by component) DB/Inst: NOVEMBER/november Snaps: 1-2
-> ordered by Begin/End snapshot, Alloc (MB) desc
Alloc Used Freeabl Max Hist Max
PId Category (MB) (MB) (MB) Alloc (MB) Alloc (MB)
- ------ ------------- ------- ------- -------- ---------- ----------
B 52 J008 -------- 12.9 7.2 .0 12.9 22.7
Other 12.8 12.8 14.3
SQL .1 .1 .1 8.3
PL/SQL .1 .0 .1 .1
11 LGWR -------- 9.2 4.4 .0 9.2 9.9
Other 9.2 9.2 9.9
PL/SQL .0 .0 .0 .0
50 J006 -------- 7.1 3.6 .0 7.1 7.4
Other 6.8 6.8 6.8
SQL .2 .1 .2 1.0
PL/SQL .1 .1 .1 .1
10 DBW0 -------- 3.6 2.2 .0 3.6 3.6
Other 3.6 3.6 3.6
PL/SQL .0 .0 .0 .0
40 SHAD -------- 3.2 2.3 .0 3.2 7.4
Other 2.7 2.7 3.6
SQL .5 .4 .5 3.8
PL/SQL .0 .0 .0 .0
41 SHAD -------- 3.1 2.1 .0 3.1 3.3
Other 2.8 2.8 2.8
SQL .2 .1 .2 1.0
PL/SQL .1 .0 .1 .1
38 SHAD -------- 2.5 2.2 .0 2.5 6.7
Other 2.3 2.3 4.3
SQL .2 .1 .2 2.3
PL/SQL .0 .0 .0 .1
51 J007 -------- 2.4 1.8 .0 2.4 5.7
Other 2.2 2.2 4.1
PL/SQL .1 .1 .1 .1
SQL .1 .1 .1 1.4
15 MMON -------- 2.2 1.7 .0 2.2 3.5
Other 2.1 2.1 2.6
SQL .1 .0 .1 .8
PL/SQL .0 .0 .0 .0
39 SHAD -------- 2.2 1.4 .0 2.2 2.9
Other 2.1 2.1 2.1
SQL .1 .0 .1 .8
PL/SQL .0 .0 .0 .0
36 SHAD -------- 2.1 1.7 .0 2.1 25.4
Other 2.2 2.2 16.0
SQL .1 .1 .1 2.9
PL/SQL .0 .0 .0 6.5
13 SMON -------- 2.1 .9 .0 2.1 3.2
Other 1.9 1.9 1.9
SQL .2 .1 .2 2.0
PL/SQL .0 .0 .0 .0
34 SHAD -------- 2.0 1.2 .0 2.0 2.1
Other 1.9 1.9 1.9
SQL .1 .0 .1 .5
PL/SQL .0 .0 .0 .0
45 SHAD -------- 1.9 1.3 .0 1.9 2.2
Other 1.8 1.8 1.8
SQL .1 .0 .1 .6
Top Process Memory (by component) DB/Inst: NOVEMBER/november Snaps: 1-2
-> ordered by Begin/End snapshot, Alloc (MB) desc
Alloc Used Freeabl Max Hist Max
PId Category (MB) (MB) (MB) Alloc (MB) Alloc (MB)
- ------ ------------- ------- ------- -------- ---------- ----------
B 45 PL/SQL .0 .0 .0 .0
35 SHAD -------- 1.9 1.0 .0 1.9 3.9
Other 1.8 1.8 2.3
SQL .2 .1 .2 1.5
PL/SQL .0 .0 .0 .1
31 SHAD -------- 1.8 1.1 .0 1.8 1.9
Other 1.7 1.7 1.7
SQL .1 .0 .1 .6
PL/SQL .0 .0 .0 .0
23 FBDA -------- 1.7 1.0 .0 1.7 2.0
Other 1.6 1.6 1.6
SQL .2 .1 .2 .5
PL/SQL .0 .0 .0 .0
12 CKPT -------- 1.7 .5 .0 1.7 1.7
Other 1.7 1.7 1.7
PL/SQL .0 .0 .0 .0
33 SHAD -------- 1.7 1.2 .0 1.7 1.9
Other 1.6 1.6 1.6
SQL .1 .0 .1 .6
PL/SQL .0 .0 .0 .0
29 SHAD -------- 1.5 1.1 .0 1.5 2.0
Other 1.5 1.5 1.5
SQL .0 .0 .0 .5
PL/SQL .0 .0 .0 .0
E 52 J008 -------- 12.9 7.2 .0 12.9 22.7
Other 12.7 12.7 14.3
SQL .2 .1 .2 8.3
PL/SQL .1 .0 .1 .1
11 LGWR -------- 9.2 4.4 .0 9.2 9.9
Other 9.2 9.2 9.9
PL/SQL .0 .0 .0 .0
50 J006 -------- 7.1 3.6 .0 7.1 7.4
Other 6.8 6.8 6.8
SQL .2 .1 .2 1.0
PL/SQL .1 .1 .1 .1
10 DBW0 -------- 3.6 2.2 .0 3.6 3.6
Other 3.6 3.6 3.6
PL/SQL .0 .0 .0 .0
45 SHAD -------- 2.9 2.0 .0 2.9 2.9
Other 2.7 2.7 2.7
SQL .1 .1 .1 .7
PL/SQL .1 .0 .1 .1
38 SHAD -------- 2.9 2.3 .0 2.9 4.1
Other 2.8 2.8 3.2
SQL .1 .0 .1 .8
PL/SQL .0 .0 .0 .1
36 SHAD -------- 2.6 1.6 .0 2.6 25.4
Other 2.4 2.4 16.0
SQL .2 .1 .2 2.9
PL/SQL .0 .0 .0 6.5
15 MMON -------- 2.5 1.7 .0 2.5 3.5
Other 2.4 2.4 2.6
SQL .1 .0 .1 .8
Top Process Memory (by component) DB/Inst: NOVEMBER/november Snaps: 1-2
-> ordered by Begin/End snapshot, Alloc (MB) desc
Alloc Used Freeabl Max Hist Max
PId Category (MB) (MB) (MB) Alloc (MB) Alloc (MB)
- ------ ------------- ------- ------- -------- ---------- ----------
E 15 PL/SQL .0 .0 .0 .0
51 J007 -------- 2.5 1.9 .0 2.5 5.7
Other 2.2 2.2 4.1
PL/SQL .2 .2 .2 .2
SQL .2 .1 .2 1.4
31 SHAD -------- 2.4 2.1 .0 2.4 4.5
Other 2.2 2.2 3.1
SQL .1 .1 .1 1.3
PL/SQL .0 .0 .0 .1
40 SHAD -------- 2.3 1.8 .0 2.3 7.9
Other 2.1 2.1 4.0
SQL .2 .1 .2 3.8
PL/SQL .0 .0 .0 .0
30 SHAD -------- 2.3 2.0 .0 2.3 3.2
Other 2.0 2.0 2.3
SQL .2 .1 .2 .8
PL/SQL .0 .0 .0 .0
13 SMON -------- 2.1 .9 .0 2.1 3.2
Other 1.9 1.9 1.9
SQL .2 .1 .2 2.0
PL/SQL .0 .0 .0 .0
35 SHAD -------- 1.9 1.0 .0 1.9 3.9
Other 1.8 1.8 2.3
SQL .2 .1 .2 1.5
PL/SQL .0 .0 .0 .1
39 SHAD -------- 1.9 1.6 .0 1.9 2.5
Other 1.7 1.7 1.9
SQL .2 .1 .2 .6
PL/SQL .0 .0 .0 .0
18 SHAD -------- 1.8 1.3 .0 1.8 2.3
Other 1.7 1.7 1.7
SQL .1 .0 .1 .8
PL/SQL .0 .0 .0 .0
23 FBDA -------- 1.8 1.1 .0 1.8 2.2
Other 1.6 1.6 1.6
SQL .2 .1 .2 .6
PL/SQL .0 .0 .0 .0
12 CKPT -------- 1.7 .5 .0 1.7 1.7
Other 1.7 1.7 1.7
PL/SQL .0 .0 .0 .0
25 SHAD -------- 1.6 1.0 .0 1.6 1.6
Other 1.5 1.5 1.5
SQL .1 .0 .1 .2
PL/SQL .0 .0 .0 .0
26 q000 -------- 1.6 .9 .0 1.6 1.9
Other 1.5 1.5 1.5
SQL .1 .1 .1 .7
PL/SQL .0 .0 .0 .0
-------------------------------------------------------------
Enqueue activity DB/Inst: NOVEMBER/november Snaps: 1-2
-> only enqueues with waits are shown
-> Enqueue stats gathered prior to 10g should not be compared with 10g data
-> ordered by Wait Time desc, Waits desc
Enqueue Type (Request Reason)
------------------------------------------------------------------------------
Requests Succ Gets Failed Gets Waits Wt Time (s) Av Wt Time(ms)
------------ ------------ ----------- ----------- ------------ --------------
PR-Process Startup
20 20 0 1 81 80,750.00
TX-Transaction (row lock contention)
1 1 0 1 24 24,281.00
PS-PX Process Reservation
80 80 0 7 19 2,718.71
UL-User-defined
33 13 20 1 6 5,672.00
CF-Controlfile Transaction
409 407 2 1 0 391.00
-------------------------------------------------------------
Undo Segment Summary DB/Inst: NOVEMBER/november Snaps: 1-2
-> Min/Max TR (mins) - Min and Max Tuned Retention (minutes)
-> STO - Snapshot Too Old count, OOS - Out Of Space count
-> Undo segment block stats:
uS - unexpired Stolen, uR - unexpired Released, uU - unexpired reUsed
eS - expired Stolen, eR - expired Released, eU - expired reUsed
Undo Num Undo Number of Max Qry Max Tx Min/Max STO/ uS/uR/uU/
TS# Blocks (K) Transactions Len (s) Concy TR (mins) OOS eS/eR/eU
---- ---------- --------------- -------- ---------- --------- ----- -----------
2 .1 558 2,066 5 29,3/49 0/0 0/0/0/0/0/0
-------------------------------------------------------------
Undo Segment Stats DB/Inst: NOVEMBER/november Snaps: 1-2
-> Most recent 35 Undostat rows, ordered by End Time desc
Num Undo Number of Max Qry Max Tx Tun Ret STO/ uS/uR/uU/
End Time Blocks Transactions Len (s) Concy (mins) OOS eS/eR/eU
------------ ----------- ------------ ------- ------- ------- ----- -----------
29-Nov 13:37 13 109 978 4 29 0/0 0/0/0/0/0/0
29-Nov 13:27 106 449 2,066 5 49 0/0 0/0/0/0/0/0
-------------------------------------------------------------
Latch Activity DB/Inst: NOVEMBER/november Snaps: 1-2
->"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
------------------------ -------------- ------ ------ ------ ------------ ------
ASM db client latch 550 0.0 0 0
ASM map operation hash t 1 0.0 0 0
Change Notification Hash 289 0.0 0 0
Consistent RBA 657 0.0 0 0
DML lock allocation 679,003 0.0 0 0
Event Group Locks 31 0.0 0 0
FIB s.o chain latch 2 0.0 0 0
FOB s.o list latch 124 0.0 0 0
File State Object Pool P 1 0.0 0 0
IPC stats buffer allocat 1 0.0 0 0
In memory undo latch 3,655 0.1 0.0 0 643 0.0
JS Sh mem access 3 0.0 0 0
JS mem alloc latch 6 0.0 0 0
JS queue access latch 7 0.0 0 0
JS queue state obj latch 6,372 0.0 0 0
JS slv state obj latch 42 0.0 0 0
KFC FX Hash Latch 1 0.0 0 0
KFC Hash Latch 1 0.0 0 0
KFCL LE Freelist 1 0.0 0 0
KFR redo allocation latc 1 0.0 0 0
KGNFS-NFS:SHM structure 1 0.0 0 0
KGNFS-NFS:SVR LIST 1 0.0 0 0
KJC message pool free li 1 0.0 0 0
KJCT flow control latch 1 0.0 0 0
KMG MMAN ready and start 330 0.0 0 0
KTF sga latch 2 0.0 0 271 0.0
KWQMN job cache list lat 61 0.0 0 0
KWQP Prop Status 1 0.0 0 0
KWQS pqsubs latch 28 0.0 0 0
KWQS pqueue ctx latch 39 0.0 0 0
Locator state objects po 1 0.0 0 0
MQL Tracking Latch 0 0 19 0.0
Memory Management Latch 1 0.0 0 330 0.0
Memory Queue 1 0.0 0 0
Memory Queue Message Sub 1 0.0 0 0
Memory Queue Message Sub 1 0.0 0 0
Memory Queue Message Sub 1 0.0 0 0
Memory Queue Message Sub 1 0.0 0 0
Memory Queue Subscriber 1 0.0 0 0
MinActiveScn Latch 2,226 0.0 0 0
Mutex 1 0.0 0 0
Mutex Stats 1 0.0 0 0
OS process 214 0.0 0 0
OS process allocation 471 0.0 0 0
OS process: request allo 61 0.0 0 0
PL/SQL warning settings 394 0.0 0 0
QMT 1 0.0 0 0
Real-time plan statistic 336 0.0 0 0
SGA blob parent 1 0.0 0 0
SGA bucket locks 1 0.0 0 0
Latch Activity DB/Inst: NOVEMBER/november Snaps: 1-2
->"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
------------------------ -------------- ------ ------ ------ ------------ ------
SGA heap locks 1 0.0 0 0
SGA pool locks 1 0.0 0 0
SQL memory manager latch 32 0.0 0 275 0.0
SQL memory manager worka 19,839 0.0 0 0
Shared B-Tree 108 0.0 0 0
Streams Generic 1 0.0 0 0
Testing 1 0.0 0 0
Token Manager 1 0.0 0 0
Write State Object Pool 1 0.0 0 0
XDB NFS Security Latch 1 0.0 0 0
XDB unused session pool 1 0.0 0 0
XDB used session pool 1 0.0 0 0
active checkpoint queue 13,138 0.0 0 0
active service list 2,131 0.0 0.0 0 381 0.0
archive control 14 0.0 0 0
begin backup scn array 4 0.0 0 0
buffer pool 1 0.0 0 0
business card 1 0.0 0 0
cache buffer handles 440 0.0 0 0
cache buffers chains 2,235,888 0.1 0.0 0 57,763 0.0
cache buffers lru chain 158,886 0.0 0.2 0 2,398 0.0
cache table scan latch 191 0.0 0 191 0.0
cas latch 1 0.0 0 0
change notification clie 1 0.0 0 0
channel handle pool latc 68 0.0 0 0
channel operations paren 4,857 0.0 0 0
checkpoint queue latch 90,154 0.0 0.0 0 53,850 0.0
client/application info 694 0.0 0 0
commit callback allocati 79 0.0 0 0
compile environment latc 66 0.0 0 0
corrupted undo seg lock 191,094 0.0 0.0 0 0
cp cmon/server latch 1 0.0 0 0
cp pool latch 1 0.0 0 0
cp server hash latch 1 0.0 0 0
cp sga latch 43 0.0 0 0
cv apply list lock 1 0.0 0 0
cv free list lock 1 0.0 0 0
deferred cleanup latch 43 0.0 0 0
dml lock allocation 43 0.0 0 0
done queue latch 1 0.0 0 0
dummy allocation 134 1.5 0.0 0 0
enqueue hash chains 20,446 0.0 0.5 0 43 0.0
enqueues 16,441 0.0 1.0 0 0
error message lists 5 0.0 0 0
fifth spare latch 1 0.0 0 0
file cache latch 50 0.0 0 0
flashback archiver latch 3 0.0 0 0
flashback copy 1 0.0 0 0
gc element 1 0.0 0 0
gcs commit scn state 1 0.0 0 0
Latch Activity DB/Inst: NOVEMBER/november Snaps: 1-2
->"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
------------------------ -------------- ------ ------ ------ ------------ ------
gcs partitioned table ha 1 0.0 0 0
gcs pcm hashed value buc 1 0.0 0 0
gcs resource freelist 1 0.0 0 0
gcs resource hash 1 0.0 0 0
gcs resource scan list 1 0.0 0 0
gcs shadows freelist 1 0.0 0 0
ges domain table 1 0.0 0 0
ges enqueue table freeli 1 0.0 0 0
ges group table 1 0.0 0 0
ges process hash list 1 0.0 0 0
ges process parent latch 1 0.0 0 0
ges resource hash list 1 0.0 0 0
ges resource scan list 1 0.0 0 0
ges resource table freel 1 0.0 0 0
ges value block free lis 1 0.0 0 0
global KZLD latch for me 10 0.0 0 0
global ctx hash table la 12 0.0 0 0
global tx hash mapping 1 0.0 0 0
granule operation 1 0.0 0 0
hash table column usage 317 0.0 0 88,022 0.1
hash table modification 46 0.0 0 0
intra txn parallel recov 18,695 0.0 0.0 0 25,263 0.0
io pool granule metadata 1 0.0 0 0
job workq parent latch 1 0.0 0 32 0.0
job_queue_processes para 31 0.0 0 0
k2q lock allocation 1 0.0 0 0
kdlx hb parent latch 1 0.0 0 0
kgb parent 1 0.0 0 0
kks stats 3,527 0.0 0 0
kokc descriptor allocati 248 0.0 0 0
ksfv messages 1 0.0 0 0
kss move lock 15 0.0 0 0
ksuosstats global area 63 0.0 0 0
ksv allocation latch 71 0.0 0 0
ksv class latch 29 0.0 0 0
ksv msg queue latch 1 0.0 0 0
ksz_so allocation latch 61 0.0 0 0
ktm global data 17 0.0 0 0
kwqbsn:qsga 39 0.0 0 0
lgwr LWN SCN 657 0.0 0 0
library cache load lock 1,670 0.0 0 0
list of block allocation 93 0.0 0 0
loader state object free 2 0.0 0 0
lob segment dispenser la 1 0.0 0 0
lob segment hash table l 5 0.0 0 0
lob segment query latch 1 0.0 0 0
lock DBA buffer during m 1 0.0 0 0
logical standby cache 1 0.0 0 0
logminer context allocat 1 0.0 0 0
logminer work area 1 0.0 0 0
Latch Activity DB/Inst: NOVEMBER/november Snaps: 1-2
->"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
------------------------ -------------- ------ ------ ------ ------------ ------
longop free list parent 382 0.0 0 1 0.0
mapped buffers lru chain 1 0.0 0 0
message pool operations 1 0.0 0 0
messages 35,487 0.0 0.0 0 0
mostly latch-free SCN 657 0.0 0 0
msg queue latch 1 0.0 0 0
multiblock read objects 398 0.0 0 0
name-service namespace b 1 0.0 0 0
ncodef allocation latch 43 0.0 0 0
object queue header heap 13,792 0.0 0 489 0.0
object queue header oper 229,820 0.0 0.0 0 0
object stats modificatio 146 0.0 0 0
parallel query alloc buf 245 0.0 0 0
parallel query stats 26 0.0 0 0
parallel txn reco latch 146,021 0.0 0.0 0 0
parameter list 102 0.0 0 0
parameter table manageme 133 2.3 0.0 0 0
peshm 1 0.0 0 0
pesom_free_list 1 0.0 0 0
pesom_hash_node 1 0.0 0 0
pesom_heap_alloc 1 0.0 0 0
post/wait queue 280 0.0 0 144 0.0
process allocation 81 1.2 5.0 0 30 0.0
process group creation 61 0.0 0 0
process queue 91 0.0 0 0
process queue reference 1,903,851 0.0 0 380,684 0.0
qmn task queue latch 149 10.7 0.9 0 0
query server freelists 95 1.1 0.0 0 0
queued dump request 3 0.0 0 0
recovery domain hash lis 1 0.0 0 0
redo allocation 3,365 0.1 0.5 0 193,354 0.0
redo copy 1 0.0 0 193,365 0.0
redo writing 15,920 0.0 0 0
resmgr group change latc 147 0.7 0.0 0 0
resmgr:active threads 484 0.0 0 150 0.0
resmgr:actses change gro 59 0.0 0 0
resmgr:actses change sta 3 0.0 0 0
resmgr:free threads list 143 0.0 0 0
resmgr:plan CPU method 1 0.0 0 0
resmgr:resource group CP 1 0.0 0 0
resmgr:schema config 208 0.0 0 300 0.0
resmgr:session queuing 1 0.0 0 0
rm cas latch 1 0.0 0 0
row cache objects 1,405,048 0.0 0.1 0 562 0.2
rules engine rule set st 100 0.0 0 0
second spare latch 1 0.0 0 0
sequence cache 103 0.0 0 0
session allocation 171,067 0.0 0.0 0 0
session idle bit 4,118 0.0 0.0 0 0
session queue latch 1 0.0 0 0
Latch Activity DB/Inst: NOVEMBER/november Snaps: 1-2
->"Get Requests", "Pct Get Miss" and "Avg Slps/Miss" are statistics for
willing-to-wait latch get requests
->"NoWait Requests", "Pct NoWait Miss" are for no-wait latch get requests
->"Pct Misses" for both should be very close to 0.0
Pct Avg Wait Pct
Get Get Slps Time NoWait NoWait
Latch Requests Miss /Miss (s) Requests Miss
------------------------ -------------- ------ ------ ------ ------------ ------
session state list latch 184 2.2 0.0 0 0
session switching 44 0.0 0 0
session timer 381 0.0 0 0
shared pool 58,302 0.1 0.5 23 0
shared pool sim alloc 18 0.0 0 0
shared pool simulator 30,798 0.0 1.0 0 0
sim partition latch 1 0.0 0 0
simulator hash latch 41,826 0.0 0 0
simulator lru latch 37,493 0.1 0.1 0 3,169 0.0
sort extent pool 2,036 0.0 0 0
space background state o 2 0.0 0 0
space background task la 1,112 0.3 1.0 0 660 0.0
tablespace key chain 1 0.0 0 0
test excl. parent l0 1 0.0 0 0
test excl. parent2 l0 1 0.0 0 0
third spare latch 1 0.0 0 0
threshold alerts latch 46 0.0 0 0
transaction allocation 152,907 0.0 0 0
transaction branch alloc 381 0.0 0 0
undo global data 99,477 0.0 0.0 0 0
user lock 72 0.0 0 0
virtual circuit buffers 1 0.0 0 0
virtual circuit holder 1 0.0 0 0
virtual circuit queues 1 0.0 0 0
-------------------------------------------------------------
Latch Sleep breakdown DB/Inst: NOVEMBER/november Snaps: 1-2
-> ordered by misses desc
Get Spin
Latch Name Requests Misses Sleeps Gets
-------------------------- --------------- ------------ ----------- -----------
cache buffers chains 2,235,888 2,728 48 2,686
row cache objects 1,405,048 541 40 508
shared pool 58,302 64 34 32
session allocation 171,067 49 2 47
cache buffers lru chain 158,886 44 9 36
object queue header operat 229,820 32 1 31
simulator lru latch 37,493 30 2 28
parallel txn reco latch 146,021 29 1 28
qmn task queue latch 149 16 15 1
space background task latc 1,112 3 3 0
shared pool simulator 30,798 3 3 0
enqueue hash chains 20,446 2 1 1
redo allocation 3,365 2 1 1
enqueues 16,441 1 1 0
process allocation 81 1 5 0
-------------------------------------------------------------
Latch Miss Sources DB/Inst: NOVEMBER/november Snaps: 1-2
-> only latches with sleeps are shown
-> ordered by name, sleeps desc
NoWait Waiter
Latch Name Where Misses Sleeps Sleeps
------------------------ -------------------------- ------- ---------- --------
In memory undo latch ktiFlush: child 0 1 0
PC and Classifier lists No latch 0 1 0
cache buffers chains kcbgcur: fast path (shr) 0 12 15
cache buffers chains kcbchg1: kslbegin: bufs no 0 10 6
cache buffers chains kcbgtcr: fast path (cr pin 0 9 10
cache buffers chains kcbgtcr: kslbegin shared 0 8 2
cache buffers chains kcbgtcr: fast path 0 5 10
cache buffers chains kcbgcur_6 0 4 0
cache buffers chains kcbchg1: kslbegin: call CR 0 3 7
cache buffers chains kcbzwb 0 3 7
cache buffers chains kcbrls_2 0 2 1
cache buffers chains kcbgtcr_9 0 2 3
cache buffers chains kcbgcur_2 0 1 0
cache buffers chains kcbchg1: no fast path 0 1 0
cache buffers lru chain kcbzgws_1 0 9 5
enqueue hash chains ksqrcl 0 1 0
enqueues ksqgtl2 0 1 0
object queue header oper kcbo_switch_cq 0 1 0
parallel txn reco latch ktprxrtf 0 1 1
process allocation ksuapc 0 4 0
process allocation ksucrp:1 0 1 0
qmn task queue latch kwqmnmvtsks: delay to read 0 15 0
redo allocation kcrfw_redo_gen: redo alloc 0 1 0
row cache objects kqrpre: find obj 0 29 17
row cache objects kqreqd: reget 0 10 0
row cache objects kqreqd 0 1 30
session allocation ksudlc 0 1 2
session allocation ksuprc 0 1 0
shared pool kghalo 0 12 9
shared pool kghupr1 0 9 19
shared pool kghfre 0 7 1
shared pool kghalp 0 5 5
shared pool kghfrh 0 1 0
shared pool simulator kglsim_upd_newhp 0 3 0
simulator lru latch kcbs_simulate: simulate se 0 2 2
space background task la ktsjCreateTask 0 1 0
space background task la ktsj_grab_task 0 1 3
-------------------------------------------------------------
Dictionary Cache Stats DB/Inst: NOVEMBER/november Snaps: 1-2
->"Pct Misses" should be very low (< 2% in most cases)
->"Final Usage" is the number of cache entries being used in End Snapshot
Get Pct Scan Pct Mod Final
Cache Requests Miss Reqs Miss Reqs Usage
------------------------- ------------ ------ ------- ----- -------- ----------
dc_awr_control 18 0.0 0 0 1
dc_database_links 23 0.0 0 0 1
dc_global_oids 889 1.7 0 0 55
dc_histogram_data 4,758 4.0 0 2 1,215
dc_histogram_defs 8,676 18.5 0 10 2,764
dc_object_grants 239 8.8 0 0 95
dc_objects 188,111 0.1 0 0 1,519
dc_profiles 28 0.0 0 0 1
dc_rollback_segments 179,705 0.0 0 0 22
dc_segments 1,914 7.7 0 2 653
dc_sequences 2 100.0 0 2 4
dc_tablespaces 182,100 0.0 0 0 6
dc_users 13,713 0.0 0 0 195
global database name 607 0.0 0 0 1
outstanding_alerts 14 0.0 0 0 6
-------------------------------------------------------------
Library Cache Activity DB/Inst: NOVEMBER/november Snaps: 1-2
->"Pct Misses" should be very low
Get Pct Pin Pct Invali-
Namespace Requests Miss Requests Miss Reloads dations
--------------- ------------ ------ -------------- ------ ---------- --------
SQL AREA 2,357 10.9 16,514 11.0 360 74
TABLE/PROCEDURE 4,893 2.2 7,433 11.0 396 0
BODY 500 0.0 1,239 0.3 2 0
TRIGGER 199 0.0 250 0.0 0 0
INDEX 8 50.0 8 75.0 1 0
CLUSTER 300 0.0 82 0.0 0 0
-------------------------------------------------------------
Rule Sets DB/Inst: NOVEMBER/november Snaps: 1-2
-> * indicates Rule Set activity (re)started between Begin/End snaps
-> Top 25 ordered by Evaluations desc
No-SQL SQL
Rule * Eval/sec Reloads/sec Eval % Eval %
----------------------------------- - ------------ ----------- ------ ------
SYS.ALERT_QUE_R 0 0 0 0
-------------------------------------------------------------
Shared Pool Advisory DB/Inst: NOVEMBER/november End Snap: 2
-> SP: Shared Pool Est LC: Estimated Library Cache Factr: Factor
-> 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
Est LC Est LC Est LC Est LC
Shared SP Est LC Time Time Load Load Est LC
Pool Size Size Est LC Saved Saved Time Time Mem
Size (M) Factr (M) Mem Obj (s) Factr (s) Factr Obj Hits
---------- ----- -------- ------------ ------- ------ ------- ------ -----------
120 .9 10 1,625 23,924 1.0 2,205 1.1 99,473
136 1.0 26 3,324 24,169 1.0 1,960 1.0 150,987
152 1.1 42 4,477 24,419 1.0 1,710 .9 152,408
168 1.2 58 5,680 24,560 1.0 1,569 .8 153,179
184 1.4 74 7,140 24,629 1.0 1,500 .8 153,523
200 1.5 90 8,723 24,661 1.0 1,468 .7 153,701
216 1.6 102 10,349 24,670 1.0 1,459 .7 153,766
232 1.7 106 10,899 24,671 1.0 1,458 .7 153,774
248 1.8 106 10,899 24,671 1.0 1,458 .7 153,775
264 1.9 106 10,899 24,671 1.0 1,458 .7 153,776
280 2.1 106 10,899 24,671 1.0 1,458 .7 153,776
-------------------------------------------------------------
Java Pool Advisory DB/Inst: NOVEMBER/november End Snap: 2
Est LC Est LC Est LC Est LC
Java JP Est LC Time Time Load Load Est LC
Pool Size Size Est LC Saved Saved Time Time Mem
Size(M) Factr (M) Mem Obj (s) Factr (s) Factr Obj Hits
---------- ----- -------- ------------ ------- ------ ------- ------ -----------
12 1.0 1 188 569 1.0 1,962 1.0 180
16 1.3 1 217 569 1.0 1,962 1.0 208
20 1.7 1 217 569 1.0 1,962 1.0 208
24 2.0 1 217 569 1.0 1,962 1.0 208
-------------------------------------------------------------
SGA Target Advisory DB/Inst: NOVEMBER/november End Snap: 2
SGA Target SGA Size Est DB Est DB Est Physical
Size (M) Factor Time (s) Time Factor Reads
---------- -------- -------- ----------- --------------
148 .5 121,064 3.3 770,159
222 .8 57,274 1.6 334,961
296 1.0 36,913 1.0 196,044
370 1.3 31,734 .9 160,717
444 1.5 30,918 .8 160,717
518 1.8 30,889 .8 160,717
592 2.0 30,896 .8 160,717
-------------------------------------------------------------
SGA Memory Summary DB/Inst: NOVEMBER/november Snaps: 1-2
End Size (Bytes)
SGA regions Begin Size (Bytes) (if different)
------------------------------ -------------------- --------------------
Database Buffers 142,606,336
Fixed Size 1,333,648
Redo Buffers 6,086,656
Variable Size 276,825,712
-------------------- --------------------
sum 426,852,352
-------------------------------------------------------------
SGA breakdown difference DB/Inst: NOVEMBER/november Snaps: 1-2
-> Top 35 rows by size, ordered by Pool, Name (note rows with null values for
Pool column, or Names showing free memory are always shown)
-> Null value for Begin MB or End MB indicates the size of that Pool/Name was
insignificant, or zero in that snapshot
Pool Name Begin MB End MB % Diff
------ ------------------------------ -------------- -------------- --------
java p free memory 3.5 3.5 0.00
java p joxlod exec hp 8.3 8.3 0.00
java p joxs heap .3 .3 0.00
large PX msg pool .1 .1 0.00
large free memory 3.9 3.9 0.00
shared ASH buffers 4.0 4.0 0.00
shared CCursor 7.3 5.8 -20.32
shared Heap0: KGL 4.2 4.3 2.58
shared KCB Table Scan Buffer 3.8 3.8 0.00
shared KGH: NO ACCESS 6.1 6.1 0.00
shared KGL buckets 3.0 3.0 0.00
shared KGL handle 6.5 6.6 1.04
shared KGLS heap 3.6 3.3 -5.83
shared KQR M PO 2.0 2.1 6.80
shared KSFD SGA I/O b 3.8 3.8 0.00
shared PCursor 2.8 2.9 5.27
shared PL/SQL DIANA 13.8 10.8 -21.94
shared PL/SQL MPCODE 13.3 13.2 -0.03
shared event statistics per sess 1.7 1.7 0.00
shared free memory 14.9 19.8 32.85
shared kglsim hash table bkts 2.0 2.0 0.00
shared obj stats allocation chun 2.1 2.1 0.00
shared row cache 3.6 3.6 0.00
shared sessions 1.7 1.7 0.00
shared sql area 11.9 10.9 -7.91
shared type object de 2.8 2.8 -0.03
buffer_cache 136.0 136.0 0.00
fixed_sga 1.3 1.3 0.00
log_buffer 5.8 5.8 0.00
-------------------------------------------------------------
SQL Memory Statistics DB/Inst: NOVEMBER/november Snaps: 1-2
Begin End % Diff
-------------- -------------- --------------
Avg Cursor Size (KB): 12.95 15.43 16.09
Cursor to Parent ratio: 1.61 1.30 -23.32
Total Cursors: 1,412 1,115 -26.64
Total Parents: 879 856 -2.69
-------------------------------------------------------------
init.ora Parameters DB/Inst: NOVEMBER/november Snaps: 1-2
End value
Parameter Name Begin value (if different)
----------------------------- --------------------------------- --------------
audit_file_dest G:\APP\ALEX\ADMIN\NOVEMBER\ADUMP
audit_trail DB
compatible 11.1.0.0.0
control_files G:\APP\ALEX\ORADATA\NOVEMBER\CONT
ROL01.CTL, G:\APP\ALEX\ORADATA\NO
VEMBER\CONTROL02.CTL, G:\APP\ALEX
\ORADATA\NOVEMBER\CONTROL03.CTL
db_block_size 8192
db_domain
db_name november
db_recovery_file_dest g:\app\alex\flash_recovery_area
db_recovery_file_dest_size 2147483648
diagnostic_dest G:\APP\ALEX
dispatchers (PROTOCOL=TCP) (SERVICE=novemberX
DB)
memory_target 427819008
open_cursors 300
processes 150
remote_login_passwordfile EXCLUSIVE
undo_tablespace UNDOTBS1
-------------------------------------------------------------
End of Report ( sp_1_2.lst ) |