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

SELECT FS.TABLESPACE_NAME "Talbspace",(DF.TOTALSPACE - FS.FREESPACE) "Userd MB",FS.FREESPACE "Free MB",DF.TOTALSPACE "Total MB",ROUND(100 * (FS.FREESPACE / DF.TOTALSPACE)) "Pct Free" FROM(SELECT TABLESPACE_NAME, ROUND(SUM(BYTES) / 1048576) TOTALSPACE FROMDBA_DATA_FILES GROUP BY TABLESPACE_NAME) DF,(SELECT TABLESPACE_NAME, ROUND(SUM(BYTES) / 1048576) FREESPACEFROM DBA_FREE_SPACEGROUP BY TABLESPACE_NAME) FS WHERE DF.TABLESPACE_NAME = FS.TABLESPACE_NAME;    

CREATE OR REPLACE TYPE EMPLOYER_NAME AS OBJECT (E_NAME VARCHAR(40));CREATE OR REPLACE TYPE PRIOR_EMPLOYER_NAME_ARR AS VARRAY(10) OF EMPLOYER_NAME;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')));

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

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

1，使用null条件查询导致全表扫，因为索引不能为空为了绕过全表扫这个问题，可以采取这样的方法 update emp set name = 'N/A' where name is null; select name from emp where name = 'N/A';2，对没有索引的字段查询，找到where条件后面的查询不带索引的字段，加索引可以大大提高查询性能。3，带有like条件的查询 like '%x%' 全表扫描，like 'x%' 不会全表扫，因为like以字符开始。4，内置的函数使索引无效，对于Date类型的数据来说非常的严重内置函数 （to_date，to_char）如果没有创建与内置函数匹配的基于函数的索引，那么这些函数通常会导致sql优化器全表扫描select name from emp where date < sysdate -8;检查where子句脚本是否含有 substr to_char decodeSELECT SQL_TEXT, DISK_READS, EXECUTIONS, PARSE_CALLSFROM V$SQLAREAWHERE LOWER(SQL_TEXT) LIKE '%substr%'OR LOWER(SQL_TEXT) LIKE '%to_char%'OR LOWER(SQL_TEXT) LIKE '%decode%'ORDER BY DISK_READS DESC;使用函数索引解决这个问题5，all_rows 优化器目标是提高吞吐量而且倾向于使用全表扫描，因此 对于任何一个要求sql快速查询返回部分结果集而言，optimizer_mode 应该设置为first_rows6,经验上，能过滤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 = ? 不可以命中索引 

 视图 v$sqlarea ,下列参数按照重要性从高到低排序 executions ：越经常执行的sql语句就应当越早的调整，因为它会对整体的性能产生巨大的影响。 disk_reads: 磁盘读取，高的磁盘读取量可能表明查询导致过多的输入输出量。 rows_processed:记录处理，处理大量的记录的查询会导致较大的输入输出量，同时在排序的时候对TEMP表空间产生影响。 buffer_gets:缓冲区读取，高的缓冲读取量可能表明了一个高资源使用量的查询。 sorts:排序会导致速度的明显减低，尤其是在TEMP表空间中进行的排序。

Oracle对单表簇和多表簇进行散列存储，用来在连接操作中减低输入 输出

索引都可以看做一组符合主键和ROWID的组合，索引访问的目的是收集对目标快速读取时所需要的ROWID

eg：select * from table where a = 'a';

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

 -- 对于原始排序的表， 仅读取少于40%的表记录查询就应该使用索引范围扫描，反之，多余40%，使用全表扫。 -- 对于未排序的表， 仅读取少于7%的表记录查询就应该使用索引范围扫描，反之，多余7%，使用全表扫。

步骤 对于在where子句中的每一个表-- 生成一个可行的执行计划列表，这个列表中列出所有可以用来访问表的路径-- 为每一个执行计划指定级别数值-- 选择级别数值最低的计划-- 对结果集的选择级别最低 连接方法进行评估 基于规则优化器（PBO）特征- 总是使用索引，使用索引永远比使用全表扫描或使用排序合并连接（排序合并连接不需要索引）更加可取- 总是从驱动表开始 在from字句的最后一个表是驱动表,在这个表中选择的记录数应该是最少(查询返回值最少)，RBO在执行嵌套循环连接操作时，将这个驱动表作为第一个操作表。- 只有在不可避免的情况下才使用全表扫描-任何索引都可以- 有时越简单越好 

 基于规则优化提供更加复杂的优化替代方案 ANALYZE TABLE TT_TCAS_HK_QTY COMPUTE STATISTICS; ANALYZE TABLE TT_TCAS_HK_QTY ESTIMATE STATISTICS SAMPLE 5000 ROWS; ANALYZE TABLE TT_TCAS_HK_QTY ESTIMATE STATISTICS SAMPLE 5000 ROWS FOR ALL INDEXED COLUMNS;  CBO在以下情况会选择错误的全表扫描 1，最高峰值过高 2，错误的optimizer_mode,如果optimizer_mode设置为all_rows,choose,那么sql优化器会倾向于使用全表扫描。 3，多表连接，存在多余3张表连接时，即使连接中存在索引，cbo仍然会对这些表进行全表扫描。 4，不平衡的索引分布，比如 color = 'blue' color字段上有索引,但是只有1%的记录属于blue，