Re: CPU waiting for... what? (mistery)

From: Juergen Lueters <jlueters_at_intranet-engineering.de>
Date: Sat, 12 Apr 2003 00:56:40 +0200
Message-ID: <pan.2003.04.11.22.56.35.400321@intranet-engineering.de>

On Sat, 12 Apr 2003 00:48:55 +0200, Juergen Lueters wrote:

> 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

How fast do the redologs get switched? (alertlog). With every redolog switch all the buffers are flushed and the database is almost on a halt during that time.

Juergen Received on Fri Apr 11 2003 - 17:56:40 CDT