Oracle Tips and Tricks — David Fitzjarrell

May 30, 2008

Feelin’ bad, Feelin’ blue, Gots me a ORA-01002

Filed under: General — dfitzjarrell @ 18:45

Most application programmers do their utmost to make their code as efficient as possible. Of course sometimes that means doing things that, at first blush, seem eminently appropriate but end up as, well, disasters by producing errors which aren’t quite understood. A good case in point is the ORA-01002 error. Oracle states this is a ‘fetch out of sequence’, but what, exactly, does that mean? Let’s look at a PL/SQL example and find out.

I’ve written before about committing inside of a loop but the ORA-01555 isn’t the only error that can cause. Depending upon how the cursor is written it could throw an ORA-01002 in your face:

SQL> --
SQL> -- "The Road to Hell is paved with
SQL> -- good intentions"
SQL> --
SQL> -- The pavement starts here...
SQL> --
SQL> -- We've opened a cursor for update
SQL> -- then do the dastardly deed of
SQL> -- committing inside the loop
SQL> --
SQL> -- When a commit is issued during a
SQL> -- select ... for update transaction
SQL> -- the cursor is closed
SQL> --
SQL> -- So, we can't fetch any more
SQL> -- records
SQL> --
SQL> -- But, we try anyway
SQL> --
SQL> declare
  2          --
  3          -- This is perfectly fine
  4          -- provided we don't commit
  5          -- somewhere in the loop
  6          --
  7          cursor get_emp_info is
  8          select empno, ename, sal, comm
  9          from emptest
 10          for update;
 12          --
 13          -- This is a bad omen indicating
 14          -- we're keeping track of how many
 15          -- records we've updated
 16          --
 17          -- I see an interim commit looming
 18          -- on the horizon
 19          --
 20          ctr number:=0;
 22  begin
 23          --
 24          -- So far, so good
 25          --
 26          -- We fetch the data from the cursor
 27          -- and go off on our merry way
 28          --
 29          for emprec in get_emp_info loop
 31                  --
 32                  -- And we update the data ...
 33                  --
 34                  update emptest set sal = sal + (emprec.sal*.1), comm = emprec.sal*.2
 35                  where empno = emprec.empno;
 37                  --
 38                  -- And we keep count ...
 39                  --
 40                  ctr := ctr+1;
 42                  if ctr > 4 then
 43                          --
 44                          -- And we kiss it goodbye
 45                          --
 46                          -- This causes the ORA-01002
 47                          --
 48                          commit;
 49                  end if;
 51          end loop;
 53          --
 54          -- We'll never get here
 55          -- because of what we did
 56          -- back there
 57          --
 58          commit;
 60  end;
 61  /
ERROR at line 1:
ORA-01002: fetch out of sequence
ORA-06512: at line 29


Note that the cursor is written as a select … for update, a special sort of select which, when met with either a commit or a rollback will close. Unceremoniously. And you won’t know it’s happened until you see that ugly ORA-01002 staring you in the face:

ERROR at line 1:
ORA-01002: fetch out of sequence
ORA-06512: at line 29

What this means is that you’ve tried to fetch records from a now-invalid cursor, one where no data is available. Of course the goal was to conserve on rollback/undo space by releasing the currently occupied blocks to make room for the next ‘block’ of data to be modified. Unfortunately the cursor was closed with the commit (or rollback) and there was no ‘next block’ of records to modify. Oracle tried dutifully to honor your request, but fought against itself in doing so, raising the ‘fetch out of sequence’ error. And the problem is resolved by not committing inside the loop. Oracle does a fine job all by itself of managing rollback/undo segments so additional efforts by the application programmers are not necessary.

This same error can raise its ugly head in Java, Pro*C/C++ and OCI programs for similar reasons, and also for attempting to fetch beyond the last row in the result set (which, by the way, is something you can do in PL/SQL because it won’t raise an exception; it will simply keep fetching the same last row over and over and over and over and …) Sometimes good intentions create more problems than they attempt to solve.

Commitment is good. Just not inside of a FOR loop.

May 29, 2008

Room With A View

Filed under: General — dfitzjarrell @ 20:06

Occasionally the question of which objects reference which table pops up, and it isn’t as difficult a question to answer as one might think. Oracle has done a lot of the work for you and has supplied the solution in three views:


Looking at the definition of ALL_DEPENDENCIES we see:

SQL> desc all_dependencies
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 OWNER                                     NOT NULL VARCHAR2(30)
 NAME                                      NOT NULL VARCHAR2(30)
 TYPE                                               VARCHAR2(17)
 REFERENCED_OWNER                                   VARCHAR2(30)
 REFERENCED_NAME                                    VARCHAR2(64)
 REFERENCED_TYPE                                    VARCHAR2(17)
 REFERENCED_LINK_NAME                               VARCHAR2(128)
 DEPENDENCY_TYPE                                    VARCHAR2(4)


[The USER_DEPENDENCIES view differs only in the absence of the OWNER column.]

Notice that we can find pretty much any object that references another object from this view. The query is fairly simple:

SQL> select owner, name
  2  from all_dependencies
  3  where referenced_type = 'TABLE'
  4  and referenced_name = 'HS$_BASE_DD'
  5  /

OWNER                          NAME
------------------------------ ------------------------------
SYS                            HS_ALL_DD
SYS                            HS_INST_DD
SYS                            HS_CLASS_DD
SYS                            DBMS_HS_UTL
SYS                            DBMS_HS_CHK
SYS                            DBMS_HS_ALT
SYS                            DBMS_HS
SYS                            HS_BASE_DD

8 rows selected.


Voila! The answer appears with nary a strain on the typing fingers. Simply copy that query into a text editor, change the referenced type (if necessary) and change the referenced_name and you’re off to the races. You could even make those two parameters passable and let SQL*Plus prompt you for the values:

select owner, name
     from dba_dependencies
     where referenced_type = upper('&1')
     and referenced_name = upper('&2');


Such information is invaluable when modifications to a table are necessary, as you can track down the packages/procedures/functions/triggers/tables/views referencing the soon-to-be-modified table to allow you to effectively recompile those objects (or re-code the package/procedure/function) to reduce downtime due to a referenced object being marked INVALID.

I tell you, it’s pretty slick that Oracle provides the information you need in a form you can use.


May 28, 2008

Let My Data Go!

Filed under: General — dfitzjarrell @ 13:22

I’ve written here regarding Oracle locks but apparently the post didn’t explain enough about the views involved. Let’s fix that.

Two views are probably the most beneficial when investigating locks:


with V$LOCK providing an overall view of the active locks in a database and V$LOCKED_OBJECT providing detail on who has TM (DML) locks against which database objects.

Looking at V$LOCK to see what information is available we see that this view provides:

 the lock address (KADDR)
 the SID of the session holding the lock (SID)
 the type of lock (TYPE) (including two additional columns [ID1,ID2] which
                          can further identify the lock)
 the lock mode (LMODE)
 the requested mode (REQUEST)
 the time since the current mode was granted (CTIME)
 whether or not the lock is blocking another session (BLOCK)

The TYPE column describes whether this is a user or a system lock. The three user-type locks are

TM — DML enqueue [INSERT/UPDATE/DELETE transactions]
TX — Transaction enqueue [possibly DDL locks on an object]
UL — User supplied/defined [created by the DBMS_LOCK package]

System locks are usually held for a short duration, although there are exceptions, such as the MR and AE lock types. These lock types are

AE        Application Edition locks  (11g Edition Based Redefinition****)
BL        Buffer hash table instance
CF        Control file schema global enqueue
CI        Cross-instance function invocation instance (RAC, OPS, single-instance PQ*)
CU        Cursor bind*****
DF        datafile instance
DL        Direct loader parallel index create
DM        Mount/startup db primary/secondary instance
DR        Distributed recovery process
DX        Distributed transaction** entry
FS        File set
HW        Space management operations on a specific segment
IN        Instance number
IR        Instance recovery serialization global enqueue
IS        Instance state
IV        Library cache invalidation instance
JQ        Job queue
KK        Thread kick
LA .. LP  Library cache lock instance lock (A..P = namespace)
MM        Mount definition global enqueue
MR        Media recovery
NA..NZ    Library cache pin instance (A..Z = namespace)
PF        Password File
PI, PS    Parallel operation
PR        Process startup*******
QA..QZ    Row cache instance (A..Z = cache)
RT        Redo thread global enqueue
SC        System change number instance
SM        SMON
SN        Sequence number instance
SQ        Sequence number enqueue
SS        Sort segment
ST        Space transaction enqueue
SV        Sequence number value
TA        Generic enqueue
TS        New block allocation enqueue (ID2=1)
TS        Temporary segment enqueue (ID2=0)
TT        Temporary table enqueue
UN        User name
US        Undo segment DDL***
WL        Being-written redo log enqueue******

[The SM lock for SMON is acquired when the process ‘wakes up’ to check for work it has to do; the lock is taken as exclusive then converted to shared when the check is complete. This happens every time SMON becomes active, which is usually every five minutes. The lock is only active for a short period of time (one, maybe two seconds on average) and usually is not one worthy of concern.]

* The CI enqueue is used not only in RAC and OPS configurations but also by the parallel query coordinator. As an example before a direct read operation the reader process uses a CI call to DBWR to request a checkpoint of all dirty blocks in the cache belonging to the segment about to be read.

** The DX lock is set when single-master or multi-master replication is configured and active or when transactions span a database link, such as inserts and updates to a local table using data from a remote site. The DR lock is set when recovering from failed transactions involving two-phase commits (usually found in environments where replication as described at the beginning of this paragraph is used).

*** The US lock is set to serialize DDL (or processes(+) that invoke DDL) against UNDO segments, specifically:

SMON – abortive offline cleanup

There is nothing to ‘fix’ with this lock as Oracle is managing the UNDO segments and preventing any access to them until any DDL or process in the above list has completed.

**** The AE lock is created for each user session connected to the database provided edition-based redefinition is enabled/installed. Each user session has one AE lock in shared mode, waiting to be pressed into service using the “ALTER SESSION SET EDITION=<edition_name>;” statement. This presumes the default database edition has been set by the DBA using “ALTER DATABASE DEFAULT EDITION = <edition_name>;”. Editions allow developers to change database object definitions in a private environment while the application is still in use, reserving the changes until the edition is put into service at the database level. More information can be found here.

***** The CU lock protects the bind variable definitions during cursor optimization. These locks tend to be used when cursor_sharing is set to a value other than EXACT (but the behaviour is version-dependent). In 8i they are allocated one lock per bind, and in 9i and later releases it appears they are allocated one lock per cursor. These locks should not be a performance problem as they are not held for long periods of time (the bind and optimization steps usually are fast operations, even in systems experiencing high levels of traffic).

****** The WL lock protects the current redo log from concurrent writes since redo logs are written sequentially. This lock may be seen frequently but it should not be a long-standing lock nor should it impede performance. Of course the size of your redo logs and the frequency in which they switch can impact performance especially when running in NOARCHIVELOG mode which may make the WL locks more visible.

******* The PR lock protects memory structures while a process starts and may not be visible in V$LOCK unless an issue arises which prevents a process from successfully starting. PR locks held for a considerable amount of time will need to be investigated as to which process is holding the lock and what is preventing the process from completing its startup. The alert log is the best place to begin such an investigation.

The ID1 and ID2 columns provide varying information from the OBJECT_ID for the object involved (for TM locks — ID1) to additional information differentiating multiple levels of a given lock type (as in the TS lock listed above). Since the ID1 column can contain information other than the id of the locked object it’s better to use the V$LOCKED_OBJECT view to investigate who has which database objects locked.

In the case of UL locks ID1 reports the lock handle used to generate and manage the lock through the DBMS_LOCK package:

SQL> select * From v$lock;

ADDR     KADDR           SID TY        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK
-------- -------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
2EC34228 2EC34254        160 XR          4          0          1          0      17231          0
2EC3429C 2EC342C8        160 CF          0          0          2          0      17228          0
2EC34384 2EC343B0        160 RS         25          1          2          0      17223          0
2EC343F8 2EC34424        162 PW          1          0          3          0      17219          0
2EC3446C 2EC34498        161 RT          1          0          6          0      17223          0
2EC344E0 2EC3450C        170 AE         99          0          4          0      17120          0
2EC34554 2EC34580        128 AE         99          0          4          0        131          0
2EC345C8 2EC345F4        146 AE         99          0          4          0      17209          0
2EC3463C 2EC34668        162 MR          1          0          4          0      17223          0
2EC346B0 2EC346DC        162 MR          2          0          4          0      17223          0
2EC34724 2EC34750        162 MR          3          0          4          0      17223          0

ADDR     KADDR           SID TY        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK
-------- -------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
2EC34798 2EC347C4        162 MR          4          0          4          0      17223          0
2EC3480C 2EC34838        162 MR          5          0          4          0      17223          0
2EC34880 2EC348AC        162 MR        201          0          4          0      17223          0
2EC348F4 2EC34920        155 AE         99          0          4          0      17120          0
2EC34968 2EC34994        159 TS          3          1          3          0      17218          0
2EC349DC 2EC34A08        157 AE         99          0          4          0      17209          0
2EC34A50 2EC34A7C        128 UL 1073742010          0          6          0        128          0
2EC34AC4 2EC34AF0        139 AE         99          0          4          0      17120          0
2EC34B38 2EC34B64        136 AE         99          0          4          0      17113          0
2EC34BAC 2EC34BD8        140 AE         99          0          4          0      16985          0
2EC34C20 2EC34C4C        137 AE         99          0          4          0      16978          0

ADDR     KADDR           SID TY        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK
-------- -------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
2EC34C94 2EC34CC0        129 AE         99          0          4          0        106          0

23 rows selected.


In the case of RS (reclaimable space) and TS (temporary space) locks ID2 provides the lock level; there may be other lock types that populate the ID2 column with non-zero values.

As noted in the list the CTIME column lists the time since the current mode was granted; as expected if the lock mode changes then the CTIME counter resets and starts counting from that point forward until the next mode change.

V$LOCKED_OBJECT provides the following for all DML locks (type TM) in the database:

 the undo segment number (XIDUSN)
 the slot number (XIDSLOT)
 the sequence number (XIDSQN)
 the object identifier (OBJECT_ID)
 the session identifier (SESSION_ID)
 the username (ORACLE_USERNAME)
 the O/S user (OS_USER_NAME)
 the O/S process id (PROCESS)
 the mode (LOCKED_MODE)

The slot and sequence number apply to the undo segment assigned to each lock and the locked mode is a number spanning the range from 0 through 6, identified as follows:

0 — no lock held
1 — NULL
2 — Row share lock (SS)
3 — Row exclusive (SX)
4 — Shared (S)
5 — S/Row-exclusive (SSX)
6 — Exclusive (X)

The above values apply to both the V$LOCK and V$LOCKED_OBJECT views.

How, then, can these views be used to report who has which objects locked and in what modes? The following query can be run:

set linesize 200 pagesize 60 trimspool on
column username format a10
column osuser format a10
column sid format 99999
column object format a35
column type format a35

select s.username, s.sid, s.serial#,
       s.osuser, k.ctime, o.object_name object, k.kaddr,
case l.locked_mode when 1 then 'No Lock'
                   when 2 then 'Row Share'
                   when 3 then 'Row Exclusive'
                   when 4 then 'Shared Table'
                   when 5 then 'Shared Row Exclusive'
                   when 6 then 'Exclusive'
end locked_mode,
when k.type = 'AE' then 'Application Edition'
when k.type = 'BL' then 'Buffer Cache Management (PCM lock)'
when k.type = 'CF' then 'Controlfile Transaction'
when k.type = 'CI' then 'Cross Instance Call'
when k.type = 'CU' then 'Bind Enqueue'
when k.type = 'DF' then 'Data File'
when k.type = 'DL' then 'Direct Loader'
when k.type = 'DM' then 'Database Mount'
when k.type = 'DR' then 'Distributed Recovery'
when k.type = 'DX' then 'Distributed Transaction'
when k.type = 'FS' then 'File Set'
when k.type = 'IN' then 'Instance Number'
when k.type = 'IR' then 'Instance Recovery'
when k.type = 'IS' then 'Instance State'
when k.type = 'IV' then 'Library Cache Invalidation'
when k.type = 'JQ' then 'Job Queue'
when k.type = 'KK' then 'Redo Log Kick'
when k.type like 'L%' then 'Library Cache Lock'
when k.type = 'MM' then 'Mount Definition'
when k.type = 'MR' then 'Media Recovery'
when k.type like 'N%' then 'Library Cache Pin'
when k.type = 'PF' then 'Password File'
when k.type = 'PI' then 'Parallel Slaves'
when k.type = 'PR' then 'Process Startup'
when k.type = 'PS' then 'Parallel slave Synchronization'
when k.type like 'Q%' then 'Row Cache Lock'
when k.type = 'RT' then 'Redo Thread'
when k.type = 'SC' then 'System Commit number'
when k.type = 'SM' then 'SMON synchronization'
when k.type = 'SN' then 'Sequence Number'
when k.type = 'SQ' then 'Sequence Enqueue'
when k.type = 'SR' then 'Synchronous Replication'
when k.type = 'SS' then 'Sort Segment'
when k.type = 'ST' then 'Space Management Transaction'
when k.type = 'SV' then 'Sequence Number Value'
when k.type = 'TA' then 'Transaction Recovery'
when k.type = 'TM' then 'DML Enqueue'
when k.type = 'TS' then 'Table Space (or Temporary Segment)'
when k.type = 'TT' then 'Temporary Table'
when k.type = 'TX' then 'Transaction'
when k.type = 'UL' then 'User-defined Locks'
when k.type = 'UN' then 'User Name'
when k.type = 'US' then 'Undo segment Serialization'
when k.type = 'WL' then 'Writing redo Log'
when k.type = 'XA' then 'Instance Attribute Lock'
when k.type = 'XI' then 'Instance Registration Lock'
 end type
from v$session s, sys.v_$_lock c, sys.v_$locked_object l, dba_objects o, sys.v_$lock k
where o.object_id = l.object_id
and l.session_id = s.sid
and k.sid = s.sid
and s.saddr = c.saddr
and k.kaddr = c.kaddr
and k.lmode = l.locked_mode
and k.lmode = c.lmode
and k.request = c.request
order by object;

This query will run on 9iR2 and later releases — earlier versions of Oracle require DECODE statements and I’ll leave it to you to make any changes for Oracle 8.1.7 and earlier releases.

Sessions holding locks blocking other sessions can be reported as well:

with blocked as (
 select sid blocked, serial#, username, blocking_session
 from v$session
 where blocking_session is not null
blocking as (
 select sid blocking, serial# bl_serial#, username bl_username
 from v$session
obj_info as (
 select l.session_id, o.object_name, l.object_id,
 decode(l.locked_mode,   1, 'No Lock',
        2, 'Row Share',
        3, 'Row Exclusive',
        4, 'Shared Table',
        5, 'Shared Row Exclusive',
        6, 'Exclusive') locked_mode
 from v$locked_object l, dba_objects o
 where o.object_id = l.object_id
select blocked, serial#, username, blocking, bl_serial#, bl_username, session_id, object_name, object_id, locked_mode
from blocked, blocking, obj_info
where blocking = blocking_session
and session_id = blocking_session;

Again, this runs on 9iR2 and later releases of Oracle which provide the subquery factoring syntax (the WITH clause).

Oracle provides another script to output a ‘tree’ structure of the locks held in a database, found in $ORACLE_HOME/rdbms/admin, named utllockt.sql. This particular script reports the locks which are blocking other sessions. It uses the CONNECT BY syntax to produce a tree-like output; an example is shown in the internal documentation in the script.

So what to do should you find a blocking lock? That depends on the type of application you’re running. It may be as simple as contacting the user with the blocking lock and asking him or her to commit or rollback the changes he or she has made. Or, if they’ve done the unthinkable, assuming the session was hung, and clicked on that little red x to kill the application, you may need to simply kill the session which remains. There is no ‘cut and dried’, ‘one-size-fits-all’ solution.

So, locks aren’t the mystery some might think, and investigating them (with these and other scripts/queries available on the web) isn’t a thankless, convoluted task. Investigation does take some patience, however, as it may require the output from several scripts to determine where the problems lie. I’ve provided some starting points and I suggest you ‘google’ for additional scripts other DBAs find useful. Hopefully I’ve provided enough information to point you in the right direction so you won’t be like the Scarecrow in L. Frank Baum’s “The Wizard Of Oz” and not know which way is up. Or down.

Now, go investigate those locks.

May 19, 2008

That’s A Keeper

Filed under: General — dfitzjarrell @ 13:25

The conventional ‘wisdom’ on the Internet regarding logical I/O is that such memory reads are ‘free’, which discounts (drastically) the cost of processing such reads [the CPU cost can be quite high on an active system, making such reads far from ‘free’]. Of course that’s not to say that logical I/O isn’t far faster than physical I/O; it is by a rather large factor. And that factor can be a boon to developers and end users alike, allowing results to return much faster than would be possible with a mixture of physical and logical I/O calls.

So, how does one ‘cache in’ (pun intended here) on this? By configuring the db_keep_cache_size parameter in Oracle 9i and later releases and by altering oft-queried tables and/or indexes to use the KEEP buffer pool. Properly sizing this parameter setting can dramatically improve query throughput, although it shouldn’t be used indiscriminantly.

What, exactly, does the KEEP buffer pool do? When using automatic SGA management (by setting sga_target, sga_max_size or both in the init.ora or spfile) this pool is dynamically allocated; it only needs to have its cache configured and tables altered to use this pool rather than the DEFAULT buffer pool (the … ummm … ‘default’ if you don’t specify which pool to use). Once used the blocks in the KEEP pool don’t get aged out by time; of course if the cache isn’t large enough to contain all of the tables configured to use it then blocks will be replaced and physical I/O will be necessary to replace them as the configured tables are queried — Table A and Table B are both configured to use the KEEP pool, the keep cache is sized to hold either all of Table A or all of Table B, but not both, so queries against Table A will cause the cache to load with those blocks, and unfortunately if Table B is queried thereafter those blocks will replace those from Table A and basically result in no better performance than using the DEFAULT pool, which is not the intended result.

The KEEP cache should be sized a bit larger than the sum of all of the tables configured to use it; the sizing is in blocks, so a simple query of the USER_TABLES view reporting the sum of the BLOCKS column for all tables using the KEEP pool should provide an excellent starting point:

SQL> select sum(blocks)
  2  from user_tables
  3  where buffer_pool  = 'KEEP'
  4  /


Knowing the granule size is also important, as that will be the value by which the cache is incremented or decremented; dividing the granule size by the db_block_size produces the number of blocks each adjustment will consume:

SQL> select granule_size/value
  2  from v$sga_dynamic_components, v$parameter
  3  where name = 'db_block_size'
  4  and component like 'KEEP%'
  5  /



Setting the db_keep_cache_size to anything less than 512 blocks will automatically allocate 512 blocks since the memory allocations are made in granule-sized increments when using automatic SGA management. Each adjustment up or down will add or subtract multiples of 512 blocks (at least in this case) to the cache. On my system the minimum value to which I can set db_keep_cache_size is 32 MB; any smaller value I might use will automatically configure the cache at 32 MB. The minimum size is dependent upon how many CPUs are installed and the granule size for the database in question. The formula for sizing this pool is

number of CPUs * granule size

Looking at the block total for all tables configured to use the KEEP pool we see the figure is larger than one granule, so we should set this parameter to at least 1024 (or a value larger than 512 to allow Oracle to ‘bump it up’ automatically to the next granule). Of course Oracle will most likely ‘bump this up’ to the minimum value obtained by the above calculation, but you won’t know that until you query V$BUFFER_POOL to obtain the current size. In any case you should have sufficient space in the KEEP pool using the default calculation. Oh, and any adjustment above the minimum will be added in granule-sized allocations, so attempting to increase this cache to 33 MB will result in a cache 36 MB in size.

How do you know the blocks remain in the cache? Using autotrace tells the tale:

Session 1:

SQL> alter table mytab storage(buffer_pool keep);

Table altered

SQL> select * from mytab;

[... lots of data here ...]

        239  recursive calls
          0  db block gets
       2964  consistent gets
        513  physical reads
          0  redo size
    2696838  bytes sent via SQL*Net to client
      20252  bytes received via SQL*Net from client
       2860  SQL*Net roundtrips to/from client
          6  sorts (memory)
          0  sorts (disk)
      42880  rows processed

SQL> select * from test;

[... lots of data here ...]

        705  recursive calls
          3  db block gets
        831  consistent gets
        146  physical reads
        632  redo size
     668321  bytes sent via SQL*Net to client
       5216  bytes received via SQL*Net from client
        712  SQL*Net roundtrips to/from client
          7  sorts (memory)
          0  sorts (disk)
      10663  rows processed

Session 2:

SQL> select * from mytab;

[... lots of data here ...]

        239  recursive calls
          0  db block gets
       3477  consistent gets
          0  physical reads
          0  redo size
    2696838  bytes sent via SQL*Net to client
      20252  bytes received via SQL*Net from client
       2860  SQL*Net roundtrips to/from client
          6  sorts (memory)
          0  sorts (disk)
      42880  rows processed

Notice that Session 2 had nothing but consistent gets for the query of mytab, even though Session 1 had also queried the table TEST, indicating that the KEEP pool is doing its job of keeping data blocks cached. Had Session 1 queried mytab again, after querying TEST, the same statistics would be generated (nothing but consistent gets for the query). Which makes this a convenient way to ensure often queried tables remain in cache for faster access.

Of course all of this is predicated on the system in question having sufficient memory to run the database, the requisite operating system processes and still have enough left to configure the KEEP cache without having to use swap space. Having to swap parts of the KEEP cache to and from disk defeats the purpose of configuring the pool in the first place, as you’ll likely end up with far worse performance than if you hadn’t used it (the KEEP pool/cache) at all.

Which brings up the question: ‘How large is the KEEP pool currently?’ We answer that with a trip through V$BUFFER_POOL:

SQL> select name, current_size
  2  from v$buffer_pool;

NAME                 CURRENT_SIZE
-------------------- ------------
KEEP                           32
DEFAULT                       488


CURRENT_SIZE ie reported in megabytes so in this case the KEEP pool consumes 32 megabytes of memory. (As mentioned earlier on this system 32 MB is the minimum size this cache will attain.) Of course it may not be set currently and, in that situation, no entry will be reported:

SQL> select name, current_size
  2  from v$buffer_pool;

NAME                 CURRENT_SIZE
-------------------- ------------
DEFAULT                       520


Statistics for the pools are viewable in, you guessed it, the V$BUFFER_POOL_STATISTICS view:

SQL> desc v$buffer_pool_statistics
 Name                                   Null?    Type
--------------------------------------- -------- ------------------
 ID                                              NUMBER
 NAME                                            VARCHAR2(20)
 BLOCK_SIZE                                      NUMBER
 SET_MSIZE                                       NUMBER
 CNUM_REPL                                       NUMBER
 CNUM_WRITE                                      NUMBER
 CNUM_SET                                        NUMBER
 BUF_GOT                                         NUMBER
 SUM_WRITE                                       NUMBER
 SUM_SCAN                                        NUMBER
 FREE_BUFFER_WAIT                                NUMBER
 WRITE_COMPLETE_WAIT                             NUMBER
 BUFFER_BUSY_WAIT                                NUMBER
 FREE_BUFFER_INSPECTED                           NUMBER
 DIRTY_BUFFERS_INSPECTED                         NUMBER
 DB_BLOCK_CHANGE                                 NUMBER
 DB_BLOCK_GETS                                   NUMBER
 CONSISTENT_GETS                                 NUMBER
 PHYSICAL_READS                                  NUMBER
 PHYSICAL_WRITES                                 NUMBER


You can monitor the overall pool usage, although you cannot monitor (through this view) whether or not a particular table in that pool is being accessed. But, as shown earlier, using autotrace can reveal that information.

Which tables are good candidates for the KEEP pool/cache? That’s up to the DBA to decide, really. Monitoring table access (through auditing) or through regular use of Statspack reports (examining the Top SQL by elapsed time) or, if you’re licensed, AWR reports,┬ácan provide a list of regularly accessed tables and the benefits can be weighed against the resource cost (RAM required) to determine which tables could be using the KEEP pool/cache. Heavily updated tables (such as call detail record tables for telecommunications companies) should probably not use the KEEP pool as the blocks in cache will need to be refreshed resulting in physical I/O (which you wanted to reduce or avoid). I say ‘probably not’ because it may be that just such a table could benefit from using the KEEP pool/cache; each situation is different and should be evaluated as such. In all cases I stress that you should:



Test again

and determine from those tests how much benefit, if any, this configuration may provide.

Yes, you can update values in tables configured to use the KEEP pool; there is nothing preventing that from occurring. Remember, though, that wholesale updates/inserts to tables using the KEEP pool will cause those tables to be refreshed at the next query so you’ll probably lose performance, if even slightly, because of this.

Configuring and using the KEEP cache/pool can be beneficial in some circumstances; using it may just help you improve application performance by keeping often used tables in memory (as much as is possible) thus reducing the physical I/O calls. Of course your mileage may vary, so as I said before:



Test again

to verify this is a viable option, as what may be good for one installation may be terribly, horribly wrong for another.

Who knows? You may just find a ‘keeper’.

May 2, 2008

"I’ve never seen THAT error before …"

Filed under: General,pl/sql — dfitzjarrell @ 14:57

It would appear that the glorious error ORA-20999 appears quite frequently in application code, as evidenced by the history of searches on the Internet for this error number. Unfortunately for the person searching endlessly for this error it’s not a standard Oracle offering. Instead, it’s a user-defined exception/error number to catch and report any of a plethora of unnamed Oracle exceptions, which makes finding a definitive answer for what this error represents practically impossible, as it means what the application programmer intended, which can, and does, vary between application programmers and applications. Let’s look at the valid range of user-definable error numbers/exceptions and try to clear the clouded air a bit.

Oracle offers a range of error numbers which are not assigned any standard Oracle error text and are not associated with any fixed Oracle exceptions; this range starts at 20000 and ends at 20999. Looking at a basic PL/SQL block to define and use some of these available error numbers it can be seen that these can either be quite useful or quite frustrating:

SQL> --
SQL> -- User defined errors are numbered
SQL> -- from 20000 to 20999 inclusive
SQL> --
SQL> --
SQL> -- Any time you see an error number
SQL> -- in that range it's an exception/error
SQL> -- defined by the user
SQL> --
SQL> declare
  2        ex20000 exception;
  3        ex20459 exception;
  4        ex20773 exception;
  5        ex20999 exception; -- a very popular error number
  7        pragma exception_init(ex20000, -20000);
  8        pragma exception_init(ex20459, -20459);
  9        pragma exception_init(ex20773, -20773);
 10        pragma exception_init(ex20999, -20999);
 12  begin
 13        begin
 14         begin
 15          begin
 17           --
 18           -- Raising our first defined exception
 19           --
 20           raise ex20000;
 22          exception
 23          when ex20000 then
 25           --
 26           -- Return the first error code
 27           -- and where we generated it
 28           --
 29           dbms_output.put(dbms_utility.format_error_stack);
 30           dbms_output.put_line('   First error');
 31           dbms_output.put_line(dbms_utility.format_error_backtrace);
 33          end;
 35          --
 36          -- Raise the second defined error
 37          --
 38          raise ex20459;
 40         exception
 41         when ex20459 then
 43          --
 44          -- Return the error code
 45          -- and where we generated it
 46          --
 47          dbms_output.put(dbms_utility.format_error_stack);
 48          dbms_output.put_line('   Second error');
 49          dbms_output.put_line(dbms_utility.format_error_backtrace);
 51         end;
 53         --
 54         -- Raise third defined error
 55         --
 56         raise ex20773;
 58        exception
 59        when ex20773 then
 61         --
 62         -- Return the error code
 63         -- and where we generated it
 64         --
 65         dbms_output.put(dbms_utility.format_error_stack);
 66         dbms_output.put_line('   Third error');
 67         dbms_output.put_line(dbms_utility.format_error_backtrace);
 69        end;
 71        --
 72        -- Raise last defined error
 73        --
 74        raise ex20999;
 76  exception
 77  when ex20999 then
 79        --
 80        -- Return the error code
 81        -- and where we generated it
 82        --
 83        dbms_output.put(dbms_utility.format_error_stack);
 84        dbms_output.put_line('   Fourth error');
 85        dbms_output.put_line(dbms_utility.format_error_backtrace);
 87  end;
 88  /
   First error
ORA-06512: at line 20

   Second error
ORA-06512: at line 38

   Third error
ORA-06512: at line 56

   Fourth error
ORA-06512: at line 74

PL/SQL procedure successfully completed.


Not much useful information was presented here, so it’s uncertain what error or errors could have occurred to generate this progression of error messages. [The ORA-06512 error is an informative message as Oracle ‘unwinds’ the error stack and reports what it believes to be as the source of the actual error.] Such user-defined error numbers can be assigned to known Oracle errors, however:

SQL> --
SQL> -- Define error messages
SQL> -- which could be more descriptive
SQL> -- and exceptions which are
SQL> -- easier to handle
SQL> --
SQL> declare
  2          ex20206 exception;
  4          pragma exception_init(ex20206, -2060); -- select for update error
  6  begin
  8          raise ex20206;
 10  exception
 11  when ex20206 then
 12          raise_application_error(-20206, 'Attempt to lock distributed tables', true);
 14  end;
 15  /
ERROR at line 1:
ORA-20206: Attempt to lock distributed tables
ORA-06512: at line 12
ORA-02060: select for update specified a join of distributed tables

SQL> declare
  3          nolock exception;
  4          pragma exception_init(nolock, -69);
  6  begin
  7          execute immediate 'alter table emp add myothercol number';
  8  exception
  9          when nolock then
 10                  raise_application_error(-20909, 'Thet ain''t allowed!!', true);
 11  end;
 12  /
ERROR at line 1:
ORA-20909: Thet ain't allowed!!
ORA-06512: at line 10
ORA-00069: cannot acquire lock -- table locks disabled for EMP


These examples are much clearer in what generated the exceptions and in the nature of the offending operations. [The ORA-00069 error mystically appears after someone has done this to a table:

SQL> alter table emp disable table lock;

Table altered.


and someone else tries to lock that table with DDL or a call to ‘LOCK TABLE …’. The solution to that ‘problem’ is to do this:

SQL> alter table emp enable table lock;

Table altered.


and then find out why someone else thought it necessary to disable locking on the affected table.]

Oracle does enforce the available error number range, as illustrated below, so existing, defined Oracle errors won’t be ‘stepped on’ inadvertently:

SQL> --
SQL> -- Attempt to raise
SQL> -- an exception using an error number
SQL> -- outside of the acceptable range
SQL> --
SQL> begin
  2        raise_application_error(-1400, 'Something strange occurred ...');
  4  end;
  5  /
ERROR at line 1:
ORA-21000: error number argument to raise_application_error of -1400 is out of
ORA-06512: at line 2


[An ORA-01400 error is generated when attempting to insert a NULL value into a column declared as NOT NULL.]

Apparently application programmers read their error messages and understand perfectly what has transpired, and that’s great for them:

“‘ORA-20618: Excessive flarpage’?!?!? What does THAT mean?!?!?”

“Oh, that means ‘don’t press the F8 key more than once on alternate Tuesdays’.”

“I never would have guessed …”

It isn’t good for the user community in general, however, as they are the ones seeing these ‘artificial’ error messages generated by the application code and, in many cases, have no idea what problems to report to Customer Service when they arise:

SQL> --
SQL> -- This could possibly be a bit clearer ...
SQL> --
SQL> declare
  2          ex20773 exception;
  4          pragma exception_init(ex20773, -1002);  -- fetch out of sequence error
  6  begin
  8          raise ex20773;
 10  exception
 11  when ex20773 then
 12          raise_application_error(-20773, 'Yew cain''t dew that!!!');
 14  end;
 15  /
ERROR at line 1:
ORA-20773: Yew cain't dew that!!!
ORA-06512: at line 12


In cases where the users have no access to the developers (and the development team hasn’t obscured the package or procedure code with the wrap utility) it may be necessary to look at that code and see exactly what did generate the error. Of course this may ‘backfire’ as the actual error condition may be buried so deep in the code as to be nearly impossible to search for and the error message was generated by the ubiquitous catch-all ‘when others then …’ exception handler:

SQL> --
SQL> -- This couldn't possibly be less informative
SQL> --
SQL> declare
  2          ex20773 exception;
  4          pragma exception_init(ex20773, -1002);  -- fetch out of sequence error
  6  begin
  8          raise ex20773;
 10  exception
 11  when others then
 12          raise_application_error(-20773, 'Yew cain''t dew that!!!');
 14  end;
 15  /
ERROR at line 1:
ORA-20773: Yew cain't dew that!!!
ORA-06512: at line 12


And, gee whiz, sometimes the developers decide to pass in the SQLCODE and SQLERRM values to RAISE_APPLICATION_ERROR, with disastrous results:

SQL> --
SQL> -- Let's try this and see if it flies
SQL> --
SQL> -- we'll declare an exception then pass in the
SQL> -- generated SQLCODE to the
SQL> -- raise_application_error handler
SQL> --
SQL> declare
  2          ex21000  exception;
  4          pragma exception_init(ex21000, -19);
  6  begin
  7          raise ex21000;
  9  exception
 10          when ex21000 then
 11                  raise_application_error(SQLCODE, SQLERRM);
 13  end;
 14  /
ERROR at line 1:
ORA-21000: error number argument to raise_application_error of -19 is out of
ORA-06512: at line 11


As mentioned earlier RAISE_APPLICATION_ERROR polices the error code values passed to it, and will unceremoniously complain when that value is out of range. [For those who are curious an ORA-00019 (which would generate the SQLCODE of -19) is a ‘maximum number of session licenses exceeded’ error.]

Possibly a ‘user-centered’ mindset on the part of the application programmers might better serve the end users, and maybe some testing should be done by people outside of the development community to verify that the error messages generated are truly useful to all parties involved.

I’ve blogged here about coding confusing text as error messages, so I won’t mention that topic again in this post. But maybe, just maybe, application programmers should read both posts and change their errant ways so the end users have something meaningful and useful as an error message and, as a result, their calls to the Help Desk aren’t exercises in futility.

Hope springs eternal.

Create a free website or blog at