Oracle索引問題診斷與優化

　　一實驗

　　create table s as select * from SHSALES;

　　s表沒有建立索引

　　s表有建立索引

　　set timing on;

　　select * from s where prod_id=;

　　可見索引對於表查詢速度的重要性

　　二索引性能測試與診斷

　　查看數據庫Index信息

　　SELECT AOWNER ATABLE_OWNER ATABLE_NAME AINDEX_NAME AINDEX_TYPE

　　BCOLUMN_POSITION BCOLUMN_NAME CTABLESPACE_NAME

　　ATABLESPACE_NAME AUNIQUENESS

　　FROM DBA_INDEXES A DBA_IND_COLUMNS B DBA_TABLES C

　　WHERE AOWNER = UPPER (hr)

　　AND AOWNER = BINDEX_OWNER

　　AND AOWNER = COWNER

　　AND ATABLE_NAME LIKE UPPER (DEPARTMENTS)

　　AND ATABLE_NAME = BTABLE_NAME

　　AND ATABLE_NAME = CTABLE_NAME

　　AND AINDEX_NAME = BINDEX_NAME

　　ORDER BY AOWNER ATABLE_OWNER ATABLE_NAME AINDEX_NAME BCOLUMN_POSITION

　　查出沒有建立index的表

　　SELECT OWNER TABLE_NAME

　　FROM ALL_TABLES

　　WHERE OWNER NOT IN (SYSSYSTEMOUTLNDBSNMP) AND OWNER = UPPER (scott)

　　MINUS

　　SELECT OWNER TABLE_NAME

　　FROM ALL_INDEXES

　　WHERE OWNER NOT IN (SYSSYSTEMOUTLNDBSNMP)

　　查出建立了過量index的表

　　SELECT OWNER TABLE_NAME COUNT (*) count

　　FROM ALL_INDEXES

　　WHERE OWNER NOT IN (SYSSYSTEMOUTLNDBSNMP) AND OWNER = UPPER (hr)

　　GROUP BY OWNER TABLE_NAME

　　HAVING COUNT (*) > ()

　　一個表可以有幾百個索引但是對於頻繁插入和更新表索引越多系統CPUI/O負擔就越重建議每張表不超過個索引

　　實驗

　　create table table as select * from SHSALES;

　　table只在prod_id列建索引

　　table在所有列建索引

　　SELECT count(*) FROM table where prod_id=;

　　set timing on;

　　update table set cust_id= where prod_id=;

　　找出全表掃描（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 batchstyle applications such as decision support We have seen some excellent runtime 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 * ))

　　/ long table scans

　　= ( ( * )) /

　　= ( ()) /

　　= blocks read per full table scan

　　In our example average disk reads performed on an OLTP application 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: fullscansql

　　REM LOCATION: Database Tuning\File I/O Reports

　　REM FUNCTION: Identifies users of full table scans

　　REM TESTED ON:

　　REM PLATFORM: nonspecific

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

　　REM This view is used by the fscanavgsql script

　　REM

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

　　REM Copyright (C) Quest Software

　　REM

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

　　DROP VIEW full_table_scans;

　　CREATE VIEW full_table_scans

　　SELECT ssusername

　　|| (

　　|| sesid

　　|| ) 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 sestatistic# = snstatistic#

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

　　OR NAME LIKE %table scans (long tables)%

　　OR NAME LIKE %table scan rows gotten%

　　)

　　AND sesid = sssid

　　AND ssusername IS NOT NULL

　　GROUP BY ssusername

　　|| (

　　|| sesid

　　|| ) ;

　　COLUMN User Process FORMAT a;

　　COLUMN Long Scans FORMAT ;

　　COLUMN Short Scans FORMAT ;

　　COLUMN Rows Retreived FORMAT ;

　　COLUMN Average Long Scan Length FORMAT ;

　　TTITLE Table Access Activity By User

　　SELECT User Process Long Scans Short Scans Rows Retrieved

　　FROM full_table_scans

　　ORDER BY Long Scans DESC;

　　找出可能有全表掃描的sql語句

　　select sidsql_text

　　From v$session sv$sql q

　　Where sid in()

　　And (qsql_id=ssql_id or qsql_id=sprev_sql_id);

　　可借助Knowledge Xpert for Oracle Administration的Active Analysis協助分析Index

　　Indexes exist primarily to improve the performance of SQL statements In many cases establishing good indexes is the best path to optimal performance

　　Active Analysis Show indexes by owner & table

　　Active Analysis Show index statistics

　　Active Analysis Show tables without indexes

　　Active Analysis Show tables with excessive indexes

　　Active Analysis Show similar indexes

　　Active Analysis Show foreign keys missing indexes

　　Active Analysis Show partitioned indexes

　　Knowing When to Rebuild Indexes

　　三Index調優建議

　　索引設計優化

　　The way columns are indexed effect their efficiency The order columns are specified should reflect the way a select will retrieve them The first column should be the one that will be accessed most often

　　Oracle recommends that you do not explicitly define UNIQUE indexes on tables When the unique constraint is created a unique index is created to maintain it Uniqueness is strictly a logical concept and should be associated with the definition of a table As such uniqueness should be defined by using UNIQUE integrity constraints on the desired columns

　　Oracle recommends that you do not explicitly define UNIQUE indexes on tables (CREATE UNIQUE INDEX) In general it is better to create constraints to enforce uniqueness than it is to use the CREATE UNIQUE INDEX syntax A constraints associated index always assumes the name of the constraint; you cannot specify a specific name for a constraint index

　　經常一起使用多個字段檢索記錄組合索引比單索引更有效

　　把最常用的列放在最前面例dx_groupid_serv_id(groupidserv_id)在where條件中使用groupid或groupidserv_id查詢將使用索引若僅用到serv_id字段則索引無效

　　試驗(組合索引比單索引更有效)

　　create table as select * from SHSALES;

　　在table上建立一個prod_id和cust_id的組合索引

　　create table as select * from SHSALES;

　　在table上建立一個索引（prod_id）

　　set autotrace on;

　　SQL> select count(*) from table where prod_id = or cust_id = ;

　　COUNT(*)

　　Elapsed: ::

　　Execution Plan

　　 SELECT STATEMENT Optimizer=ALL_ROWS (Cost= Card= Bytes=

　　)

　　 SORT (AGGREGATE)

　　 INDEX (FAST FULL SCAN) OF IDX_PROD_ID_CUST_ID (INDEX)

　　(Cost= Card= Bytes=)

　　Statistics

　　 recursive calls

　　 db block gets

　　 consistent gets

　　 physical reads

　　 redo size

　　 bytes sent via SQL*Net to client

　　 bytes received via SQL*Net from client

　　 SQL*Net roundtrips to/from client

　　 sorts (memory)

　　 sorts (disk)

　　 rows processed

　　SQL> select count(*) from table where prod_id = or cust_id = ;

　　COUNT(*)

　　Elapsed: ::

　　Execution Plan

　　 SELECT STATEMENT Optimizer=ALL_ROWS (Cost= Card= Bytes=

　　)

　　 SORT (AGGREGATE)

　　 TABLE ACCESS (FULL) OF TABLE (TABLE) (Cost= Card=

　　 Bytes=)

　　Statistics

　　 recursive calls

　　 db block gets

　　 consistent gets

　　 physical reads

　　 redo size

　　 bytes sent via SQL*Net to client

　　 bytes received via SQL*Net from client

　　 SQL*Net roundtrips to/from client

　　 sorts (memory)

　　 sorts (disk)

　　 rows processed

　　試驗(組合索引在Where子句中的使用)

　　set autotrace on;

　　僅僅使用prod_id

　　SQL> select count(*) from table where prod_id=;

　　COUNT(*)

　　Elapsed: ::

　　Execution Plan

　　 SELECT STATEMENT Optimizer=ALL_ROWS (Cost= Card= Bytes=

　　)

　　 SORT (AGGREGATE)

　　 INDEX (RANGE SCAN) OF IDX_PROD_ID_CUST_ID (INDEX) (Cos

　　t= Card= Bytes=)

　　Statistics

　　 recursive calls

　　 db block gets

　　 consistent gets

　　 physical reads

　　 redo size

　　 bytes sent via SQL*Net to client

　　 bytes received via SQL*Net from client

　　 SQL*Net roundtrips to/from client

　　 sorts (memory)

　　 sorts (disk)

　　 rows processed

　　prod_id和cust_id一起使用

　　SQL> select count(*) from table where prod_id = and cust_id = ;

　　COUNT(*)

　　Elapsed: ::

　　Execution Plan

　　 SELECT STATEMENT Optimizer=ALL_ROWS (Cost= Card= Bytes=)

　　 SORT (AGGREGATE)

　　 INDEX (RANGE SCAN) OF IDX_PROD_ID_CUST_ID (INDEX) (Cos

　　t= Card= Bytes=)

　　Statistics

　　 recursive calls

　　 db block gets

　　 consistent gets

　　 physical reads

　　 redo size

　　 bytes sent via SQL*Net to client

　　 bytes received via SQL*Net from client

　　 SQL*Net roundtrips to/from client

　　 sorts (memory)

　　 sorts (disk)

　　 rows processed

　　僅僅使用cust_id

　　SQL> select count(*) from table where cust_id = ;

　　COUNT(*)

　　Elapsed: ::

　　Execution Plan

　　 SELECT STATEMENT Optimizer=ALL_ROWS (Cost= Card= Bytes=

　　)

　　 SORT (AGGREGATE)

　　 INDEX (FAST FULL SCAN) OF IDX_PROD_ID_CUST_ID (INDEX)

　　(Cost= Card= Bytes=)

　　Statistics

　　 recursive calls

　　 db block gets

　　 consistent gets

　　 physical reads

　　 redo size

　　 bytes sent via SQL*Net to client

　　 bytes received via SQL*Net from client

　　 SQL*Net roundtrips to/from client

　　 sorts (memory)

　　 sorts (disk)

　　 rows processed

　　Oracle索引掃描的四種類型

　　索引使用優化

　　避免意外的表掃描

　　Avoid accidental table scans

　　One of the most fundamental SQL tuning problems is the accidental table scan Accidental table scans usually occur when the SQL programmer tries to perform a search on an indexed column that cant be supported by an index This can occur when:

　　Using != (not equals to) Even if the not equals condition satisfies only a small number of rows Oracle does not use an index to satisfy such a condition Often you can recode these queries using > or IN conditions which can be supported by index lookups

　　Searching for NULLS Oracle wont use an index to find null values since null values are not usually stored in an index (the exception is a concatenated index entry where only some of the values are NULL) If youre planning to search for values that are logically missing consider changing the column to NOT NULL with a DEFAULT clause For example you could set a default value of UNKNOWN and use the index to find these values Interestingly recent versions of Oracle can index to find values that are NOT NULL if the costbased optimizer determines that such an approach is costeffective

　　Using functions on indexed columns Any function or operation on an indexed column prevents Oracle from using an index on that column For instance Oracle cant use an index to find SUBSTR(SURNAME)=SMIT Instead of manipulating the column try to manipulate the search condition In the previous example a better formulation would be SURNAME LIKE SMIT%

　　實驗（慎用!=）

　　!=使用的是Fast Full Scan

　　SQL> select count(*) from table where prod_id != ;

　　COUNT(*)

　　Elapsed: ::

　　Execution Plan

　　 SELECT STATEMENT Optimizer=ALL_ROWS (Cost= Card= Bytes=

　　)

　　 SORT (AGGREGATE)

　　 INDEX (FAST FULL SCAN) OF IDX_TABLE (INDEX) (Cost=

　　 Card= Bytes=)

　　而<>IN用的是Range Scan

　　SQL> select count(*) from table where prod_id < ;

　　COUNT(*)

　　Elapsed: ::

　　Execution Plan

　　 SELECT STATEMENT Optimizer=ALL_ROWS (Cost= Card= Bytes=

　　)

　　 SORT (AGGREGATE)

　　 INDEX (RANGE SCAN) OF IDX_TABLE (INDEX) (Cost= Ca

　　rd= Bytes=)

　　實驗（慎用函數）

　　create table emp as select * from scottemp;

　　在emp的empno和ename字段上分別建立index

　　SQL> select Count(*) from emp where substr(ename) = smit;

　　COUNT(*)

　　Elapsed: ::

　　Execution Plan

　　 SELECT STATEMENT Optimizer=ALL_ROWS (Cost= Card= Bytes=)

　　 SORT (AGGREGATE)

　　 TABLE ACCESS (FULL) OF EMP (TABLE) (Cost= Card= Byte

　　s=)

　　SQL> select Count(*) from emp where ename like smit%;

　　COUNT(*)

　　Elapsed: ::

　　Execution Plan

　　 SELECT STATEMENT Optimizer=ALL_ROWS (Cost= Card= Bytes=)

　　 SORT (AGGREGATE)

　　 INDEX (RANGE SCAN) OF IDX_EMP (INDEX)

From:http://tw.wingwit.com/Article/program/Oracle/201311/18041.html