Topic: Performance & Tuning >> Upgrade 9.2.0.6 to 9.2.0.8 causes up to 3x slower performance on some large queries, but 2x faster on most
|
Disclaimer: The purpose of all dbapool forums including OCP and Other Oracle Certification forums is to help each other with specific issues but not to share dump and copyrighted exam content, materials or intellectual property.
You may review the entire Oracle Certification Program Candidate Agreement online Here.
|
|
|
|
| Title: Upgrade 9.2.0.6 to 9.2.0.8 causes up to 3x slower performance on some large queries, but 2x faster on most |
| Mike |
Posted: Feb 12, 2008 10:13:06 AM |
Total Post: 2
Joined: Feb, 2008
|
We are running on an HP DL580 4 CPU (3.0 GHz Xeon, HT) box, RH4 AS U5, Oracle 9.2.0.8, 8 GB ram, 4 TB SCSI320 Raid5 disk.
After the upgrade to 9.2.0.8, we see most things (count(*) of most tables, insert/update/deletes, most CTAS, etc.) are 2x faster than under 9.2.0.6 without changing any parms or any of our SQL. So that is very good.
However, for some particular long-running queries that are CTAS involving large table (160 GB) joined to small table to create a new version of the large one, and even SOMETIMES count(*) on the large table, can take up to 3x SLOWER than under 9.2.0.6. But then at other times, the join can run as fast as it did under 9.2.0.6, and the count(*) can run 2x faster. So it is random that sometimes we get faster performance, but sometime much much slower performance. The big table is parallel 16 and the new one is created parallel 16. This achieved our fastest performance under 9.2.0.6.
For the one particular table involved, we run the SQL CTAS under 9.2.0.8, might take 1h. Next time, run again it takes 1.5h. Run a 3rd time, it takes 2.5h. Most times the count(*) on that table runs in 5min. But occassionally the count(*) can run slower, in 12 or even 18min.
Under 9.2.0.6, consistently the CTAS would take 55 minutes (we have almost 2 years worth of execution history that confirms that), and the count(*) of the table would take around 10 minutes. Unless other things were going on the server at the same time, the performance was very consistent under 9.2.0.6 for all of our queries.
We have dbms gathered stats on all tables involved, and the server is idle when evaluating the run times. We have checked for extra swapping during execution, redo log switches, iostat on the devices, even used dbv to verify the dbf files and found no corruption. We don't have indexes on the tables as this is a data warehouse implementation.
We did have the Jan 2008 CPU patch installed too, but rolled that back to test if it improved things, but it didn't help. We've had support tickets open with oracle support for numerous weeks now but no luck there. At this point we are planning to rollback to 9.2.0.6, unless a quick fix or workaround can be found.
I can supply ora parms or example SQL if you need to see that. Thanks in advance for your help.
Mike
|
|
|
 Murtuja |
| Posted: Feb 12, 2008 10:54:49 PM |  |
|
Total Post: 814
Joined: Jan, 2006
|
Hi,
Take a staspack snapshots and try to see what wait events consumed the most of the time. You can also use this report to see what statements are the most expensive ones and check if their execution plans are what you expected them to be.
Alternatively,you can upload your report to this site and get free tuning recommendations instantly from online analyzer.
http://www.dbapool.com/analyzer/
|
|
|
|
|
| Mike |
| Posted: Feb 14, 2008 10:11:02 PM | |
|
Total Post: 2
Joined: Feb, 2008
|
I have more information to add to this topic, related to memory usage under 9.2.0.8 vs. 9.2.0.6. It seems that 9.2.0.6 was much better at handling our memory because we never had out of memory or swapping issues. Now, with no change to parms or sql or anything, our big hash joins are occupying and never freeing more and more RAM (is this a memory leak under 9.2.0.8?). This causes severe swapping over time, and the only way to remedy it we have found is to bounce the instance. The PQS's that are performing the slowest are swapped out and are experiencing large idle times.
Just after we bounce the instance, and oracle/server memory usage is low, we get very fast expected run times for the join and it is consistent. But as oracle has more activity and we run more and more queries, the memory usage continues to climb and is never freed, until we end up with all 8 GB of RAM used up, and swap space usage continuing to climb.
We have a support ticket opened with Oracle and they are pursuing it on their end.
We have also recently upgraded another similar server to 10.2.0.3, and we are seeing the same kind of behavior on it.
If anyone has seen this kind of growing memory issue either in 9.2.0.8 or 10.2.0.3 and has a solution or workaround, it would be much appreciated!
|
|
|
|
|
| Murtuja |
| Posted: Apr 08, 2008 04:49:45 AM | |
|
Total Post: 814
Joined: Jan, 2006
|
Hi Mike,
Do you have any updates regarding this issue ?
|
|
|
|
|
| Time Zone: EDT |
Send this thread to your friend |
|
|
|
|
Forum Rules & Description
Who Can Read The Forum? Any registered user or guest
Who Can Post New Topics? Any registered user
Who Can Post Replies? Any registered user
|
| |
Get FREE Magazines
|
Top 10 Forum User
|
| Murtuja Khokhar | 814 |
| Mohammed Taj | 694 |
| Jayanta Sur | 479 |
| Vigyan Kaushik | 386 |
| positive fanatic | 361 |
| Gitesh Trivedi | 322 |
| Gopu Gopi | 242 |
| neeraj sharma | 228 |
| Ramesh Jois | 226 |
| snehalatha p | 169 |
|
|
