FAQ
Afternoon (UK time) all,

Oracle 8174. (I know, I know !)
HPUX 11.11.

I wonder if anyone has any clues about this problem that I'm seeing on
one of my databases. There is a housekeeping packaged procedure run on a
daily basis to remove old data. It simply core dumps every night.

I've traced it to the following delete statement in the code below.
This delete actually deletes zero rows (no data matches anything
configured yet).

I've also watched the session's PGA memory max increasing to 2 GB and
when it gets there, the session crashes out. I'm puzzled as to how a
delete (with no rows to delete) uses so much PGA. The function call to
Pk_Utils.fnc_get_sys_par ( 'HOUSE_AUDIT') returns a number from an
in-memory array/collection and is currently returning 20. This
array/collection is initialised on package initialisation and is simply
a 64 row 'table' of names and values.

I've tested all the code beloe in isolation and there's nothing I can
see that makes the PGA usage go up and up, there is an slight increase
when the packages are initialised but that's it. It eats up about 1 MB
of PGA per second (rough estimate) and I have absolutely no idea where
it is going or why.

The DELETE does a full table scan of a table with 1,527,000 rows of
average length 102 bytes. The table has 22,008 blocks (8K block size)
and 6 free blocks and was analysed earlier this week - even with my
limited arithmetical abilities, that's a grand total of approx 150MB of
data (as per rows * length) or approx 175 MB if you take blocks * block
size. However, that will be part of the buffer cache and noty in the
PGA.

Obviously, there will be a cursor opened in the PGA but I cannot see
why it would need over 2 GB. The delete uses no UNDO at all - there are
no rows selected for deletion.

BEGIN

Pk_Trace.ENTER ( 'Delete HAZ_RGBA_REG_BATCH_AUD records' );
DELETE HAZ_RGBA_REG_BATCH_AUD
WHERE MONTHS_BETWEEN(SYSDATE, LAST_USED_DATTIM) >
Pk_Utils.fnc_get_sys_par ( 'HOUSE_AUDIT');
COMMIT;
Pk_Trace.LEAVE;
EXCEPTION
WHEN OTHERS THEN
Pk_Trace.REPORT_ERROR;
RAISE;

END;

In the WHERE clause above, is the PK_UTILS package being called for
each row or just once. 10046 trace didn't give any useful help. (Nor did
single stepping the code in TOAD - it executed as one statement, the
DELETE.)

The PK_UTILS code makes two calls to PK_TRACE internally and this
simply writes a record to a logfile using UTL_FILE.

This is about step 7 of 10 in the houskeeping code and everything
before it uses MONTHS_BETWEEN etc as above - different tables are
involved along with a different parameter from the fnc_get_sys_par call.
That's all. So far, nothing is actually being deleted as we have not hit
anything older than the retention period in any of the preceeding or
following DELETEs.

We are seriously puzzled here.

Each core dump has this in the alert log :

ORA-07445: exception encountered: core dump [11] [3221215928]
[552153088] [0] [] []
ORA-04030: out of process memory when trying to allocate 1048 bytes
(koh-kghu call,pl/sql vc2)
ORA-06510: PL/SQL: unhandled user-defined exception
ORA-06500: PL/SQL: storage error

An extract of the call stack trace for one of these dumps is :

ORA-07445: exception encountered: core dump [11] [3221215928]
[1609378340] [0] [] []
ORA-04030: out of process memory when trying to allocate 8216 bytes
(PLS non-lib hp,PL/SQL STACK)
ORA-06500: PL/SQL: storage error
----- CAll Stack Trace -----

calling call entry argument values in
hex
location type point (? means dubious

value)

-------------------- -------- --------------------
----------------------------
ssexhd()+344 ? ksedst() 000000000 ?
800003FFBFFF0DA0 ?
C0000000002983D7 ?
0001F7EE8 ?
_sigreturn()+0 ? ssexhd() 000000000 ?
000000000 ?
053440010 ?
800003FFBFFFD570 ?
lfirist()+424 ? _sigreturn() 4000000000576418 ?
8000000100078450 ?
000000000 ?
8000000174EC5DA8 ?
lpmterm()+240 ? lfirist() C00000000029DCBB ?
8000000174EC4258 ?
4000000000ED8D47 ?
0000000A0 ?
lpminit()+664 ? lpmterm() 80000001000B7BC4 ?
4000000000463510 ?
000007FFF ?
4000000000468F08 ?
pifictx()+140 ? lpminit() 800003FFBFFF88D0 ?
000000000 ?
000000003 ?
4000000000A60E97 ?

Metalink doesn't have anyhting for our parameters but a few hits on
core dukp [11] show what appears to be different errors:o(

Any help gratefully received, but I suspect am iTAR/SR is in the
pipeline.

Cheers,
Norman.

Norman Dunbar.
Contract Oracle DBA.
Rivers House, Leeds.

Internal : 7 28 2051
External : 0113 231 2051

Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else.

We have checked this email and its attachments for viruses. But you should still check any attachment before opening it.

We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes.

If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk

Search Discussions

  • Mladen Gogala at Dec 20, 2006 at 2:16 pm

    On 12/20/2006 08:57:04 AM, Norman Dunbar wrote:


    ORA-07445: exception encountered: core dump [11] [3221215928]
    [552153088] [0] [] []
    ORA-04030: out of process memory when trying to allocate 1048 bytes
    (koh-kghu call,pl/sql vc2)
    ORA-06510: PL/SQL: unhandled user-defined exception
    ORA-06500: PL/SQL: storage error
    Norman, you got exception signal 11, segmentation violation. HP-UX 11.11
    is a 64-bit OS so maximum segment size should be slightly larger then 2GB,
    unless there is an implicit or explicit setting in your OS preventing users
    from creating segments larger then 2GB. Is there any such limit? I am not well
    versed in HP-UX any more, but I'd look into SAM and check the OS parameters.
    Some other flavors have such things configured in /etc/security/limits.conf
    You should enlist help of your local SOB (System / Operations Boss) to help
    you out with the OS parameters.
  • Norman Dunbar at Dec 20, 2006 at 2:41 pm
    Thanks for your replies gents.

    I've checked SAM (I used to do HP sys admin many years ago too!) and
    found MAXDSIZ_64BIT is set to exactly 2 GB. Looks like I'm needing a
    kernel rebuild.

    I'm stilll interested in knowing what is causing it to blow out though
    .....

    When (if) I get more MAXDSIZ_64BIT soon and it works, I'll report back
    with a summary.

    Cheers,
    Norman.

    Norman Dunbar.
    Contract Oracle DBA.
    Rivers House, Leeds.

    Internal : 7 28 2051
    External : 0113 231 2051

    Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else.

    We have checked this email and its attachments for viruses. But you should still check any attachment before opening it.

    We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes.

    If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk
  • Jason Heinrich at Dec 20, 2006 at 2:51 pm
    Norman,
    Are you able to modify this code? Using MONTHS_BETWEEN in your WHERE clause
    is forcing the full table scan, which, I suspect, is causing a large sort
    in-memory. Placing an index on LAST_USED_DATTIM and rewriting your WHERE
    clause thusly:

    WHERE LAST_USED_DATTIM < ADD_MONTHS( SYSDATE,
    Pk_Utils.fnc_get_sys_par('HOUSE_AUDIT') * (-1) )

    would avoid both the scan and the sort. I don't know if this will solve all
    of your PGA usage, but it should help.
    On 12/20/06 7:57 AM, Norman Dunbar wrote:

    The DELETE does a full table scan of a table with 1,527,000 rows of
    average length 102 bytes. The table has 22,008 blocks (8K block size)
    and 6 free blocks and was analysed earlier this week - even with my
    limited arithmetical abilities, that's a grand total of approx 150MB of
    data (as per rows * length) or approx 175 MB if you take blocks * block
    size. However, that will be part of the buffer cache and noty in the
    PGA.
    Obviously, there will be a cursor opened in the PGA but I cannot see
    why it would need over 2 GB. The delete uses no UNDO at all - there are
    no rows selected for deletion.

    BEGIN
    Pk_Trace.ENTER ( 'Delete HAZ_RGBA_REG_BATCH_AUD records' );
    DELETE HAZ_RGBA_REG_BATCH_AUD
    WHERE MONTHS_BETWEEN(SYSDATE, LAST_USED_DATTIM) >
    Pk_Utils.fnc_get_sys_par ( 'HOUSE_AUDIT');
    COMMIT;
    Pk_Trace.LEAVE;
    EXCEPTION
    WHEN OTHERS THEN
    Pk_Trace.REPORT_ERROR;
    RAISE;
    END;
    In the WHERE clause above, is the PK_UTILS package being called for
    each row or just once. 10046 trace didn't give any useful help. (Nor did
    single stepping the code in TOAD - it executed as one statement, the
    DELETE.)
    The PK_UTILS code makes two calls to PK_TRACE internally and this
    simply writes a record to a logfile using UTL_FILE.

    This is about step 7 of 10 in the houskeeping code and everything
    before it uses MONTHS_BETWEEN etc as above - different tables are
    involved along with a different parameter from the fnc_get_sys_par call.
    That's all. So far, nothing is actually being deleted as we have not hit
    anything older than the retention period in any of the preceeding or
    following DELETEs.
    Jason Heinrich
    Oracle Database Administrator
    Pensacola Christian College
  • Rajeev Prabhakar at Dec 20, 2006 at 3:06 pm
    Hi Norman

    Although, the versions being talked about in the thread in
    the below mentioned URL is different, I did notice that the
    core issue is quite close (ie.. 2GB limit).

    Plus, it is on hp-ux 11.11, same as yours.

    http://www.orafaq.com/maillist/oracle-l/2003/12/23/1651.htm

    -Rajeev
    On 12/20/06, Jason Heinrich wrote:

    Norman,
    Are you able to modify this code? Using MONTHS_BETWEEN in your WHERE
    clause
    is forcing the full table scan, which, I suspect, is causing a large sort
    in-memory. Placing an index on LAST_USED_DATTIM and rewriting your WHERE
    clause thusly:

    WHERE LAST_USED_DATTIM < ADD_MONTHS( SYSDATE,
    Pk_Utils.fnc_get_sys_par('HOUSE_AUDIT') * (-1) )

    would avoid both the scan and the sort. I don't know if this will solve
    all
    of your PGA usage, but it should help.

    On 12/20/06 7:57 AM, Norman Dunbar wrote:

    The DELETE does a full table scan of a table with 1,527,000 rows of
    average length 102 bytes. The table has 22,008 blocks (8K block size)
    and 6 free blocks and was analysed earlier this week - even with my
    limited arithmetical abilities, that's a grand total of approx 150MB of
    data (as per rows * length) or approx 175 MB if you take blocks * block
    size. However, that will be part of the buffer cache and noty in the
    PGA.
    Obviously, there will be a cursor opened in the PGA but I cannot see
    why it would need over 2 GB. The delete uses no UNDO at all - there are
    no rows selected for deletion.

    BEGIN
    Pk_Trace.ENTER ( 'Delete HAZ_RGBA_REG_BATCH_AUD records' );
    DELETE HAZ_RGBA_REG_BATCH_AUD
    WHERE MONTHS_BETWEEN(SYSDATE, LAST_USED_DATTIM) >
    Pk_Utils.fnc_get_sys_par ( 'HOUSE_AUDIT');
    COMMIT;
    Pk_Trace.LEAVE;
    EXCEPTION
    WHEN OTHERS THEN
    Pk_Trace.REPORT_ERROR;
    RAISE;
    END;
    In the WHERE clause above, is the PK_UTILS package being called for
    each row or just once. 10046 trace didn't give any useful help. (Nor did
    single stepping the code in TOAD - it executed as one statement, the
    DELETE.)
    The PK_UTILS code makes two calls to PK_TRACE internally and this
    simply writes a record to a logfile using UTL_FILE.

    This is about step 7 of 10 in the houskeeping code and everything
    before it uses MONTHS_BETWEEN etc as above - different tables are
    involved along with a different parameter from the fnc_get_sys_par call.
    That's all. So far, nothing is actually being deleted as we have not hit
    anything older than the retention period in any of the preceeding or
    following DELETEs.

    ---------------
    Jason Heinrich
    Oracle Database Administrator
    Pensacola Christian College

    --
    http://www.freelists.org/webpage/oracle-l

    --
    http://www.freelists.org/webpage/oracle-l
  • Nigel Thomas at Dec 20, 2006 at 3:07 pm
    Mladen wrote
    I'd look into SAM and check the OS parameters.
    I wouldn't bother. Look at the error
    ORA-04030: out of process memory when trying to allocate 1048 bytes
    (koh-kghu call,pl/sql vc2)
    you've somehow blown the call stack. double the SGA and it will just blow a few milliseconds later...

    I bet that Norman's little proc is

    - executing every row
    - doing a PkTrace.Enter on every row (in the entire table) and stacking its context
    - maybe not doing a PkTrace.leave and so not popping the stack (either through code error or PL/SQL to SQL integration snafu)
    - and so generally exploding.

    As I said privately to Norman

    1) pull the function call out of the SQL
    2) rewrite the SQL to be WHERE LAST_DATTIM < value
    3) have a look through Pk_Utils.fnc_get_sys_par to see where it's lost its pop
    4) go home and start cooking the brussel sprouts (there's barely time before Monday...)

    Regards Nigel
  • Norman Dunbar at Dec 20, 2006 at 3:32 pm
    Nigel,

    (Your private email to me has not arrived yet - this one from the list,
    has.)
    you've somehow blown the call stack. double the SGA and it will just
    blow a few milliseconds later...
    Oh dear, so changing the kernel parameter won't help much then. Blowing
    the stack sounds like we have a recursion problem - possibly. Hmmmmmmm.
    I bet that Norman's little proc is
    - executing every row
    That's what I'm trying to find out.
    - doing a PkTrace.Enter on every row (in the entire table) and
    stacking its context
    Yes - possibly. It is certainly doing it once per call.
    - maybe not doing a PkTrace.leave and so not popping the stack
    (either through code error or
    PL/SQL to SQL integration snafu)
    No - the code is (a) performing an exit on sucessful exit, and the
    exception handlers are perfroming an error call which itself exits. Good
    call however.
    - and so generally exploding.
    It certainly does.
    As I said privately to Norman
    1) pull the function call out of the SQL
    2) rewrite the SQL to be WHERE LAST_DATTIM < value
    Right, I can do this as we have the source.
    3) have a look through Pk_Utils.fnc_get_sys_par to see where it's
    lost its pop
    Already done - it pops all its pushes!
    4) go home and start cooking the brussel sprouts (there's barely
    time before Monday...)
    They've been biling away nicely since April. We Scots like our veggies
    boiled to a mush - apparently.

    Thanks.
    Norman.

    Norman Dunbar.
    Contract Oracle DBA.
    Rivers House, Leeds.

    Internal : 7 28 2051
    External : 0113 231 2051

    Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else.

    We have checked this email and its attachments for viruses. But you should still check any attachment before opening it.

    We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes.

    If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk
  • Norman Dunbar at Dec 20, 2006 at 3:34 pm
    Hi Rajeev,
    Although, the versions being talked about in the thread in
    the below mentioned URL is different, I did notice that the
    core issue is quite close (ie.. 2GB limit).
    I followed the thread and while similar, appears to be a problem with
    PGA_AGGREGATE_SIZE and auto wokload management. My problem is on an 8174
    database whihc doesn't have those settings.

    Thanks anyway.

    Cheers,
    Norman.

    Norman Dunbar.
    Contract Oracle DBA.
    Rivers House, Leeds.

    Internal : 7 28 2051
    External : 0113 231 2051

    Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else.

    We have checked this email and its attachments for viruses. But you should still check any attachment before opening it.

    We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes.

    If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk
  • Powell, Mark D at Dec 20, 2006 at 3:40 pm
    By any chance is it possible that the array is being initialized
    repeatedly so that in fact you have hundreds or thousands of pl/sql
    tables (arrays) rather than just one?

    When done using a specific incarnation of a table is it truncated?

    Perhaps dbms_session.free_unused_memory would be of use.

    Mark D Powell --
    Phone (313) 592-5148

    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Norman Dunbar
    Sent: Wednesday, December 20, 2006 8:57 AM
    To: oracle-l_at_freelists.org
    Subject: Session PGA memory max exceeded 2 GB and crashes.

    Afternoon (UK time) all,

    Oracle 8174. (I know, I know !)
    HPUX 11.11.

    I wonder if anyone has any clues about this problem that I'm seeing on
    one of my databases. There is a housekeeping packaged procedure run on a
    daily basis to remove old data. It simply core dumps every night.

    I've traced it to the following delete statement in the code below.
    This delete actually deletes zero rows (no data matches anything
    configured yet).

    I've also watched the session's PGA memory max increasing to 2 GB and
    when it gets there, the session crashes out. I'm puzzled as to how a
    delete (with no rows to delete) uses so much PGA. The function call to
    Pk_Utils.fnc_get_sys_par ( 'HOUSE_AUDIT') returns a number from an
    in-memory array/collection and is currently returning 20. This
    array/collection is initialised on package initialisation and is simply
    a 64 row 'table' of names and values.

    I've tested all the code beloe in isolation and there's nothing I can
    see that makes the PGA usage go up and up, there is an slight increase
    when the packages are initialised but that's it. It eats up about 1 MB
    of PGA per second (rough estimate) and I have absolutely no idea where
    it is going or why.

    The DELETE does a full table scan of a table with 1,527,000 rows of
    average length 102 bytes. The table has 22,008 blocks (8K block size)
    and 6 free blocks and was analysed earlier this week - even with my
    limited arithmetical abilities, that's a grand total of approx 150MB of
    data (as per rows * length) or approx 175 MB if you take blocks * block
    size. However, that will be part of the buffer cache and noty in the
    PGA.

    Obviously, there will be a cursor opened in the PGA but I cannot see why
    it would need over 2 GB. The delete uses no UNDO at all - there are no
    rows selected for deletion.

    BEGIN

    Pk_Trace.ENTER ( 'Delete HAZ_RGBA_REG_BATCH_AUD records' );
    DELETE HAZ_RGBA_REG_BATCH_AUD
    WHERE MONTHS_BETWEEN(SYSDATE, LAST_USED_DATTIM) >
    Pk_Utils.fnc_get_sys_par ( 'HOUSE_AUDIT');
    COMMIT;
    Pk_Trace.LEAVE;
    EXCEPTION
    WHEN OTHERS THEN
    Pk_Trace.REPORT_ERROR;
    RAISE;

    END;

    In the WHERE clause above, is the PK_UTILS package being called for each
    row or just once. 10046 trace didn't give any useful help. (Nor did
    single stepping the code in TOAD - it executed as one statement, the
    DELETE.)

    The PK_UTILS code makes two calls to PK_TRACE internally and this simply
    writes a record to a logfile using UTL_FILE.

    This is about step 7 of 10 in the houskeeping code and everything before
    it uses MONTHS_BETWEEN etc as above - different tables are involved
    along with a different parameter from the fnc_get_sys_par call.
    That's all. So far, nothing is actually being deleted as we have not hit
    anything older than the retention period in any of the preceeding or
    following DELETEs.

    We are seriously puzzled here.

    Each core dump has this in the alert log :

    ORA-07445: exception encountered: core dump [11] [3221215928]
    [552153088] [0] [] []
    ORA-04030: out of process memory when trying to allocate 1048 bytes
    (koh-kghu call,pl/sql vc2)
    ORA-06510: PL/SQL: unhandled user-defined exception
    ORA-06500: PL/SQL: storage error

    An extract of the call stack trace for one of these dumps is :

    ORA-07445: exception encountered: core dump [11] [3221215928]
    [1609378340] [0] [] []

    ORA-04030: out of process memory when trying to allocate 8216 bytes (PLS
    non-lib hp,PL/SQL STACK)
    ORA-06500: PL/SQL: storage error
    ----- CAll Stack Trace -----
    calling call entry argument values in
    hex
    location type point (? means dubious

    value)

    -------------------- -------- --------------------
    ----------------------------
    ssexhd()+344 ? ksedst() 000000000 ?
    800003FFBFFF0DA0 ?
    C0000000002983D7 ?
    0001F7EE8 ?
    _sigreturn()+0 ? ssexhd() 000000000 ?
    000000000 ?
    053440010 ?
    800003FFBFFFD570 ?
    lfirist()+424 ? _sigreturn() 4000000000576418 ?
    8000000100078450 ?
    000000000 ?
    8000000174EC5DA8 ?
    lpmterm()+240 ? lfirist() C00000000029DCBB ?
    8000000174EC4258 ?
    4000000000ED8D47 ?
    0000000A0 ?
    lpminit()+664 ? lpmterm() 80000001000B7BC4 ?
    4000000000463510 ?
    000007FFF ?
    4000000000468F08 ?
    pifictx()+140 ? lpminit() 800003FFBFFF88D0 ?
    000000000 ?
    000000003 ?
    4000000000A60E97 ?

    Metalink doesn't have anyhting for our parameters but a few hits on core
    dukp [11] show what appears to be different errors:o(

    Any help gratefully received, but I suspect am iTAR/SR is in the
    pipeline.

    Cheers,
    Norman.

    Norman Dunbar.
    Contract Oracle DBA.
    Rivers House, Leeds.

    Internal : 7 28 2051
    External : 0113 231 2051

    Information in this message may be confidential and may be legally
    privileged. If you have received this message by mistake, please notify
    the sender immediately, delete it and do not copy it to anyone else.

    We have checked this email and its attachments for viruses. But you
    should still check any attachment before opening it.

    We may have to make this message and any reply to it public if asked to
    under the Freedom of Information Act, Data Protection Act or for
    litigation. Email messages and attachments sent to or from any
    Environment Agency address may also be accessed by someone other than
    the sender or recipient, for business purposes.

    If we have sent you information and you wish to use it please read our
    terms and conditions which you can get by calling us on 08708 506 506.
    Find out more about the Environment Agency at
    www.environment-agency.gov.uk

    --
    http://www.freelists.org/webpage/oracle-l

    --
    http://www.freelists.org/webpage/oracle-l
  • Norman Dunbar at Dec 20, 2006 at 3:47 pm
    Nigel,
    I bet that Norman's little proc is
    - executing every row
    Ok, this has been tested. (not quite on this table though).

    I built a small function to return a number and taking a single
    VARCHAR2 as an input parameter. I then put a call to
    DBMS_OUTPUT.PUT_LINE in the function and called it in a similar manner
    to the 'blow up' as in :

    DELETE FROM test_table WHERE ID > function_call('UNUSED');

    I only had one line of output and, as expected, zero rows were deleted
    from my test table. The test table has a few thousand rows in it.

    Oh well, more investigation is required.

    Cheers,
    norman.

    Norman Dunbar.
    Contract Oracle DBA.
    Rivers House, Leeds.

    Internal : 7 28 2051
    External : 0113 231 2051

    Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else.

    We have checked this email and its attachments for viruses. But you should still check any attachment before opening it.

    We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes.

    If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk
  • Nigel Thomas at Dec 20, 2006 at 9:31 pm
    Norman

    See this AskTom thread - the number of executions of the user function depends on the SQL execution plan: http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:6978972926020.

    I reproduced (on Oracle XE, using SQL Developer:

    script

    select * from v$version;

    CREATE OR REPLACE FUNCTION TESTUSER.TEST_FUNCTION

    (param IN VARCHAR2)
    RETURN NUMBER AS

    BEGIN

    counter_pkg.increment_counter;
    RETURN 190;

    END;

    /
    drop index th_amt_i;
    begin counter_pkg.reset_counter; end;
    /

    select * from trade_history where amount > test_function('X');



    begin

    counter_pkg.print_counter('FTS access');
    counter_pkg.reset_counter;
    end;
    /
    select * from trade_history where amount > (select test_function('X') from dual);
    begin

    counter_pkg.print_counter('FTS access with subquery');
    counter_pkg.reset_counter;
    end;
    /


    create index th_amt_i on trade_history(amount);
    select count(*) from trade_history;
    select * from trade_history where amount > test_function('X');
    begin

    counter_pkg.print_counter('Index range scan');
    counter_pkg.reset_counter;
    end;
    /


    script output

    BANNER

    Oracle Database 10g Express Edition Release 10.2.0.1.0 - Product

    PL/SQL Release 10.2.0.1.0 - Production
    CORE 10.2.0.1.0 Production
    TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
    NLSRTL Version 10.2.0.1.0 - Production

    5 rows selected

    FUNCTION TESTUSER.TEST_FUNCTION Compiled.
    drop index th_amt_i succeeded.
    anonymous block completed

    TRADE_DATE AMOUNT TICKER
    ------------------------- ---------------------- ------
    12-SEP-06 199 ORCL
    11-SEP-06 198 ORCL
    10-SEP-06 197 ORCL
    09-SEP-06 196 ORCL
    08-SEP-06 195 ORCL
    07-SEP-06 194 ORCL
    06-SEP-06 193 ORCL
    05-SEP-06 192 ORCL
    04-SEP-06 191 ORCL

    9 rows selected

    anonymous block completed
    FTS access: execution count = 365

    TRADE_DATE AMOUNT TICKER
    ------------------------- ---------------------- ------
    12-SEP-06 199 ORCL
    11-SEP-06 198 ORCL
    10-SEP-06 197 ORCL
    09-SEP-06 196 ORCL
    08-SEP-06 195 ORCL
    07-SEP-06 194 ORCL
    06-SEP-06 193 ORCL
    05-SEP-06 192 ORCL
    04-SEP-06 191 ORCL

    9 rows selected

    anonymous block completed
    FTS access with subquery: execution count = 1
    create index succeeded.
    COUNT(*)

    365
    1 rows selected

    TRADE_DATE AMOUNT TICKER
    ------------------------- ---------------------- ------
    04-SEP-06 191 ORCL
    05-SEP-06 192 ORCL
    06-SEP-06 193 ORCL
    07-SEP-06 194 ORCL
    08-SEP-06 195 ORCL
    09-SEP-06 196 ORCL
    10-SEP-06 197 ORCL
    11-SEP-06 198 ORCL
    12-SEP-06 199 ORCL

    9 rows selected

    anonymous block completed
    Index range scan: execution count = 1

    That still doesn't explain how your call stack was blown, of course...

    Regards

    Nigel
  • Jonathan Lewis at Dec 21, 2006 at 8:53 am
    Norman,

    In general you can expect the function to be called
    twice if you use it to identify an indexed access path
    (as in your later experiment, perhaps).

    id > function(const)

    However, for a tablescan - which this seems to be -
    the function is called once per row. (Until 10g where
    deterministic functions nearly work).

    I would guess that you have a memory leak in the pl/sql.

    Change the code to:

    WHERE MONTHS_BETWEEN(SYSDATE, LAST_USED_DATTIM) >

    (select Pk_Utils.fnc_get_sys_par ( 'HOUSE_AUDIT') from dual)

    and I think scalar subquery caching will probably kick in -
    so you should only call the function once, and bypass any
    leakage problem. You'll also save a lot of CPU too, probably.

    Regards

    Jonathan Lewis
    http://jonathanlewis.wordpress.com

    Author: Cost Based Oracle: Fundamentals
    http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

    The Co-operative Oracle Users' FAQ
    http://www.jlcomp.demon.co.uk/faq/ind_faq.html
    ------------------------------

    Date: Wed, 20 Dec 2006 13:57:04 +0000
    From: "Norman Dunbar"
    Subject: Session PGA memory max exceeded 2 GB and crashes.


    Afternoon (UK time) all,

    Oracle 8174. (I know, I know !)
    HPUX 11.11.

    BEGIN
    Pk_Trace.ENTER ( 'Delete HAZ_RGBA_REG_BATCH_AUD records' );
    DELETE HAZ_RGBA_REG_BATCH_AUD
    WHERE MONTHS_BETWEEN(SYSDATE, LAST_USED_DATTIM) >
    Pk_Utils.fnc_get_sys_par ( 'HOUSE_AUDIT');
    COMMIT;
    Pk_Trace.LEAVE;
    EXCEPTION
    WHEN OTHERS THEN
    Pk_Trace.REPORT_ERROR;
    RAISE;
    END;
    >
  • Jonathan Lewis at Dec 21, 2006 at 9:15 am
    Prashant,

    Before recommending that link again, you might like to think carefully
    about the quality of the content. Here's just one small example of how
    erroneous it is:

    By segregating high activity tables into a separate, smaller data buffer,
    Oracle has far less RAM frames to scan for dirty block, improving the
    throughout and also reducing CPU consumption. This is especially
    important for super-high update tables with more than 100 row changes
    per second.

    Since Oracle 8.1, dirty blocks go on to the checkpoint queue the moment
    they are made dirty - and they stay in place on that queue until the database
    writer has written them; and DBWR picks them off the queue in the correct
    order without having to scan the buffer. That's why you see far fewer
    "checkpoint not complete" errors, and how the fast_start_mttr_target
    can be made to work.

    Regards

    Jonathan Lewis
    http://jonathanlewis.wordpress.com

    Author: Cost Based Oracle: Fundamentals
    http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

    The Co-operative Oracle Users' FAQ
    http://www.jlcomp.demon.co.uk/faq/ind_faq.html
    Date: Tue, 19 Dec 2006 22:51:58 -0800 (PST)
    From: Murtuja Khokhar
    Subject: RE: Multiple Block Size

    Prashant,
    www.dba-oracle.com/oracle_tips_multiple_blocksizes.htm

    --
    http://www.freelists.org/webpage/oracle-l
  • Norman Dunbar at Dec 21, 2006 at 9:55 am
    Morning Jonathan,
    In general you can expect the function to be called
    twice if you use it to identify an indexed access path
    (as in your later experiment, perhaps).
    id > function(const)
    You are correct. I didn't do an explain plan but now that I have it
    was indeed using an index. The function was called once. I added a full
    hint and the function was indeed called for each row in the table. It
    *is* looking more and more likely that we have a memory leak in the
    PJ/SQL - I shall have to delve deeper.
    However, for a tablescan - which this seems to be -
    the function is called once per row. (Until 10g where
    deterministic functions nearly work).
    The original query does indeed use a full scan. I should have made that
    clear in my original post.
    I would guess that you have a memory leak in the pj/sql.
    :o(
    Change the code to:
    WHERE MONTHS_BETWEEN(SYSDATE, LAST_USED_DATTIM) >
    (select Pk_Utils.fnc_get_sys_par ( 'HOUSE_AUDIT') from dual)
    and I think scalar subquery caching will probably kick in -
    so you should only call the function once, and bypass any
    leakage problem. You'll also save a lot of CPU too, probably.
    I have tested this and it does one call to the function. I shall see if
    I can get a test in the system and see how it goes.

    Thanks very much.

    Cheers,
    Norman.

    Norman Dunbar.
    Contract Oracle DBA.
    Rivers House, Leeds.

    Internal : 7 28 2051
    External : 0113 231 2051

    Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else.

    We have checked this email and its attachments for viruses. But you should still check any attachment before opening it.

    We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes.

    If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk
  • Jonathan Lewis at Dec 21, 2006 at 10:01 pm
    Apologies to Prashant for attributing to him the suggestion
    to read the article quoted below. The suggestion actually
    came from Murtuja.khokhar.

    Regards

    Jonathan Lewis
    http://jonathanlewis.wordpress.com

    Author: Cost Based Oracle: Fundamentals
    http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

    The Co-operative Oracle Users' FAQ
    http://www.jlcomp.demon.co.uk/faq/ind_faq.html

    Original Message -----
    From: "Jonathan Lewis"
    To:
    Sent: Thursday, December 21, 2006 9:15 AM
    Subject: Re: oracle-l Digest V3 #373
    Prashant,

    Before recommending that link again, you might like to think carefully
    about the quality of the content. Here's just one small example of how
    erroneous it is:

    " By segregating high activity tables into a separate, smaller data buffer,
    "
    " Oracle has far less RAM frames to scan for dirty block, improving the"
    " throughout and also reducing CPU consumption. This is especially "
    " important for super-high update tables with more than 100 row changes "
    " per second."

    Since Oracle 8.1, dirty blocks go on to the checkpoint queue the moment
    they are made dirty - and they stay in place on that queue until the database
    writer has written them; and DBWR picks them off the queue in the correct
    order without having to scan the buffer. That's why you see far fewer
    "checkpoint not complete" errors, and how the fast_start_mttr_target
    can be made to work.
    --
    http://www.freelists.org/webpage/oracle-l

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedDec 20, '06 at 1:57p
activeDec 21, '06 at 10:01p
posts15
users7
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase