研究發(fā)現(xiàn)，Oracle數(shù)據(jù)庫(kù)使用的索引不會(huì)超過總數(shù)的25%，或者不易他們期望被使用的方式使用。通過 監(jiān)控?cái)?shù)據(jù)庫(kù)索引的使用，釋放那些未被使用的索引，從而節(jié)省維護(hù)索引的開銷，優(yōu)化性能。 1、在oracle8i中，確定使用了那個(gè)索引的方法意味著要對(duì)存在語(yǔ)共享SQL區(qū)中的所有語(yǔ)句運(yùn)行EXPLIAN PALN，然后查詢計(jì)劃表中的OPERATION列，從而識(shí)別有OBJECT_OWNER和OBJECT_NAME列所確定的那個(gè)索引上的索引訪問。 下面是一個(gè)監(jiān)控索引使用的腳本，這個(gè)腳本僅僅是一個(gè)樣品，在某種條件下成立： 條件： 運(yùn)行這個(gè)腳本的用戶擁有權(quán)限解釋所有的v$sqlarea中的sql，除了不是被SYS裝載的。plan_table.remarks能夠別用來決定與特權(quán)習(xí)慣的錯(cuò)誤。對(duì)所有的共享池中SQL，參數(shù)OPTIMIZER_GOAL是一個(gè)常量，無(wú)視v$sqlarea.optimizer_mode。兩次快照之間，統(tǒng)計(jì)資料被再次分析過。沒有語(yǔ)句別截?cái)唷Ｋ械膶?duì)象都是局部的。所有被引用的表或視圖或者是被運(yùn)行腳本的用戶所擁有，或者完全有資格的名字或同義詞被使用。自從上次快照以來，沒有不受'歡迎'的語(yǔ)句被沖洗出共享池(例如，在裝載)。對(duì)于所有的語(yǔ)句，v$sqlarea.version_count = 1 (children)。 腳本: Code: [Copy to clipboard]set echo offRem Drop and recreate PLAN_TABLE for EXPLAIN PLANdrop table plan_table;create table PLAN_TABLE (statement_id varchar2(30),timestampdate,remarks　varchar2(80),operationvarchar2(30),options　 varchar2(255),object_node　varchar2(128),object_owner varchar2(30),object_name　varchar2(30),object_instancenumeric,object_type varchar2(30),optimizer　 varchar2(255),search_columns number,idnumeric,parent_idnumeric,positionnumeric,costnumeric,cardinalitynumeric,bytesnumeric,other_tagvarchar2(255),partition_start varchar2(255),partition_stop　varchar2(255),partition_idnumeric,otherlong,distributionvarchar2(30),cpu_costnumeric,io_costnumeric,temp_spacenumeric,Access_predicates　 varchar2(4000),filter_predicates　 varchar2(4000));Rem Drop and recreate SQLTEMP for taking a snapshot of the SQLAREAdrop table sqltemp;create table sqltemp　 (ADDR VARCHAR2 (16),SQL_TEXT VARCHAR2 (2000),DISK_READSNUMBER,EXECUTIONSNUMBER,PARSE_CALLS NUMBER);set echo onRem Create procedure to populate the plan_table by executingRem explain plan...for 'sqltext' dynamicallycreate 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 PLANdbms_sql.close_cursor(mycursor);end;/Rem Start EXPLAINing all S/I/U/D statements in the shared pooldeclare-- exclude statements with v$sqlarea.parsing_schema_id = 0 (SYS)cursor c1 is select address, sql_text, DISK_READS, EXECUTIONS, PARSE_CALLSfrom v$sqlareawhere command_type in (2,3,6,7)and parsing_schema_id != 0;cursor c2 is select addr, sql_text from sqltemp;addr2　varchar(16);sqltextv$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) loopinsert into sqltemp values (addr2,sqltext,dreads,execs,pcalls);commit;fetch c1 into addr2, sqltext,dreads,execs,pcalls;endloop;close　c1;open　c2;fetch c2 into addr2, sqltext;while (c2%found) loopdo_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 timesRem a SQL statement using that index was executedselect p.owner, p.name, sum(s.executions) totexecfrom sqltemp s,(select distinct statement_id stid, object_owner owner, object_name namefrom plan_table where operation = 'INDEX') pwhere s.addr = p.stidgroup by p.owner, p.nameorder by 2 desc;Rem Perform cleanup on exit (optional)delete　from plan_tablewhere　statement_id in( select　addr　from sqltemp );drop table sqltemp;關(guān)于這個(gè)腳本，有幾個(gè)重要的地方需要注重，即它可能一起明顯的開銷，因此，應(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視圖很類似與計(jì)劃表，但V$SQL_PLAN使用ADDRESS和HASH_VALUE列 來識(shí)別語(yǔ)句， 而計(jì)劃表使用用戶提供得STATEMENT_ID來識(shí)別語(yǔ)句。下面的SQL顯示了在一個(gè)oracle9i數(shù)據(jù)庫(kù)中，由出現(xiàn)在共享SQL區(qū)中語(yǔ)句使用的所有索引。 select object_owner, object_name, options, count(*)from　 v$sql_planwhere　operation='INDEX'andobject_owner!='SYS'group　by　object_owner, object_name, operation, optionsorder　by count(*) desc;所有基于共享SQL區(qū)中的信心來識(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è)問題的方案，即它為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 tablecreate 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 tablealter table productsadd (constraint products_pk primary key (prod_id));(c) Query v$object_usage: the monitoring has not started yetcolumn