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

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

tsfree.sql视图

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

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

varray 表的使用

CREATE OR REPLACE TYPE FULL_MAILLING_ADRESS_TYPE AS OBJECT(STREET
VARCHAR2(80),CITY VARCHAR2(80),STATE CHAR(2),ZIP VARCHAR2(10));

CREATE OR REPLACE TYPE EMPLOYEE AS OBJECT(LAST_NAME VARCHAR(40),FULL_ADDRESS FULL_MAILLING_ADRESS_TYPE,PRIOR_EMPLOYERS
PRIOR_EMPLOYER_NAME_ARR);

CREATE TABLE EMP OF EMPLOYEE;

INSERT INTO EMP VALUES('Jim',FULL_MAILLING_ADRESS_TYPE('Airplan Ave','Rocky','NC','2343'),PRIOR_EMPLOYER_NAME_ARR(EMPLOYER_NAME('IBM'),EMPLOYER_NAME('APPLE'),EMPLOYER_NAME('CNN')));

-- 回滚

DROP TYPE PRIOR_EMPLOYER_NAME_ARR FORCE;
DROP TYPE FULL_MAILLING_ADRESS_TYPE FORCE;
DROP TYPE EMPLOYEE FORCE;
DROP TYPE EMPLOYER_NAME FORCE;
DROP TABLE EMP;
COMMIT;
SELECT P.LAST_NAME,PRIOR_EMPLOYERS.*
FROM EMP P,TABLE(P.PRIOR_EMPLOYERS) PRIOR_EMPLOYERS
WHERE P.LAST_NAME = 'Jim';

SQL 执行过程

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

数据访问方式：

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

6,经验上，能过滤80%数据时就可以使用索引，对于订单状态，如果状态很少，不宜
使用索引，如果状态值很多可以使用索引。

7，如果查询字段大部分是单条数据查询，使用Hash索引性能更好
原因:B-TREE 索引的时间复杂度是O(log(n))
Hash 索引的时间复杂度是O(1)

8,符合索引最左前缀，例如建立符合索引(passWord,userName)
select from user u where u.pass_word = ? and u.user_name = ? 可以命中索引
select from user u where u.user_name = ? and u.pass_word= ? 可以命中索引
select from user u where u.pass_word = ? 可以命中索引
select from user u where u.user_name = ? 不可以命中索引

如何找出影响力高的sql语句

2.赛列获取

3，ROWID 访问

通过Rowid访问单条数据最快的方式，在实际的引用中，首先从索引中收集ROWID,然后通过ROWID进行数据读取

索引访问方式

B树索引，位图索引 基于函数的索引.

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

快速全索引扫描

eg: select distinct color,count(*) from table group by color;

单个索引扫描:读取一个单独的ROWID

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

AND - EQUALS: select * from table where a = 'a' and b > 34; 从where字句中收集多个ROWID

连接操作

嵌套循环连接

散列连接

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

排序合并连接

连接提示：

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

查询语句：show parameter sort_area_size;

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

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

1，同在内存中进行排序比较，速度太慢
2，磁盘排序耗费临时表空间的资源

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

select DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE from dba_users where username='SYS';

select * from dba_temp_free_space;

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

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

首先是选择PGA的内存sort area、hash area和bitmap area。

如果SQL使用排序空间很高，单个server process对应的PGA不足以支撑排序要求的时候，临时表空间会充当排序段的数据写入。

而磁盘排序会降低单个任务的速度，同时还会影响Oracle实例中正在执行的其他任务，而且过多的磁盘排序将导致过多的空闲缓冲等待

以及将其他任务的数据块从缓冲池中分页出去的昂贵代价。

Oracle首先尝试在sort_area_size 分配的内存区中进行排序，Oracle只有不能再内存中排序时，才会调用磁盘排序
并将内存框架迁移到TEMP表空间，继续进行排序。

使用索引范围扫描的总体原则

表的访问方式

sql优化器

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

db_file_multiblock_read_count

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

1，基于规则的优化器

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

  2，基于成本的优化器(CBO)

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

SQL 的SGA统计资料

select name,value from v$sysstat where name like 'table%'

table scans(short table) -- 对小表全表扫描的次数

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

table scans Rows Gotten -- 这个数目说明全表扫描扫描记录条数

table scans blocks Gotten -- 扫描获取数据库的数目

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

table　fetch by Continued Row -- 这个数目说明与其他数据块连接在一起的记录数目

程序库缓存中可以多次使用的SQL

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

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

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!