Hi there
I have two oracle servers with following (same) details:
RHEL 5.8 64-bit
Oracle 10gR2 - 10.2.0.5.8
ASM 10gR2 - 10.2.0.5.8
Server A: RAM 32GB, 8 CPUs @ 3.00GHz
Server B: RAM 128GB, vCPUs 16 cores
Server A (physical server) already has a database A. Server B (on VMWare - yes, my client is moving all Oracle servers to VMware for whatever reason) is a new installation and new database B with exact same init params as databas A. I expdp the data from database A and impdp into database B.
As per the hardware team, the hardware is better than the old server B. I did a very basic test to check if new DB performs better than that on physical server. Here is the results:
I ran a simple query to create a new table. The original table (say, table_a) contains 1.7+ million rows and size is 2.2GB.
create table test1
as
select * from table_a;
It took 3:28mins on database B while it took only 1:55mins on database A. So the new database B seems to be performing poor (apparently). Then I looked at the explain plan (not sure if it means much because it s a very simple query) and here it is from both databases:
Database A (physical server
SELECT STATEMENT ALL_ROWS
Cost: 14,052 Bytes: 2,161,302,003 Cardinality: 16,250,391 |
| | 1 TABLE ACCESS FULL TABLE table_a
Cost: 14,052 Bytes: 2,161,302,003 Cardinality: 16,250,391 |
Database B (virtual server)
SELECT STATEMENT ALL_ROWS
Cost: 59,844 Bytes: 2,161,302,003 Cardinality: 16,250,391 |
| | 1 TABLE ACCESS FULL TABLE table_a
Cost: 59,844 Bytes: 2,161,302,003 Cardinality: 16,250,391 |
Questions:
1. Why is the cost different? Should I "compute statistics" on database B (virtual server)?
2. How to investigate further and find out reason for the time difference?
3. What other benchmark test can I run to make sure that I have the right database configuration?
Not sure if this is enough info - if not, please let me know what else should I provide.
The team I have to hand-over this server is refusing to accept it by saying that it is slower than the existing one.
Please advise!
Best regards