Re: Slow OLTP users 14CPU SunFire

BLGuy wrote:
> We have a large (3TB) Oracle 8.1.7 OLTP database running on a 14CPU
> SunFire server (48GB RAM). This drives a website with around 100 users
> requests per second (transactional commits per second). We use MTS to
> manage web server connection requests.
>
> Generally the system runs fine - easy sub second responses. However,
> this can be very easily upset if we need to do maintenance tasks (data
> loads and index rebuilds) and we find that this brings the system to a
> virtual halt.
>
> We are planning a large upgrade and data migration on the system -
> which will have to include work during working hours (it's a 6 month
> project). As the DBA, I'm concerned that the system this powerful
> cannot support OLTP users and our data management tasks.
>
> Is this typical? Do other large OLTP Oracle system have the same
> "feature" where a 5 minute "CREATE TABLE AS SELECT" command in SQLPLUS
> turns millisecond web responses into 5+ second responses.
>
>
> Example - During normal working day, a look at V$SESSION will show 100
> sessions, with only 5 or so "ACTIVE" at that very moment. (Each web
> request is sub second so you don't see them!) However, if I run a
> "CREATE TABLE newtable AS SELECT * FROM table_with_a_million_rows" then
> V$SESSION will show up all the active users (60+) and their active
> session time goes into many seconds. User response becomes very poor
> (the phone starts ringing off the hook!).
>
> If I run the same in "NOLOGGING" mode, then there is no noticable
> problem. Is this a LGWR problem?
>
>
> I know this post does not have many "facts" - I will be willing to
> supply as requred.
>
> Thanks in advance - Guy

Start a 10046 trace on both one of the jobs doing the dml and another job retrieving. Look at hotsos.com or asktom.oracle.com if you don't know how to start a 10046 trace.

Use tkprof from a 9i install or the free orarsp utility to analyze both trace files.

What are you waiting on?

Once you know that you can get somewhere.

You are just guessing without having that information and while it can be a lot of techy fun to switch redo logs around, go raw, etc ... you won't have the info you need.

Buy Cary Millsap's book quickly and use that as a guide on how to proceed. Received on Mon Jun 06 2005 - 18:24:39 CDT