Re: dbms_output and trigger

From: DA Morgan <damorgan_at_psoug.org>
Date: Fri, 07 Oct 2005 08:25:27 -0700
Message-ID: <1128698717.664108@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)

Received on Fri Oct 07 2005 - 10:25:27 CDT