On Fri, 11 Apr 2003 22:32:35 +0200, Rick Denoire wrote:
> "Juergen Lueters" <jlueters_at_intranet-engineering.de> wrote:
>
>>
>>Use statspack (as someone has metionend earlier.). With statspack you
>>can identify the exact resource consumption for every sql statement.
>>Might be a locking problem, statspack will show you everything.
>
> I don't know what statspack would help if the whole bunch of metrics
> collected with the Performance Monitor (OEM Diagnostics Pack) did not
> help either, including wait events, wait time, file based I/O
> statistics, locks, etc.
>
> Oracle has already been continuously optimized during the last two
> years or so. I don't blame the DB performance per se. This is just a
> suitable example for me to show that there is a performance problem
> with I/O, but rather on the host side. There have been other cases,
> not DB related, with overwhelming evidences of such a problem. But
> every time I check the fundamentals, I get no further than confirming
> the problem using the usual tools. No word about how to actually SOLVE
> the problem (except the usual advice to distribute I/O operations
> etc.).
>
> Bye
I prefer statspack because it is a batch tool and can take snapshots at
well defined intervals.
1 % buffer hit rate is really poor and may be the cause of the
problem. According to my experience full table scan has nothing to do with
raid layout or disk performance.
Why does the database do a full table scan?
1. application uses role based optimizer and a poorly optimized sql-statement
2. statistical optimizer enabled and statistics are not up to date.
3. statistical optimizer enabled, but application believes that rule based
optimizer is in effect.
4. maybe full table scan is really the most effiencient way to do the job.
In this case inrease the database block buffer.
5. what about db and filesystem block size
6. large varchar or char fields are used as primary and secondary keys and
therfore causing a lot of i/o in order to keep the references up to date
7. make sure that asyncio is enabled. Increase the number of database
writers as alternative
8. what about partinioning the table?
9. do you have enough cursors?
Regards
Juergen
Received on Fri Apr 11 2003 - 17:48:55 CDT
