Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Peter.

Asked: December 01, 2016 - 6:56 am UTC

Last updated: December 04, 2016 - 1:10 pm UTC

Version: 12.1.0.2.0

Viewed 1000+ times

You Asked

Say I have a table:
create table emp(
empid number(5),
name varchar2(30),
photo blob
);

create or replace procedure get_photo(p_empid in number, p_name out varchar2, p_photo OUT BLOB) as
begin
select name, photo
into p_name, p_photo
from emp
where empid = p_empid;
exception when no_data_found then null;
end;
/

create or replace procedure get_photo2(p_empid in number, p_name out varchar2, p_photo OUT NOCOPY BLOB) as
begin
select name, photo
into p_name, p_photo
from emp
where empid = p_empid;
exception when no_data_found then null;
end;
/

Is adding NOCOPY hint in the second procedure more efficient than simply "OUT BLOB" ?

Does the first procedure copies the blob contents into a temporary buffer
and then copies from buffer into the OUT parameter upon exit.
Or is it only the LOB locator that is copied ?

and Connor said...

We can do a little benchmark to see if there are any differences - with a nice big 1GB clob.

SQL> create table t(
  2  photo clob
  3  );

Table created.

SQL> create or replace procedure get_photo( p_photo OUT cLOB) as
  2  begin
  3    select  photo
  4    into p_photo
  5    from t;
  6  end;
  7  /

Procedure created.

SQL> create or replace procedure get_photo2( p_photo OUT NOCOPY cLOB) as
  2  begin
  3    select  photo
  4    into p_photo
  5    from t;
  6  end;
  7  /

Procedure created.

SQL> declare
  2    c clob;
  3  begin
  4    dbms_lob.createtemporary(c,true);
  5    for i in 1 .. 100000 loop
  6       dbms_lob.writeappend(c,10000,rpad('x',10000,'x'));
  7    end loop;
  8    insert into t values (c);
  9    commit;
 10  end;
 11  /

PL/SQL procedure successfully completed.

SQL> select dbms_lob.getlength(photo) from t;

DBMS_LOB.GETLENGTH(PHOTO)
-------------------------
               1000000000

SQL> conn connor/connor
Connected.

SQL> variable c2 clob
SQL> exec get_photo2(:c2);

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
SQL> @stat
Enter value for stat_prefix: pga

 SID_STAT# NAME                                                    VALUE
---------- -------------------------------------------------- ----------
        37 session pga memory                                    1311640
        38 session pga memory max                                2508344

2 rows selected.

SQL> conn connor/connor
Connected.

SQL> variable c1 clob
SQL> exec get_photo(:c1);

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
SQL> @stat
Enter value for stat_prefix: pga

 SID_STAT# NAME                                                    VALUE
---------- -------------------------------------------------- ----------
        37 session pga memory                                     983960
        38 session pga memory max                                2508344

2 rows selected.



We can see in either case, it was very fast and minimal PGA consumption


Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Procedure Stat

A reader, December 02, 2016 - 5:54 am UTC

Nice demo. So better is without nocopy.

Can have a link or a past of the STAT proc?

Connor McDonald
December 04, 2016 - 1:10 pm UTC

stat is just statistics for a session, or "my" for my session.

<code>
undefine sid

set verify off
col name format a50
set lines 100
select
--st.sid,
decode('&&sid','my',s.STATISTIC#,st.sid) sid_stat#,
s.name, st.value
from v$statname s, v$sesstat st
where st.STATISTIC# = s.STATISTIC#
and st.sid = nvl(to_number(
decode('&&sid','my',sys_context('USERENV','SID'),'','','&&sid')
),st.SID)
and s.name like '%'||nvl('&stat_prefix',s.name)||'%';

undefine sid

<code>

More to Explore

CLOB/BLOB/etc

Complete documentation on Securefiles and Large Objects here