Oracle 性能分析与诊断｜诊断与优化Oracle 索引[通俗易懂]

数据库表设计不恰当往往是数据库性能表现低下的主要原因，其中索引设计不大是常见的问题。在进行数据库性能问题诊断时，应该重点关注索引的设计，以及SQL 语句的写法对索引的利用是否恰当。

在适当的表字段建立索引，能有效加快查询速度，例如创建两个表：

create table s1 as select * from SH.SALES;

create table s2 as select * from SH.SALES;

其中，s1 表没有建立索引，s2 表有建立索引。分别测试两个表的查询速度：

set timing on;

select * from s1 where prod_id=1;

2.45s

select * from s2 where prod_id=1;

0.59s

有建立索引的表比未建立索引的表查询速度要快很多，可见索引对于表查询速度的重要性。

另外，即使创建了索引，如果SQL 查询语句写法有问题，也会造成索引不能被利用的情况，例如，在Where 关键字后应该尽量避免使用函数，否则将抑制索引的使用，例如下面SQL 语句：

SELECT * FROM dwtable2 WHERE to_number(empno)=783;

Elapsed: 00:00:00.15

这个SQL 语句在Where 关键字后使用了不必要的to_number 函数，导致empno 这个字段上的索引不能被查询利用，这个SQL 语句可以改写成更高效率的写法：

SELECT * FROM dwtable2 WHERE empno=783;

Elapsed: 00:00:00.01

为了诊断数据库的索引设计，可利用下面的SQL 语句来查看Oracle 数据库index 信息：

SELECT A.OWNER, A.TABLE_OWNER, A.TABLE_NAME, A.INDEX_NAME, A.INDEX_TYPE,

B.COLUMN_POSITION, B.COLUMN_NAME, C.TABLESPACE_NAME,

A.TABLESPACE_NAME, A.UNIQUENESS

FROM DBA_INDEXES A, DBA_IND_COLUMNS B, DBA_TABLES C

WHERE A.OWNER = UPPER (‘hr’)

AND A.OWNER = B.INDEX_OWNER

AND A.OWNER = C.OWNER

AND A.TABLE_NAME LIKE UPPER (‘DEPARTMENTS’)

AND A.TABLE_NAME = B.TABLE_NAME

AND A.TABLE_NAME = C.TABLE_NAME

AND A.INDEX_NAME = B.INDEX_NAME

ORDER BY A.OWNER, A.TABLE_OWNER, A.TABLE_NAME, A.INDEX_NAME,

B.COLUMN_POSITION

还可以用下面的SQL 语句直接查出某个库中没有建立index 的表，分析是否有必要补充建立索引：

SELECT OWNER, TABLE_NAME

FROM ALL_TABLES

WHERE OWNER NOT IN (‘SYS’,’SYSTEM’,’OUTLN’,’DBSNMP’)

AND OWNER = UPPER (‘scott’)

MINUS

SELECT OWNER, TABLE_NAME

FROM ALL_INDEXES

WHERE OWNER NOT IN (‘SYS’,’SYSTEM’,’OUTLN’,’DBSNMP’)

一个表可以有几百个索引，但是对于频繁插入和更新表，索引越多系统CPU，I/O 负担就越重;建议每张表不超过5 个索引。可用以下SQL 语句查出建立了过量index 的表：

SELECT OWNER, TABLE_NAME, COUNT (*) “count”

FROM ALL_INDEXES

WHERE OWNER NOT IN (‘SYS’,’SYSTEM’,’OUTLN’,’DBSNMP’)

AND OWNER = UPPER (‘hr’)

GROUP BY OWNER, TABLE_NAME

HAVING COUNT (*) > (‘4’)

为了验证过量索引对性能的影响，读者可进行如下实验。

(1)创建两个表：

create table table1 as select * from SH.SALES;

create table table2 as select * from SH.SALES;

其中，table1 只在prod_id 列建索引，table2 在所有列建索引。

(2)分别更新两个表的相同prod_id 的数据，共29282 条数据：

SELECT count(*) FROM table1 where prod_id=30;

29282

(3)对比更新的速度：

set timing on;

update table1 set cust_id=1 where prod_id=30;

10.56s

update table2 set cust_id=1 where prod_id=30;

11.35s

可见过量的索引对于做更新操作的SQL 语句而言，会造成执行效率下降的情况，如果要更新的数据量比较大的话，效率的影响会更加明显。

对于一个SQL 语句的执行，如果没有可利用的索引，Oracle 将进行全表扫描，这将对性能造成比较大的影响，尤其是大数据量的全表扫描，效率是非常低下的，因此在性能测试和诊断分析过程中，寻找发生了全表扫描的Sid 和SQL 就尤为关键，下面的SQL 语句可以从v$sysstat

视图中找出有多少全表扫描在进行：

SELECT name, value

FROM v$sysstat

WHERE name LIKE ‘%table %’

ORDER BY name;

下面的SQL 语句将找出正在做全表扫描的Session：

SELECT ss.username

|| ‘(‘

|| se.sid

|| ‘) ‘ “User Process”,

SUM (DECODE (NAME, ‘table scans (short tables)’, VALUE)) “Short Scans”,

SUM (DECODE (NAME, ‘table scans (long tables)’, VALUE)) “Long Scans”,

SUM (DECODE (NAME, ‘table scan rows gotten’, VALUE)) “Rows Retrieved”

FROM v$session ss, v$sesstat se, v$statname sn

WHERE se.statistic# = sn.statistic#

AND ( NAME LIKE ‘%table scans (short tables)%’

OR NAME LIKE ‘%table scans (long tables)%’

OR NAME LIKE ‘%table scan rows gotten%’

)

AND se.sid = ss.sid

AND ss.username IS NOT NULL

GROUP BY ss.username

|| ‘(‘

|| se.sid

|| ‘) ‘;