On the Explicit, Size, and Complex

Our technologist answers questions on cursors, extents, and intervals.

January/February 2003

Is it true that from Oracle7 Release 7.3 onward, implicit cursors are optimized and they don't double-fetch? Also, why does the following implicit cursor run faster than the explicit cursor below it when table T has an index on column X, but otherwise the explicit cursor runs faster?

Implicit Cursor:
Select x
  into y
  from T
 where x = j; 

Explicit Cursor:
cursor c(p number) is
select x from blah where x = p;
open c(j);
fetch c into y;
close c; 

I'll start with a quick definition so that everyone understands what implicit and explicit cursors are.

In general, an implicit cursor is one that the programmer does not "explicitly" declare, open, fetch from, or close; these operations are implicit. So, in the above example, the SELECT X INTO Y query is an implicit cursor. There is no "cursor cursor_name is ..." definition for it. The second example, on the other hand, is the classic explicit cursor. The programmer declared, opened, fetched, and closed it explicitly.

Now, it is a fact that implicit cursors are faster in PL/SQL than explicit cursors, and implicit cursors have been faster since releases before Oracle7 Release 7.3. In fact, I have test cases that show this is true going back to Oracle7 Release 7.1 (see asktom.oracle.com for those tests). The reason implicit cursors are faster (both cursor FOR LOOP implicit cursors as well as SELECT INTO implicit cursors) is that the PL/SQL engine has a lot less of your code to interpret and execute. The more PL/SQL can do under the covers, the faster it will be in general. The implicit cursor above takes one line of PL/SQL code; the explicit cursor takes at least three lines of code and, if done "correctly," actually takes six lines of code. Your explicit code does not do the work of the implicit cursor, which looks to make sure you are getting at least one row and at most one row. Your explicit code is missing a lot of stuff you need to do. In order to accurately compare your two cursor examples, your explicit code should be more along the lines of:

open c(j);
fetch c into y;
if ( c%notfound ) then raise NO_DATA_FOUND;
end if;
fetch c into y;
if ( c%found ) then raise TOO_MANY_ROWS;
end if;
close c;

If this were your explicit cursor, you would find that the explicit cursor is slower in all cases, even in your example with the index versus no index.

Now, to get to the crux of your question: Why in your example without the index does the implicit cursor appear to perform so badly, whereas when there is an index, the implicit cursor wins? The answer lies in the work a full scan does, and the fact that your explicit test stops after exactly one row. I'll cook up an example to show you the difference between the two:

SQL> create table t ( x int )
  2  pctfree 99 pctused 1;
Table created.
SQL> insert into t
  2  select rownum
  3    from all_objects;
29264 rows created.
SQL> analyze table t compute statistics;
Table analyzed.
SQL> select blocks, empty_blocks, num_rows
  2    from user_tables
  3   where table_name = 'T';
    BLOCKS     EMPTY_BLOCKS     NUM_ROWS
-------------  ------------   -----------
     4212          140           29264

I've created a table with a lot of blocks; the

pctfree 99

Next, I'll run a small PL/SQL block that will show the consistent gets performed by various implicit and explicit cursors against this data. Since there are no indexes, queries will do a full scan of the entire table. The differences in performance will be easy to quantify once I do this and review the results:

SQL> declare
  2     l_last_cgets number default 0;
  3     l_x      number;
  4     cursor c( p_x in number ) is
  5     select x
  6     from t
  7          where x = p_x;
  8
  9  procedure cgets( p_msg in varchar2 )
 10  is
 11    l_value number;
 12  begin
 13    select b.value into l_value
 14      from v$statname a, v$mystat b
 15     where a.statistic# = b.statistic#
 16       and a.name = 'consistent gets';
 17
 18    dbms_output.put_line( p_msg );
 19    dbms_output.put_line
 20    (  'Incremental cgets: ' ||
 21      to_char(l_value-l_last_cgets,
 22                       '999,999') );
 23    l_last_cgets := l_value;
 24  end;
 25
 26  begin
 27    cgets('Starting');
 28
 29    open c(1);
 30    fetch c into l_x;
 31    close c;
 32    cgets('Explicit to find X=1 ' ||
 33              'stop at first hit' );
 34
 35    open c(1);
 36    fetch c into l_x;
 37    fetch c into l_x;
 38    close c;
 39    cgets('Explicit to find X=1 ' ||
 40              'check for dups' );
 41
 42    select x into l_x
 43      from t
 44     where x = 1 AND rownum = 1;
 45    cgets('Implicit to find X=1 ' ||
 46              'stop at first hit' );
 47
 48    select x into l_x
 49      from t
 50     where x = 1;
 51    cgets('Implicit to find X=1 ' ||
 52              'check for dups' );
 53
 54    open c(29000);
 55    fetch c into l_x;
 56    close c;
 57    cgets('Explicit to find X=29000');
 58
 59    select x into l_x
 60            from t
 61           where x = 29000;
 62    cgets('Implicit to find X=29000');
 63  end;
 64  /
Starting
Incremental cgets:  514,690
Explicit to find X=1 stop at first hit
Incremental cgets:        4
Explicit to find X=1 check for dups
Incremental cgets:    4,220
Implicit to find X=1 stop at first hit
Incremental cgets:        4
Implicit to find X=1 check for dups
Incremental cgets:    4,219
Explicit to find X=29000
Incremental cgets:    4,101
Implicit to find X=29000
Incremental cgets:    4,219
PL/SQL procedure successfully completed.

Here you can see why in your example the explicit cursor appeared faster than the implicit cursor. When I do the explicit cursor test and do just one fetch for X=1, that query needs to scan very few blocks (very few consistent gets) in order to find the answer. However, as soon as I make the explicit cursor do the work of the implicit cursor, checking to make sure no other row satisfies that same criteria, you see that the explicit cursor looks at each and every block in the table. Now, I move on to the implicit cursor and see that if I tell it also to stop at the very first hit, using ROWNUM=1 , it does the same amount of work as the explicit cursor. When it checks for a second row in the table matching the criteria, you see that it again does the same number of consistent gets as the explicit cursor does; it, too, had to finish full scan of the table to verify that there was only one row with X=1.

The interesting part is when I query for X=29,000. Since that row is near the "end" of the table, no matter what approach I take, the two queries will do about the same amount of work. They both must scan almost the entire table to find the first row.

Now, if there were an index on X, both queries would tend to use an index range scan and both queries would rapidly find that there is but one row, without having to do a full scan of the table.

This accounts for your cursor behavior: the SELECT INTO checked for a second row, whereas your explicit cursor did not. If you compare apples to apples—either do the second explicit fetch or add "rownum = 1" to the SELECT INTO —you will find that both cursors do the same amount of work.

In short, implicit cursors are great. They execute faster than the same code with an explicit cursor, they are easier to code (less code to type), and I personally find the code more readable and understandable.

In our new application, we designed the database and created the data model. We even sized the tables and came up with storage parameters for each table. But now our DBA people tell us that they will give us three tablespaces: TS_small with uniform extent size 160K, TS_med with uniform extent size 5MB, and TS_large with uniform extent size 160MB. They are telling us to create tables that are going to be less than 5MB in TS_small , tables that are going to be less than 160MB in TS_med, and tables that are going to be more than 160MB in TS_large . Additionally, they do not want us to use any storage parameters for the table. They are saying the same would be true with the indexes. It doesn't look reasonable to me, because for a table with an estimated size of 120MB, we place it in TS_med , and if we create it in that tablespace, it takes 24 extents! The DBA claims that numerous tests have proven this design provides ultimate performance and prevents fragmentation. My question is, are they right? I'm worried about objects with so many extents.

Well, it looks as if they've been reading the asktom Web site (asktom.oracle.com) and internet discussion groups and have found good advice. In looking at their numbers, I see that they've allowed for a table up to 5GB in size to have 32 or fewer extents. Given that hundreds—even more than hundreds—of extents will not affect runtime data manipulation language (DML) performance, I would say they have done an excellent job.

Both of their premises are correct: fragmentation of the tablespaces will be impossible, and performance will be optimal. Let's look at each claim.

That fragmentation is not possible should be easy to see. A dictionary-managed tablespace gets fragmented due to different-sized extents. A dictionary-managed tablespace might include thousands of extentsÑfree and used, each a different size. Now, you start dropping and creating objects in this tablespace, and over time you end up with lots of different-sized "holes" (free space) in it. So, you could look at a dictionary-managed tablespace and sum up the free space in it and discover you have 500MB free. But then you try to create a table with a 40MB initial extent and get an error about not being able to allocate the first extent. How could that be? You have 500MB free, right? Well, yes, but unfortunately that 500MB is in dozens of extents, each of which is less than 40MB in size! So, you have lots of unusable free space; your tablespace is fragmented. Now, consider a locally managed tablespace with uniform extents. Here each and every extent is the same exact size as every other extent, without exception. If you find you have 500MB free, I can assure you that you will be able to allocate a new extent in this tablespace, since every free extent by definition can be used in your object.

As for optimal performance, you have to understand that having tens, hundreds, or even more extents will not have a material bearing on runtime performance. Your DML operations (which include queries) will not be adversely impacted by having many extents. Thirty-two extents for your objects would be excellent—not a performance impact at all. In fact, since locally managed tablespaces are much more efficient at space allocation than dictionary-managed tablespaces, using them increases your performance, not the other way around.

Rather than being concerned about 32 extents or so, you should be rejoicing that you never again have to figure out what the "best" INITIAL, NEXT, PCTINCREASE, MINEXTENTS, and MAXEXTENTS are.

I am using DBMS_JOB, and I want to schedule a job that runs every 15 minutes from Monday to Friday, between 6 a.m. and 6 p.m. How do I schedule it? I cannot figure out the interval I should be passing.

Well, for figuring out complex intervals for DBMS_JOB , I like to use the new (as of Oracle8i Release 2) CASE statement. For example, the following CASE statement returns the correct interval for your specification:

SQL> alter session set nls_date_format =
  2  'dy mon dd, yyyy hh24:mi';
Session altered.
SQL> select
  2  sysdate,
  3  case
  4    when (to_char( sysdate, 'hh24' )
  5                   between 6 and 17
  6     and to_char(sysdate,'dy') NOT IN
  7                   ('sat','sun') )
  8    then trunc(sysdate)+
  9      (trunc(to_char(sysdate,'sssss')/
 10                     900)+1)*15/24/60
 11    when (to_char( sysdate, 'dy' )
 12          not in ('fri','sat','sun') )
 13    then trunc(sysdate)+1+6/24
 14    else next_day( trunc(sysdate),
 15                   'Mon' )+6/24
 16  end interval_date
 17   from dual
 18  /
SYSDATE
------------------------------
INTERVAL_DATE
------------------------------
sun sep 15, 2002 16:35
mon sep 16, 2002 06:00

The CASE statement gives you great flexibility in generating a complex value such as you need. Unfortunately, DBMS_JOB will allow you to use only an interval that is 200 characters or less, and even if you "scrunch up" the CASE statement above, you'll find it is about 300 characters minimally. So, you cannot use it directly in the call to DBMS_JOB . My solution to that is one of two things: either I would create a view NEXT_DATE as that select, so that select * from next_date would return the next time the job runs, or I would wrap the above query in a PL/SQL function that returns a date. If I used a view, my call to DBMS_JOB might look like:

begin  dbms_job.submit
  ( :n, 'proc;', sysdate,
    '(select * from next_date)'
  );
end;
/  

Or, if I used the PL/SQL function approach and created a function NEXT_DATE , it could be:

begin
  dbms_job.submit
  ( :n, 'proc;', sysdate,
    'next_date()'
  );
end;
/

We have several tables partitioned by time (fiscal year). What do you recommend as the best approach for having historical data be read-only and current data be read/write?

Basically, we want to be able to append for the current fiscal year and lock the prior fiscal years so they cannot be modified. My current thought is to have the historical data be in a separate tablespace than the current data. Does this approach make sense? I'm using Oracle9i Release 9.0.1 on Microsoft Windows 2000.

This is rather easy to accomplish, actually. A tablespace may be read-only or read-write. If you use a tablespace per partition (or at least have the historical partitions in a separate tablespace from the current), you can simply ALTER TABLESPACE <tablespace name> READ ONLY to make it read-only. The end users will not be able to modify that tablespace and, in fact, you can save considerable backup time, because you'll have to back up that tablespace only once (unless you make it read-write and modify it—then you'll need to back it up again, obviously).

In fact, you can even put this tablespace on some read-only media such as a CD, making it impossible to modify.

If I were using Oracle9i Database Release 2, I would go a step further. Before making this historical partition read-only, I would compress it, using the new table COMPRESS feature. I could save significantly on the amount of disk space this data occupies. I would accomplish this by "moving" the existing partition with the compress option. Compression factors of 3:1, 5:1, and even 12:1 are not unreasonable to expect in many cases, depending on the nature of the data.

Could you please explain for this result what are recursive calls, db block gets, and so on?

Statistics
---------------------------------------------
     0 recursive calls
202743 db block gets
 84707 consistent gets
     0 physical reads
     0 redo size
  2010 bytes sent via SQL*Net to client
   430 bytes received via SQL*Net from ...
     2 SQL*Net roundtrips to/from client
     0 sorts (memory)
     0 sorts (disk) 8 rows processed

Well, this is definitely a frequently asked question, so I'll look at each statistic in some detail. I'll use the definition offered by the Oracle9i Database Performance Tuning Guide and Reference manual that describes each statistic and annotate when I think the definition might not be clear:

• Recursive Calls. Number of recursive calls generated at both the user and system level.

  Oracle Database maintains tables used for internal processing. When it needs to change these tables, Oracle Database generates an internal SQL statement, which in turn generates a recursive call.

  In short, recursive calls are basically SQL performed on behalf of your SQL. So, if you had to parse the query, for example, you might have had to run some other queries to get data dictionary information. These would be recursive calls. Space management, security checks, calling PL/SQL from SQL—all incur recursive SQL calls.

• DB Block Gets. Number of times a CURRENT block was requested.

  Current mode blocks are retrieved as they exist right now, not in a consistent read fashion. Normally, blocks retrieved for a query are retrieved as they existed when the query began. Current mode blocks are retrieved as they exist right now, not from a previous point in time. During a SELECT , you might see current mode retrievals due to reading the data dictionary to find the extent information for a table to do a full scan (because you need the "right now" information, not the consistent read). During a modification, you will access the blocks in current mode in order to write to them.

• Consistent Gets. Number of times a consistent read was requested for a block.

  This is how many blocks you processed in "consistent read" mode. This will include counts of blocks read from the rollback segment in order to roll back a block. This is the mode you read blocks in with a SELECT , for example. Also, when you do a searched UPDATE/DELETE , you read the blocks in consistent read mode and then get the block in current mode to actually do the modification.

• Physical Reads. Total number of data blocks read from disk. This number equals the value of "physical reads direct" plus all reads into buffer cache.

• Redo Size. Total amount of redo generated in bytes.

• Bytes Sent via SQL*Net to Client. Total number of bytes sent to the client from the foreground process(es).

  Basically, this will be the collective size of your result set.

• Bytes Received via SQL*Net from Client. Total number of bytes received from the client over Net.

  Basically, this is the size of your query as transmitted over the network.

• SQL*Net Round-trips to/from Client. Total number of Net messages sent to and received from the client.

  Basically, this is how many interactions between you and the server took place to get the answer. As you increase your ARRAYSIZE setting in SQL*Plus, you'll see this number go down for a SELECT that returns many rows (fewer round-trips, since each fetch of N rows is a round-trip). As you decrease your ARRAYSIZE , you'll see this value go up.

• Sorts (memory). Number of sort operations that were performed completely in memory and did not require any disk writes.

  You cannot do much better than memory sorts, except maybe by having no sorts at all. Sorting is usually caused by selection criteria specifications within table join SQL operations.

• Sorts (disk). Number of sort operations that required at least one disk write. Sorts that require I/O to disk are quite resource intensive. Try increasing the size of the initialization parameter SORT_AREA_SIZE.

• Rows Processed. This is the total number of rows returned by your SELECT or modified by your INSERT, UPDATE, or DELETE statement.

  Next Steps ASK TOM asktom.oracle.com Oracle Vice President Tom Kyte answers your most difficult technology questions. Highlights from that forum appear in this column. READ Oracle9i Documentation Oracle9i Database Performance Tuning Guide and Reference

Send us your comments