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

<p.santos000_at_gmail.com> wrote in message news:1166653299.575962.3830_at_48g2000cwx.googlegroups.com...
> Folks,
> 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
> do
> UPDATE DEMO_<CLIENT_ID>
> 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;
> commit;
> done.
>
> 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
> cache.
>
> 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?
>

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.)

-- 
Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

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