How to compare performances of two databases

Question

There are two database servers and I want to compare their performances.

Both have Windows operating system and and Oracle (10.2.0.4 and 11.0.2) database.

Both are OLAP databases with 15-20 users each. Hardwares are different but I don't have much info about configurations.

There are similar tables on both of them and I used explain plan to compare performance firing same sql queries to exactly same tables. (58M rows, 60 columns).

I used select * from TABLE and select count(*) from TABLE

One explain plan shows 00:35:00 and other shows 01:45:00 for time column.

I want to learn if comparing explain plans is enough to compare performances, and if not what shall I do?

Answer 1

You can not compare the performance of 2 databases by just looking at their sql plans. The same queries can have very different plans. When the versions are different all kinds of computations for the cost are different. The plan that a query follows in execution could very well be different than the plan you get from explain plan from your client.

The only way to compare the performance is to measure the execution of the queries or processes and compare those timings. You could use AWR to compare the database performance. A certain workload will generate a certain thoughput and consume more or less dbtime. If the dbtime spent and the elapsed time of your jobs is lower, you can safely say that the performance in that database is better compared to the one where the times are higher.

In 11g, with the auto tuning tasks enabled and auto accept sql profiles enabled, it could very well be that the next run already shows better timings.

Answer 2

The way to do this is with database replay, using a real workload captured from your production system.