Oracle索引問(wèn)題診斷與優(yōu)化方法是什么呢？

一、實(shí)驗

create table s1 as select * from SH。SALES;

create table s2 as select * from SH。SALES;

s1表沒(méi)有建立索引

s2表有建立索引

set timing on;

select * from s1 where prod_id=1;

2。

??45s

select * from s2 where prod_id=1;

0。59s

可見(jiàn)索引對于表查詢(xún)速度的重要性。

二、索引性能測試與診斷

1、查看數據庫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

2、查出沒(méi)有建立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')

3、查出建立了過(guò)量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')

一個(gè)表可以有幾百個(gè)索引，但是對于頻繁插入和更新表，索引越多系統CPU，I/O負擔就越重；建議每張表不超過(guò)5個(gè)索引。

實(shí)驗：

create table table1 as select * from SH。SALES;

create table table2 as select * from SH。SALES;

table1只在prod_id列建索引

table2在所有列建索引

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

29282

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

4、找出全表掃描（Full Scan）的Sid和SQL

A full table scan occurs when every block is read from a table。

?? Full table scans are often a preferred performance option in batch-style applications, such as decision support。 We have seen some excellent run-time improvements in decision support systems that use the parallel query option, which relies on full table scans to operate。

?? However, full table scans at an OLTP site during prime online usage times can create havoc with response times。 Full table scans, even on small tables, can degrade response times particularly when the small table drives the query, and this table is not always the most efficient access path。

The following query reports how many full table scans are taking place:

SELECT name, value

FROM v$sysstat

WHERE name LIKE '%table %'

ORDER BY name;

The values relating to the full table scans are:

table scans (long tables) - a scan of a table that has more than five database blocks

table scans (short tables) - a count of full table scans with five or fewer blocks

If the number of long table scans is significant, there is a strong possibility that SQL statements in your application need tuning or indexes need to be added。

To get an appreciation of how many rows and blocks are being accessed on average for the long full table scans, use this calculation (the sample data comes from an OLTP application):

Average Long Table Scan Blocks

= (table scan blocks gotten - (short table scans * 5))

/ long table scans

= (3,540,450 - (160,618 * 5)) / 661

= (3,540,450 - (803,090)) / 661

= 4,141 blocks read per full table scan

In our example, 4141 average disk reads performed on an OLTP application 661 times in the space of a few short hours is not a healthy situation。

If you can identify the users who are experiencing the full table scans, you can find out what they were running to cause these scans。

?? Below is a script that allows you to do this:

REM FILE NAME: fullscan。sql

REM LOCATION: Database Tuning\File I/O Reports

REM FUNCTION: Identifies users of full table scans

REM TESTED ON: 7。

??3。3。5, 8。0。4。1, 8。1。5, 8。1。7, 9。0。1, 9。2。0。2

REM PLATFORM: non-specific

REM REQUIRES: v$session, v$sesstat, v$statname

REM This view is used by the fscanavg。

??sql script

REM

REM This is a part of the Knowledge Xpert for Oracle Administration REM library。

REM Copyright (C) 2001 Quest Software

REM All rights reserved。

REM

REM************ Knowledge Xpert for Oracle Administration *************

DROP VIEW full_table_scans;

CREATE VIEW full_table_scans

AS

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