Performance of a database for a particular task

Question

Scenario: We have a PL/SQL task and are interested in its performance with certain configurations of the database. This includes global features of the database along with the addition of hints for the optimizer.

Question: Is it sufficient to run the test once for each configuration to take a measurement (memory used, elapsed time, CPU time etc). Or should more samples be required. If the latter how does one determine the least number is required.

Addendum

I was trying to elude to trying to understand how performance of the query changes when one factor changes - e.g number of entries in a particular table. This is so we can gain understanding and prediction of performance for high data loads. Also to try to find if any of these factors does not produce exponential growth in usage.

One assumes that for each data point the test needs to be fun multiple times - so how many times is required?

Answer 1

Whenever we think about the performance aspect of the Application/ Database/ feature or whatever. Never go for single results set or single iteration. You need to collect enough data for the statistical analysis of the result for reaching to an answer that your subject under test is performing Good or Bad.

It is simple fundamental of Statistics that "More data you have more precise/accurate your result will be" i.e. you will be more near to the correct result (with limit --> 0). Hence you need to collect data for multiple samplers. You can again relate this with the results provided by JMeter i.e. 90% line (for example), if you have only 1 sample then your Average, Min., Max. and 90% will be same and when you have more data all values will be different and same will happen with your Std. Deviation too and you will be plot a good graph for analysis and reporting.

So, answer to your question

Is it sufficient to run the test once for each configuration to take a measurement (memory used, elapsed time, CPU time etc). Or should more samples be required

is YES, you should have more samples.

These more samples you can generate using the JDBC sampler of Apache JMeter, for this either you can run your script for Single user but for Multiple Rounds (if you don't want to put concurrent user load on your scenario) Or you can run multiple threads at once.

Collect data for 400-500 samplers and that too multiple times like:

1. Run your script for single user (i.e. No of Threads = 1, but number of rounds = 500)
2. Run this above mentioned scenario multiple times a day e.g. once in morning, then in afternoon, 3rd one in evening.
3. For more precision, you can repeat steps 1 and 2, for multiple days (3-4 days) and then compare & average out the results.

You can try tuning the Database between your various rounds of execution to see if that tuning and optimizing is helping you in +ve direction (that's how performance testing and performance tuning goes hand-in-hand), but never go with single sample or single round results only always have mutiple.

Answer 2

I would run it a number of times for each configuration and average the results (maybe a dozen times?)

However, the important thing is to make sure that the system is in the same state before each run, otherwise the database may be, for example, using cached execution plans.

The most obvious way to get a somewhat consistent environment would be to restart the database service before each run.

Answer 3

I would recommend using Apache JMeter which is capable of producing severe load using JDBC protocol.

In order to test your database:

1. Download Oracle JDBC Driver for your Oracle installation and drop it to lib folder
2. Add JDBC Connection Configuration test element and specify Oracle connection details (driver class name, JDBC url, credentials)
3. Add JDBC Request Sampler and put your query there
4. Repeat step 3 for all queries you're interested in
5. Add a Listener to visualize results. I.e. Aggregate Report should be fine
6. Configure virtual users and loops count
7. Run test for each configuration and compare results.

See The Real Secret to Building a Database Test Plan With JMeter guide for more detailed explanations and instructions.

Answer 4

There are multiple contributing factors affecting query execution

• Isolation levels (This decide concurrency / deadlocks when same data access by multiple sessions)
• Indexes (This will decide execution plan seek / scan which will effect query execution time / performance)
• You need to look at execution plan of the complete proc. You need to look at top contributing factors (IO / CPU / Order / Sort / Temp Tables)

Step #3 you can arrive at basic data set. With large (product similar) data set you can arrive at Step #3 and Step #2.

For understanding Isolation levels and impact on concurrent access, you need to run all processes which would simulate concurrent data access. There are define DB Counters and execution plan details which can fetch required details