GATHER_DATABASE_STATS 的常见问题及 权限 ANALYZE ANY

2024-01-15 19:44

本文主要是介绍GATHER_DATABASE_STATS 的常见问题及 权限 ANALYZE ANY,希望对大家解决编程问题提供一定的参考价值,需要的开发者们随着小编来一起学习吧!

​​​​​​光有执行DBMS_STATS的权限还是不够的

  • DBMS_STATS.GATHER_DATABASE_STATS (ESTIMATE_PERCENT => 30);

    *
    ERROR at line 1:
    ORA-20000: Insufficient privileges to analyze an object in Database
    ORA-06512: at "SYS.DBMS_STATS", line 13323
    ORA-06512: at "SYS.DBMS_STATS", line 13682
    ORA-06512: at "SYS.DBMS_STATS", line 13826
    ORA-06512: at "SYS.DBMS_STATS", line 13790
    ORA-06512: at line 1

CHANGES

CAUSE

User did not have the right privilege:   ANALYZE ANY DICTIONARY.

You must have the SYSDBA or both ANALYZE ANY DICTIONARY and ANALYZE ANY system privilege to execute this procedure.

SOLUTION

Connect to Sqlplus as sysdba and issue command:

SQL> Grant ANALYZE ANY DICTIONARY to username;
SQL>grant ANALYZE ANY system privilege to username;

NOTE: username is the name of the user trying to gather statistics.

10G:

IMPORTANT: PLEASE NOTE:

These recommendations apply to the majority of databases.
The recommendations aim for statistics accuracy  so full samples are suggested.

For very large systems, the gathering of statistics can be a very time consuming and resource intensive activity.
In this environment sample sizes need to be carefully controlled to ensure that gathering completes within acceptable timescales and resource constraints.
For guidance on this topic See:

 

Note:237901.1 Gathering Schema or Database Statistics Automatically - Examples


In these environments, it is also recommended to utilize change based statistics gathering to avoid re-gathering information unnecessarily.
Please see:

 

Note:44961.1 Statistics Gathering: Frequency and Strategy Guidelines



Gathering statistics an individual table

 

exec dbms_stats.gather_table_stats( -
ownname => NULL, -
tabname => ' Table_name ', -
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, -
cascade => TRUE, -
method_opt => 'FOR ALL COLUMNS SIZE AUTO' );

N.B. replace '  Table_name  ' with the name of the table to gather statistics for.

Gathering statistics for all objects in a schema

 

exec dbms_stats.gather_schema_stats( -
 ownname => NULL, -
cascade => TRUE, -
method_opt => 'FOR ALL COLUMNS SIZE AUTO' );

Gathering statistics for all objects in the database:

 

exec dbms_stats.gather_database_stats( -
cascade => TRUE, -
method_opt => 'FOR ALL COLUMNS SIZE AUTO' );

12C:


Quick Recreate Recommendation
To achieve a quick delete and recreate of the statistics on an individual table and its indexes (adding column statistics for any skewed columns) and following the recommendations in this article use:

exec dbms_stats.delete_table_stats(ownname=>'user_name',-
  tabname=>'table_name',cascade_indexes=>true);


Sample Statistic Gathering Commands
Gathering statistics an individual table
exec dbms_stats.gather_table_stats(  -
       ownname => '  Schema_name ', -
       tabname => '  Table_name  ', -
       estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,  -
       cascade => TRUE,  -
       method_opt => 'FOR ALL COLUMNS SIZE AUTO' );

Gathering statistics for all objects in a schema
exec dbms_stats.gather_schema_stats( -
 ownname => '  Schema_name ', -
cascade => TRUE, -
method_opt => 'FOR ALL COLUMNS SIZE AUTO' );


Gathering statistics for all objects in the database:
exec dbms_stats.gather_database_stats( -
cascade => TRUE, -
method_opt => 'FOR ALL COLUMNS SIZE AUTO' );

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

How to exclude external tables when you run dbms_stats.gather_database_stats procedure manually.

The following errors may be seen when you gather database or schema stats:

DBMS_STATS: GATHER_STATS_JOB: GATHER_TABLE_STATS('"SCOTT"','"TEST_EXT"','""', ...)
DBMS_STATS: ORA-20011: Approximate NDV failed: ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-04040: file sapasset.csv in MAXIMO_CURRENCY not found
DBMS_STATS: Too many errors... Rest of the erros are not reporded.

SOLUTION

Lock the stats for the external table and rerun the database or schema stats:

1. EXECUTE DBMS_STATS.LOCK_TABLE_STATS ('owner name', 'table name');

2. EXEC DBMS_STATS.gather_database_stats;

or

EXEC DBMS_STATS.gather_schema_stats('SCOTT');

3. Once gather stats is complete, unlock the table stats:

EXECUTE DBMS_STATS.UNLOCK_TABLE_STATS ('owner name', 'table name');

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

The document clarifies how sys and system stats are gathered by procedures DBMS_STATS.GATHER_DICTIONARY_STATS and DBMS_STATS.GATHER_DATABASE_STATS.

QUESTIONS AND ANSWERS

  1. Does executing "DBMS_STATS.GATHER_DICTIONARY_STATS" gather statistics on schemas other than the "SYS" schema?

    Yes. From "dbmsstat.sql" in $ORACLE_HOME/rdbms/admin, the "GATHER_DICTIONARY_STATS" procedure "gathers statistics for dictionary schemas 'SYS', 'SYSTEM' and schema (internal) of RDBMS components.":

    ...
    procedure gather_dictionary_stats
    ...
    -- Gather statistics for dictionary schemas 'SYS', 'SYSTEM' and schemas of
    -- RDBMS components.
    ...

  2. What is the difference between "DBMS_STATS.GATHER_DICTIONARY_STATS" and "DBMS_STATS.GATHER_SCHEMA_STATS ('SYS')"?

    • "GATHER_DICTIONARY_STATS" gathers statistics for dictionary schemas 'SYS', 'SYSTEM' and schema (internal) of RDBMS components (as above)
    • "GATHER_SCHEMA_STATS ('SYS') gathers statistics on objects owned by 'SYS' schema only.
  3. Does "DBMS_STATS.GATHER_DATABASE_STATS" gather statistics on both the 'SYS' and 'SYSTEM' schemas? Do I need to specify "GATHER_SYS=>TRUE" to achieve this?

    The GATHER_DATABASE_STATS procedure gathers statistics for all objects in the database. Since "GATHER_SYS" is true for "GATHER_DATABASE_STATS" by default, it is not required to specify this parameter to gather statistics on both the 'SYS' and 'SYSTEM' schemas.

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

GOAL

To describe how to force table statistics to be gathered on every execution of the default automatic statistics job and the GATHER_DATABASE_STATS job.
 

SOLUTION

If you set the stale percentage of the particular table(s) you are interested in to 0, this will force the statistcs of this table to be gathered for every execution of default auto job regardless of the actual number modifications. You can set this using the following command:

exec dbms_stats.set_table_prefs('<user_name>','EMP','STALE_PERCENT',0);

The default value for STALE_PERCENT is 10%  (the default will be applied if NULL is specified).
For further information, refer to following:

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

SYMPTOMS

No statistics are gathered for certain objects such as Materialized view logs when using dbms_stats.gather_database_stats  or dbms_stats.gather_schema_stats.

CHANGES

 none

CAUSE

Statistics are not gathered for the following objects:
 

  •  IOT overflow segments
  •  IOT mapping tables
  •  snapshot logs
  •  objects in the recycle bin 
  •  external tables
  •  context DR$/DR# tables
  •  sys.sumdelta$:
  •  secondary tables of domain indexes

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

SYMPTOMS

  • Statistics on some indexes are never gathered by the Automatic Optimizer Statistics Collection job despite having stale statistics. For example, the following object is marked as stale but is never gathered:
    SQL> SELECT table_name, index_name, stale_stats FROM dba_ind_statistics WHERE table_owner = user AND table_name = 'STALE_IDX';TABLE_NAME    INDEX_NAME      STALE_STATS
    -----------   -------------   -----------
    STALE_IDX     SYS_C0013557    YES    
    
  • The table related to these indexes is not marked as stale

CHANGES

None

CAUSE

When gather_database_stats or gather_schema_stats is called with LIST AUTO, it only returns stale tables, tables with no stats, and indexes with no stats. It is by design that stale index statistics are not refreshed unless the corresponding table statistics are stale as well.

A defect was filed against this symptom but it cannot be fixed in the scope of a bug fix within the current infrastructure. An enhancement was also filed to address this in a future version, subject to feasibility:

Bug 16411709 AUTO GATHER STATS IS NOT PICKING UP STALE OBJECTS
Bug 10411689 DBMS_STATS.GATHER_*_STATS SHOULD RETURN STALE INDEXES IN LISTOBJ FOR LIST AUTO

SOLUTION

As a workaround, periodically Gather Statistics on stale objects manually.


Note: If the table is also marked as stale then it could be that the Automatic Optimizer Statistics Collection job does not have enough time available to get to these objects. According to the FAQ for Automatic Statistics Collection:

"The GATHER_DATABASE_STATS_JOB_PROC procedure called by the 'auto optimizer stats collection' job prioritizes database objects that have no statistics. This means that objects that most need statistics are processed first. Once these are done then objects with stale statistics are addressed. For these, there is no particular prioritization."

Document 1233203.1 FAQ: Automatic Statistics Collection

---------------Oracle10g 后 自动monitor  objects become stale

Intended for anyone collecting stats via the DBMS_STATS package

DETAILS


This is brief note to add some clarification in the area of the DBMS_STATS.GATHER_SCHEMA_STATS and DBMS_STATS.GATHER_DATABASE_STATS procedures.  The 'options' parameter of these two procedures allows you to provide further
specifications on which objects to gather statistics on. Two of the values that this parameter can take are 'GATHER STALE' and 'GATHER AUTO'.

Summary:

  • 'GATHER AUTO': Oracle implicitly determines which objects need new statistics, and determines how to gather those statistics. Oracle makes up its own mind based on its own criteria.
     
  • 'GATHER STALE': Oracle gathers on objects considered to be STALE. By default 10% of the rows need to change to trigger this.

Detail:

Oracle will gather statistics on objects which have statistics considered to be STALE. This is done by looking at the *_tab_modifications views. To the end user, this means that if more than 10% of the rows change, then statistics will automatically be gathered.

Oracle will gather automatically statistics on objects which currently have NO statistics (even if they have NO MONITORING set) *plus* existing objects with STALE statistics. Prior to Oracle 10g GATHER AUTO (just like GATHER STALE) required monitoring to be turned on for the objects which already have statistics. If monitoring is was not turned on, there was no way for Oracle to know which objects become stale. In 10g and above this is handled automatically.

When GATHER AUTO is specified in DBMS_STATS.GATHER_STATS syntax, Oracle implicitly determines which objects need new statistics, and determines how to gather those statistics. Thus, Gather AUTO option can be used to allow Oracle to decide how much statistics to gather.

When GATHER STALE is specified, any other parameters specified will also be taken into account.

-------------------STALE_PERCENT 设置为0 也必须有改变才gather

GOAL

Execute dbms_stats.set_table_prefs(owner,table,'STALE_PERCENT',0) does not have desired effect.


 

SOLUTION


 
This is expected behavior as the stats will gather initially with stale_percent =0,but subsequently will not be gathered unless there is modifications on the objects that are involved.

Following testcase gives an example:
 

Testcase
==========

SQL> select to_char(last_analyzed,'YYYY/MM/DD HH24:MI:SS') from dba_tables where table_name = 'SHAWN';

TO_CHAR(LAST_ANALYZ
-------------------
2019/01/09 13:53:53

SQL> exec DBMS_AUTO_TASK_IMMEDIATE.GATHER_OPTIMIZER_STATS;

PL/SQL procedure successfully completed.

SQL> select to_char(last_analyzed,'YYYY/MM/DD HH24:MI:SS') from dba_tables where table_name = 'SHAWN';

TO_CHAR(LAST_ANALYZ
-------------------
2019/01/09 13:53:53


=============================================

Todays date 1/14/2019

Reran the below query


select to_char(last_analyzed,'YYYY/MM/DD HH24:MI:SS') from dba_tables where table_name = 'SHAWN';

TO_CHAR(LAST_ANALYZ
-------------------
2019/01/09 13:53:53


SQL> select * from shawn;

FNAME LNAME ID
-------------------- -------------------- ----------
SEWY JOHN 1
DOE MIKE 2
RONALDO MESSI 3
JORDAN NIKE 4



Then Inserted a row

SQL> insert into shawn values ('seshe', 'bisi',5);

1 row created.

SQL> commit
 2 ;



Then rerun the auto stats


SQL> exec DBMS_AUTO_TASK_IMMEDIATE.GATHER_OPTIMIZER_STATS;

PL/SQL procedure successfully completed.

SQL> select to_char(last_analyzed,'YYYY/MM/DD HH24:MI:SS') from dba_tables where table_name = 'SHAWN';

TO_CHAR(LAST_ANALYZ
-------------------
2019/01/14 15:27:35 ==========Now its analyzed

这篇关于GATHER_DATABASE_STATS 的常见问题及 权限 ANALYZE ANY的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!



http://www.chinasem.cn/article/610011

相关文章

Spring Security 基于表达式的权限控制

前言 spring security 3.0已经可以使用spring el表达式来控制授权,允许在表达式中使用复杂的布尔逻辑来控制访问的权限。 常见的表达式 Spring Security可用表达式对象的基类是SecurityExpressionRoot。 表达式描述hasRole([role])用户拥有制定的角色时返回true (Spring security默认会带有ROLE_前缀),去

SQL中的外键约束

外键约束用于表示两张表中的指标连接关系。外键约束的作用主要有以下三点: 1.确保子表中的某个字段(外键)只能引用父表中的有效记录2.主表中的列被删除时,子表中的关联列也会被删除3.主表中的列更新时,子表中的关联元素也会被更新 子表中的元素指向主表 以下是一个外键约束的实例展示

基于MySQL Binlog的Elasticsearch数据同步实践

一、为什么要做 随着马蜂窝的逐渐发展,我们的业务数据越来越多,单纯使用 MySQL 已经不能满足我们的数据查询需求,例如对于商品、订单等数据的多维度检索。 使用 Elasticsearch 存储业务数据可以很好的解决我们业务中的搜索需求。而数据进行异构存储后,随之而来的就是数据同步的问题。 二、现有方法及问题 对于数据同步,我们目前的解决方案是建立数据中间表。把需要检索的业务数据,统一放到一张M

如何去写一手好SQL

MySQL性能 最大数据量 抛开数据量和并发数,谈性能都是耍流氓。MySQL没有限制单表最大记录数,它取决于操作系统对文件大小的限制。 《阿里巴巴Java开发手册》提出单表行数超过500万行或者单表容量超过2GB,才推荐分库分表。性能由综合因素决定,抛开业务复杂度,影响程度依次是硬件配置、MySQL配置、数据表设计、索引优化。500万这个值仅供参考,并非铁律。 博主曾经操作过超过4亿行数据

性能分析之MySQL索引实战案例

文章目录 一、前言二、准备三、MySQL索引优化四、MySQL 索引知识回顾五、总结 一、前言 在上一讲性能工具之 JProfiler 简单登录案例分析实战中已经发现SQL没有建立索引问题,本文将一起从代码层去分析为什么没有建立索引? 开源ERP项目地址:https://gitee.com/jishenghua/JSH_ERP 二、准备 打开IDEA找到登录请求资源路径位置

MySQL数据库宕机,启动不起来,教你一招搞定!

作者介绍:老苏,10余年DBA工作运维经验,擅长Oracle、MySQL、PG、Mongodb数据库运维(如安装迁移,性能优化、故障应急处理等)公众号:老苏畅谈运维欢迎关注本人公众号,更多精彩与您分享。 MySQL数据库宕机,数据页损坏问题,启动不起来,该如何排查和解决,本文将为你说明具体的排查过程。 查看MySQL error日志 查看 MySQL error日志,排查哪个表(表空间

MySQL高性能优化规范

前言:      笔者最近上班途中突然想丰富下自己的数据库优化技能。于是在查阅了多篇文章后,总结出了这篇! 数据库命令规范 所有数据库对象名称必须使用小写字母并用下划线分割 所有数据库对象名称禁止使用mysql保留关键字(如果表名中包含关键字查询时,需要将其用单引号括起来) 数据库对象的命名要能做到见名识意,并且最后不要超过32个字符 临时库表必须以tmp_为前缀并以日期为后缀,备份

[MySQL表的增删改查-进阶]

🌈个人主页:努力学编程’ ⛅个人推荐: c语言从初阶到进阶 JavaEE详解 数据结构 ⚡学好数据结构,刷题刻不容缓:点击一起刷题 🌙心灵鸡汤:总有人要赢,为什么不能是我呢 💻💻💻数据库约束 🔭🔭🔭约束类型 not null: 指示某列不能存储 NULL 值unique: 保证某列的每行必须有唯一的值default: 规定没有给列赋值时的默认值.primary key:

MySQL-CRUD入门1

文章目录 认识配置文件client节点mysql节点mysqld节点 数据的添加(Create)添加一行数据添加多行数据两种添加数据的效率对比 数据的查询(Retrieve)全列查询指定列查询查询中带有表达式关于字面量关于as重命名 临时表引入distinct去重order by 排序关于NULL 认识配置文件 在我们的MySQL服务安装好了之后, 会有一个配置文件, 也就

Java 连接Sql sever 2008

Java 连接Sql sever 2008 /Sql sever 2008 R2 import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.Statement; public class TestJDBC