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

It strikes me that for all of that horsepower (14 CPU, 48GB RAM) and only 100 requests per second you should not be seeing the kind of impact you are reporting. But as you say there is a lot of information you have not supplied.

When you look at a StatsPack what does it show as the delta between baseline and when maintenance is taking place?

-- 
Daniel A. Morgan
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond)