| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: question on dbazine article
According to your statspack report, the major wait is "log file parallel
write" event. This has nothing to do with freelists, it just states that
your IO for redologs is too slow. Put the redologs on separate, faster
disks, preferrably on raw devices. If you continue to see waits on log file
parallel write event, then either reduce the amount of redo generated or add
more disks for redologs and stripe them (into two groups if you're in
archivelog mode).
Now, when you've eliminated the redo waits, you'll probably start seeing more buffer busy waits on the table blocks where rows are inserted. You can verify this by checking p1 & p2 for finding buffers waited on in v$session_wait. If these buffers are regular data blocks, then increasing process freelists will help (the inserts will be spread to different sets of blocks). But if you continue to see buffer busy waits, but on segment header block, then it means that the freelists are updated (blocks added to) that often that the segment header block containing all freelists is becoming the bottleneck of contention. In a freelist managed segment you can relieve this issue by having multiple freelist groups, but this requires rebuilding of the segment. As an alternative, you could increase the value of _bump_high_water_mark_count parameter, which will allocate more blocks to freelists in time, thus causing less contention on segment header or freelist group blocks.
Tanel.
> 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
> ----------------------------------------- -------- ----------------------
--Received on Mon Mar 01 2004 - 04:32:48 CST
> ----
> 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_at_jlcomp.demon.co.uk>
> To: <oracle-l_at_freelists.org>
> 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_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
> > -----------------------------------------------------------------
> >
> >
>
> ----------------------------------------------------------------
> 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
> -----------------------------------------------------------------
> ---------------------------------------------------------------- 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 -----------------------------------------------------------------
 |
 |