Oracle Database running slow on ZFS Solaris

[avion1]

Hi All,

We recently moved our oracle database (ver 9.2.0.8, Solaris 9, UFS ) to a new T5120 Sun Server.

Since moving to ZFS oracle queries are running very slow (almost 50% slow).

I have tried to change a oracle parameters but still no improvement.

I have created 8K files system for oracle database files and indexes as described on a forum.

The query is (a simple select ) taking very long time.

Timing:

real 2m20.192s
user 1m52.123s
sys 0m22.978s


Could someone, please advise where else to look at.

[PAVB]

The query is (a simple select ) taking very long time.

Timing:

real 2m20.192s
user 1m52.123s
sys 0m22.978s


Could someone, please advise where else to look at.

Provided information is out of context and really means nothing.

Have you compared execution plans? old server vs. new server?
Does the technology refresh includes new storage subsystem?
Have you compared wait events? old server vs. new server?
Are database setup identical? old server vs. new server?
Have you moved to an upper version of Oracle during the technology refresh?
Would you mind in describing "how" you moved the database to the new server?

[avion1]

Hi Paul

thanks your reply.

I created the database from scratch and then imported all the data using oracle import.

All the hit ratio are very good

Library Cache Data Dictionary Cache Buffer Cache Hit Ratio
99.92 99.57 80.64
99.91 99.56 80.21

A simple querty (select code,sum(1) from customers ....)

taking 3 time more time on the new server.

Is there any parameter required for ZFS for improve performance.

[PAVB]

I'm seeing you have answered none but the last question.

Let me add a new one to the list... how init.ora files compare?

[avion1]

Have you compared execution plans? old server vs. new server?

Yes, I have compared both execution plan and the are identicals.

oh the new server, plan is
Rows Plan
------------ ----------------------------------------------------------------------
30 SELECT STATEMENT
30 SORT GROUP BY
36,029,373 INDEX FAST FULL SCAN IDX3_WM_JOB_HEADER_NARR

old the old server 30
SELECT STATEMENT

30
SORT GROUP BY

34,803,182
INDEX FAST FULL SCAN IDX3_WM_JOB_HEADER_NARR

-----






Does the technology refresh includes new storage subsystem?
Have you compared wait events? old server vs. new server?
Are database setup identical? old server vs. new server?
New Server
------------
SQL*Plus: Release 9.2.0.8.0 - Production on Mon Oct 10 09:13:44 2011

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.


new serverSQL*Plus: Release 9.2.0.8.0 - Production on Mon Oct 10 09:13:44 2011

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer RSQL*Plus: Release 9.2.0.8.0 - Production on Mon Oct 10 09:17:37 2011

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.


Connected to:
Oracle9i Release 9.2.0.8.0 - Production
JServer Release 9.2.0.8.0 - Production
elease 9.2.0.8.0 - Production


----------




Have you moved to an upper version of Oracle during the technology refresh?

No, we are still on the same verion and same patch no.

By mistake, We have installed Enterprise version on the new server.

Would you mind in describing "how" you moved the database to the new server?

We exported the full data using oracle export import.

Parameters Files:


Old Server

_pga_max_size big integer 1677721600
cpu_count integer 4
db_cache_size big integer 1056964608

db_writer_processes integer 1

db_file_multiblock_read_count integer 8
java_pool_size big integer 16777216
pga_aggregate_target big integer 2097152000


New Server

cpu_count integer 32
db_cache_size big integer 1174405120
db_file_multiblock_read_count integer 128
db_writer_processes integer 4
java_pool_size big integer 33554432
pga_aggregate_target big integer 1048576000

[PAVB]

Have you compared execution plans? old server vs. new server?

Yes, I have compared both execution plan and the are identicals.

Please do a 10046 trace of the offending query, both on the old and new server.
Check how wait events compare.