显式游标、范围大小和复杂间隔的相关问题

显式游标、范围大小和复杂间隔的相关问题

作者：Tom Kyte

我们的技术专家回答关于游标、范围（extent）和间隔的问题。

是不是从Oracle7第7.3版以后的版本，隐式游标得到了优化，不会两次取数据？还有，为什么当表T在列X上有一个索引时下面的隐式游标比显式游标运行得更快，而没有索引时是显式游标运行得较快呢？

　

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;

　

为了让每个人都了解显式游标和隐式游标是什么，我先简单介绍一下它们的定义。

通常，隐式游标是指程序员并不"显式"声明、打开、从中取数据和关闭的那些游标；这些操作都是隐式的。因此，在上面的例子中，SELECT X INTO Y查询就是一个隐式游标。对于它来说并没有"cursor cursor_name is ..."这样的定义语句。相反，第二个例子是典型的显式关标。程序员显式地声明、打开、取数据和关闭它。

在PL/SQL中隐式游标比显式游标运行得更快是一个事实，在Oracle7 7.3版之前的版本中就是这样。事实上，我在Oracle7 7.1版中就测试过这样的情况并得到了同样的结论（这些测试请参见asktom.oracle.com/~tkyte/ivse.html）。隐式游标运行得更快的原因（FOR LOOP隐式游标和SELECT INTO隐式游标）是PL/SQL引擎只需要解释和执行很少的代码。一般来说，PL/SQL引擎在后台做的越多，程序就运行地越快。上面的隐式游标只使用了一行PL/SQL代码；显式游标至少使用了三行代码，如果要"正确地"运行，实际上要使用6行代码。你的显式代码并不像隐式游标那样运行，它要确保你得到一条且只得到一条记录。你的显式代码缺少了许多你要做的工作。为了精确地比较你的两个游标例子，你的显式代码应该被扩展出以下几行：

　

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;

如果这就是你的显式游标，你会发现在所有情况下显式游标都运行得比较慢，甚至于无论你的例子中有没有索引都是这样。

那么，你的问题的症结所在是：为什么在你的例子中没有索引时，隐式游标好像运行地非常慢，然而当存在一个索引的时候，隐式游标却运行得较快呢？答案在于全表扫描，事实上在得到一条记录后，你的显式测试就停止了。我将给出一个例子来向你展示它们之间的不同之处：

　

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

我创建了一个有许多数据块的表；值pctfree 99为随后更新数据保留了99％的块作为"空闲空间"。因此，即使表中的数据量很小，表本身也相当大。接着，我通过INSERT把值1，2，3，...一直到29,264严格按顺序插入到表中。因此，X＝1在该表的"第一个"块中而X＝29,000在表中相当接近表的最后一个块。

　

接下来，我将运行一个小PL/SQL块，它会显示各种隐式和显式游标对数据进行一致读的次数。因为没有索引，查询将对整个表进行全面扫描。一旦我运行这个程序然后评审查询结果，将很容易对性能的差异进行量化。

　

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.

现在你就可以明白在你的例子中为什么显式游标好像比隐式游标运行得更快了。当我使用显式游标进行测试的时候，只取一次数据X＝1，为了找到答案，查询只需要扫描非常少的块（很少的一致的读次数）。然而，只要我使显式游标来进行隐式游标的工作，检查确保没有其他记录满足同一条件，你就会看到显式游标检查表中的每一个块。现在，我接着说隐式游标，通过使用ROWNUM=1看看它是否也会在找到第一条符合条件的记录时停下来，它和显式游标做相同的工作量。当它检查表中的第二行是否符合条件时，你会看到它同显式游标一样进行相同次数的一致读；它也不得不对表进行全面扫描以核定只有一行X＝1。

最有趣的是当我查询X＝29,000的时候。因为那行接近表的"结尾"，所以无论我采用什么方法，两个查询的工作量都差不多。为了找到满足条件的第一行它们都必须扫描几乎整个表。

现在，如果在X上有一个索引，两个查询都会使用索引范围扫描，而且两个查询都不必对表进行全面扫描，便能快速地发现只有一行满足条件。

这就解释了你的游标行为：SELECT INTO检查第二行，但显式游标却不这么做。如果你对应地进行比较：第二次显式地取数据或者把"rownum = 1"添加到SELECT INTO语句中－－你就会发现两个游标的工作量相同。

简而言之，隐式游标更好。它们比使用显式游标的相同代码运行地更快，更容易编码（需要键入的代码更少），而且我个人认为使用隐士游标的代码更容易读也更容易理解。

小、中和大

在我们的新应用程序中，我们设计了数据库并创建了数据模型，甚至还估计了表的大小并为每个标指定了存储参数。但现在我们的数据库管理员告诉我们将给我们三个表空间：范围大小统一为160K的TS_small表空间、范围大小统一为5MB的TS_med表空间和范围大小统一为160MB的TS_large表空间。他们告诉我们在TS_small中创建小于5MB的表，在TS_med中创建小于160MB的表，在TS_large中创建大于160MB的表。另外，他们不希望我们对表使用任何存储参数。对索引也是这样。这好像并不合理，因为对于一个预计大小为120MB的表，我们应把它放在TS_med中，接下来如果我们在那个表空间中创建它，它会占24个范围！数据库管理员声称许多测试已经证明这种设计提供了最佳的性能并可以防止碎片。我的问题是，他们说的对吗？我担心对象会有太多的范围。

看来他们已经读过asktom Web站点（asktom.oracle.com）和互联网讨论组的相关内容，并发现了好的建议。从他们的数字看，我注意到他们允许一个表占用的最大空间是5GB，可以有32个或更少的范围。假设上百个（或者上千个）范围不会影响运行时数据操纵语言（DML）的性能，我会说他们做得非常好。

他们的前提都是正确的：将不会出现表空间碎片，性能也将得到优化。让我们来看看每个主张。

不可能出现碎片应该很容易明白。数据字典管理的表空间由于范围的尺寸大小不同所以会出现碎片。数据字典管理的表空间可能包括上千个范围吗？每一个空闲范围和已使用的范围的大小都不同。现在，你开始在这个表空间中删除并创建对象，随着时间的推移，表空间中就会出现许多大小不同的"空洞"（空闲空间）。接着，你查看一下数据字典管理的表空间并累计其中的空闲空间，你会发现有500MB的空闲空间。但接着你试着创建一个带有40MB初始范围的表，却得到一个关于不能分配第一个范围的错误消息。这是怎么回事？你有500MB的空闲空间，不是吗？是这样，但不幸的是那500MB的空间分布许多范围中，那些范围中的每一个空闲空间都小于40MB！因此，你有许多无法使用的空闲空间；你的表空间有许多碎片。现在，让我们考虑一下使用统一范围的本地化管理的表空间。每个范围都毫无例外地与其他每个范围拥有相同的尺寸。如果你发现你有500MB的空闲空间，那么我可以保证你将能够在这个表空间中分配一个新的范围，因为每个被定义的空闲范围都可以被你的对象使用。

至于最佳性能，你必须明白拥有几十个，上百个甚至更多的范围不会对运行时性能产生实质性的影响。你的DML操作（包括查询）不会由于有许多范围而受到不利的影响。我就不在这里证明它了，详细情况请通过以下两个链接参见usenet讨论组：asktom.oracle.com/~tkyte/extents.html 和asktom.oracle.com/~tkyte/extents2.html，每一个都包含有关这个主题的相当多的讨论。对于你的对象来说三十二个范围是非常合适的－根本不会对性能产生影响。事实上，因为本地化管理的表空间在空间分配方面比数据字典管理的表空间高效得多，使用它们将会提高性能，而不是降低性能。

不要担心32个范围等问题，你应该高兴你再也不必弄清什么是"最好的"INITIAL、NEXT、PCTINCREASE、MINEXTENTS和MAXEXTENTS。

设置一个复杂的间隔

我正在使用DBMS_JOB，我想在从周一到周五每天早6点到晚6点之间每15分钟运行一次任务。我怎样调度它呢？我无法计算出调度间隔。

对于计算DBMS_JOB的复杂间隔，我喜欢使用使用新的（Oracle8i第2版中）CASE语句。例如，下面的CASE语句将按你的要求返回正确的间隔：

　

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

CASE语句在产生诸如你需要的复杂值方面具有很大的灵活性。不幸的是，DBMS_JOB只允许你使用200字符或少于200字符的间隔，即使你"压缩"了以上的CASE语句，你还是会发现它最少也有大约300个字符。因此你不能在对DBMS_JOB的调用中直接使用它。有以下两种解决方法：一种是为那个select语法创建一个视图NEXT_DATE，因此select * from next_date将返回该任务下次运行的时间；第二种方法是在一个返回日期值的PL/SQL函数中封装以上的查询。如果我使用视图，我对DBMS_JOB的调用看起来可能像下面这样：

　

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

　

如果我使用PL/SQL函数方法并创建一个函数NEXT_DATE，它可能是这样：

　

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

　

实现信息只读的最好方式

我们有几个按时间（财政年度）划分的表。你认为什么是使历史数据只读而当前数据可读/写的最好方式？

从根本上来说，我们希望能够对当前财政年度的数据进行添加并锁定以前财政年度的数据以便使它们不能被修改。我现在的想法是把历史数据放在一个与当前数据隔离的表空间中。这个方法可行吗？我在Microsoft Windows 2000上使用Oracle9i 9.0.1版。

实际上，这很容易实现。一个表空间可以是只读的或是可读写的。如果你每个分区使用一个表空间（或者至少将历史分区在与当前分区不同的表空间中），你可以简单地使用ALTER TABLESPACE READ ONLY来使它只读。最终用户将不能修改那个表空间，而且，事实上，你可以节省相当可观的备份时间，因为你只需备份那个表空间一次（除非你使它可读写并修改它--那么很显然你将需要再次备份）。

事实上，你甚至可以把这个表空间放在某些只读介质（例如CD），使它不可能被修改。

如果我使用Oracle9i数据库第2版，我会更进一步。在使这个历史分区只读之前，我会使用新的表COMPRESS特性压缩它。这样我可以节省这个数据占用的大量磁盘空间。我会通过使用压缩选项移动"现有的分区来完成它。在许多情况下，不要过分期望压缩比会达到3:1、5:1甚至12:1，这依赖于数据的性质。

Autotrace的输出意味着什么？

你能为我解释这个结果中的recursive calls、db block gets等等是什么意思吗？

　

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

　

这确实是一个最常见的问题，我将详细讨论每个统计。我将使用Oracle9i数据库性能调优向导和参考（Oracle9i Database Performance Tuning Guide and Reference）手册中提供的定义来描述每个统计，在我认为定义可能不清楚时我会加一些注释：

Recursive Calls. 在用户级和系统级产生的递归调用的数目。
　

Oracle数据库维护用于内部处理的表。当它需要改变那些表时，Oracle数据库生成一个内部SQL语句，该语句反过来产生一个递归调用。

简而言之，递归调用就是代表你的SQL执行的SQL语句。因此，如果你必须解析该查询，例如，你可能必须运行一些其他的查询来得到数据字典的信息。这就是递归调用。空间管理、安全性检查、从SQL中调用PL/SQL--所有这些都会引起递归SQL调用。

　

　

DB Block Gets. 当前块被请求的次数。
当存在时，当前模式块将被立即检索，而不会以一致读的方式检索。通常，查询检索的块如果在查询开始时存在，它们就被检索。当前模式块如果存在就立即被检索，而不是从一个以前的时间点检索。在一个SELECT期间，你可以看到当前模式检索，因为对于需要进行全面扫描的表来说，需要读数据字典来找到范围信息（因为你需要"立即"信息，而不是一致读）。在修改期间，为了向块中写入内容，你要以当前模式访问块。

　

　

Consistent Gets. 对于一个块一致读被请求的次数。
　

这是你以"一致读"模式处理的块数。为了回滚一个块，这将包括从回滚段读取的块的数目。例如，这是你在SELECT语句中读取块的模式。当你进行一个指定的UPDATE/DELETE操作时，你也以一致读模式读取块，然后以当前模式获得块以便实际进行修改。

　

　

Physical Reads. 从磁盘读取的数据块的总数。这个数等于"physical reads direct"（物理上直接读取的块数）的值加上读入缓存区的所有块数。
　

　

Redo Size. 所产生的以字节为单位的redo（重做日志）总数。
　

　

Bytes Sent via SQL*Net to Client. 从前台进程发送到客户端的字节总数。
　

一般来说，这是你的结果集的整体大小。

　

　

Bytes Received via SQL*Net from Client. 通过网络从客户端收到的字节总数。
　

一般来说，这是通过网络传输的你的查询的大小。

　

　

SQL*Net Round-trips to/from Client. 发送到客户端和从客户端接收的网络消息总数。
　

一般来说，这是为了得到回答在你和服务器间发生的交互次数。当你在SQL*Plus中增加ARRAYSIZE设置值时，你将看到对于返回多条记录的SELECT语句，这个数字会下降（更少的来回交互，因为每获取N条记录是一个来回）。当你减少你的ARRAYSIZE值时，你将看到这个数字增加。

　

　

Sorts (memory). 完全在内存中执行、且不需要任何磁盘写的排序操作的数目。
　

没有比在内存中排序更好的排序了，除非根本没有排序。排序通常是由表连接SQL操作的选择条件设定所引起的。

　

　

Sorts (disk). 至少需要一次磁盘写的排序操作的次数。需要磁盘输入/输出的排序操作需要耗费大量资源。请试着增加初始化参数SORT_AREA_SIZE的大小。
　

　

Rows Processed. 这是由SELECT语句所返回的或是由INSERT、UPDATE或DELETE语句修改的总行数。