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?
thanks
-peter
Received on Mon Feb 27 2006 - 21:22:34 CST
