Home » RDBMS Server » Performance Tuning » Become DB import too slow after server reboot (Oracle 10G, Solaris 10)
| Become DB import too slow after server reboot [message #514347] |
Mon, 04 July 2011 03:46  |
Tlg13team
Messages: 100
Registered: June 2008
Location: MGL
|
Senior Member |
|
|
Hi all,
Few days ago, My database server no access to StorageBox then I reboot it then after works fine. But, know DB import process is too slow.
Before 100GB DB import process completed within 10 hours when server normal running. Now 2 day working, but not complete 
How to investigate this issue?
Maybe I miss increase some parameters on the Server or Oracle?
Here is my server brief info:
RAM is 16GB,
SWAP size is 16GB,
CPU 12 cores
SQL> show sga;
Total System Global Area 4294967296 bytes
Fixed Size 1984144 bytes
Variable Size 369105264 bytes
Database Buffers 3909091328 bytes
Redo Buffers 14786560 bytes
|
|
|
|
|
|
|
|
| Re: Become DB import too slow after server reboot [message #514683 is a reply to message #514404] |
Tue, 05 July 2011 20:30  |
Tlg13team
Messages: 100
Registered: June 2008
Location: MGL
|
Senior Member |
|
|
Dears,
Here is the STATSPACK report:
How to investigate it? where is become bottleneck on my server?
STATSPACK report for
Database DB Id Instance Inst Num Startup Time Release RAC
~~~~~~~~ ----------- ------------ -------- --------------- ----------- ---
279891961 certst 1 03-╚■ы-11 14:31 10.2.0.1.0 NO
Host Name: certest0.cerilli Num CPUs: 8 Phys Memory (MB): 16,082
~~~~
Snapshot Snap Id Snap Time Sessions Curs/Sess Comment
~~~~~~~~ ---------- ------------------ -------- --------- -------------------
Begin Snap: 4 06-╚■ы-11 09:09:07 17 7.2
End Snap: 5 06-╚■ы-11 09:20:55 17 5.6
Elapsed: 11.80 (mins)
Cache Sizes Begin End
~~~~~~~~~~~ ---------- ----------
Buffer Cache: 3,728M Std Block Size: 8K
Shared Pool Size: 304M Log Buffer: 14,002K
Load Profile Per Second Per Transaction
~~~~~~~~~~~~ --------------- ---------------
Redo size: 16,152.50 11,435,972.00
Logical reads: 411.59 291,403.00
Block changes: 177.05 125,350.00
Physical reads: 95.17 67,382.00
Physical writes: 149.50 105,848.00
User calls: 1.24 881.00
Parses: 1.52 1,077.00
Hard parses: 0.19 133.00
Sorts: 0.54 379.00
Logons: 0.00 1.00
Executes: 3.12 2,211.00
Transactions: 0.00
% Blocks changed per Read: 43.02 Recursive Call %: 89.98
Rollback per transaction %: 0.00 Rows per Sort: 46.16
Instance Efficiency Percentages
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 100.00 Redo NoWait %: 100.00
Buffer Hit %: 76.88 In-memory Sort %: 100.00
Library Hit %: 93.31 Soft Parse %: 87.65
Execute to Parse %: 51.29 Latch Hit %: 99.97
Parse CPU to Parse Elapsd %: 52.94 % Non-Parse CPU: 98.06
Shared Pool Statistics Begin End
------ ------
Memory Usage %: 89.96 89.98
% SQL with executions>1: 53.64 51.48
% Memory for SQL w/exec>1: 85.07 89.95
Top 5 Timed Events Avg %Total
~~~~~~~~~~~~~~~~~~ wait Call
Event Waits Time (s) (ms) Time
----------------------------------------- ------------ ----------- ------ ------
dispatcher listen timer 34 1,992 58600 58.4
db file parallel write 345 689 1997 20.2
direct path write temp 1,342 326 243 9.6
db file scattered read 10,053 196 19 5.7
free buffer waits 4,599 82 18 2.4
-------------------------------------------------------------
Host CPU (CPUs: 8)
~~~~~~~~ Load Average
Begin End User System Idle WIO WCPU
------- ------- ------- ------- ------- ------- --------
0.15 0.13 0.57 1.20 98.23 0.00 0.02
Instance CPU
~~~~~~~~~~~~
% of total CPU for Instance: 0.57
% of busy CPU for Instance: 31.99
%DB time waiting for CPU - Resource Mgr:
Memory Statistics Begin End
~~~~~~~~~~~~~~~~~ ------------ ------------
Host Mem (MB): 16,082.4 16,082.4
SGA use (MB): 4,096.0 4,096.0
PGA use (MB): 172.2 172.1
% Host Mem used for SGA+PGA: 26.5 26.5
-------------------------------------------------------------
Time Model System Stats DB/Inst: CERTST/certst Snaps: 4-5
-> Ordered by % of DB time desc, Statistic name
Statistic Time (s) % of DB time
----------------------------------- -------------------- ------------
sql execute elapsed time 694.5 99.7
DB CPU 17.2 2.5
parse time elapsed 0.5 .1
hard parse elapsed time 0.5 .1
PL/SQL compilation elapsed time 0.0 .0
PL/SQL execution elapsed time 0.0 .0
connection management call elapsed 0.0 .0
hard parse (bind mismatch) elapsed 0.0 .0
hard parse (sharing criteria) elaps 0.0 .0
repeated bind elapsed time 0.0 .0
DB time 696.3
background elapsed time 739.1
background cpu time 14.9
-------------------------------------------------------------
Wait Events DB/Inst: CERTST/certst Snaps: 4-5
-> 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
%Time Total Wait wait Waits
Event Waits -outs Time (s) (ms) /txn
--------------------------------- ------------ ------ ---------- ------ --------
db file parallel write 345 0 689 1997 345.0
direct path write temp 1,342 0 326 243 1,342.0
db file scattered read 10,053 0 196 19 ########
free buffer waits 4,599 98 82 18 4,599.0
db file sequential read 4,673 0 78 17 4,673.0
log file parallel write 229 0 30 129 229.0
control file parallel write 325 0 14 43 325.0
log file sync 4 25 2 433 4.0
control file sequential read 732 0 0 0 732.0
Streams AQ: waiting for time mana 1 100 4,152 ###### 1.0
PX Idle Wait 690 101 1,362 1974 690.0
SQL*Net message from client 618 0 780 1262 618.0
Streams AQ: qmn slave idle wait 25 0 690 27589 25.0
Streams AQ: qmn coordinator idle 53 53 690 13013 53.0
virtual circuit status 23 100 674 29307 23.0
-------------------------------------------------------------
Background Wait Events DB/Inst: CERTST/certst Snaps: 4-5
-> %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
%Time Total Wait wait Waits
Event Waits -outs Time (s) (ms) /txn
--------------------------------- ------------ ------ ---------- ------ --------
db file parallel write 345 0 689 1997 345.0
log file parallel write 229 0 30 129 229.0
control file parallel write 327 0 14 43 327.0
control file sequential read 144 0 0 0 144.0
rdbms ipc message 2,013 100 4,796 2382 2,013.0
Streams AQ: waiting for time mana 1 100 4,152 ###### 1.0
smon timer 3 100 879 ###### 3.0
pmon timer 236 100 690 2926 236.0
Streams AQ: qmn slave idle wait 25 0 690 27589 25.0
Streams AQ: qmn coordinator idle 53 53 690 13013 53.0
-------------------------------------------------------------
Wait Event Histogram DB/Inst: CERTST/certst Snaps: 4-5
-> 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
-------------------------- ----- ----- ----- ----- ----- ----- ----- ----- -----
control file parallel writ 326 .3 26.4 73.3
control file sequential re 732 99.6 .1 .3
db file parallel write 345 100.0
db file scattered read 10K 28.2 .4 1.1 3.0 37.7 22.1 7.5 .1
db file sequential read 4677 28.6 .8 2.3 4.6 35.0 20.9 7.7 .0
direct path write temp 1342 50.3 48.7 1.0
dispatcher listen timer 34 100.0
free buffer waits 4599 .3 .2 .1 .4 21.1 77.9
log file parallel write 229 2.2 15.3 81.7 .9
log file sync 4 100.0
SQL*Net more data to clien 14 100.0
pmon timer 237 .4 99.6
PX Idle Wait 678 100.0
rdbms ipc message 2012 .1 .0 .0 34.9 64.8
smon timer 3 100.0
SQL*Net message from clien 618 70.4 10.4 12.0 3.9 .8 .5 .2 1.9
SQL*Net message to client 618 100.0
SQL*Net more data from cli 64 100.0
Streams AQ: qmn coordinato 53 47.2 52.8
Streams AQ: qmn slave idle 25 100.0
Streams AQ: waiting for ti 1 100.0
virtual circuit status 23 100.0
-------------------------------------------------------------
SQL ordered by CPU DB/Inst: CERTST/certst Snaps: 4-5
-> Resources reported for PL/SQL code includes the resources used by all SQL
statements called by the code.
-> Total DB CPU (s): 17
-> Captured SQL accounts for 16.0% 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
---------- ------------ ---------- ------ ---------- --------------- ----------
2.34 1 2.34 13.6 2.34 8,551 2522684317
Module: SQL*Plus
BEGIN statspack.snap; END;
-------------------------------------------------------------
SQL ordered by Gets DB/Inst: CERTST/certst Snaps: 4-5
-> Resources reported for PL/SQL code includes the resources used by all SQL
statements called by the code.
-> End Buffer Gets Threshold: 10000 Total Buffer Gets: 291,403
-> Captured SQL accounts for 12.5% 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
--------------- ------------ -------------- ------ -------- --------- ----------
25,198 1 25,198.0 8.6 0.11 0.11 1580262155
Module: SQL*Plus
with event_histogram as ( select /*+ inline ordered index(h) i
ndex(se) */ h.snap_id , se.event , sum(h.
wait_count) total_waits , sum(case when (h.wait_time_mill
i = 1) then (nvl(h.wait_count,0)) else 0 end)
to1 , sum(case when (h.wait_time_milli = 2)
8,551 1 8,551.0 2.9 2.34 2.34 2522684317
Module: SQL*Plus
BEGIN statspack.snap; END;
5,667 1 5,667.0 1.9 0.02 0.03 4061365063
Module: sqlplus@certest0.cer.momn (TNS V1-V3)
INSERT INTO STATS$SEG_STAT_OBJ ( TS# , OBJ# , DATAOBJ# , DBID ,
OWNER , OBJECT_NAME , SUBOBJECT_NAME , OBJECT_TYPE , TABLESPACE_
NAME ) SELECT /*+ ordered index(ss1) */ VS.TS# , VS.OBJ# , VS.DA
TAOBJ# , :B2 , VS.OWNER , VS.OBJECT_NAME , VS.SUBOBJECT_NAME , V
S.OBJECT_TYPE , VS.TABLESPACE_NAME FROM STATS$SEG_STAT SS1, V$SE
-------------------------------------------------------------
SQL ordered by Executions DB/Inst: CERTST/certst Snaps: 4-5
-> End Executions Threshold: 100 Total Executions: 2,211
-> Captured SQL accounts for 89.7% 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
------------ --------------- ---------------- ----------- ---------- ----------
529 127 0.2 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
ds = range_preds + decode(bitand(:flag,8),0,0,1),
402 402 1.0 0.00 0.00 260339297
insert into sys.col_usage$ values ( :objn, :coln, decode(bit
and(:flag,1),0,0,1), decode(bitand(:flag,2),0,0,1), decode(b
itand(:flag,4),0,0,1), decode(bitand(:flag,8),0,0,1), decode
(bitand(:flag,16),0,0,1), decode(bitand(:flag,32),0,0,1), :t
ime)
162 0 0.0 0.00 0.00 2554034351
lock table sys.col_usage$ in exclusive mode nowait
141 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
141 141 1.0 0.00 0.00 1693927332
select count(*) from sys.job$ where (next_date > sysdate) and (n
ext_date < (sysdate+5/86400))
134 134 1.0 0.00 0.00 1307714173
Module: SQL*Plus
BEGIN DBMS_OUTPUT.GET_LINES(:LINES, :NUMLINES); END;
115 115 1.0 0.00 0.00 1307778841
select condition from cdef$ where rowid=:1
56 6 0.1 0.00 0.00 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
56 0 0.0 0.00 0.00 2396279102
lock table sys.mon_mods$ in exclusive mode nowait
50 50 1.0 0.00 0.00 1048608874
insert into sys.mon_mods$ values (:1, :2, :3, :4, :5, :6, :7)
26 0 0.0 0.00 0.00 4274598960
select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt, tim
estamp#, sample_size, minimum, maximum, distcnt, lowval, hival,
density, col#, spare1, spare2, avgcln from hist_head$ where obj#
=:1 and intcol#=:2
SQL ordered by Executions DB/Inst: CERTST/certst Snaps: 4-5
-> End Executions Threshold: 100 Total Executions: 2,211
-> Captured SQL accounts for 89.7% 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
------------ --------------- ---------------- ----------- ---------- ----------
23 483 21.0 0.00 0.00 87488304
select size_for_estimate, size_factor * 100
f, estd_physical_read_time, e
std_physical_reads from v$db_cache_advice where id
= '3'
23 46 2.0 0.00 0.00 303149640
select java_pool_size_for_estimate s, java_pool_size_f
actor * 100 f, estd_lc_load_time l, 0 from
v$java_pool_advice
23 460 20.0 0.00 0.00 854839081
select streams_pool_size_for_estimate s, streams_pool_
size_factor * 100 f, estd_spill_time + estd_unspill_ti
me, 0 from v$streams_pool_advice
23 299 13.0 0.00 0.00 1301067080
select shared_pool_size_for_estimate s, shared_pool_siz
e_factor * 100 f, estd_lc_load_time l, 0 fr
om v$shared_pool_advice
-------------------------------------------------------------
SQL ordered by Parse Calls DB/Inst: CERTST/certst Snaps: 4-5
-> End Parse Calls Threshold: 1000 Total Parse Calls: 1,077
-> Captured SQL accounts for 89.9% of Total Parse Calls
-> SQL reported below exceeded 1.0% of Total Parse Calls
% Total Old
Parse Calls Executions Parses Hash Value
------------ ------------ -------- ----------
162 402 15.04 260339297
insert into sys.col_usage$ values ( :objn, :coln, decode(bit
and(:flag,1),0,0,1), decode(bitand(:flag,2),0,0,1), decode(b
itand(:flag,4),0,0,1), decode(bitand(:flag,8),0,0,1), decode
(bitand(:flag,16),0,0,1), decode(bitand(:flag,32),0,0,1), :t
ime)
162 162 15.04 2554034351
lock table sys.col_usage$ in exclusive mode nowait
162 529 15.04 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),
134 134 12.44 1307714173
Module: SQL*Plus
BEGIN DBMS_OUTPUT.GET_LINES(:LINES, :NUMLINES); END;
115 115 10.68 1307778841
select condition from cdef$ where rowid=:1
56 56 5.20 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
56 56 5.20 2396279102
lock table sys.mon_mods$ in exclusive mode nowait
50 50 4.64 1048608874
insert into sys.mon_mods$ values (:1, :2, :3, :4, :5, :6, :7)
-------------------------------------------------------------
Instance Activity Stats DB/Inst: CERTST/certst Snaps: 4-5
Statistic Total per Second per Trans
--------------------------------- ------------------ -------------- ------------
active txn count during cleanout 52 0.1 52.0
application wait time 0 0.0 0.0
background checkpoints completed 0 0.0 0.0
background checkpoints started 0 0.0 0.0
background timeouts 2,041 2.9 2,041.0
buffer is not pinned count 44,616 63.0 44,616.0
buffer is pinned count 233,824 330.3 233,824.0
bytes received via SQL*Net from c 323,838 457.4 323,838.0
bytes received via SQL*Net from d 0 0.0 0.0
bytes sent via SQL*Net to client 216,596 305.9 216,596.0
bytes sent via SQL*Net to dblink 0 0.0 0.0
Cached Commit SCN referenced 0 0.0 0.0
calls to get snapshot scn: kcmgss 2,321 3.3 2,321.0
calls to kcmgas 338 0.5 338.0
calls to kcmgcs 85 0.1 85.0
change write time 142 0.2 142.0
cleanout - number of ktugct calls 121,380 171.4 121,380.0
cleanouts and rollbacks - consist 5 0.0 5.0
cleanouts only - consistent read 121,325 171.4 121,325.0
cluster key scan block gets 4,638 6.6 4,638.0
cluster key scans 1,676 2.4 1,676.0
commit batch/immediate performed 0 0.0 0.0
commit batch/immediate requested 0 0.0 0.0
commit cleanout failures: block l 0 0.0 0.0
commit cleanout failures: callbac 5 0.0 5.0
commit cleanouts 600 0.9 600.0
commit cleanouts successfully com 595 0.8 595.0
commit immediate performed 0 0.0 0.0
commit immediate requested 0 0.0 0.0
Commit SCN cached 0 0.0 0.0
commit txn count during cleanout 121,348 171.4 121,348.0
concurrency wait time 0 0.0 0.0
consistent changes 5 0.0 5.0
consistent gets 287,838 406.6 287,838.0
consistent gets - examination 131,196 185.3 131,196.0
consistent gets direct 0 0.0 0.0
consistent gets from cache 287,838 406.6 287,838.0
CPU used by this session 465 0.7 465.0
CPU used when call started 318 0.5 318.0
CR blocks created 5 0.0 5.0
current blocks converted for CR 0 0.0 0.0
cursor authentications 15 0.0 15.0
data blocks consistent reads - un 5 0.0 5.0
db block changes 125,350 177.1 125,350.0
db block gets 3,565 5.0 3,565.0
db block gets direct 0 0.0 0.0
db block gets from cache 3,565 5.0 3,565.0
DB time 515 0.7 515.0
DBWR checkpoint buffers written 22,550 31.9 22,550.0
DBWR checkpoints 0 0.0 0.0
DBWR object drop buffers written 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 thread checkpoint buffers wr 0 0.0 0.0
DBWR transaction table writes 1 0.0 1.0
DBWR undo block writes 3 0.0 3.0
Instance Activity Stats DB/Inst: CERTST/certst Snaps: 4-5
Statistic Total per Second per Trans
--------------------------------- ------------------ -------------- ------------
deferred (CURRENT) block cleanout 396 0.6 396.0
dirty buffers inspected 67,380 95.2 67,380.0
enqueue conversions 188 0.3 188.0
enqueue releases 5,832 8.2 5,832.0
enqueue requests 5,832 8.2 5,832.0
enqueue timeouts 0 0.0 0.0
enqueue waits 0 0.0 0.0
execute count 2,211 3.1 2,211.0
free buffer inspected 76,767 108.4 76,767.0
free buffer requested 76,726 108.4 76,726.0
heap block compress 17 0.0 17.0
hot buffers moved to head of LRU 0 0.0 0.0
immediate (CR) block cleanout app 121,330 171.4 121,330.0
immediate (CURRENT) block cleanou 91 0.1 91.0
IMU commits 0 0.0 0.0
IMU contention 0 0.0 0.0
IMU CR rollbacks 0 0.0 0.0
IMU- failed to get a private stra 0 0.0 0.0
IMU Flushes 1 0.0 1.0
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 Redo allocation size 6,188 8.7 6,188.0
IMU undo allocation size 12,864 18.2 12,864.0
index fast full scans (full) 0 0.0 0.0
index fetch by key 9,139 12.9 9,139.0
index scans kdiixs1 2,230 3.2 2,230.0
leaf node splits 20 0.0 20.0
leaf node 90-10 splits 1 0.0 1.0
lob reads 0 0.0 0.0
lob writes 0 0.0 0.0
lob writes unaligned 0 0.0 0.0
logons cumulative 1 0.0 1.0
messages received 354 0.5 354.0
messages sent 354 0.5 354.0
no buffer to keep pinned count 0 0.0 0.0
no work - consistent read gets 34,045 48.1 34,045.0
opened cursors cumulative 1,054 1.5 1,054.0
parse count (failures) 0 0.0 0.0
parse count (hard) 133 0.2 133.0
parse count (total) 1,077 1.5 1,077.0
parse time cpu 9 0.0 9.0
parse time elapsed 17 0.0 17.0
physical read bytes 551,993,344 779,651.6 ############
physical read IO requests 14,730 20.8 14,730.0
physical read total bytes 563,986,432 796,591.0 ############
physical read total IO requests 15,462 21.8 15,462.0
physical read total multi block r 10,053 14.2 10,053.0
physical reads 67,382 95.2 67,382.0
physical reads cache 67,382 95.2 67,382.0
physical reads cache prefetch 52,652 74.4 52,652.0
physical reads direct 0 0.0 0.0
physical reads direct temporary t 0 0.0 0.0
physical reads prefetch warmup 0 0.0 0.0
physical write bytes 867,106,816 1,224,727.1 ############
physical write IO requests 87,485 123.6 87,485.0
Instance Activity Stats DB/Inst: CERTST/certst Snaps: 4-5
Statistic Total per Second per Trans
--------------------------------- ------------------ -------------- ------------
physical write total bytes 906,968,064 1,281,028.3 ############
physical write total IO requests 88,952 125.6 88,952.0
physical write total multi block 2,256 3.2 2,256.0
physical writes 105,848 149.5 105,848.0
physical writes direct 17,280 24.4 17,280.0
physical writes direct (lob) 0 0.0 0.0
physical writes direct temporary 17,280 24.4 17,280.0
physical writes from cache 88,568 125.1 88,568.0
physical writes non checkpoint 82,734 116.9 82,734.0
pinned buffers inspected 0 0.0 0.0
prefetch clients - default 0 0.0 0.0
prefetch warmup blocks aged out b 0 0.0 0.0
prefetched blocks aged out before 0 0.0 0.0
process last non-idle time 753 1.1 753.0
PX local messages recv'd 0 0.0 0.0
PX local messages sent 0 0.0 0.0
recovery blocks read 0 0.0 0.0
recursive calls 7,908 11.2 7,908.0
recursive cpu usage 274 0.4 274.0
redo blocks read for recovery 0 0.0 0.0
redo blocks written 23,231 32.8 23,231.0
redo buffer allocation retries 0 0.0 0.0
redo entries 123,760 174.8 123,760.0
redo log space requests 0 0.0 0.0
redo log space wait time 0 0.0 0.0
redo ordering marks 68 0.1 68.0
redo size 11,435,972 16,152.5 ############
redo synch time 177 0.3 177.0
redo synch writes 156 0.2 156.0
redo wastage 59,320 83.8 59,320.0
redo write time 2,940 4.2 2,940.0
redo writes 229 0.3 229.0
rollback changes - undo records a 0 0.0 0.0
rollbacks only - consistent read 0 0.0 0.0
rows fetched via callback 4,306 6.1 4,306.0
session connect time 0 0.0 0.0
session cursor cache hits 629 0.9 629.0
session logical reads 291,403 411.6 291,403.0
session pga memory 851,968 1,203.3 851,968.0
session uga memory 53,992 76.3 53,992.0
session uga memory max 2,992,736 4,227.0 2,992,736.0
shared hash latch upgrades - no w 587 0.8 587.0
SMON posted for dropping temp seg 0 0.0 0.0
SMON posted for undo segment shri 0 0.0 0.0
sorts (disk) 0 0.0 0.0
sorts (memory) 379 0.5 379.0
sorts (rows) 17,494 24.7 17,494.0
sql area purged 2 0.0 2.0
SQL*Net roundtrips to/from client 617 0.9 617.0
SQL*Net roundtrips to/from dblink 0 0.0 0.0
summed dirty queue length 11,347,727 16,027.9 ############
switch current to new buffer 0 0.0 0.0
table fetch by rowid 131,859 186.2 131,859.0
table fetch continued row 0 0.0 0.0
table scan blocks gotten 121,544 171.7 121,544.0
table scan rows gotten 6,918,949 9,772.5 6,918,949.0
Instance Activity Stats DB/Inst: CERTST/certst Snaps: 4-5
Statistic Total per Second per Trans
--------------------------------- ------------------ -------------- ------------
table scans (long tables) 0 0.0 0.0
table scans (short tables) 154 0.2 154.0
total number of times SMON posted 0 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 422,892 597.3 422,892.0
user calls 881 1.2 881.0
user commits 1 0.0 1.0
user I/O wait time 59,987 84.7 59,987.0
user rollbacks 0 0.0 0.0
workarea executions - onepass 0 0.0 0.0
workarea executions - optimal 185 0.3 185.0
write clones created in backgroun 0 0.0 0.0
write clones created in foregroun 1 0.0 1.0
-------------------------------------------------------------
Instance Activity Stats DB/Inst: CERTST/certst Snaps: 4-5
-> Statistics with absolute values (should not be diffed)
Statistic Begin Value End Value
--------------------------------- --------------- ---------------
logons current 17 17
opened cursors current 123 95
session cursor cache count 3,746 3,766
workarea memory allocated 105,320 104,576
-------------------------------------------------------------
Instance Activity Stats DB/Inst: CERTST/certst Snaps: 4-5
-> Statistics identified by '(derived)' come from sources other than SYSSTAT
Statistic Total per Hour
--------------------------------- ------------------ ---------
log switches (derived) 0 .00
-------------------------------------------------------------
OS Statistics DB/Inst: CERTST/certst Snaps: 4-5
-> ordered by statistic type (CPU use, Virtual Memory, Hardware Config), Name
Statistic Total
------------------------- ----------------------
BUSY_TIME 10,026
IDLE_TIME 557,075
SYS_TIME 6,796
USER_TIME 3,230
OS_CPU_WAIT_TIME 100
VM_IN_BYTES 4,177,920
PHYSICAL_MEMORY_BYTES 16,863,617,024
NUM_CPUS 8
-------------------------------------------------------------
Tablespace IO Stats DB/Inst: CERTST/certst Snaps: 4-5
->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)
-------------- ------- ------ ------- ------------ -------- ---------- ------
DATA01
14,713 21 18.6 4.6 86,549 122 0 0.0
TEMP
0 0 0.0 675 1 0 0.0
SYSTEM
8 0 15.0 1.0 5 0 0 0.0
UNDOTBS
0 0 0.0 4 0 0 0.0
PERFSTAT
1 0 30.0 1.0 0 0 0 0.0
-------------------------------------------------------------
File IO Stats DB/Inst: CERTST/certst Snaps: 4-5
->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)
-------------- ------- ----- --- ------- ------------ -------- ---------- ------
DATA01 /certest1/oradata/certst/DATA01A.dbf
5,021 7 11.5 ### 4.6 28,729 41 0
/certest1/oradata/certst/DATA01A1.dbf
4,848 7 21.0 ### 4.8 31,905 45 0
/certest1/oradata/certst/DATA01.dbf
4,844 7 23.4 ### 4.3 25,915 37 0
PERFSTAT /certest1/oradata/certst/perfstat.dbf
1 0 30.0 32 1.0 0 0 0
SYSTEM /certest1/oradata/certst/system01.dbf
8 0 15.0 32 1.0 5 0 0
TEMP /certest1/oradata/certst/temp01.dbf
0 0 675 1 0
UNDOTBS /certest1/oradata/certst/undotbs01.dbf
0 0 4 0 0
-------------------------------------------------------------
File Read Histogram Stats DB/Inst: CERTST/certst Snaps: 4-5
->Number of single block reads in each time range
->ordered by Tablespace, File
Tablespace Filename
------------------------ ----------------------------------------------------
0 - 2 ms 2 - 4 ms 4 - 8 ms 8 - 16 ms 16 - 32 ms 32+ ms
------------ ------------ ------------ ------------ ------------ ------------
DATA01 /certest1/oradata/certst/DATA01.dbf
249 30 84 676 483 186
/certest1/oradata/certst/DATA01A.dbf
750 25 47 505 168 57
/certest1/oradata/certst/DATA01A1.dbf
378 51 79 450 322 120
PERFSTAT /certest1/oradata/certst/perfstat.dbf
0 0 0 0 1 0
SYSTEM /certest1/oradata/certst/system01.dbf
0 0 3 3 2 0
-------------------------------------------------------------
Buffer Pool Statistics DB/Inst: CERTST/certst Snaps: 4-5
-> 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 462K 77 291,431 67,378 88,568 4,599 0 0
-------------------------------------------------------------
Instance Recovery Stats DB/Inst: CERTST/certst Snaps: 4-5
-> 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 300 238 115240 55031 70157 2764800 70157
E 300 297 148115 68832 72682 2764800 72682
-------------------------------------------------------------
Buffer Pool Advisory DB/Inst: CERTST/certst End Snap: 5
-> 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 368 .1 46 1.1 17,183 26,771 8.3
D 736 .2 91 1.1 17,153 26,574 8.2
D 1,104 .3 137 1.1 17,141 26,494 8.2
D 1,472 .4 183 1.1 17,133 26,441 8.2
D 1,840 .5 228 1.1 17,041 25,843 8.0
D 2,208 .6 274 1.1 16,949 25,239 7.8
D 2,576 .7 319 1.1 16,945 25,212 7.8
D 2,944 .8 365 1.0 16,926 25,091 7.7
D 3,312 .9 411 1.0 16,704 23,642 7.3
D 3,680 1.0 456 1.0 16,158 20,079 6.2
D 3,728 1.0 462 1.0 16,136 19,935 6.1
D 4,048 1.1 502 1.0 15,989 18,972 5.8
D 4,416 1.2 548 1.0 15,860 18,132 5.6
D 4,784 1.3 593 1.0 15,718 17,204 5.3
D 5,152 1.4 639 1.0 15,570 16,505 5.1
D 5,520 1.5 684 1.0 15,419 16,505 5.1
D 5,888 1.6 730 0.9 15,109 16,505 5.1
D 6,256 1.7 776 0.9 14,841 16,505 5.1
D 6,624 1.8 821 0.9 14,691 16,505 5.1
D 6,992 1.9 867 0.9 14,580 16,505 5.1
D 7,360 2.0 913 0.9 14,476 16,505 5.1
-------------------------------------------------------------
PGA Aggr Target Stats DB/Inst: CERTST/certst Snaps: 4-5
-> 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 21 0
%PGA %Auto %Man
PGA Aggr Auto PGA PGA Mem W/A PGA W/A W/A W/A Global Mem
Target(M) Target(M) Alloc(M) Used(M) Mem Mem Mem Bound(K)
- --------- --------- ---------- ---------- ------ ------ ------ ----------
B 1,024 893 172.2 102.5 59.6 100.0 .0 104,850
E 1,024 893 172.1 101.8 59.2 100.0 .0 104,850
-------------------------------------------------------------
PGA Aggr Target Histogram DB/Inst: CERTST/certst Snaps: 4-5
-> Optimal Executions are purely in-memory operations
Low High
Optimal Optimal Total Execs Optimal Execs 1-Pass Execs M-Pass Execs
------- ------- -------------- ------------- ------------ ------------
2K 4K 153 153 0 0
64K 128K 3 3 0 0
128K 256K 2 2 0 0
512K 1024K 27 27 0 0
-------------------------------------------------------------
PGA Memory Advisory DB/Inst: CERTST/certst End Snap: 5
-> 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
---------- ------- ---------------- ---------------- -------- ----------
128 0.1 41,034.4 73,782.9 36.0 5
256 0.3 41,034.4 18,353.7 69.0 0
512 0.5 41,034.4 18,198.1 69.0 0
768 0.8 41,034.4 18,198.1 69.0 0
1,024 1.0 41,034.4 18,198.1 69.0 0
1,229 1.2 41,034.4 18,198.1 69.0 0
1,434 1.4 41,034.4 18,198.1 69.0 0
1,638 1.6 41,034.4 18,198.1 69.0 0
1,843 1.8 41,034.4 18,198.1 69.0 0
2,048 2.0 41,034.4 18,198.1 69.0 0
3,072 3.0 41,034.4 18,198.1 69.0 0
4,096 4.0 41,034.4 18,198.1 69.0 0
6,144 6.0 41,034.4 18,198.1 69.0 0
8,192 8.0 41,034.4 18,198.1 69.0 0
-------------------------------------------------------------
Process Memory Summary Stats DB/Inst: CERTST/certst Snaps: 4-5
-> 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 -------- 172.2 134.1 18.7 7.5 22.4 108 108 23
SQL 102.7 102.3 9.3 30.8 102 103 11
Other 50.5 2.2 4.6 22 23 23
Freeable 18.7 .0 1.6 1.8 7 12
PL/SQL .2 .1 .0 .0 0 0 17
E -------- 172.1 134.3 17.7 7.5 22.4 108 108 23
SQL 102.7 102.3 9.3 30.8 102 103 11
Other 51.4 2.2 4.6 22 23 23
Freeable 17.7 .0 1.5 1.8 7 12
PL/SQL .2 .1 .0 .0 0 0 17
-------------------------------------------------------------
Top Process Memory (by component) DB/Inst: CERTST/certst Snaps: 4-5
-> ordered by Begin/End snapshot, Alloc (MB) desc
Alloc Used Freeabl Max Hist Max
PId Category (MB) (MB) (MB) Alloc (MB) Alloc (MB)
- ------ ------------- ------- ------- -------- ---------- ----------
B 20 ------------ 107.7 106.3 1.0 107.7 107.7
SQL 102.3 102.1 102.3 103.3
Other 4.3 4.3 4.3
Freeable 1.0 .0 1.0
PL/SQL .1 .0 .1 .3
6 LGWR -------- 23.0 10.5 .7 23.0 23.7
Other 22.3 22.3 23.0
Freeable .7 .0 .7
PL/SQL .0 .0 .0 .0
5 DBW0 -------- 11.1 1.2 7.0 11.1 13.4
Freeable 7.0 .0 7.0
Other 4.1 4.1 6.4
PL/SQL .0 .0 .0 .0
19 ------------ 7.5 6.0 1.0 7.5 12.9
Other 6.3 6.3 6.3
Freeable 1.0 .0 1.0
SQL .1 .0 .1 5.7
PL/SQL .0 .0 .0 .0
21 TNS V1-V3 --- 3.6 .9 1.8 3.6 3.6
Freeable 1.8 .0 1.8
Other 1.6 1.6 1.6
SQL .1 .1 .1 2.2
PL/SQL .0 .0 .0 .0
11 MMON -------- 3.3 1.3 1.4 3.3 3.3
Other 1.7 1.7 1.7
Freeable 1.4 .0 1.4
SQL .1 .0 .1 1.4
PL/SQL .0 .0 .0 .0
8 SMON -------- 2.3 .6 1.4 2.3 2.4
Freeable 1.4 .0 1.4
Other .8 .8 .8
SQL .0 .0 .0 .6
PL/SQL .0 .0 .0 .0
7 CKPT -------- 2.2 .4 1.4 2.2 3.5
Freeable 1.4 .0 1.4
Other .8 .8 2.1
SQL .0 .0 .0 .1
PL/SQL .0 .0 .0 .0
10 CJQ0 -------- 1.9 .7 1.0 1.9 1.9
Freeable 1.0 .0 1.0
Other .9 .9 .9
SQL .1 .0 .1 .6
PL/SQL .0 .0 .0 .0
17 P000 -------- 1.2 .3 .8 1.2 5.4
Freeable .8 .0 .8
Other .3 .3 4.6
PL/SQL .0 .0 .0 .0
18 P001 -------- 1.2 .3 .8 1.2 5.4
Freeable .8 .0 .8
Other .3 .3 4.6
PL/SQL .0 .0 .0 .0
E 20 ------------ 107.7 106.3 1.0 107.7 107.7
SQL 102.3 102.1 102.3 103.3
Top Process Memory (by component) DB/Inst: CERTST/certst Snaps: 4-5
-> ordered by Begin/End snapshot, Alloc (MB) desc
Alloc Used Freeabl Max Hist Max
PId Category (MB) (MB) (MB) Alloc (MB) Alloc (MB)
- ------ ------------- ------- ------- -------- ---------- ----------
E 20 Other 4.3 4.3 4.3
Freeable 1.0 .0 1.0
PL/SQL .1 .0 .1 .3
6 LGWR -------- 23.0 10.5 .7 23.0 23.7
Other 22.3 22.3 23.0
Freeable .7 .0 .7
PL/SQL .0 .0 .0 .0
5 DBW0 -------- 11.1 1.2 7.0 11.1 13.4
Freeable 7.0 .0 7.0
Other 4.1 4.1 6.4
PL/SQL .0 .0 .0 .0
19 ------------ 7.5 6.0 1.0 7.5 12.9
Other 6.3 6.3 6.3
Freeable 1.0 .0 1.0
SQL .1 .0 .1 5.7
PL/SQL .0 .0 .0 .0
21 TNS V1-V3 --- 3.4 1.1 1.7 3.4 3.4
Freeable 1.7 .0 1.7
Other 1.6 1.6 1.6
SQL .1 .1 .1 2.1
PL/SQL .0 .0 .0 .0
11 MMON -------- 3.3 1.3 1.4 3.3 3.3
Other 1.7 1.7 1.7
Freeable 1.4 .0 1.4
SQL .1 .0 .1 1.4
PL/SQL .0 .0 .0 .0
8 SMON -------- 2.4 .6 .6 2.4 2.4
Other 1.8 1.8 1.8
Freeable .6 .0 .6
SQL .0 .0 .0 .6
PL/SQL .0 .0 .0 .0
7 CKPT -------- 2.2 .4 1.4 2.2 3.5
Freeable 1.4 .0 1.4
Other .8 .8 2.1
SQL .0 .0 .0 .1
PL/SQL .0 .0 .0 .0
10 CJQ0 -------- 1.9 .7 1.0 1.9 1.9
Freeable 1.0 .0 1.0
Other .9 .9 .9
SQL .1 .0 .1 .6
PL/SQL .0 .0 .0 .0
17 P000 -------- 1.2 .3 .8 1.2 5.4
Freeable .8 .0 .8
Other .3 .3 4.6
PL/SQL .0 .0 .0 .0
18 P001 -------- 1.2 .3 .8 1.2 5.4
Freeable .8 .0 .8
Other .3 .3 4.6
PL/SQL .0 .0 .0 .0
-------------------------------------------------------------
Undo Segment Summary DB/Inst: CERTST/certst Snaps: 4-5
-> 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, your - 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
---- ---------- --------------- -------- ---------- --------- ----- -----------
1 .5 137 0 4 180/180 0/0 0/0/0/0/0/0
-------------------------------------------------------------
Undo Segment Stats DB/Inst: CERTST/certst Snaps: 4-5
-> 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
------------ ----------- ------------ ------- ------- ------- ----- -----------
06-╚■ы 09:11 533 137 0 4 180 0/0 0/0/0/0/0/0
-------------------------------------------------------------
Latch Activity DB/Inst: CERTST/certst Snaps: 4-5
->"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
------------------------ -------------- ------ ------ ------ ------------ ------
active checkpoint queue 345 0.0 0 0
active service list 1,277 0.0 0 236 0.0
archive control 3 0.0 0 0
cache buffer handles 72 0.0 0 0
cache buffers chains 1,048,388 0.0 0.0 0 165,414 0.0
cache buffers lru chain 267,098 0.2 0.0 0 165,061 0.1
cache table scan latch 0 0 10,053 0.0
channel handle pool latc 2 0.0 0 0
channel operations paren 3,270 0.0 0 0
checkpoint queue latch 108,516 0.0 0.0 0 121,443 0.0
client/application info 10 0.0 0 0
compile environment latc 33 0.0 0 0
Consistent RBA 229 0.0 0 0
dml lock allocation 578 0.0 0 0
dummy allocation 2 0.0 0 0
enqueue hash chains 11,848 0.0 0 35 0.0
enqueues 10,619 0.0 0 0
event group latch 1 0.0 0 0
file cache latch 15 0.0 0 0
FOB s.o list latch 13 0.0 0 0
global KZLD latch for me 1 0.0 0 0
hash table column usage 202 0.0 0 7,652 0.0
In memory undo latch 311 0.0 0 153 0.0
internal temp table obje 7 0.0 0 0
job_queue_processes para 12 0.0 0 0
JS queue state obj latch 4,230 0.0 0 0
kks stats 262 0.0 0 0
KMG MMAN ready and start 236 0.0 0 0
ksuosstats global area 49 0.0 0 0
KTF sga latch 2 0.0 0 225 0.0
ktm global data 3 0.0 0 0
kwqbsn:qsga 28 0.0 0 0
KWQMN job cache list lat 1 0.0 0 0
KWQP Prop Status 1 0.0 0 0
lgwr LWN SCN 231 0.0 0 0
library cache 14,170 0.0 0 333 0.0
library cache load lock 30 0.0 0 0
library cache lock 3,679 0.0 0 0
library cache lock alloc 42 0.0 0 0
library cache pin 8,104 0.0 0 0
library cache pin alloca 14 0.0 0 0
list of block allocation 15 0.0 0 0
longop free list parent 236 0.0 0 0
Memory Management Latch 0 0 236 0.0
messages 4,730 0.0 0.0 0 0
mostly latch-free SCN 231 0.0 0 0
MQL Tracking Latch 0 0 13 0.0
multiblock read objects 29,412 0.0 0 0
ncodef allocation latch 12 0.0 0 0
object queue header heap 13 0.0 0 0
Latch Activity DB/Inst: CERTST/certst Snaps: 4-5
->"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
------------------------ -------------- ------ ------ ------ ------------ ------
object queue header oper 434,970 0.0 0.0 0 0
object stats modificatio 268 0.0 0 0
OS process 3 0.0 0 0
OS process allocation 237 0.0 0 0
OS process: request allo 2 0.0 0 0
parallel query alloc buf 96 0.0 0 0
parameter table allocati 1 0.0 0 0
PL/SQL warning settings 132 0.0 0 0
post/wait queue 9,440 0.0 0 4 0.0
process allocation 2 0.0 0 1 0.0
process group creation 2 0.0 0 0
qmn task queue latch 100 0.0 0 0
redo allocation 1,012 0.0 0 123,733 0.0
redo copy 0 0 123,735 0.0
redo writing 1,532 0.0 0 0
resmgr group change latc 2 0.0 0 0
resmgr:actses active lis 3 0.0 0 0
resmgr:actses change gro 1 0.0 0 0
resmgr:free threads list 2 0.0 0 0
resmgr:schema config 1 0.0 0 0
row cache objects 18,466 0.0 0 40 0.0
rules engine rule set st 100 0.0 0 0
sequence cache 6 0.0 0 0
session allocation 521 0.0 0 0
session idle bit 1,794 0.0 0 0
session state list latch 8 0.0 0 0
session switching 12 0.0 0 0
session timer 236 0.0 0 0
Shared B-Tree 28 0.0 0 0
shared pool 8,743 0.0 0 0
sim partition latch 0 0 35 0.0
simulator hash latch 123,016 0.0 0 0
simulator lru latch 101,498 0.0 0.0 0 21,506 0.0
sort extent pool 156 0.0 0 0
SQL memory manager latch 1 0.0 0 231 0.0
SQL memory manager worka 15,648 0.0 0 0
threshold alerts latch 35 0.0 0 0
transaction allocation 5 0.0 0 0
transaction branch alloc 12 0.0 0 0
undo global data 122,575 0.0 0 0
user lock 4 0.0 0 0
-------------------------------------------------------------
Latch Miss Sources DB/Inst: CERTST/certst Snaps: 4-5
-> only latches with sleeps are shown
-> ordered by name, sleeps desc
NoWait Waiter
Latch Name Where Misses Sleeps Sleeps
------------------------ -------------------------- ------- ---------- --------
cache buffers chains kcbzib: multi-block read: 0 3 0
-------------------------------------------------------------
Segments by Logical Reads DB/Inst: CERTST/certst Snaps: 4-5
-> End Segment Logical Reads Threshold: 10000
-> Pct Total shows % of logical reads for each top segment compared with total
logical reads for all segments captured by the Snapshot
Subobject Obj. Logical Pct
Owner Tablespace Object Name Name Type Reads Total
---------- ---------- -------------------- ------------ ----- ------------ -----
PERFSTAT PERFSTAT STATS$SYSTEM_EVENT TABLE 25,632 86.5
SYS SYSTEM I_COL_USAGE$ INDEX 1,792 6.1
SYS SYSTEM SMON_SCN_TIME TABLE 1,296 4.4
SYS SYSTEM COL_USAGE$ TABLE 592 2.0
SYS SYSTEM I_MON_MODS$_OBJ INDEX 256 .9
-------------------------------------------------------------
Segments by Physical Reads DB/Inst: CERTST/certst Snaps: 4-5
-> End Segment Physical Reads Threshold: 1000
Subobject Obj. Physical Pct
Owner Tablespace Object Name Name Type Reads Total
---------- ---------- -------------------- ------------ ----- ------------ -----
PERFSTAT PERFSTAT STATS$SYSTEM_EVENT TABLE 1,134 100.0
-------------------------------------------------------------
Dictionary Cache Stats DB/Inst: CERTST/certst Snaps: 4-5
->"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 12 0.0 0 0 1
dc_global_oids 356 0.0 0 0 56
dc_histogram_defs 2,144 1.2 0 21 4,963
dc_object_grants 12 0.0 0 0 153
dc_object_ids 1,353 0.1 0 2 1,555
dc_objects 304 2.3 0 4 2,193
dc_profiles 1 0.0 0 0 1
dc_rollback_segments 145 0.0 0 0 22
dc_segments 497 0.0 0 0 1,050
dc_tablespaces 339 0.0 0 0 16
dc_usernames 5 0.0 0 0 40
dc_users 968 0.0 0 0 44
outstanding_alerts 11 0.0 0 0 10
-------------------------------------------------------------
Library Cache Activity DB/Inst: CERTST/certst Snaps: 4-5
->"Pct Misses" should be very low
Get Pct Pin Pct Invali-
Namespace Requests Miss Requests Miss Reloads dations
--------------- ------------ ------ -------------- ------ ---------- --------
BODY 7 0.0 127 0.0 0 0
SQL AREA 116 100.0 2,973 8.8 2 0
TABLE/PROCEDURE 123 6.5 1,113 1.7 3 0
-------------------------------------------------------------
Rule Sets DB/Inst: CERTST/certst Snaps: 4-5
-> * 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
-------------------------------------------------------------
Streams Pool Advisory DB/Inst: CERTST/certst End Snap: 5
Streams Pool Streams Pool Est Spill Est Spill Est Unspill Est Unspill
Size (M) Size Factor Count Time (s) Count Time (s)
------------ ------------ --------- --------- ----------- -----------
.0 1.0 0 0 0 0
.0 2.0 0 0 0 0
.0 3.0 0 0 0 0
.0 4.0 0 0 0 0
.0 5.0 0 0 0 0
.0 6.0 0 0 0 0
.0 7.0 0 0 0 0
.0 8.0 0 0 0 0
.0 9.0 0 0 0 0
.0 10.0 0 0 0 0
.0 11.0 0 0 0 0
.0 12.0 0 0 0 0
.0 13.0 0 0 0 0
.0 14.0 0 0 0 0
.0 15.0 0 0 0 0
.0 16.0 0 0 0 0
.0 17.0 0 0 0 0
.0 18.0 0 0 0 0
.0 19.0 0 0 0 0
.0 20.0 0 0 0 0
-------------------------------------------------------------
Shared Pool Advisory DB/Inst: CERTST/certst End Snap: 5
-> 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
---------- ----- -------- ------------ ------- ------ ------- ------ -----------
240 .8 35 4,007 706 1.0 309 1.0 504,007
272 .9 66 6,091 711 1.0 304 1.0 508,569
304 1.0 97 8,216 714 1.0 301 1.0 511,495
336 1.1 128 10,768 716 1.0 299 1.0 513,539
368 1.2 159 13,217 718 1.0 297 1.0 515,410
400 1.3 190 14,861 719 1.0 296 1.0 516,699
432 1.4 221 16,571 719 1.0 296 1.0 517,219
464 1.5 252 18,172 719 1.0 296 1.0 517,568
496 1.6 283 19,524 719 1.0 296 1.0 517,821
528 1.7 309 20,951 719 1.0 296 1.0 517,974
560 1.8 323 21,996 719 1.0 296 1.0 518,086
592 1.9 323 21,996 719 1.0 296 1.0 518,151
624 2.1 323 21,996 719 1.0 296 1.0 518,198
-------------------------------------------------------------
SGA Target Advisory DB/Inst: CERTST/certst End Snap: 5
SGA Target SGA Size Est DB Est DB Est Physical
Size (M) Factor Time (s) Time Factor Reads
---------- -------- -------- ----------- --------------
1,024 .3 331,060 1.0 17,152,807
2,048 .5 330,931 1.0 17,133,444
3,072 .8 329,698 1.0 16,944,650
4,096 1.0 324,410 1.0 16,136,225
5,120 1.3 321,685 1.0 15,718,297
6,144 1.5 320,971 1.0 15,419,777
7,168 1.8 320,971 1.0 14,690,419
8,192 2.0 320,971 1.0 14,475,807
-------------------------------------------------------------
SGA Memory Summary DB/Inst: CERTST/certst Snaps: 4-5
End Size (Bytes)
SGA regions Begin Size (Bytes) (if different)
------------------------------ -------------------- --------------------
Database Buffers 3,909,091,328
Fixed Size 1,984,144
Redo Buffers 14,786,560
Variable Size 369,105,264
-------------------- --------------------
sum 4,294,967,296
-------------------------------------------------------------
SGA breakdown difference DB/Inst: CERTST/certst Snaps: 4-5
-> 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 16.0 16.0 0.00
large free memory 15.8 15.8 0.00
large PX msg pool .2 .2 0.00
shared ASH buffers 15.5 15.5 0.00
shared CCursor 10.2 10.5 2.97
shared db_block_hash_buckets 16.8 16.8 0.00
shared enqueue 3.9 3.9 0.00
shared event statistics per sess 19.2 19.2 0.00
shared FileOpenBlock 3.2 3.2 0.00
shared free memory 30.5 30.5 -0.20
shared Heap0: KGL 4.3 4.5 2.71
shared KCB Table Scan Buffer 4.0 4.0 0.00
shared KGLS heap 5.4 5.0 -7.01
shared kglsim hash table bkts 4.0 4.0 0.00
shared KQR M PO 4.2 4.1 -1.85
shared KQR M SO 4.1 4.1 -1.13
shared KSFD SGA I/O b 4.0 4.0 0.00
shared KTI-UNDO 16.3 16.3 0.00
shared library cache 13.2 13.5 2.12
shared PCursor 6.7 6.7 1.14
shared private strands 30.5 30.5 0.00
shared procs: ksunfy 3.9 3.9 0.00
shared row cache 7.1 7.1 0.00
shared sessions 10.9 10.9 0.00
shared sql area 28.4 28.0 -1.10
shared transaction 5.1 5.1 0.00
shared VIRTUAL CIRCUITS 4.8 4.8 0.00
stream free memory 16.0 16.0 0.00
buffer_cache 3,728.0 3,728.0 0.00
fixed_sga 1.9 1.9 0.00
log_buffer 14.1 14.1 0.00
-------------------------------------------------------------
SQL Memory Statistics DB/Inst: CERTST/certst Snaps: 4-5
Begin End % Diff
-------------- -------------- --------------
Avg Cursor Size (KB): 46.79 42.12 -11.10
Cursor to Parent ratio: 1.32 1.32 -.56
Total Cursors: 2,218 2,265 2.08
Total Parents: 1,674 1,719 2.62
-------------------------------------------------------------
init.ora Parameters DB/Inst: CERTST/certst Snaps: 4-5
End value
Parameter Name Begin value (if different)
----------------------------- --------------------------------- --------------
aq_tm_processes 1
background_dump_dest /opt/u01/app/oracle/admin/certst/
compatible 10.2.0.0.0
control_files /certest1/oradata/certst/control0
core_dump_dest /opt/u01/app/oracle/admin/certst/
db_block_size 8192
db_domain certest0
db_file_multiblock_read_count 8
db_name certst
dispatchers (address= (partial=true) (protoco
fast_start_mttr_target 300
global_names TRUE
instance_name certst
job_queue_processes 10
local_listener listener_certst
log_archive_dest /backups_home/arch/certst
log_archive_format %t_%s_%r.arc
open_cursors 600
parallel_max_servers 10
parallel_min_servers 2
pga_aggregate_target 1073741824
processes 2000
query_rewrite_enabled FALSE
rollback_segments rbs01, rbs02, rbs03, rbs04
sga_target 4294967296
star_transformation_enabled FALSE
timed_statistics TRUE
undo_management AUTO
undo_retention 10800
user_dump_dest /opt/u01/app/oracle/admin/certst/
-------------------------------------------------------------
End of Report ( buyan_sp_4_5.lst )
|
|
|
|
|
|
|
|
|
|
| Re: Become DB import too slow after server reboot [message #514690 is a reply to message #514689] |
Tue, 05 July 2011 21:12 |
Tlg13team
Messages: 100
Registered: June 2008
Location: MGL
|
Senior Member |
|
|
Right know, I looking import process session via TOAD.
Current statment tab:
CREATE INDEX "I_AR_TRANS_GROUP_UID" ON "ACCOUNTS_RECEIVABLE" ("TRANS_GROUP_UID" ) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 3041918976 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "DATA01" LOGGING
Long Ops tab:
step1: Percent 100% | Table Scan: SUPER.ACCOUNTS_RECEIVABLE: 2505087 out of 2505087 Blocks done
step2: Percent 4.28% | Sort Output: : 15315 out of 358128 Blocks done
Waits tab:
direct path write:WAITED SHORT TIME
[i]Total waits[/i]
ROWNUM EVENT TOTAL_WAITS TOTAL_TIMEOUTS TIME_WAITED AVERAGE_WAIT MAX_WAIT TIME_WAITED_MICRO
1 latch: cache buffers chains 25 0 0 0 0 786
[b]
2 free buffer waits 1514855 1495943 2925535 1.93 99 29255349582
[/b]
3 log buffer space 18828 15727 1703528 90.48 99 17035282023
4 log file switch (checkpoint incomplete) 1411 952 136353 96.64 99 1363528054
5 log file switch completion 319 201 25309 79.34 99 253086538
6 log file sync 362 27 4720 13.04 99 47198523
7 db file sequential read 120108 0 57470 0.48 297 574698768
8 db file scattered read 334252 0 235977 0.71 410 2359771848
9 db file parallel read 7 0 108 15.49 25 1084373
10 direct path read temp 1476 0 750 0.51 15 7502737
11 direct path write 46881 0 1 0 0 6733
12 direct path write temp 27817 0 662935 23.83 369 6629349834
13 latch: library cache 3 0 0 0.01 0 187
14 SQL*Net message to client 78904 0 6 0 0 62787
15 SQL*Net message from client 78904 0 62779 0.8 31 627785477
16 SQL*Net more data from client 9574072 0 7443 0 1 74430103
17 events in waitclass Other 5 0 0 0.01 0 428
[Updated on: Tue, 05 July 2011 21:14] Report message to a moderator |
|
|
|
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Thu Apr 25 03:51:00 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
