Using dbms_stats to Gather Oracle Stats for Schema, Table and Indexes

 

Learn how to use dbms_stats to gather Oracle database statistics for schemas, tables, and indexes. This step-by-step guide covers essential commands to optimize database performance

Scientists looking at a dashboard and doing calculations and analysis

In Oracle databases, maintaining up-to-date statistics is crucial for optimal query performance. The dbms_stats package is a powerful tool that helps database administrators gather and manage these statistics for schemas, tables, and indexes.

In this guide, we will walk you through the essential steps to effectively use dbms_stats to collect accurate Oracle database statistics, ensuring that your database performs at its best.

Whether you're optimizing a large enterprise database or fine-tuning a smaller environment, this tutorial will provide you with the knowledge you need to manage Oracle stats with confidence.

👓 Table of Contents

• Gather Schema Stats
• Gather Table Stats
• Gather Index Stats
• Gather Database Stats

Gather Schema Stats with GATHER_SCHEMA_STATS

To collect statistics for an entire schema, use the following command.

BEGIN
    -- GATHER STATS FROM SCHEMA USING DEFAULT OPTIONS
    EXEC DBMS_STATS.GATHER_SCHEMA_STATS('SCHEMA_NAME'); 
END;

Using the GATHER_SCHEMA_STATS command

Considerations

• For partitioned tables, use CASCADE=>TRUE to gather statistics for all partitions

Full Syntax of GATHER_SCHEMA_STATS

DBMS_STATS.GATHER_SCHEMA_STATS ( 
   ownname          VARCHAR2, 
   estimate_percent NUMBER   DEFAULT to_estimate_percent_type 
                                                (get_param('ESTIMATE_PERCENT')), 
   block_sample     BOOLEAN  DEFAULT FALSE, 
   method_opt       VARCHAR2 DEFAULT get_param('METHOD_OPT'),
   degree           NUMBER   DEFAULT to_degree_type(get_param('DEGREE')), 
   granularity      VARCHAR2 DEFAULT GET_PARAM('GRANULARITY'), 
   cascade          BOOLEAN  DEFAULT to_cascade_type(get_param('CASCADE')), 
   stattab          VARCHAR2 DEFAULT NULL, 
   statid           VARCHAR2 DEFAULT NULL, 
   options          VARCHAR2 DEFAULT 'GATHER', 
   objlist          OUT      ObjectTab,
   statown          VARCHAR2 DEFAULT NULL, 
   no_invalidate    BOOLEAN  DEFAULT to_no_invalidate_type (
                                     get_param('NO_INVALIDATE')),
  force             BOOLEAN DEFAULT FALSE,
  obj_filter_list  ObjectTab DEFAULT NULL);

Syntax of GATHER_SCHEMA_STATS

Gather Table Stats with GATHER_TABLE_STATS

To gather statistics of individual tables use the following command.

BEGIN
    EXEC DBMS_STATS.GATHER_TABLE_STATS(
        ownname     => 'SCHEMA_NAME',
        tabname     => 'TABLE_NAME'
    );
END;

Using the GATHER_TABLE_STATS command

Considerations

• Small tables: Use a larger sample size
• Large tables: Consider parallelism and incremental statistics

Full Syntax of GATHER_TABLE_STATS

DBMS_STATS.GATHER_TABLE_STATS (
   ownname          VARCHAR2, 
   tabname          VARCHAR2, 
   partname         VARCHAR2 DEFAULT NULL,
   estimate_percent NUMBER   DEFAULT to_estimate_percent_type 
                                                  (get_param('ESTIMATE_PERCENT')),
   block_sample     BOOLEAN  DEFAULT FALSE,
   method_opt       VARCHAR2 DEFAULT get_param('METHOD_OPT'),
   degree           NUMBER   DEFAULT to_degree_type(get_param('DEGREE')),
   granularity      VARCHAR2 DEFAULT GET_PARAM('GRANULARITY'), 
   cascade          BOOLEAN  DEFAULT to_cascade_type(get_param('CASCADE')),
   stattab          VARCHAR2 DEFAULT NULL, 
   statid           VARCHAR2 DEFAULT NULL,
   statown          VARCHAR2 DEFAULT NULL,
   no_invalidate    BOOLEAN  DEFAULT  to_no_invalidate_type (
                                                      get_param('NO_INVALIDATE')),
   stattype         VARCHAR2 DEFAULT 'DATA',
   force            BOOLEAN  DEFAULT FALSE,
   context          DBMS_STATS.CCONTEXT DEFAULT NULL, -- non operative
   options          VARCHAR2 DEFAULT get_param('OPTIONS'));

Syntax of GATHER_TABLE_STATS

Gather Index Stats with GATHER_INDEX_STATS

To gather Indexes statistics use the following command.

BEGIN
    EXEC DBMS_STATS.GATHER_INDEX_STATS(
        ownname     => 'SCHEMA_NAME',
        indname     => 'INDEX_NAME'
    );
END;
/

Using the GATHER_INDEX_STATS command

Considerations

• Unique indexes: Gather statistics for uniqueness checks
• Bitmap indexes: Handle them differently due to their nature

Full Syntax of GATHER_INDEX_STATS

DBMS_STATS.GATHER_INDEX_STATS (
   ownname          VARCHAR2, 
   indname          VARCHAR2, 
   partname         VARCHAR2 DEFAULT NULL,
   estimate_percent NUMBER   DEFAULT to_estimate_percent_type 
                                                (GET_PARAM('ESTIMATE_PERCENT')),
   stattab          VARCHAR2 DEFAULT NULL, 
   statid           VARCHAR2 DEFAULT NULL,
   statown          VARCHAR2 DEFAULT NULL,
   degree           NUMBER   DEFAULT to_degree_type(get_param('DEGREE')),
   granularity      VARCHAR2 DEFAULT GET_PARAM('GRANULARITY'),
   no_invalidate    BOOLEAN  DEFAULT to_no_invalidate_type 
                                               (GET_PARAM('NO_INVALIDATE')),
   force            BOOLEAN DEFAULT FALSE);

Syntax of GATHER_INDEX_STATS

Gather Stats for Database with GATHER_DATABASE_STATS

To gather whole database statistics use the following command.

BEGIN
    DBMS_STATS.GATHER_DATABASE_STATS;
END;
/

Using the GATHER_DATABASE_STATS command

Full Syntax of GATHER_DATABASE_STATS

DBMS_STATS.GATHER_DATABASE_STATS (
   estimate_percent NUMBER   DEFAULT to_estimate_percent_type 
                                                (get_param('ESTIMATE_PERCENT')),
   block_sample     BOOLEAN  DEFAULT FALSE,
   method_opt       VARCHAR2 DEFAULT get_param('METHOD_OPT'),
   degree           NUMBER   DEFAULT to_degree_type(get_param('DEGREE')),
   granularity      VARCHAR2 DEFAULT GET_PARAM('GRANULARITY'), 
   cascade          BOOLEAN  DEFAULT to_cascade_type(get_param('CASCADE')),
   stattab          VARCHAR2 DEFAULT NULL, 
   statid           VARCHAR2 DEFAULT NULL,
   options          VARCHAR2 DEFAULT 'GATHER',
   objlist          OUT      ObjectTab,
   statown          VARCHAR2 DEFAULT NULL,
   gather_sys       BOOLEAN  DEFAULT TRUE,
   no_invalidate    BOOLEAN  DEFAULT to_no_invalidate_type (
                                     get_param('NO_INVALIDATE')),
   obj_filter_list ObjectTab DEFAULT NULL);

DBMS_STATS.GATHER_DATABASE_STATS (
   estimate_percent NUMBER   DEFAULT to_estimate_percent_type 
                                                (get_param('ESTIMATE_PERCENT')), 
   block_sample     BOOLEAN  DEFAULT FALSE,
   method_opt       VARCHAR2 DEFAULT get_param('METHOD_OPT'),
   degree           NUMBER   DEFAULT to_degree_type(get_param('DEGREE')),
   granularity      VARCHAR2 DEFAULT GET_PARAM('GRANULARITY'), 
   cascade          BOOLEAN  DEFAULT to_cascade_type(get_param('CASCADE')),
   stattab          VARCHAR2 DEFAULT NULL, 
   statid           VARCHAR2 DEFAULT NULL,
   options          VARCHAR2 DEFAULT 'GATHER',
   statown          VARCHAR2 DEFAULT NULL,
   gather_sys       BOOLEAN  DEFAULT TRUE,
   no_invalidate    BOOLEAN  DEFAULT to_no_invalidate_type (
                                     get_param('NO_INVALIDATE')),
   obj_filter_list ObjectTab DEFAULT NULL);

Syntax of GATHER_DATABASE_STATS

Conclusion

Gathering statistics is not just a routine task—it’s a critical part of maintaining a healthy database. By following best practices and understanding the nuances, you’ll ensure optimal query performance.

For further details check the official documentation.

Remember to apply these techniques in your database environment, and happy querying! 🚀

503 Service Unavailable from ORDS

 The error message “503 Service Unavailable” from ORDS (Oracle REST Data Services) indicates that ORDS is unable to establish a connection to the database due to connection pool exhaustion. The key part of the error is:

“All connections in the Universal Connection Pool are in use”

This suggests that all available database connections in the ORDS connection pool are currently occupied. Here’s how you can troubleshoot and fix the issue:

1. Check for Active Sessions in the Database

Since the error suggests that no more connections are available, check the number of active database sessions.

Run this SQL query as a privileged user (e.g., SYS or DBA):

SELECT COUNT(*) FROM v$session WHERE status = 'ACTIVE';

If the number is close to the maximum allowed, you may need to increase the database session limit.

You can check the current limits with:

SHOW PARAMETER sessions;

SHOW PARAMETER processes;

If needed, increase the limits (adjust the value as required):

ALTER SYSTEM SET SESSIONS = 300 SCOPE=BOTH;

ALTER SYSTEM SET PROCESSES = 300 SCOPE=BOTH;

Restart the database for the changes to take effect.

2. Restart ORDS

Below command is to start ORDS.

Logon to server dp0026alli.genworth.net

cd /u01/oracle/ords

nohup java -jar ords.war standalone &

If ORDS has locked up due to exhausted connections, restart it:

ords stop

ords start

or if running as a service:

systemctl restart ords

3. Increase ORDS Connection Pool Size

Modify the connection pool size for ORDS in defaults.xml (or standalone.xml if running standalone).

The file is typically located in:

• /etc/ords/standalone/standalone.xml (for standalone ORDS)

• $ORDS_HOME/config/ords/defaults.xml (for WAR deployments)

Find and update these parameters:

<?xml version="1.0" encoding="UTF-8" standalone="no"?>

<!DOCTYPE properties SYSTEM "http://java.sun.com/dtd/properties.dtd">

<properties>

<comment>Saved on Wed Feb 09 02:29:45 EST 2022</comment>

<!-- Database Connection Settings -->

<entry key="database.api.enabled">true</entry>

<entry key="db.connectionType">basic</entry>

<entry key="db.hostname">dp00001234</entry>

<entry key="db.port">1521</entry>

<entry key="db.sid">mussaib</entry>

<!-- ORDS Feature Settings -->

<entry key="feature.sdw">true</entry>

<entry key="restEnabledSql.active">true</entry>

<!-- Security Settings -->

<entry key="security.requestValidationFunction">wwv_flow_epg_include_modules.authorize</entry>

<entry key="security.validationFunctionType">plsql</entry>

<!-- JDBC Settings -->

<entry key="jdbc.DriverType">thin</entry>

<entry key="jdbc.InitialLimit">5</entry>

<entry key="jdbc.MinLimit">5</entry>

<entry key="jdbc.MaxLimit">50</entry>   <!-- Increased from 50 to 500 -->

<entry key="jdbc.InactivityTimeout">1800</entry>

<entry key="jdbc.AbandonedConnectionTimeout">900</entry>

<entry key="jdbc.MaxConnectionReuseCount">50000</entry>

</properties> 

Restart ORDS after making changes.

4. Check Long-Running Queries

If connections are stuck due to long-running queries, find them with:

SELECT SID, SERIAL#, STATUS, SQL_ID, MACHINE, PROGRAM

FROM v$session

WHERE STATUS = 'ACTIVE'

ORDER BY LAST_CALL_ET DESC;

Kill a problematic session (use with caution):

ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;

5. Check for ORDS Leaks or Misconfigurations

• Ensure that ORDS is properly closing connections when requests complete.

• Check ORDS logs (ords.log) for errors related to connection pooling.

6. Check Database Availability

Ensure the database is actually running:

sqlplus / as sysdba

Then, verify that the database is open:

SELECT status FROM v$instance;

If the database is down, start it with:

STARTUP;

Final Thoughts

• If this happens frequently, consider increasing the connection pool size and optimizing database session handling.

• If the issue is due to excessive traffic, consider load balancing ORDS across multiple instances.

DR Parameters db_file_name_convert & log_file_name_convert

#################################################################################################################################################################################db_file_name_convert##################################################

Below 2 parameters needs to be set only on standby Database.

db_file_name_convert

log_file_name_convert

SQL> SHOW PARAMETER db_file_name_convert;

NAME                                 TYPE        VALUE

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

db_file_name_convert                 string      /u10/oradata/, /u11/oradata/

pdb_file_name_convert                string

SQL> select name,open_mode from v$database;

NAME      OPEN_MODE

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

MUSAIB     MOUNTED

SQL>

Example Scenario:

Production Database: Files might be located in /prod/data/.

Disaster Recovery (DR) Database: Files might need to be located in /dr/data/.

The db_file_name_convert will help Oracle understand the conversion from production to disaster recovery paths.

Example Command:

ALTER SYSTEM SET db_file_name_convert = '/prod/data/', '/dr/data/' SCOPE=BOTH;

This would set the file name conversion for your DR setup.

#################################################################################################################################################################################log_file_name_convert##################################################

The log_file_name_convert parameter in Oracle is used in a Data Guard or Standby Database configuration to handle redo log file name conversion during a restore or recovery operation from a primary (production) database to a standby (DR) database.

SQL> show parameter log_file_name_convert;

NAME                                 TYPE        VALUE

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

log_file_name_convert                string      /u15/oradata/, /u10/oradata/,

                                                 /u16/oradata/, /u11/oradata/

SQL>

ALTER SYSTEM SET log_file_name_convert = '/u15/oradata/', '/u10/oradata/' SCOPE=BOTH;

ALTER SYSTEM SET log_file_name_convert = '/primary_path/', '/standby_path/' SCOPE=BOTH;

Multiple Path Mappings: You can define multiple source-to-target directory mappings as needed. For example, in your output:

ALTER SYSTEM SET log_file_name_convert = '/u15/oradata/', '/u10/oradata/', '/u16/oradata/', '/u11/oradata/' SCOPE=BOTH;

/u15/oradata/, /u10/oradata/, /u16/oradata/, /u11/oradata/

This means the primary database has log files in /u15/oradata/ and /u16/oradata/, and the corresponding standby directories are /u10/oradata/ and /u11/oradata/, respectively.

log_file_name_convert

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

/u15/oradata/, /u10/oradata/,

 /u16/oradata/, /u11/oradata/