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%

On 6 Feb 2005 14:40:11 -0800, agonenil@gmail.com wrote:

>My questions are :
>
>1. Is it good to use _serial_direct_read ?
>2. does someone has experince with this parameter ?

My questions are
- why are you using an undocumented parameter?
- Was this recommended to you by OTS and/or did you ask the same
questions to OTS?
- or are you just adventurous and willing to break your support
license?


--
Sybrand Bakker, Senior Oracle DBA

I'm not using it . Just want to see what is effect.
I'm working in company which develop Fraud application.
Our database get a lot of records per day 250m (each record is 0.5K).
I was to use as much possiable I/O throwput to our servers (oci + c
programs) , with impacting the database performace.

You might consider reading up on the Resource Manager functionality.
It's CPU-based rather than i/o-based, but that may suffice to limit the
resources used by the query.

Apart from that, as Sybrand says you shouldn't be touching this
parameter.

One of the oddities about serial_direct_read is that
it seems to become an attribute of the cursor. This
means that you won't see a difference in your test
because the second run of the SQL uses the cursor
opened in the first part of the test, and therefore does
not do direct reads. You need to have two slightly
different statements if you want to see the effects
in the stats and latches.

Your theory about less CPU because of the reduction
of buffer and latch activity is correct - but the effect
may not be significant (The same is true for a change
in block size).

It is not 'good' to use the parameter, because it is
a hidden parameter - and these, more or less by
definition, are not good parameters to use on
production systems without the direct approval
of Oracle support.

Regards

Jonathan Lewis

http://www.jlcomp.demon.co.uk/faq/ind_faq.html
The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/seminar.html
Public Appearances - schedule updated Jan 21st 2005






<agonenil@gmail.com> wrote in message
news:1107729611.453578.112930@c13g2000cwb.googlegr oups.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).
>

agonenil@gmail.com wrote:

> 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 ?

I've read your inquiry and the responses from Jonathan, Sybrand,
agonenil, and David and based on the fact that what you are suggesting
is undocumented or otherwise unrecommended I would be hesitant to
endorse them.

But what bothers me about your inquiry is that other than "academic
interest" there doesn't seem to be a business case. Is something slow?
How slow compared to some metric? How many records? What percentage of
the records do you actually need to read? 1% or 100%? What version of
Oracle? And if 10g what does ASH show?
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace 'x' with 'u' to respond)