Re: stress tests for a scale to 30,000 users
Inline... [EMAIL PROTECTED] 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: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
Re: stress tests for a scale to 30,000 users
At 13:14 22-12-03 -0800, you wrote: 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!). You can find James wonderfull paper at http://www.oaktable.net/getFile?id=65. Regards, Carel-Jan === If you think education is expensive, try ignorance. (Derek Bok) === -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Carel-Jan Engel INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
Re: stress tests for a scale to 30,000 users
- Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, December 22, 2003 4:14 PM Inline... [EMAIL PROTECTED] 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? we are on 9.2, Im hoping a large undo tablespace will be ok. Most of the long running queries will be in query only tables though.(i think... getting too many 'dunnos' when i ask questions). however, cant guarantee they will use undo tablespaces since I cant control or even look at the production instance. 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: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] 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
Re: stress tests for a scale to 30,000 users
see my responses below... - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, December 22, 2003 4:14 PM Inline... [EMAIL PROTECTED] 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: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the
Re: stress tests for a scale to 30,000 users
Since you are on 9i, have you considered monitoring the tables? ( alter/create table monitor ) This would reduce the need to collect statistics so often. Jared On Mon, 2003-12-22 at 12:19, [EMAIL PROTECTED] 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 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. 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. 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: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: Jared Still INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
