ORACLE中如何锁住/解锁统计信息?

这篇具有很好参考价值的文章主要介绍了ORACLE中如何锁住/解锁统计信息?。希望对大家有所帮助。如果存在错误或未考虑完全的地方,请大家不吝赐教,您也可以点击"举报违法"按钮提交疑问。

从Oracle 10g 开始,Oracle提供了锁定/解锁表统计信息功能,它的目的是阻止数据库自动收集统计信息,防止可能会产生/出现的糟糕的计划。它对于数据频繁更改的Volatile Tables最有用,因为Volatile Tables的某些数据集可能会生成糟糕的计划。

官方的介绍如下:

Prevent automatic statistic gathering where this may otherwise produce poor plans. As such it is most useful for volatile tables whose data changes frequently and for whom poor plans may be generated with certain data sets.

注:什么是Volatile Tables呢?官方解释:Volatile tables are being deleted or truncated, and then rebuilt during the day. 还有一种解释指:Volatile Tables 是指变化比较大的表,即频繁进行Insert、Delete、Update 多种操作的表。

另外,数据库自动收集表的统计信息,大部分情况下,这种行为对于数据库的性能是有利的。但是也有一些情况,我们不想数据库自动收集某个表的统计信息,例如:

  • 1:自动收集统计信息作业数据采样的比例过低,尤其对于一些大表,准确来说是对于一些数据不怎么变化的大表,我们想手工收集集统计信息(指定较高的采样比例)。这样有利于相关SQL生成正确的执行计划。
  • 2:自动统计信息收集作业运行过后或运行期间,由于一些作业或业务逻辑出现了大量的DML,此时收集的的统计信息可能是不准确,也是就说自动统计信息收集对于这种表没有什么意义,反而浪费了大量资源,我们需要手工或设置相关作业去收集统计信息。那么我们就可以通过锁定表的统计信息,阻止数据库的自动收集统计信息作业去采集相关统计信息。

如果一些对象没有统计信息,而你又锁定了统计信息,那么此时数据库在执行SQL时,就会使用动态采样。这个也是锁定统计信息的另外一个功能。

When you set the statistics of a volatile object to null, Oracle Database dynamically gathers the necessary statistics during optimization using dynamic statistics. The OPTIMIZER_DYNAMIC_SAMPLING initialization parameter controls this feature.

This is useful when you wish to use dynamic sampling on a volatile table. You may also lock statistics on a volatile table at a point when it is fully populated, so that the table statistics are more representative of the table population, so as to use those statistics to generate plans, whatever happens to the table data.

统计信息锁定

当不需要对某个表做收集的时候,可以采用锁定统计信息的方法,把不需要收集的表排除在外,这样可以使得此表上的统计信息不变,Oracle提供三种粒度的锁定统计信息的方法,如下所示

--锁定表的统计信息

EXEC DBMS_STATS.LOCK_TABLE_STATS();


参数:
PROCEDURE LOCK_TABLE_STATS
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 OWNNAME                        VARCHAR2                IN
 TABNAME                        VARCHAR2                IN
 STATTYPE                       VARCHAR2                IN     DEFAULT

--锁定表的分区统计信息

EXEC DBMS_STATS.LOCK_PARTITION_STATS(); 


参数
PROCEDURE LOCK_PARTITION_STATS
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 OWNNAME                        VARCHAR2                IN
 TABNAME                        VARCHAR2                IN
 PARTNAME                       VARCHAR2                IN

--锁定用户统计信息

EXEC DBMS_STATS.LOCK_SCHEMA_STATS();


参数:
PROCEDURE LOCK_SCHEMA_STATS
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 OWNNAME                        VARCHAR2                IN
 STATTYPE                       VARCHAR2                IN     DEFAULT

案例,如何锁定表scott.emp的统计信息,如下所示:

SQL> exec dbms_stats.lock_table_stats('scott', 'emp');

PL/SQL procedure successfully completed.

如果在锁定条件下收集统计信息,那么会出现如下报错:

SQL> exec dbms_stats.lock_table_stats('TEST', 'TEST');

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_table_stats('TEST', 'TEST');
BEGIN dbms_stats.gather_table_stats('TEST''TEST'); END;

*
ERROR at line 1:
ORA-20005: object statistics are locked (stattype = ALL)
ORA-06512: at "SYS.DBMS_STATS", line 40751
ORA-06512: at "SYS.DBMS_STATS", line 40035
ORA-06512: at "SYS.DBMS_STATS", line 9393
ORA-06512: at "SYS.DBMS_STATS", line 10317
ORA-06512: at "SYS.DBMS_STATS", line 39324
ORA-06512: at "SYS.DBMS_STATS", line 40183
ORA-06512: at "SYS.DBMS_STATS", line 40732
ORA-06512: at line 1

还有一个要注意的是,如果当表的统计信息被锁定时,此表上创建索引时,不会采集生成索引的相关统计信息,如下所示:

SQL> drop table test.test;

Table dropped.

SQL> create table test.test
  2  as select * from dba_objects;

Table created.

SQL> exec dbms_stats.lock_table_stats('TEST', 'TEST');

PL/SQL procedure successfully completed.

SQL>  create index test.idx_test_n1 on test.test(object_id, object_name);

Index created.

SQL> select num_rows, last_analyzed from dba_ind_statistics where index_name =upper('idx_test_n1');

  NUM_ROWS LAST_ANALYZED
---------- -------------------


1 row selected.

如果表的统计信息锁定的时候,我们可以使用下面脚本来收集统计信息:

--使用参数force,强制收集统计信息
exec dbms_stats.gather_index_stats('TEST', 'idx_test_n1',force=>true);

SQL> exec dbms_stats.gather_index_stats('TEST', 'idx_test_n1',force=>true);

PL/SQL procedure successfully completed.

SQL> select num_rows, last_analyzed from dba_ind_statistics where index_name =upper('idx_test_n1');

  NUM_ROWS LAST_ANALYZED
---------- -------------------
     72502 2023-08-31 13:55:01

1 row selected.

如果要收集表的统计信息,使用下面SQL

exec dbms_stats.gather_table_stats('TEST', 'TEST',force=>true);

当然还有一种方法就是,先给表解锁统计信息,收集统计信息,然后锁定表的统计信息,如下所示:

exec dbms_stats.unlock_table_stats('TEST','TEST');
exec dbms_stats.gather_table_stats(ownname =>'TEST', tabname =>'TEST',cascade => true,method_opt=>'for all indexed columns size');
exec dbms_stats.lock_table_stats('TEST','TEST');

查看统计信息锁定的对象

我们可以使用如下的SQL查询数据库中哪些表或索引的统计信息被锁定了:

SET LINESIZE 680;
COL OWNER FOR A16
COL INDEX_NAME FOR A30
COL TABLE_OWNER FOR A16
COL TABLE_NAME FOR A30
COL PARTITION_NAME FOR A30
COL SUBPARTITION_NAME FOR A30
SELECT D.OWNER,
       D.INDEX_NAME,
       D.TABLE_OWNER,
       D.TABLE_NAME,
       D.PARTITION_NAME,
       D.SUBPARTITION_NAME,
       D.OBJECT_TYPE
FROM DBA_IND_STATISTICS D
 WHERE STATTYPE_LOCKED IN('ALL''DATA''CACHE')
UNION ALL
SELECT '---',
       '---',
       D.OWNER,
       D.TABLE_NAME,
       D.PARTITION_NAME,
       D.SUBPARTITION_NAME,
       D.OBJECT_TYPE
 FROM DBA_TAB_STATISTICS D
  WHERE STATTYPE_LOCKED  IN('ALL''DATA''CACHE');

STATTYPE_LOCKED为空代表统计信息未锁定。

查看统计信息锁定的表,可以使用下面SQL语句查询获取。

SET LINESIZE 680;
COL OWNER FOR A16
COL TABLE_OWNER FOR A16
COL TABLE_NAME FOR A30
COL PARTITION_NAME FOR A30
COL SUBPARTITION_NAME FOR A30
SELECT 
       D.OWNER,
       D.TABLE_NAME,
       D.PARTITION_NAME,
       D.SUBPARTITION_NAME,
       D.OBJECT_TYPE
 FROM DBA_TAB_STATISTICS D
  WHERE STATTYPE_LOCKED  IN('ALL''DATA''CACHE');

解锁统计信息锁定

语法:

PROCEDURE UNLOCK_TABLE_STATS
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 OWNNAME                        VARCHAR2                IN
 TABNAME                        VARCHAR2                IN
 STATTYPE                       VARCHAR2                IN     DEFAULT

解锁单个表的统计信息

EXEC DBMS_STATS.UNLOCK_TABLE_STATS('scott','emp');

--解锁用户统计信息

EXEC DBMS_STATS.UNLOCK_SCHEMA_STATS('xxx');

解锁某个用户下(例如,scott用户)的表的统计信息

EXEC DBMS_STATS.UNLOCK_SCHEMA_STATS('scott');

参考资料:

https://cloud.tencent.com/developer/article/1515990

Preserving Statistics using DBMS_STATS.LOCK_TABLE_STATS (Doc ID 283890.1)文章来源地址https://www.toymoban.com/news/detail-687120.html

到了这里,关于ORACLE中如何锁住/解锁统计信息?的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处: 如若内容造成侵权/违法违规/事实不符,请点击违法举报进行投诉反馈,一经查实,立即删除!

领支付宝红包 赞助服务器费用

相关文章

  • 【Oracle】收集Oracle数据库内存相关的信息

    【声明】文章仅供学习交流,观点代表个人,与任何公司无关。 编辑|SQL和数据库技术(ID:SQLplusDB) Oracle数据库包含多个内存区域,每个区域都包含多个子组件。 Oracle Database Memory Structures 根据具体问题的需要,可以通过如下命令收集Oracle数据库内存相关的信息。 例: 注:SET

    2024年01月21日
    浏览(67)
  • 解决Oracle SQL语句性能问题——收集和维护统计数据

    所谓统计数据,前述文中也提到过,它们是数据库优化器赖以产生SQL语句最优执行计划的依据。以这些统计数据为基础,优化器通过内置的模型、算法等,计算出全部或候选执行计划的成本(Cost),通过比较每个执行计划的成本,选出成本最低的执行计划,也就得到了SQL语句

    2024年02月10日
    浏览(83)
  • 键盘锁住了怎么解锁?这4个方法轻松帮你解决!

    “我在使用电脑输入文字时不知道按错了什么键,导致键盘锁住了。有什么简单的方法可以帮助键盘快速解锁吗?” 当我们在使用电脑时,突然发现键盘被锁住,无法输入任何字符,这无疑会让人感到困扰。但请别担心,这种情况通常是由于某些误操作或系统设置导致的,并

    2024年04月10日
    浏览(29)
  • 查询Oracle和MySQL数据库中当前所有连接信息

    查询Oracle当前所有连接信息: 查询MySQL当前所有连接信息: 在这两个查询中,我为每个字段添加了中文别名,以提高查询结果的可读性

    2024年02月12日
    浏览(70)
  • Centos7-安装Oracle11g-图形化+静默配置监听+DB实例+Navicate远程连接+解锁scott/tiger用户+导入Openssl项目所需要的数据+OCCI测试

    本环境搭建主要用于黑马金融数据安全传输平台项目。 https://github.com/Chufeng-Jiang/OpenSSL_Secure_Data_Transmission_Platform 金融数据安全传输平台 https://space.bilibili.com/38600195/channel/seriesdetail?sid=3727139ctype=0 https://www.bilibili.com/video/BV1Uc411f7To/?spm_id_from=333.999.0.0vd_source=3353f83539e46042d8cf76efb17

    2024年01月21日
    浏览(68)
  • Mac如何安装Oracle?Mac如何配置Docker?手把手教你配置Docker并配置Oracle

    !!!安装前准备:请提前安装好jdk 1.先去官网下载Doker,下载好以后安装并打开Docker,安装成功后,mac最上面导航栏会出现Docker图标,可以在终端中输入docker --version来查看Docker版本 Docker官网地址:https://www.docker.com 部分Mac打不开Docker???怎么解决,如下图所示在通过导航

    2024年02月03日
    浏览(59)
  • oracle查询如何优化

    1.对查询进行优化,应尽量避免全表扫描,首先应考虑在 where 及 order by 涉及的列上建立索引。 2.应尽量避免在 where 子句中对字段进行 null 值判断,否则将导致引擎放弃使用索引而进行全表扫描,如: select id from t where num is null 可以在num上设置默认值0,确保表中num列没有nul

    2023年04月20日
    浏览(41)
  • oracle如何才能卸载干净

    1、可以通过通过windows运行中输入services.msc进入服务管理器中,找到oracle相关所有服务,点击停止。 2、通过快捷键(Ctrl+Alt+Delete)进入任务管理器,点击服务,找到oracle相关所有服务,点击停止。 1、可以通过通过windows运行中输入regedit进入注册表编辑器中,找到该路径HKEY

    2024年02月09日
    浏览(68)
  • LR中监控ORACLE数据库常用计数器(如何自定义Oracle计数器)

    目录 一、添加自定义计数器的方法 1、要创建自定义查询,请执行以下操作: 2、配置文件示例对象 二、常用自定义计数器列表 三、LR中监控ORACLE数据库常用计数器遇到问题及处理 1. 在安装路径的Mercury LoadRunnerdatmonitors找到vmon.cfg文件,打开。 2. 在vmon.cfg文件的第三行中,

    2024年02月15日
    浏览(52)
  • 解锁讯飞星火:从零开始,教你如何轻松驾驭这款全能AI工具

    星火助手中心   |  原创作者/编辑:凯哥Java                      |  分类:人工智能学习系列教程 0 1 前言 在之前的文章中,介绍了AI的好处,但是我们知道,因为种种原因,在国内想要访问AI需要通过上网手段才可以访问到,而且还有封号风险。今天凯哥给大家介绍一款

    2024年02月21日
    浏览(67)

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

博客赞助

微信扫一扫打赏

请作者喝杯咖啡吧~博客赞助

支付宝扫一扫领取红包,优惠每天领

二维码1

领取红包

二维码2

领红包