FAQ
Since I'm getting nowhere fast with Oracle Support, I'll try this here:

While looking in V$DB_OBJECT_CACHE, we noticed an unpinned row-level trigger
had been fired over 900K times. We compared this to other row-level triggers
on the same table and noticed that the other triggers were only executed
around
1000 or 2000 times. From knowledge of the business, it would seem that the
latter is much more believable. The major difference between this trigger
and
the others is that the others all have a column declared in the FOR UPDATE
clause of the trigger. For example, here's the header of the trigger in
question:

CREATE OR REPLACE TRIGGER QT_BLAH_BLAH_IU

AFTER INSERT OR UPDATE ON my_table
FOR EACH ROW

WHEN (NEW.commodity_code != OLD.commodity_code OR OLD.commodity_code IS
NULL)

...while the other triggers would also contain an "OF my_column" clause
immediately following "AFTER INSERT OR UPDATE".

The table contains about 200K rows. Having this trigger fire 900K times is
highly unlikely, given the WHEN clause. According to Oracle8i Application
Developer's Guide - Fundamentals, about the WHEN clause of a row-level
trigger:

"If the expression evaluates to TRUE for a row, then the trigger body is
fired
on behalf of that row. However, if the expression evaluates to FALSE or NOT
TRUE for a row (unknown, as with nulls), then the trigger body is not fired
for
that row."

The same manual also says:

"If a triggering statement specifies UPDATE, then an optional list of
columns
can be included in the triggering statement. If you include a column list,
then
the trigger is fired on an UPDATE statement only when one of the specified
columns is updated. If you omit a column list, then the trigger is fired
when
any column of the associated table is updated."

This leaves me a little confused as to what exactly fires when with a
trigger.
I know that bug 1764313 says the execution count is inaccurate, but if
anything
that bug would suggest that the 900K rows is LOW and not HIGH like we've
seen.
I guess my real question is what part of a trigger is loaded when. Is there
a
difference between the trigger and the trigger body when it comes to
executions? How is this difference shown the V$DB_OBJECT_CACHE or V$SQLAREA
for that matter? Is the distinction similar to PACKAGE vs. PACKAGE BODY?

BTW, if it matters, we're currently on 32-bit 8.1.7.4.0 on 64-bit HP/UX
11.0.

TIA,

Rich

Rich Jesse System/Database Administrator
rjesse_at_qtiworld.com Quad/Tech Inc, Sussex, WI USA

--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Jesse, Rich
INET: Rich.Jesse_at_qtiworld.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).

Search Discussions

  • Jamadagni, Rajendra at Jul 30, 2003 at 7:20 pm
    the trigger will fire if :new.commodity_code is null as well ...

    Raj

    Rajendra dot Jamadagni at nospamespn dot com
    All Views expressed in this email are strictly personal.
    QOTD: Any clod can have facts, having an opinion is an art !

    -----Original Message-----
    Sent: Wednesday, July 30, 2003 3:10 PM
    To: Multiple recipients of list ORACLE-L

    Since I'm getting nowhere fast with Oracle Support, I'll try this here:

    While looking in V$DB_OBJECT_CACHE, we noticed an unpinned row-level trigger
    had been fired over 900K times. We compared this to other row-level triggers
    on the same table and noticed that the other triggers were only executed
    around
    1000 or 2000 times. From knowledge of the business, it would seem that the
    latter is much more believable. The major difference between this trigger
    and
    the others is that the others all have a column declared in the FOR UPDATE
    clause of the trigger. For example, here's the header of the trigger in
    question:

    CREATE OR REPLACE TRIGGER QT_BLAH_BLAH_IU

    AFTER INSERT OR UPDATE ON my_table
    FOR EACH ROW

    WHEN (NEW.commodity_code != OLD.commodity_code OR OLD.commodity_code IS
    NULL)

    ...while the other triggers would also contain an "OF my_column" clause
    immediately following "AFTER INSERT OR UPDATE".

    The table contains about 200K rows. Having this trigger fire 900K times is
    highly unlikely, given the WHEN clause. According to Oracle8i Application
    Developer's Guide - Fundamentals, about the WHEN clause of a row-level
    trigger:

    "If the expression evaluates to TRUE for a row, then the trigger body is
    fired
    on behalf of that row. However, if the expression evaluates to FALSE or NOT
    TRUE for a row (unknown, as with nulls), then the trigger body is not fired
    for
    that row."

    The same manual also says:

    "If a triggering statement specifies UPDATE, then an optional list of
    columns
    can be included in the triggering statement. If you include a column list,
    then
    the trigger is fired on an UPDATE statement only when one of the specified
    columns is updated. If you omit a column list, then the trigger is fired
    when
    any column of the associated table is updated."

    This leaves me a little confused as to what exactly fires when with a
    trigger.
    I know that bug 1764313 says the execution count is inaccurate, but if
    anything
    that bug would suggest that the 900K rows is LOW and not HIGH like we've
    seen.
    I guess my real question is what part of a trigger is loaded when. Is there
    a
    difference between the trigger and the trigger body when it comes to
    executions? How is this difference shown the V$DB_OBJECT_CACHE or V$SQLAREA
    for that matter? Is the distinction similar to PACKAGE vs. PACKAGE BODY?

    BTW, if it matters, we're currently on 32-bit 8.1.7.4.0 on 64-bit HP/UX
    11.0.

    TIA,

    Rich

    Rich Jesse System/Database Administrator
    rjesse_at_qtiworld.com Quad/Tech Inc, Sussex, WI USA

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
    --
    Author: Jesse, Rich
    INET: Rich.Jesse_at_qtiworld.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.net
    --
    Author: Jamadagni, Rajendra
    INET: Rajendra.Jamadagni_at_espn.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).

    text/plain attachment: ESPN_Disclaimer.txt
  • Jesse, Rich at Jul 30, 2003 at 8:04 pm
    Sorry, I should have mentioned that the app doesn't allow NULLs, even though
    there's no constraint on that column. The column is type CHAR. The
    :old.commodity_code check in the WHEN clause is there to account for
    INSERTed rows. The table has about 200K rows total (inventory part master),
    with only 1-to-10 rows inserted per day normally. Many 1000s of UPDATEs per
    day, potentially, yes.

    Thanks,
    Rich

    Rich Jesse System/Database Administrator
    rjesse_at_qtiworld.com Quad/Tech Inc, Sussex, WI USA

    -----Original Message-----
    Sent: Wednesday, July 30, 2003 2:20 PM
    To: Multiple recipients of list ORACLE-L

    the trigger will fire if :new.commodity_code is null as well ...
    Raj

    Rajendra dot Jamadagni at nospamespn dot com
    All Views expressed in this email are strictly personal.
    QOTD: Any clod can have facts, having an opinion is an art !

    -----Original Message-----
    Sent: Wednesday, July 30, 2003 3:10 PM
    To: Multiple recipients of list ORACLE-L

    Since I'm getting nowhere fast with Oracle Support, I'll try this here:
    While looking in V$DB_OBJECT_CACHE, we noticed an unpinned row-level trigger

    had been fired over 900K times. We compared this to other row-level triggers

    on the same table and noticed that the other triggers were only executed
    around
    1000 or 2000 times. From knowledge of the business, it would seem that the
    latter is much more believable. The major difference between this trigger
    and
    the others is that the others all have a column declared in the FOR UPDATE
    clause of the trigger. For example, here's the header of the trigger in
    question:
    CREATE OR REPLACE TRIGGER QT_BLAH_BLAH_IU

    AFTER INSERT OR UPDATE ON my_table
    FOR EACH ROW

    WHEN (NEW.commodity_code != OLD.commodity_code OR OLD.commodity_code IS
    NULL)

    ...while the other triggers would also contain an "OF my_column" clause
    immediately following "AFTER INSERT OR UPDATE".
    The table contains about 200K rows. Having this trigger fire 900K times is
    highly unlikely, given the WHEN clause. According to Oracle8i Application
    Developer's Guide - Fundamentals, about the WHEN clause of a row-level
    trigger:
    "If the expression evaluates to TRUE for a row, then the trigger body is
    fired
    on behalf of that row. However, if the expression evaluates to FALSE or NOT
    TRUE for a row (unknown, as with nulls), then the trigger body is not fired
    for
    that row."
    The same manual also says:
    "If a triggering statement specifies UPDATE, then an optional list of
    columns
    can be included in the triggering statement. If you include a column list,
    then
    the trigger is fired on an UPDATE statement only when one of the specified
    columns is updated. If you omit a column list, then the trigger is fired
    when
    any column of the associated table is updated."
    This leaves me a little confused as to what exactly fires when with a
    trigger.
    I know that bug 1764313 says the execution count is inaccurate, but if
    anything
    that bug would suggest that the 900K rows is LOW and not HIGH like we've
    seen.
    I guess my real question is what part of a trigger is loaded when. Is there
    a
    difference between the trigger and the trigger body when it comes to
    executions? How is this difference shown the V$DB_OBJECT_CACHE or V$SQLAREA

    for that matter? Is the distinction similar to PACKAGE vs. PACKAGE BODY?
    BTW, if it matters, we're currently on 32-bit 8.1.7.4.0 on 64-bit HP/UX
    11.0.
    TIA,

    Rich

    Rich Jesse System/Database Administrator
    rjesse_at_qtiworld.com Quad/Tech Inc, Sussex, WI USA

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
    --
    Author: Jesse, Rich
    INET: Rich.Jesse_at_qtiworld.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).

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedJul 30, '03 at 7:09p
activeJul 30, '03 at 8:04p
posts3
users2
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase