首页 > 分享 > 《Oracle编程艺术：深入理解数据库体系结构（第3版）》试读：1.3　开发数据库应用的正确（和不正确）方法

《Oracle编程艺术：深入理解数据库体系结构（第3版）》试读：1.3　开发数据库应用的正确（和不正确）方法

花匠小妙招
2024-09-10 12:59

这里的假设够多了。本章后面则会靠实验说话，明确地讨论为什么了解数据库及其工作原理对于项目的成功大有帮助（而无需在出现问题后重写应用）。有些问题修改起来很简单，只要你知道怎么发现这些问题即可。还有一些问题则不同，必须大动干戈地重写应用方能更正。这本书的目标之一就是帮助你避开这些问题。注意在下面的几节中，我会谈到一些核心的Oracle特性，但并不会深入地讨论这些特性到底是什么，也不会全面地介绍使用这些特性的全部细节。如果想了解更多的信息，建议接着阅读本书后面的章节，或者参考相关的Oracle文档。 1.3.1　了解Oracle体系结构最近，我和许多客户合作了一些大型的生产应用。这些应用已经从SQL Server“移植到”Oracle。之所以把“移植”一词用引号括起来，原因是我看到的大多数移植都只是“怎样对SQL Server代码做最少的改动，就能让它们在Oracle上编译和执行”。要把应用从一个数据库真正移植到另一个数据库，这绝对是一项大工程。这样的大工程如果只是通过这种所谓的“移植”来完成，必然会带来大量的问题。我见过很多这样的案例，为什么？因为问题太多了，我被叫去帮忙！要声明的是，我不是在责难SQL Server。当然，反过来也一样：把一个Oracle应用简单地“移植”到SQL Server，而且尽可能地避免改动，这也会得到一个很成问题、表现极差的应用。在某种特殊的情况下，SQL Server体系结构和使用方法会影响在Oracle实现相同的功能。这种移植”所声称的目标是扩展应用，但“移植”应用的开发人员一方面想达到这个目的，另一方面又想尽量少出力。所以，这些开发人员往往保持客户端和数据库层的体系结构基本不变。这种决定最糟糕的两个后果是： Oracle中采用与SQL Server同样的数据库连接体系结构；  开发人员在SQL中使用字面量（而不是绑定变量）。  这两个结果导致系统无法支持所需的用户负载（数据库服务器的可用内存会耗尽），应用的性能也极差。 1. 在Oracle中使用一个连接目前SQL Server中有一种很普遍的做法，就是对想要执行的每条并发语句都打开一个数据库连接。如果想执行5个查询，可能就会在SQL Server中看到5个连接。在Oracle中，不论你想执行5个查询还是500个查询，最好就打开一个连接。所以，SQL Server中常用的做法在Oracle中却不被提倡，你可能并不想维护多个数据库连接。不过，他们确实这么做了。一个简单的Web应用中，每个网页可能打开5个、10个、15个甚至更多连接，这意味着，相对于服务器原本能支持的并发用户数，现在服务器只能支持其1/5、1/10、1/15甚至更少的并发用户数。另外，他们的数据库运行在Windows 平台上，而没有使用Data Center版本的Windows。这意味着Oracle数据库服务器会受限于Windows单进程体系结构，它将最多能使用大约1.75 GB的内存。由于每个Oracle数据连接都需要分配一些内存，这样系统用户的可扩展性将会受到极大的限制。尽管他们的Windows 平台上有8 GB的内存，但是真正能用的只有2 GB左右。注意32位Windows环境中还能通过其他办法用到更多的内存，如利用/AWE开关选项，但他们在这个项目中使用的操作系统版本并不支持这个开关。针对这个问题，可能的解决方案有3种，无论哪一种解决方案都需要做大量工作。（请记住这可是在原先以为“移植”已经结束的情况下补充的工作！）具体如下。 重建应用，要认清这样一个事实：应用是在Oracle上运行，而不是在其他种类的数据库上；此外生成一个页面只使用一个连接，而不是5～15个连接。这是从根本上解决这个问题的唯一方法。  升级操作系统（这可不是小事情）到Windows Data Center版本以使用更大的内存（这本身就非小事，而且还会带来相当复杂的数据库安装，需要一些间接的数据缓冲区和其他非标准的设置）。  把数据库从Windows系列操作系统移植到另外某个使用多进程的操作系统，以便数据库能够充分利用服务器的内存。在32位Windows平台上，由于受限于其单进程架构，Oracle数据库的PGA和SGA最多可以使用2 GB 内存。在32位多进程平台上，SGA最多可用到2GB，并且每个服务器进程的PGA也能使用2 GB 内存，比在32位Windows平台上多多了。  可以看到，以上都不是轻轻松松就能办到的。不论哪种方法，你都不会毫无芥蒂地一口应允“好的，我们下午就来办”。每种方案都相当复杂，所要解决的问题原本在数据库“移植”阶段修正才最为容易，那是你查看和修改代码的第一个机会。另外，如果交付生产系统之前先对“可扩展性”做一个简单的测试，就能在最终用户遭遇苦痛之前及时地捕捉到这些问题。 2. 使用绑定变量如果我要写一本书谈谈如何构建不可扩展的Oracle应用，肯定会把“不要使用绑定变量”作为唯一一章的标题重点强调。“不使用绑定变量”是导致性能问题的一个主要原因，也是阻碍可扩展性的一个重要因素，更不用说，它也极大地提高了安全风险。Oracle共享池（shared pool）是一个非常重要的共享内存结构，在大多数情况下，它的运行及使用状况是由开发人员是否使用绑定变量来决定的。如果确实想让Oracle运行缓慢，甚至几近停顿，只要根本不使用绑定变量就可以办到。绑定变量是查询中的一个占位符。例如，要获取员工123的相应记录，可以使用以下查询。 select * from emp where empno = 123; 或者，也可以执行以下查询。 select * from emp where empno = :empno; 在一般的系统中，可能只查询一两次员工123，然后可能很长一段时间内不再查询这个员工的信息。之后，可能会查询员工456，然后是员工789，如此等等。如果在插入语句中不使用绑定变量（上面提到的第一个SQL），主键值可能硬碥码在其中，这样的话，插入语句对于Oracle数据库来说是不可重用的！如果在查询中使用字面量（常量），那么在数据库看来，每个查询都将是一个全新的查询，以前从未见过，必须对查询进行解析、限定（命名解析）、安全性检查、优化等。简单地讲，就是执行的每条不同（但是很相似）的语句都要进行编译。第二个查询使用了一个绑定变量:empno，变量值在查询执行时提供。这个查询只编译一次，随后数据库会把查询计划存储在共享池的一个区域（库缓存）中，以便以后获取和重用这个查询计划。以上两个查询在性能和可扩展性方面有很大差别，甚至可以说有天壤之别。从前面的描述应该能清楚地看到，与重用已解析的查询计划（称为软解析，soft parse）相比，解析包含有硬编码变量的语句（称为硬解析，hard parse）需要的时间更长，而且要消耗更多的资源。硬解析会减少系统能支持的用户数，但程度如何可能不容易度量。这取决于多耗费了多少资源，但更重要的因素是库缓存所用的闩定（latching）机制。硬解析一个查询时，数据库会更长时间地占用一种低级串行化设备，这称为闩（latch），有关的详细内容请参见第6章。这些闩能保护Oracle共享内存中的数据结构不会同时被两个进程修改（否则，Oracle的数据结构会最终遭到破坏），而且如果有人正在修改某个数据结构，则不允许另外的人同时读取。对这些数据结构加闩的时间越长、越频繁，排队等待闩的进程就越多，等待队列也越长。当大家都在试图抢占这种珍贵的闩资源时，你的服务器可能看上去非常空闲，但是数据库中的所有应用都运行得非常慢。造成这种现象的原因可能是有人占据着某种串行化设备，而其他等待串行化设备的人开始排队，因此你无法全速运行。数据库中只要有一个应用表现不佳，就会严重地影响所有其他应用的性能。如果只有一个小应用没有使用绑定变量，那么即使其他应用原本设计得很好，能适当地将已解析的SQL放在共享池中以备重用，但因为这个小应用的存在，过一段时间就会从共享池中把已存储的SQL的执行计划排挤出去。这就使得这些设计得当的应用也必须再次硬解析SQL，真是一粒老鼠屎就能毁了一锅汤。注意如果你想更加清楚地了解软解析与硬解析之间的区别，我推荐Oracle Real World Performance小组的一个在线演示（http://tinyurl.com/RWP-OLTP-PARSING）。这个视频清楚地展示了软解析与硬解析在性能上的天壤之别。在一个事务型的系统上，使用软解析与硬解析在性能上可能会有着数量级上的差别，也就是说，在这一点上的差别可能会让你的系统能支撑十倍（或十分之一）的工作负荷。你也可以用这个视频来让你的开发者了解使用（或者不使用）绑定变量所造成的巨大影响。如果使用绑定变量，任何会话提交的SQL在经过编译之后会放入共享池，整个数据库的任何会话都可以重用这个编译过的查询计划（当然前提是完全一样的SQL以及SQL参照的对象也相同）。 SQL只需编译一次就可以反复使用。这样做效率很高，这也正是数据库期望采用的做法，不仅如此，数据库进程占用闩的时间也更短，而且不再那么频繁地需要闩。这些都会改善应用的性能和可扩展性。要想知道使用绑定变量在性能方面会带来多大的差别，只需要运行一个非常小的测试来看看。在这个测试中，我们将在一个表中插入一些数据，并使用如下的一个简单的表。 EODA@ORA12CR1> create table t ( x int ); Table created. 接下来创建两个非常简单的存储过程，它们都向这个表中插入数字1到10 000。不过，第一个过程使用了一条带绑定变量的SQL语句。 EODA@ORA12CR1> create or replace procedure proc1 2 as 3 begin 4 for i in 1 .. 10000 5 loop 6 execute immediate 7 'insert into t values ( :x )' using i; 8 end loop; 9 end; 10 / Procedure created. 第二个存储过程在插入每一行数据的时候，所使用的SQL都是不完全一样的。 EODA@ORA12CR1> create or replace procedure proc1 2 as 3 begin 4 for i in 1 .. 10000 5 loop 6 execute immediate 7 'insert into t values ( '||i||')'; 8 end loop; 9 end; 10 / Procedure created. 现在看来，二者之间唯一的差别是一个存储过程使用了绑定变量，而另一个没有使用。它们都使用了动态SQL，而且过程中的逻辑也是相同的。下面将使用我开发的一个简单工具runstats来比较这两种方法。 EODA@ORA12CR1> exec runstats_pkg.rs_start PL/SQL procedure successfully completed. EODA@ORA12CR1> exec proc1 PL/SQL procedure successfully completed. EODA@ORA12CR1> exec runstats_pkg.rs_middle PL/SQL procedure successfully completed. EODA@ORA12CR1> exec proc2 PL/SQL procedure successfully completed. EODA@ORA12CR1> exec runstats_pkg.rs_stop(9500) Run1 ran in 34 cpu hsecs Run2 ran in 432 cpu hsecs run 1 ran in 7.87% of the time 注意关于runstats以及其他的小脚本，请参考本书开始部分有关配置环境的章节。你可能观察到你的CPU值与这里不完全相同，实际上，你会发现所有度量结果都与这里报告的数据不同。造成这些差异的原因可能是不同的Oracle版本、不同的操作系统或者不同的硬件平台。尽管这些具体数字存在些许差别，但是这不会影响我想表达的意思。前面的结果清楚地显示出，根据CPU时间来看，不使用绑定变量插入10 000行与使用绑定变量时相比，需要更长的时间，所需的资源也大幅增加。实际上，如果不使用绑定变量，插入这些记录所需的CPU时间几乎比使用绑定变量要多一个数量级。对于每一个不使用绑定变量的INSERT语句，数据库的大部分处理时间只是用来解析SQL语句！不过，还有更糟糕的。当我们查看其他信息时，可以看到这两种方法在资源利用方面简直有天壤之别。 Name Run1 Run2 Diff STAT...CCursor + sql area evic 2 9,965 9,963 STAT...enqueue requests 35 10,012 9,977 STAT...enqueue releases 34 10,012 9,978 STAT...execute count 10,020 20,005 9,985 STAT...opened cursors cumulati 10,019 20,005 9,986 STAT...table scans (short tabl 3 10,000 9,997 STAT...sorts (memory) 3 10,000 9,997 STAT...parse count (hard) 2 10,000 9,998 LATCH.session allocation 5 10,007 10,002 LATCH.session idle bit 17 10,025 10,008 STAT...db block gets 10,447 30,376 19,929 STAT...db block gets from cach 10,447 30,376 19,929 STAT...db block gets from cach 79 20,037 19,958 LATCH.shared pool simulator 8 19,980 19,972 STAT...calls to get snapshot s 22 20,003 19,981 STAT...parse count (total) 18 20,005 19,987 LATCH.call allocation 4 20,016 20,012 LATCH.enqueue hash chains 70 20,211 20,141 STAT...consistent gets 266 40,093 39,827 STAT...consistent gets from ca 266 40,093 39,827 STAT...consistent gets pin (fa 219 40,067 39,848 STAT...consistent gets pin 219 40,067 39,848 STAT...calls to kcmgcs 117 40,085 39,968 STAT...session logical reads 10,713 70,469 59,756 STAT...recursive calls 10,058 70,005 59,947 STAT...KTFB alloc space (block 196,608 131,072 -65,536 LATCH.cache buffers chains 51,835 171,570 119,735 LATCH.row cache objects 206 240,686 240,480 LATCH.shared pool 20,090 289,899 269,809 STAT...session pga memory 65,536 -262,144 -327,680 STAT...logical read bytes from 87,760,896 577,282,048 489,521,152 Run1 latches total versus runs -- difference and pct Run1 Run2 Diff Pct 73,620 784,913 711,293 9.38% PL/SQL procedure successfully completed. runstats生成一个报告，显示了闩利用率的差别以及统计结果的不同。这里我要求runstats打印出差距大于9500的所有比较结果。可以看到，在使用绑定变量的第一种方法中，共有2次硬解析，而不使用绑定变量时，则有10 000次的硬解析（每个插入都会带来一次硬解析）。不过，硬解析方面的差别还只是冰山一角。你会看到，无绑定变量方法中使用的“闩”几乎比绑定变量方法多出一个数量级。提到这个差别，可能有人会问：“什么是闩？” 我先来回答这个问题。闩（latch）是一种锁，它是保护Oracle的共享数据结构的一种串行化设备。共享池就是系统全局区（System Global Area，SGA）中一个庞大的共享数据结构，Oracle正是在这里存储已解析、已编译的SQL。修改这个共享结构时，必须注意一次只允许一个进程访问。（如果两个进程或线程试图同时更新内存中相同的数据结构，这会很糟糕，会导致大量数据被破坏。）所以，Oracle采用了一种闩定（latching）机制，这是一种串行化访问的轻量级锁定方法。不要被“轻量级”这个词蒙住了，作为串行化设备，闩一次只允许一个进程操作内存中的数据结构。数据库在硬解析时会大量使用共享池中的闩，当应用设计不当时，它们就会成为用户频繁争抢的目标。试图同时硬解析语句的用户越多，性能就会变得越差。人们执行SQL时的解析越多，对共享池的闩竞争就越厉害，等待的队列排得越长，时间也越久。执行无绑定变量的SQL语句，就类似于每次调用程序前都要做一次编译。假设把Java源代码交付给客户，在调用类中的方法之前，客户必须调用Java编译器，编译这个类，再运行该方法，然后丢掉字节码。下一次想要执行同样的方法时，他们还要把这个过程再来一遍：先编译，再运行，然后丢掉字节码。你肯定不希望在应用中这样做，所以也不要在数据库里这样做。对于开发人员来说，不使用绑定变量还存在一个影响，即安全性，你的数据库可能会受到一种被称为“SQL注入”（SQL Injection）的攻击。如果你对这个术语还不熟悉，建议先把这本书放一放，在网上查找SQL INJECTION。在我写本书期间，查询SQL INJECTION几乎能搜索到五百万条结果，而且有充分而详细的文档说明。注意所谓“SQL注入”，就是通过把SQL命令插入到Web表单提交或输入域名或页面请求的查询字符串，最终达到欺骗服务器执行恶意的SQL命令。如果开发人员接受来自最终用户的输入，把这个输入拼接到查询中，然后编译和执行这个查询，就会存在“SQL注入”这样的安全漏洞。如果存在这样的安全漏洞，最终用户就有可能修改SQL语句，做一些应用开发人员本意之外的事情。这就像是你打开终端，登录一个SQL Plus会话并作为SYSDBA连接，然后等着有人来键入某个命令，然后编译并执行这个命令。这种漏洞的后果将是灾难性的。实际上如果你没有使用绑定变量，而且使用了之前PROC2中所示的串拼接技术，代码就很有可能遭遇“SQL注入”攻击。这种串拼接的代码一定要严格审查，而且应当由对代码作者心存芥蒂的人来审查，因为代码必须得到批评性的、客观的审查。如果审查人员与代码作者是同事，甚至审查人员是代码作者亲密的朋友或下属，那么审查将无法保证本该有的批评性和建设性。对于未使用绑定变量开发的代码，必须带着怀疑的态度去审视。不使用绑定变量应当作为一种特例情况，而绝非普遍现象。为了说明“SQL注入”攻击的危险性，请看下面这个存储过程。 EODA@ORA12CR1> create or replace procedure inj( p_date in date ) 2 as 3 l_username all_users.username%type; 4 c sys_refcursor; 5 l_query varchar2(4000); 6 begin 7 l_query := ' 8 select username 9 from all_users 10 where created = ''' ||p_date ||''''; 11 12 dbms_output.put_line( l_query ); 13 open c for l_query; 14 15 for i in 1 .. 5 16 loop 17 fetch c into l_username; 18 exit when c%notfound; 19 dbms_output.put_line( l_username || '.....' ); 20 end loop; 21 close c; 22 end; 23 / Procedure created. 注意这段代码最多只打印5个记录。它将在一个“空” Schema中执行。如果一个Schema中已经包含大量的表，可能会得到与以下所示不同的结果。一个结果就是你可能根本看不到这个例子中想要显示的表，这可能是因为我们只能打印5个记录。另一个可能的结果是会得到一个数字或数值错误，这可能是一个长表名所致。不要因为这些情况而质疑这个例子的作用，如果有人确实想窃取你的数据，他们肯定会想办法克服这些问题的。目前，我认识的大多数开发人员查看代码后都会说：这段代码不会受到“SQL注入”攻击。他们还会解释说，这是因为这段程序的输入必须是一个Oracle DATE变量，DATE是一个表示世纪、年、月、日、小时、分和秒的7字节的数据类型。他们认为DATE变量根本没办法改变SQL语句的含义。但事实上，他们都错了！这段代码确实可以“被注入”，有办法的人（显然，确实有人知道怎么做）能轻轻松松地在这段代码运行时作出修改。如果按开发人员“希望”的方式执行这个存储过程，可能会看到以下结果。 EODA@ORA12CR1> exec inj( sysdate ) select * from all_users where created = '12-MAR-14' PL/SQL procedure successfully completed. 这个结果显示出的SQL正如我们所预期，并且看上去好像安全性也没什么问题。那么，怎么会有人能以恶意的方式利用这个程序呢？下面我来解释。假设你的项目中还有一个开发人员——一个恶意的开发人员。开发人员都能够执行这个存储过程来查看目前数据库中已经创建的用户，不过开发人员无权访问存储过程所有者（Schema Owner）的其他表。现在，他们不知道这个Schema中有哪些表（安全小组认为这种“无知而安全”的策略很好），所以他们不允许任何人在任何地方发布表名。因此用户不知道存在以下这个特别的表。 EODA@ORA12CR1> create table user_pw 2 ( uname varchar2(30) primary key, 3 pw varchar2(30) 4 ); Table created. EODA@ORA12CR1> insert into user_pw 2 ( uname, pw ) 3 values ( 'TKYTE', 'TOP SECRET' ); 1 row created. EODA@ORA12CR1> commit; Commit complete. USER_PW表看起来是一个非常重要的表，但是用户并不知道这个表的存在。不过，他们可以访问INJ存储过程。 EODA@ORA12CR1> create user devacct identified by foobar; User created. EODA@ORA12CR1> grant create session to devacct; Grant succeeded. EODA@ORA12CR1> grant execute on inj to devacct; Grant succeeded. 这样，恶意的开发人员或用户只需执行下面的代码就可以了。 EODA@ORA12CR1> connect devacct/foobar; Connected. DEVACCT@ORA12CR1> alter session set 2 nls_date_format = '"''union select tname from tab--"'; Session altered. DEVACCT@ORA12CR1> exec eoda.inj( sysdate ) select username from all_users where created = ''union select tname from tab--' USER_PW..... PL/SQL procedure successfully completed. 在这段代码当中，SELECT执行了下面这个语句（返回零行）： select username from all_users where created ='' 以及这个语句，并将结果联合起来。 select tname from tab 让我们看一下最后的 --'部分。在SQL*Plus中，双减号主要是用于注释。这里是为了将后面的引号注释掉，从而使语句在语法上是正确的。这个NLS_DATE_FORMAT很有意思，大多数人甚至不知道可以在NLS_DATE_FORMAT中嵌入字符常量。（另外，很多人甚至不知道日期格式竟然是可以更改的，他们更不知道即使没有ALTER SESSION权限，也可以在当前的数据库连接会话中更改NLS_DATE_FORMAT。）在这里，恶意用户的做法就是使用你的权限集“蒙骗”代码查询你原本不希望他查询的表。TAB是一个字典视图，它可以返回当前用户所能看到的所有表。用户运行这个过程时，实际上是以存储过程所有者身份在运行的，也就是说，他在运行这个存储过程时拥有存储过程所有者的全部权限。如此一来，他就能看到这个用户中所有的表。当他看到表USER_PW时，觉得“嗯，好像有点意思。”于是，接下来他尝试访问这个表。 DEVACCT@ORA12CR1> select * from eoda.user_pw; select * from eoda.user_pw * ERROR at line 1: ORA-00942: table or view does not exist 恶意用户不能直接访问这个表，他没有对这个表进行SELECT的权限。不过，不用担心，还另有办法。用户想知道这个表中有哪些列，他可以采用如下方法。 DEVACCT@ORA12CR1> alter session set 2 nls_date_format = '"''union select tname||''/''||cname from col--"'; Session altered. DEVACCT@ORA12CR1> exec eoda.inj( sysdate ) select username from all_users where created = ''union select tname||'/'||cname from col--' USER_PW/PW..... USER_PW/UNAME..... PL/SQL procedure successfully completed. 这样一来，我们就知道了列名。既然已经知道表名及其列名，下面可以再一次修改NLS_DATE_ FORMAT来直接查询这个表。所以，接下来恶意用户可以进行如下操作。 DEVACCT@ORA12CR1> alter session set 2 nls_date_format = '"''union select uname||''/''||pw from user_pw--"'; Session altered. DEVACCT@ORA12CR1> exec eoda.inj( sysdate ) select username from all_users where created = ''union select uname||'/'||pw from user_pw--' TKYTE/TOP SECRET..... PL/SQL procedure successfully completed. 如此一来，恶意开发人员或用户现在就掌握了你最为机密的用户名和密码信息。这还没完，如果这个开发人员有CREATE PROCEDURE权限呢？这很有可能（他毕竟是个开发者）。在这个例子中他还能再进一步吗？当然！这个看上去无辜的存储过程把EODA用户可以读取的全部的对象都暴露了出来，并且，如果利用这个缺陷的开发者用户有CREATE PROCEDURE权限，那么通过下面这个存储过程，他将可以执行任何在EODA权限范围内的命令。下面让我们看看是怎么发生的。 DEVACCT@ORA12CR1> connect eoda/foo Connected. EODA@ORA12CR1> grant create procedure to devacct; Grant succeeded. EODA@ORA12CR1> connect devacct/foobar; Connected. 注意在本例中，我们假设EODA具有DBA角色权限及管理选项（ADMIN OPTION）。这里作为开发者，我们创建一个函数来赋予DBA权限。这个函数有两点需要注意：定义时必须指定以有调用者权限执行，即谁来执行这个函数，那么这个函数在执行的过程中就具有谁的权限；另外一点就是他必须调用自治事务，也就是说它将在函数的执行过程中创建子事务，并在控制权返还给函数前完成提交或者回退，从而能够让其在SQL执行的过程中可以被正常调用。下面就是这个函数。 DEVACCT@ORA12CR1> create or replace function foo 2 return varchar2 3 authid CURRENT_USER 4 as 5 pragma autonomous_transaction; 6 begin 7 execute immediate 'grant dba to devacct'; 8 return null; 9 end; 10 / Function created. 现在我们需要做的就是设法让EODA用户（可以给其他用户赋DBA权限）调用这个函数。有了上面的“SQL注入”的缝隙，我们就能很轻松地通过NLS_DATE_FORMAT调用这个函数，并且将此函数的执行权限赋予EODA。 DEVACCT@ORA12CR1> alter session set 2 nls_date_format = '"''union select devacct.foo from dual--"'; Session altered. DEVACCT@ORA12CR1> grant execute on foo to eoda; Grant succeeded. 欢呼吧，我们有了DBA权限。 DEVACCT@ORA12CR1> select * from session_roles; no rows selected DEVACCT@ORA12CR1> exec eoda.inj( sysdate ) select username from all_users where created = ''union select devacct.foo from dual--' ..... PL/SQL procedure successfully completed. DEVACCT@ORA12CR1> connect devacct/foobar Connected. DEVACCT@ORA12CR1> select * from session_roles; ROLE ------------------------------------------------------------------------------- DBA SELECT_CATALOG_ROLE ... XS_RESOURCE OLAP_DBA 24 rows selected. 提示通过视图ROLE_ROLE_PRIVS，我们可以查看各个角色相互赋予的权限。你该怎么保护自己呢？就是要使用绑定变量。 EODA@ORA12CR1> create or replace procedure NOT_inj( p_date in date ) 2 as 3 l_username all_users.username%type; 4 c sys_refcursor; 5 l_query varchar2(4000); 6 begin 7 l_query := ' 8 select username 9 from all_users 10 where created = :x'; 11 12 dbms_output.put_line( l_query ); 13 open c for l_query USING P_DATE; 14 15 for i in 1 .. 5 16 loop 17 fetch c into l_username; 18 exit when c%notfound; 19 dbms_output.put_line( l_username || '.....' ); 20 end loop; 21 close c; 22 end; 23 / Procedure created. EODA@ORA12CR1> exec NOT_inj(sysdate) select username from all_users where created = :x PL/SQL procedure successfully completed. 有一点相当简单明了：如果使用绑定变量，就不会遭遇“SQL注入”攻击。如果没有使用绑定变量，你必须谨慎审查每一行代码，把自己当成一个心怀鬼胎的天才（通晓Oracle的方方面面），看看有没有办法攻击这段代码。我不了解你的情况，不过就我而言，如果我能确信我的代码中99.9999%都不会受到“SQL注入”攻击，而只需担心剩下0.0001%的代码（出于某种原因，这里没有使用绑定变量），我晚上肯定会睡得更踏实，而不至于因为担心100%的代码都可能遭到“SQL注入”攻击而夜不能寐。总之，对于本节开头描述的项目，唯一的解决办法就是重写现有的代码来使用绑定变量。重写后的代码运行速度将呈数量级增长，而且系统所能支持的并发用户数也能成倍增加。不仅如此，代码也更为安全，不再需要审查整个代码库来检查是否存在“SQL注入”攻击问题。不过，这种安全性是以时间和精力的高昂代价换来的，因为我的客户必须在编写系统之后再重新编写。这并不是说使用绑定变量很困难，也不是说这样容易出错，而只是因为他们没有从一开始就使用绑定变量，所以不得不返工，被迫重新检查几乎所有代码并全面修改。如果开发人员从第一天起就懂得在应用中使用绑定变量的重要性，我的客户就不必付出这种高昂代价了。 1.3.2　理解并发控制并发控制在不同的数据库中机制各不相同。正是因为并发控制的差异，才使得数据库不同于文件系统，也使得不同的数据库彼此有所区别。作为程序员，一定要保证你的数据库应用程序在并发访问条件下能够正常地工作。这一点很重要，但这也是人们时常疏于测试的一个方面。有些程序在一切都按照顺序方式执行的情况下可能工作得很好，但是如果任务要同时进行，这些程序的表现可能就差强人意了。如果对数据库如何实现并发控制了解不够，可能会有下列后果： 破坏数据的完整性；  随着用户数的增多，应用的运行速度减慢；  不能很好地扩展应用来支持大量用户。  注意我没有说“你可能……”或者“有……的风险”，你肯定会遇到这些情况，甚至不会意识到你将陷入到这些麻烦中。如果没有正确的并发控制，你将会破坏数据的完整性，因为有些程序单独能工作，但是在多用户情况下就不能像你预期的那样正常工作了。应用也会比预想的速度要慢，因为它总在等待资源。另外，因为存在锁和竞争问题，你的应用将不会有很好的扩展性。随着访问资源的等待队列变得越来越长，等待的时间也会越来越久。这里可以打个比方，考虑一下发生在收费站的阻塞情况。如果所有汽车都以顺序的、可预料的方式到来，一辆接着一辆，就不会出现阻塞。但是，如果多辆车同时到达，就要排队。另外，等待时间并不是按照到达收费站的车辆数量线性增长。达到某个临界点后，一方面要花费大量额外的时间来“管理”排队等待的人，另一方面还要为他们提供服务（在数据库中这称为上下文切换）。并发问题最难跟踪，就像调试多线程程序一样。在调试工具的可控人工环境下，程序可能工作得很好，但到实际中却可怕地崩溃了。例如，在竞争条件下，你会发现两个线程试图同时修改一个数据结构。这种bug跟踪起来非常困难，也很难修正。如果只是使用单用户来进行测试，然后部署，并交给数十个并发用户使用，就很有可能遭遇原先未能检测到的并发问题。在下面两节中，我会使用两个小例子，来谈谈如果对并发控制缺乏了解，将会如何破坏数据，或影响应用的性能和可扩展性。 1. 锁机制数据库使用锁（lock）来保证任何时刻最多只有一个事务在修改给定的一段数据。实质上讲，正是锁机制才使并发控制成为可能。如果没有某种锁定模型来阻止对同一行的并发更新，数据库就不可能提供多用户访问。不过，如果滥用或者使用不当，“锁”反倒会阻碍并发性。如果你或数据库本身对数据进行不必要的加锁，那么能并发完成操作的人数就会减少。因此，要理解什么是“锁”，以及在数据库中“锁”是怎样工作的，这对于开发可扩展的、正确的应用至关重要。还有一点很重要，要知道每个数据库会以不同的方式实现锁机制。有些数据库可能有“页级锁”，另外一些则有“行级锁”；有些实现会把“行级锁”升级为“页级锁”，另外一些则不然；有些使用“读锁”，另外一些不使用；有些通过锁机制实现串行化事务，另外一些则通过数据的“读一致”来实现（没有“锁”）。如果不清楚这些微小的差别，它们就会逐步膨胀为严重的性能问题，甚至演变成致命的bug。以下是对Oracle锁机制策略的总结。 Oracle只在修改时才对数据加“行级锁”，正常情况下不会升级到“块级锁”或“表级锁”。  如果只是读数据，Oracle绝不会对数据加锁。  写操作不会阻塞读操作。换种说法：读不会被写阻塞。这一点几乎与其他许多数据库都不一样。在其他数据库中，读往往会被写阻塞。尽管听上去这个特性似乎很不错（一般情况下确实如此），但是，如果没有充分理解这个思想，而且想通过应用逻辑对应用施加完整性约束，就极有可能做得不对。  如果会话A想更改一行数据，但这行数据已经被另外一个会话B锁定，此时会话A才会被阻塞。但是如果会话A仅仅是想读取数据则不会被阻塞。  开发应用时必须考虑这些因素，而且还要认识到这个策略是Oracle所独有的，每个数据库实现锁机制的方法都存在细微的差别。即使在应用中使用最通用的SQL，由于各数据库开发商采用的锁机制和并发控制模型不同，应用也可能有不同的表现。倘若开发人员不了解自己的数据库是如何处理并发的，肯定会遇到数据完整性问题。（开发人员从另外某种数据库转向Oracle，或者从Oracle转向其他数据库时，如果没有考虑在应用中采用不同的并发机制，那就很可能破坏数据的完整性。） 2. 防止“丢失更新”（Lost Updates） Oracle的无阻塞方法有一个副作用：如果确实想保证一次最多只有一个用户访问一行数据，开发人员就得自己做些工作。考虑下面这个例子。一位开发人员向我展示了他刚开发的一个资源调度程序（可以用来调度会议室、投影仪等资源），这个程序正在部署当中。应用中实现了这样一个业务规则：在任何时间段都不能将一种资源分配给多个人。也就是说，应用中包含了实现这个业务规则的代码，它会明确地检查此前这个时间段没有将某个资源分配给其他用户（至少，这个开发人员认为是这样）。这段代码先查询SCHEDULES表，如果不存在与该时间段重叠的记录行（该时间段尚未分配），就插入新行。所以，开发人员设计了下面的两个表。 EODA@ORA12CR1> create table resources 2 ( resource_name varchar2(25) primary key, 3 other_data varchar2(25) 4 ); Table created. EODA@ORA12CR1> create table schedules 2 ( resource_name varchar2(25) references resources, 3 start_time date, 4 end_time date 5 ); Table created. 在往SCHEDULES表中插入一个房间预订之后，提交之前，应用将进行如下查询。 EODA@ORA12CR1> select count(*) 2 from schedules 3 where resource_name = :resource_name 4 and (start_time < :new_end_time) 5 AND (end_time > :new_start_time) 6 / 看上去很简单，也很安全（在开发人员看来）。如果得到的计数为零，这个房间就是你的了。如果返回的数大于零，那在此期间就不能预订这个房间。了解逻辑后，我建立了一个非常简单的测试，来展示这个应用运行时可能出现的一个错误，这个错误极难跟踪，而且事后也很难诊断。有人甚至以为这必定是一个数据库bug。我所做的其实很简单，就是让另外一个人使用这个开发人员旁边的一台机器。两个人都浏览同一个屏幕，然后一起数到3时，两人都单击Go按钮，尽量同时预订同一个房间。结果两个人都预订成功。这个逻辑独立执行时原本能很好地工作，但到多用户环境中就不行了。为什么会出现这个问题？部分原因就在于Oracle的非阻塞读。这两个会话都不会阻塞对方，它们只是运行查询，然后完成调度房间的逻辑。两个会话都通过运行查询来查找是否已经有预订，尽管另一个会话可能已经开始修改SCHEDULES表，但查询看不到这些修改（所做的修改在提交之前对其他会话来说是不可见的，而等到提交时已为时过晚）。由于这两个会话并没有试图修改SCHEDULES表中的同一行，所以它们不会相互阻塞。由此说来，这个应用不能像预期的那样保证前面提到的业务规则。这个结果让这位开发者很吃惊，他开发过很多数据库应用，但都是在有读取锁的数据库上。也就是说，数据读取者会阻塞写入者，反之亦然。在他的世界里，其中的一个事务会阻塞另外一个事务，或可能会进入死锁状态，总之事务会失败。开发人员需要一种方法，使得这个业务规则在多用户环境下也能得到保证，也就是要确保一次只有一个人预订一种给定的资源。在这种情况下，解决方案就是加入他自己的一些串行化机制。除了前面的count(*)外，开发人员首先需要完成以下查询。 select * from resources where resource_name = :resource_name FOR UPDATE; 这里，他在调度资源之前先锁定了资源（这里指房间），换句话说，就是在SCHEDULES表中查询该资源的预订情况之前先锁定资源。通过锁定所要调度的资源，开发人员可以确保别人不会同时修改对这个资源的调度。其他人都必须等待，直到他提交了事务为止，此时就能看到他所做的调度。这样就杜绝了调度重叠的可能性。开发人员必须了解到，在多用户环境中，他们必须要使用多线程编程中的一些技术。在这里，FOR UPDATE子句的作用就像是一个信号量（semaphore），它只允许串行访问RESOURCES表中特定的行，这样就能确保不会出现两个人同时调度同一资源的情况。使用FOR UPDATE方法仍是高度并发的，因为可能有数以千计可预订的资源。这里的做法是，对于任一给定的资源，确保任何时刻只能有一个人进行预订。在数据库层面，相当于手动地（FOR UPDATE从句）锁定了“资源”表中的数据，但是更新这个动作发生在“调度”表上，这种处理方式是很少见的。要知道哪些情况下需要这样做，还要知道哪些情况下不需要这样做（稍后会给出这样一个例子），这同样很重要。另外，FOR UPDATE不会阻塞其他读的会话（无FOR UPDATE）（这在其他数据库中可能不是这样），所以这种解决方案在这里具有很好的可扩展性。本节所讨论的内容在数据库移植项目中有着相当大的影响，如果你不注意并发控制，那么一定会在这点上吃苦头。例如，你有使用另外某种数据库的经验。在这种数据库上，“读”“写”动作会相互阻塞，并且你可能会依赖这一点来保护数据的完整性。保护数据的完整性，其中一种解决办法是干脆不要并发，这在许多非Oracle数据库中就是这样做的。但Oracle则尊崇“并发至上”的原则，你必须在这点上要意识到Oracle与其他数据库的不同之处（否则可能会遭受相应的后果）。在与开发人员针对设计进行讨论时，我发现，即使已经向他们展示了这种例子，开发人员还是认为没有必要真正了解所有这些是如何工作的。他们对需要深入了解工作原理的想法往往嗤之以鼻。他们会这样回应：“我们只需要查看Hibernate应用中的‘事务’箱，它会负责为我们处理所有事务工作，所以我们不需要了解这些底层工作。”但我对他们说：“Hibernate会为SQL Server、DB2和Oracle生成不同的代码吗？要知道，是完全不同的代码，SQL语句数量不一，逻辑也不同，会这样吗？”他们认为不会，但是他们又继续辩解说产生的SQL都具有事务性。这就存在一个误解。在这里，事务性只是指支持提交和回滚，而不表示代码能够保证在“事务”上的一致性（可以理解为“不能保证代码是正确的”）。不论你使用哪个工具或框架来访问数据库，如果不希望破坏数据，就一定要对并发控制有所了解。 99%的情况下，锁机制是完全透明的，无需操心。但还有另外的1%，你必须清楚哪些情况下需要自己考虑锁机制。对于这个问题，并没有非黑即白的直接结论，无法简单地罗列出“如果你想这样，就应该这样做”之类的条条框框。关键是要了解应用在多用户环境中有何表现，另外在数据库中表现如何。在本书的锁机制与并发控制章节，我们将会更深入地讨论这个内容，你会进一步了解本节介绍的这种完整性约束。有些情况下，一个表中的数据必须遵循某个规则，或者两个表或多个表之间必须保证某个规则（如参照完整性约束），一定要特别注意这些情况，而且这些情况也最有可能需要采用手动锁定或者另外某种技术来确保多用户环境下的数据完整性。 1.3.3　多版本控制这个话题与并发控制的关系非常紧密，因为这正是Oracle并发控制机制的基础。Oracle采用了一种多版本、读一致（read-consistent）的并发模型。我们将在第7章更详细地介绍有关的技术。不过，实质上讲，Oracle利用这种机制提供了以下特性。 读一致查询：对于一个给定的时间点（point in time），查询会产生一致的结果。  非阻塞查询：查询的会话不会被写入的会话阻塞，但在其他数据库中可能不是这样。  Oracle数据库中有两个非常重要的概念：多版本与读一致性。多版本（multi-versioning）一词实质上指Oracle数据库能够同时维护多个版本（或时间点）（从1983年的3.0版本起）的数据。读一致性反映了一个事实：Oracle中的查询会以一个一致的时间点为基准返回结果，查询使用的每个块都会以这个时间点为基准，即使在你执行查询时数据块被修改或锁定也不会影响结果。如果理解了多版本和读一致性如何工作，你就会知道能从数据库得到什么样的结果。在进一步深入讨论Oracle如何实现多版本之前，下面用我认为最简单的一个方法来演示Oracle中的多版本。 EODA@ORA12CR1> create table t 2 as 3 select username, created 4 from all_users 5 / Table created. EODA@ORA12CR1> set autoprint off EODA@ORA12CR1> variable x refcursor; EODA@ORA12CR1> begin 2 open :x for select * from t; 3 end; 4 / PL/SQL procedure successfully completed. EODA@ORA12CR1> declare 2 pragma autonomous_transaction; 3 -- you could do this in another 4 -- sqlplus session as well, the 5 -- effect would be identical 6 begin 7 delete from t; 8 commit; 9 end; 10 / PL/SQL procedure successfully completed. EODA@ORA12CR1> print x USERNAME CREATED ------------------------------ --------- DEVACCT 02-SEP-13 OPS$MELANIE 17-JUL-13 SCOTT 03-JUL-13 OPS$TKYTE 02-SEP-13 APEX_040200 28-JUN-13 APEX_PUBLIC_USER 28-JUN-13 ... AUDSYS 28-JUN-13 SYS 28-JUN-13 36 rows selected. 在前面的例子中，我创建了一个测试表T，并把ALL_USERS表的一些数据加载到这个表中，然后在这个表上打开一个游标，但是我并没有马上从该游标读取数据，只是保持游标打开的状态而已。注意要记住，Oracle并不会为任何查询预先准备数据。打开游标时，Oracle不复制任何数据。可以想想看，如果一个表有十亿条记录，是不是也能很快就打开游标？没错，游标会立即打开，它会边行进边回答查询。换句话说，只是在你获取数据时它才从表中读数据。在同一个会话中（或者也可以在另一个会话中，效果一样），再从该表删除所有数据，接着用COMMIT提交删除所做的工作。这时表中的记录都没有了，但是真的没有了吗？实际上，我们还是可以通过游标读取到数据（或通过闪回查询的AS OF从句）。OPEN命令返回的结果集在打开的那一个时间点就已经确定。游标打开时，我们根本没有碰过表中的任何数据块，但答案已经是铁板钉钉的了。获取数据之前，我们无法知道答案会是什么。不过，从游标角度看，结果则是确定下来的。打开游标时， Oracle并非将所有数据复制到另外某个位置。实际上是DELETE命令在删除数据之前，把这些数据放在一个被称为undo段（undo segment）的数据区，这个数据区也被称为回滚段（rollback segment）。 1. 闪回在9i版本之前，Oracle查询的结果是由某个时间点来确定的（从这个时间点开始查询是一致的）。也就是说，Oracle会保证返回的结果集是基于以下两个时间点之一。 游标打开时的（亦或查询开始真正执行的）时间点。这是READ COMMITTED隔离模式的默认动作（第7章将介绍READ COMMITTED、READ ONLY和SERIALIZABLE事务级别之间的差别）。  查询所属事务开始的时间点。这是READ ONLY和SERIALIZABLE隔离级别中的默认动作。  不过，从Oracle Database 9i开始，我们可以通过闪回查询特性来指示Oracle提供任何指定时间的查询结果（对于这个时间可能会有一些限制）。利用闪回查询的特性，我们可以更直接地看到读一致性和多版本控制。注意闪回数据归档（flashback data archive）可以闪回查询很久之前的数据（过去的数月甚至数年），这是Oracle Database 11g Release 1开始增加的特性。它没有使用读一致性和多版本控制来生成之前某个时间点的数据，而是使用了归档中的“前映像”副本。稍后还会讨论闪回数据归档。从11.2.0.4起，闪回数据归档是作为一个免费的选件提供给用户的，之前版本需要付费购买。请考虑以下例子。首先我们有一个SCN，也就是系统修改号（System Change Number）或系统提交号（System Commit Number），这两个术语可互换使用。SCN是Oracle的内部时钟，每次发生提交时，这个时钟就会开始计时（递增）。实际上我们也可以使用日期或时间戳，不过这里SCN很容易得到，而且相当精准。 SCOTT@ORA12CR1> variable scn number SCOTT@ORA12CR1> exec :scn := dbms_flashback.get_system_change_number; PL/SQL procedure successfully completed. SCOTT@ORA12CR1> print scn SCN ---------- 13646156 注意你的系统可能会限制访问DBMS_FLASHBACK包。在我的数据库中已经为SCOTT授予了执行这个包的权限，你可能也得这么做。拿到了SCN之后，我们就可以告诉Oracle要查询的时间点。以后再查询Oracle时，我们就能看看这个时间点上表中的内容。首先来看EMP表中现在有什么。 SCOTT@ORA12CR1> select count(*) from emp; COUNT(*) ---------- 14 下面把这些信息都删除，并验证数据是否确实没有了。 SCOTT@ORA12CR1> delete from emp; 14 rows deleted. SCOTT@ORA12CR1> select count(*) from emp; COUNT(*) ---------- 0 SCOTT@ORA12CR1> commit; Commit complete. 不过，我们可以使用闪回查询（即AS OF SCN或AS OF TIMESTAMP子句）来看看某时间点上表中有什么。 SCOTT@ORA12CR1> select count(*), 2 :scn then_scn, 3 dbms_flashback.get_system_change_number now_scn 4 from emp as of scn :scn; COUNT(*) THEN_SCN NOW_SCN ---------- ---------- ---------- 14 13646156 13646157 如果你使用的是Oracle Database 10g 及以上版本，还有一个flashback命令，它使用了这种底层多版本技术，可以把对象返回到以前某个时间点的状态。在这个例子中，我们可以将EMP表闪回到删除信息前的那个时间点（在使用闪回表之前，我们还需要为表启用行移动，这是使用闪回表的一个前提条件）。 SCOTT@ORA12CR1> alter table emp enable row movement; Table altered. SCOTT@ORA12CR1> flashback table emp to scn :scn; Flashback complete. SCOTT@ORA12CR1> select cnt_now, cnt_then, 2 :scn then_scn, 3 dbms_flashback.get_system_change_number now_scn 4 from (select count(*) cnt_now from emp), 5 (select count(*) cnt_then from emp as of scn :scn) 6 / CNT_NOW CNT_THEN THEN_SCN NOW_SCN ---------- ---------- ---------- ---------- 14 14 13646156 13646786 这就是所有有关“读一致性”和“多版本控制”的内容。如果我们不理解Oracle多版本控制的工作方式和意义，就不能充分利用Oracle，也不能用Oracle编写正确的保证数据完整性的应用。注意闪回表特性只在Oracle数据库企业版提供。 2. 读一致性和非阻塞读下面来看多版本、读一致查询以及非阻塞读的含义。如果不熟悉多版本这个概念，下面的代码看起来可能有些奇怪。为简单起见，这里假设读取的表在每个数据库块（数据库中最小的存储单元）中只存放一行，而且这个例子要做全表扫描。待查询的表是一个简单的ACCOUNTS表。其中包含了一家银行的账户余额。其结构很简单。 create table accounts ( account_number number primary key, account_balance number ); 在实际中，ACCOUNTS表中可能有上百万行记录，但是为了力求简单，这里只考虑一个仅有4行的表（第7章还会更详细地分析这个例子），如表1-1所示。表1-1　ACCOUNTS表的内容行账　　号账户余额（美元） 1123500.00 （续）行账　　号账户余额（美元） 2234250.00 3345400.00 4456100.00 我们可能想运行一个日报表，看看银行里有多少钱。这是一个非常简单的查询： select sum(account_balance) from accounts; 这个例子的答案很明显：$1250。不过，如果现在读了第1行，准备读第2行和第3行时，一台自动柜员机（ATM）针对这个表发生了一个事务，将$400从账户123转到了账户456，这会发生什么情况呢？当查询扫描到第4行的时候发现其余额为$500，最后就得到了$1650，是这样吗？当然不对，应该避免这种情况，因为任何时刻账户余额列中的实际总额都不是这个数。读一致性就是Oracle为避免发生这种情况所采用的办法，而这种方法与其他大部分的数据库截然不同，并且你非常有必要详细地了解。在许多其他数据库中，如果想得到“一致”和“正确”的查询答案，就必须在计算总额时锁定整个表，或者在读取记录时锁定数据。这样一来，当你读取结果时其他人就不能对数据进行修改。如果提前锁定表，就会得到查询开始时数据库中的结果。如果在读取时对数据加锁［这通常被称为共享读锁（shared read lock），可以防止更新，但不妨碍其他人访问数据］，就会得到查询结束时数据库中的结果。这两种方法都会大大影响并发性。由于存在表锁，查询期间会阻止对整个表进行更新（对于一个仅有4行的表，这可能只是很短的一段时间，但是对于有上百万行记录的表，可能就会达到几分钟）。“边读边锁定”的办法也有问题，这种方式不允许对已经读取和已经处理过的数据再做更新，实际上这会导致查询与其他更新之间产生死锁。我之前说过，如果没有理解多版本控制的概念，就无法充分利用Oracle。下面告诉你一个原因。Oracle会利用多版本控制来得到结果，也就是查询开始时那个时间点的结果，在查询的过程中，不需要对任何数据加锁（转账事务更新第1行和第4行时，被执行更新操作的会话会在这些行上加锁，但被读取的会话不会对其加锁，如这里的SELECT SUM...查询）。实际上，Oracle根本没有也不需要“共享读”锁（这是其他数据库中一种常用的锁）。对于可能妨碍并发性的一切因素，只要能去掉的，Oracle都已经去掉了。我见过这样一些实际案例，开发人员没有很好地理解Oracle的多版本控制功能，他编写的查询报表将整个系统紧紧地锁起来。之所以会这样，主要是因为开发人员想从查询得到读一致的（即正确的）结果。这个开发人员以前用过其他一些数据库，在这些数据库中，要做到这一点都需要对表加锁，或者使用一个SELECT...WITH HOLDLOCK（这是SQL Server中的一种锁机制，可以边读取边以共享模式对行加锁）。所以开发人员需要在运行报表前先对这个表加锁，或者使用SELECT...FOR UPDATE（这是Oracle中与WITH HOLDLOCK最接近的命令）。这就导致系统实质上会停止处理事务，而这完全没有必要。那么，Oracle如何在读取时既得到正确一致的答案（$1250）又不锁定任何数据呢？也就是说，如何做到不降低并发性？秘密就在于Oracle使用的事务机制。只要我们修改数据，Oracle就会在两个不同的位置（redo和undo）进行记录（大多数其他数据库可能会把redo/undo放在一起，并将其当成“事务数据”）。放在redo中的记录用于重做或者“前滚”事务，比如INSERT，Oracle就会在redo记下插入的行；如果是DELETE，则是一个简单的消息（删除文件X、块Y、行槽Z中的行）。放在undo中的记录是为了对应事务失败或者回滚而准备的，此时，Oracle会从undo中读取“修改前的数据镜像”来恢复数据。此外， Oracle还会用它构建数据块修改前的状态，也就是说可以把块恢复到查询开始时的状态。这样一来，你不仅可以得到正确一致的答案，也不会对任何数据加锁。所以，对我们这个例子来说，Oracle得到的答案如表1-2所示。表1-2　实际的多版本控制例子时　　间查　　询转账事务 T1读第1行；到目前为止balance = $500、sum = $500 T2更新第1行。对第1行加一个排他锁（也称独占锁，exclusive lock)，阻止其他更新。第1行现在有$100 T3读第2行；到目前为止balance = $250、sum = $750 T4读第3行；到目前为止balance = $400、sum = $1150 T5更新第4行。对第4行加一个排他锁，阻止其他更新（但不阻止读操作）。第4行现在有$500 T6读第4行，发现第4行已修改。这会将块回滚到T1时刻的状态。查询从这个块读到值$100 T7提交事务 T8得到答案$1250 在T6时，Oracle有效地“摆脱”了事务加在第4行上的锁。非阻塞读是这样实现的：Oracle只看数据是否改变，它并不关心数据当前是否被锁住（如发现数据被锁则意味着数据已经改变）。Oracle这时会从回滚段中取回原来的值，并继续处理下一个数据块。这是又一个清楚展示多版本控制的例子。在数据库中，我们可以得到同一个信息处于不同时间点的多个版本。Oracle能充分使用不同时间点的数据快照来提供读一致查询和非阻塞读。在Oracle数据库中，SQL语句总是以一个一致性的视角来读取/处理数据的。每个SQL语句的结果在其开始的那个时间点就已经确定下来了，所以下面的语句可以插入可预知的数据集。 for x in (select * from t) loop insert into t values (x.username, x.user_id, x.created); end loop; SELECT * FROM T的结果在查询开始执行时就已经确定了。这个SELECT并不看INSERT生成的任何新数据。倘若真的能看到新插入的数据，这条语句就会陷入一个无限循环。如果INSERT在T中生成了更多的记录，而SELECT也随之能“看到”这些新插入的行，前面的代码就会建立数目未知的记录。如果表T刚开始有10行，等结束时T中可能就会有20、21、23或无限行记录，这完全不可预测。Oracle为所有语句都提供了这种读一致性，所以如下的INSERT语句的结果也是可预知的。 insert into t select * from t; 由于上面所解释的SELECT语句的读一致性，这条INSERT语句看不到自己刚刚插入的行，而只能看到插入操作刚开始时表中已有的记录。许多数据库甚至不允许前面的这种递归语句，因为它们不知道到底可能插入多少行。所以，如果用惯了其他数据库，只熟悉这些数据库中处理查询一致性和并发性的方法，或者根本没有接触过这些概念（或者说根本没有使用数据库的经验），那你现在就应该知道，理解Oracle的做法对你来说有何等重要的意义。要想最大限度地发挥Oracle的潜能，实现正确的代码，你必须了解在Oracle中这些问题是怎么解决的（而不是在其他数据库中是如何实现的）。 1.3.4　数据库独立性至此，你可能想到这一节要讲什么了。我提到了其他的数据库，也谈到各种数据库中会以不同的方式实现其特性。除了一些只读应用外，我的观点是：要构建一个完全独立于数据库的应用，而且是高度可扩展的应用，是极其困难的。实际上，这几乎不可能，除非真正了解每种数据库具体如何工作。另外，如果清楚每种数据库工作的具体细节，就会知道，数据库独立性可能并不是你真正想要的。（这个说法有点绕！）例如，再来看最早提到的资源调度例子（增加FOR UPDATE子句之前）。假设我们在另一个数据库上开发这个应用，这个数据库有着与Oracle完全不同的锁机制/并发模型。我想说的是，如果把应用从一种数据库移植到另一种数据库，就必须验证它在完全不同的环境下还能正常地工作，而且为此我们要做大幅修改！假设我们把这个资源调度应用部署在这样一个数据库上，它采用了阻塞读机制（读会被写阻塞）。现在业务规则通过一个数据库触发器实现（这个触发器会在INSERT之后、事务提交之前，查询表中要预定的时间段的记录只有一行，也就是这条插入的记录）。在阻塞读系统中，如果一个表中存在新插入但并未提交的数据，那么其他读取数据的会话必须要等待。现在假设第一个预订者要在星期五的下午2:00到下午3:00使用“房间A”，然后他插入了一条预订记录。在他提交之前，由于读阻塞机制，第二个预订者将不能读取表中的记录，也就是说，触发器在读取数据（来确认房间在某个时段是否可用）时会被阻塞。在这个采用阻塞读机制的数据库中，我们的应用显然可以正常工作（不过如果两个人都插入自己的行，然后试图读对方的数据，就有可能得到一个死锁，这个概念将在第6章讨论），但不能并发工作，因为我们是一个接一个地检查是否存在重叠的资源分配。如果把这个应用移植到Oracle，并简单地认为它也能同样地工作，结果可能让人震惊。由于Oracle会在行级锁定，并提供了非阻塞读，所以看上去一切都乱七八糟。如前所示，我们必须使用FOR UPDATE子句来完成串行访问。如果没有这个子句，两个用户就可能同时调度同一个资源。如果我们不了解所用数据库在多用户环境中如何工作，就会直接导致这样的后果。将应用从数据库A移植到数据库B时，我时常遇到这种问题：应用在数据库A上原本无懈可击，到了数据库B上却不能工作，或者表现得很离奇。看到这种情况，我们的第一个想法往往是，数据库B是一个“不好的”数据库。而真正的原因其实是数据库B的工作方式完全不同。没有哪个数据库是错的或“不好的”，它们只是有所不同而已，了解它们如何工作将对我们处理这些问题有很大的帮助。同样的，将应用从Oracle移植到SQL Server时，也会暴露SQL Server的阻塞读和死锁问题，换句话说，不论从哪个方向移植都可能存在问题。例如，有人请我帮忙将一些TSQL（SQL Server的存储过程语言）转换为PL/SQL。做这个转换的开发人员一直在抱怨Oracle中SQL查询返回的结果是“错的”。查询如下所示。 declare l_some_variable varchar2(25); begin if ( some_condition ) then l_some_variable := f( ... ); end if; for x in ( select * from T where x = l_some_variable ) loop ... 这个查询的目标是：在T表中，如果不满足某个条件，则找出x为NULL的所有行；如果满足某个条件，就找出x等于某个特定值的所有行。开发人员抱怨说，在Oracle中，如果L_SOME_VARIABLE未设置为一个特定的值（仍为NULL），这个查询居然不返回任何数据。但是在Sybase或SQL Server中不是这样的，查询会找到x为NULL值的所有行。这个问题在应用从Sybase或SQL Server移植到Oracle中时非常普遍。ANSI SQL采用一种三值逻辑（即TRUE、FALSE和UNKNOWN）来运作，Oracle正是按此要求来实现NULL值的。基于这些规则的要求，x与NULL的比较结果既不为true也不为false，也就是说，它是未知的（unknown）。从以下代码可以看出我的意思。 EODA@ORA12CR1> select * from dual where null=null; no rows selected EODA@ORA12CR1> select * from dual where null <> null; no rows selected EODA@ORA12CR1> select * from dual where null is null; D - X 第一次看到这些结果，你可能会觉得有点不可思议。这个例子说明，在Oracle中，NULL与NULL既不相等，也不是完全不相等。默认情况下，SQL Server不是这样处理。在SQL Server和Sybase中，NULL就等于NULL。（这是其默认行为。在SQL Server当前版本中，默认行为有所修改，以反映ANSI标准。）没有任何一个数据库的处理不对，它们只是方式不同罢了。实际上，所有这些数据库都符合ANSI（符合ANSI并不是说100%符合ANSI所定的标准，我们在下面一节“标准的影响”会更详细地讨论这个问题），它们的具体做法还是有差异。此外每个数据库还有许多二义性、向后兼容性等问题需要解决。例如， SQL Server也支持ANSI方法的NULL比较，但这不是默认的方式（如果改成ANSI方法的NULL比较，基于SQL Server构建的数千个历史应用就会出问题）。在这种情况下，一种解决方案是把查询改成下面这样。 select * from t where ( x = l_some_variable OR (x is null and l_some_variable is NULL )) 不过，这又会带来另一个问题。在SQL Server中，这个查询会使用x上的索引。Oracle中却不会这样，因为B*Tree索引不会对一个完全为NULL的行加索引（索引技术将在第11章介绍）。因此，如果需要查找NULL值，B*Tree索引就没有什么用处。注意只要Oracle B*Tree索引至少有一列定义为NOT NULL，表中的所有行都会出现在索引中，并且X为空的谓词可以使用索引来检索行。这里，为了尽量减少对代码的影响，我们的做法是赋给x某个值，不过这个值并没有实际意义。在此，根据定义可知，x的正常值是正数，所以可以选择1。这样一来，查询就变成： select * from t where nvl(x,-1) = nvl(l_some_variable,-1) 由此创建一个基于函数的索引： create index t_idx on t( nvl(x,-1) ); 这样我们只需做最少的修改，就能在Oracle中得到与SQL Server同样的结果。从这个例子我们可以总结出以下几个要点。 数据库是不同的。在一个数据库上取得的经验也许可以部分应用于另一个数据库，但是必须有心理准备，二者之间可能存在一些重大差别，可能还有一些细微的差别。  细微的差别（如对NULL的处理）与重大差别（如并发控制机制）可能有同样显著的影响。  应当了解数据库，知道它是如何工作的，它的特性如何实现，这是解决这些问题的唯一途径。  常有开发人员问我如何在数据库中做某件特定的事情（通常这样的问题一天不止一个），例如：“如何在一个存储过程中创建临时表？”对于这些问题，我并不直接回答，而是反过来问他们：“你为什么想那么做？”给我的回答常常是：“我们在SQL Server中就是用存储过程创建临时表，所以在Oracle中也要这么做。”这不出我所料，所以我的回答很简单：“在Oracle中你根本没必要这么做，你只是想当然地以为在Oracle中也需要这么做。”实际上，在Oracle中这样做是很不好的。在Oracle中，如果在存储过程中创建表，你会发现存在以下问题： DDL操作会阻碍可扩展性；  DDL操作的速度往往不快；  DDL操作会提交事务；  必须在所有存储过程中使用动态SQL而不是静态SQL来访问这个表；  PL/SQL的动态SQL没有静态SQL速度快，或者说没有静态SQL优化得好。  关键是，即使真的需要在Oracle中使用临时表，也不需要像在SQL Server 中那样在存储过程中创建临时表，而是要Oracle能以其最佳方式工作。反过来也一样，在Oracle中，你会为所有用户创建一个表来存放临时数据；但是从Oracle移植到SQL Server时，可能不希望这样做，这会影响SQL Server的可扩展性和并发性。所有数据库都生而不同，它们存在很大的差异。这并不是说不能在Oracle中使用临时表。你当然可以使用，只是在Oracle和在SQL Server中的用法有所不同。 1. 标准的影响我们经常做这样的假设：如果所有数据库都符合SQL99，那它们肯定一样。在这一节中，我将揭开SQL标准的神秘面纱。 SQL99是数据库的一个ANSI/ISO标准。这个标准的前身是SQL92 ANSI/ISO标准，而SQL92之前还有一个SQL89 ANSI/ISO标准。在SQL99之后还出现了SQL2003、SQL2008以及SQL2011。这个标准定义了一种语言（SQL）以及数据库的行为（事务、隔离级别等）。你知道吗？许多商业数据库都在某种程度上符合SQL99。不过，这对于查询和应用的可移植性没有多大的意义，这一点你也清楚吗？从SQL92开始，标准有4个层次。 入门级。这是大多数开发商符合的级别。这一级只是对前一个标准SQL89稍做修改。所有数据库开发商都没有通过更高级别的认证，实际上， NIST（National Institute of Standards and Technology，美国国家标准与技术研究所，这是一家专门检验SQL合规性的机构）现在也不再做这种认证了。Oracle 7.0于1993年通过了NIST的SQL92入门级合规性验证，那时我也是小组中的一个成员。如果一个数据库符合入门级，它的特性集则是Oracle 7.0的一个功能子集。  过渡级。这一级在特性集方面大致介于入门级和中间级之间。  中间级。这一级增加了许多特性，包括（以下所列并不完整）：  动态SQL 级联DELETE以保证参照完整性 DATE和TIME数据类型域变长字符串 CASE表达式数据类型之间的CAST函数 完备级。增加了以下特性（同样，这个列表也不完整）：  连接管理 BIT字符串数据类型可延迟的完整性约束 FROM子句中的导出表 CHECK约束子句中的子查询临时表入门级标准不包括诸如外联结（outer join）、新的内联结（inner join）语法等特性。过渡级则指定了外联结语法和内联结语法。中间级增加了更多的特性，当然，完备级就是SQL92全部。有关SQL92的大多数书都没有区分这些级别，这就会带来一些混淆。这些书只是说明了一个完整实现SQL92的“理论”数据库会是什么样子，所以无论你拿起哪一本书，都无法将书中所学直接应用到任何SQL92数据库上。关键是，这些数据库在SQL92标准下最多只达到入门级，如果使用了这些数据库的中间级或更高级里的特性，就存在无法“移植”应用的风险。 SQL99只定义了两级一致性：核心（core）一致性和增强（enhanced）一致性。SQL99力图远远超越传统的“SQL”，并引入了一些对象—关系构造（数组、集合等）。它包括SQL MM（多媒体，multi-media）类型、对象—关系类型等。还没有哪个开发商的数据库经认证符合SQL99核心级或增强级。实际上，据我所知，甚至没有哪种商用数据库声称他们的产品完全达到了某级一致性。不要害怕使用商用数据库特有的特性，毕竟，你为这些特性花了钱。每个数据库都有自己的一套“技巧”，在每个数据库中总能找到一种完成操作的好办法。正确的方式是使用最适合当前数据库的做法，移植到其他数据库时再做出相应的调整。你可以采用一些适当的编程技术来隔离这些不同之处，编写操作系统可移植应用的人们（如Oracle核心开发人员）也使用这种技术。 2. 确保具有良好的适应性如果希望你的应用具有良好的适应性，那么就应该充分利用可用的工具，但是也要确保根据不同的特点做出相应的修改。就Oracle数据库来说，它本身相对于操作系统是一个可移植的应用。尽管它可以在多种操作系统上运行，但在Windows上，它就以Windows的方式运行，使用线程和其他Windows特有的机制；而在UNIX/Linux上，Oracle则作为一个多进程服务器运行，使用单个进程来完成Windows上线程完成的工作，这正是UNIX/Linux的方式。两个平台都提供了“核心Oracle”功能，但是在底层却以完全不同的方式来实现。如果数据库应用要在多种数据库上运行，道理也是一样的。例如，许多数据库应用都有一个功能，即为每一行生成一个唯一的键。插入行时，系统应自动生成一个键。为此，Oracle实现了一个名为SEQUENCE的数据库对象，SYS_GUID()是另一个也提供唯一键的函数；Informix有一个SERIAL数据类型；Sybase和SQL Server有一个IDENTITY类型。每个数据库都有一个解决办法。不过，不论从做法上讲，还是从输出来看，各个数据库都有所不同。所以，有经验的开发人员有两条路可走： 开发一个完全独立于数据库的程序来生成唯一的键；  在各个数据库中实现键时，充分利用数据库提供的功能（不同数据库可能不一样）。  注意Oracle数据库从12c开始也提供了IDENTITY类型，它与SQL Server的IDENTITY类型非常相似，但是Oracle是使用SEQUENCE与默认值来实现的。从理论上讲，第一种方法的好处是从一个数据库转向另一个数据库时无需执行任何修改。我把它称为“理论上”的好处，因为这种实现实在太复杂了，所以这种方案根本不可行。要开发一个完全独立于数据库的处理流程，必须创建如下所示的一个表。 EODA@ORA12CR1> create table id_table 2 ( id_name varchar2(30) primary key, 3 id_value number ); Table created. EODA@ORA12CR1> insert into id_table values ( 'MY_KEY', 0 ); 1 row created. EODA@ORA12CR1> commit; Commit complete. 然后，为了得到一个新的键，必须执行以下代码。 EODA@ORA12CR1> update id_table 2 set id_value = id_value+1 3 where id_name = 'MY_KEY'; 1 row updated. EODA@ORA12CR1> select id_value 2 from id_table 3 where id_name = 'MY_KEY'; ID_VALUE ---------- 1 看上去很简单，但是请注意以下结果（注意结果不止一项）。 一次只能有一个用户处理事务。也就是说，你的程序只能以串行的方式来执行这个UPDATE语句，来实现计数器的递增。每次只有一个会话能够生成一个新的键值。  在Oracle中（其他数据库中的行为可能有所不同），倘若隔离级别为SERIALIZABLE，除第一个用户外，试图并发完成此操作的其他用户都会接到这样一个错误：“ORA-08177: can't serialize access for this transaction”（ORA-08177：无法串行访问这个事务）。  例如，使用一个SERIALIZABLE的事务（在J2EE环境中比较常见，其中许多工具都自动将SERIALIZABLE用作默认的隔离模式，但开发人员通常并不知道），你会观察到以下行为。注意SQL提示符包含了活动会话的有关信息。 ops$tkyte session(419,269)> set transaction isolation level serializable; Transaction set. ops$tkyte session(419,269)> update id_table 2 set id_value = id_value+1 3 where id_name = 'MY_KEY'; 1 row updated. ops$tkyte session(419,269)> select id_value 2 from id_table 3 where id_name = 'MY_KEY'; ID_VALUE ---------- 7 下面，再到另一个SQL*Plus会话完成同样的操作，并发地请求一个唯一性的ID。 ops$tkyte session(6,479)> set transaction isolation level serializable; Transaction set. ops$tkyte session(6,479)> update id_table 2 set id_value = id_value+1 3 where id_name = 'MY_KEY'; 此时它会被阻塞，因为一次只有一个事务可以更新这一行。这展示了第一种可能的结果，即这个会话会被阻塞，并等待持有锁的会话提交。但是由于我们使用的是Oracle，而且隔离级别是SERIALIZABLE，提交第一个会话的事务时会观察到以下行为。 ops$tkyte session(419,269)> commit; Commit complete. 第二个会话会立即显示以下错误。 ops$tkyte session(6,479)> update id_table 2 set id_value = id_value+1 3 where id_name = 'MY_KEY'; update id_table * ERROR at line 1: ORA-08177: can't serialize access for this transaction 无论上面的COMMIT命令放在那里（但至少是SET TRANSACTION语句之后），都会收到这个错误信息。虽然第二个会话在更新的时候被阻塞住了，但是当这个锁被第一个会话释放时，第二个会话的语句在执行时会发现数据已经发生了变化，这在串行事务隔离级别下是不允许的，所以会报错。所以，尽管这个逻辑原本想做到独立于数据库，但它根本没有做到这一点。这个逻辑甚至在单个数据库中都无法可靠地完成，因为它要求事务隔离级别必须为SERIALIZABLE！有时我们会阻塞并等待，但有时却会得到一条错误消息。说得简单些，无论是哪种情况（等待很长时间，或者等待很长时间后得到一个错误），都会让最终用户不高兴。实际上，我们的事务比上面所列的要大得多，所以问题也更为复杂。实际的事务中包含多条语句，上例中的UPDATE和SELECT只是其中的两条而已。除了产生键值外，应用还要用这个键向表中插入数据，并完成这个事务所需的其他工作。这种串行化对于应用的扩展是一个很大的制约因素。如果把这个技术用在处理订单的网站上，而且使用这种方式来生成订单号，你可以想想可能带来的后果。真这么做的话，多用户并发性就会成为泡影，因为我们不得不按串行方式来做所有事情。对于这个问题，正确的解决方法是在各种数据库中使用最合适的代码。在Oracle Database 12c中，代码应该如下（假设我们要为T表生成主键）。 EODA@ORA12CR1> create sequence s; Sequence created. EODA@ORA12CR1> create table t 2 ( x number 3 default s.nextval 4 constraint t_pk primary key, 5 other_data varchar2(20) 6 ) 7 / Table created. 另外一种方式是，你可以不使用SEQUENCE，但是需要指定列的IDENTITY属性。 EODA@ORA12CR1> create table t 2 ( x number 3 generated as identity 4 constraint t_pk primary key, 5 other_data varchar2(20) 6 ) 7 / Table created. 注意IDENTITY列必须为数值类型。其实IDENTITY列也是由序列来实现的——数据库自动地给你创建了一个序列。在Oracle Database 11g之前，我们可能会使用下面的代码来生成主键值。 EODA@ORA12CR1> create table t 2 ( pk number primary key, 3 other_data varchar2(20) 4 ) 5 / Table created. EODA@ORA12CR1> create sequence t_seq; Sequence created. EODA@ORA12CR1> create trigger t before insert on t 2 for each row 3 begin 4 :new.pk := t_seq.nextval; 5 end; 6 / Trigger created. 注意在Oracle Database 11g之前的版本中，我们必须使用SELECT T_SEQ.NEXTVAL INTO:NEW.PK FROM DUAL进行赋值。PL/SQL中序列的直接赋值是11g的新特性。其效果是为所插入的每一行自动地（而且透明地）指定一个唯一键。还有一种性能更优的方法，如下所示。 Insert into t ( pk, .... ) values ( t_seq.NEXTVAL, .... ); 也就是说，完全没有触发器的开销（这是我的首选方法）。在其他数据库中使用它们的类型也能达到同样的效果。可能这些数据库的CREATE TABLE语句有所不同，但最终的效果是一样的。这里我们特意使用了各个数据库的特性来生成一个非阻塞、高度并发的唯一键，而且未对应用代码带来任何真正的改动，因为在这个例子中所有逻辑都包含在DDL中。 3. 分层编程理解了每个数据库会以不同的方式实现类似的特性，再来看一个支持可移植性的防御式编程的例子，这就是必要时将数据库访问分层。例如，你在使用JDBC进行编程，如果用的都是直接的SQL（SELECT、INSERT、UPDATE和DELETE），可能不需要抽象层。你完全可以在应用程序中直接编写SQL，但前提是只能用各个数据库都支持的语句，而且经验证，这些语句在不同数据库上会以同样的方式工作。（还记得关于NULL=NULL的讨论吧！）也就是说，这么做不但SQL的性能会很差，而且需要你通晓所有的数据库，知道哪些SQL在所有数据库上的效果都是完全一样的。说实话，对所有数据库的了解能达到这种程度的，我还真没碰到过这样的人。另一种方法的可移植性更好，而且可以提供更好的性能，就是使用存储过程来返回结果集。你会发现，每种数据库都可以从存储过程返回结果集，但是返回的方式不同。针对不同的数据库，我们编写的具体代码可能也需要做出相应的调整。这里有两个选择，一种做法是不使用存储过程返回结果集，另一种做法是针对不同的数据库实现不同的代码。我会采用第二种做法，即针对不同的数据库编写不同的代码，而且大量使用存储过程。初看上去，针对每种数据库采用不同的实现好像会增加开发时间。不过你会发现，在多种数据库上实现时，采用这种方法实际上容易得多。你不用再费心寻找适用于所有数据库的最佳SQL（虽然语法都一样，但在不同数据库上的表现可能会有所区别），而只需实现最适合该数据库的SQL。这些工作可以在应用之外完成，这样对应用调优时就有了更大的灵活性。如果发现一个性能很差的SQL，你可以在数据库自身中立即修改并部署所做的改动，而无需对应用进行任何改动。另外，采用这种方法，还可以充分利用不同数据库提供的SQL扩展功能。Oracle提供了丰富的SQL扩展功能，例如分析函数、建模从句以及行模式匹配等等。在Oracle中，你可以自由地使用这些SQL扩展，因为它在应用“之外”（也就是说，隐藏在数据库中）。在其他数据库中，你可能需要利用其他的途径或者特性来得到相同的结果。既然你已经花钱购买了这些特性，自然可以充分地加以使用。采用这个方法（为每种数据库编写有针对性的代码）还有一个原因，要想找到一个样样精通的开发人员，要求他熟知Oracle、SQL Server和DB2之间的细微差别（这里只讨论这3个数据库）几乎是不可能的，更别说找到这样一个开发小组了。我在过去20年间一直在用Oracle（大体如此，但不排除其他软件）。每一天使用Oracle，都会让我学到一些新的东西。但我还是不敢说同时精通这3种数据库，知道它们之间的差别，并且清楚这些差别会对要构建适用于所有数据库的“通用代码”有什么影响。我觉得自己无法准确或高效地实现这样一个“通用代码”。再说了，我们指的是一般的开发人员，有多少开发人员能真正理解或充分使用他们手上的数据库呢？更别说同时掌握这3种数据库了！要寻找这样一个“全才”，他能开发安全、可扩展而且独立于数据库的程序，就像是大海捞针一样。而希望由这样的人员组建一支开发队伍更是绝无可能。反过来，如果去找一个Oracle专家、一个DB2专家和一个SQL Server专家，告诉他们“我们需要事务完成X、Y和Z”，这倒是很容易。只需告诉他们“这是你的输入，这些是我们需要的输出，这是业务过程要做的事情”，根据这些来生成满足要求的事务性API（存储过程），这样的要求就很简单了。每种数据库针对其特有的功能，我们可以采用最适合于该数据库的方式来实现，这样开发人员也能充分利用底层数据库平台的强大能力（也可能底层数据库缺乏某种功能，而需要另辟蹊径）。这种技术与实现多平台代码所用的开发技术是一样的。例如，Oracle公司在开发数据库时就使用了这种技术。这一层代码量很大（但相对于数据库的全部代码来讲，还只是很少的一部分），被称为操作系统相关（Operating System-Dependent，OSD）代码，是专门针对各个平台实现的。经过这层抽象之后，Oracle就能够充分利用操作系统的特性来提高性能和集成度，而无需重写数据库本身的功能代码。Oracle能作为一个多线程应用在Windows上运行，也能作为一个多进程应用在UNIX/Linux上运行，这就反映出Oracle OSD代码的这种能力。它将进程间通信的机制抽象到这样一个代码层上，然后根据不同的操作系统来做出相应的调整，所以在这个层面上允许有完全不同的实现，它们的表现与专门为各平台编写的应用相差无几。对于不同的数据库来说，SQL语法可能存在差异，实现有所不同，同一个查询在不同数据库中的性能也不一样。不仅如此，不同数据库还存在并发控制、隔离级别以及查询一致性等方面的差异。我们将在第7章详细讨论这些问题，并介绍不同数据库的差异对你有什么影响。SQL92/SQL99试图对事务应如何工作以及隔离级别如何实现给出一个明确的定义，但最终，不同的数据库还是有不同的表现，这都是具体实现所致。在一个数据库中，某个应用可能会发生死锁并完全阻塞，但在另一个数据库中，同样是这个应用，这些问题却有可能不会发生，应用能平稳地运行。在一个数据库中，你可能利用了阻塞机制，但在另一个数据库上部署时，由于不同数据库阻塞机制的差异，你可能就会得到错误的答案。要将一个应用部署在另一种数据库上，需要花费大量的精力，付出艰辛的劳动，即使你百分之百地遵循SQL标准也不例外。 4. 特性和功能不必费力地争取数据库独立性，我还有一个很自然的理由：你应当准确地知道每种数据库能够提供什么特性，并应该加以充分利用。这一节不会列出Oracle Database 12c提供的所有特性，光是这些特性本身就需要一本很厚的书才能讲完。Oracle Database 9i、10g、11g和12c本身的新特性在Oracle文档中已做介绍，Oracle为此提供了大约10 000页的文档，涵盖了每一个有意义的特性和功能。虽然全部阅读有些不切实际，但你起码要对数据库提供的特性和功能有一个大致的了解。这一节只是讨论大致地了解这些特性有什么好处。前面提到过，我常在网上回答有关Oracle的问题。我说过，我的答案中80%都只是给出相关文档的URL（这是指我公布出来的那些问题，其中许多答案都只是指向文档，另外还会有几个问题我没有公布出来，因为这些问题的答案几乎都是“读读这本书”）。有时会有人问我怎么在数据库中编写一些复杂的功能（或者在数据库之外编写），我就会告诉他们在文档的哪个地方可以了解到Oracle如何实现他们需要的功能，并且还说明了应该如何使用这个功能。我时常会遇到一些有关复制的问题，经常有人这样提问：有没有一个视图可以显示实际运行的SQL字面量？我的意思是说，当查看V$SQL时，SQL_TEXT显示为：INSERT INTO TABLE1 (COL1,COL2) VALUES(:1,:2)，我需要看到具体提交的数据，比如说：INSERT INTO TABLE1 (COL1,COL2) VALUES ('FirstVal',12)。我想抓出某个Schema所执行的所有语句（INSERT/UPDATE/DELETE），然后再以相同的顺序在第二个Schema上面执行，比如说： Select SQL_FULLTEXT from V$SQL where FIRST_LOAD_TIME > SYSDATE-(1/24) AND➥ (SQL_TEXT like 'INSERT%'...) order by FIRST_LOAD_TIME 这一系列操作通过一个Web服务发送到schema2，它将处理这些语句。这样可以吗？这个人想要重写Oracle已经提供的复制功能。他无法得到SQL字面量（谢天谢地，幸亏如此）。不过即使可以，这种方法也无法奏效。你不可能简简单单地把一些并发执行的SQL（如果在一个多CPU机器上，两个SQL语句会同时执行，该怎么办）按照串行的方式执行（最后会得到完全不同的答案），重放时SQL语句执行的并发必须要与原系统一致。例如，如果你和我都在执行INSERT INTO A_TABLE SELECT * FROM A_TABLE，而且几乎是同时进行的，最后A_TABLE的行数将是开始时的3倍。比如说，如果开始时A_TABLE有100行，我完成这个插入时，它会有200行。如果你在我之后紧接着执行这个插入（但在我提交之前），你不会看到200行，而只是向A_TABLE再插入100行，这样最终的结果就是表中将有300行。现在，如果执行的顺序稍有变化，比如说Web服务先执行我的插入（A_TABLE从100行变为200行），再完成你的插入（A_TABLE会从200行变为400行），你就应该能看出问题所在了。复制绝不简单，实际上，它相当困难。Oracle（以及其他数据库）在复制方面已经做了二十多年的研究，复制的实现和维护需要付出很多努力。你当然可以编写你自己的复制，这么做可能很有意思，但到最后，你会发现自己编写可能不是最明智的做法。数据库在这方面做了很多工作，一般来说，数据库会比我们自己做得更好。例如，Oracle中复制是用C编写并在数据库内核层面实现的，它不仅执行起来速度快、容易操作，而且也很健壮。它允许跨版本和跨平台，并且提供了强大的技术支持，所以倘若遇到问题，Oracle Support会很乐意提供帮助。如果你使用Oracle的复制，当它升级时，Oracle也会同步地提供支持，而且还会增加一些新的特性。下面考虑一下如果由你自己来开发会怎么样。你必须为每一个版本都提供支持。老版本和新版本之间的互通性谁来负责？这个任务会落在你的头上。如果出了“问题”，你是没有办法寻求Oracle Support的支持。在得到一个足够能展示你的问题的测试用例之前，没有人能帮得了你。当Oracle推出新版本时，你得自己将你的复制代码移植到这个新版本。 5. 要知道有哪些功能如果你没有充分地了解数据库已经提供了哪些功能，从长远看，其负面影响将会一直困扰着你。我曾经与一些有多年数据库应用开发经验的人共事，不过他们原先是在其他数据库上开发应用。这一次他们在Oracle上构建了一个分析软件（趋势分析、报告和可视化软件）来分析临床医学数据（与保健相关）。这些开发人员不知道SQL的一些语法特性，如内联视图、分析函数和标量子查询。他们遇到的一个主要问题是需要分析一个父表及两个子表的数据，相应的实体联系图（Entity-Relationship Diagram，ERD）如图1-1所示。

图1-1 简单的实体联系图

他们想要生成一个报表，这个报表应包含子表中的聚合结果，并将其与父表进行关联。他们原来使用的数据库不支持子查询因子化（WITH子句），也不支持内联视图（所谓内联视图，就是“查询一个查询”，而不是查询一个表）。由于不知道有这些特性，开发人员们在中间层编写了他们自己的一个数据库。他们的做法是先查询父表，对应返回的每一行，再对各个子表分别运行聚合查询。这样做的后果是：最终用户每运行一次这个查询，都会在数据库中执行数千个小查询，才能得到所需的结果。他们的另一种做法是在中间层获取完整的子表的聚合结果，再将其放入内存中的一个散列表，并完成一个散列连接（hash join）。简而言之，他们重新开发了一个数据库，自行完成了与嵌套循环连接或散列连接相当的功能，而没有充分利用临时表空间、复杂的查询优化器等数据库已经提供的功能。这些开发人员把大量时间都花费在这个软件的开发、设计、调优和改进上，而这个软件的功能数据库早已提供，要知道他们原本已经花钱买了这些功能！与此同时，最终用户希望增加一些新特性，但是一直没有如愿，因为开发人员一直在忙于开发这个“引擎”，他们没有更多的时间来考虑这些新特性，实际上这个报告引擎就是一个伪“数据库引擎”。我告诉他们可以直接将这两个子表的聚合结果连接起来来完成这个操作（代码清单1-1～代码清单1-3展示了几种可用的方法）。代码清单1-1　内联视图：对“查询”的查询 select p.id, c1_sum1, c2_sum2 from p, (select id, sum(q1) c1_sum1 from c1 group by id) c1, (select id, sum(q2) c2_sum2 from c2 group by id) c2 where p.id = c1.id and p.id = c2.id / 代码清单1-2　标量子查询：每行运行另一个查询 select p.id, (select sum(q1) from c1 where c1.id = p.id) c1_sum1, (select sum(q2) from c2 where c2.id = p.id) c2_sum2 from p where p.name = '1234' / 代码清单1-3　使用WITH从句实现子查询因子化 with c1_vw as (select id, sum(q1) c1_sum1 from c1 group by id), c2_vw as (select id, sum(q2) c2_sum2 from c2 group by id), c1_c2 as (select c1.id, c1.c1_sum1, c2.c2_sum2 from c1_vw c1, c2_vw c2 where c1.id = c2.id ) select p.id, c1_sum1, c2_sum2 from p, c1_c2 where p.id = c1_c2.id / 除了上面所见之外，使用LAG、LEAD、ROW_NUMBER之类的分析函数、分级函数等也能很好地完成这个任务。我们没有再花时间去优化这个中间层数据库引擎，而是把余下的时间都用来学习SQL Reference Guide，我们把它投影在屏幕上，另外还打开一个SQL*Plus来进行演示。我们的最终目标不是对中间层调优，而是尽快地把中间层去掉。另外一个例子，我曾经见过许多人在Oracle数据库中建立了一些守护进程（daemon process），这些进程会从管道（通过DBMS_PIPE实现的数据库IPC机制）读取消息。这些守护进程执行并提交管道消息中的SQL，他们这样做是为了用这些SQL进行审计以及记录错误日志，这样当数据库中的真正执行业务的事务回滚时，这个管道中的审计及日志记录动作不会被回滚。通常，如果使用触发器之类的工具来审计时，一条语句失败会导致所有工作都回滚（包含审计的SQL）。而通过向另一个进程发送消息，我们就可以有一个独立的事务来提交审计工作。这样，即使父事务回滚，审计记录仍然保留在数据库中。在Oracle Database 8i以前的版本中，这是实现此功能的一个合适的方法（可能也是唯一的方法）。我告诉他们，数据库还有一个称为自治事务的特性，他们听后很是郁闷。自治事务的实现只需一行代码，就完全可以做到他们一直在做的事情。好的一面是，这说明他们可以丢掉原来的大量代码，不用再维护了。另外，系统总的来讲运行得更快，而且更容易理解。不过，他们为浪费了那么多时间在无用功上感到懊恼不已，特别是那个写守护进程的开发人员更是沮丧，因为他写了一大堆无用代码。这样的例子我见了又见，尽管数据库都已经提供了某个特性，还有人在尝试用笨拙的大型复杂方案来实现相同的功能。在这个方面，我自己也有些心虚。我还记得，有一天我的Oracle销售顾问走进我的办公室（那时我还只是一个客户），他看见我被成堆的Oracle文档包围着。我抬起头，问他：“这是真的吗？”接下来的几天我一直在深入研究这些文档。此前我落入一个陷阱，自以为“完全了解数据库”，因为我用过SQL/DS、DB2、Ingress、Sybase、Informix、SQLBase、Oracle，还有其他一些数据库。我没有花时间去了解每个数据库提供了什么，而只是把从其他数据库学到的经验简单地应用到当时正在使用的数据库上（移植到Sybase/SQL Server时对我的触动最大，它与其他数据库的工作方式根本不一样）。等到我真正发现Oracle（以及其他数据库）能做什么之后，我才开始充分利用它，这之后我开发得不仅更快，而且实现相同功能所需的代码更少。我认识到这一点的时候是1993年，在二十年后的今天，数据库已经有了长远的发展，请仔细想想你能用它做些什么。请花点时间来了解数据库都提供了什么样的功能，否则以后你肯定会犯同样的错误。每天我都会学到Oracle的一些新知识，这需要“与时俱进”，时刻跟踪最新动态。我自己就常常阅读文档。 6. 以简单的方式解决问题通常解决问题的途径有两种：容易的方法和困难的方法，我总是看到人们选择后者。这并不一定是故意的，更多的情况下，这么做只是出于无知。他们没想到数据库能“做那个工作”。而我则相反，我总是希望数据库什么都能做，只有当我发现它确实做不了某件事时，才会选择困难的办法（自己来编写）。例如，人们经常问我：“怎么确保最终用户在数据库中只有一个会话？”（其实类似这样的例子还有很多，我只是随便选了一个）。可能许多应用都有这个需求，但是我参与的项目都没有，我不知道有什么必要以这种方式限制用户。不过，如果确实想这样做，人们往往选择困难的方法来实现。例如，他们可能建立一个由操作系统运行的批处理，这个批处理将查看V$SESSION表；如果用户有多个会话，就直接杀掉这些会话。还有一种办法，他们可能会创建一个表，用户登录时由应用在这个表中插入一行，用户注销时删除相应行。这种办法的后果是：在应用“崩溃”时，帮助台的电话会铃声大作，因为这行数据在应用 “崩溃”时不会被清除掉。对于这个需求，我还见过许多“有创意的”方法，不过哪一个也没有下面这种方法简单。 EODA@ORA12CR1> create profile one_session limit sessions_per_user 1; Profile created. EODA@ORA12CR1> alter user scott profile one_session; User altered. EODA@ORA12CR1> alter system set resource_limit=true; System altered. 现在让我们以SCOTT用户连接两次，第二次连接尝试应会报错。 EODA@ORA12CR1> connect scott/tiger Connected. SCOTT@ORA12CR1> host sqlplus scott/tiger SQL*Plus: Release 12.1.0.1.0 Production on Fri Mar 14 11:12:04 2014 Copyright (c) 1982, 2013, Oracle. All rights reserved. ERROR: ORA-02391: exceeded simultaneous SESSIONS_PER_USER limit 仅此而已。现在任何使用ONE_SESSION的用户都将只能有一个数据库连接。每次我提出这个解决方案时，人们总是拍着自己的脑门，不无惊羡地说：“我不知道居然还能这么做！”正所谓磨刀不误砍柴工，花些时间好好熟悉一下所用的工具，了解它能做些什么，在开发时你会节省大量的时间和精力。还是这句“力求简单”，它也同样适用于更宽泛的体系结构层。我总是鼓励人们在采用非常复杂的实现之前先要再三思量。系统中不固定的部分越多，出问题的地方就越多。在一个相当复杂的体系结构中，要想准确地跟踪到错误出在哪里不是一件容易的事。实现一个有“无数”层的应用可能看起来很“酷”，但是如果用一个简单的存储过程就能更好、更快地完成任务，而且只利用更少的资源，那么前者就不是正确的选择。我见过许多项目的应用开发持续数月之久，好像没有尽头。开发人员都在使用最新、最好的技术和语言，但是开发速度还是不快。应用本身的规模并不大，也许这正是问题所在。如果你在建一个狗窝（这是一个很小的木工活），就不会用到重型机器。你只需要几样小工具就行了，大玩艺是用不上的。另一方面，如果你在建一栋公寓楼，就要下大功夫，可能要用到大型机器。与建狗窝相比，解决这个问题所用的工具完全不同。应用开发也是如此。没有一种“万全的体系结构”，没有一种“完美的语言”，也没有一个“无懈可击的方法”。例如，我就使用了APEX（Application Express）来建我的网站。这是一个很小的应用，只有一个（或两个）开发人员参与。它有大约20个界面。这个实现使用PL/SQL和APEX是合适的，这里不需要用Java编写大量的代码，不需要建立EJB，也不必使用Hibernate，等等。这是一个简单的问题，所以应该用简单的方式解决。确实有一些大型应用需要复杂、规模很大（如今我们大多会直接购买这些应用，如人力资源系统、ERP系统等）的解决方案，但是对于为数众多的小型应用，我们应该选用适当的方法和工具来完成任务。不论什么时候，我总是提倡用最简单的体系结构来解决问题，而不要采用复杂的体系结构。这样做可能有显著的回报。每种技术都有自己合适的位置，不要把每个问题都当成钉子，不要高举铁锤随处便砸，我们的工具箱里并非只有铁锤。 7. 开放性我经常看到，人们选择艰难的方式来解决问题还有一个原因。这还是与那种观点有关，我们总认为要不遗余力地追求开放性和数据库独立性。开发人员希望避免使用封闭的专有数据库特性，即使像存储过程或序列这样简单的特性也不敢用，因为使用这些专有特性会把他们锁定到某个数据库系统。在我看来，只要应用涉及到读/写操作，就已经在某种程度上被锁定了。一旦开始运行查询和修改，你就会发现数据库间存在着一些微小的差别（有时还可能存在显著差异）。例如，在一个数据库中，你可能发现SELECT COUNT(*) FROM T查询与一个更新两行的操作发生了死锁。在Oracle中，却发现SELECT COUNT(*)绝对不会被写操作阻塞。你可能见过这样的情况，一种数据库看上去能保证某种业务规则，这是由于该数据库锁机制的副作用造成的，但另一种数据库则不能保证这个业务规则。就算事务的代码完全相同，不同种类的数据库也有可能得到全然不同的答案，这都是因为数据库的实现存在重大的差别。你会发现，要想把一个应用轻轻松松地从一种数据库移植到另一种数据库，这种应用少之又少。不同数据库中对于如何解释SQL（例如，NULL=NULL这个例子）以及如何处理SQL往往有不同的做法。在一个项目中，开发人员在使用Visual Basic、ActiveX控件、IIS服务器和Oracle构建一个基于Web的产品。他们不无担心地告诉我，由于业务逻辑是用PL/SQL编写的，这个产品已经依赖数据库了。他们问我：“怎么修正这个问题？” 先不谈这个问题，退一步说，针对他们所选的技术，我实在看不出依赖于数据库有什么“不好”： 开发人员选择的语言已经把他们与操作系统绑定了（要想独立于操作系统，其实他们更应选择Java）；  他们选择的组件技术已经把他们与操作系统绑定了（选择J2EE更合适）；  他们选择的Web服务器已经将他们与操作系统绑定了。（为什么不用别的更具开放性的产品呢？）  这个项目中所选择的每一项技术都已经把他们绑定到一个非常特定的配置，实际上，唯一能让他们有所选择的技术就是数据库。暂且不管这些（选择这些技术可能有他们自己的原因），这些开发人员还刻意不去用体系结构中一个重要部件的功能，而美其名曰是为了开放性。在我看来，既然精心地选择了技术，就应该最大限度地加以利用。购买这些技术已经花了不少钱，难道你想白花冤枉钱吗？我认为，他们一直想尽力发挥其他技术的潜能，那么为什么要对数据库另眼相看呢？再者，数据库对此项目的成功至关重要，单凭这一点不充分利用数据库也说不过去。如果从开放性的角度来考虑，可以稍稍换个思路。你把所有数据都放在数据库中，数据库是一个很开放的工具，它支持通过大量开放的系统协议和访问机制来访问数据。这听起来好像很不错，简直就是世界上最开放的事物。不过接下来，你把所有应用逻辑还有（更重要的）安全都放在数据库之外，可能放在访问数据的bean中，也可能放在访问数据的JSP中，或者Visual Basic代码中。最终结果就是，数据库被封闭起来，这么一来，数据库已经被弄得“不开放”了。人们无法再采用现有技术使用这些数据，他们必须使用你的访问方法（或者干脆绕过你的安全防护）。尽管现在看上去还不错，但是你要记住，今天响当当的技术也会成为明日黄花，到了明天可能就是一个让人厌倦的技术了。在关系领域中（以及大多数对象实现中），过去30年来只有数据库自己傲然屹立。数据前台技术几乎每年一变，如果应用把安全放在前台实现，而不是在数据库中实现，随着前台技术的变革，这些应用就会成为前进道路上的绊脚石。 Oracle数据库提供了一个称为细粒度访问控制（fine-grained access control，FGAC）的特性。简而言之，这种技术允许开发人员通过一个存储过程，对某些查询在执行时进行动态地修改。这种查询修改可用于限制客户只能接收或修改某些行。这个存储过程在运行查询时能查看是谁以及在何时运行查询，他们从哪个终端运行查询，等等，然后能适当地约束对数据的访问。利用FGAC，可以保证以下安全性： 某类用户在工作时间之外执行的查询将返回0条记录；  如果终端处于安全设施内，可以向其返回所有数据，但是远程终端只能得到不敏感的信息。  实质上讲，FGAC允许把访问控制放在数据库中，与数据“如影随形”。不论用户从bean、JSP、使用ODBC的Visual Basic应用，还是通过SQL*Plus访问数据，数据库都会应用同样的安全策略。这样无论前台在将来采用什么样的新技术，你都能稳坐泰山。现在我再来问你，哪种实现更具“开放性”？你想让所有数据访问都通过调用Visual Basic代码和ActiveX控件来完成（如果愿意，也可以把Visual Basic换成Java，把ActiveX换成EJB。我并不是推崇哪一种技术，这里只是泛指这种实现），还是希望只要能与数据库通信就能对其访问（无论访问协议是SSL、HTTP、Oracle Net还是其他协议，无论使用的是ODBC、JDBC、OCI还是其他API），这两种方式哪种更具“开放性”？我还没见过哪个报告工具能“查询”Visual Basic代码，但是能查询SQL的工具却有不少。人们总是不遗余力地去争取数据库“独立性”和完全的“开放性”，但我认为这是一个错误的决定。不管使用的是什么数据库，我们都应该充分地加以利用，压榨出它的每一个功能的最大价值。调优阶段也就是这样做的（不过，往往在部署之后才会调优）。如果通过充分利用软件的功能，让应用快上5倍，你会惊讶地发现，居然这么快就把数据库独立性需求抛在脑后了。 1.3.5　怎么能让应用运行得更快总是有人问我这个问题：“怎么能让应用运行得更快?”所有人都希望在数据库中有一个开关，按下去之后（或者说调整完一个参数之后），其应用的速度就会变快，认为“数据库调优”就是调优数据库。实际上，根据我的经验，80%以上（甚至经常是100%）的性能问题都出现在设计和实现层面，而不是数据库层面。在这种情况下，不对应用程序作出调整，而去调优数据库，这完全就是南辕北辙。随着时间的推移，我们在数据库层面也有了一些开关来减轻某些不当使用数据库所带来的影响。例如，Oracle 8.1.6增加了一个新参数CURSOR_SHARING=FORCE。如果你愿意，这个特性会实现一个自动绑定器（auto-binder）。如果有一个查询编写为SELECT * FROM EMP WHERE EMPNO = 1234，自动绑定器会悄无声息地把它改写成SELECT * FROM EMP WHERE EMPNO = :x。这确实能大幅动态地减少硬解析数，并减少前面讨论的库闩等待时间。但是（凡事总有个“但是”），它可能有一些副作用。游标共享的一个常见副作用如下所示。 EODA@ORA12CR1> select /* TAG */ substr( username, 1, 1 ) 2 from all_users au1 3 where rownum = 1; S - S EODA@ORA12CR1> alter session set cursor_sharing=force; Session altered. EODA@ORA12CR1> select /* TAG */ substr( username, 1, 1 ) 2 from all_users au2 3 where rownum = 1; SUBSTR(USERNAME,1,1) ------------------------------------------------------------------------------- S 这里到底发生了什么？为什么到第二个查询时SQL*Plus报告的列突然变得这么大？要知道，这还是同一个查询呀！如果查看一下游标共享设置为我们做了些什么，原因就会很清楚了（还会明白其他一些问题）。 EODA@ORA12CR1> select sql_text from v$sql where sql_text like 'select /* TAG */ %'; SQL_TEXT ------------------------------------------------------------------------------- select /* TAG */ substr( username, 1, 1 ) from all_users au1 where rownum = 1 select /* TAG */ substr( username, :"SYS_B_0", :"SYS_B_1" ) from all_users au2 where rownum = :"SYS_B_2" 游标共享会丢失查询中的一些信息。它找到每一个字面量就会把这些字面量从查询中删除，并代之以绑定变量。SQL引擎这时就不知道这个列是长度为1的子串，它的长度对它来说是不确定的。另外，可以看到where rownum = 1现在也已经绑定，看上去似乎不错。不过，优化器把一个重要的信息也一并删除了。它不知道“这个查询将获取一行”；现在只认为“这个查询将返回前N行，而N可能是任何值”，这可能会对你生成的查询计划有着很大的负面影响。另外，与解析、优化大量各不相同的查询相比（如谓词部分使用字面量，请参考前面关于绑定变量的小节），使用CURSOR_SHARING = FORCE会让运行速度更快，但同时我也发现，倘若开发人员确实在查询中使用了绑定变量，查询的速度比使用游标共享还要快。这不是因为数据库游标共享这部分代码的效率不高，而是因为应用程序本身的效率低下。在许多情况下，开发人员如果没有意识在应用中使用绑定变量，那就更不会有意识去高效地解析和重用游标。因为这样开发出来的应用程序认为每个查询都是不同的（并为这些不同的查询分别建立不同的语句），所以它绝对不会多次使用一个游标。事实上，如果程序员从一开始就使用了绑定变量，那么数据库就能只解析一次查询，然后多次重用它。正是这种解析开销降低了总体性能。注意需要重点指出的是，CURSOR_SHARING=FORCE并不会修正/降低系统的“SQL注入”风险。要知道在“SQL注入”攻击的过程中，首先是（恶意）用户更改了SQL，更改的SQL包括两部分，正常的业务SQL与恶意的SQL，CURSOR_SHARING=FORCE会替换你的正常业务SQL的字面量，但是不能够让你免于执行后面的恶意攻击代码。只有使用绑定变量才能使你的数据库免于“SQL注入”攻击，CURSOR_SHARING=FORCE不会降低你的这种风险。一定要记住，只打开CURSOR_SHARING = FORCE并不一定能解决问题，而且游标共享还可能带来新的问题。在有些情况下CURSOR_SHARING是一个非常有用的工具，但它不是银弹。开发得很好的应用从不需要通过设置数据库参数来实现游标共享。从长远来看，我们要尽可能地使用绑定变量，而在需要时才使用常量，这才是正确的做法。注意世上没有银弹，要记住，根本没有。如果有的话，自然就会默认地采用那种做法，这样也就无所谓银弹了。我们确实能在数据库级放几个开关（这种开关真的很少），但是很多问题的根源是应用程序没有做好并发控制（可能是因为查询写得不好，也可能是因为数据的结构性差），这些问题用开关是解决不了的。这些情况往往需要重写代码（有时甚至架构上需要重新作出调整）。移动数据文件、调整参数和其他数据库级开关对应用的总体性能通常影响很小。要想让用户接受你的应用，可能需要让性能提升2倍、3倍、……、n倍才行。你能记起来多少次你的应用只慢了10%？如果只是慢10%，没有人会有太多抱怨。但是如果速度只是期望的1/5，就会让人很不高兴。再说一遍，如果只是移动数据文件，性能不会提升5倍。要想达到这个目的，只能通过调整应用才能办到，可能要让它大幅减少I/O操作。注意可能有人会不认同这种说法，即通过移动数据文件不会让性能提升5倍。确实，近几年Oracle推出了数据库一体机——Exadata，可以简单的将数据文件放到其内部存储上就能让响应时间缩短到1/5、1/10、1/50甚至更短。不过，这更应理解为“我们完全改变了硬件体系结构”，而不是“我们重新组织了部分存储结构”。此外，如果一个应用移植到Exadata上，但是速度只快了5倍或者10倍，这对于我来说还不是很理想，我的期望是至少达到50倍或者更多，如果达不到这个目标我就会重新考虑这个应用是怎么实现的。在整个开发阶段，我们都要把性能作为一个目标精心地设计，合理地构建，并且不断地测试，绝对不能把它当做马后炮，事后才想起来。我真是很奇怪，为什么那么多人根本不对应用调优，就草率地把应用交付到客户手里，匆匆上马，并运行起来。我见过一些应用除了主键索引外，居然没有其他的任何索引。他们从来没有对查询进行过调优，也没有执行过压力测试。应用的用户数很少，从未让更多的用户试用过。这些应用总是把调优当成产品安装的一部分，对我来说，这种做法绝对不可接受。最终用户应该从一开始就拿到一个响应迅速、充分优化的系统。应用上线的第一天肯定还有许多“产品问题”需要处理，但不能让用户从一开始就领教糟糕的性能。对用户来说，一个新应用里有几个bug尚能容忍，不可忍的是这些bug需要他们在屏幕前痛苦地等待很久之后才会出现。 1.3.6　DBA与开发人员的关系有一点很肯定，要建立最成功的信息系统，前提是DBA与应用开发人员之间要有一种“共生关系”。在这一节里，我想从开发人员的角度谈谈开发人员与DBA之间的分工（假设所有正式开发都有DBA小组的参与）。作为一名开发人员，你不必知道如何安装和配置软件。这应该是DBA或者系统管理员（system administrator，SA）的任务。安装Oracle Net、配置监听器、配置共享服务器、建立连接池、安装数据库、创建数据库等，这些事情我都会交给DBA/SA来做。一般来讲，开发人员不必知道如何对操作系统调优。我个人通常会让系统的SA负责这个任务。作为数据库应用的软件开发人员，你应该能熟练地使用所选择的操作系统，但是不要求能对它调优。 DBA最重大的职责是数据库恢复。注意，我说的可不是“备份”，而是“恢复”。而且，我认为这也是DBA唯一重要的职责。DBA要知道回滚（rollback）和重做（redo）怎么工作，不错，这也是开发人员要了解的。DBA还要知道如何完成表空间时间点恢复，这一点开发人员不必介入。如果你能有所了解，也许以后会用得上，但是开发人员目前不必亲力而为。在数据库实例级调优，并得出最优的PGA_AGGREGATE_TARGET是什么，这一般是DBA的任务（数据库往往能帮助他们得出正确的答案）。也有一些例外情况，有时开发人员可能需要修改会话的某个设置，但是如果在数据库级修改设置，就要由DBA来负责。一般来说，数据库并不是只支持一位开发人员的应用，而是运行着多个应用，因此只有支持所有应用的DBA才能做出最终（正确）的决定。分配空间和管理文件也是DBA的工作。开发人员可以对分配的空间做出估计（他们觉得需要多少空间），但是余下的都要由DBA/SA决定。基本上，开发人员不必知道如何维护数据库，他们只需要知道如何在数据库中运行他们的应用就可以了。开发人员和DBA要协同解决问题，但各有分工。假设你是一位开发人员，如果你的查询用的资源太多，DBA就会来找你；如果你不知道怎么让系统跑得更快，可以去找DBA（如果应用已经得到充分调优，此时就可以完成实例级调优）。这些任务因环境而异，不过我还是认为存在着分工。好的开发人员往往是很糟糕的DBA，反之亦然。在我看来，他们的能力不同、思路不同，而且个性也不同。