Re: question on dbazine article
- From: "zhu chao" <chao_ping@xxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 1 Mar 2004 17:50:16 +0800
Hi, Jonathan:
I will do another test according to your suggestions. My test is not
precise.
My server is a SUNFire 880 with 8CPu/16G memory, an empty table with
no data, no index.The test has been running for 1 day and now the table has
23M records.
SQL> desc testinsert
Name Null? Type
----------------------------------------- -------- ------------------------
----
ID NUMBER
REC CHAR(100)
and now the statspack report look like:
Top 5 Wait Events
~~~~~~~~~~~~~~~~~ Wait %
Total
Event Waits Time (cs) Wt
Time
-------------------------------------------- ------------ ------------ -----
--
log file parallel write 7,708 1,314
58.12
buffer busy waits 36,537 679
30.03
latch free 56 74
3.27
control file parallel write 92 65
2.87
enqueue 4,857 62
2.74
it seems that the number of records in the table does has big influce on the
insert.
Thanks for your idear, I know more about buffer busy wait. I will modify the
table with more list and check if buffer busy wait does drop down
significantly, it seems it will.
Thanks
Regards
Zhu Chao
----- Original Message -----
From: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
Sent: Sunday, February 29, 2004 1:55 AM
Subject: 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 //www.freelists.org/archives/oracle-l/
> FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
> -----------------------------------------------------------------
>
>
----------------------------------------------------------------
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 //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
