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

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?

>
>
> 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!).

>
>
> 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).