Skip to content
pvmehta.com

pvmehta.com

  • Home
  • About Me
  • Toggle search form
  • good linux notes Linux/Unix
  • runsql_once.ksh Linux/Unix
  • Single character replacement in Unix Linux/Unix
  • oracle 10g on linux Linux/Unix
  • find_idle_cpu.sql Oracle
  • cif crons Linux/Unix
  • oracle 11g RAC on vmware Oracle
  • Kill a session dynanically using execute immediate Oracle
  • Library cahe Latches and internal explaination Oracle
  • adding new line after specific pattern using sed Linux/Unix
  • Giving Grant on v$DATABASE Oracle
  • Index Range Scan Oracle
  • Optimizer SORT Operations Oracle
  • Finding locked objects Oracle
  • Temporary Tablespsace Temp tablespace behaviour 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

  • executing Function from SQLPLUS prompt Oracle
  • Reclaim temp tablespace for oracle 8, 8i Oracle
  • Gathering statistics with DBMS_STATS Oracle
  • Finding Oracle Patches with opatch Oracle
  • How to Make Trace Files Created by Oracle Readable by All Users ? Oracle
  • Implementing Listener Security 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 (388)
  • PHP/MYSQL/Wordpress (10)
  • Power-BI (0)
  • Python/PySpark (7)
  • RAC (17)
  • rman-dataguard (26)
  • shell (149)
  • SQL scripts (337)
  • Uncategorized (0)
  • Videos (0)

Recent Posts

  • 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
  • Reading config file from other folder inside class24-Sep-2024
  • Python class import from different folders22-Sep-2024
  • Transfer SQL Profiles from One database to other database.05-Sep-2024
  • Load testing on Oracle 19C RAC with HammerDB18-Jan-2024

Archives

  • 2025
  • 2024
  • 2023
  • 2010
  • 2009
  • 2008
  • 2007
  • 2006
  • 2005
  • Specify the Rollback segment to use in Transaction Oracle
  • Jai Shree Ram Oracle
  • how to find OS block size Oracle
  • Running some SQL on multiple databases connecting using monitoring userid and password Linux/Unix
  • Single character replacement in Unix Linux/Unix
  • sid_wise_sql.sql Oracle
  • find_log_switch.sql Find log switches in graphical manner Oracle
  • Locally Managed Tablespace and Dictionary managed tablespace (LMT-DMT) Oracle

Copyright © 2025 pvmehta.com.

Powered by PressBook News WordPress theme