我们都知道索引监控,但是索引监控只能知道监控的索引是否被使用,并不能得到索引使用率。其实我们可以利用执行计划来得到索引使用率,只是,如果语句使用了绑定变量,执行了语句多次,都的的确确使用了索引,但是因为执行计划是一致的,且一直放在库缓存中,因此统计的索引使用次数为1次。使用该方法是通过v$sql_plan中获取索引使用率。
在10g之后,可以利用awr来获取。视图为dba_hist_sql_plan,使用方法和v$sql_plan一样。awr保存的执行计划中,并不是每条语句都会保存,所以做测试的时候难免出问题, 因为awr默认是保存topsql语句靠前的,如果做测试,可以调整下参数
exec dbms_workload_repository.modify_snapshot_settings(topnsql=>'MAXIMUM');
----后面,我会贴从执行计划中获取索引使用率的脚本
----这里先用我自己的方法去得到索引使用率和使用时间:
思路:开启索引监控后,使用索引一次后就会修改 USED 列为 YES ,而再次开启索引监控后,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 as
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 1
------当然利用监控索引的方法会有一定开销,所以如果不是一定要统计重要表的索引使用次数、使用时间,不必这样来操作
下面的从执行计划中获取的统计记录:
reference 小麦苗: http://blog.itpub.net/26736162/viewspace-2120752/
(1) 从v$sql_plan中获取
WITH Q AS (
SELECT
S.OWNER A_OWNER,
INDEX_NAME A_INDEX_NAME,
INDEX_TYPE A_INDEX_TYPE,
SUM(S.bytes) / 1048576 A_MB
FROM DBA_SEGMENTS S,
DBA_INDEXES I
WHERE
INDEX_NAME = SEGMENT_NAME
and s.owner in (select username from dba_users where account_status = 'OPEN')
and s.owner NOT IN ('SYS', 'SYSTEM', 'PERFSTAT', 'MGMT_VIEW', 'MONITOR', 'SYSMAN', 'DBSNMP')
and s.owner not like '%SYS%'
GROUP BY S.OWNER, TABLE_NAME, INDEX_NAME, INDEX_TYPE
-- HAVING SUM(S.BYTES) > 1048576
)
SELECT /*+ NO_QUERY_TRANSFORMATION(S) */
A_OWNER OWNER,
A_INDEX_NAME INDEX_NAME,
A_INDEX_TYPE INDEX_TYPE,
A_MB MB,
DECODE (OPTIONS, null, ' -',OPTIONS) INDEX_OPERATION,
COUNT(OPERATION) NR_EXEC
FROM Q,
v$sql_plan d
WHERE
D.OBJECT_OWNER(+)= q.A_OWNER AND
D.OBJECT_NAME(+) = q.A_INDEX_NAME
GROUP BY
A_OWNER,
A_INDEX_NAME,
A_INDEX_TYPE,
A_MB,
DECODE (OPTIONS, null, ' -',OPTIONS)
ORDER BY
A_OWNER,
A_INDEX_NAME,
A_INDEX_TYPE,
A_MB DESC,
NR_EXEC DESC ;
(2)从awr视图dba_hist_sql_plan中获取
WITH Q AS (
SELECT
S.OWNER A_OWNER,
INDEX_NAME A_INDEX_NAME,
INDEX_TYPE A_INDEX_TYPE,
SUM(S.bytes) / 1048576 A_MB
FROM DBA_SEGMENTS S,
DBA_INDEXES I
WHERE
INDEX_NAME = SEGMENT_NAME
and s.owner in (select username from dba_users where account_status = 'OPEN')
and s.owner NOT IN ('SYS', 'SYSTEM', 'PERFSTAT', 'MGMT_VIEW', 'MONITOR', 'SYSMAN', 'DBSNMP')
and s.owner not like '%SYS%'
GROUP BY S.OWNER, TABLE_NAME, INDEX_NAME, INDEX_TYPE
-- HAVING SUM(S.BYTES) > 1048576
)
SELECT /*+ NO_QUERY_TRANSFORMATION(S) */
A_OWNER OWNER,
A_INDEX_NAME INDEX_NAME,
A_INDEX_TYPE INDEX_TYPE,
A_MB MB,
DECODE (OPTIONS, null, ' -',OPTIONS) INDEX_OPERATION,
COUNT(OPERATION) NR_EXEC
FROM Q,
DBA_HIST_SQL_PLAN d
WHERE
D.OBJECT_OWNER(+)= q.A_OWNER AND
D.OBJECT_NAME(+) = q.A_INDEX_NAME
GROUP BY
A_OWNER,
A_INDEX_NAME,
A_INDEX_TYPE,
A_MB,
DECODE (OPTIONS, null, ' -',OPTIONS)
ORDER BY
A_OWNER,
A_INDEX_NAME,
A_INDEX_TYPE,
A_MB DESC,
NR_EXEC DESC;
----查看AWR保留的时间段
select to_char (min(BEGIN_INTERVAL_TIME), 'yyyy-mm-dd hh24:mi:ss')
|| ' ---- ' ||
to_char (max(END_INTERVAL_TIME), 'yyyy-mm-dd hh24:mi:ss')
from dba_hist_snapshot;
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/30130773/viewspace-2123715/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/30130773/viewspace-2123715/