FAQ
List,

Oracle 10.2.0.4, Solaris 8

I've found a simple insert that has:

1 execution
2,512,102 Buffer Gets
36,479 Physical Reads
1 Parse call
1 Row

This insert statement appears quite a few times in the AWR report, and for
the times I see it, the statistics are pretty close to what I've listed. The
insert just has a list of columns and a list of values. No subqueries. There
is a TO_DATE conversion on two columns.This is a real table, not a view.

Does anyone have any suggestions of why a simple insert could cause this
much database activity?

Thanks to Brandon for suggesting awrsqrpt.sql in response to another
question. That is a great tool.

Dennis Williams

Search Discussions

  • Stephens, Chris at Dec 12, 2008 at 8:57 pm
    Indexes?



    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Dennis Williams
    Sent: Friday, December 12, 2008 2:55 PM
    To: oracle-l_at_freelists.org
    Subject: Simple insert - 36,000 physical reads



    List,



    Oracle 10.2.0.4, Solaris 8



    I've found a simple insert that has:

    1 execution

    2,512,102 Buffer Gets

    36,479 Physical Reads

    1 Parse call

    1 Row



    This insert statement appears quite a few times in the AWR report, and
    for the times I see it, the statistics are pretty close to what I've
    listed. The insert just has a list of columns and a list of values. No
    subqueries. There is a TO_DATE conversion on two columns.This is a real
    table, not a view.



    Does anyone have any suggestions of why a simple insert could cause this
    much database activity?



    Thanks to Brandon for suggesting awrsqrpt.sql in response to another
    question. That is a great tool.



    Dennis Williams

    CONFIDENTIALITY NOTICE:

    This message is intended for the use of the individual or entity to which it is addressed and may contain information that is privileged,
    confidential and exempt from disclosure under applicable law. If the reader of this message is not the intended recipient or the employee or agent responsible for delivering this message to the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this
    communication in error, please notify us immediately by email reply.
  • Michael Fontana at Dec 12, 2008 at 9:02 pm
    Explain plan?



    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Stephens, Chris
    Sent: Friday, December 12, 2008 2:57 PM
    To: oracledba.williams_at_gmail.com; oracle-l_at_freelists.org
    Subject: RE: Simple insert - 36,000 physical reads



    Indexes?



    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Dennis Williams
    Sent: Friday, December 12, 2008 2:55 PM
    To: oracle-l_at_freelists.org
    Subject: Simple insert - 36,000 physical reads



    List,



    Oracle 10.2.0.4, Solaris 8



    I've found a simple insert that has:

    1 execution

    2,512,102 Buffer Gets

    36,479 Physical Reads

    1 Parse call

    1 Row



    This insert statement appears quite a few times in the AWR report, and for
    the times I see it, the statistics are pretty close to what I've listed.
    The insert just has a list of columns and a list of values. No subqueries.
    There is a TO_DATE conversion on two columns.This is a real table, not a
    view.



    Does anyone have any suggestions of why a simple insert could cause this
    much database activity?



    Thanks to Brandon for suggesting awrsqrpt.sql in response to another
    question. That is a great tool.



    Dennis Williams



    CONFIDENTIALITY NOTICE:

    This message is intended for the use of the individual or entity to which
    it is addressed and may contain information that is privileged,
    confidential and exempt from disclosure under applicable law. If the
    reader of this message is not the intended recipient or the employee or
    agent responsible for delivering this message to the intended recipient,
    you are hereby notified that any dissemination, distribution or copying of
    this communication is strictly prohibited. If you have received this
    communication in error, please notify us immediately by email reply.
  • Ken Naim at Dec 12, 2008 at 9:00 pm
    Triggers could do it.



    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Dennis Williams
    Sent: Friday, December 12, 2008 3:55 PM
    To: oracle-l_at_freelists.org
    Subject: Simple insert - 36,000 physical reads



    List,



    Oracle 10.2.0.4, Solaris 8



    I've found a simple insert that has:

    1 execution

    2,512,102 Buffer Gets

    36,479 Physical Reads

    1 Parse call

    1 Row



    This insert statement appears quite a few times in the AWR report, and for
    the times I see it, the statistics are pretty close to what I've listed. The
    insert just has a list of columns and a list of values. No subqueries. There
    is a TO_DATE conversion on two columns.This is a real table, not a view.



    Does anyone have any suggestions of why a simple insert could cause this
    much database activity?



    Thanks to Brandon for suggesting awrsqrpt.sql in response to another
    question. That is a great tool.



    Dennis Williams
  • Channa, Santhosh at Dec 12, 2008 at 9:12 pm
    Are the values for the insert are straight values or any home-grown
    function calls that generates the values?



    Regards,
    Santhosh Channa

    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Dennis Williams
    Sent: Friday, December 12, 2008 3:55 PM
    To: oracle-l_at_freelists.org
    Subject: Simple insert - 36,000 physical reads



    List,



    Oracle 10.2.0.4 <http://10.2.0.4>, Solaris 8



    I've found a simple insert that has:

    1 execution

    2,512,102 Buffer Gets

    36,479 Physical Reads

    1 Parse call

    1 Row



    This insert statement appears quite a few times in the AWR report, and
    for the times I see it, the statistics are pretty close to what I've
    listed. The insert just has a list of columns and a list of values. No
    subqueries. There is a TO_DATE conversion on two columns.This is a real
    table, not a view.



    Does anyone have any suggestions of why a simple insert could cause this
    much database activity?



    Thanks to Brandon for suggesting awrsqrpt.sql in response to another
    question. That is a great tool.



    Dennis Williams
  • Rjamya at Dec 12, 2008 at 10:30 pm
    Lots of indexes on the tables could also cause additional pio attributed to
    main query. possible fk validations ?

    Raj

    On Fri, Dec 12, 2008 at 3:55 PM, Dennis Williams <
    oracledba.williams_at_gmail.com> wrote:
    List,

    Oracle 10.2.0.4, Solaris 8

    I've found a simple insert that has:
    1 execution
    2,512,102 Buffer Gets
    36,479 Physical Reads
    1 Parse call
    1 Row
    --
    http://www.freelists.org/webpage/oracle-l
  • Nuno Souto at Dec 13, 2008 at 1:06 am
    Trigger(s)?

    --
    Cheers
    Nuno Souto
    in sunny Sydney, Australia
    dbvision_at_iinet.net.au

    Dennis Williams wrote,on my timestamp of 13/12/2008 7:55 AM:
    Does anyone have any suggestions of why a simple insert could cause this
    much database activity?
    --
    http://www.freelists.org/webpage/oracle-l
  • Stefan Knecht at Dec 13, 2008 at 9:06 am
    If you grab the SQL and run it yourself in a session manually (using
    autotrace or sql trace) do you see the same effect ?

    For things like this a SQL trace would also reveal where the IO takes place
    (file#, block#) and any recursive sql occurring through triggers, vpd, or
    whatever else there might be.

    Stefan

    Stefan P Knecht
    Senior Consultant
    Systems Engineering

    OPITZ CONSULTING Schweiz GmbH
    Seestrasse 97
    CH-8800 Thalwil

    Mobile +41-79-571 36 27
    stefan.knecht_at_opitz-consulting.ch
    http://www.opitz-consulting.ch

    OCP 9i/10g SCSA SCNA

    On Fri, Dec 12, 2008 at 9:55 PM, Dennis Williams <
    oracledba.williams_at_gmail.com> wrote:
    List,

    Oracle 10.2.0.4, Solaris 8

    I've found a simple insert that has:
    1 execution
    2,512,102 Buffer Gets
    36,479 Physical Reads
    1 Parse call
    1 Row

    This insert statement appears quite a few times in the AWR report, and for
    the times I see it, the statistics are pretty close to what I've listed. The
    insert just has a list of columns and a list of values. No subqueries. There
    is a TO_DATE conversion on two columns.This is a real table, not a view.

    Does anyone have any suggestions of why a simple insert could cause this
    much database activity?

    Thanks to Brandon for suggesting awrsqrpt.sql in response to another
    question. That is a great tool.

    Dennis Williams
    --
    http://www.freelists.org/webpage/oracle-l
  • Bjoern Rost at Dec 15, 2008 at 10:35 am

    Does anyone have any suggestions of why a simple insert could cause
    this much database activity?
    a foreign key constraint on a column that is not indexed ?

    Bjoern

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedDec 12, '08 at 8:55p
activeDec 15, '08 at 10:35a
posts9
users9
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase