Help - Search - Members - Calendar
Full Version: will increasing the initial extent on a table help the speed
Oracle DBA Forums > Oracle > Oracle Forum
muppet
I have a huge loading procedure that runs overnight and it takes 20 minutes.
The data is loaded from a DB link into temp tables and then processed.
The process includes populating other tables and doing a lot of updating.

The process depends on other load jobs and only kicks off once they are complete.
If they are late it only starts after 8am, then it take 3 HOURS so from 20 min to 3 hours is a HUGE change.
However code wise I can't speed it up as the fastest I can get it is 20 min.
The tables are read only no changes can be made to them by users only the procedure can update them so there are no external locks happening on them but the users can select from them.

But after 8am the traffic on the DB goes crazy and I need to find out what I can do about these 3 hours.
Someone suggested I increase the initial extents of the tables. The initial extent is 80kb but the max extent is unlimited.

ANY SUGGESTIONS PLEASE.
What about the temp tablespace?

the stats on the table look like this:
Name value
active txn count during cleanout 563
application wait time 0
auto extends on undo tablespace 0
background checkpoints completed 0
background checkpoints started 0
background timeouts 0
branch node splits 3
buffer is not pinned count 54937540
buffer is pinned count 5347985674
bytes received via SQL*Net from client 0
bytes received via SQL*Net from dblink 1460829
bytes sent via SQL*Net to client 0
bytes sent via SQL*Net to dblink 7199
Cached Commit SCN referenced 20967
calls to get snapshot scn: kcmgss 21701
calls to kcmgas 2768
calls to kcmgcs 332
calls to kcmgrs 0
change write time 252
cleanout - number of ktugct calls 838
cleanouts and rollbacks - consistent read gets 0
cleanouts only - consistent read gets 243
cluster key scan block gets 5922
cluster key scans 852
cluster wait time 0
cold recycle reads 0
commit batch performed 1
commit batch requested 1
commit batch/immediate performed 1
commit batch/immediate requested 1
commit cleanout failures: block lost 0
commit cleanout failures: buffer being written 0
commit cleanout failures: callback failure 62
commit cleanout failures: cannot pin 1
commit cleanout failures: hot backup in progress 0
commit cleanout failures: write disabled 0
commit cleanouts 8279
commit cleanouts successfully completed 8216
commit immediate performed 0
commit immediate requested 0
commit nowait performed 0
commit nowait requested 0
Commit SCN cached 6
commit txn count during cleanout 453
commit wait performed 0
commit wait requested 0
commit wait/nowait performed 0
commit wait/nowait requested 0
concurrency wait time 191
consistent changes 218
consistent gets 64840811
consistent gets - examination 26341
consistent gets direct 0
consistent gets from cache 64840817
CPU used by this session 163394
CPU used when call started 0
CR blocks created 129
current blocks converted for CR 0
cursor authentications 10
data blocks consistent reads - undo records applied 164
db block changes 186385
db block gets 318331
db block gets direct 6642
db block gets from cache 311689
DB time 0
DBWR checkpoint buffers written 0
DBWR checkpoints 0
DBWR fusion writes 0
DBWR lru scans 0
DBWR make free requests 0
DBWR object drop buffers written 0
DBWR parallel query checkpoint buffers written 0
DBWR revisited being-written buffer 0
DBWR tablespace checkpoint buffers written 0
DBWR thread checkpoint buffers written 0
DBWR transaction table writes 0
DBWR undo block writes 0
DDL statements parallelized 0
deferred (CURRENT) block cleanout applications 6873
deferred CUR cleanouts (index blocks) 0
DFO trees parallelized 0
dirty buffers inspected 3618
DML statements parallelized 0
doubling up with imu segment 0
drop segment calls in space pressure 0
enqueue conversions 345
enqueue deadlocks 0
enqueue releases 3451
enqueue requests 3453
enqueue timeouts 0
enqueue waits 0
exchange deadlocks 0
execute count 18344
flashback log writes 0
frame signature mismatch 0
free buffer inspected 301939
free buffer requested 300576
gc blocks corrupt 0
gc blocks lost 0
gc claim blocks lost 0
gc CPU used by this session 0
gc cr block build time 0
gc cr block flush time 0
gc cr block receive time 0
gc cr block send time 0
gc cr blocks received 0
gc cr blocks served 0
gc current block flush time 0
gc current block pin time 0
gc current block receive time 0
gc current block send time 0
gc current blocks received 0
gc current blocks served 0
gcs messages sent 0
ges messages sent 0
global enqueue CPU used by this session 0
global enqueue get time 0
global enqueue gets async 0
global enqueue gets sync 0
global enqueue releases 0
global undo segment hints helped 0
global undo segment hints were stale 0
heap block compress 27
hot buffers moved to head of LRU 10767
immediate (CR) block cleanout applications 243
immediate (CURRENT) block cleanout applications 808
immediate CR cleanouts (index blocks) 0
IMU bind flushes 0
IMU commits 3
IMU contention 0
IMU CR rollbacks 0
IMU Flushes 6
IMU ktichg flush 0
IMU mbu flush 0
IMU pool not allocated 1
IMU recursive-transaction flush 0
IMU Redo allocation size 0
IMU undo allocation size 4896
IMU undo retention flush 0
IMU- failed to get a private strand 1
index fast full scans (direct read) 0
index fast full scans (full) 6
index fast full scans (rowid ranges) 0
index fetch by key 4717
index scans kdiixs1 18140
instance recovery database freeze count 0
IPC CPU used by this session 0
java call heap collected bytes 0
java call heap collected count 0
java call heap gc count 0
java call heap live object count 0
java call heap live object count max 0
java call heap live size 0
java call heap live size max 0
java call heap object count 0
java call heap object count max 0
java call heap total size 0
java call heap total size max 0
java call heap used size 0
java call heap used size max 0
java session heap collected bytes 0
java session heap collected count 0
java session heap gc count 0
java session heap live object count 0
java session heap live object count max 0
java session heap live size 0
java session heap live size max 0
java session heap object count 0
java session heap object count max 0
java session heap used size 0
java session heap used size max 0
leaf node 90-10 splits 12
leaf node splits 230
lob reads 0
lob writes 0
lob writes unaligned 0
local undo segment hints helped 0
local undo segment hints were stale 0
logons cumulative 1
logons current 1
messages received 0
messages sent 145
Misses for writing mapping 0
native hash arithmetic execute 0
native hash arithmetic fail 0
no buffer to keep pinned count 0
no work - consistent read gets 64795437
number of map misses 0
number of map operations 0
opened cursors cumulative 1866
opened cursors current 50
OS All other sleep time 0
OS Chars read and written 0
OS Data page fault sleep time 0
OS Input blocks 0
OS Involuntary context switches 0
OS Kernel page fault sleep time 0
OS Major page faults 0
OS Messages received 0
OS Messages sent 0
OS Minor page faults 0
OS Other system trap CPU time 0
OS Output blocks 0
OS Process heap size 0
OS Process stack size 0
OS Signals received 0
OS Swaps 0
OS System call CPU time 0
OS System calls 0
OS Text page fault sleep time 0
OS User level CPU time 0
OS User lock wait sleep time 0
OS Voluntary context switches 0
OS Wait-cpu (latency) time 0
OTC commit optimization attempts 0
OTC commit optimization failure - setup 0
OTC commit optimization hits 0
Parallel operations downgraded 1 to 25 pct 0
Parallel operations downgraded 25 to 50 pct 0
Parallel operations downgraded 50 to 75 pct 0
Parallel operations downgraded 75 to 99 pct 0
Parallel operations downgraded to serial 0
Parallel operations not downgraded 0
parse count (failures) 4
parse count (hard) 102
parse count (total) 1512
parse time cpu 113
parse time elapsed 171
physical read bytes 3286163456
physical read IO requests 91373
physical read total bytes 3286949888
physical read total IO requests 91421
physical read total multi block requests 26188
physical reads 401143
physical reads cache 298561
physical reads cache prefetch 253323
physical reads direct 102582
physical reads direct (lob) 0
physical reads direct temporary tablespace 102582
physical reads for flashback new 0
physical reads prefetch warmup 0
physical reads retry corrupt 0
physical write bytes 739622912
physical write IO requests 3427
physical write total bytes 739622912
physical write total IO requests 3427
physical write total multi block requests 3386
physical writes 90286
physical writes direct 90286
physical writes direct (lob) 0
physical writes direct temporary tablespace 90286
physical writes from cache 0
physical writes non checkpoint 90286
pinned buffers inspected 6
prefetch clients - 16k 0
prefetch clients - 2k 0
prefetch clients - 32k 0
prefetch clients - 4k 0
prefetch clients - 8k 0
prefetch clients - default 1
prefetch clients - keep 0
prefetch clients - recycle 0
prefetch warmup blocks aged out before use 0
prefetch warmup blocks flushed out before use 0
prefetched blocks aged out before use 33249
process last non-idle time 1215404072
PX local messages recv'd 0
PX local messages sent 0
PX remote messages recv'd 0
PX remote messages sent 0
queries parallelized 0
queue flush 0
queue ocp pages 0
queue position update 0
queue qno pages 0
queue single row 0
queue splits 0
queue update without cp update 0
recovery array read time 0
recovery array reads 0
recovery blocks read 0
recursive calls 41811
recursive cpu usage 4
redo blocks read for recovery 0
redo blocks written 0
redo buffer allocation retries 0
redo entries 93751
redo log space requests 0
redo log space wait time 0
redo log switch interrupts 0
redo ordering marks 1463
redo size 28342624
redo subscn max counts 0
redo synch time 16
redo synch writes 43
redo wastage 0
redo write time 0
redo writer latching time 0
redo writes 0
rollback changes - undo records applied 0
rollbacks only - consistent read gets 128
RowCR - resume 0
RowCR - row contention 0
RowCR attempts 0
RowCR hits 0
rows fetched via callback 720
serializable aborts 0
session connect time 1215404072
session cursor cache count 100
session cursor cache hits 1688
session logical reads 65158917
session pga memory 10657168
session pga memory max 108895632
session stored procedure space 0
session uga memory 8436384
session uga memory max 105990568
shared hash latch upgrades - no wait 18142
shared hash latch upgrades - wait 0
SMON posted for dropping temp segment 0
SMON posted for instance recovery 0
SMON posted for supplemental logging cleanup 0
SMON posted for txn recovery for other instances 0
SMON posted for undo segment recovery 0
SMON posted for undo segment shrink 0
sorts (disk) 1
sorts (memory) 188
sorts (rows) 17670590
space was found by tune down 0
space was not found by tune down 0
sql area aged 0
sql area purged 16
SQL*Net roundtrips to/from client 0
SQL*Net roundtrips to/from dblink 180
steps of tune down ret. in space pressure 0
summed dirty queue length 0
switch current to new buffer 6
table fetch by rowid 2701368658
table fetch continued row 6
table lookup prefetch client count 0
table scan blocks gotten 235637
table scan rows gotten 8812164
table scans (cache partitions) 2
table scans (direct read) 0
table scans (long tables) 10
table scans (rowid ranges) 0
table scans (short tables) 260
total number of slots 0
total number of times SMON posted 0
total number of undo segments dropped 0
transaction lock background get time 0
transaction lock background gets 0
transaction lock foreground requests 0
transaction lock foreground wait time 0
transaction rollbacks 1
transaction tables consistent read rollbacks 0
transaction tables consistent reads - undo records applied 0
tune down retentions in space pressure 0
undo change vector size 9867884
undo segment header was pinned 0
user calls 2
user commits 23
user I/O wait time 9740
user rollbacks 0
workarea executions - multipass 2
workarea executions - onepass 8
workarea executions - optimal 142
workarea memory allocated 6179
write clones created for recovery 0
write clones created in background 0
write clones created in foreground 2
HAL9000
"If they are late it only starts after 8am, then it take 3 HOURS so from 20 min to 3 hours is a HUGE change."

What else is running at that time?

"But after 8am the traffic on the DB goes crazy"

Run a statspack report for the duration, that will tell you what's up.

Larger initial extents won't help. After 8, it's competing with other tasks, most likely disk or network contention.
muppet
THANKS will do that
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.
Invision Power Board © 2001-2014 Invision Power Services, Inc.