分享
 
 
 

如何监控oracle索引的使用详细说明

王朝oracle·作者佚名  2008-05-31
窄屏简体版  字體: |||超大  

如何监控索引的使用?

研究发现,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),

timestampdate,

remarksvarchar2(80),

operationvarchar2(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,

idnumeric,

parent_idnumeric,

positionnumeric,

costnumeric,

cardinalitynumeric,

bytesnumeric,

other_tagvarchar2(255),

partition_start varchar2(255),

partition_stopvarchar2(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 SQLAREA

drop table sqltemp;

create table sqltemp (

ADDR VARCHAR2 (16),

SQL_TEXT VARCHAR2 (2000),

DISK_READSNUMBER,

EXECUTIONSNUMBER,

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;

endloop;

closec1;

openc2;

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

( selectaddrfrom 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

whereoperation='INDEX'

andobject_owner!='SYS'

groupbyobject_owner, object_name, operation, options

orderby 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 productsadd (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_MONITORINGEND_MONITORING

---------------------------------------------------------------

PRODUCTS_PKYESNO 04/25/2001 15:43:13

Note: Column MONITORING='YES', START_MONITORING gives the timestamp.

(f) Issue the SELECT statement which uses the indexFirst, 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

10TABLE ACCESS (BY INDEX ROWID) OF 'PRODUCTS'

21INDEX (UNIQUE SCAN) OF 'PRODUCTS_PK' (UNIQUE)

set autotrace offNow, 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_

---------- -----

2bbbbb

(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_MONITORINGEND_MONITORING

------------ ---------- ---- ------------------- ---- ------------

PRODUCTS_PKYESYES04/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_MONITORINGEND_MONITORING

------------ ---------- ---- ------------------- -------------------

PRODUCTS_PKNO YES04/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_sqlvarchar2(128);

begin

for recin

(select 'alter index'owner.'.'index_name' monitoring usage' mon

from dba_indexes

whereowner not in ('SYS', 'SYSTEM')

andindex_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 DEPARTMENTSYES YES

EMP_EMP_ID_PKEMPLOYEESYES YES

EMP_DEPT_FKEMPLOYEES YESYES

[/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

fromsys.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

anda.dbablk between b.block_id and b.block_id+blocks-1

andsegment_type='INDEX'

andb.owner not in ('SYS','SYSTEM');

[/code]

这个过程可能要耗费一定时间,建议在数据库不太繁忙的时候运行。

to be continued... 2004-05-28

假如大家觉得浏览不方便, 我可以上传文本文件。

[ Last edited by amaome on 2004-5-28 at 02:59 PM ]

 
 
 
免责声明:本文为网络用户发布,其观点仅代表作者个人观点,与本站无关,本站仅提供信息存储服务。文中陈述内容未经本站证实,其真实性、完整性、及时性本站不作任何保证或承诺,请读者仅作参考,并请自行核实相关内容。
2023年上半年GDP全球前十五强
 百态   2023-10-24
美众议院议长启动对拜登的弹劾调查
 百态   2023-09-13
上海、济南、武汉等多地出现不明坠落物
 探索   2023-09-06
印度或要将国名改为“巴拉特”
 百态   2023-09-06
男子为女友送行,买票不登机被捕
 百态   2023-08-20
手机地震预警功能怎么开?
 干货   2023-08-06
女子4年卖2套房花700多万做美容:不但没变美脸,面部还出现变形
 百态   2023-08-04
住户一楼被水淹 还冲来8头猪
 百态   2023-07-31
女子体内爬出大量瓜子状活虫
 百态   2023-07-25
地球连续35年收到神秘规律性信号,网友:不要回答!
 探索   2023-07-21
全球镓价格本周大涨27%
 探索   2023-07-09
钱都流向了那些不缺钱的人,苦都留给了能吃苦的人
 探索   2023-07-02
倩女手游刀客魅者强控制(强混乱强眩晕强睡眠)和对应控制抗性的关系
 百态   2020-08-20
美国5月9日最新疫情:美国确诊人数突破131万
 百态   2020-05-09
荷兰政府宣布将集体辞职
 干货   2020-04-30
倩女幽魂手游师徒任务情义春秋猜成语答案逍遥观:鹏程万里
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案神机营:射石饮羽
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案昆仑山:拔刀相助
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案天工阁:鬼斧神工
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案丝路古道:单枪匹马
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:与虎谋皮
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:李代桃僵
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:指鹿为马
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案金陵:小鸟依人
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案金陵:千金买邻
 干货   2019-11-12
 
推荐阅读
 
 
 
>>返回首頁<<
 
靜靜地坐在廢墟上,四周的荒凉一望無際,忽然覺得,淒涼也很美
© 2005- 王朝網路 版權所有