Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: lots of waiting on 'db file parallel write'

Re: lots of waiting on 'db file parallel write'

From: Joel Garry <joel-garry_at_home.com>
Date: 28 Feb 2006 11:39:09 -0800
Message-ID: <1141155549.318840.87450@i39g2000cwa.googlegroups.com>

peter wrote:
> Joel,
> thanks for the feedback.
>
> I don't have multiple buffer pools because I have not been able to
> determine which
> objects to place outside the default pool. What happens is that we
> typically have
> the following load on the system at any point in time.
>
> - 20+ concurrent uploads jobs - customers upload anywhere from
> 100K records to 2 million records .. each upload job
> commits every
> 100 recs and they do things like single index lookups to
> check if
> user/data exists etc, then insert or update....
>
> - 2-3 concurrent processes clearing columns for customers.
> Basically updating
> anywhere from 1-5 columns=NULL. These commit every 500K
> records and
> typically customers clear anywhere between 500K recs and 9
> million rows.
> Sometimes we generate 1MB of redo/sec...
>
> - 10+ concurrent select queries..this is where customers select
> large datasets
> by IFFS or TScan's and hash joins or nested loops.
> These queries only do index lookups and nested loops when
> the
> rows selected is small.. A customer can decide to
> execute 20+ queries
> like this sequentially in 1hr period, but then not
> execute a query for
> another 3 days.
>
> - 10+ concurrent counts with queries similar to the above..just
> counting instead
> of selecting.
>
> *** every customer has it's own data tables and they can
> query/filter on any
> column they feel like it... at anytime during the day.
>
> - So given this type of daily workload, it's difficult to say "i'm
> going to cache this customer's data, but not that customer". We are
> moving to remove the large index lookup that we use for everything in
> our system..this large index (500MM rec index) accounts for over 40% of
> the wait time of 'db file sequential read' because every
> upload/select/count needs to query the index since it's the only thing
> shared between customers.
>
> To make matters worse, we are growing at about 50% rate every year, so
> this type of volume/processes increase every month..
>
> It's a mess of a system, but until we re-write it I'm just going to
> have to do whatever I can to keep it going.
>
> What guidelines can I use to try and determine which objects would
> benefit from being placed in other pools?

See
http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:2170006092044 for some overall discussion.

Search for V$BH in the docs, see this
http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14211/memory.htm#i30935

Google this group for various opinions, too. I think putting that big index you are getting rid of into its own pool (sized by examing v$bh) might be some quick and dirty help.

One obvious thing we've skipped in this thread- are you sure you aren't swapping during the slowdowns? If you are real close on physical memory usage, any random new process could put you over the edge. Even if you are not real close, some process could use a lot more than you would expect. I've learned to take that pga_aggregate_target advice with some salt.

jg

--
@home.com is bogus.
Q. Your item on the most admired body parts for females made me wonder:
Who do male cosmetic surgery patients want to look like?  -Brian
Henry, Indianapolis, Ind.
A. Brokeback Mountain stars and Oscar nominees Heath Ledger and Jake
Gyllenhaal. "We've seen a shift away from the heavy macho features
of Ben Affleck and Russell Crowe," says Beverly Hills cosmetic
surgeon Dr. Richard Fleming. "Men ask for Jake's eyes and Heath's
mouth, chin and jaw."  - Walter Scott
Received on Tue Feb 28 2006 - 13:39:09 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US