研究發(fā)現(xiàn)，Oracle數(shù)據(jù)庫(kù)使用的索引不會(huì)超過(guò)總數(shù)的25%，或者不易他們期望被使用的方式使用。通過(guò)監(jiān)控?cái)?shù)據(jù)庫(kù)索引的使用，釋放那些未被使用的索引，從而節(jié)省維護(hù)索引的開(kāi)銷(xiāo)，優(yōu)化性能。 1、在oracle8i中，確定使用了那個(gè)索引的方法意味著要對(duì)存在語(yǔ)共享SQL區(qū)中的所有語(yǔ)句運(yùn)行EXPLIAN PALN，然后查詢(xún)計(jì)劃表中的OPERATION列，從而識(shí)別有OBJECT_OWNER和OBJECT_NAME列所確定的那個(gè)索引上的索引訪問(wèn)。 下面是一個(gè)監(jiān)控索引使用的腳本，這個(gè)腳本僅僅是一個(gè)樣品，在某種條件下成立： 條件： 運(yùn)行這個(gè)腳本的用戶(hù)擁有權(quán)限解釋所有的v$sqlarea中的sql，除了不是被SYS裝載的。 plan_table.remarks能夠別用來(lái)決定與特權(quán)習(xí)慣的錯(cuò)誤。 對(duì)所有的共享池中SQL，參數(shù)OPTIMIZER_GOAL是一個(gè)常量，無(wú)視v$sqlarea.optimizer_mode。 兩次快照之間，統(tǒng)計(jì)資料被再次分析過(guò)。 沒(méi)有語(yǔ)句別截?cái)唷?所有的對(duì)象都是局部的。 所有被引用的表或視圖或者是被運(yùn)行腳本的用戶(hù)所擁有，或者完全有資格的名字或同義詞被使用。 自從上次快照以來(lái)，沒(méi)有不受'歡迎'的語(yǔ)句被沖洗出共享池(例如，在裝載)。 對(duì)于所有的語(yǔ)句， v$sqlarea.version_count = 1 (children)。 腳本: CODE: set echo off Rem Drop and recreate PLAN_TABLE for EXPLAIN PLAN drop table plan_table; create table PLAN_TABLE ( statement_id varchar2(30), timestamp date, remarks varchar2(80), operation varchar2(30), options varchar2(255), object_nodevarchar2(128), object_owner varchar2(30), object_namevarchar2(30), object_instancenumeric, object_type varchar2(30), optimizer varchar2(255), search_columns number, id numeric, parent_id numeric, position numeric, cost numeric, cardinality numeric, bytes numeric, other_tag varchar2(255), partition_start varchar2(255), partition_stop varchar2(255), partition_idnumeric, other long, distributionvarchar2(30), cpu_cost numeric, io_cost numeric, temp_space numeric, Access_predicates varchar2(4000), filter_predicates varchar2(4000));Rem Drop and recreate SQLTEMP for taking a snapshot of the SQLAREA drop table sqltemp; create table sqltemp ( ADDR VARCHAR2 (16),SQL_TEXT VARCHAR2 (2000),DISK_READS NUMBER,EXECUTIONS NUMBER,PARSE_CALLS NUMBER); set echo on Rem Create procedure to populate the plan_table by executing Rem explain plan...for 'sqltext' dynamically create or replace procedure do_explain ( addr IN varchar2, sqltext IN varchar2) as dummy varchar2 (1100); mycursor integer; ret integer; my_sqlerrm varchar2 (85); begin dummy:='EXPLAIN PLAN SET STATEMENT_ID=' ; dummy:=dummy''''addr''''' FOR 'sqltext; mycursor := dbms_sql.open_cursor; dbms_sql.parse(mycursor,dummy,dbms_sql.v7); ret := dbms_sql.execute(mycursor); dbms_sql.close_cursor(mycursor); commit; exception -- Insert errors into PLAN_TABLE... when others then my_sqlerrm := substr(sqlerrm,1,80); insert into plan_table(statement_id,remarks) values (addr,my_sqlerrm); -- close cursor if exception raised on EXPLAIN PLAN dbms_sql.close_cursor(mycursor); end; / Rem Start EXPLAINing all S/I/U/D statements in the shared pool declare -- exclude statements with v$sqlarea.parsing_schema_id = 0 (SYS) cursor c1 is select address, sql_text, DISK_READS, EXECUTIONS, PARSE_CALLS from v$sqlarea where command_type in (2,3,6,7) and parsing_schema_id != 0; cursor c2 is select addr, sql_text from sqltemp; addr2 varchar(16); sqltext v$sqlarea.sql_text%type; dreads v$sqlarea.disk_reads%type; execs v$sqlarea.executions%type; pcalls v$sqlarea.parse_calls%type; begin open c1; fetch c1 into addr2,sqltext,dreads,execs,pcalls; while (c1%found) loop insert into sqltemp values(addr2,sqltext,dreads,execs,pcalls); commit; fetch c1 into addr2,sqltext,dreads,execs,pcalls; end loop; close c1; open c2; fetch c2 into addr2, sqltext; while (c2%found) loop do_explain(addr2,sqltext); fetch c2 into addr2, sqltext; end loop; close c2; end; / Rem Generate a report of index usage based on the number of times Rem a SQL statement using that index was executed select p.owner, p.name, sum(s.executions) totexec from sqltemp s, (select distinct statement_id stid, object_owner owner, object_name name from plan_table where operation = 'INDEX') p where s.addr = p.stid group by p.owner, p.name order by 2 desc; Rem Perform cleanup on exit (optional) deletefrom plan_table wherestatement_id in (selectaddrfromsqltemp); drop table sqltemp;關(guān)于這個(gè)腳本，有幾個(gè)重要的地方需要注重，即它可能一起明顯的開(kāi)銷(xiāo)，因此，應(yīng)該在仔細(xì)地進(jìn)行 權(quán)衡后才把它應(yīng)用到繁忙的生產(chǎn)應(yīng)用系統(tǒng)中區(qū)。2、 oracle9i中如何確定索引的使用情況 在oracle9i中，情況會(huì)簡(jiǎn)單得多，因?yàn)橛幸粋€(gè)新得字典視圖V$SQL_PLAN存儲(chǔ)了實(shí)際計(jì)劃，這些計(jì)劃用于執(zhí)行共享SQL區(qū)中得語(yǔ)句。V$SQL_PLAN視圖很類(lèi)似與計(jì)劃表，但V$SQL_PLAN使用ADDRESS和HASH_VALUE列 來(lái)識(shí)別語(yǔ)句， 而計(jì)劃表使用用戶(hù)提供得STATEMENT_ID來(lái)識(shí)別語(yǔ)句。下面的SQL顯示了在一個(gè)oracle9i數(shù)據(jù)庫(kù)中，由出現(xiàn)在共享SQL區(qū)中語(yǔ)句使用的所有索引。 select object_owner, object_name, options, count(*) from v$sql_plan where operation='INDEX' and object_owner!='SYS' group by object_owner, object_name, operation, options order by count(*) desc; 所有基于共享SQL區(qū)中的信心來(lái)識(shí)別索引使用情況的方法， 都可能會(huì)收集到不完整的信息。共享SQL區(qū)是一 個(gè)動(dòng)態(tài)結(jié)構(gòu)，除非能對(duì)它進(jìn)行足夠頻繁的采樣， 否則在有關(guān)索引使用的情況的信息被收集之前，SQL語(yǔ)句可 能就已經(jīng)(因?yàn)槔匣?被移出緩存了。oracle9i提供了解決這個(gè)問(wèn)題的方案，即它為alter index提供了一個(gè)monitoring usage子句。當(dāng)啟用monitoring usage 時(shí)，oralce記錄簡(jiǎn)單的yes或no值，以指出在監(jiān)控間隔 期間某個(gè)索引是否被使用。 為了演示這個(gè)新特性，你可以使用下面的例子: (a) Create and populate a small test table (b) Create Primary Key index on that table (c) Query v$object_usage: the monitoring has not started yet (d) Start monitoring of the index usage (e) Query v$object_usage to see the monitoring in progress (f) Issue the SELECT statement which uses the index (g) Query v$object_usage again to see that the index has been used (h) Stop monitoring of the index usage (i) Query v$object_usage to see that the monitoring sDetailed steps: (a) Create and populate a small test table create table prodUCts ( prod_id number(3), prod_name_code varchar2(5)); insert into products values(1,'aaaaa'); insert into products values(2,'bbbbb'); insert into products values(3,'ccccc'); insert into products values(4,'ddddd'); commit; (b) Create Primary Key index on that table alter table products add (constraint products_pk primary key (prod_id)); (c) Query v$object_usage: the monitoring has not started yet column index_name format a12 column monitoring format a10 column used format a4 column start_monitoring format a19 column end_monitoring format a19 select index_name,monitoring,used,start_monitoring,end_monitoring from v$object_usage; no rows selected (d) Start monitoring of the index usage alter index products_pk monitoring usage; Index altered. (e) Query v$object_usage to see the monitoring in progress select index_name,monitoring,used,start_monitoring,end_monitoring from v$object_usage; INDEX_NAME MONITORING USED START_MONITORING END_MONITORING --------------------------------------------------------------- PRODUCTS_PK YES NO 04/25/2001 15:43:13 Note: Column MONITORING='YES', START_MONITORING gives the timestamp. (f) Issue the SELECT statement which uses the index First, make sure that index will be used for this statement. Create plan_table in your schema, as required by Oracle Autotrace utility: @$ORACLE_HOME/rdbms/admin/utlxplan Table created. Use Oracle Autotrace utility to oBTain the execution plan: set autotrace on explain select * from products where prod_id = 2; Execution Plan ------------------------------------------------------ 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'PRODUCTS' 2 1 INDEX (UNIQUE SCAN) OF 'PRODUCTS_PK' (UNIQUE) set autotrace off Now, since you know the index will be used for this query, issue the actual SELECT statement: select * from products where prod_id = 2; PROD_ID PROD_ ---------- ----- 2 bbbbb (g) Query v$object_usage again to see that the index has been used select index_name,monitoring,used,start_monitoring,end_monitoring from v$object_usage; INDEX_NAME MONITORING USED START_MONITORING END_MONITORING ------------ ---------- ---- ------------------- ---- ------------ PRODUCTS_PK YES YES 04/25/2001 15:43:13 Note: Column USED='YES'. (h) Stop monitoring of the index usage alter index products_pk nomonitoring usage; Index altered. (i) Query v$object_usage to see that the monitoring stopped select index_name,monitoring,used,start_monitoring,end_monitoring from v$object_usage; INDEX_NAME MONITORING USED START_MONITORING END_MONITORING ------------ ---------- ---- ------------------- ------------------- PRODUCTS_PK NO YES 04/25/2001 15:43:13 04/25/2001 15:48:44 Note: Column MONITORING='NO', END_MONITORING gives the timestamp. 下面的PL/SQL塊對(duì)數(shù)據(jù)庫(kù)中的所有索引(SYS和SYSTEM擁有的索引除外)啟用監(jiān)控： declare l_sql varchar2(128); begin for rec in (select 'alter index 'owner.'.'index_name' monitoring usage' mon from dba_indexes where owner not in ('SYS', 'SYSTEM') and index_type='NORMAL') loop l_sql:=rec.mon; execute immediate l_sql; end loop; end; 下面我們來(lái)看一下Oracle 9i 這個(gè)新特性能不能識(shí)別在進(jìn)行DML操作時(shí)外鍵列上索引的使用情況： 以9i中HR模式為例： 標(biāo)準(zhǔn)的dept和emp表是一個(gè)與外鍵關(guān)聯(lián)的父子表的例子。這個(gè)例子主要想看一下，在父表上刪除一個(gè)記錄，會(huì)不會(huì)調(diào)用子表上外鍵上的索引。 首先監(jiān)控HR模式下所有索引的使用，為了便于在主表上刪除一條記錄，不違反引用完整性約束。我們首先丟棄原有的約束，重新創(chuàng)建支持級(jí)聯(lián)刪除的約束. alter table employees add constraint emp_dept_fk foreign key (department_id) references departments on delete cascade;alter table job_history drop constraint jhist_emp_fk;alter table job_history add constraint jhist_emp_fk foreign key(employee_id) references employees on delete cascade;delete from departments where department_id=10;注重在此為了方便，我們刪除部門(mén)id為10的記錄。假如你刪除其他的部門(mén)，可能你還要更改表job_history中相關(guān)的約束。 現(xiàn)在我們看看索引使用的情況： select index_name, table_name, monitoring, used from v$object_usage where used='YES' INDEX_NAME TABLE_NAME MON USE ------------------------------ -------------------- --- --- DEPT_ID_PK DEPARTMENTSYES YES EMP_EMP_ID_PKEMPLOYEES YES YES EMP_DEPT_FKEMPLOYEES YES YES很明顯刪除父表上的記錄，也利用了子表中相關(guān)的索引。 v$object_usage 視圖的一個(gè)異常之處是， 它只能顯示屬于連接用戶(hù)的索引的信息。Oracle可能在將來(lái)會(huì)解決這個(gè)問(wèn)題。假如您的數(shù)據(jù)庫(kù)只顯示連接用戶(hù)的對(duì)象使用信息，下面的視圖(必須被創(chuàng)建為SYS)可用于提供來(lái)自任何帳戶(hù)的所有被監(jiān)控的索引的信息： create or replace view V$ALL_OBJECT_USAGE(INDEX_NAME, TABLE_NAME, MONITORING, USED, START_MONITORING, END_MONITORING) as select io.name, t.name, decode(bitand(i.flags, 65536),0,'NO','YES'), decode(bitand(ou.flags,1),0,'NO','YES'), ou.start_monitoring, ou.end_monitoring from sys.obj$ io, sys.obj$ t, sys.ind$ i, sys.object_usage ou where i.obj#=ou.obj# and io.obj#=ou.obj# and t.obj#=i.bo#;grant select on v$all_object_usage to public;create public synonym v$all_object_usage for v$all_object_usage;3、最后我們簡(jiǎn)單的說(shuō)一下，如何監(jiān)控最近被使用的索引 下列查詢(xún)將列出最近被訪問(wèn)的索引： column owner format a20 trunc column segment_name format a30 trunc select distinct b.owner, b.segment_namefrom x$bh a, dba_extents bwhere b.file_id=a.dbafil anda.dbablk between b.block_id and b.block_id+blocks-1 andsegment_type='INDEX' andb.owner not in ('SYS','SYSTEM');這個(gè)過(guò)程可能要耗費(fèi)一定時(shí)間，建議在數(shù)據(jù)庫(kù)不太繁忙的時(shí)候運(yùn)行。