oracledbms_stats统计信息管理(七)
CT_ID 1 NUMBER(22)
----------------------------------------------------
3.删除统计信息
----------------------------------------------------
DELETE_COLUMN_STATS Procedure
DELETE_DATABASE_STATS Procedure
DELETE_DICTIONARY_STATS Procedure
DELETE_FIXED_OBJECTS_STATS Procedure
DELETE_INDEX_STATS Procedure
DELETE_SCHEMA_STATS Procedure
DELETE_SYSTEM_STATS Procedure
DELETE_TABLE_STATS Procedure
---3.1 DBMS_STATS.DELETE_TABLE_STATS
DBMS_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 to_no_invalidate_type (
get_param('NO_INVALIDATE')),
force BOOLEAN DEFAULT FALSE);
--删除统计信息表中指定表的分析信息
BEGIN
DBMS_STATS.delete_table_stats(ownname => 'JINRILOG',tabname => 'ORDERLOG');
END;
/
----3.2 DBMS_STATS.DELETE_SCHEMA_STATS
DBMS_STATS.DELETE_SCHEMA_STATS (
ownname VARCHAR2,
stattab VARCHAR2 DEFAULT NULL,
statid VARCHAR2 DEFAULT NULL,
statown VARCHAR2 DEFAULT NULL,
no_invalidate BOOLEAN DEFAULT to_no_invalidate_type (
get_param('NO_INVALIDATE')),
force BOOLEAN DEFAULT FALSE);
--删除指定schema的分析信息
BEGIN
DBMS_STATS.DELETE_SCHEMA_STATS(ownname => 'JINRILOG',tabname => 'ORDERLOG');
END;
/
----------------------------------------------------
4.传输统计信息
----------------------------------------------------
/********************************
DBMS_STATS.CREATE_STAT_TABLE (
ownname VARCHAR2,
stattab VARCHAR2,
tblspace VARCHAR2 DEFAULT NULL);
DBMS_STATS.EXPORT_TABLE_STATS (
ownname VARCHAR2,
tabname VARCHAR2,
partname VARCHAR2 DEFAULT NULL,
stattab VARCHAR2,
statid VARCHAR2 DEFAULT NULL,
cascade BOOLEAN DEFAULT TRUE,
statown VARCHAR2 DEFAULT NULL);
DBMS_STATS.EXPORT_SCHEMA_STATS (
ownname VARCHAR2,
stattab VARCHAR2,
statid VARCHAR2 DEFAULT NULL,
statown VARCHAR2 DEFAULT NULL);
DBMS_STATS.EXPORT_SYSTEM_STATS (
stattab VARCHAR2,
statid VARCHAR2 DEFAULT NULL,
statown VARCHAR2 DEFAULT NULL);
DBMS_STATS.IMPORT_TABLE_STATS (
ownname VARCHAR2,
tabname VARCHAR2,
partname VARCHAR2 DEFAULT NULL,
stattab VARCHAR2,
statid VARCHAR2 DEFAULT NULL,
cascade BOOLEAN DEFAULT TRUE,
statown VARCHAR2 DEFAULT NULL,
no_invalidate BOOLEAN DEFAULT to_no_invalidate_type(
get_param('NO_INVALIDATE')),
force BOOLEAN DEFAULT FALSE);
DBMS_STATS.IMPORT_SCHEMA_STATS (
ownname VARCHAR2,
stattab VARCHAR2,
statid VARCHAR2 DEFAULT NULL,
statown VARCHAR2 DEFAULT NULL,
no_invalidate BOOLEAN DEFAULTto_no_invalidate_type(
get_param('NO_INVALIDATE')),
force BOOLEAN DEFAULT FALSE);
DBMS_STATS.IMPORT_SYSTEM_STATS (
stattab VARCHAR2,
statid VARCHAR2 DEFAULT NULL,
statown VARCHAR2 DEFAULT NULL);
********************************/
--步骤1：在使用DBMS_STATS分析表的时候，我们经常要保存之前的分析，以防分析后导致系统性能低下然后进行快速恢复。
首先创建一个分析表，该表是用来保存之前的分析值。
begin
dbms_stats.create_stat_table(ownname =>
'JINRILOG',stattab => 'STAT_TABLE');
end;
/
分析表信息
BEGIN
--DBMS_STATS.delete_table_stats(ownname => 'JINRILOG',tabname => 'ORDERLOG');
DBMS_STATS.gather_table_stats(ownname => 'JINRILOG',tabname => 'ORDERLOG');