This time I am going to tell you about a very unique problem and the way it was solved, I am sure that many AIX 6.1 users, and maybe from other platforms, must have this problem and have not noticed it yet.
The story goes back a few months, we had completed a database upgrade from Oracle 10.2.0.3 to Oracle 11.2.0.2.3, which was the most recent version at the time, and after that we noticed that the response times had deteriorated, without explanation.
As the upgrade included the migration of the database to a new server, we started doing some tests using both servers and both versions of Oracle and finally managed to reduce the problem to the use of SYSDATE, with the results shown below.
DECLARE
v_dt DATE;
BEGIN
FOR i IN 1 .. 1000000 LOOP
SELECT SYSDATE INTO v_dt FROM dual;
END LOOP;
END;
/
Old server
10.2.0.3:
Elapsed: 00:00:32.17
11.2.0.2.3:
Elapsed: 00:00:41.09
New server
11.2.0.2.3:
Elapsed: 00:00:56.89Notice how on the old computer, Oracle 11.2 takes 28% more time to process than Oracle 10.2, and on the new computer the situation is even more serious as it takes 78% more time, this considering that the new computer is twice as fast as the old one!
As the problem was evident we turned to Oracle Support, who finally recommended us to upgrade to the recent Oracle 11.2.0.3, which we did initially on the old server, with the following result:
Elapsed: 00:00:32.62The situation improved remarkably and we could already observe times similar to those provided by Oracle 10.2.0.3, so we started the upgrade of the production server to Oracle 11.2.0.3.
Great was our surprise when we then found the following:
Elapsed: 00:00:51.53The improvement was only 9%, terrible, inexplicable, discouraging. It was then that Oracle pulled out its ace up its sleeve and told us to apply the patch for the bug 12596494 GENERALLY HIGHER CPU USAGE IN 11.2.0.2 THAN 10.2.0.4 11.2.0.3.0 IBM AIX on POWER Systems (64-bit), which we had already applied in Oracle 11.2.0.2.3 but which had not yet been resolved in 11.2.0.3.
With the hope that we would finally solve the problem we applied it on the production server, obtaining the following result:
Elapsed: 00:00:43.43Even with the patch applied, the time was 34% higher than Oracle 10.2.0.3 achieved on the old server, with processors much slower than the production one.
We had already run out of ideas, but as they say: when it is darkest, it is because it is about to dawn, and it was just at this moment that IBM made its appearance: an IBM Engineer from Argentina directed us to the APAR IZ86764 PERFORMACE DIFFERENCE WHEN USING OLSON TZ .VS. POSIX TZ:
Problem summary
Doc change noting performance impact when using Olson time zone instead of Posix time zone.
Problem conclusion
There will be new information added to the "Miscellaneous tunable parameters" section under "Tunable parameters – Environment variables" in the Performance Management Guide regarding the Olson time zone, basically stating that TZ in AIX 6.1 and later are defaulted to Olson time zone, and may be tuned to POSIX time zone for performance sensitive applications that do not depend on adequately handled changes to time zone rules and daylight saving time.
Bingo! Comparing the old and the new server we found:
[root@old.server]$ oslevel –s
5300-07-00-0000
[root@old.server]$ echo $TZ
EST5 (POSIX)
[root@new.server]$ oslevel –s
6100-06-05-1115
[root@new.server]$ echo $TZ
America/Lima (OLSON)Indeed, that was the difference between the two servers, the old one used a Posix type TZ, unlike the new one, which used an Olson type TZ, so we immediately made the change:
/etc/environment
#TZ=America/Lima
TZ=EST5And then we performed the obligatory test, with the result:
Elapsed: 00:00:17.19Finally we got the process to run in less time, being 88% faster compared to the time observed in the old computer, problem solved!
Some time later I have had the opportunity to work with similar installations (AIX 6.1 with Oracle 11.2), in which they were working with Olson type TZ and without applying any patch, so I am afraid that for the vast majority of users of this combination the problem has not been noticed yet and therefore do not have the performance they could have.
That’s as far as it goes, for now; I hope that with the above you can get improvements in your installations, maybe this also happens in other platforms, if so please share your experience through this medium, we will be very grateful, see you next time!