repost due to mess code; Re: db file parallel read for prefetching?

--my previous mail also show mess code per some people's feedback. so
repost. sorry for the spam.

Thanks Joze and Tanel for your great document!

I confirmed with 10299 that for the fast database, oracle is using prefetch , but for the slow database, it is not using prefetch.

The current problem is, how do I force session to use prefetch, since it does help improve performance of my application.

I tried :
alter session set "_index_prefetch_factor"=1; and then flush shared pool to make the SQL reparsed.

but it did not help.

Thanks you!

Trace file look like:
WAIT #1: nam='db file sequential read' ela= 45217 p1=593 p2=68394 p3=1 FETCH #1:c=10000,e=1103857,p=55,cr=56,cu=0,mis=0,r=15,dep=0,og=4,tim=4023215085717 WAIT #1: nam='SQL*Net message from client' ela= 1124 p1=1650815232 p2=1 p3=0 old_reading=90 new_reading=104
Prefetching 15 blocks
WAIT #1: nam='db file parallel read' ela= 110756 p1=14 p2=15 p3=15 WAIT #1: nam='SQL*Net message to client' ela= 5 p1=1650815232 p2=1 p3=0 Prefetching 0 blocks
WAIT #1: nam='db file sequential read' ela= 48212 p1=556 p2=250140 p3=1 FETCH #1:c=10000,e=199485,p=16,cr=17,cu=0,mis=0,r=15,dep=0,og=4,tim=4023215286540 WAIT #1: nam='SQL*Net message from client' ela= 925 p1=1650815232 p2=1 p3=0 old_reading=60 new_reading=74
Prefetching 15 blocks
--

http://www.freelists.org/webpage/oracle-l Received on Tue Nov 29 2005 - 23:37:55 CST