如何監控索引的使用？
研究發現，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區中語句使用的所有索引
[/code]
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;
[/code]
所有基于共享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 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塊對數據庫中的所有索引(SYS和SYSTEM擁有的索引除外)啟用監控：
[/code]
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;
/
[/code]
下面我們來看一下Oracle 9i 這個新特性能不能識別在進行DML操作時外鍵列上索引的使用情況：
以9i中HR模式為例：
標準的dept和emp表是一個與外鍵關聯的父子表的例子。這個例子主要想看一下，在父表上刪除一個記錄，會不會調用子表上外鍵上的索引。 首先監控HR模式下所有索引的使用，為了便于在主表上刪除一條記錄，不違反引用完整性約束。我們首先丟棄原有的約束，重新創建支持級聯刪除的約束.
[/code]
alter table employees drop constraint emp_dept_fk;
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;
[/code]
注重在此為了方便，我們刪除部門id為10的記錄。假如你刪除其他的部門，可能你還要更改表job_history中相關的約束。
現在我們看看索引使用的情況：
[/code]
select index_name, table_name, monitoring, used
from v$object_usage
where used='YES'
INDEX_NAME TABLE_NAME MON USE
------------------------------ -------------------- --- ---
DEPT_ID_PK DEPARTMENTS YES YES
EMP_EMP_ID_PK EMPLOYEES YES YES
EMP_DEPT_FK EMPLOYEES YES YES
[/code]
很明顯刪除父表上的記錄，也利用了子表中相關的索引。
v$object_usage 視圖的一個異常之處是， 它只能顯示屬于連接用戶的索引的信息。Oracle可能在將來會解決這個問題。假如您的數據庫只顯示連接用戶的對象使用信息，下面的視圖(必須被創建為SYS)可用于提供來自任何帳戶的所有被監控的索引的信息：
[/code]
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;
[/code]
3、最后我們簡單的說一下，如何監控最近被使用的索引
下列查詢將列出最近被訪問的索引：
[/code]
column owner format a20 trunc
column segment_name format a30 trunc
select distinct b.owner, b.segment_name
from x$bh a, dba_extents b
where b.file_id=a.dbafil
and a.dbablk between b.block_id and b.block_id+blocks-1
and segment_type='INDEX'
and b.owner not in ('SYS','SYSTEM');
[/code]
這個過程可能要耗費一定時間，建議在數據庫不太繁忙的時候運行。
to be continued... 2004-05-28
假如大家覺得瀏覽不方便， 我可以上傳文本文件。
[ Last edited by amaome on 2004-5-28 at 02:59 PM ]
