oracle统计表的使用次数,监控索引使用次数及使用时间

最新推荐文章于 2023-09-28 11:35:43 发布

weixin_39824020

最新推荐文章于 2023-09-28 11:35:43 发布

阅读量1.6k

点赞数

文章标签： oracle统计表的使用次数

，而再次开启索引监控后，USED列并又会变为NO，因此脚本就是利用该特性，创建一张记录表，用于记录索引使用的，然后重新开启索引监控。

----1.开启索引监控

SELECT 'ALTER INDEX ' || owner || '.' ||

index_name || ' MONITORING USAGE;' enable_monitor,

'ALTER INDEX ' || owner || '.' || index_name ||

' NOMONITORING USAGE;' disable_monitor

FROM

dba_indexes

WHERE

INDEX_TYPE != 'LOB'

and

owner IN

(SELECT username FROM dba_users WHERE account_status = 'OPEN')

AND

owner NOT IN ('SYS',

'SYSTEM',

'PERFSTAT',

'MGMT_VIEW',

'MONITOR',

'SYSMAN',

'DBSNMP')

AND

owner not like '%SYS%';

----为什么要这样来创建，可以找找v$object_usage该视图是怎么创建的

=============》

ALTER INDEX SCOTT.SHALL_ID_ZHONG MONITORING

USAGE;

ALTER INDEX SCOTT.PK_EMP MONITORING USAGE;

ALTER INDEX SCOTT.PK_DEPT MONITORING USAGE;

ALTER INDEX HR.EMP_EMP_ID_PK MONITORING USAGE;

----2.查看索引监控情况

select * from v$object_usage;

select * from (select z.name||'.'||io.name

index_named, t.name table_named,

decode(bitand(i.flags, 65536), 0, 'NO', 'YES') monitord,

decode(bitand(ou.flags, 1), 0, 'NO', 'YES') used,

ou.start_monitoring,

ou.end_monitoring

from

sys.obj$ io, sys.obj$ t, sys.ind$ i, sys.object_usage ou ,sys.user$ z

Where i.obj# = ou.obj#

and

io.obj# = ou.obj# And io.owner#=z.user#

and

t.obj# = i.bo# )

Order

By 4 Desc,2 Desc;

----3.创建记录索引使用表

create table db_moniter_index as (

select z.name user_named,io.name

index_named, t.name table_named,

decode(bitand(i.flags, 65536), 0, 'NO', 'YES') monitord,

decode(bitand(ou.flags, 1), 0, 'NO', 'YES') used,

ou.start_monitoring,

ou.end_monitoring

from

sys.obj$ io, sys.obj$ t, sys.ind$ i, sys.object_usage ou ,sys.user$ z

Where i.obj# = ou.obj#

and

io.obj# = ou.obj# And io.owner#=z.user#

and

t.obj# = i.bo# and 1=2);

----添加一个字段，记录日期

alter table db_moniter_index add use_date date

default sysdate;

select * from db_moniter_index;

----4.创建存储过程

----用于统计索引时间

create or replace procedure p_moniter_index

begin

insert

into sys.db_moniter_index

select

z.name,

io.name index_named,

t.name table_named,

decode(bitand(i.flags, 65536), 0, 'NO', 'YES') monitord,

decode(bitand(ou.flags, 1), 0, 'NO', 'YES') used,

ou.start_monitoring,

ou.end_monitoring,

sysdate

from sys.obj$ io,

sys.obj$ t,

sys.ind$ i,

sys.object_usage ou,

sys.user$ z

Where

i.obj# = ou.obj#

and io.obj# = ou.obj#

And io.owner# = z.user#

and t.obj# = i.bo#

and bitand(i.flags, 65536) <> 0

and bitand(ou.flags, 1) <> 0;

--and

z.name='监控用户';

commit;

for

v_sql in (select z.name || '.' || io.name aa

from sys.obj$ io,

sys.obj$ t,

sys.ind$ i,

sys.object_usage ou,

sys.user$ z

where i.obj# = ou.obj#

and io.obj# = ou.obj#

And io.owner# = z.user#

and t.obj# = i.bo#

and ou.flags = 1) loop

-- DBMS_Utility.Exec_DDL_Statement(v_sql.aa);

execute

immediate 'alter index ' || v_sql.aa || ' monitoring usage';

end

loop;

end;

----5.设置job为每5分钟监控一次记录

var job1 number

begin

dbms_job.submit(:job1,'p_moniter_index;',sysdate,'sysdate+5/1440');

end;

----手动执行一次job

exec dbms_job.run(:job1);

select * from dba_jobs; ----找到刚刚执行的job号为63

----6.开始测试

select * from db_moniter_index;

select * from v$object_usage;

select * from (select z.name||'.'||io.name

index_named, t.name table_named,

decode(bitand(i.flags, 65536), 0, 'NO', 'YES') monitord,

decode(bitand(ou.flags, 1), 0, 'NO', 'YES') used,

ou.start_monitoring,

ou.end_monitoring

from

sys.obj$ io, sys.obj$ t, sys.ind$ i, sys.object_usage ou ,sys.user$ z

Where i.obj# = ou.obj#

and

io.obj# = ou.obj# And io.owner#=z.user#

and

t.obj# = i.bo# )

Order

By 4 Desc,2 Desc;

----使用索引：

select count(id) from scott.shall;

select count(*) from scott.dept;

select employee_id from hr.employees where

employee_id=100;

----等job执行，或者手动触发job执行

begin

dbms_job.run(63);

end;

----运行job后，再次执行语句

select count(id) from scott.shall;

select count(*) from scott.dept;

select * from hr.employees;

----再次调用job

begin

dbms_job.run(63);

end;

----再次执行语句

select * from scott.shall;

select count(*) from scott.dept;

select * from hr.employees;

----再次调用job

begin

dbms_job.run(63);

end;

----最后查看下索引使用次数

select user_named,index_named,table_named,count(*)

from db_moniter_index group by user_named,index_named,table_named;

SCOTT

PK_DEPT DEPT 3

SCOTT

SHALL_ID_ZHONG SHALL 2

HR EMP_EMP_ID_PK EMPLOYEES

------当然利用监控索引的方法会有一定开销，所以如果不是一定要统计重要表的索引使用次数、使用时间，不必这样来操作

下面的从执行计划中获取的统计记录：

reference