| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> _serial_direct_read effect
Hi ,
I've a situation that I need to read a big table very fast.
I'm using FTS, but I don't want to use parallel slave (not to over load
the system). I need to read this data only once
I've tried to play with _serial_direct_read but I've found any
differnt.
Alought I think it should reduce cpu usage and latches (not going on
cache).
My other option is to use recycle pool of none-standard block size.
My questions are :
My test is as follow:
I've create a table big_table -
SQL> exec show_space('BIG_TABLE')
Unformatted Blocks ..................... 0 FS1 Blocks (0-25) ..................... 0 FS2 Blocks (25-50) ..................... 0 FS3 Blocks (50-75) ..................... 0 FS4 Blocks (75-100)..................... 0 Full Blocks ..................... 60,564 Total Blocks............................ 101,888 Total Bytes............................. 834,666,496 Total MBytes............................ 796 Unused Blocks........................... 40,981 Unused Bytes............................ 335,716,352 Last Used Ext FileId.................... 4 Last Used Ext BlockId................... 69,768 Last Used Block......................... 491
And run the following test:
set serveroutput on size 1000000
declare a number;
begin
runstats_pkg.rs_start;
execute immediate 'alter session set "_serial_direct_read"=false';
select sum(id) into a from big_table;
runstats_pkg.rs_middle;
execute immediate 'alter session set "_serial_direct_read"=true';
select sum(id) into a from big_table;
runstats_pkg.rs_stop;
end;
/
status is :
Run1 ran in 1792 hsecs
Run2 ran in 1896 hsecs
run 1 ran in 94.51% of the time
I've run on 10.1.0.3 . on my pc .
Can
Name Run1 Run2 Diff STAT...recursive calls 7 6 -1 STAT...enqueue requests 1 0 -1 STAT...enqueue releases 1 0 -1 LATCH.JS slv state obj latch 2 1 -1 LATCH.session timer 6 7 1 LATCH.PL/SQL warning settings 0 1 1 LATCH.ncodef allocation latch 1 0 -1 LATCH.transaction branch alloc 1 0 -1 LATCH.KTF sga enqueue 0 1 1 STAT...redo ordering marks 5 6 1 LATCH.event group latch 0 1 1 LATCH.session switching 1 0 -1 LATCH.process allocation 0 1 1 STAT...parse count (hard) 1 0 -1 STAT...cursor authentications 0 1 1 LATCH.ktm global data 0 1 1 LATCH.simulator lru latch 18 17 -1 STAT...calls to kcmgas 5 6 1 STAT...hot buffers moved to he 1 0 -1 LATCH.job_queue_processes para 0 2 2 LATCH.SQL memory manager worka 408 410 2 LATCH.cursor bind value captur 0 2 2 STAT...consistent gets 60,591 60,594 3 STAT...cleanout - number of kt 11 14 3 STAT...active txn count during 4 7 3 LATCH.session idle bit 3 6 3 LATCH.sort extent pool 4 1 -3 STAT...calls to kcmgcs 4 7 3 STAT...consistent gets from ca 60,591 60,594 3 STAT...consistent gets - exami 11 14 3 LATCH.library cache pin alloca 0 4 4 LATCH.compile environment latc 0 4 4 LATCH.library cache lock alloc 0 4 4 STAT...recursive cpu usage 147 153 6 STAT...consistent changes 670 676 6 LATCH.channel operations paren 61 67 6 STAT...db block changes 1,346 1,353 7 STAT...Cached Commit SCN refer 60,432 60,425 -7 STAT...db block gets 682 690 8 STAT...db block gets from cach 682 690 8 LATCH.active service list 25 33 8 LATCH.active checkpoint queue 12 21 9 STAT...session logical reads 61,273 61,284 11 LATCH.file cache latch 11 0 -11 STAT...physical reads 58,355 58,367 12 STAT...physical reads cache pr 54,676 54,688 12 STAT...physical reads cache 58,355 58,367 12 LATCH.library cache load lock 0 12 12 STAT...free buffer requested 58,360 58,373 13 LATCH.threshold alerts latch 14 0 -14 LATCH.In memory undo latch 14 0 -14 LATCH.JS queue state obj latch 108 144 36 LATCH.lgwr LWN SCN 7 48 41 LATCH.Consistent RBA 6 47 41 LATCH.mostly latch-free SCN 7 49 42 LATCH.cache buffers lru chain 58,378 58,420 42 LATCH.checkpoint queue latch 155 198 43 LATCH.enqueues 285 347 62 LATCH.object queue header oper 116,750 116,819 69 STAT...user I/O wait time 1,696 1,783 87 LATCH.redo allocation 34 143 109 LATCH.child cursor hash table 7 129 122 STAT...free buffer inspected 58,305 58,432 127 LATCH.redo writing 44 177 133 LATCH.messages 132 266 134 STAT...redo size 91,196 91,344 148 LATCH.hash table column usage 0 150 150 LATCH.dml lock allocation 1 274 273 LATCH.undo global data 37 484 447 LATCH.simulator hash latch 14,949 15,471 522 LATCH.row cache objects 152 680 528 LATCH.enqueue hash chains 287 898 611 LATCH.session allocation 2 864 862 LATCH.library cache lock 14 1,909 1,895 LATCH.shared pool 54 2,287 2,233 LATCH.library cache pin 55 2,652 2,597 LATCH.cache buffers chains 183,048 187,099 4,051 LATCH.library cache 79 4,131 4,052 Run1 latches total versus runs -- difference and pct Run1 Run2 Diff Pct
 |
 |