oracle删除固化的执行计划及索引监控

 

第一步先查询

select sql_id, address, hash_value, executions, loads, parse_calls, invalidations from v$sqlarea

  where sql_text = 'select 2 from dual';  

 

 

SQL_ID        ADDRESS          HASH_VALUE EXECUTIONS      LOADS PARSE_CALLS INVALIDATIONS

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

9fusd37prv595 000000006C5D2988 3950875941          1          1           1             0

第二步：

exec sys.dbms_shared_pool.purge('000000006C5D2988,3950875941', 'c');

 

参考：

http://www.savedba.com/?p=128

https://blog.csdn.net/weixin_34186950/article/details/89795951

 

索引监控

怎样监控无用的索引 　

 开始监控：alter index index_name monitoring usage;

 检查使用状态：select * from v$object_usage; 

 停止监控：alter index index_name nomonitoring usage;