用EXISTS替换DISTINCT
当提交一个包含一对多表信息(比如部门表和雇员表)的查询时避免在SELECT子句中使用DISTINCT 一般可以考虑用EXIST替换
例如
低效
SELECTDISTINCTDEPT_NODEPT_NAME
FROMDEPTDEMPE
WHEREDDEPT_NO=EDEPT_NO
高效
SELECTDEPT_NODEPT_NAME
FROMDEPTD
WHEREEXISTS(SELECTX
FROMEMPE
WHEREEDEPT_NO=DDEPT_NO);
EXISTS 使查询更为迅速因为RDBMS核心模块将在子查询的条件一旦满足后立刻返回结果
识别低效执行的SQL语句
用下列SQL工具找出低效SQL
SELECTEXECUTIONSDISK_READSBUFFER_GETS
ROUND((BUFFER_GETSDISK_READS)/BUFFER_GETS)Hit_radio
ROUND(DISK_READS/EXECUTIONS)Reads_per_run
SQL_TEXT
FROMV$SQLAREA
WHEREEXECUTIONS>
ANDBUFFER_GETS>
AND(BUFFER_GETSDISK_READS)/BUFFER_GETS<
ORDERBYDESC;
(译者按 虽然目前各种关于SQL优化的图形化工具层出不穷但是写出自己的SQL工具来解决问题始终是一个最好的方法)
使用TKPROF 工具来查询SQL性能状态
SQL trace 工具收集正在执行的SQL的性能状态数据并记录到一个跟蹤文件中 这个跟蹤文件提供了许多有用的信息例如解析次数执行次数CPU使用时间等这些数据将可以用来优化你的系统
设置SQL TRACE在会话级别
有效
ALTER SESSION SET SQL_TRACE TRUE
设置SQL TRACE 在整个数据库有效仿 你必须将SQL_TRACE参数在initora中设为TRUE USER_DUMP_DEST参数说明了生成跟蹤文件的目录
(译者按 这一节中作者并没有提到TKPROF的用法 对SQL TRACE的用法也不够准确 设置SQL TRACE首先要在initora中设定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
SELECT*
FROMdeptemp
*WHEREempdeptno=deptdeptno
SQL>setautotracetraceonly/*traceonly可以不显示执行结果*/
SQL>/
rowsselected
ExecutionPlan
SELECTSTATEMENTptimizer=CHOOSE
NESTEDLOOPS
TABLEACCESS(FULL)OFEMP
TABLEACCESS(BYINDEXROWID)OFDEPT
INDEX(UNIQUESCAN)OFPK_DEPT(UNIQUE)
Statistics
recursivecalls
dbblockgets
consistentgets
physicalreads
redosize
bytessentviaSQL*Nettoclient
bytesreceivedviaSQL*Netfromclient
SQL*Netroundtripsto/fromclient
sorts(memory)
sorts(disk)
rowsprocessed
通过以上分析可以得出实际的执行步骤是
TABLEACCESS(FULL)OFEMP
INDEX(UNIQUESCAN)OFPK_DEPT(UNIQUE)
TABLEACCESS(BYINDEXROWID)OFDEPT
NESTEDLOOPS(JOININGAND)
注 目前许多第三方的工具如TOAD和ORACLE本身提供的工具如OMS的SQL Analyze都提供了极其方便的EXPLAIN PLAN工具也许喜欢图形化界面的朋友们可以选用它们
