sql优化首先了解SQL的执行过程
1. SQL语句处理过程
open-->parse-->bind-->execute-->fetch-->close
2. SQL语句详细处理过程
DML语句和SELECT查询语句都会经历如下过程:
2.1. Create a Cursor
2.2. Parse the Statement
2.5. Bind Aany Variables
2.7. Run the Statement
2.8. Close the Cursor
可选过程:
2.6. Parallelize the Statement
SELECT语句,带有Returning的子句的DML语句要求如下处理过程:
2.3. Describe Results of a Query
2.4. Define Output of a Query
2.8. Fetch Rows of a Query
3. 解析
在共享池中是否存在共享的SQL,语法检查,语义检查,权限检查,确定最佳执行计划。
解析是一个昂贵的操作,其中生成执行计划是最耗时的,应该避免重复解析,解析一次,多次执行。
将用户的SQL文本转换成oracle认识且可执行的语句,这个过程叫做解析过程。
解析分为软解析和硬解析。
当客户端发出一条SQL语句进入shared pool时,oracle首先将SQL文本转换成ASCII字符,然后根据hash函数计算对应的hash_value,然后到library catch中查找是否存在该SQL语句。
如果不存在,进行硬解析,步骤:
(1. 语法检查。失败退出解析。
(2. 到数据字典校验SQL语句涉及的对象和列是否都存在。失败退出解析。
(3. 将对象进行名称转换,比如将同名翻译成实际的对象等。失败退出解析。
(4. 检查游标里用户是否居有访问SQL语句里面所引用的对象的权限。失败退出解析。
(5. 通过优化器创建最优的执行计划,这一步是最耗CPU资源的。
(6. 将该游标产生的执行计划,SQL文本等装载进library cache中。
如果存在,说明该SQL以前运行过,于是进行软解析。软解析是相对于硬解析而已,如果从硬解析中去掉一些步骤,就是软解析。软解析分为三种类型。
(1. 某个sesison发出的SQL语句在library catch里其他session发出的SQL语句一致。这是,该解析过程可以将硬解析的5和6步骤去掉,但是仍然要进行硬解析的2,3,4步骤。
(2. 某个sesison发出的SQL语句在library catch里同一个session发出的SQL语句一致。该解析过程可以将硬解析的2,3,5和6步骤去掉,仍然需要4步骤进行权限检查,因为可能通过grant改变了该session用户的权限。
(3. 当设置了初始化参数session_cached_cursors时,当某个session对相同的cursor进行第三次访问时,将在该session的PGA里创建一个标记,并且该游标即使已经关闭也不会从library cache中交换出去。这样,该session以后再执行相同的SQL时,将跳过硬解析的所有步骤。这种情况下是最高效的解析方式,但是将会消耗很大的内存。
4. 绑定变量
找到绑定变量,给绑定变量分配值,减少硬解析,从共享执行计划中获益。
5. 执行
如果是DML语句,则锁定修改的行,知道提交和回滚。
如果是查询语句,则不锁定任何行。
执行必要的I/O与排序
6. 提取
为查询和带有returning子句的DML语句返回行
如果需要,进行排序
使用批量提取改进性能
Oracle没次可以fetch多行,在SQL*PLUS中使用ARRAYSIZE修改设定。
show arraysize;
set arraysize 100.
一般来讲,如果arraysize设定超过100,对性能的改善就没有任何实际意义了。
1. SQL语句处理过程
open-->parse-->bind-->execute-->fetch-->close
2. SQL语句详细处理过程
DML语句和SELECT查询语句都会经历如下过程:
2.1. Create a Cursor
2.2. Parse the Statement
2.5. Bind Aany Variables
2.7. Run the Statement
2.8. Close the Cursor
可选过程:
2.6. Parallelize the Statement
SELECT语句,带有Returning的子句的DML语句要求如下处理过程:
2.3. Describe Results of a Query
2.4. Define Output of a Query
2.8. Fetch Rows of a Query
3. 解析
在共享池中是否存在共享的SQL,语法检查,语义检查,权限检查,确定最佳执行计划。
解析是一个昂贵的操作,其中生成执行计划是最耗时的,应该避免重复解析,解析一次,多次执行。
将用户的SQL文本转换成oracle认识且可执行的语句,这个过程叫做解析过程。
解析分为软解析和硬解析。
当客户端发出一条SQL语句进入shared pool时,oracle首先将SQL文本转换成ASCII字符,然后根据hash函数计算对应的hash_value,然后到library catch中查找是否存在该SQL语句。
如果不存在,进行硬解析,步骤:
(1. 语法检查。失败退出解析。
(2. 到数据字典校验SQL语句涉及的对象和列是否都存在。失败退出解析。
(3. 将对象进行名称转换,比如将同名翻译成实际的对象等。失败退出解析。
(4. 检查游标里用户是否居有访问SQL语句里面所引用的对象的权限。失败退出解析。
(5. 通过优化器创建最优的执行计划,这一步是最耗CPU资源的。
(6. 将该游标产生的执行计划,SQL文本等装载进library cache中。
如果存在,说明该SQL以前运行过,于是进行软解析。软解析是相对于硬解析而已,如果从硬解析中去掉一些步骤,就是软解析。软解析分为三种类型。
(1. 某个sesison发出的SQL语句在library catch里其他session发出的SQL语句一致。这是,该解析过程可以将硬解析的5和6步骤去掉,但是仍然要进行硬解析的2,3,4步骤。
(2. 某个sesison发出的SQL语句在library catch里同一个session发出的SQL语句一致。该解析过程可以将硬解析的2,3,5和6步骤去掉,仍然需要4步骤进行权限检查,因为可能通过grant改变了该session用户的权限。
(3. 当设置了初始化参数session_cached_cursors时,当某个session对相同的cursor进行第三次访问时,将在该session的PGA里创建一个标记,并且该游标即使已经关闭也不会从library cache中交换出去。这样,该session以后再执行相同的SQL时,将跳过硬解析的所有步骤。这种情况下是最高效的解析方式,但是将会消耗很大的内存。
4. 绑定变量
找到绑定变量,给绑定变量分配值,减少硬解析,从共享执行计划中获益。
5. 执行
如果是DML语句,则锁定修改的行,知道提交和回滚。
如果是查询语句,则不锁定任何行。
执行必要的I/O与排序
6. 提取
为查询和带有returning子句的DML语句返回行
如果需要,进行排序
使用批量提取改进性能
Oracle没次可以fetch多行,在SQL*PLUS中使用ARRAYSIZE修改设定。
show arraysize;
set arraysize 100.
一般来讲,如果arraysize设定超过100,对性能的改善就没有任何实际意义了。