SQL 性能优化大全

你当然也在SQL句级或是会话(session)级对其进行覆盖；
8
二、 SQL 优化建议原则
1、选用合适的ORACLE优化器
为了使用基于成本的优化器(CBO, Cost-Based Optimizer) , 你 必须经常运行analyze 命令,以增加数据库中的对象统计信息
(object statistics)的准确性 。
15
二、 SQL 优化建议原则
3、选择最有效率的表名顺序(基于规则的优化器中有效)
例如: 表 TAB1 表 TAB2 16,384 条记录 1 条记录
选择TAB2作为基础表 (最好的方法)
select count(*) from tab1,tab2 执行时间0.96秒 选择TAB1作为基础表 (不佳的方法) select count(*) from tab2,tab1 执行时间26.09秒
4、WHERE子句中的连接顺序
ORACLE采用自下而上的顺序解析WHERE子句； 根据这个原理,表之间的连接必须写在其他WHERE 条件之前, 那些可以过滤掉最大数量记录的条件必 须写在WHERE子句的末尾。
18
二、 SQL 优化建议原则
4、WHERE子句中的连接顺序
SELECT * FROM EMP E
方式C：where colA between 1 AND 10000
20
二、 SQL 优化建议原则
5、SELECT子句中避免使用 ‚*‛
当你想在 SELECT 子句中列出所有的 COLUMN 时 , 使用动态SQL列引用 ‘*’ 是一个方便的方法.
实际上,ORACLE在解析的过程中, 会将’*’ 依次
转换成所有的列名, 这个工作是通过查询数据字典
完成的, 这意味着将耗费更多的时间
21
二、 SQL 优化建议原则
6、减少访问数据库的次数
当执行每条SQL语句时, ORACLE在内部执行了许 多工作: 解析SQL语句, 估算索引的利用率, 绑定变
量 , 读数据块等等.
由此可见, 减少访问数据库的次数 , 就能实际上减
23
二、 SQL 优化建议原则
6、减少访问数据库的次数
方式二:DECLARE
CURSOR C1 (E_NO NUMBER) IS SELECT EMP_NAME,SALARY,GRADE FROM EMP WHERE EMP_NO = E_NO;
BEGIN OPEN C1(342); FETCH C1 INTO …,..,.. ; ….. OPEN C1(291);
6
交流提纲
SQL 性能优化意义
SQL 优化建议
7
二、 SQL 优化建议原则
1、选用合适的ORACLE优化器
主要有3种:a. RULE (基于规则) CHOOSE (选择性)。 b. COST (基于成本) c.
设置缺省的优化器,可以通过对init.ora文件中
OPTIMIZER_MODE参数的各种声明,如 RULE,COST,CHOOSE,ALL_ROWS,FIRST_ROWS .
SQL 性能优化交流资料
交流提纲
SQL 性能优化意义
SQL 优化建议
1
一、 SQL 性能优化意义
（一）ORACLE 性能优化主要方法
硬件升级(CPU、内存、硬盘)； 版本及参数设置； 应用程序设计（框架、调用方式 --- 源代 码）； SQL 语句优化；
2
一、 SQL 性能优化意义
读入多个数据块(database block)的方式优化全表扫描 ；
基于ROWID的访问方式情况,能提高访问表的效率, ROWID 包含了表中记录的物理位置信息.ORACLE采用索引(INDEX)
实现了数据和存放数据的物理位置(ROWID)之间的联系. 通
常索引提供了快速访问ROWID的方法,因此那些基于索引列 的查询就可以得到性能上的提高
二、 SQL 优化建议原则
2、合理使用索引
select,update,delete 语句中的子查询应当有规律地查找少于20%的表 行.如果一个语句查找的行数超过总行数的20%,它将不能通过使用 索引获得性能上的提高. 索引可能产生碎片,因为记录从表中删除时,相应也从表的索引中删 除.表释放的空间可以再用,而索引释放的空间却不能再用.频繁进行 删除操作的被索引的表,应当阶段性地重建索引,以避免在索引中造 成空间碎片,影响性能.在许可的条件下,也可以阶段性地truncate 表,truncate命令删除表中所有记录,也删除索引碎片. 在使用索引时一定要按索引对应字段的顺序进行引用。 用（+）比用NOT IN更有效率。 12
16
二、 SQL 优化建议原则
3、选择最有效率的表名顺序(基于规则的优化器中有效)
如果有3个以上的表连接查询, 那就需要选择交叉表(intersection table)作为
基础表, 交叉表是指那个被其他表所引用的表 。 例如:EMP表描述了LOCATION表和CATEGORY表的交集. SELECT * FROM LOCATION L , CATEGORY C,EMP E WHERE E.EMP_NO BETWEEN 1000 AND 2000 AND E.CAT_NO = C.CAT_NO AND E.LOCN = L.LOCN
1、硬件升级
CPU：在任何机器中CPU的数据处理能力往往是衡量计算机性能的 一个标志，并且ORACLE是一个提供并行能力的数据库系统，如果 运行队列数目超过了CPU处理的数目，性能就会下降
；
内存：衡量机器性能的另外一个指标就是内存的多少了，在 ORACLE中内存和我们在建数据库中的交换区进行数据的交换，读 数据时，磁盘I/O必须等待物理I/O操作完成，在出现ORACLE的内 存瓶颈时，我们第一个要考虑的是增加内存，由于I/O的响应时间 是影响ORACLE性能的主要参数； 网络条件：NET*SQL负责数据在网络上的来往，大量的SQL会令 网络速度变慢。比如10M的网卡和100的网卡就对NET*SQL有非常 明显的影响，还有交换机、集线器等等网络设备的性能对网络的影 响很明显，建议在任何网络中不要试图用3个集线器来将网段互联。 3
UNION ALL
SELECT ＊ FROM orders WHERE order_num=1008 ；
14
二、 SQL 优化建议原则
3、选择最有效率的表名顺序(基于规则的优化器中有效)
ORACLE的解析器按照从右到左的顺序处理FROM子句中的表名,因 此FROM子句中写在最后的表(基础表 driving table)将被最先处理； 在 FROM子句中包含多个表的情况下 ,你必须选择记录条数最少的 表作为基础表。 当ORACLE处理多个表时, 会运用排序及合并的方式连接它们.首先, 扫描第一个表(FROM子句中最后的那个表)并对记录进行派序,然后 扫描第二个表(FROM子句中最后第二个表),最后将所有从第二个表 中检索出的记录与第一个表中合适记录进行合并。
<=“P1234”
13
二、 SQL 优化建议原则
2、合理使用索引
SELECT ＊ FROM orders WHERE (cust_num=126 AND order_num>1001) OR order_num=1008 ；
SELECT ＊ FROM orders WHERE cust_num=126 AND order_num>1001
WHERE SAL > 5000 AND JOB = „MANAGER‟ AND 25 < (SELECT COUNT(*) FROM EMP
WHERE MGR=E.EMPNO);

SELECT * FROM EMP E WHERE 25 < (SELECT COUNT(*) FROM EMP
WHERE MGR=E.EMPNO)
在缺省情况下,ORACLE采用CHOOSE优化器, 为了避免那些 不必要的全表扫描(full table scan) , 你必须尽量避免使用
CHOOSE优化器,而直接采用基于规则或者基于成本的优化
器
9
二、 SQL 优化建议原则
1、选用合适合ORACLE优化器
访问Table的两种方式 ：全表扫描和通过ROWID访问表 ； 全表扫描就是顺序地访问表中每条记录. ORACLE采用一次
一、 SQL 性能优化意义
4、SQL脚本优化的主要原因
SQL语句是对数据库(数据)进行操作的惟一途径； SQL语句消耗了70%~90%的数据库资源； SQL语句独立于程序设计逻辑，相对于对程序源 代码的优化，对SQL语句的优化在时间成本和风
险上的代价都很低；
SQL语句可以有不同的写法； SQL语句易学，难精通
一、 SQL 性能优化意义
2、版本及参数设置
4
一、 SQL 性能优化意义
3、数据库应用程序的优化
程序设计中的一个著名定律是20％的代码用去了80％的时间； 两种方式优化：源代码的优化和SQL语句的优化。源代码的优化在 时间成本和风险上代价很高；另一方面，源代码的优化对数据库系 统性能的提升收效有限。 DBMS处理查询计划的过程是这样的：在做完查询语句的词法、语 法检查之后，将语句提交给DBMS的查询优化器，优化器做完代数 优化和存取路径的优化之后，由预编译模块对语句进行处理并生成 查询规划，然后在合适的时间提交给系统处理执行，最后将执行结 果返回给用户。 5

----本脚本更有效率
SELECT * FROM EMP E， LOCATION L , CATEGORY C
WHERE E.EMP_NO BETWEEN 1000 AND 2000 AND E.CAT_NO = C.CAT_NO AND E.LOCN = L.LOCN