Re: dbms_output and trigger

From: Mark C. Stock <mcstockX_at_Xenquery>
Date: Sat, 8 Oct 2005 05:13:44 -0400
Message-ID: <p9mdnVxAQMZQENreRVn-qg@comcast.com>

"DA Morgan" <damorgan_at_psoug.org> wrote in message news:1128698717.664108_at_yasure...

> Mark C. Stock wrote:

>> <sybrandb_at_yahoo.com> wrote in message
>> news:1128673533.967653.47990_at_g14g2000cwa.googlegroups.com...
>>
>>>As far as I discovered, before being flushed to stdout, the output goes
>>>to your PGA.
>>>If the output is never flushed, you can run out of PGA.
>>>That happened to me once.
>>>So you may think the output is going into a 'black hole', but it isn't,
>>>and it can potentially bite you.
>>>
>>>--
>>>Sybrand Bakker
>>>Senior Oracle DBA
>>>
>>
>>
>> I took a few minutes to test my assumptions about enabled vs not enabled
>> DBMS_OUTPUT
>>
>> From the docs (10g):
>>
>> --------------------------------------
>> ENABLE Procedure
>> This procedure enables calls to PUT, PUT_LINE, NEW_LINE, GET_LINE, and
>> GET_
>> LINES. Calls to these procedures are ignored if the DBMS_OUTPUT package
>> is not
>> enabled.
>> --------------------------------------
>>
>> So, presumably, your experience was with DBMS_OUTPUT enabled, which of
>> course would eventually give the 'ORU-10027: buffer overflow' error
>>
>> Without enabling DBMS_OUTPUT, this block appears to run forever, and the
>> PUT_LINE appears to do nothing
>>
>> begin
>> loop
>> dbms_output.put_line('endless loop');
>> end loop;
>> end;
>>
>> PGA usage does not change (based on 'session pga memory' in V$SESSTAT)
>>
>> But with DBMS_OUTPUT enabled, ORU-10027 comes quickly, and PGA usage
>> increases
>>
>> ++ mcs

>
> While your conclusion may well be valid it is not the entire story:
>
> SQL> set timing on
>
> SQL> BEGIN
>   2    FOR i IN 1 .. 200000 LOOP
>   3      dbms_output.put_line(i);
>   4    END LOOP;
>   5  END;
>   6  /
>
> PL/SQL procedure successfully completed.
>
> Elapsed: 00:00:00.26
> SQL> BEGIN
>   2    FOR i IN 1 .. 200000 LOOP
>   3      NULL;
>   4    END LOOP;
>   5  END;
>   6  /
>
> PL/SQL procedure successfully completed.
>
> Elapsed: 00:00:00.01
> /
>
> Test with loops of any size from 50000 up I see a cost to
> dbms_output.put_line even when it is not enabled.
> -- 
> Daniel A. Morgan
> http://www.psoug.org
> damorgan_at_x.washington.edu
> (replace x with u to respond)

i guess i didn't hit the threshold -- enabled, i maxed the buffer w/in a couple seconds, so non enabled a ran for about 10 - 20 seconds, but didn't accumulate any counters.

how does the cost compare to the # of calls and size of allegedly ignored output?

++ mcs Received on Sat Oct 08 2005 - 04:13:44 CDT