程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> Oracle數據庫 >> Oracle數據庫基礎 >> 實用心得:Oracle中監控索引的使用

實用心得:Oracle中監控索引的使用

編輯:Oracle數據庫基礎

研究發現,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:

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 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擁有的索引除外)啟用監控:

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;

下面我們來看一下Oracle 9i 這個新特性能不能識別在進行DML操作時外鍵列上索引的使用情況:

以9i中HR模式為例:

標准的dept和emp表是一個與外鍵關聯的父子表的例子。這個例子主要想看一下,在父表上刪除一個記錄,會不會調用子表上外鍵上的索引。 首先監控HR模式下所有索引的使用,為了便於在主表上刪除一條記錄,不違反引用完整性約束。我們首先丟棄原有的約束,重新創建支持級聯刪除的約束.

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;

注意在此為了方便,我們刪除部門id為10的記錄。如果你刪除其他的部門,可能你還要更改表job_history中相關的約束。

現在我們看看索引使用的情況:

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

很明顯刪除父表上的記錄,也利用了子表中相關的索引。

v$object_usage 視圖的一個異常之處是, 它只能顯示屬於連接用戶的索引的信息。Oracle可能在將來會解決這個問題。如果您的數據庫只顯示連接用戶的對象使用信息,下面的視圖(必須被創建為SYS)可用於提供來自任何帳戶的所有被監控的索引的信息:

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、最後我們簡單的說一下,如何監控最近被使用的索引

下列查詢將列出最近被訪問的索引:

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');

這個過程可能要耗費一定時間,建議在數據庫不太繁忙的時候運行。

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved