Performance Tuning

[Thomas7]

Hi,

I am looking for testing the production database on my close test database. I started exporting the statistics from my production database and imported them to my test database. And now I am looking to go forward to do the performance testing on my test database. I need your expertise in the following scenarios.
1) How to generate large no of user's work load?
2) How to test the queries are using optimal execution plans.
3) As my test Database is smaller than Prod, how can I ensure Test performance will also be optimal on PROD.

Thanks in advance for your help.

[PAVB]

1) How to generate large no of user's work load?

Look for a workload generator/simulator. Plenty of offer.

2) How to test the queries are using optimal execution plans.

Looking at them, one at a time.

3) As my test Database is smaller than Prod, how can I ensure Test performance will also be optimal on PROD.

You can't.

[Thomas7]

Thanks for your advice sir. How often is it recommended to gather the statistics on OLTP and OLAP databases?.

Thanks in Advance for your help.

[PAVB]

As a rule of thumbs there is no reason to gather fresh stats if number of rows AND data distribution has changed less than 20%

In the case of tables which number of rows goes up and down -like staging tables - as far as data distribution is similar you may want to gather fresh stats when table is loaded to its max size and stick with it.

Gathering stats for no good reason only leads to problems - chances are Oracle will be changing execution plans as statistics change therfore database would not show a consistent performance.

Bottom line, if queries are consistently performing well - don't touch statistics.

[Thomas7]

Thanks for your valuable advice sir. It made me just clear to how often the statistics should be gathered.