Re: HELP ! My ORACLE Performance Dropped

verghese kochummen wrote:
>
> Need help on this problem. Please provide any guiding clues as I have run
> out of theories of my own.
> my system's performance has dropped drastically recently. my question is as
> follows :
> 1. If my hard disk capacity is at 90% (~ 1GB free). how serious is this
> implication on performance. previously i had about 2 GB free when the
> performance was good.
> 2. i have a table that has about 28 million records and this table is quite
> heavily used for the process that i am having performance problems with. i
> was told that the table was created with a pre-allocated table space and
> that it should not affect performance so long as there is space allocated.
> My question is : I have to indexes on this table and each index uses a
> combination of 4 fields. When there are 28 million records in a table, is
> the indexing process a factor in performance drop even though there is space
> available on the disk.
>
> any guiding input will be greatly appreciated. thanks.
> jadzien.

The first question would be: what has changed recently? That is your best starting point for clues.

Filling up a disk isn't really too bad for performance -- UNLESS it is highly accessed data all on the same disk.

You need to look at I/o Contention for that device and really start checking other Oracle SGA Stats (such as cache hit rates, latch contention, sorts to disk vs memory sorts, log contention, # of redo log switches, etc).
Look at your OS performance stats -- could you be maxing out the hardware? (don't laugh -- I seen this one far too often)

Indexes can be a factor in performance -- on inserts, updates -- and even searches if they become too deep (Balance tree) or if there is a high # of deleted leaf rows in the index. You may need to rebuild them.

Have you examined the shared sql area (sys.v$sqlarea) to see what types of queries are being run and the various execution stats of them? It maybe some simple tuning is needed on your application queries.

Here is a thought: How is your Optimizer set (Rule, Cost, or Choose)? If it is set to choose or cost, maybe the statistics could be out of date (you would need to periodically re-analyze the objects in the schema to update the statistics for the cost based optimizer).

Good Luck,
darryl dB Balaski Received on Thu Feb 04 1999 - 17:04:39 CST