统计信息备份
来源:互联网 发布:phpstorm配置mysql环境 编辑:程序博客网 时间:2024/06/02 06:20
下面例子中,都是使用SEC用户作为测试用户
例子中函数只使用常用参数,部分参数未使用,可以查看oracle 在线帮助手册
位置:
步骤一:创建储统备份计信息的表
SQL> conn sec/sec
Connected.
SQL>
SQL>exec dbms_stats.create_stat_table(ownname => 'sec',stattab => 'stat_backup_table') ;
PL/SQL procedure successfully completed.
SQL>select * from stat_backup_table;
no rows selected
SQL>
这个时候只是在SEC用户下创建一个没有记录的表stat_backup_table。
步骤二:保存统计信息
SQL>exec dbms_stats.export_schema_stats(ownname => 'sec',stattab => 'stat_backup_table') ;
PL/SQL procedure successfully completed.
SQL>
SQL>select count(*) from stat_backup_table;
COUNT(*)
----------
18
SQL>
这个时候,就会把SEC下面所有统计信息备份到表stat_backup_table。
步骤三:查看原来的表和索引的统计信息
列的统计信息
SQL>select t.table_name,t.column_name,t.last_analyzed from dba_tab_col_statistics t
2 where t.owner='SEC' AND t.table_name in ('COLOCATED','DISORGANIZED','TESTT');
TABLE_NAME COLUMN_NAME LAST_ANAL
------------------------------ ------------------------------ ---------
TESTT FLAG 30-DEC-13
TESTT ID 30-DEC-13
DISORGANIZED Y 23-DEC-13
DISORGANIZED X 23-DEC-13
COLOCATED Y 23-DEC-13
COLOCATED X 23-DEC-13
6 rows selected.
索引的统计信息
SQL> set linesize 300
SQL>select t.index_name,table_owner,table_name,last_analyzed
2 from dba_indexes t where t.OWNER='SEC'
3 and t.table_name in ('COLOCATED','DISORGANIZED','TESTT');
INDEX_NAME TABLE_OWNER TABLE_NAME LAST_ANAL
------------------------------ ------------------------------ ------------------------------ ---------
COLOCATED_PK SEC COLOCATED 23-DEC-13
DISORGANIZED_PK SEC DISORGANIZED 23-DEC-13
INX_TESTT SEC TESTT 30-DEC-13
SQL>
表的统计信息
SQL>
SQL>select t.TABLE_NAME, t.LAST_ANALYZED
2 from dba_tables t where t.OWNER = 'SEC'
3 and t.table_name in ('COLOCATED', 'DISORGANIZED', 'TESTT');
TABLE_NAME LAST_ANAL
------------------------------ ---------
TESTT 30-DEC-13
DISORGANIZED 23-DEC-13
COLOCATED 23-DEC-13
SQL>
上面3个结果主要看LAST_ANAL这列,在下面步骤要重新生成统计信息,这样LAST_ANAL的时候会有有变化
步骤四:重新生成统计信息,查看LAST_ANAL的变化
SQL> exec dbms_stats.gather_table_stats(ownname => 'SEC',tabname => 'COLOCATED',estimate_percent => 10,method_opt=> 'for all columns') ;
PL/SQL procedure successfully completed.
SQL>
SQL> exec dbms_stats.gather_table_stats(ownname => 'SEC',tabname => 'DISORGANIZED',estimate_percent => 10,
method_opt=> 'for all columns') ;
PL/SQL procedure successfully completed.
SQL>
SQL> exec dbms_stats.gather_table_stats(ownname => 'SEC',tabname => 'TESTT',estimate_percent => 10,method_opt=> 'for all columns') ;
PL/SQL procedure successfully completed.
SQL>
SQL>
重新收集3个表的统计信息,查询相关统计信息视图
列的统计信息
SQL> select t.table_name,t.column_name,t.last_analyzed from dba_tab_col_statistics t
2 where t.owner='SEC' AND t.table_name in ('COLOCATED','DISORGANIZED','TESTT');
TABLE_NAME COLUMN_NAME LAST_ANAL
------------------------------ ------------------------------ ---------
TESTT FLAG 17-DEC-14
TESTT ID 17-DEC-14
DISORGANIZED Y 17-DEC-14
DISORGANIZED X 17-DEC-14
COLOCATED Y 17-DEC-14
COLOCATED X 17-DEC-14
6 rows selected.
SQL>
索引的统计信息
SQL> select t.index_name,table_owner,table_name,last_analyzed
2 from dba_indexes t where t.OWNER='SEC'
3 and t.table_name in ('COLOCATED','DISORGANIZED','TESTT');
INDEX_NAME TABLE_OWNER TABLE_NAME LAST_ANAL
------------------------------ ------------------------------ ------------------------------ ---------
COLOCATED_PK SEC COLOCATED 17-DEC-14
DISORGANIZED_PK SEC DISORGANIZED 17-DEC-14
INX_TESTT SEC TESTT 17-DEC-14
SQL>
表的统计信息
SQL>
SQL> select t.TABLE_NAME, t.LAST_ANALYZED
2 from dba_tables t where t.OWNER = 'SEC'
3 and t.table_name in ('COLOCATED', 'DISORGANIZED', 'TESTT');
TABLE_NAME LAST_ANAL
------------------------------ ---------
TESTT 17-DEC-14
DISORGANIZED 17-DEC-14
COLOCATED 17-DEC-14
SQL>
可以看到相关统计信息的LAST_ANAL字段时间已经变化
步骤五:还原备份的统计信息
导入单个索引的信息
SQL> exec dbms_stats.import_index_stats(ownname => 'SEC',indname => 'COLOCATED_PK',stattab => 'stat_backup_table') ;
PL/SQL procedure successfully completed.
SQL>
SQL> select t.index_name,table_owner,table_name,last_analyzed
2 from dba_indexes t where t.OWNER='SEC'
3 and t.table_name in ('COLOCATED','DISORGANIZED','TESTT');
INDEX_NAME TABLE_OWNER TABLE_NAME LAST_ANAL
------------------------------ ------------------------------ ------------------------------ ---------
COLOCATED_PK SEC COLOCATED 23-DEC-13
DISORGANIZED_PK SEC DISORGANIZED 17-DEC-14
INX_TESTT SEC TESTT 17-DEC-14
SQL>
导入单个表的信息
SQL>
SQL> exec dbms_stats.import_table_stats(ownname => 'SEC',tabname => 'COLOCATED',stattab => 'stat_backup_table') ;
PL/SQL procedure successfully completed.
SQL>
SQL> select t.TABLE_NAME, t.LAST_ANALYZED
2 from dba_tables t where t.OWNER = 'SEC'
3 and t.table_name in ('COLOCATED', 'DISORGANIZED', 'TESTT');
TABLE_NAME LAST_ANAL
------------------------------ ---------
TESTT 17-DEC-14
DISORGANIZED 17-DEC-14
COLOCATED 23-DEC-13
SQL>
SQL> select t.table_name,t.column_name,t.last_analyzed from dba_tab_col_statistics t
2 where t.owner='SEC' AND t.table_name in ('COLOCATED','DISORGANIZED','TESTT');
TABLE_NAME COLUMN_NAME LAST_ANAL
------------------------------ ------------------------------ ---------
TESTT FLAG 17-DEC-14
TESTT ID 17-DEC-14
DISORGANIZED Y 17-DEC-14
DISORGANIZED X 17-DEC-14
COLOCATED Y 23-DEC-13
COLOCATED X 23-DEC-13
6 rows selected.
如果一个一个的表比较麻烦,可以导入刚才所有的备份信息
SQL>exec dbms_stats.import_schema_stats(ownname => 'SEC',stattab => 'stat_backup_table');
PL/SQL procedure successfully completed.
SQL> select t.table_name,t.column_name,t.last_analyzed from dba_tab_col_statistics t
2 where t.owner='SEC' AND t.table_name in ('COLOCATED','DISORGANIZED','TESTT');
TABLE_NAME COLUMN_NAME LAST_ANAL
------------------------------ ------------------------------ ---------
TESTT FLAG 30-DEC-13
TESTT ID 30-DEC-13
DISORGANIZED Y 23-DEC-13
DISORGANIZED X 23-DEC-13
COLOCATED Y 23-DEC-13
COLOCATED X 23-DEC-13
6 rows selected.
SQL>
SQL> select t.TABLE_NAME, t.LAST_ANALYZED
2 from dba_tables t where t.OWNER = 'SEC'
3 and t.table_name in ('COLOCATED', 'DISORGANIZED', 'TESTT');
TABLE_NAME LAST_ANAL
------------------------------ ---------
TESTT 30-DEC-13
DISORGANIZED 23-DEC-13
COLOCATED 23-DEC-13
SQL>
SQL> select t.index_name,table_owner,table_name,last_analyzed
2 from dba_indexes t where t.OWNER='SEC'
3 and t.table_name in ('COLOCATED','DISORGANIZED','TESTT');
INDEX_NAME TABLE_OWNER TABLE_NAME LAST_ANAL
------------------------------ ------------------------------ ------------------------------ ---------
COLOCATED_PK SEC COLOCATED 23-DEC-13
DISORGANIZED_PK SEC DISORGANIZED 23-DEC-13
INX_TESTT SEC TESTT 30-DEC-13
SQL>
- 备份恢复统计信息
- 统计信息备份
- 备份恢复统计信息
- Oracle 统计信息备份/表分析
- oracle 收集统计信息(除去备份表)
- 用dbms_stats的备份表,备份统计信息
- 统计信息!
- 统计信息
- 统计信息
- 统计信息
- 统计信息
- Oracle 统计信息(备份/导入)表分析-[注意要刷缓存]
- VBS脚本备份桌面文档到指定目录,同时统计文档的信息。
- 备份PuTTY信息?PuTTY信息备份
- 系统统计信息与对象统计信息
- 获取DB备份信息
- 资源信息备份
- [信息备份库] WinCE
- ios FMDB类库的使用 学习笔记
- 精美的vim配置
- mysql在win7下重装卡在start service配置界面的解决方案
- sdfsdf
- C语言实现ini解析函数 getPrivateProfileString
- 统计信息备份
- kafuka get started
- 电子圈存的秘密——去中心化的一个例子
- 【JAVA学习】找不到或无法加载主类
- One-to-One(共享主键)
- Android SDK 镜像,GFW内安装及更新方法
- DMA TCM CACHE
- Android应用开发SharedPreferences存储数据的使用方法
- web项目部署到tomcat根目录