Results for DBMS_STATS


1 to 40 of 326 results. Prev  Next
Results per page:  20 |  40  | 60


Supplied PL/SQL Packages and Types Reference, 9i Release 2 (9.2.0.2)

DBMS_STATS, 1 of 2

Using DBMS_STATSThe DBMS_STATS subprograms perform the following general functions: Set or get statistics Transfer … statistics Gather optimizer statistics Most of the DBMS_STATS procedures include the three … dictionary. When a DBMS_STATS subprogram modifies or deletes the statistics for an object, all the

Supplied PL/SQL Packages and Types Reference, 9i Release 2 (9.2.0.2)

DBMS_STATS, 1 of 2

70 DBMS_STATSWith DBMS_STATS you can view and modify optimizer statistics gathered for database objects. The … optimizer. You can also use DBMS_STATS to gather statistics in parallel. This chapter contains the following … topics: Using DBMS_STATS Setting or Getting Statistics Transferring Statistics Gathering Optimizer Statistics Summary of DBMS_STATS

Performance Tuning Guide and Reference, 9i Release 2 (9.2.0.2)

Gathering Optimizer Statistics

Using the DBMS_STATS PackageThe PL/SQL package DBMS_STATS lets you generate and manage statistics for cost-based optimization … also use this package to identify or name statistics gathered. The DBMS_STATS package can gather … schema or database. It does not gather cluster statistics--you can use DBMS_STATS to gather … tables and indexes, DBMS_STATS

Supplied PL/SQL Packages and Types Reference, 9i Release 2 (9.2.0.2)

DBMS_STATS, 2 of 2

Summary of DBMS_STATS SubprogramsTable 70-1  DBMS_STATS Subprograms Subprogram Description PREPARE_COLUMN_VALUES Procedure Converts

Migration, 9i Release 2 (9.2.0.2)

After Upgrading a Database

Upgrade Statistics Tables Created by the DBMS_STATS PackageIf you created statistics tables using the DBMS_STATS.CREATE_STAT_TABLE procedure, then upgrade these tables by executing the following procedure: EXECUTE DBMS_STATS.UPGRADE_STAT_TABLE('scott', 'stat_table'); where SCOTT is the owner of the statistics table and STAT_TABLE is the name of the statistics table. Execute this procedure for each statistics table.

Supplied PL/SQL Packages and Types Reference, 9i Release 2 (9.2.0.2)

DBMS_STATS, 2 of 2

SET_COLUMN_STATS ProcedureThis procedure sets column-related information. In the version of this procedure that deals with user-defined statistics, the statistics type specified is the type to store in the dictionary, in addition to the actual user-defined statistics. If this statistics type is NULL, the statistics type associated with the index or column is stored.

Supplied PL/SQL Packages and Types Reference, 9i Release 2 (9.2.0.2)

DBMS_STATS, 2 of 2

ExceptionsORA-20000: Object does not exist or insufficient privileges. ORA-20001: Invalid or inconsistent input values.

Supplied PL/SQL Packages and Types Reference, 9i Release 2 (9.2.0.2)

DBMS_STATS, 2 of 2

SET_INDEX_STATS ProcedureThis procedure sets index-related information. In the version of this procedure that deals with user-defined statistics, the statistics type specified is the type to store in the dictionary, in addition to the actual user-defined statistics. If this statistics type is NULL, the statistics type associated with the index or column is stored.

Supplied PL/SQL Packages and Types Reference, 9i Release 2 (9.2.0.2)

DBMS_STATS, 2 of 2

ParametersTable 70-11  GET_INDEX_STATS Procedure Parameters Parameter Description ownname Name of the schema. indname Name of the index. partname Name of the index partition for which to get the statistics. If the index is partitioned and if partname is NULL, then the statistics are retrieved for the global index level. stattab User stat table identifier describing from where to retrieve the statistics. If

Supplied PL/SQL Packages and Types Reference, 9i Release 2 (9.2.0.2)

DBMS_STATS, 2 of 2

SyntaxDBMS_STATS.GET_SYSTEM_STATS ( status OUT VARCHAR2, dstart OUT DATE, dstop OUT DATE, pname VARCHAR2, pvalue OUT NUMBER, stattab IN VARCHAR2 DEFAULT NULL, statid IN VARCHAR2 DEFAULT NULL, statown IN VARCHAR2 DEFAULT NULL);

Supplied PL/SQL Packages and Types Reference, 9i Release 2 (9.2.0.2)

DBMS_STATS, 2 of 2

ExceptionsORA-20000: Object does not exist or insufficient privileges. ORA-20002: Bad user statistics table; may need to be upgraded. ORA-20003: Unable to gather system statistics. ORA-20004: Parameter does not exist.

Supplied PL/SQL Packages and Types Reference, 9i Release 2 (9.2.0.2)

DBMS_STATS, 2 of 2

GET_TABLE_STATS ProcedureThis procedure gets all table-related information.

Supplied PL/SQL Packages and Types Reference, 9i Release 2 (9.2.0.2)

DBMS_STATS, 2 of 2

ParametersTable 70-14  DELETE_COLUMN_STATS Procedure Parameters Parameter Description ownname Name of the schema. tabname Name of the table to which this column belongs. colname Name of the column. partname Name of the table partition for which to delete the statistics. If the table is partitioned and if partname is NULL, then global column statistics are deleted. stattab User stat table identifier describing

Supplied PL/SQL Packages and Types Reference, 9i Release 2 (9.2.0.2)

DBMS_STATS, 2 of 2

DELETE_INDEX_STATS ProcedureThis procedure deletes index-related statistics.

Supplied PL/SQL Packages and Types Reference, 9i Release 2 (9.2.0.2)

DBMS_STATS, 2 of 2

ExceptionsORA-20000: Object does not exist or insufficient privileges.

Supplied PL/SQL Packages and Types Reference, 9i Release 2 (9.2.0.2)

DBMS_STATS, 2 of 2

ExceptionsORA-20000: Object does not exist or insufficient privileges.

Supplied PL/SQL Packages and Types Reference, 9i Release 2 (9.2.0.2)

DBMS_STATS, 2 of 2

SyntaxDBMS_STATS.DELETE_DATABASE_STATS ( stattab VARCHAR2 DEFAULT NULL, statid VARCHAR2 DEFAULT NULL, statown VARCHAR2 DEFAULT NULL, no_invalidate BOOLEAN DEFAULT FALSE);

Supplied PL/SQL Packages and Types Reference, 9i Release 2 (9.2.0.2)

DBMS_STATS, 2 of 2

SyntaxDBMS_STATS.CREATE_STAT_TABLE ( ownname VARCHAR2, stattab VARCHAR2, tblspace VARCHAR2 DEFAULT NULL);

Supplied PL/SQL Packages and Types Reference, 9i Release 2 (9.2.0.2)

DBMS_STATS, 2 of 2

ParametersTable 70-22  EXPORT_COLUMN_STATS Procedure Parameters Parameter Description ownname Name of the schema. tabname Name of the table to which this column belongs. colname Name of the column. partname Name of the table partition. If the table is partitioned and if partname is NULL, then global and partition column statistics are exported. stattab User stat table identifier describing where to store the

Supplied PL/SQL Packages and Types Reference, 9i Release 2 (9.2.0.2)

DBMS_STATS, 2 of 2

ParametersTable 70-25  EXPORT_TABLE_STATS Procedure Parameters Parameter Description ownname Name of the schema. tabname Name of the table. partname Name of the table partition. If the table is partitioned and if partname is NULL, then global and partition table statistics are exported. stattab User stat table identifier describing where to store the statistics. statid Identifier (optional) to associate with

Supplied PL/SQL Packages and Types Reference, 9i Release 2 (9.2.0.2)

DBMS_STATS, 2 of 2

ExceptionsORA-20000: Object does not exist or insufficient privileges.

Supplied PL/SQL Packages and Types Reference, 9i Release 2 (9.2.0.2)

DBMS_STATS, 2 of 2

IMPORT_COLUMN_STATS ProcedureThis procedure retrieves statistics for a particular column from the user stat table identified by stattab and stores them in the dictionary.

Supplied PL/SQL Packages and Types Reference, 9i Release 2 (9.2.0.2)

DBMS_STATS, 2 of 2

ExceptionsORA-20000: Object does not exist or insufficient privileges. ORA-20001: Invalid or inconsistent values in the user stat table.

Supplied PL/SQL Packages and Types Reference, 9i Release 2 (9.2.0.2)

DBMS_STATS, 2 of 2

ExceptionsORA-20000: Object does not exist or insufficient privileges. ORA-20001: Invalid or inconsistent values in the user stat table.

Supplied PL/SQL Packages and Types Reference, 9i Release 2 (9.2.0.2)

DBMS_STATS, 2 of 2

GENERATE_STATS ProcedureThis procedure generates object statistics from previously collected statistics of related objects. For fully populated schemas, the gather procedures should be used instead when more accurate statistics are desired.The currently supported objects are b-tree and bitmap indexes.

Supplied PL/SQL Packages and Types Reference, 9i Release 2 (9.2.0.2)

DBMS_STATS, 2 of 2

ParametersTable 70-39  GENERATE_STATS Procedure Parameters Parameter Description ownname Schema of object. objname Name of object. organized Amount of ordering associated between the index and its underlying table. A heavily organized index would have consecutive index keys referring to consecutive rows on disk for the table (the same block). A heavily disorganized index would have consecutive keys referencing

Supplied PL/SQL Packages and Types Reference, 9i Release 2 (9.2.0.2)

DBMS_STATS, 2 of 2

SyntaxDBMS_STATS.ALTER_SCHEMA_TABLE_MONITORING ( ownname VARCHAR2 DEFAULT NULL, monitoring BOOLEAN DEFAULT TRUE);

Supplied PL/SQL Packages and Types Reference, 9i Release 2 (9.2.0.2)

DBMS_STATS, 1 of 2

Setting or Getting StatisticsUse the following procedures to store and retrieve individual column-related, index-related, and table-related statistics: PREPARE_COLUMN_VALUES SET_COLUMN_STATS SET_INDEX_STATS SET_SYSTEM_STATS SET_TABLE_STATS In the special versions of the SET_*_STATS procedures for setting user-defined statistics, the following, if provided, are stored in the dictionary or external statistics table: User-defined

Supplied PL/SQL Packages and Types Reference, 9i Release 2 (9.2.0.2)

DBMS_STATS, 2 of 2

ExceptionsORA-20000: Object does not exist or insufficient privileges. ORA-20001: Invalid input value. ORA-20002: Bad user statistics table; may need to be upgraded. ORA-20003: Unable to set system statistics. ORA-20004: Parameter does not exist.

Supplied PL/SQL Packages and Types Reference, 9i Release 2 (9.2.0.2)

DBMS_STATS, 2 of 2

GET_COLUMN_STATS ProcedureThis procedure gets all column-related information. In the version of this procedure that deals with user-defined statistics, the statistics type returned is the type stored, in addition to the user-defined statistics.

Supplied PL/SQL Packages and Types Reference, 9i Release 2 (9.2.0.2)

DBMS_STATS, 2 of 2

SyntaxUse the following for standard statistics: DBMS_STATS.GET_INDEX_STATS ( ownname VARCHAR2, indname VARCHAR2, partname VARCHAR2 DEFAULT NULL, stattab VARCHAR2 DEFAULT NULL, statid VARCHAR2 DEFAULT NULL, numrows OUT NUMBER, numlblks OUT NUMBER, numdist OUT NUMBER, avglblk OUT NUMBER, avgdblk OUT NUMBER, clstfct OUT NUMBER, indlevel OUT NUMBER, statown VARCHAR2 DEFAULT NULL); DBMS_STATS.GET_INDEX_STATS

Supplied PL/SQL Packages and Types Reference, 9i Release 2 (9.2.0.2)

DBMS_STATS, 2 of 2

ExceptionsORA-20000: Object does not exist or insufficient privileges

Supplied PL/SQL Packages and Types Reference, 9i Release 2 (9.2.0.2)

DBMS_STATS, 2 of 2

ExceptionsORA-20000: Object does not exist or insufficient privileges. ORA-20002: Bad user statistics table; may need to be upgraded.

Supplied PL/SQL Packages and Types Reference, 9i Release 2 (9.2.0.2)

DBMS_STATS, 2 of 2

DELETE_TABLE_STATS ProcedureThis procedure deletes table-related statistics.

Supplied PL/SQL Packages and Types Reference, 9i Release 2 (9.2.0.2)

DBMS_STATS, 2 of 2

SyntaxDBMS_STATS.DELETE_TABLE_STATS ( ownname VARCHAR2, tabname VARCHAR2, partname VARCHAR2 DEFAULT NULL, stattab VARCHAR2 DEFAULT NULL, statid VARCHAR2 DEFAULT NULL, cascade_parts BOOLEAN DEFAULT TRUE, cascade_columns BOOLEAN DEFAULT TRUE, cascade_indexes BOOLEAN DEFAULT TRUE, statown VARCHAR2 DEFAULT NULL, no_invalidate BOOLEAN DEFAULT FALSE);

Supplied PL/SQL Packages and Types Reference, 9i Release 2 (9.2.0.2)

DBMS_STATS, 2 of 2

ParametersTable 70-17  DELETE_TABLE_STATS Procedure Parameters Parameter Description ownname Name of the schema. tabname Name of the table to which this column belongs. colname Name of the column. partname Name of the table partition from which to get the statistics. If the table is partitioned and if partname is NULL, then the statistics are retrieved from the global table level. stattab User stat table identifier

Supplied PL/SQL Packages and Types Reference, 9i Release 2 (9.2.0.2)

DBMS_STATS, 2 of 2

DELETE_DATABASE_STATS ProcedureThis procedure deletes statistics for an entire database.

Supplied PL/SQL Packages and Types Reference, 9i Release 2 (9.2.0.2)

DBMS_STATS, 2 of 2

ParametersTable 70-21 DROP_STAT_TABLE Procedure Parameters Parameter Description ownname Name of the schema. stattab User stat table identifier.

Supplied PL/SQL Packages and Types Reference, 9i Release 2 (9.2.0.2)

DBMS_STATS, 2 of 2

SyntaxDBMS_STATS.EXPORT_INDEX_STATS ( ownname VARCHAR2, indname VARCHAR2, partname VARCHAR2 DEFAULT NULL, stattab VARCHAR2, statid VARCHAR2 DEFAULT NULL, statown VARCHAR2 DEFAULT NULL);

Supplied PL/SQL Packages and Types Reference, 9i Release 2 (9.2.0.2)

DBMS_STATS, 2 of 2

ParametersTable 70-23  EXPORT_INDEX_STATS Procedure Parameters Parameter Description ownname Name of the schema. indname Name of the index. partname Name of the index partition. If the index is partitioned and if partname is NULL, then global and partition index statistics are exported. stattab User stat table identifier describing where to store the statistics. statid Identifier (optional) to associate with





Was this page helpful?

Integrated Cloud Applications & Platform Services