FAQ
List,
i have two heavily inserted tables, the structures are same.
currently these tables reside on separate disks, can i increase the
performance
of inserts if i create these tables in a cluster ? as a cluster would force
the rows of both the tables
to be physically close on the disk !

regards
-rahul

Ora 7.3 on AIX

--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Rahul
INET: rahul_at_ratelindo.co.id

Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message

to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).

Search Discussions

  • Stephane Faroult at Oct 25, 2002 at 1:04 pm

    Rahul wrote:

    List,
    i have two heavily inserted tables, the structures are same.
    currently these tables reside on separate disks, can i increase the
    performance
    of inserts if i create these tables in a cluster ? as a cluster would force
    the rows of both the tables
    to be physically close on the disk !

    regards
    -rahul

    Ora 7.3 on AIX
    You would also increase contention ... I'd rather try to augment the
    number of free lists, and, if you are lucky enough not to access your
    indexes in RANGE SCAN mode, to create them as REVERSE.
    Beware of indexes, by the way, each additional index costs about 2.5
    times the cost of inserting into a non-indexed table (in terms of
    logical blocks).

    --
    Regards,

    Stephane Faroult
    Oriole Software
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Stephane Faroult
    INET: sfaroult_at_oriole.com

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
  • Tim Gorman at Oct 25, 2002 at 2:13 pm
    Rahul,

    It does no good to speculate; let's work with facts...

    What wait-events are occurring in the sessions running the INSERTs? If you
    can locate the sessions in the V$SESSION view, then use the value in the
    column SID to locate associated rows in the V$SESSION_EVENT view, sorting by
    the cumulative time spent on each wait-event:

    select event, time_waited, average_time, max_time
    from v$session_event
    where sid = &&SID
    union
    select n.name, s.value, 0, 0
    from v$sesstat s, v$statname n
    where s.sid = &&SID
    and n.name in ('CPU used by this session','parse time cpu','recursive
    cpu usage')

    and s.statistic# = n.statistic#
    order by 2 desc

    As you can see, in addition to wait-event information, this query will also
    mix in CPU statistics from the V$SESSTAT view, to give a better picture of
    where time is being spent by these sessions...

    Can you post the results of these queries back to the list?

    Thanks!

    -Tim

    Original Message -----
    To: "Multiple recipients of list ORACLE-L"
    Sent: Friday, October 25, 2002 6:18 AM
    List,
    i have two heavily inserted tables, the structures are same.
    currently these tables reside on separate disks, can i increase the
    performance
    of inserts if i create these tables in a cluster ? as a cluster would force
    the rows of both the tables
    to be physically close on the disk !

    regards
    -rahul

    Ora 7.3 on AIX



    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Rahul
    INET: rahul_at_ratelindo.co.id

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Tim Gorman
    INET: Tim_at_SageLogix.com

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
  • DENNIS WILLIAMS at Oct 25, 2002 at 6:14 pm
    Stephane

    You mentioned "each additional index costs about 2.5 times the cost of
    inserting into a non-indexed table". I just wanted to point out that Kevin
    Loney has done some performance tests involving the number of indexes. I
    don't know if he has published these anywhere. In a nutshell, the results
    were that a single index really hurts insert performance, and each
    additional index increases the hurt, but by a decreasing amount. The
    conclusions were:

    If you can drop all indexes, that will really help inserts.
    If you have one index, adding a second index will really hurt, but not
    as bad.
    If the table already has 15 indexes, adding one more index probably
    won't be noticed.

    Dennis Williams
    DBA, 40%OCP

    Lifetouch, Inc.
    dwilliams_at_lifetouch.com

    -----Original Message-----
    Sent: Friday, October 25, 2002 8:04 AM
    To: Multiple recipients of list ORACLE-L

    Rahul wrote:
    List,
    i have two heavily inserted tables, the structures are same.
    currently these tables reside on separate disks, can i increase the
    performance
    of inserts if i create these tables in a cluster ? as a cluster would force
    the rows of both the tables
    to be physically close on the disk !

    regards
    -rahul

    Ora 7.3 on AIX
    You would also increase contention ... I'd rather try to augment the
    number of free lists, and, if you are lucky enough not to access your
    indexes in RANGE SCAN mode, to create them as REVERSE.
    Beware of indexes, by the way, each additional index costs about 2.5
    times the cost of inserting into a non-indexed table (in terms of
    logical blocks).

    --
    Regards,

    Stephane Faroult
    Oriole Software
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Stephane Faroult
    INET: sfaroult_at_oriole.com

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: DENNIS WILLIAMS
    INET: DWILLIAMS_at_LIFETOUCH.COM

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
  • Rahul at Oct 26, 2002 at 7:33 am
    the DB is *not* experiencing any waits... i'm trying to bring down the run
    time of the
    insertion process, currently it takes around 9 hrs... the management wants
    to bring it
    down to 5-6 hrs...again.. i OD NOT see any wait events while the process is
    running..

    there are no indexes on the tables
    ----------
    From: Tim Gorman[SMTP:Tim_at_SageLogix.com]
    Reply To: ORACLE-L_at_fatcity.com
    Sent: Friday, October 25, 2002 9:13 PM
    To: Multiple recipients of list ORACLE-L
    Subject: Re: can clustering help INSERTS ?

    Rahul,

    It does no good to speculate; let's work with facts...

    What wait-events are occurring in the sessions running the INSERTs? If
    you
    can locate the sessions in the V$SESSION view, then use the value in the
    column SID to locate associated rows in the V$SESSION_EVENT view, sorting
    by
    the cumulative time spent on each wait-event:

    select event, time_waited, average_time, max_time
    from v$session_event
    where sid = &&SID
    union
    select n.name, s.value, 0, 0
    from v$sesstat s, v$statname n
    where s.sid = &&SID
    and n.name in ('CPU used by this session','parse time
    cpu','recursive
    cpu usage')
    and s.statistic# = n.statistic#
    order by 2 desc

    As you can see, in addition to wait-event information, this query will
    also
    mix in CPU statistics from the V$SESSTAT view, to give a better picture of
    where time is being spent by these sessions...

    Can you post the results of these queries back to the list?

    Thanks!

    -Tim

    ----- Original Message -----
    To: "Multiple recipients of list ORACLE-L"
    Sent: Friday, October 25, 2002 6:18 AM

    List,
    i have two heavily inserted tables, the structures are same.
    currently these tables reside on separate disks, can i increase the
    performance
    of inserts if i create these tables in a cluster ? as a cluster would force
    the rows of both the tables
    to be physically close on the disk !

    regards
    -rahul

    Ora 7.3 on AIX



    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Rahul
    INET: rahul_at_ratelindo.co.id

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Tim Gorman
    INET: Tim_at_SageLogix.com

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Rahul
    INET: rahul_at_ratelindo.co.id

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
  • Naveen Nahata at Oct 26, 2002 at 8:43 am
    Dennis,

    Did he publish any figures? I mean, it seems common-sense that adding the
    first index will hurt but adding 11th index to a table won't hurt that much.

    As stephane pointed out, cost of an index is 2.5 times more than the cost of
    insert in a non-indexed table.

    So assuming cost is 1, than cost with 1 index will be 3.5 as 250% increase.

    Cost with 5 indexes should be 13.5 and cost with 6 indexes will be 16 less
    than twenty percent increase.

    Since the addition cost is constant for every index added, the percentage
    increase in cost (and also maybe time) will be lower and lower.

    Am I right or missing something?

    Regards
    Naveen

    -----Original Message-----
    Sent: Friday, October 25, 2002 11:45 PM
    To: Multiple recipients of list ORACLE-L

    Stephane

    You mentioned "each additional index costs about 2.5 times the cost of
    inserting into a non-indexed table". I just wanted to point out that Kevin
    Loney has done some performance tests involving the number of indexes. I
    don't know if he has published these anywhere. In a nutshell, the results
    were that a single index really hurts insert performance, and each
    additional index increases the hurt, but by a decreasing amount. The
    conclusions were:

    If you can drop all indexes, that will really help inserts.
    If you have one index, adding a second index will really hurt, but not
    as bad.
    If the table already has 15 indexes, adding one more index probably
    won't be noticed.

    Dennis Williams
    DBA, 40%OCP

    Lifetouch, Inc.
    dwilliams_at_lifetouch.com

    -----Original Message-----
    Sent: Friday, October 25, 2002 8:04 AM
    To: Multiple recipients of list ORACLE-L

    Rahul wrote:
    List,
    i have two heavily inserted tables, the structures are same.
    currently these tables reside on separate disks, can i increase the
    performance
    of inserts if i create these tables in a cluster ? as a cluster would force
    the rows of both the tables
    to be physically close on the disk !

    regards
    -rahul

    Ora 7.3 on AIX
    You would also increase contention ... I'd rather try to augment the
    number of free lists, and, if you are lucky enough not to access your
    indexes in RANGE SCAN mode, to create them as REVERSE.
    Beware of indexes, by the way, each additional index costs about 2.5
    times the cost of inserting into a non-indexed table (in terms of
    logical blocks).

    --
    Regards,

    Stephane Faroult
    Oriole Software
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Stephane Faroult
    INET: sfaroult_at_oriole.com

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: DENNIS WILLIAMS
    INET: DWILLIAMS_at_LIFETOUCH.COM

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Naveen Nahata
    INET: naveen_nahata_at_mindtree.com

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
  • Tim Gorman at Oct 26, 2002 at 3:18 pm
    Can you display to the list the output from one of the queries, just for
    fun?

    Original Message -----
    To: "Multiple recipients of list ORACLE-L"
    Sent: Saturday, October 26, 2002 1:33 AM
    the DB is *not* experiencing any waits... i'm trying to bring down the run
    time of the
    insertion process, currently it takes around 9 hrs... the management wants
    to bring it
    down to 5-6 hrs...again.. i OD NOT see any wait events while the process is
    running..

    there are no indexes on the tables
    ----------
    From: Tim Gorman[SMTP:Tim_at_SageLogix.com]
    Reply To: ORACLE-L_at_fatcity.com
    Sent: Friday, October 25, 2002 9:13 PM
    To: Multiple recipients of list ORACLE-L
    Subject: Re: can clustering help INSERTS ?

    Rahul,

    It does no good to speculate; let's work with facts...

    What wait-events are occurring in the sessions running the INSERTs? If
    you
    can locate the sessions in the V$SESSION view, then use the value in the
    column SID to locate associated rows in the V$SESSION_EVENT view,
    sorting
    by
    the cumulative time spent on each wait-event:

    select event, time_waited, average_time, max_time
    from v$session_event
    where sid = &&SID
    union
    select n.name, s.value, 0, 0
    from v$sesstat s, v$statname n
    where s.sid = &&SID
    and n.name in ('CPU used by this session','parse time
    cpu','recursive
    cpu usage')
    and s.statistic# = n.statistic#
    order by 2 desc

    As you can see, in addition to wait-event information, this query will
    also
    mix in CPU statistics from the V$SESSTAT view, to give a better picture
    of
    where time is being spent by these sessions...

    Can you post the results of these queries back to the list?

    Thanks!

    -Tim

    ----- Original Message -----
    To: "Multiple recipients of list ORACLE-L"
    Sent: Friday, October 25, 2002 6:18 AM

    List,
    i have two heavily inserted tables, the structures are same.
    currently these tables reside on separate disks, can i increase the
    performance
    of inserts if i create these tables in a cluster ? as a cluster would force
    the rows of both the tables
    to be physically close on the disk !

    regards
    -rahul

    Ora 7.3 on AIX



    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Rahul
    INET: rahul_at_ratelindo.co.id

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Tim Gorman
    INET: Tim_at_SageLogix.com

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Rahul
    INET: rahul_at_ratelindo.co.id

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Tim Gorman
    INET: Tim_at_SageLogix.com

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
  • Naveen Nahata at Oct 26, 2002 at 4:03 pm
    Is timed_statistics set to true?

    Regards
    Naveen

    -----Original Message-----
    Sent: Saturday, October 26, 2002 8:48 PM
    To: Multiple recipients of list ORACLE-L

    Can you display to the list the output from one of the queries, just for
    fun?

    Original Message -----
    To: "Multiple recipients of list ORACLE-L"
    Sent: Saturday, October 26, 2002 1:33 AM
    the DB is *not* experiencing any waits... i'm trying to bring down the run
    time of the
    insertion process, currently it takes around 9 hrs... the management wants
    to bring it
    down to 5-6 hrs...again.. i OD NOT see any wait events while the process is
    running..

    there are no indexes on the tables
    ----------
    From: Tim Gorman[SMTP:Tim_at_SageLogix.com]
    Reply To: ORACLE-L_at_fatcity.com
    Sent: Friday, October 25, 2002 9:13 PM
    To: Multiple recipients of list ORACLE-L
    Subject: Re: can clustering help INSERTS ?

    Rahul,

    It does no good to speculate; let's work with facts...

    What wait-events are occurring in the sessions running the INSERTs? If
    you
    can locate the sessions in the V$SESSION view, then use the value in the
    column SID to locate associated rows in the V$SESSION_EVENT view,
    sorting
    by
    the cumulative time spent on each wait-event:

    select event, time_waited, average_time, max_time
    from v$session_event
    where sid = &&SID
    union
    select n.name, s.value, 0, 0
    from v$sesstat s, v$statname n
    where s.sid = &&SID
    and n.name in ('CPU used by this session','parse time
    cpu','recursive
    cpu usage')
    and s.statistic# = n.statistic#
    order by 2 desc

    As you can see, in addition to wait-event information, this query will
    also
    mix in CPU statistics from the V$SESSTAT view, to give a better picture
    of
    where time is being spent by these sessions...

    Can you post the results of these queries back to the list?

    Thanks!

    -Tim

    ----- Original Message -----
    To: "Multiple recipients of list ORACLE-L"
    Sent: Friday, October 25, 2002 6:18 AM

    List,
    i have two heavily inserted tables, the structures are same.
    currently these tables reside on separate disks, can i increase the
    performance
    of inserts if i create these tables in a cluster ? as a cluster would force
    the rows of both the tables
    to be physically close on the disk !

    regards
    -rahul

    Ora 7.3 on AIX



    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Rahul
    INET: rahul_at_ratelindo.co.id

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Tim Gorman
    INET: Tim_at_SageLogix.com

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Rahul
    INET: rahul_at_ratelindo.co.id

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Tim Gorman
    INET: Tim_at_SageLogix.com

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Naveen Nahata
    INET: naveen_nahata_at_mindtree.com

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
  • Jared Still at Oct 27, 2002 at 4:53 am
    *no* waits?

    How is this possible?

    Is intantaneous computing now a reality?

    Sorry for the sarcasm. Wait, no, not really.;)

    Seriously, all databases wait, all operations take
    time. The question on everyone's lips is
    'How long are *yours* taking?'

    Jared
    On Saturday 26 October 2002 00:33, Rahul wrote:
    the DB is *not* experiencing any waits... i'm trying to bring down the run
    time of the
    insertion process, currently it takes around 9 hrs... the management wants
    to bring it
    down to 5-6 hrs...again.. i OD NOT see any wait events while the process is
    running..

    there are no indexes on the tables
    ----------
    From: Tim Gorman[SMTP:Tim_at_SageLogix.com]
    Reply To: ORACLE-L_at_fatcity.com
    Sent: Friday, October 25, 2002 9:13 PM
    To: Multiple recipients of list ORACLE-L
    Subject: Re: can clustering help INSERTS ?

    Rahul,

    It does no good to speculate; let's work with facts...

    What wait-events are occurring in the sessions running the INSERTs? If
    you
    can locate the sessions in the V$SESSION view, then use the value in the
    column SID to locate associated rows in the V$SESSION_EVENT view, sorting
    by
    the cumulative time spent on each wait-event:

    select event, time_waited, average_time, max_time
    from v$session_event
    where sid = &&SID
    union
    select n.name, s.value, 0, 0
    from v$sesstat s, v$statname n
    where s.sid = &&SID
    and n.name in ('CPU used by this session','parse time
    cpu','recursive
    cpu usage')
    and s.statistic# = n.statistic#
    order by 2 desc

    As you can see, in addition to wait-event information, this query will
    also
    mix in CPU statistics from the V$SESSTAT view, to give a better picture
    of where time is being spent by these sessions...

    Can you post the results of these queries back to the list?

    Thanks!

    -Tim

    ----- Original Message -----
    To: "Multiple recipients of list ORACLE-L"
    Sent: Friday, October 25, 2002 6:18 AM
    List,
    i have two heavily inserted tables, the structures are same.
    currently these tables reside on separate disks, can i increase the
    performance
    of inserts if i create these tables in a cluster ? as a cluster would force
    the rows of both the tables
    to be physically close on the disk !

    regards
    -rahul

    Ora 7.3 on AIX



    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Rahul
    INET: rahul_at_ratelindo.co.id

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Tim Gorman
    INET: Tim_at_SageLogix.com

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Jared Still
    INET: jkstill_at_cybcon.com

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
  • Tim Gorman at Oct 27, 2002 at 2:48 pm
    Someone else cracked it -- he doesn't have TIMED_STATISTICS = TRUE...

    Original Message -----
    To: "Multiple recipients of list ORACLE-L"
    Sent: Saturday, October 26, 2002 9:53 PM
    *no* waits?

    How is this possible?

    Is intantaneous computing now a reality?

    Sorry for the sarcasm. Wait, no, not really.;)

    Seriously, all databases wait, all operations take
    time. The question on everyone's lips is
    'How long are *yours* taking?'

    Jared
    On Saturday 26 October 2002 00:33, Rahul wrote:
    the DB is *not* experiencing any waits... i'm trying to bring down the
    run
    time of the
    insertion process, currently it takes around 9 hrs... the management
    wants
    to bring it
    down to 5-6 hrs...again.. i OD NOT see any wait events while the process
    is
    running..

    there are no indexes on the tables
    ----------
    From: Tim Gorman[SMTP:Tim_at_SageLogix.com]
    Reply To: ORACLE-L_at_fatcity.com
    Sent: Friday, October 25, 2002 9:13 PM
    To: Multiple recipients of list ORACLE-L
    Subject: Re: can clustering help INSERTS ?

    Rahul,

    It does no good to speculate; let's work with facts...

    What wait-events are occurring in the sessions running the INSERTs?
    If
    you
    can locate the sessions in the V$SESSION view, then use the value in
    the
    column SID to locate associated rows in the V$SESSION_EVENT view,
    sorting
    by
    the cumulative time spent on each wait-event:

    select event, time_waited, average_time, max_time
    from v$session_event
    where sid = &&SID
    union
    select n.name, s.value, 0, 0
    from v$sesstat s, v$statname n
    where s.sid = &&SID
    and n.name in ('CPU used by this session','parse time
    cpu','recursive
    cpu usage')
    and s.statistic# = n.statistic#
    order by 2 desc

    As you can see, in addition to wait-event information, this query will
    also
    mix in CPU statistics from the V$SESSTAT view, to give a better
    picture
    of where time is being spent by these sessions...

    Can you post the results of these queries back to the list?

    Thanks!

    -Tim

    ----- Original Message -----
    To: "Multiple recipients of list ORACLE-L"
    Sent: Friday, October 25, 2002 6:18 AM
    List,
    i have two heavily inserted tables, the structures are same.
    currently these tables reside on separate disks, can i increase the
    performance
    of inserts if i create these tables in a cluster ? as a cluster
    would
    force
    the rows of both the tables
    to be physically close on the disk !

    regards
    -rahul

    Ora 7.3 on AIX



    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Rahul
    INET: rahul_at_ratelindo.co.id

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting
    services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Tim Gorman
    INET: Tim_at_SageLogix.com

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
    -------------------------------------------------------
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Jared Still
    INET: jkstill_at_cybcon.com

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Tim Gorman
    INET: Tim_at_SageLogix.com

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
  • Stephane Faroult at Oct 27, 2002 at 3:33 pm

    DENNIS WILLIAMS wrote:

    Stephane
    You mentioned "each additional index costs about 2.5 times the cost of
    inserting into a non-indexed table". I just wanted to point out that Kevin
    Loney has done some performance tests involving the number of indexes. I
    don't know if he has published these anywhere. In a nutshell, the results
    were that a single index really hurts insert performance, and each
    additional index increases the hurt, but by a decreasing amount. The
    conclusions were:
    - If you can drop all indexes, that will really help inserts.
    - If you have one index, adding a second index will really hurt, but not
    as bad.
    - If the table already has 15 indexes, adding one more index probably
    won't be noticed.

    Dennis Williams
    DBA, 40%OCP
    Lifetouch, Inc.
    dwilliams_at_lifetouch.com
    Dennis,

    I have also benched it, and what I gave are my results. Note that we
    totally agree in _relative_ terms. If you have 15 indexes, I estimate
    the cost to be about 100 + 15 * 250, so in truth at this stage the cost
    of an index is about 7% ...
    Note also that the costs I gave are relative to the number of logical
    reads. I have met a number of cases when a significant increase in
    logical reads was hardly noticeable in terms of elapsed time.

    --
    Regards,

    Stephane Faroult
    Oriole Software
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Stephane Faroult
    INET: sfaroult_at_oriole.com

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
  • DENNIS WILLIAMS at Oct 27, 2002 at 6:38 pm
    Naveen -

    He provided figures, and they are on the handout that is somewhere in my
    office. Maybe I'll run across it someday, or even better, maybe he'll
    publish his results. Here are a few more details that I posted to this list
    earlier.

    Kevin Loney (author of Oracle DBA Handbook) has performed index
    performance tests and presented a paper at our Twin Cities Oracle User's
    Group (http://www.tcoug.org). I don't know if his paper is on that site or
    if Kevin has posted it somewhere or if he will included his findings in a
    future book.

    His results (from memory) was that there weren't any big surprises. Say
    it takes 1 hour to load a table with no indexes on it. If you put 1 index on
    that table, load time will increase about 20% to maybe 1hr 12 minutes
    (depending on how many columns are indexed, etc.). If we add a second index,
    load time will again increase, but by a smaller amount than for the first,
    maybe to 1 hr. 23 minutes. And so it goes. By the time we reach 20 indexes,
    adding a 21st index may add only 3 or 4 minutes to our load time. There
    didn't seem to be any point where adding one more index would throw load
    times into a black hole and double load times or something like that.

    Kevin also tested whether the size of the index mattered. There were
    points where say, the 100,000th row caused index performance to suddenly
    drop, probably due to factors like adding a newer blevel. However it was
    almost impossible to predict this point ahead of time.

    My conclusions:

    Dropping indexes speeds inserts.
    If you have a single index on a table, adding a second index is
    costly.
    If the table already has 20 indexes, one more isn't going to have a
    noticeable effect.

    Dennis Williams
    DBA, 40%OCP

    Lifetouch, Inc.
    dwilliams_at_lifetouch.com

    -----Original Message-----
    Sent: Saturday, October 26, 2002 3:43 AM
    To: Multiple recipients of list ORACLE-L

    Dennis,

    Did he publish any figures? I mean, it seems common-sense that adding the
    first index will hurt but adding 11th index to a table won't hurt that much.

    As stephane pointed out, cost of an index is 2.5 times more than the cost of
    insert in a non-indexed table.

    So assuming cost is 1, than cost with 1 index will be 3.5 as 250% increase.

    Cost with 5 indexes should be 13.5 and cost with 6 indexes will be 16 less
    than twenty percent increase.

    Since the addition cost is constant for every index added, the percentage
    increase in cost (and also maybe time) will be lower and lower.

    Am I right or missing something?

    Regards
    Naveen

    -----Original Message-----
    Sent: Friday, October 25, 2002 11:45 PM
    To: Multiple recipients of list ORACLE-L

    Stephane

    You mentioned "each additional index costs about 2.5 times the cost of
    inserting into a non-indexed table". I just wanted to point out that Kevin
    Loney has done some performance tests involving the number of indexes. I
    don't know if he has published these anywhere. In a nutshell, the results
    were that a single index really hurts insert performance, and each
    additional index increases the hurt, but by a decreasing amount. The
    conclusions were:

    If you can drop all indexes, that will really help inserts.
    If you have one index, adding a second index will really hurt, but not
    as bad.
    If the table already has 15 indexes, adding one more index probably
    won't be noticed.

    Dennis Williams
    DBA, 40%OCP

    Lifetouch, Inc.
    dwilliams_at_lifetouch.com

    -----Original Message-----
    Sent: Friday, October 25, 2002 8:04 AM
    To: Multiple recipients of list ORACLE-L

    Rahul wrote:
    List,
    i have two heavily inserted tables, the structures are same.
    currently these tables reside on separate disks, can i increase the
    performance
    of inserts if i create these tables in a cluster ? as a cluster would force
    the rows of both the tables
    to be physically close on the disk !

    regards
    -rahul

    Ora 7.3 on AIX
    You would also increase contention ... I'd rather try to augment the
    number of free lists, and, if you are lucky enough not to access your
    indexes in RANGE SCAN mode, to create them as REVERSE.
    Beware of indexes, by the way, each additional index costs about 2.5
    times the cost of inserting into a non-indexed table (in terms of
    logical blocks).

    --
    Regards,

    Stephane Faroult
    Oriole Software
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Stephane Faroult
    INET: sfaroult_at_oriole.com

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: DENNIS WILLIAMS
    INET: DWILLIAMS_at_LIFETOUCH.COM

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Naveen Nahata
    INET: naveen_nahata_at_mindtree.com

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: DENNIS WILLIAMS
    INET: DWILLIAMS_at_LIFETOUCH.COM

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
  • Mark J. Bobak at Oct 27, 2002 at 7:18 pm
    But even with TIMED_STATISTICS=FALSE, the system will still have waits.
    Just because the duration of said waits is not accurately recorded
    doesn't mean they do not exist. And if one looks at the V$ wait
    interface, the waits will be logged there.

    -Mark
    On Sun, 2002-10-27 at 09:48, Tim Gorman wrote:
    Someone else cracked it -- he doesn't have TIMED_STATISTICS = TRUE...

    ----- Original Message -----
    To: "Multiple recipients of list ORACLE-L"
    Sent: Saturday, October 26, 2002 9:53 PM

    *no* waits?

    How is this possible?

    Is intantaneous computing now a reality?

    Sorry for the sarcasm. Wait, no, not really.;)

    Seriously, all databases wait, all operations take
    time. The question on everyone's lips is
    'How long are *yours* taking?'

    Jared
    On Saturday 26 October 2002 00:33, Rahul wrote:
    the DB is *not* experiencing any waits... i'm trying to bring down the
    run
    time of the
    insertion process, currently it takes around 9 hrs... the management
    wants
    to bring it
    down to 5-6 hrs...again.. i OD NOT see any wait events while the process
    is
    running..

    there are no indexes on the tables
    ----------
    From: Tim Gorman[SMTP:Tim_at_SageLogix.com]
    Reply To: ORACLE-L_at_fatcity.com
    Sent: Friday, October 25, 2002 9:13 PM
    To: Multiple recipients of list ORACLE-L
    Subject: Re: can clustering help INSERTS ?

    Rahul,

    It does no good to speculate; let's work with facts...

    What wait-events are occurring in the sessions running the INSERTs?
    If
    you
    can locate the sessions in the V$SESSION view, then use the value in
    the
    column SID to locate associated rows in the V$SESSION_EVENT view,
    sorting
    by
    the cumulative time spent on each wait-event:

    select event, time_waited, average_time, max_time
    from v$session_event
    where sid = &&SID
    union
    select n.name, s.value, 0, 0
    from v$sesstat s, v$statname n
    where s.sid = &&SID
    and n.name in ('CPU used by this session','parse time
    cpu','recursive
    cpu usage')
    and s.statistic# = n.statistic#
    order by 2 desc

    As you can see, in addition to wait-event information, this query will
    also
    mix in CPU statistics from the V$SESSTAT view, to give a better
    picture
    of where time is being spent by these sessions...

    Can you post the results of these queries back to the list?

    Thanks!

    -Tim

    ----- Original Message -----
    To: "Multiple recipients of list ORACLE-L"
    Sent: Friday, October 25, 2002 6:18 AM
    List,
    i have two heavily inserted tables, the structures are same.
    currently these tables reside on separate disks, can i increase the
    performance
    of inserts if i create these tables in a cluster ? as a cluster
    would
    force
    the rows of both the tables
    to be physically close on the disk !

    regards
    -rahul

    Ora 7.3 on AIX



    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Rahul
    INET: rahul_at_ratelindo.co.id

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting
    services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Tim Gorman
    INET: Tim_at_SageLogix.com

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
    -------------------------------------------------------
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Jared Still
    INET: jkstill_at_cybcon.com

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
    --
    --
    Mark J. Bobak
    Oracle DBA
    mark_at_bobak.net
    "It is not enough to have a good mind. The main thing is to use it
    well."
    -- Rene Descartes
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Mark J. Bobak
    INET: mark_at_bobak.net

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
  • Tim Gorman at Oct 27, 2002 at 7:58 pm
    Of course the system will still have waits -- it's just that the query
    provided will show values of zero for all of them when TIMED_STATISTICS =
    FALSE. That's why Rahul said that there were "no waits at all". My bad...

    Rahul,

    Please enable the parameter TIMED_STATISTICS = TRUE, re-run the INSERT
    operations, re-run the query provided in the earlier email, and then post
    the results to the list.

    You can enable the parameter either by setting the parameter in your
    "init.ora" file and restarting the database instance and then re-running
    your INSERT operations, or by simply running ALTER SYSTEM SET
    TIMED_STATISTICS = TRUE and the re-running your INSERT operations (without
    restarting the database instance). If you choose the latter route, please
    be sure to update your "init.ora" file accordingly for future database
    instance startups, as well.

    Hope this helps...

    -Tim

    Original Message -----
    To: "Multiple recipients of list ORACLE-L"
    Sent: Sunday, October 27, 2002 12:18 PM
    But even with TIMED_STATISTICS=FALSE, the system will still have waits.
    Just because the duration of said waits is not accurately recorded
    doesn't mean they do not exist. And if one looks at the V$ wait
    interface, the waits will be logged there.

    -Mark
    On Sun, 2002-10-27 at 09:48, Tim Gorman wrote:
    Someone else cracked it -- he doesn't have TIMED_STATISTICS = TRUE...

    ----- Original Message -----
    To: "Multiple recipients of list ORACLE-L"
    Sent: Saturday, October 26, 2002 9:53 PM

    *no* waits?

    How is this possible?

    Is intantaneous computing now a reality?

    Sorry for the sarcasm. Wait, no, not really.;)

    Seriously, all databases wait, all operations take
    time. The question on everyone's lips is
    'How long are *yours* taking?'

    Jared
    On Saturday 26 October 2002 00:33, Rahul wrote:
    the DB is *not* experiencing any waits... i'm trying to bring down
    the
    run
    time of the
    insertion process, currently it takes around 9 hrs... the management
    wants
    to bring it
    down to 5-6 hrs...again.. i OD NOT see any wait events while the
    process
    is
    running..

    there are no indexes on the tables
    ----------
    From: Tim Gorman[SMTP:Tim_at_SageLogix.com]
    Reply To: ORACLE-L_at_fatcity.com
    Sent: Friday, October 25, 2002 9:13 PM
    To: Multiple recipients of list ORACLE-L
    Subject: Re: can clustering help INSERTS ?

    Rahul,

    It does no good to speculate; let's work with facts...

    What wait-events are occurring in the sessions running the
    INSERTs?
    If
    you
    can locate the sessions in the V$SESSION view, then use the value
    in
    the
    column SID to locate associated rows in the V$SESSION_EVENT view,
    sorting
    by
    the cumulative time spent on each wait-event:

    select event, time_waited, average_time, max_time
    from v$session_event
    where sid = &&SID
    union
    select n.name, s.value, 0, 0
    from v$sesstat s, v$statname n
    where s.sid = &&SID
    and n.name in ('CPU used by this session','parse time
    cpu','recursive
    cpu usage')
    and s.statistic# = n.statistic#
    order by 2 desc

    As you can see, in addition to wait-event information, this query
    will
    also
    mix in CPU statistics from the V$SESSTAT view, to give a better
    picture
    of where time is being spent by these sessions...

    Can you post the results of these queries back to the list?

    Thanks!

    -Tim

    ----- Original Message -----
    To: "Multiple recipients of list ORACLE-L"
    Sent: Friday, October 25, 2002 6:18 AM
    List,
    i have two heavily inserted tables, the structures are same.
    currently these tables reside on separate disks, can i increase
    the
    performance
    of inserts if i create these tables in a cluster ? as a cluster
    would
    force
    the rows of both the tables
    to be physically close on the disk !

    regards
    -rahul

    Ora 7.3 on AIX



    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Rahul
    INET: rahul_at_ratelindo.co.id

    Fat City Network Services -- 858-538-5051
    http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting
    services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail
    message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and
    in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You
    may
    also send the HELP command for other information (like
    subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Tim Gorman
    INET: Tim_at_SageLogix.com

    Fat City Network Services -- 858-538-5051
    http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting
    services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and
    in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You
    may
    also send the HELP command for other information (like
    subscribing).
    -------------------------------------------------------
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Jared Still
    INET: jkstill_at_cybcon.com

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
    --
    --
    Mark J. Bobak
    Oracle DBA
    mark_at_bobak.net
    "It is not enough to have a good mind. The main thing is to use it
    well."
    -- Rene Descartes
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Mark J. Bobak
    INET: mark_at_bobak.net

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Tim Gorman
    INET: Tim_at_SageLogix.com

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
  • Rachel Carmichael at Oct 27, 2002 at 9:03 pm
    I believe Kevin gave that presentation at OpenWorld -- either last year
    or the year before. His paper is available for download on the TUSC
    site, as he works for TUSC.

    DENNIS WILLIAMS wrote:
    Naveen -
    He provided figures, and they are on the handout that is somewhere
    in my
    office. Maybe I'll run across it someday, or even better, maybe he'll
    publish his results. Here are a few more details that I posted to
    this list
    earlier.
    Kevin Loney (author of Oracle DBA Handbook) has performed index
    performance tests and presented a paper at our Twin Cities Oracle
    User's
    Group (http://www.tcoug.org). I don't know if his paper is on that
    site or
    if Kevin has posted it somewhere or if he will included his findings
    in a
    future book.
    His results (from memory) was that there weren't any big
    surprises. Say
    it takes 1 hour to load a table with no indexes on it. If you put 1
    index on
    that table, load time will increase about 20% to maybe 1hr 12 minutes
    (depending on how many columns are indexed, etc.). If we add a second
    index,
    load time will again increase, but by a smaller amount than for the
    first,
    maybe to 1 hr. 23 minutes. And so it goes. By the time we reach 20
    indexes,
    adding a 21st index may add only 3 or 4 minutes to our load time.
    There
    didn't seem to be any point where adding one more index would throw
    load
    times into a black hole and double load times or something like that.
    Kevin also tested whether the size of the index mattered. There
    were
    points where say, the 100,000th row caused index performance to
    suddenly
    drop, probably due to factors like adding a newer blevel. However it
    was
    almost impossible to predict this point ahead of time.
    My conclusions:
    - Dropping indexes speeds inserts.
    - If you have a single index on a table, adding a second index
    is
    costly.
    - If the table already has 20 indexes, one more isn't going to
    have a
    noticeable effect.


    Dennis Williams
    DBA, 40%OCP
    Lifetouch, Inc.
    dwilliams_at_lifetouch.com


    -----Original Message-----
    Sent: Saturday, October 26, 2002 3:43 AM
    To: Multiple recipients of list ORACLE-L


    Dennis,

    Did he publish any figures? I mean, it seems common-sense that adding
    the
    first index will hurt but adding 11th index to a table won't hurt
    that much.

    As stephane pointed out, cost of an index is 2.5 times more than the
    cost of
    insert in a non-indexed table.

    So assuming cost is 1, than cost with 1 index will be 3.5 as 250%
    increase.

    Cost with 5 indexes should be 13.5 and cost with 6 indexes will be 16
    less
    than twenty percent increase.

    Since the addition cost is constant for every index added, the
    percentage
    increase in cost (and also maybe time) will be lower and lower.

    Am I right or missing something?

    Regards
    Naveen

    -----Original Message-----
    Sent: Friday, October 25, 2002 11:45 PM
    To: Multiple recipients of list ORACLE-L


    Stephane
    You mentioned "each additional index costs about 2.5 times the
    cost of
    inserting into a non-indexed table". I just wanted to point out that
    Kevin
    Loney has done some performance tests involving the number of
    indexes. I
    don't know if he has published these anywhere. In a nutshell, the
    results
    were that a single index really hurts insert performance, and each
    additional index increases the hurt, but by a decreasing amount. The
    conclusions were:
    - If you can drop all indexes, that will really help inserts.
    - If you have one index, adding a second index will really hurt,
    but not
    as bad.
    - If the table already has 15 indexes, adding one more index
    probably
    won't be noticed.

    Dennis Williams
    DBA, 40%OCP
    Lifetouch, Inc.
    dwilliams_at_lifetouch.com


    -----Original Message-----
    Sent: Friday, October 25, 2002 8:04 AM
    To: Multiple recipients of list ORACLE-L


    Rahul wrote:
    List,
    i have two heavily inserted tables, the structures are same.
    currently these tables reside on separate disks, can i increase the
    performance
    of inserts if i create these tables in a cluster ? as a cluster
    would
    force
    the rows of both the tables
    to be physically close on the disk !

    regards
    -rahul

    Ora 7.3 on AIX
    You would also increase contention ... I'd rather try to augment the
    number of free lists, and, if you are lucky enough not to access your
    indexes in RANGE SCAN mode, to create them as REVERSE.
    Beware of indexes, by the way, each additional index costs about 2.5
    times the cost of inserting into a non-indexed table (in terms of
    logical blocks).

    --
    Regards,

    Stephane Faroult
    Oriole Software
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Stephane Faroult
    INET: sfaroult_at_oriole.com

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: DENNIS WILLIAMS
    INET: DWILLIAMS_at_LIFETOUCH.COM
    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Naveen Nahata
    INET: naveen_nahata_at_mindtree.com

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: DENNIS WILLIAMS
    INET: DWILLIAMS_at_LIFETOUCH.COM
    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    === message truncated ===

    Do you Yahoo!?
    Y! Web Hosting - Let the expert host your web site
    http://webhosting.yahoo.com/

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Rachel Carmichael
    INET: wisernet100_at_yahoo.com

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
  • Rahul at Oct 28, 2002 at 3:13 am
    ;-)
    the only waits i see are "parallel query dequeue wait", and sometimes
    v$session_wait shows "write complete waits" ..
    but i'm sure these are not slowing down the process... (or are they ?)
    as most of the time v$session_wait does not return a row !!
    and i query this view once every second

    Rahul
    ----------
    From: Jared Still[SMTP:jkstill_at_cybcon.com]
    Sent: Sunday, October 27, 2002 8:27 AM
    To: ORACLE-L_at_fatcity.com; Rahul
    Subject: Re: can clustering help INSERTS ?


    *no* waits?

    How is this possible?

    Is intantaneous computing now a reality?

    Sorry for the sarcasm. Wait, no, not really.;)

    Seriously, all databases wait, all operations take
    time. The question on everyone's lips is
    'How long are *yours* taking?'

    Jared
    On Saturday 26 October 2002 00:33, Rahul wrote:
    the DB is *not* experiencing any waits... i'm trying to bring down the run
    time of the
    insertion process, currently it takes around 9 hrs... the management wants
    to bring it
    down to 5-6 hrs...again.. i OD NOT see any wait events while the process is
    running..

    there are no indexes on the tables
    ----------
    From: Tim Gorman[SMTP:Tim_at_SageLogix.com]
    Reply To: ORACLE-L_at_fatcity.com
    Sent: Friday, October 25, 2002 9:13 PM
    To: Multiple recipients of list ORACLE-L
    Subject: Re: can clustering help INSERTS ?

    Rahul,

    It does no good to speculate; let's work with facts...

    What wait-events are occurring in the sessions running the INSERTs?
    If
    you
    can locate the sessions in the V$SESSION view, then use the value in
    the
    column SID to locate associated rows in the V$SESSION_EVENT view,
    sorting
    by
    the cumulative time spent on each wait-event:

    select event, time_waited, average_time, max_time
    from v$session_event
    where sid = &&SID
    union
    select n.name, s.value, 0, 0
    from v$sesstat s, v$statname n
    where s.sid = &&SID
    and n.name in ('CPU used by this session','parse time
    cpu','recursive
    cpu usage')
    and s.statistic# = n.statistic#
    order by 2 desc

    As you can see, in addition to wait-event information, this query will
    also
    mix in CPU statistics from the V$SESSTAT view, to give a better
    picture
    of where time is being spent by these sessions...

    Can you post the results of these queries back to the list?

    Thanks!

    -Tim

    ----- Original Message -----
    To: "Multiple recipients of list ORACLE-L"
    Sent: Friday, October 25, 2002 6:18 AM
    List,
    i have two heavily inserted tables, the structures are same.
    currently these tables reside on separate disks, can i increase the
    performance
    of inserts if i create these tables in a cluster ? as a cluster
    would
    force
    the rows of both the tables
    to be physically close on the disk !

    regards
    -rahul

    Ora 7.3 on AIX
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Rahul
    INET: rahul_at_ratelindo.co.id

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
  • Rahul at Oct 28, 2002 at 3:23 am
    top waits from system_event, when i siad "no waits" .. i ment no waits while
    querying session_wait !!
    offcourse a DB WILL experience waits... but, are these waits slowing down my
    inserts ??

    these stats are after the insertion of 16 million rows, the table in
    question is the only table on that disk

    EVENT TOTAL_WAITS TOTAL_TIMEOUTS SEC_WAITED AVERAGE_WAIT
    ----------------------------------- ----------- -------------- ----------
    ------------
    parallel query dequeue wait 314695 314824 629392.78
    200.000883
    db file parallel write 28199 356 3850.24
    13.6538175
    db file sequential read 650214 0 2681.93
    .412468818
    buffer busy waits 31427 16 955.09
    3.03907468
    latch free 1623885 42121 818.44
    .050400121
    log file parallel write 25338 0 737.44
    2.91041124
    db file scattered read 50131 0 690.79
    1.37796972
    log file sync 10085 3 144.78

    1.43559742
    the DB is *not* experiencing any waits... i'm trying to bring down the run
    time of the
    insertion process, currently it takes around 9 hrs... the management wants
    to bring it
    down to 5-6 hrs...again.. i OD NOT see any wait events while the process is
    running..

    there are no indexes on the tables
    ----------
    From: Tim Gorman[SMTP:Tim_at_SageLogix.com]
    Reply To: ORACLE-L_at_fatcity.com
    Sent: Friday, October 25, 2002 9:13 PM
    To: Multiple recipients of list ORACLE-L
    Subject: Re: can clustering help INSERTS ?

    Rahul,

    It does no good to speculate; let's work with facts...

    What wait-events are occurring in the sessions running the INSERTs?
    If
    you
    can locate the sessions in the V$SESSION view, then use the value in
    the
    column SID to locate associated rows in the V$SESSION_EVENT view,
    sorting
    by
    the cumulative time spent on each wait-event:

    select event, time_waited, average_time, max_time
    from v$session_event
    where sid = &&SID
    union
    select n.name, s.value, 0, 0
    from v$sesstat s, v$statname n
    where s.sid = &&SID
    and n.name in ('CPU used by this session','parse time
    cpu','recursive
    cpu usage')
    and s.statistic# = n.statistic#
    order by 2 desc

    As you can see, in addition to wait-event information, this query will
    also
    mix in CPU statistics from the V$SESSTAT view, to give a better
    picture
    of
    where time is being spent by these sessions...

    Can you post the results of these queries back to the list?

    Thanks!

    -Tim

    ----- Original Message -----
    To: "Multiple recipients of list ORACLE-L"
    Sent: Friday, October 25, 2002 6:18 AM

    List,
    i have two heavily inserted tables, the structures are same.
    currently these tables reside on separate disks, can i increase the
    performance
    of inserts if i create these tables in a cluster ? as a cluster
    would
    force
    the rows of both the tables
    to be physically close on the disk !

    regards
    -rahul

    Ora 7.3 on AIX



    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Rahul
    INET: rahul_at_ratelindo.co.id

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting
    services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Tim Gorman
    INET: Tim_at_SageLogix.com

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Rahul
    INET: rahul_at_ratelindo.co.id

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Tim Gorman
    INET: Tim_at_SageLogix.com

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Rahul
    INET: rahul_at_ratelindo.co.id

    Fat City Network Services -- 858-538-5051 http://www.fatcity.com
    San Diego, California -- Mailing list and web hosting services
    ---------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedOct 25, '02 at 12:18p
activeOct 28, '02 at 3:23a
posts17
users8
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase