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@(protected)>
>
> 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@(protected)
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
-----------------------------------------------------------------
