Oracle SQL语法和性能优化.docx
- 文档编号:17890863
- 上传时间:2023-08-04
- 格式:DOCX
- 页数:12
- 大小:101.88KB
Oracle SQL语法和性能优化.docx
《Oracle SQL语法和性能优化.docx》由会员分享,可在线阅读,更多相关《Oracle SQL语法和性能优化.docx(12页珍藏版)》请在冰点文库上搜索。
OracleSQL语法和性能优化
OracleSQL的性能优化
目录
OracleSQL的性能优化1
1.术语1
2.性能优化的策略和原则2
3.常见的诊断和性能优化工具3
3.1.操作系统参数的监控:
3
3.2.数据库参数调整3
3.3.应用系统的通用优化工具4
3.4.Sql的优化工具5
4.SQL优化的基本原则5
5.一些有用的sql语句8
6.思考9
1.术语
RBO和CBO:
Oracle的优化器共有两种的优化方式,即基于规则的优化方式(Rule-BasedOptimization,简称为RBO)和基于代价的优化方式(Cost-BasedOptimization,简称为CBO)。
A、RBO方式优化器在分析SQL语句时,所遵循的是Oracle内部预定的一些规则。
比如我们常见的,当一个where子句中的一列有索引时去走索引。
B、CBO方式依词义可知,它是看语句的代价(Cost)了,这里的代价主要指Cpu和内存。
优化器在判断是否用这种方式时,主要参照的是表及索引的统计信息。
统计信息给出表的大小、有少行、每行的长度等信息。
这些统计信息起初在库内是没有的,是你在做analyze后才出现的,很多的时侯过期统计信息会令优化器做出一个错误的执行计划,因些我们应及时更新这些信息。
内存分配(SGA和PGA)SGA:
是用于存储数据库信息的内存区,该信息为数据库进程所共享。
它包含Oracle服务器的数据和控制信息,它是在Oracle服务器所驻留的计算机的实际内存中得以分配,如果实际内存不够再往虚拟内存中写。
这些参数影响数据库的系统全局区域(SGA)。
每当一个Oracle9i数据库例程启动时,都在内存中分配SGA。
SGA是一组共享的内存结构,包含一个Oracle9i数据库系统的数据和控制信息。
如果多个用户同时连接到相同的数据库例程,这些用户将共享SGA中的数据。
2.性能优化的策略和原则
性能优化的策略和原则
Oracle数据库的性能优化策略一般包括服务器操作系统参数调整、数据库参数调整、网络性能调整、应用程序SQL语句分析及设计等几个方面,而应用程序的执行最终将归结为数据库中的SQL语句执行,因此SQL语句的执行效率最终决定了Oracle数据库的性能。
优化调整主要集中在以下几个方面
设计调整—应用的设计是ORACLE性能中最重要的因素。
实例调整—调整PGA、SGA等初始化参数。
对象调整—对表及索引的存储参数如pctfree、pctuse、freelists等。
这些参数能对性能参数比较大的影响。
SQL调整—因为对数据库的访问是通过SQL来实现的所以这部分的调整最花时间。
服务器、网络及磁盘调整—如果出现诸如CPU忙、内存交换频繁、磁盘I/O瓶颈等情况,我们需检查服务器、网络及磁盘配置。
一般性能优化的基本方法
1.设立合理的性能优化目标
我们做任何事情都会有个目标,没有了目标就会茫然,不知所措,性能优化也不例外。
有了目标还是不够的,就像我们平时说的要优化系统,要优化到什么程度呢?
所以说设立目标的最重要的一点是它们必须是可量化和可达到的。
例如对关键的某个过程或SQL花费了多少时间,要求在多少时间内完成,使用了多少资源,不能超过多少,最好用表格准确详细的记录下来。
2.测量并记录当前性能
利用相关工具进行量化
3.确定当前oracle的性能瓶颈(等待什么)
从Oracle动态性能视图v$system_event、v$session_event和v$session_wait中获得等待事件,进而找出影响性能的对象和sql语句
4.确定当前的os瓶颈
5.确定瓶颈原因
6.优化所需的部分(应用程序、数据库、i/o、争用、os、存储、网络等)
7.跟踪并实施更改过程
8.测量并记录当前性能
9.重复3-7,直到满足优化目标
3.常见的诊断和性能优化工具
3.1.操作系统参数的监控:
1.主要是监测OS的状况
2.
(1)Windows上的监控
使用控制面板-〉管理工具-〉性能即可
3.
(2)UNIX/LINUX上的监控
使用通用性的工具,包括sar、iostat、cpustat、mpstat、netstat、top、osview等。
3.2.数据库参数调整
打开enterprisemanagerconsole
查看数据库的众多参数,每个值的意思
打开工具菜单,里面有众多的工具.如topsql
3.3.OracleSQL性能检测的命令和工具
用下列SQL工具找出低效SQL:
SELECTEXECUTIONS,DISK_READS,BUFFER_GETS,ROUND((BUFFER_GETS-DISK_READS)/BUFFER_GETS,2)Hit_radio,ROUND(DISK_READS/EXECUTIONS,2)Reads_per_run,SQL_TEXT
FROMV$SQLAREA
WHEREEXECUTIONS>0ANDBUFFER_GETS>0AND(BUFFER_GETS-DISK_READS)/BUFFER_GETS<0.8
ORDERBY4DESC;
使用TKPROF工具来查询SQL性能状态SQLtrace工具收集正在执行的SQL的性能状态数据并记录到一个跟踪文件中.这个跟踪文件提供了许多有用的信息,例如解析次数.执行次数,CPU使用时间等.这些数据将可以用来优化你的系统.(未亲自做试验,TKPROF有许多执行参数.大家可以参考ORACLE手册来了解具体的配置.)
看一个实际的例子。
oracle10G自带工具:
ADDM和AWR。
国图的sql性能监控和分析。
3.4.应用系统的通用优化工具
你做analyze了吗?
表的统计信息的更新
analyzetabletable_namecomputestatistics;
验证该语句对性能的影响
演示如何生成所有的表的分析语句批命令
验证同样一条语句在不同环境下执行的时间和oracle采用的优化策略:
selectcount(*)fromt_archive_0100_file
664179
优化策略1:
SELECTSTATEMENT,GOAL=RULE
SORTAGGREGATE
TABLEACCESSFULLObjectowner=JADAObjectname=T_ARCHIVE_0100_FILE
执行时间:
2.594,2.828,3.063
优化策略2:
SELECTSTATEMENT,GOAL=ALL_ROWSCost=126Cardinality=1
SORTAGGREGATECardinality=1
INDEXFASTFULLSCANObjectowner=JADAObjectname=IND_0100_F_SERIES_CODECost=126Cardinality=664179
执行时间:
0.093,0.094,0.093
索引对性能的影响
3.5.Sql的优化工具
Pl/sql
OPTIMIZER_MODE初始化参数为整个instance选择了一个缺省的优化器模式,OPTIMIZER_MODE可设置的值及描述如下:
CHOOSE
优化器在CBO和RBO中选择一个模式,依赖于统计数据是否可用:
1)如果数据目录至少包含了一个表的统计数据,则选择CBO,并且以最大吞吐量(all_rows)作为执行目标。
2)如果数据目录仅包含了部分统计数据,则还是使用CBO模式,但优化器必须要猜测没有统计数据的部分的统计数据,可能会选择一个不是最好的执行计划。
3)如果数据目录没有包含任何表的统计数据,则使用RBO模式。
4.SQL优化的基本原则
1、用EXISTS替代IN,NOTEXISTS替代NOTIN:
在子查询中,NOT在许多基于基础表的查询中,为了满足一个条件,往往需要对另一个表进行联接.在这种情况下,使用EXISTS(或NOTEXISTS)通常将提高查询的效率。
SELECT*
FROMEMP(基础表)
WHEREEMPNO>0
ANDEXISTS(SELECT‘X'
FROMDEPT
WHEREDEPT.DEPTNO=EMP.DEPTNO
ANDLOC=‘MELB')
2、用Where子句替换HAVING子句:
避免使用HAVING子句,HAVING只会在检索出所有记录之后才对结果集进行过滤。
这个处理需要排序,总计等操作。
如果能通过WHERE子句限制记录的数目,那就能减少这方面的开销。
例如:
低效:
SELECTREGION,AVG(LOG_SIZE)
FROMLOCATION
GROUPBYREGION
HAVINGREGIONREGION!
=‘SYDNEY'
ANDREGION!
=‘PERTH'
高效
SELECTREGION,AVG(LOG_SIZE)
FROMLOCATION
WHEREREGIONREGION!
=‘SYDNEY'
ANDREGION!
=‘PERTH'
GROUPBYREGION
3、使用表的别名(Alias)
当在SQL语句中连接多个表时,请使用表的别名并把别名前缀于每个Column上。
这样一来,就可以减少解析的时间并减少那些由Column歧义引起的语法错误。
4、用EXISTS替换DISTINCT
当提交一个包含一对多表信息(比如部门表和雇员表)的查询时,避免在SELECT子句中使用DISTINCT。
一般可以考虑用EXIST替换
例如:
低效:
SELECTDISTINCTDEPT_NO,DEPT_NAME
FROMDEPTD,EMPE
WHERED.DEPT_NO=E.DEPT_NO
高效:
SELECTDEPT_NO,DEPT_NAME
FROMDEPTD
WHEREEXISTS(SELECT‘X'
FROMEMPE
WHEREE.DEPT_NO=D.DEPT_NO);
EXISTS使查询更为迅速,因为RDBMS核心模块将在子查询的条件一旦满足后,立刻返回结果。
5、用UNION替换OR(适用于索引列)
通常情况下,用UNION替换WHERE子句中的OR将会起到较好的效果。
对索引列使用OR将造成全表扫描。
注意,以上规则只针对多个索引列有效。
如果有column没有被索引,查询效率可能会因为你没有选择OR而降低。
在下面的例子中,LOC_ID和REGION上都建有索引。
高效:
SELECTLOC_ID,LOC_DESC,REGION
FROMLOCATION
WHERELOC_ID=10
UNION
SELECTLOC_ID,LOC_DESC,REGION
FROMLOCATION
WHEREREGION=“MELBOURNE”
低效:
SELECTLOC_ID,LOC_DESC,REGION
FROMLOCATION
WHERELOC_ID=10ORREGION=“MELBOURNE”
如果你坚持要用OR,那就需要返回记录最少的索引列写在最前面。
6、用IN来替换OR
下面的查询可以被更有效率的语句替换:
低效:
SELECT….
FROMLOCATION
WHERELOC_ID=10
ORLOC_ID=20
ORLOC_ID=30
高效
SELECT…
FROMLOCATION
WHERELOC_ININ(10,20,30);
7、WHERE子句中的连接顺序.
ORACLE采用自下而上的顺序解析WHERE子句,根据这个原理,表之间的连接必须写在其他WHERE条件之前,那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾。
例如:
(低效,执行时间156.3秒)
SELECT…
FROMEMPE
WHERESAL>50000
ANDJOB=‘MANAGER'
AND25<(SELECTCOUNT(*)FROMEMP
WHEREMGR=E.EMPNO);
(高效,执行时间10.6秒)
SELECT…
FROMEMPE
WHERE25<(SELECTCOUNT(*)FROMEMP
WHEREMGR=E.EMPNO)
ANDSAL>50000
ANDJOB=‘MANAGER';
8、计算记录条数和一般的观点相反,count(*)比count
(1)稍快,当然如果可以通过索引检索,对索引列的计数仍旧是最快的。
例如COUNT(EMPNO)
现场演示sql的优化
5.一些有用的sql语句
1.取所有的表名
selecttable_namefromsys.all_all_tableswhereowner=user(要有dba权限)
selecttable_namefromdba_tableswhereowner=userordertable_name
2.生成计算所有表的记录数的sql语句。
select'select'''||a.table_name||''',count(*)from'||a.table_namefromdba_tablesawhereowner='JADA'
3.重新分析表的统计信息
analyzetabletable_namecomputestatistics;
4.用sql生成批命令
select'analyzetable'||table_name||'computestatistics;'fromdba_tableswhereowner=userorderbytable_name;
5.用ultraedit生成批命令
selecttable_namefromdba_tableswhereowner=user
6.思考
几个问题:
1.索引是做什么用的?
基于什么原理?
2.什么是复合索引,字段的顺序有影响吗?
3.什么叫索引覆盖技术?
4.oracle有聚集索引吗?
5.Count(*)和count
(1),count(field)一样吗?
6.Like‘%史%’能使用索引吗?
7.Where、Orderby、groupby、having哪个能用到索引?
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- Oracle SQL语法和性能优化 SQL 语法 性能 优化