ITPub博客

首页 > Linux操作系统 > Linux操作系统 > PERF--DBMS_UTILITY對schema analyze

PERF--DBMS_UTILITY對schema analyze

原创 Linux操作系统 作者:vongates 时间:2019-07-17 21:54:02 0 删除 编辑

DBMS_UTILITY.ANALYZE_SCHEMA

This routine will generate statistics on an individual schema level. It is used for analyzing all tables, clusters and indexes.

It takes the following parameters:

  • schema - Name of the schema
  • method - Estimation method, COMPUTE or ESTIMATE. DELETE can be used to remove statistics.
  • estimate_rows - The number of rows to be considered for estimation.
  • estimate_percent - The percentage of rows to be considered for estimation.
  • method_opt - Method options. Generate statistics FOR TABLE, FOR ALL COLUMNS, FOR ALL INDEXED COLUMNS, FOR ALL INDEXES.

If the ESTIMATE method is used, then either estimate_rows or estimate_percent should be specified; these actually specify the sample size to be considered.

Call syntax

dbms_utility.analyze_schema(schema, method, estimate_rows, estimate_percent,  method_opt)

e.g.: Computing statistics for a schema

SQL> exec dbms_utility.analyze_schema('SYSTEM', 'COMPUTE');
PL/SQL procedure successfully completed.

e.g.: Estimating statistics for a schema, sample size is 1024 row.

SQL> exec dbms_utility.analyze_schema('FEM', 'ESTIMATE', estimate_rows => 1024);
PL/SQL procedure successfully completed.

e.g.: Estimating statistics for FA schema, sample size is 10 percent of rows.

SQL> exec dbms_utility.analyze_schema('FA', 'ESTIMATE', estimate_percent => 10);
PL/SQL procedure successfully completed.

e.g.: Deleting statistics for FA schema

SQL> exec dbms_utility.analyze_schema('FA', 'DELETE');
PL/SQL procedure successfully completed.

e.g.: Estimating statistics with 5 percent rows for all indexes in a schema.

SQL> exec dbms_utility.analyze_schema('FA', 'ESTIMATE', estimate_percent => 5, 
          method_opt => 'FOR ALL INDEXES');
PL/SQL procedure successfully completed.

e.g.: Estimating statistics with 5 percent rows for columns with indexes in a schema.

SQL> exec dbms_utility.analyze_schema('FA', 'ESTIMATE', estimate_percent => 5, 
          method_opt => 'FOR ALL INDEXED COLUMNS');
PL/SQL procedure successfully completed.

e.g.: Estimating statistics with 5 percent rows for all columns in a schema.

SQL> exec dbms_utility.analyze_schema('FA', 'ESTIMATE', estimate_percent => 5, 
          method_opt => 'FOR ALL COLUMNS');
PL/SQL procedure successfully completed.

e.g.: Estimating statistics for all tables in a schema.

SQL> exec dbms_utility.analyze_schema('FA', 'ESTIMATE', estimate_percent => 5, 
          method_opt => 'FOR TABLE');
PL/SQL procedure successfully completed.

e.g.: Proper sample size should be given, otherwise ORA-01493 is encountered.

SQL> exec dbms_utility.analyze_schema('FA', 'ESTIMATE', estimate_percent => -5);
BEGIN dbms_utility.analyze_schema('FA', 'ESTIMATE', estimate_percent => -5); END;
 
*
ERROR at line 1:
ORA-01493: invalid SAMPLE size specified
ORA-06512: at "SYS.DBMS_DDL", line 179
ORA-06512: at "SYS.DBMS_UTILITY", line 331
ORA-06512: at line 1

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29987/viewspace-51842/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2018-09-11

  • 博文量
    449
  • 访问量
    292703