一实验
create table s as select * from SHSALES;
create table s as select * from SHSALES;
s表没有建立索引
s表有建立索引
set timing on;
select * from s where prod_id=;
s
select * from s where prod_id=;
s
可见索引对于表查询速度的重要性
二索引性能测试与诊断
查看数据库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;
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=;
s
update table set cust_id= where prod_id=;
s
找出全表扫描（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 All rights reserved
REM
REM************ Knowledge Xpert for Oracle Administration *************
DROP VIEW full_table_scans;
CREATE VIEW full_table_scans
AS
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)
