Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
add comment

2 Answers

up vote 3 down vote accepted

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.

share|improve this answer
add comment

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

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.