Re: stress tests for a scale to 30,000 users

see my responses below...
----- Original Message -----
To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Monday, December 22, 2003 4:14 PM

> Inline...
>
> ryan_oracle_at_cox.net wrote:
>
> > My estimate right now is about a 500GB instance(but could grow). There
are several complexities.
> >
> > 1. high transaction system, but also will have alot of long running
queries
>
> Hello, 1555s! I think you will be plagued by these, even with a high
number/size of rollback/undo segments. Any chance to push the queries to a DSS?
>
> >
> >
> > 2. We deliver data daily and rebuild large parts of the database nightly
with loads. Im not certain I have the window to analyze every index or get histograms on all the tables. There are VERY large data loads and deliveries. Data has to be delivered by a certain time and we get data feeds from other groups. I cannot control when we recieve data to load.
>
> Why analyze every night? If the tables are being rebuilt every night, how
much will be changing? If the size/nature of the data and objects are basically the same, populate it with a set of statistics that will enable the CBO to make good decisions and leave it alone. Keep an eye on the data and adjust the stats as needed. If there are changes, would it be
> possible to determine the new stats and then populate the tables
accordingly using dbms_stats?

1. we have alot of data feeds. we do ingestion from multiple instances. So 'monitoring' is problematic(plus I have alot to do... I write as much code as I do dba stuff).
2. I cannot guarantee that the tables will be the same. One time we had a 29 million row tables with 17 million deletes. We do alot of full refreshes. I cannot guarantee data being the same. I cannot guarantee that 90% of the values in one column will be updated and the 'spread' of data will change.
3. remember, I do not know if Ill even be able to see statistics in production or have any say on parameter settings, etc...

>
> >
> >
> > 3. We will not be actively managing the production server. Its going to
be delivered as an off the shelf product. I do not know what statistics ill be allowed to have for security reasons(this is not govenment stuff so dont worry about what I say). Its up to the client.
> >
> > 4. We are using web server level connection pooling so tracing isnt very
useful.
> >
> > Im essentially the lone performance guy on the team. Ive never done a
scale up this large, or with this many complexities. We just managed to convince them to use bind variables... but they haven't been implemented yet.
> >
> > Im having trouble getting accurate test cases. This is what I am
'attempting' to do at first. Please let me know if my approach is accurate.
> >
> > 1. Find out which queries will be run the most. Are there things people
will do in the mornings, but not in the afternoon(so far its 'dunno').
> >
> > 2. Hopefully, I can get a hold of either the use cases or 'preferebly'
test cases, so we can design our stress tests around actual user processes. All they are doing now is opening up 50+ users and running queries in loops.
>
> I think you are on the right track. If you can turn on tracing with a
logon trigger, you should be able to get some/most(?) of the sql and the order in which they are performed. Strip out the extraneous info and you have a test script. James Morle of the Oak Table (www.scaleabilities.com) had a presentation at UKOUG 2003 about using 10046 files for
> benchmarking. It is not on his site yet, but perhaps we could persuade him
to post it (it was excellent!).

im using statspack. Ill read morle's article. I have his book, but havent been able to read it yet. He seems like a smart guy. This is a 9.2 instance. I dont know if I can get any 10046 settings in production... This REALLY concerns me.

>
> >
> >
> > What other approach should I take to get started. Im rather troubled by
this...
> >
> > --
> > Please see the official ORACLE-L FAQ: http://www.orafaq.net
> > --
> > Author: <ryan_oracle_at_cox.net
> > INET: ryan_oracle_at_cox.net
> >
> > Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> > San Diego, California -- Mailing list and web hosting services
> > ---------------------------------------------------------------------
> > To REMOVE yourself from this mailing list, send an E-Mail message
> > to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> > the message BODY, include a line containing: UNSUB ORACLE-L
> > (or the name of mailing list you want to be removed from). You may
> > also send the HELP command for other information (like subscribing).
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Daniel Fink
> INET: Daniel.Fink_at_Sun.COM
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Ryan
  INET: ryan_oracle_at_cox.net

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).