Re: Oracle - solving performance problem

Perhaps call in a consultant such as Jonathan Lewis?

As Sybrand already mentioned tuning the app sql code is your best bet.

other ideas include:

Are your database statistics current? How are they being gathered?

select owner, trunc(last_analyzed), count(1) from all_tables
group by owner, trunc(last_analyzed)
/

Investigate the use of partitioning so as to reduce the number of blocks fetched (aim for getting partition view elimination working). This is a separately licensed option above and beyond enterprise edition.

Investigate the use of parallel query option so as to parallelize large operations to possibly shorten their duration (probably no large gains possible due to existing number of CPUs) (enterprise edition required).

Investigate the use of materialized views for aggregation of summary data.

Investigate the use of analytic functions in the application SQL
(available in standard edition).

You didn't mention the filesystems used to support the datafiles
(extfs3, 4096 byte block size). Did you configure large file settings
when you ran mkfs?
What is the block size used for the app tablespaces? There is a paper on hotsos that covers "aligning database block and filesystem block sizes". Your file io might be very inefficient due to misalignment.

How much sorting is taking place to temp tablespaces?
(find this via statspack)

Have you super-sized your pga?
(joke)

Get a baseline of what is going on from within the database and from within the operating system. Install and configure oracle's "statspack" utility and schedule some snapshots (level 7 is a good start). generate a statspack report and upload it to oraperf.com.

hth. good luck.

-bdbafh Received on Fri Oct 07 2005 - 17:14:27 CDT