Skip to content
pvmehta.com

pvmehta.com

  • Home
  • About Me
  • Toggle search form
  • good linux notes Linux/Unix
  • Sending email with file attachment. Linux/Unix
  • Gathering statistics with DBMS_STATS Oracle
  • New OFA for 11g Oracle
  • Oracle Statspack survival Guide Oracle
  • To find all disk io ( EMC as well as local) Linux/Unix
  • Good notes for shared pool Oracle
  • SQLPLUS COPY command Precautions. Oracle
  • PLSQL Table Syntax 2 Oracle
  • avail.sh ( find filesystem spae usage) Linux/Unix
  • chk_space_SID.ksh Linux/Unix
  • changing kernel parameter in Oracle Enterpise Linux Linux/Unix
  • AWR settings- MMON is not taking snapshot. Oracle
  • Changing default shell Linux/Unix
  • Drop tempfiles from database Oracle

DBMS_UTILITY PACKAGE

Posted on 18-Nov-2005 By Admin No Comments on DBMS_UTILITY PACKAGE

DBMS_UTILITY

Oracle provides two procedures under the DBMS_UTILITY package related to statistics generation. (Oracle recommends use of DBMS_STATS package for generating statistics).

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

DBMS_UTILITY.ANALYZE_DATABASE

DBMS_UTILITY.ANALYZE_DATABASE is used for analyzing all tables, clusters and indexes at database level. It takes the same set of parameters as above except for the schema name.

e.g.: Estimating statistics for database with 30 percent sample.

SQL> exec dbms_utility.analyze_database(‘ESTIMATE’, estimate_percent => 30);

PL/SQL procedure successfully completed.

9.2) ANALYZE command

The ANALYZE command can also be used to collect statistics for individual objects. The Object to be analyzed should belong to the local schema or the user should have ANALYZE ANY TABLE system privilege. This command can be used for the following purpose:

Collect statistics for individual objects.

Validate the structure of an object.

To list migrated or chained rows.

Validate REF links.

Collect statistics not used by the optimizer.

Oracle recommends the use of the DBMS_STATS package for collecting statistics. The ANALYZE command can be used for the other 4 points mentioned above. Statistics are not collected for columns of type- REFs, varrays, nested tables, LOBs , LONG or object types.

If no sample size is provided when estimating statistics with the ANALYZE command, Oracle will take a default sample size of the first 1064 rows. This may not be effective and most often will result in bad queries.

If the ESTIMATE sample size is greater than 50%, it is as good as the COMPUTE option.

Columns such as EMPTY_BLOCKS, AVG_SPACE, CHAIN_CNT, AVG_SPACE_FREELIST_BLOCKS and NUM_FREELIST_BLOCKS are not populated using the DBMS_STATS package. These are populated by using the ANALYZE command and could be used for maintenance and administration activities.

The below examples are given for statistics generation only.

e.g.: Gathering statistics for a table. This will also compute for individual columns and related indexes.

orAP>analyze table am_statchk compute statistics;

Table analyzed.

e.g.: Deleting statistics for a table. This will delete statistics related to table, columns and related indexes.

orAP> analyze table am_statchk delete statistics;

Table analyzed.

e.g.: Estimating statistics for a table with 20 percent rows.

orAP>analyze table am_statchk estimate statistics sample 20 percent;

Table analyzed.

e.g.: Estimating statistics for a table with 1000 sample rows.

orAP>analyze table am_statchk estimate statistics sample 1000 rows;

Table analyzed.

e.g.: Gathering statistics for indexed columns of a table.

orAP>analyze table am_statchk compute statistics for all indexed columns;

Table analyzed.

e.g.: Computing statistics for all columns in a table.

orAP>analyze table am_statchk compute statistics for all columns;

Table analyzed.

e.g.: Computing statistics for individual indexes.

orAP>analyze index am_statchk_n1 compute statistics;

Index analyzed.

e.g.: Deleting statistics for an index.

orAP>analyze index am_statchk_n1 delete statistics;

Index analyzed.

e.g.: Computing statistics for all indexes and all indexed columns.

orAP>analyze table am21 estimate statistics sample 5 percent for all indexes for all indexed columns;

Table analyzed.

Oracle, SQL scripts

Post navigation

Previous Post: DBMS_UTILITY.ANALYZE_SCHEMA
Next Post: get_vmstat.ksh

Related Posts

  • CTAS with LONG Column for 9i and higher Oracle
  • New OFA for 11g Oracle
  • Disbaling DBA_SCHEDULER_JOBS Oracle
  • Rollback force for distributed transactions Oracle
  • eplan9i.sql Oracle
  • oradebug ipcrm ipcs Oracle

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Categories

  • AWS (2)
  • Azure (1)
  • Linux/Unix (149)
  • Oracle (392)
  • PHP/MYSQL/Wordpress (10)
  • Power-BI (0)
  • Python/PySpark (7)
  • RAC (17)
  • rman-dataguard (26)
  • shell (149)
  • SQL scripts (341)
  • Uncategorized (0)
  • Videos (0)

Recent Posts

  • load SPM baseline from cursor cache05-Jun-2025
  • Drop all SPM baselines for SQL handle05-Jun-2025
  • Load SPM baseline from AWR05-Jun-2025
  • Drop specific SQL plan baseline – spm05-Jun-2025
  • findinfo.sql (SQL for getting CPU and Active session info)27-May-2025
  • SQL Tracker by SID sqltrackerbysid.sql22-Apr-2025
  • How to connect to Oracle Database with Wallet with Python.21-Mar-2025
  • JSON/XML Types in Oracle18-Mar-2025
  • CPU Core related projections12-Mar-2025
  • Exadata Basics10-Dec-2024

Archives

  • 2025
  • 2024
  • 2023
  • 2010
  • 2009
  • 2008
  • 2007
  • 2006
  • 2005
  • How to calculate PROCESSES parameter Oracle
  • Search and Replace vi editor command. Linux/Unix
  • find the files that are 1 day old. Linux/Unix
  • configUOCIOTTO.ora Oracle
  • Korn Shell Arithmatic Linux/Unix
  • create user with unlimited quota Oracle
  • temp_use.sql diplays usage of temp ts Oracle
  • eplan.sql Oracle

Copyright © 2025 pvmehta.com.

Powered by PressBook News WordPress theme