-
Oracle Database running slow on ZFS Solaris
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.
-
Originally Posted by avion1
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?
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
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.
-
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?
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
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
-
Originally Posted by avion1
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.
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|