Browse Prior Art Database

Automatically Generating Database Statistics Profiles

IP.com Disclosure Number: IPCOM000201621D
Publication Date: 2010-Nov-16
Document File: 3 page(s) / 23K

Publishing Venue

The IP.com Prior Art Database

Abstract

Disclosed is a method for generating a statistics profile for an object based on existing collected statistics.

This text was extracted from a PDF file.
This is the abbreviated version, containing approximately 53% of the total text.

Page 01 of 3

Automatically Generating Database Statistics Profiles

Database statistics profiles are used to simplify statistics collection by associating a set of collection options for each database object. For example, a statistics profile for a table may contain options to collect selected cardinality and distribution statistics. When the statistics collection utility executes, collection options are loaded from the profile, avoiding the need to specify the options directly in the utility command. This simplifies the collection process when the same statistics need to be collected for an object regularly.

The problem with statistics profiles is that the collection options must be manually typed in when creating the profile for the first time. This is particularly cumbersome for large

workloads with many tables, indexes, views, etc. These objects will often have existing

statistics collected using finely-tuned and complex collection options. In order to translate these options to a statistics profile, they must be manually entered when creating the profile for the first time. For a large collection of database objects, this becomes a daunting task.

This disclosure describes a solution that generates a statistics profile for an object based on its existing statistics collected.

Database statistics are stored in the system catalog. By analyzing an object's existing statistics from the catalog, a set of collection options is generated and used as input

when creating the statistics profile.

The novel process for generating a statistics profile for a database object based on its existing statistics collected is as follows:

1. Database statistics are stored in system catalog tables. Each table is analyzed and the results become a part of the overall set of collection options.

The initial set is empty (no statistics collected):

S = { }

2. Tables and statistical views: Search the catalog for table/view statistics related to the object and add it to the overall set.

T = { table/view cardinalities, data...