当前位置：首页 -> 技术开发 -> 数据库 -> 显式游标、范围大小和复杂间隔的相关问题

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

时间： 2021-07-31 作者：daque

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

作家：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 /startingincremental cgets: 514,690explicit to find x=1 stop at first hitincremental cgets: 4explicit to find x=1 check for dupsincremental cgets: 4,220implicit to find x=1 stop at first hitincremental cgets: 4implicit to find x=1 check for dupsincremental cgets: 4,219explicit to find x=29000incremental cgets: 4,101implicit to find x=29000incremental 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:35mon 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操纵的采用前提设定所惹起的。