From oracle-l-bounce@freelists.org Thu Feb 26 12:49:49 2004 Return-Path: Received: from air189.startdedicated.com (root@localhost) by orafaq.com (8.11.6/8.11.6) with ESMTP id i1QInnP25695 for ; Thu, 26 Feb 2004 12:49:49 -0600 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air189.startdedicated.com (8.11.6/8.11.6) with ESMTP id i1QInmo25689 for ; Thu, 26 Feb 2004 12:49:48 -0600 Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 1F343396561; Thu, 26 Feb 2004 13:51:05 -0500 (EST) Received: with ECARTIS (v1.0.0; list oracle-l); Thu, 26 Feb 2004 13:49:53 -0500 (EST) X-Original-To: oracle-l@freelists.org Delivered-To: oracle-l@freelists.org Received: from mx10.radisys.com (mx10.radisys.com [206.102.10.36]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 0DF12394FA4 for ; Thu, 26 Feb 2004 13:49:51 -0500 (EST) Received: by mx10.radisys.com (Postfix, from userid 5) id F2751142DFA; Thu, 26 Feb 2004 10:52:46 -0800 (PST) Received: from UNKNOWN(206.103.52.194), claiming to be "dcblackberry.radisys.com" via SMTP by mx10, id smtpdAAA0AQUce; Thu Feb 26 10:52:44 2004 To: oracle-l@freelists.org Subject: Re: question on dbazine article MIME-Version: 1.0 X-Mailer: Lotus Notes Release 5.0.10 March 22, 2002 Message-ID: From: Jared.Still@radisys.com Date: Thu, 26 Feb 2004 10:52:40 -0800 X-MIMETrack: Serialize by Router on DCBlackBerry/Radisys_Corporation/US(Release 5.0.12 |February 13, 2003) at 02/26/2004 10:49:54 AM, Serialize complete at 02/26/2004 10:49:54 AM Content-Type: multipart/alternative; boundary="=_alternative 00679EAE88256E46_=" X-archive-position: 2142 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: Jared.Still@radisys.com Precedence: normal Reply-To: oracle-l@freelists.org X-list: oracle-l --=_alternative 00679EAE88256E46_= Content-Type: text/plain; charset="us-ascii" Re the freelists: there isn't enough information provided in the article to do anything but guess as to why it worked. My guess is that the add'l freelists on the index gave some relief due to a monotonically incrementing key. Jared "zhu chao" Sent by: oracle-l-bounce@freelists.org 02/26/2004 05:16 AM Please respond to oracle-l To: cc: "Yong Huang" Subject: question on dbazine article 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. --=_alternative 00679EAE88256E46_= Content-Type: text/html; charset="us-ascii"
Re the freelists:  there isn't enough information provided in the article to do
anything but guess as to why it worked.

My guess is that the add'l freelists on the index gave some relief due to
a monotonically incrementing key.

Jared




"zhu chao" <chao_ping@vip.163.com>
Sent by: oracle-l-bounce@freelists.org

 02/26/2004 05:16 AM
 Please respond to oracle-l

       
        To:        <oracle-l@freelists.org>
        cc:        "Yong Huang" <yong321@yahoo.com>
        Subject:        question on dbazine article



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.
 

--=_alternative 00679EAE88256E46_=-- ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@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 -----------------------------------------------------------------