Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: question on dbazine article

Re: question on dbazine article

From: zhu chao <>
Date: Fri, 27 Feb 2004 13:22:45 +0800
Message-ID: <016601c3fcf1$bc7555b0$0207a8c0@chaos>


    In 10g it does work ,but he said it was 9.0.2(there is only 9.2.0 and 9.0.1 in fact), both version cannot change it dynamically. using scope=spfile is different from without using it, as in fact it does not change the parameter until the instance is bounced.     Hope Donald can give us more detailed technical information.


  In 10g alter system works as well.


    Perhaps he meant to change the parameter at the session level. Also your Oracle version may come into play here also.

      1* alter system set optimizer_index_caching = 85 scope = spfile
@UT1 SQL>> /
    System altered.

@UT1 SQL>> alter session set optimizer_index_caching = 85;

    Session altered.

    Test ran on AIX 4.3.3

      -----Original Message-----
      From: []On Behalf Of zhu chao
      Sent: Thursday, February 26, 2004 8:16 AM
      Cc: Yong Huang
      Subject: question on dbazine article
      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

      Zhu chao.

Please see the official ORACLE-L FAQ:

To unsubscribe send email to: put 'unsubscribe' in the subject line.
Archives are at
FAQ is at
Received on Thu Feb 26 2004 - 23:20:13 CST

Original text of this message