熱點推薦:
您现在的位置: 電腦知識網 >> 編程 >> Oracle >> 正文

Oracle索引問題診斷與優化

2013-11-13 16:15:22  來源: Oracle 

  一實驗

  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)


From:http://tw.wingwit.com/Article/program/Oracle/201311/18041.html
    推薦文章
    Copyright © 2005-2013 電腦知識網 Computer Knowledge   All rights reserved.