Showing posts with label Statistics. Show all posts
Showing posts with label Statistics. Show all posts
Wednesday, June 12, 2019

How to use dbms_stats.report_col_usage

›
set long 10000 select dbms_stats.report_col_usage('SALES', 'ORDERS') from dual; exit Output could look something like thi...
Thursday, May 2, 2019

What exactly is meant by "global statistics" on a partitioned table?

›
From the whitepaper " Understanding Optimizer Statistics ": "When dealing with partitioned tables the Optimizer relies on...
Tuesday, April 9, 2019

A workaround for ORA-54033: column to be modified is used in a virtual column expression

›
Applicable for Oracle 12.1.0.2. My customer reports that an attempt to execute a simple DDL statement failed: ALTER TABLE mytable MODIF...
Wednesday, April 3, 2019

How to gather histograms on a column of a table

›
Use the METHOD_OPT directive in DBMS_STATS.GATHER_TABLE_STATS to collect histograms for column ARTICLE_NAME in table SALES_Q1_RESULTS. In...
Thursday, February 14, 2019

Syntax for using the dbms_stats.gather_index_stats procedure

›
How to analyze an index using dbms_stats: BEGIN SYS.DBMS_STATS.GATHER_INDEX_STATS ( OwnName => 'SCOTT' ,...
Thursday, June 21, 2018

What is the meaning of INCREMENTAL_STALENESS in dbms_stats?

›
INCREMENTAL_STALENESS means "how we decide if a partition's statistics are stale". Its values can be either USE_STALE_PERC...
Tuesday, April 10, 2018

More on finding failed autostats jobs

›
In a previous post , I showed how to check if an autostats job had succeeded. Recently I had to unlock table stats on a very large parti...
Wednesday, February 14, 2018

How to view default settings for dbms_stats

›
Thanks to the author of the blog " Thinking out loud " for writing this piece of code. It makes it easy to view the default sett...
Tuesday, April 4, 2017

How to enable incremental statistics gathering for a large partitioned table

›
What is incremental statistics? It's a way to improve performance of global statisics gathering on large partitioned tables. Increm...
Wednesday, February 22, 2017

How to speed up statistics gathering on a large table - inter object parallelism

›
When you use parallelism to analyze a specific object, Oracle refer to this as "Inter Object Parallelism". In short, the Degree O...

Oracle 12c feature: Online statistics gathering

›
Oracle 12c introduced the concept of "online statitistics gathering". The white paper "Best Practices for Gathering Optimiz...
Wednesday, February 15, 2017

How to lock table statstics in Oracle

›
Use dbms_stats.lock_table_stats conn scott exec dbms_stats.lock_table_stats('SCOTT','DEPT'); Verify the setting: select...
Tuesday, December 27, 2016

How to analyze a specific partition only using dbms_stats

›
To analyze only specific partitions, including subpartitions, use the dbms_stats.gather_table_stats while specifying the directives PART_NA...
Thursday, January 22, 2015

A complete script for gathering system, data dictionary and fixed objects stats

›
alter session set nls_language='AMERICAN'; set timing on set serveroutput on set lines 200 col pname format a20 BEGIN DBMS_STATS.GA...
Wednesday, June 25, 2014

How to gather dictionary statistics and fixed-objects statistics

›
Use the procedures dbms_stats.gather_dictionary_stats and dbms_stats.gather_fixed_objects_stats Examples: BEGIN DBMS_STATS.GATHER_...
Tuesday, June 3, 2014

How to check progress of a long running statistics gathering job

›
If you have a long-running statistics job running, you can check it from v$session_longops: For example, you execute: SQL> EXECUTE dbm...
Thursday, October 31, 2013

Syntax for using the dbms_stats.gather_table_stats and gather_schema_stats procedures

›
To use the default options: BEGIN SYS.DBMS_STATS.GATHER_TABLE_STATS ( OwnName => 'SCOTT' ,TabName =...