中国联通北六省融合项目应用性能与安全开发建议v(编辑修改稿)

中国联通北六省融合项目应用性能与安全开发建议v(编辑修改稿)内容摘要：

建议 14 数据不能被恢复 .因此很少的资源被调用 ,执行时间也会很短 . . 降低数据库竞争优化 （ 建议） 尽量多使用 COMMIT 只要有可能 ,在程序中尽量多使用 COMMIT, 这样程序的性能得到提高 ,需求也会因为 COMMIT 所释放的资源而减少 . COMMIT 所释放的资源 :  回滚段上用于恢复数据的信息。  被程序语句获得的锁  redo log buffer 中的空间  ORACLE 为管理上述 3 种资源中的内部花费 . 用 Where子句替换 HAVING子句 （建议） 避免使用 HAVING 子句 , HAVING 只会在检索出所有记录之后才对结果集进行过滤 . 这个处理需要排序 ,总计等操作 . 如果能通过 WHERE子句限制记录的数目 ,那就能减少这方面的开销 . 例如 :  低效 : SELECT REGION， AVG(LOG_SIZE) FROM LOCATION GROUP BY REGION HAVING REGION != ‘SYDNEY39。 AND REGION != ‘PERTH39。  高效 SELECT REGION， AVG(LOG_SIZE) FROM LOCATION WHERE REGION != ‘SYDNEY39。 AND REGION != ‘PERTH39。 GROUP BY REGION 北六融合项目应用性能与安全开发建议 15 . 减少对表的查询 （建议） 在含有子查询的 SQL 语句中 ,要特别注意减少对表的查询 例如 :  低效 SELECT TAB_NAME FROM TABLES WHERE TAB_NAME = ( SELECT TAB_NAME FROM TAB_COLUMNS WHERE VERSION = 604) AND DB_VER= ( SELECT DB_VER FROM TAB_COLUMNS WHERE VERSION = 604)  高效 SELECT TAB_NAME FROM TABLES WHERE (TAB_NAME,DB_VER) = ( SELECT TAB_NAME,DB_VER) FROM TAB_COLUMNS WHERE VERSION = 604) Update 多个 Column 例子 :  低效 : UPDATE EMP SET EMP_CAT = (SELECT MAX(CATEGORY) FROM EMP_CATEGORIES), SAL_RANGE = (SELECT MAX(SAL_RANGE) FROM EMP_CATEGORIES) WHERE EMP_DEPT = 0020。  高效 : UPDATE EMP 北六融合项目应用性能与安全开发建议 16 SET (EMP_CAT, SAL_RANGE) = (SELECT MAX(CATEGORY) , MAX(SAL_RANGE) FROM EMP_CATEGORIES) WHERE EMP_DEPT = 0020。 . 通过内部函数提高 SQL效率 (建议 ) SELECT ,COUNT(*) FROM HISTORY_TYPE T,EMP E,EMP_HISTORY H WHERE = AND = GROUP BY ,。 通过调用下面的函数可以提高效率 . FUNCTION LOOKUP_HIST_TYPE(TYP IN NUMBER) RETURN VARCHAR2 AS TDESC VARCHAR2(30)。 CURSOR C1 IS SELECT TYPE_DESC FROM HISTORY_TYPE WHERE HIST_TYPE = TYP。 BEGIN OPEN C1。 FETCH C1 INTO TDESC。 CLOSE C1。 RETURN (NVL(TDESC,39。 ?39。 ))。 END。 北六融合项目应用性能与安全开发建议 17 FUNCTION LOOKUP_EMP(EMP IN NUMBER) RETURN VARCHAR2 AS ENAME VARCHAR2 (30)。 CURSOR C1 IS SELECT ENAME FROM EMP WHERE EMPNO=EMP。 BEGIN OPEN C1。 FETCH C1 INTO ENAME。 CLOSE C1。 RETURN (NVL(ENAME,39。 ?39。 ))。 END。 SELECT ,LOOKUP_EMP(), ,LOOKUP_HIST_TYPE(),COUNT(*) FROM EMP_HISTORY H GROUP BY ,。 . 使用表的别名 (Alias)(严格执行 ) 当在 SQL 语句中连接多个表时 , 请使用表的别名并把别名前缀于每个 Column上 .这样一来 ,就可以减少解析的时间并减少那些由 Column 歧义引起的语法错误 . (译者注 : Column歧义指的是由于 SQL中不同的表具有相同的 Column名 ,当 SQL语句中出现这个 Column 时 ,SQL 解析器无法判断这个 Column 的归属 ) 北六融合项目应用性能与安全开发建议 18 . 用 EXISTS替代 IN(强烈建议 ) 在许多基于基础表的查询中 ,为了满足一个条件 ,往往需要对另一个表进行联接 .在这种情况下 , 使用 EXISTS(或 NOT EXISTS)通常将提高查询的效率 .  低效 : SELECT * FROM EMP (基础表 ) WHERE EMPNO 0 AND DEPTNO IN (SELECT DEPTNO FROM DEPT WHERE LOC = ‘MELB39。 )  高效 : SELECT * FROM EMP (基础表 ) WHERE EMPNO 0 AND EXISTS (SELECT ‘X39。 FROM DEPT WHERE = AND LOC = ‘MELB39。 ) (译者按 : 相对来说 ,用 NOT EXISTS 替换 NOT IN 将更显著地提高效率 ,下一节中将指出 ) . 用 NOT EXISTS 替代 NOT IN(严格要求 ) 在子查询中 ,NOT IN 子句将执行一个内部的排序和合并 . 无论在哪种情况下 ,NOT IN都是最低效的 (因为它对子查询中的表执行了一个全表遍历 ). 为了避免使用 NOT IN ,我们可以把它改写成外连接 (Outer Joins)或 NOT EXISTS. 北六融合项目应用性能与安全开发建议 19 例如 : SELECT „ FROM EMP WHERE DEPT_NO NOT IN (SELECT DEPT_NO FROM DEPT WHERE DEPT_CAT=39。 A39。 )。 为了提高效率 .改写为 :  (方法一 : 高效 ) SELECT „. FROM EMP A,DEPT B WHERE = (+) AND IS NULL AND (+) = ‘A39。  (方法二 : 最高效 ) SELECT „. FROM EMP E WHERE NOT EXISTS (SELECT ‘X39。 FROM DEPT D WHERE = AND DEPT_CAT = ‘A39。 )。 . 用表连接替换 EXISTS（建议） 通常来说 , 采用表连接的方式比 EXISTS 更有效率 SELECT ENAME FROM EMP E WHERE EXISTS (SELECT ‘X39。 FROM DEPT 北六融合项目应用性能与安全开发建议 20 WHERE DEPT_NO = AND DEPT_CAT = ‘A39。 )。 (更高效 ) SELECT ENAME FROM DEPT D,EMP E WHERE = AND DEPT_CAT = ‘A39。 (译者按 : 在 RBO 的情况下 ,前者的执行路径包括 FILTER,后者使用 NESTED LOOP) . 用 EXISTS替换 DISTINCT(建议 ) 当提交一个包含一对多表信息 (比如部门表和雇员表 )的查询时 ,避免在 SELECT子句中使用 DISTINCT. 一般可以考虑用 EXIST 替换 例如 :  低效 : SELECT DISTINCT DEPT_NO,DEPT_NAME FROM DEPT D,EMP E WHERE =  高效 : SELECT DEPT_NO,DEPT_NAME FROM DEPT D WHERE EXISTS ( SELECT ‘X39。 FROM EMP E WHERE = )。 EXISTS 使查询更为迅速 ,因为 RDBMS 核心模块将在子查询的条件一旦满足后 ,北六融合项目应用性能与安全开发建议 21 立刻返回结果 . . 识别 39。 低效执行 39。 的 SQL语句 (自查方法 ) 用下列 SQL 工具找出低效 SQL: SELECT EXECUTIONS , DISK_READS, BUFFER_GETS, ROUND((BUFFER_GETSDISK_READS)/BUFFER_GETS,2) Hit_radio, ROUND(DISK_READS/EXECUTIONS,2) Reads_per_run, SQL_TEXT FROM V$SQLAREA WHERE EXECUTIONS0 AND BUFFER_GETS 0 AND (BUFFER_GETSDISK_READS)/BUFFER_GETS ORDER BY 4 DESC。 (译者按 : 虽然目前各种关于 SQL优化的图形化工具层出不穷 ,但是写出自己的SQL 工具来解决问题始终是一个最好的方法 ) . 使用 TKPROF 工具来查询 SQL性能状态 （自查方法） SQL trace 工具收集正在执行的 SQL 的性能状态数据并记录到一个跟踪文件中 . 这个跟踪文件提供了许多有用的信息 ,例如解析次数 .执行次数 ,CPU 使用时间等 .这些数据将可以用来优化你的系统 . 设置 SQL TRACE 在会话级别 : 有效 ALTER SESSION SET SQL_TRACE TRUE 设置 SQL TRACE 在 整个数据库有效仿 , 你必须将 SQL_TRACE 参数在 中设为 TRUE, USER_DUMP_DEST 参数说明了生成跟踪文件的目录 (译者按 : 这一节中 ,作者并没有提到 TKPROF 的用法 , 对 SQL TRACE 的用法也北六融合项目应用性能与安全开发建议 22 不够准确 , 设置 SQL TRACE 首先要在 中设定 TIMED_STATISTICS, 这样才能得到那些重要的时间状态 . 生成的 trace 文件是不可读的 ,所以要用 TKPROF 工具对其进行转换 ,TKPROF有许多执行参数 . 大家可以参考 ORACLE手册来了解具体的配置 . ) . 用 EXPLAIN PLAN 分析 SQL语句 （自查方法） EXPLAIN PLAN 是一个很好的分析 SQL 语句的工具 ,它甚至可以在不执行 SQL 的情况下分析语句 . 通过分析 ,我们就可以知道 ORACLE 是怎么样连接表 ,使用什么方式扫描表 (索引扫描或全表扫描 )以及使用到的索引名称 . 你需要按照从里到外 ,从上到下的次序解读分析的结果 . EXPLAIN PLAN 分析的结果是用缩进的格式排列的 , 最内部的操作将被最先解读 , 如果两个操作处于同一层中 ,带有最小操作号的将被首先执行 . NESTED LOOP 是 少数不按照上述规则处理的操作 , 正确的执行路径是检查对NESTED LOOP 提供数据的操作 ,其中操作号最小的将被最先处理 . 译者按 : 通过实践 , 感到还是用 SQLPLUS 中的 SET TRACE 功能比较方便 . 举例 : SQL list 1 SELECT * 2 FROM dept, emp 3* WHERE = SQL set autotrace traceonly /*traceonly 可以不显示执行结果 */ SQL / 14 rows selected. Execution Plan 北六融合项目应用性能与安全开发建议 23 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 NEST。