成功开发Oracle应用的关键要点

### 成功开发Oracle应用的关键要点 #### 1. 应用调优的重点 经验表明，80% - 90%（甚至更多）的调优工作应在应用层面进行，通常是读写数据库的接口代码部分，而非数据库层面。在实际项目中，开发者可能不清楚代码在数据库中的具体操作，也不知道从何处查找潜在问题。此外，还需解决一些难题，如： - 如何在不更改SQL的情况下对其进行调优。一般来说，这很难做到，但从Oracle 10g及以上版本开始，在一定程度上可通过SQL配置文件（此选项需要Oracle调优包许可证）实现；11g及以上版本可使用扩展统计信息；12c及以上版本可采用自适应查询优化。不过，低效的SQL始终是低效的。 - 如何衡量性能。 - 如何找出瓶颈所在。 - 如何进行索引以及索引哪些内容等。 在项目中，起初与数据库隔离的开发者在了解数据库能为他们提供的功能以及获取信息的便捷性后，感到十分惊讶。更重要的是，他们看到了利用数据库特性对应用性能产生的巨大影响。最终，项目成功完成，只是比计划延迟了几周。 #### 2. 理解Oracle架构的重要性 在将应用从其他数据库（如SQL Server）迁移到Oracle时，很多人秉持“尽量少修改代码，使SQL Server代码能在Oracle上编译和执行”的观点。这种做法导致的应用往往性能不佳。实际上，将Oracle应用迁移到SQL Server时，若同样尽量少做修改，也会出现类似的性能问题。 在一个具体案例中，SQL Server的架构和使用方式对Oracle的实现产生了重大影响。项目目标是实现扩展，但开发者不想进行大规模迁移，而是尽可能保持客户端和数据库层的架构不变。这一决策带来了两个重要后果： - Oracle的连接架构与SQL Server相同。 - 开发者使用了字面量（非绑定）SQL。 这两个后果导致系统无法支持所需的用户负载（数据库服务器内存耗尽），且性能极差。 #### 3. 在Oracle中使用单连接 在SQL Server中，为每个并发语句打开一个数据库连接是常见做法。例如，执行五个查询可能会打开五个连接。然而，在Oracle中，无论执行五个查询还是五百个查询，最多只需打开一个连接。在Oracle中，不鼓励甚至应避免使用多个数据库连接。 但在实际项目中，一个简单的基于Web的应用每页会打开5、10、15个或更多连接，这使得服务器只能支持原本应支持并发用户数量的1/5、1/10或1/15。此外，他们在普通Windows服务器上运行数据库，由于Windows单进程架构的限制，Oracle数据库服务器总共只能使用约1.75GB的RAM。尽管服务器有8GB的RAM，但实际只能使用约2GB。 虽然在32位Windows环境中可以通过/AWE开关等方式使用更多RAM，但该项目使用的操作系统版本不支持这些方法。解决此问题有三种方法，但都需要大量工作，且是在“迁移”完成后进行的： - 重新架构应用，使其能利用运行在Oracle上的优势，使用单个连接生成页面，而非5 - 15个连接。这是唯一能真正解决问题的方法。 - 升级操作系统（这并非易事），并利用Windows数据中心版本的更大内存模型（这也涉及复杂的数据库设置，如间接数据缓冲区和其他非标准设置）。 - 将数据库从基于Windows的操作系统迁移到使用多进程的其他操作系统，从而使数据库能够利用所有安装的RAM。在32位Windows平台上，PGA/SGA区域总共只能使用约2GB的RAM；而使用32位的多进程平台，SGA约为2GB，每个进程的PGA约为2GB，能更充分地利用资源。 #### 4. 使用绑定变量 不使用绑定变量是导致性能问题和可扩展性受限的主要原因，同时还存在巨大的安全风险。Oracle共享池（一个非常重要的共享内存数据结构）的运行机制在很大程度上依赖于开发者使用绑定变量。若拒绝使用绑定变量，会使事务性Oracle实现运行缓慢，甚至完全停滞。 绑定变量是查询中的占位符。例如，要查询员工编号为123的记录，可使用以下两种方式： ```sql -- 不使用绑定变量 select * from emp where empno = 123; -- 使用绑定变量 select * from emp where empno = :empno; ``` 在典型系统中，一段时间内可能只会查询员工123一两次，之后会查询员工456、789等。若在插入语句中不使用绑定变量，主键值会被硬编码，这些插入语句后续无法复用。使用字面量（常量）的查询对于数据库来说是全新的，每次执行都需要进行解析、验证（解析名称）、安全检查、优化等操作，即每次执行唯一的语句都需要重新编译。 而使用绑定变量的查询只编译一次，查询计划会存储在共享池（库缓存）中，可被重复使用。这两种方式在性能和可扩展性上的差异非常显著。 为了直观展示使用绑定变量和不使用绑定变量的差异，我们进行一个小测试。首先创建一个简单的表： ```sql EODA@ORA12CR1> create table t ( x int ); Table created. ``` 然后创建两个简单的存储过程，它们都用于向表中插入1到10000的数字，但第一个过程使用带有绑定变量的单个SQL语句，第二个过程为每行插入构造一个唯一的SQL语句： ```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. -- 不使用绑定变量的过程 EODA@ORA12CR1> create or replace procedure proc2 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. ``` 使用`runstats`工具来详细比较这两种方法： ```sql 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 ``` 结果表明，不使用绑定变量插入10000行数据所需的CPU时间和资源明显更多，实际上，不使用绑定变量插入数据所需的CPU时间是使用绑定变量的十倍以上。而且，从其他信息中可以看出，两种方法在资源利用上存在显著差异，如下表所示： | 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 | 可以看到，使用绑定变量时硬解析两次，而不使用绑定变量时硬解析10000次（每次插入都进行一次硬解析）。不使用绑定变量时，“闩锁”的使用量是使用绑定变量时的一个数量级。闩锁是一种用于序列化访问Oracle共享数据结构的锁，硬解析时使用的闩锁是最常用的闩锁之一，如共享池和库缓存的闩锁。随着同时进行硬解析的用户数量增加，性能会逐渐变差，因为更多人会排队等待获取共享池的闩锁。 执行不使用绑定变量的SQL语句就像在每次调用方法前都编译一个子程序。例如，将Java源代码发送给客户，每次调用类中的方法前都要调用Java编译器编译类、运行方法，然后丢弃字节码，这显然是不可取的，在数据库中同样如此。 #### 5. 绑定变量与SQL注入 对于使用字符串拼接的开发者来说，不使用绑定变量还会带来安全问题，即SQL注入。SQL注入是指开发者接受终端用户的输入并将其拼接到查询中，然后编译和执行该查询。这使得终端用户有可能修改SQL语句，执行开发者原本未预期的操作，后果可能非常严重。 若不使用绑定变量，代码就容易受到SQL注入攻击，必须进行仔细审查。审查应由对代码作者持客观批判态度的人进行，因为同行、朋友或下属可能无法进行足够严格的审查。不使用绑定变量的代码应受到怀疑，不使用绑定变量应是特殊情况，而非常态。 以下是一个简单的存储过程示例，展示了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 / ``` 大多数开发者可能认为这个代码不会受到SQL注入攻击，因为输入是Oracle DATE变量。但实际上，了解方法的人可以轻松对其进行修改。 假设项目中有一个“恶意开发者”，他可以访问`INJ`过程，但没有访问该过程所在模式中其他表的权限。他不知道存在`user_pw`表： ```sql 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. ``` 恶意开发者可以通过以下操作获取表名： ```sql 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 ) ``` 执行上述代码后，恶意开发者可以看到`user_pw`表。接着，他可以通过修改`NLS_DATE_FORMAT`获取表的列名： ```sql DEVACCT@ORA12CR1> alter session set 2 nls_date_format = '"''union select tname||''/''||cname from col--"'; Session altered. DEVACCT@ORA12CR1> exec eoda.inj( sysdate ) ``` 最后，他可以再次修改`NLS_DATE_FORMAT`获取敏感的用户名和密码信息： ```sql DEVACCT@ORA12CR1> alter session set 2 nls_date_format = '"''union select uname||''/''||pw from user_pw--"'; Session altered. DEVACCT@ORA12CR1> exec eoda.inj( sysdate ) ``` 如果这个开发者拥有`CREATE PROCEDURE`权限，他还可以进一步利用这个漏洞。例如，创建一个授予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 / ``` 然后通过修改`NLS_DATE_FORMAT`并授予执行权限，让`eoda`用户执行该函数，从而获取DBA权限： ```sql 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. DEVACCT@ORA12CR1> exec eoda.inj( sysdate ) ``` 为了防止SQL注入，应使用绑定变量。例如，将上述`inj`过程修改为： ```sql 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 ``` 综上所述，在开发Oracle应用时，应重视应用层面的调优，理解Oracle架构，使用单连接和绑定变量，以提高应用的性能和安全性。 下面是一个简单的流程图，展示了使用绑定变量和不使用绑定变量在查询执行过程中的差异： ```mermaid graph LR classDef startend fill:#F5EBFF,stroke:#BE8FED,stroke-width:2px; classDef process fill:#E5F6FF,stroke:#73A6FF,stroke-width:2px; classDef decision fill:#FFF6CC,stroke:#FFBC52,stroke-width:2px; A([开始查询]):::startend --> B{是否使用绑定变量}:::decision B -->|是| C(编译一次查询计划):::process C --> D(存储查询计划到共享池):::process D --> E(从共享池获取并复用查询计划):::process B -->|否| F(每次执行都进行硬解析):::process F --> G(解析、验证、安全检查、优化):::process G --> H(执行查询):::process E --> H H --> I([结束查询]):::startend ``` 通过这个流程图可以更直观地看到使用绑定变量和不使用绑定变量在查询执行流程上的差异，以及使用绑定变量带来的优势。 ### 成功开发Oracle应用的关键要点（续） #### 6. 应用调优与数据库性能的综合考量 在开发Oracle应用时，应用调优和数据库性能是紧密相连的。调优工作大部分应在应用层面进行，这是因为应用层的代码直接与数据库交互，其效率会对整个系统性能产生重大影响。当开发者对数据库操作缺乏了解时，就容易出现各种问题，如性能瓶颈难以定位、SQL调优困难等。 在实际项目中，开发者往往只关注应用功能的实现，而忽略了数据库的特性和性能优化。例如，在将应用从其他数据库迁移到Oracle时，很多人只是简单地进行代码移植，没有充分考虑Oracle的架构和特性，导致应用性能不佳。因此，开发者需要深入了解数据库的工作原理，充分利用数据库的功能来优化应用性能。 #### 7. 连接架构对应用性能的影响 连接架构是影响Oracle应用性能的重要因素之一。在SQL Server中常见的多连接模式，在Oracle中并不适用。Oracle建议使用单连接，因为多连接会消耗大量的系统资源，导致数据库服务器内存不足，从而影响系统的可扩展性和性能。 以下是一个简单的对比表格，展示了多连接和单连接在资源使用和性能方面的差异： | 连接模式 | 资源使用 | 性能表现 | 可扩展性 | | --- | --- | --- | --- | | 多连接 | 大量内存和系统资源 | 响应时间长，易出现性能瓶颈 | 低，难以支持大量并发用户 | | 单连接 | 较少资源 | 响应时间短，性能稳定 | 高，可支持更多并发用户 | 在实际项目中，如果采用了不适合的连接架构，就需要采取相应的解决措施。解决多连接问题的方法有以下几种： 1. **重新架构应用**：修改应用代码，使其利用Oracle的单连接优势。具体步骤如下： - 分析应用中所有与数据库连接相关的代码。 - 将多个连接合并为一个连接，确保在一个页面或事务中只使用一个数据库连接。 - 对修改后的代码进行测试，确保功能正常且性能得到提升。 2. **升级操作系统**：使用Windows数据中心版本，以利用其更大的内存模型。操作步骤如下： - 备份数据库和应用数据。 - 安装Windows数据中心版本操作系统。 - 重新配置数据库，包括设置间接数据缓冲区和其他非标准设置。 - 恢复数据库和应用数据，并进行测试。 3. **迁移数据库**：将数据库从Windows平台迁移到多进程的操作系统，如Linux。迁移步骤如下： - 选择合适的目标操作系统和数据库版本。 - 备份源数据库。 - 在目标操作系统上安装数据库软件。 - 使用数据库迁移工具将源数据库迁移到目标数据库。 - 对迁移后的数据库进行配置和优化。 - 测试应用在新环境下的功能和性能。 #### 8. 绑定变量在性能和安全方面的重要性 绑定变量不仅能提高Oracle应用的性能，还能增强系统的安全性。从性能角度来看，使用绑定变量可以减少硬解析的次数，降低系统资源的消耗，提高查询的执行效率。从安全角度来看，绑定变量可以有效防止SQL注入攻击，保护数据库的安全。 以下是一个使用绑定变量和不使用绑定变量的性能对比测试流程： 1. **创建测试表**： ```sql EODA@ORA12CR1> create table t ( x int ); Table created. ``` 2. **创建存储过程**： - 使用绑定变量的存储过程： ```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 proc2 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. ``` 3. **使用`runstats`工具进行测试**： ```sql 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) ``` 测试结果表明，使用绑定变量的`proc1`执行时间明显短于不使用绑定变量的`proc2`，且资源消耗也更少。 在安全方面，不使用绑定变量容易导致SQL注入攻击。以下是一个SQL注入攻击的示例流程： 1. **创建存储过程`inj`**： ```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 / ``` 2. **恶意开发者利用`NLS_DATE_FORMAT`进行攻击**： ```sql 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 ) ``` 通过以上操作，恶意开发者可以获取数据库中的表名，进而获取敏感信息。为了防止SQL注入攻击，应使用绑定变量，如将`inj`过程修改为： ```sql 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 ``` #### 9. 总结与建议 为了成功开发Oracle应用，开发者需要注意以下几点： - **深入了解数据库**：开发者应深入了解Oracle的架构、特性和工作原理，充分利用数据库的功能来优化应用性能。 - **优化应用代码**：在应用层面进行调优，避免在数据库层面进行过多的优化。例如，使用单连接、绑定变量等技术来提高应用的性能和可扩展性。 - **进行性能测试**：在应用上线前，进行充分的性能测试，及时发现和解决性能问题。可以使用工具如`runstats`来进行性能测试和分析。 - **加强安全意识**：使用绑定变量来防止SQL注入攻击，确保数据库的安全。同时，对代码进行严格的安全审查，避免安全漏洞的出现。 以下是一个开发Oracle应用的简单流程图，展示了开发过程中的关键步骤： ```mermaid graph LR classDef startend fill:#F5EBFF,stroke:#BE8FED,stroke-width:2px; classDef process fill:#E5F6FF,stroke:#73A6FF,stroke-width:2px; classDef decision fill:#FFF6CC,stroke:#FFBC52,stroke-width:2px; A([需求分析]):::startend --> B(设计应用架构):::process B --> C{选择数据库连接方式}:::decision C -->|单连接| D(编写应用代码):::process C -->|多连接| E(重新设计连接架构):::process E --> D D --> F(使用绑定变量优化SQL):::process F --> G(进行性能测试):::process G --> H{性能是否达标}:::decision H -->|是| I(进行安全审查):::process H -->|否| J(优化应用和数据库):::process J --> G I --> K(部署应用):::process K --> L([上线运行]):::startend ``` 通过遵循以上建议和流程，开发者可以开发出性能优良、安全可靠的Oracle应用。