Oracle Magazine - September/October 2011

Code Listing 5: Function result cache solution

SQL> create or replace function f( x in varchar2 ) return number

2 RESULT_CACHE 3 as

4 begin

5 dbms_application_info.set_client_info
(userenv('client_info')+ 1 );
6 return length(x);

7 end;

8 / Function created.

SQL> begin

2 :cpu := dbms_utility.get_cpu_time;

3 dbms_application_info.set_client_info(0);

4 end;

5 / PL/SQL procedure successfully completed.

SQL> select owner, f(owner) from stage;

…

72841 rows selected.

settings. Could you explain CACHE and NOCACHE with an example?

Sure. In short, if you use NOCACHE, every time you call sequence.nextval, you will cause a table named SYS.SEQ$ to be updated and committed. Every single time. If, on the other hand, you use the default value, CACHE 20, you will cause SYS.SEQ$ to be updated and committed every 20th time you call sequence.nextval.

It is a performance thing. If you don’t cache the next couple of sequence values in the system global area (SGA), you will be doing an UPDATE and COMMI T every single time you call nextval. You can evaluate the performance impact this might have on your application—especially with large data loads—if the cache value is set too low. For example, let’s create three sequences

SQL> select

2 dbms_utility.get_cpu_time-:cpu cpu_hsecs, 3 userenv('client_info')

4 from dual;

SQL> create sequence nocache_seq NOCACHE; Sequence created.

CPU_HSECS USERENV('CLIENT_INFO')

—————————————— ————————————————————————————————

73 32

to get the performance benefit of the scalar subquery cache.

IS THE CURSOR DYING?

With subquery factoring, MODEL, PIVOT, MERGE with DELETE, “Materialize” and other hints, global temporary tables, DML (data manipulation language) error logging, and so many other powerful new SQL features, when is it necessary to use cursors (apart from using ref cursors for reports). Is the cursor dying?

Well, the simple UPDATE statement

update t set x = 5;

requires a cursor. In fact, every SELECT you
execute, every DELETE you perform—pretty
much everything—requires a cursor. So it is
doubtful that cursors are “dying.”
However, I think you may mean, “Is the
need to procedurally process the output of
a cursor dying?” Many times (many, many,
many times), a ton of procedural code can
be replaced with a single SQL statement.
That has been true for a long time and is
truer today than ever with features such as
DML error logging.

SEQUENCE CACHING

I read about sequence caching, but I still do not understand the CACHE and NOCACHE

SQL> create sequence default_cache_seq; Sequence created.

SQL> create sequence big_cache_seq cache 100000; Sequence created.

and a table to test with

SQL> create table t ( x int ); Table created.

Now let’s execute the following block of code with SQL trace enabled for each of the three sequences—replace & 1 with the actual sequence name (nocache_seq, default_cache_seq, or big_cache_seq) as you run them: