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

p.santos000_at_gmail.com wrote:
> 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.

Please state explicitly how you are deciding this obvious thing.

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

See the discussion on oracle-l about this ( http://www.freelists.org/archives/oracle-l/12-2006/msg00294.html ) - state your platform and versions. Also see metalink Note:232443.1 and http://jonathanlewis.wordpress.com/2006/11/27/event-10132/

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

Well, only testing will tell, but sometimes you wind up spending more resources on putting the parallel's back together than if you hadn't used parallel. http://www.oracledoug.com/papers.html has a lot of interesting parallel discussion.

Since you are worried about buffer thrashing, check out using keep and recycle buffer pools. See details in the performance tuning manual. (And more details on Jonathan Lewis' site).

If you are not using dedicated connections to begin with, you can't complain about performance. Sharing servers is for scalability in the sense of large numbers of users, the docs are explicit that it is not for performance.

jg

-- 
@home.com is bogus.
http://www.securityfocus.com/archive/1/454965