Skip to Main Content

Breadcrumb

Connor and Chris will both be at AI World from October 12 to October 17 , the premier Oracle conference of 2025. If you're in Vegas, please come say Hi or pop into our sessions

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

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

CLOB/BLOB/etc

Complete documentation on Securefiles and Large Objects here