Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> _serial_direct_read effect

_serial_direct_read effect

From: <agonenil_at_gmail.com>
Date: 6 Feb 2005 14:40:11 -0800
Message-ID: <1107729611.453578.112930@c13g2000cwb.googlegroups.com>


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 :

  1. Is it good to use _serial_direct_read ?
  2. does someone has experince with this parameter ?

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

382,630 401,740 19,110 95.24% Received on Sun Feb 06 2005 - 16:40:11 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US