实例分析ORACLE数据库性能优化

2023-09-20 04:21:07 170

ORACLE数据库的优化方式和MYSQL等很大的区别，今天通过一个ORACLE数据库实例从表格、数据等各个方便分析了如何进行ORACLE数据库的优化。

tsfree.sql视图

这个sql语句迅速的对每一个表空间中的空间总量与每一个表空间中可用的空间的总量进行比较

表空间是数据库的逻辑划分，一个表空间只能属于一个数据库。所有的数据库对象都存放在指定的表空间中。但主要存放的是表，所以称作表空间。

SELECTFS.TABLESPACE_NAME"Talbspace",
(DF.TOTALSPACE-FS.FREESPACE)"UserdMB",
FS.FREESPACE"FreeMB",
DF.TOTALSPACE"TotalMB",
ROUND(100*(FS.FREESPACE/DF.TOTALSPACE))"PctFree"FROM
(SELECTTABLESPACE_NAME,ROUND(SUM(BYTES)/1048576)TOTALSPACEFROM
DBA_DATA_FILESGROUPBYTABLESPACE_NAME)DF,
(SELECTTABLESPACE_NAME,ROUND(SUM(BYTES)/1048576)FREESPACE
FROMDBA_FREE_SPACE
GROUPBYTABLESPACE_NAME)FSWHEREDF.TABLESPACE_NAME=FS.TABLESPACE_NAME;

varray表的使用

CREATEORREPLACETYPEEMPLOYER_NAMEASOBJECT(E_NAMEVARCHAR(40));

CREATEORREPLACETYPEPRIOR_EMPLOYER_NAME_ARRASVARRAY(10)OF
EMPLOYER_NAME;

CREATEORREPLACETYPEFULL_MAILLING_ADRESS_TYPEASOBJECT(STREET
VARCHAR2(80),CITYVARCHAR2(80),STATECHAR(2),ZIPVARCHAR2(10));

CREATEORREPLACETYPEEMPLOYEEASOBJECT(LAST_NAMEVARCHAR(40),
FULL_ADDRESSFULL_MAILLING_ADRESS_TYPE,PRIOR_EMPLOYERS
PRIOR_EMPLOYER_NAME_ARR);

CREATETABLEEMPOFEMPLOYEE;

INSERTINTOEMPVALUES('Jim',FULL_MAILLING_ADRESS_TYPE('AirplanAve','Rocky','NC','2343'),PRIOR_EMPLOYER_NAME_ARR(EMPLOYER_NAME('IBM'),EMPLOYER_NAME('APPLE'),EMPLOYER_NAME('CNN')));

--回滚

DROPTYPEPRIOR_EMPLOYER_NAME_ARRFORCE;
DROPTYPEFULL_MAILLING_ADRESS_TYPEFORCE;
DROPTYPEEMPLOYEEFORCE;
DROPTYPEEMPLOYER_NAMEFORCE;
DROPTABLEEMP;
COMMIT;
SELECTP.LAST_NAME,PRIOR_EMPLOYERS.*
FROMEMPP,TABLE(P.PRIOR_EMPLOYERS)PRIOR_EMPLOYERS
WHEREP.LAST_NAME='Jim';

SQL执行过程

1，检查安全性，确保sql数据执行者有权限执行
2，检查sql语法
3，可能发生的查询重新书写
4，执行

创建执行计划
生产器接受经过解析的sql
捆绑执行计划执行执行计划读取结果记录排序结果集

数据访问方式：

1，全表扫描db_file_multiblock_read_count=128
一次性最大读取block的数量
Oracle开启并行：Altertableemployeeparalleldegree35;
顺序读取，直到结尾
1，当表中不存在索引
2，查询中不包含where字句
3，内置函数中的索引无效
4，like操作%开头
5，使用基于成本优化器数据量少时
6，当初始化文件中存在optimizer_mode=all_rows
7，负向条件查询不能使用索引例如status!=0,notin,notexists可以优化为in(2,3);

下列情况的SQL语句会导致全表扫：

1，使用null条件查询导致全表扫，因为索引不能为空
为了绕过全表扫这个问题，可以采取这样的方法
updateempsetname='N/A'wherenameisnull;
selectnamefromempwherename='N/A';
2，对没有索引的字段查询，找到where条件后面的查询不带索引的字段，加索引可以
大大提高查询性能。
3，带有like条件的查询like'%x%'全表扫描，like'x%'不会全表扫，因为like
以字符开始。
4，内置的函数使索引无效，对于Date类型的数据来说非常的严重
内置函数（to_date，to_char）
如果没有创建与内置函数匹配的基于函数的索引，那么这些函数通常会导致sql优化器全表扫描
selectnamefromempwheredate
如何找出影响力高的sql语句
视图v$sqlarea,下列参数按照重要性从高到低排序
executions：越经常执行的sql语句就应当越早的调整，因为它会对整体的性能产生巨大的影响。
disk_reads:磁盘读取，高的磁盘读取量可能表明查询导致过多的输入输出量。
rows_processed:记录处理，处理大量的记录的查询会导致较大的输入输出量，同时在排序的时候对TEMP表空间产生影响。
buffer_gets:缓冲区读取，高的缓冲读取量可能表明了一个高资源使用量的查询。
sorts:排序会导致速度的明显减低，尤其是在TEMP表空间中进行的排序。

2.赛列获取
Oracle对单表簇和多表簇进行散列存储，用来在连接操作中减低输入输出
3，ROWID访问


通过Rowid访问单条数据最快的方式，在实际的引用中，首先从索引中收集ROWID,然后通过ROWID进行数据读取
索引访问方式
索引都可以看做一组符合主键和ROWID的组合，索引访问的目的是收集对目标快速读取时所需要的ROWID
B树索引，位图索引基于函数的索引.
索引范围扫描：读取一个或者多个ROWID索引数值升序排列
eg：select*fromtablewherea='a';
快速全索引扫描


eg:selectdistinctcolor,count(*)fromtablegroupbycolor;
单个索引扫描:读取一个单独的ROWID


降序索引范围扫描:读取一个或者多个ROWID索引数值降序排列


AND-EQUALS:select*fromtablewherea='a'andb>34;从where字句中收集多个ROWID


连接操作


嵌套循环连接


散列连接


散列连接通常快于嵌套循环连接，特别是在驱动表以及在查询的where子句中过滤，只剩下少量的记录的情况下


排序合并连接


连接提示：
表反向连接提示，例如，NOTIN,NOTEXISTS
尽量避免使用NOTIN子句（它将调用子查询），而应该使用NOTEXISTS子句(它将调用相关联的子查询)，
因为如果子查询返回的任何一条记录中包含空值，那么该查询将不会返回记录,如果允许NOTIN子句查询为空，那么
这种查询的性能非常的低，子查询会在外层查询块中对每一条记录重新执行一次。


排序大小sort_area_size_init.ora参数，在控制台查看sort_area_size;


查询语句：showparametersort_area_size;


磁盘排序的执行速度要比内存排序的的执行速度慢14000倍


磁盘排序之所以昂贵，有以下几个原因：


1，同在内存中进行排序比较，速度太慢

2，磁盘排序耗费临时表空间的资源


数据库分配2个临时表空间：


selectDEFAULT_TABLESPACE,TEMPORARY_TABLESPACEfromdba_userswhereusername='SYS';


select*fromdba_temp_free_space;


Oracle临时表空间主要充当两个主要作用：临时表数据段分配和排序汇总溢出段。


排序汇总溢出的范围比较广泛。我们在SQL语句中进行orderby/groupby等操作，


首先是选择PGA的内存sortarea、hasharea和bitmaparea。


如果SQL使用排序空间很高，单个serverprocess对应的PGA不足以支撑排序要求的时候，临时表空间会充当排序段的数据写入。
而磁盘排序会降低单个任务的速度，同时还会影响Oracle实例中正在执行的其他任务，而且过多的磁盘排序将导致过多的空闲缓冲等待


以及将其他任务的数据块从缓冲池中分页出去的昂贵代价。
Oracle首先尝试在sort_area_size分配的内存区中进行排序，Oracle只有不能再内存中排序时，才会调用磁盘排序

并将内存框架迁移到TEMP表空间，继续进行排序。
使用索引范围扫描的总体原则
--对于原始排序的表，仅读取少于40%的表记录查询就应该使用索引范围扫描，反之，多余40%，使用全表扫。
--对于未排序的表，仅读取少于7%的表记录查询就应该使用索引范围扫描，反之，多余7%，使用全表扫。
表的访问方式
sql优化器


对于任何一个sql语句来说，存在唯一的优化表访问方式，而你的工作就是找到这种方式，并且长期使用它。


db_file_multiblock_read_count


目的是为sql语句生成最快并且好资源最少的执行计划


1，基于规则的优化器
步骤
对于在where子句中的每一个表
--生成一个可行的执行计划列表，这个列表中列出所有可以用来访问表的路径
--为每一个执行计划指定级别数值
--选择级别数值最低的计划
--对结果集的选择级别最低连接方法进行评估

基于规则优化器（PBO）特征
-总是使用索引，使用索引永远比使用全表扫描或使用排序合并连接（排序合并连接不需要索引）更加可取
-总是从驱动表开始在from字句的最后一个表是驱动表,在这个表中选择的记录数应该是最少(查询返回值最少)，RBO在执行嵌套循环连接
操作时，将这个驱动表作为第一个操作表。
-只有在不可避免的情况下才使用全表扫描
-任何索引都可以
-有时越简单越好

2，基于成本的优化器(CBO)
基于规则优化提供更加复杂的优化替代方案
ANALYZETABLETT_TCAS_HK_QTYCOMPUTESTATISTICS;
ANALYZETABLETT_TCAS_HK_QTYESTIMATESTATISTICSSAMPLE5000ROWS;
ANALYZETABLETT_TCAS_HK_QTYESTIMATESTATISTICSSAMPLE5000ROWSFORALLINDEXEDCOLUMNS;

CBO在以下情况会选择错误的全表扫描
1，最高峰值过高
2，错误的optimizer_mode,如果optimizer_mode设置为all_rows,choose,那么sql优化器会倾向于使用全表扫描。
3，多表连接，存在多余3张表连接时，即使连接中存在索引，cbo仍然会对这些表进行全表扫描。
4，不平衡的索引分布，比如color='blue'color字段上有索引,但是只有1%的记录属于blue，



SQL的SGA统计资料
selectname,valuefromv$sysstatwherenamelike'table%'


tablescans(shorttable)--对小表全表扫描的次数


tablescans(longtable)--对大表全表扫描的次数，评估是否通过加索引减少大表的扫描次数或者通过调用Oracle并行（opq）来提高查询的执行速度。


tablescansRowsGotten--这个数目说明全表扫描扫描记录条数


tablescansblocksGotten--扫描获取数据库的数目


Tablefetchbyrowid--通过索引访问记录的数目，这里的索引通常是嵌套循环连接


tablefetchbyContinuedRow--这个数目说明与其他数据块连接在一起的记录数目
程序库缓存中可以多次使用的SQL


Oracle在辨认"相同的"sql语句是存在问题


例如：selectfromcustomer;SelectFromCustomer;尽管区别字母的大小写，Oracle会对第二个sql语句进行重新编译执行；

实例分析ORACLE数据库性能优化

热门推荐

随机推荐