From: tackenhu@fnal.gov
Date: Fri Dec 06 2002 - 11:39:53 EST
I am looking for any help or suggestions to address a drastic query
performance problem we have encountered in our production Oracle database
after doing a 4.0F to 5.1A PK3 upgrade.
The symptom being observed is that some data intensive reporting jobs
which before ran in approximately 1 hour now run approximately 9 hours.
Other reporting jobs and, for as far as we can find, the online
transactions are not noticeable affected. The application is Oracle
Financials 11.0.3. The database version is 8.1.7.1.
We have verified this problem is not corrected by any of the following
actions:
- Upgrading the database to 8.1.7.4.
- Disabling AdvFS direct I/O use by Oracle 8.1.7.4 using the
_TRU64_DIRECTIO_ Oracle parameter. (Parameter isn't available in 8.1.7.1
to test it.)
- Verifying our kernel parameters include vfs.fifo_do_adaptive=0 and
vm.new_wire_method=0
- Verifying our IPC and proc kernel parameters meet the recommended
settings from the Oracle installprep.sh script (this script examines the
settings for max_per_proc_stack_size, per_proc_stack_size,
max_per_proc_data_size, per_proc_data_size, max_per_proc_address_space,
per_proc_address_space, SHMMAX, SHMMIN, SHMMNI, SHMSEG)
- Running on different systems (the problem occurs both on our production
8400 with 6 625MHz processors, 6 Gb RAM, external HSZ-80 RAID array and on
our test ES40 with 2 833 MHz processors, 4 Gb RAM, and internal RAID
controller) - the same performance problem occurs on both (I just wish
performance testing had been done by the testing team PRIOR to the
production upgrade....<sigh>)
- Running syscheck on both systems doesn't yield any significant messages
and doesn't flag any concerns in the Oracle area
- Monitoring CPU usage doesn't show significant load averages or CPU
usage, although there are many Oracle processes in a WAIT state (as shown
by top)
- Doing explain plans on the queries show they are using indices as
expected
- Recreating the indices (testing the theory they might be corrupt)
doesn't fix the problem
We have not yet verified if this same problem is affecting our other
databases on the same system - right now our only verified problems are on
the Oracle Financials application. However, the size of the database
(16Gb), tables (up to 12 million rows and 1.5 Gb for the largest), and
jobs (up to an hour runtime before this problem) are an order of magnitude
larger for this application than our other ones. As you can see, however,
the database is not particularly large. We have actually been running in
production since 11/16/2002 without any reported problems - the problems
were only reported once our monthly accounting close reports were at the
beginning of December.
There were no issues in doing the OS upgrades - we used the path of
4.0F->4.0G->5.1->5.1A->5.1A+PK3. We did NOT upgrade the AdvFS domains
(which sit under the database files) to V4 - we are still using V3.
Does anyone have any suggestions on what one might try, documentation on
possible diagnostics steps, or relevant tools to use?
Any suggestions would be appreciated. I will summarize with what works
when we resolve the problem.
Thanks,
Tom
=================================================================
Tom Ackenhusen tackenhu@fnal.gov
Business Systems Technical Services
Fermi National Accelerator Laboratory Batavia, IL, USA
This archive was generated by hypermail 2.1.7 : Sat Apr 12 2008 - 10:49:01 EDT