Re: Competition for OraPerf

"Charles Hooper" <hooperc2000_at_yahoo.com> wrote in message news:1162561951.186173.66360_at_m7g2000cwm.googlegroups.com...

> -------------------------------
> "Your average wait time for SQL*Net message from client events is 241
> milliseconds. Check your application to see if it might benefit from
> bulk collection (using PL/SQL "forall" or "bulk collect" operators. In
> addition, optimize your TNS settings in your tnsnames.ora file and
> investigate consolidating your Oracle requests into larger TNS
> packets."
> -------------------------------
> Maybe I am wrong, but SQL*Net message from client wait events are
> entirely client side wait events, possibly indicating that the client
> computer is waiting for the user to press the Any key, or actively
> processing the data returned by Oracle. PL/SQL runs server side. From
> an accuracy standpoint, should SQL*Net message from client wait events
> even be interpretted from a Statspack report? The events may have
> taken place immediately before or immediately after the database
> instance processing activity that was of concern. Would you
> tune/optimize settings in the tnsnames.ora fie to fix SQL*Net message
> from client wait events? Probably not.

Over a 2-hour period, an average of 241 milliseconds for SQL*Net message from client events tends to suggest a client program performing a massive load one row at a time. This does suggest the need for array processing, but as you say, pl/sql is (barring Forms) serverside. I think you would only look at SQL*Net tuning if you were seeing lots of 'more data' from or to client - because there might be some mileage in a larger TDU or SDU.

>
> -------------------------------
>
> "You have excessive buffer busy waits with 3.1 per transaction. Buffer
> busy waits are most commonly caused by segment header contention and
> can be remedied by increasing the value of the tables & index freelists
> or freelist_groups parameters, tuning your database writer (DBWR
> process, or by using Automatic Segment Storage Management (ASSM) in the
> tablespace definition. Using super-fast SSD will also reduce buffer
> busy waits because transactions are completed many times faster."
> -------------------------------
>
> Without examining V$WAITSTAT, you could very well be throwing
> hardware/disk storage at a problem that is caused by contention for the
> same block that is already in memory - this hardware/disk storage
> improvement may adversely affect the performance of a critical
> application process, if it removes the bottleneck that prevented a less
> critical application process from consuming a greater percentage of
> system resources (Cary Millsap discusses this in his tuning book, and
> it seems as though it was also mentioned in Tales of the Oak Table).
>

And in this particular case, given the constant table-scanning going on, the buffer busy waits might simply be the result of sessions waiting for other sessions to finish a scattered read. So in a small sense, the suggestion of buying SSD is correct - it might reduce the wait times for other reads: but (a) it would probably be cheaper to stop doing the excess tablescans, and (b) the reason given for buying the SSD is not the right reason. Received on Fri Nov 03 2006 - 08:18:23 CST