switching to 'direct path read' should help prevent thrashing buffer cache ?

switching to 'direct path read' should help prevent thrashing buffer cache ?

Post by p.santos00 » Fri, 22 Dec 2006 07:21:39

Our environment is neither an OLTP nor a DSS system. We have
transactions that cross boundaries.

Particularly we have these one of jobs that many of our customers run
at any time during the
day in which they chose to clear a table column.
Most clients clear anywhere from 1 million rows to 11 million rows
per job.

The pseudo-code and SQL looks like this:

while SQL%ROWCOUNT < 500,000
set col1= null,
col2= null,
col3= null
WHERE ( col1 is not null OR
col2 is not null OR
col3 is not null ) AND ROWNUM <= 500,000;

We use a ROWNUM limit in order to prevent row blocking for other
processes that
might be processing single row dml against the table ..

We have increased the efficiency of these processes .. making IO
faster and now customers
are just doing it more often. ... this obviously thrashes my buffer

Nearly all updates spend most of their time waiting on 'db file
scattered read'.
We have db_file_multiblock_read_count = 128.

Should also mention that this process connects via a shared/mts
connection... although
we can change that if needed.

I'm thinking about having just this process run in parallel in order to
bypass the buffer cache because, I don't believe this process benefits
from caching and it causes blocks to age out faster for other clients
that are doing other things .. and do benefit from caching.

My thought is that if I switch this to a dedicated connection and I
add a PARALLEL hint
( even if it's just 2 parallel servers per job), the job will
complete faster, it will prevent my cache from being thrashed only at
the cost of more pga memory , and a little bit more io.

I'm looking for the cons in doing something like this?

switching to 'direct path read' should help prevent thrashing buffer cache ?

Post by Jonathan L » Fri, 22 Dec 2006 19:35:35

< XXXX@XXXXX.COM > wrote in message
news: XXXX@XXXXX.COM ...

How big is the whole table that's being processed ?
How tightly co-located is the data set to be updated ?

If you can run into locking issues doing this in a single
update, what does it mean for the business logic that
one process can (apparently) update rows that another
process is apparently trying to put out of bounds ?

Parallel probably won't help - large tablescans (and
I assume this table is larger than 2% of the size of the
db_cache - but you could try setting it to NOCACHE)
use only the tail-end of the buffer, so tend not to do much
damage to the cache. Moreover, a parallel update will
lock the table - and you are committing regularly to try
and avoid locking issues.

If the few million rows represent a small fraction of the
data, with a good clustering effect, then creating a function
based index to identify them, and updating through the index
might help.

Otherwise, take out the commit - you are repeatedly re-reading
the blocks that you have just changed, introducing extra costs
of cleanout, and excessive scattered reads (possibly assisted by
a file-system or disk cache, but still wasting resources.)


Jonathan Lewis

Author: Cost Based Oracle: Fundamentals

The Co-operative Oracle Users' FAQ