Re: question on dbazine article


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:

a)    The number of concurrent users had doubled.
    (expert inference .. increased contention may be an issue)

b)    "buffer busy wait" is the most significant wait
    event (consistent with the information that concurrency
    had doubled).

c)    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@xxxxxxxxxxx>

>
> 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@xxxxxxxxxxxxx
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
-----------------------------------------------------------------

Other related posts: