优化与索引

@对表的访问

1、全表扫描

1、对表所有的块，进行访问，采用多块读的方式

2、设置多块读的参数

SQL> show parameter db_file_multiblock

NAME

TYPE VALUE

------------------------------------

----------- ------------------------------

db_file_multiblock_read_count integ er 16

上面设置的多块读是16，Oracle读的时候尽量每次读取是16块，Oracle不害怕多块读，害怕的是产生多次物理io的读取

成本计算：

cost：标的块数

/db_file_multiblock_read_count db_file_multiblock_read_count这个参数设置的大小会影响Oracle在计算的时候的一个成本。如果说这个参数设置的足够大，那么就会导致好多表不走索引，会去走全表扫描

3、filter：过滤

读取了大量的数据，然后使用条件过滤了大量的数据，剩余了少量的数据行

4、filter是否合适，判断标准

1、读取了多少数据

2、取出了多少数据，过滤了多少数据

假设过滤掉99%的数据，那么过滤是失败的

90%以上的数据过滤掉，我们就应该考虑这个过滤的价值，也就是cost

2、走索引不使用多块读

1、成本计算：

访问索引的成本+索引访问表的成本

访问索引的成本：索引树的高度+叶子节点的块数

索引访问表的成本：行数*集群因子/总行数

2、集群因子：

最小值就是表的块数

最大值就是表的行数

集群因子高带来的问题：

1、计算走索引的时候的成本高

2、额外的占用过多的buffer

3、额外的增加物理io

3、取得数据量一般<5%~20%的话我们建议走索引

4、Oracle在计算成本的时候是假设所有的数据都在磁盘中，这样的话计算的成本都有物理读，而实际的生产中我们经常读取的数据，大部分都缓存在内存中

5、全表扫描在oltp中肯定会产生额外的一些物理读，因此一般不要全表扫描

表整理：

表不存在碎片的问题，表时间长了会有一个集群因子很高的问题，有些索引是一个表的主索引，时间长了可以按照主索引对这个表重新生成一下，按照这个主索引排下序，这样的话表的集群因子又会降下来了

select d.clustering_factor from dba_indexes d where d.table_name='T1'and owner='SYS '; //查看表的集群因子大小

select t.num_rows,t.blocks from dba_tables t where t.table_name='T1'and owner='SYS' ; //查看表的行数和块数

SQL> select * from t1 where object_id=10;

Execution Plan

----------------------------------------------------------

Plan hash value: 3617692013

--------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 8 | 1416 | 155 (2)| 00:00:02 |

|* 1 | TABLE ACCESS FULL| T1 | 8 | 1416 | 155 (2)| 00:00:02 |

--------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter("OBJECT_ID"=10)

Note

-----

- dynamic sampling used for this statement

Statistics

----------------------------------------------------------

5 recursive calls //递归sql 产生系统级别的select

0 db block gets

762 consistent gets //内存读

0 physical reads //物理读

0 redo size

1401 bytes sent via SQL*Net to client // 服务器给客户端发的字节

492 bytes received via SQL*Net from client //接收的字节

2 SQL*Net roundtrips to/from client //客户端和服务器的响应

0 sorts (memory) //内存排序

0 sorts (disk)

1 rows processed //实际返回一行

1、select*from dba_indexes d where d.index_name like'I_T1%'and owner='SYS';

blevel：是索引的树的高度，一般在5以下

distinct_keys：唯一值得数量

avg_leaf_blocks_per_key:平均块中的叶子块

avg_data_blocks_per_key:平均每个块中的数据块

在这里面有一列是distinct_keys和num_rows 我们希望distinct_keys很

高num_rows/distinct_keys若是很高那么就会取出很多行，所以这样是不好的，我们希望的是索引的值很高接近这个num的值

索引：

1、选择性

希望distinct_keys/num_rows的值大一些

选择性高说明where条件取出的数值少

2、集群因子

cluster_factor 取决于索引访问表的值

看索引好不好主要是上面的两个值

2、关于索引的几个参数

SQL> show parameter ind

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------