Method for automatically collecting Oracle statistical information

Abstract

The present invention discloses a method for automatically collecting Oracle statistical information, comprising the following steps: (a) designing a database table into a partition table, and dividing the partition table into different statistical collection types according to a service feature; (b) periodically scheduling, by an operating system, a database storage procedure, and an input database table owner, a table name, or a partition name, to perform statistical information collection of different granularities; and (c) in the database storage procedure, searching for a corresponding statistical collection type according to the input database table owner, the table name, or the partition name, and completing statistical information collection of a database table object. According to the method for automatically collecting Oracle statistical information, the database table is designed into a partition table, operations such as addition, modification, and deletion are merely for a current partition or some specific partitions, and query and statistics-collection are separately implemented according to different statistical collection types, so that it is convenient to perform statistical information collection based on a granularity of such as an owner, a table, or a partition, thereby improving running efficiency, stability and service continuity of an Oracle database.

Description

technical field [0001] The invention relates to a method for collecting statistical information of a database, in particular to a method for automatically collecting Oracle statistical information. Background technique [0002] Statistical information refers to Oracle database optimizer (optimizer) statistical information. Statistical information describes the physical characteristics of Oracle database objects, such as how many records are in the table, how much disk space is occupied, etc. It is a collection that describes the database and database objects in more detail . [0003] When the Oracle database works in CBO (CostBasedOptimizer) mode (Oracle database 10g and later versions are default and recommended to use this mode), the optimizer evaluates the cost of different execution plans of SQL statements according to the object statistics recorded in the data dictionary, so as to find the optimal The execution plan makes the execution efficiency of the SQL statement t...

AI Technical Summary

Problems solved by technology

The process of collecting statistical information consumes a lot of system resources, but within this time window, not all business systems are idle, so executing statistics collection jobs during this time period will cause serious resource contention and affect normal business processing

[0012] 2. The default collection strategy is adopted, and the integrity of statistical information collection is uncontrollable

Due to the limited range of the default time window, when there are many database objects, a large amount of data, and a large amount of data change, it may not be possible to complete the statistical information collection operation of the required objects within the specified time period

In this case, the statistical information of database objects may not be updated for a long time, affecting the execution efficiency of SQL statements

The database administrator can manually modify and extend the time window of the job, but the time window of the job may coincide with the subsequent business peak, resulting in resource contention

[0013] 3. The dynamic sampling method of statistical information is used, which leads to performance degradation when frequently accessing an object

It can alleviate the problems caused by the default policy to a certain extent, but there are still limitations, such as: 1. Only specified object statistics can be collected

2. Cannot automatically identify new objects, cannot automatically identify and define collection granularity and limit resource consumption

3. The script needs to be modified frequently according to the change of the object, the maintenance cost is high, and the efficiency is not high

Images