研究發現，Oracle數據庫使用的索引不會超過總數的25%，或者不易他們期望被使用的方式使用。通過 監控數據庫索引的使用，釋放那些未被使用的索引，從而節省維護索引的開銷，優化性能。 1、在oracle8i中，確定使用了那個索引的方法意味著要對存在語共享SQL區中的所有語句運行EXPLIAN PALN，然后查詢計劃表中的OperaTION列，從而識別有OBJECT_OWNER和OBJECT_NAME列所確定的那個索引上的索引訪問。 下面是一個監控索引使用的腳本，這個腳本僅僅是一個樣品，在某種條件下成立： 條件： 運行這個腳本的用戶擁有權限解釋所有的v$sqlarea中的sql，除了不是被SYS裝載的。plan_table.remarks能夠別用來決定與特權習慣的錯誤。對所有的共享池中SQL，參數OPTIMIZER_GOAL是一個常量，無視v$sqlarea.optimizer_mode。兩次快照之間，統計資料被再次分析過。沒有語句別截斷。所有的對象都是局部的。所有被引用的表或視圖或者是被運行腳本的用戶所擁有，或者完全有資格的名字或同義詞被使用。自從上次快照以來，沒有不受"歡迎"的語句被沖洗出共享池(例如，在裝載)。對于所有的語句，v$sqlarea.version_count = 1 (children)。 腳本: Code: [Copy to clipboard] 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_node varchar2(128), object_owner varchar2(30), object_name varchar2(30), object_instance numeric, 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_id numeric, other long, distribution varchar2(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) delete from plan_table where statement_id in ( select addr from sqltemp ); drop table sqltemp;
關于這個腳本，有幾個重要的地方需要注重，即它可能一起明顯的開銷，因此，應該在仔細地進行 權衡后才把它應用到繁忙的生產應用系統中區。 2、oracle9i中如何確定索引的使用情況: 在oracle9i中，情況會簡單得多，因為有一個新得字典視圖V$SQL_PLAN存儲了實際計劃，這些計劃用于執行共享SQL區中得語句。V$SQL_PLAN視圖很類似與計劃表，但V$SQL_PLAN使用ADDRESS和HASH_VALUE列 來識別語句， 而計劃表使用用戶提供得STATEMENT_ID來識別語句。下面的SQL顯示了在一個oracle9i數據庫中，由出現在共享SQL區中語句使用的所有索引。 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區中的信心來識別索引使用情況的方法， 都可能會收集到不完整的信息。共享SQL區是一 個動態結構，除非能對它進行足夠頻繁的采樣， 否則在有關索引使用的情況的信息被收集之前，SQL語句可 能就已經(因為老化)被移出緩存了。oracle9i提供了解決這個問題的方案，即它為alter index提供了一個monitoring usage子句。當啟用monitoring usage 時，oralce記錄簡單的yes或no值，以指出在監控間隔 期間某個索引是否被使用。 為了演示這個新特性，你可以使用下面的例子: (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
