Oracle 搜集统计信息的存储过程
2024-04-03 12:20:23
DECLARE
CURSOR STALE_TABLE IS
SELECT OWNER,
SEGMENT_NAME,
CASE
WHEN SIZE_GB < 0.5 THEN
30
WHEN SIZE_GB >= 0.5 AND SIZE_GB < 1 THEN
20
WHEN SIZE_GB >= 1 AND SIZE_GB < 5 THEN
10
WHEN SIZE_GB >= 5 AND SIZE_GB < 10 THEN
5
WHEN SIZE_GB >= 10 THEN
1
END AS PERCENT,
8 AS DEGREE
FROM (SELECT OWNER,
SEGMENT_NAME,
SUM(BYTES / 1024 / 1024 / 1024) SIZE_GB
FROM DBA_SEGMENTS
WHERE OWNER = 'ADWU_OPTIMA_AP11'
AND SEGMENT_NAME IN
(SELECT /*+ UNNEST */
DISTINCT TABLE_NAME
FROM DBA_TAB_STATISTICS
WHERE (LAST_ANALYZED IS NULL OR STALE_STATS = 'YES')
AND OWNER = 'ADWU_OPTIMA_AP11')
GROUP BY OWNER, SEGMENT_NAME); BEGIN
DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;
FOR STALE IN STALE_TABLE LOOP
DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => STALE.OWNER,
TABNAME => STALE.SEGMENT_NAME,
ESTIMATE_PERCENT => STALE.PERCENT,
METHOD_OPT => 'for all columns size repeat',
DEGREE => 8,
GRANULARITY => 'ALL',
CASCADE => TRUE);
END LOOP;
END;
/
最新文章
- Python复习之生成器 generator
- 【2016-10-21】【坚持学习】【Day11】【.net 自带的三种委托】
- LINUX测试环境部署mysql（三）
- Spring事务配置的五种方式
- 《算法竞赛入门经典》5.12TeX括号
- cheat engine lua
- CXF之七 传输文件
- 初次了解CSS3
- 实现ios常见菜单效果的思路
- mysql表设计---时间类型
- 在linux下使用百度ueditor编辑器上传图片
- jquery/Js属性无效
- SQL的介绍及MySQL的安装
- How To Handle MLOG$_AP_SUPPLIER_SITES_AL, MLOG$_AP_SUPPLIERS Growing So Much? Having Lots of Data
- Commons Daemon procrun stdout initialized
- 多级nginx代理，获取客户端真实ip
- Python_生产者消费者模型、管道、数据共享、进程池
- 【静默】在RHEL 6.5上静默安装Oracle 18c
- linux基础之程序包管理(rpm,yum)
- Python基本数据类型以及字符串