Re: question on dbazine article

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Sat, 28 Feb 2004 17:55:10 -0000
Message-ID: <008701c3fe24$023dace0$6702a8c0@Primary>

RE: the freelists example:

I think you have to remember that this is an article about 'rapid-response' where
it is important to do something that is
"likely to be the right thing NOW", rather than "definitely exactly the right thing in 48 hours time".

I'd guess that the adventure has also been written down from memory, rather than
from a set of notes made at the time - which would explain some of the technical inaccuracies around the edges.

Based on these two premises: the action is the most logical response to the observations made at the time, viz:

The number of concurrent users had doubled. (expert inference .. increased contention may be an issue)
"buffer busy wait" is the most significant wait event (consistent with the information that concurrency had doubled).
virtually all the DML in v$sql was "insert into customer_orders" (highly concurrent inserts are often a cause of buffer busy waits and we have just doubled the concurrency. the commonest error on systems with highly concurrent inserts is an incorrect setting of freelists).

So Don has

Been given a verbal clue

Found that the primary cause of lost time is consistent with the clue

Found that the commonest activity in the system is consistent with the clue and the lost time.

In the face of time-pressure, the most sensible option is to correct the (probable) freelist error. The change is quick to make, totally reversible in no time at all and highly likely to be a significant factor in the performance problem. Ten out of ten to DB for adopting a scientific approach that led quickly and inevitably to the correct solution.

A couple of thoughts on your experiment:

How many CPUs did you have in the system - if the answer is one, than experiments to highlight certain concurrency problems WILL produce unexpected results.

How much other activity did you have going on at the same time ? Concurrency issues become exaggerated if the available CPUs are loaded with other tasks.

How long did your test run for ?

Is it possible that the 3 second sleep produced a self-balancing effect after just a few failures. (Why not try the test again, and use dbms_random to produce a sleep time between 0.01 and 10.00 seconds to see what happens).

How many indexes did you have on the table, and how big were they before you started the test ?

Note most of your top waits are for background processes, this does make the 'small percentage' not very meaningful.

Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

  The educated person is not the person
  who can answer the questions, but the
  person who can question the answers -- T. Schick Jr

Next public appearances:
March 2004 Hotsos Symposium - The Burden of Proof Dynamic Sampling - an investigation
March 2004 Charlotte OUG (www.cltoug.org) CBO Tutorial April 2004 Iceland

One-day tutorials:
http://www.jlcomp.demon.co.uk/tutorial.html

Three-day seminar:
see http://www.jlcomp.demon.co.uk/seminar.html ____UK___February
____UK___June

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Original Message ----- > "zhu chao" <chao_ping_at_vip.163.com>

>
> http://www.dbazine.com/burleson20.shtml
> I often visit dbazine and read articlles there, on this issue, I have some
> questions:
> question to that article:
> 1. he said:
> alter system set optimizer_index_cost_adj=20;
> alter system set optimizer_index_caching=65.
> but in fact, these parameters cannot be modified online. How did he do
> that?
>
> 2.Implement cursor_sharing=force
> According to wait event based tuning, tuning something that is not the
> bottleneck does not helps much. In his case, euqueue wait and full table
> scan caused most of the problem. Would change cursor_sharing be the
> solution of his problem?
>
> 3. question about add freelists;
> He has 450 users inserting records, even if one person can insert a
> record every 3 seconds, it is only possible that there is 150 new records
> per second. Can't oracle process 150 record insert per second even if only
> 1 freelists? I did a small test with 300 concurrent session doing insert
> into a table, each insert a table after 3 second sleep. and this is the
> statspack report:( i removed the plsql locker timer event from statspack
> via modifying stats$idle_event).
> Top 5 Wait Events
> ~~~~~~~~~~~~~~~~~ Wait %
> Total
> Event Waits Time (cs) Wt
> Time
> -------------------------------------------- ------------ ------------
> -------
> log file parallel write 25,955 2,345
> 90.72
> control file parallel write 146 109
> 4.22
> db file parallel write 168 55
> 2.13
> buffer busy waits 30,761 34
> 1.32 --only a few percent of that.
> log file switch completion 4 22 .85
>
>
> Regards
> Zhu chao.
>
>
>

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Sat Feb 28 2004 - 11:52:05 CST