自分用メモ。
- ユーザー統計表を作成する
SQL> EXEC DBMS_STATS.CREATE_STAT_TABLE(ownname=>'OGG_IAU',stattab=>'STAT_TABLE_BEFORE'); SQL> EXEC DBMS_STATS.CREATE_STAT_TABLE(ownname=>'OGG_IAU',stattab =>'STAT_TABLE_AFTER');
- 統計情報を収集する
SQL> BEGIN DBMS_STATS.GATHER_SCHEMA_STATS( OWNNAME => 'OGG_IAU', ESTIMATE_PERCENT => 100, METHOD_OPT => 'FOR ALL COLUMNS SIZE 254', GRANULARITY => 'ALL', CASCADE => TRUE, NO_INVALIDATE => FALSE, FORCE => TRUE ); END; /
- 統計情報をユーザー統計表にエクスポートする
SQL> EXEC DBMS_STATS.EXPORT_SCHEMA_STATS(ownname=>'OGG_IAU',stattab=>'STAT_TABLE_BEFORE');
- 表に行を insert する
SQL> begin for i in 1..100000 loop insert into oam(iau_id) values(i); end loop; end; / SQL> commit;
- 保留統計を取得する
SQL> EXEC DBMS_STATS.SET_SCHEMA_PREFS(ownname=>'OGG_IAU', pname=>'PUBLISH', pvalue=>'FALSE'); SQL> BEGIN DBMS_STATS.GATHER_SCHEMA_STATS( OWNNAME => 'OGG_IAU', ESTIMATE_PERCENT => 100, METHOD_OPT => 'FOR ALL COLUMNS SIZE 254', GRANULARITY => 'ALL', CASCADE => TRUE, NO_INVALIDATE => FALSE, FORCE => TRUE ); END; /
- 保留統計を公開する
SQL> EXEC DBMS_STATS.publish_pending_stats('OGG_IAU', NULL);
- 統計情報をユーザー統計表にエクスポートする
SQL> BEGIN DBMS_STATS.EXPORT_SCHEMA_STATS( ownname => 'OGG_IAU', stattab => 'STAT_TABLE_AFTER' ); END; /
- 統計情報を比較する
SQL> select * from table(DBMS_STATS.DIFF_TABLE_STATS_IN_STATTAB (ownname=>'OGG_IAU',tabname=> 'OAM',stattab1=>'STAT_TABLE_BEFORE',stattab2=>'STAT_TABLE_AFTER')); "REPORT","MAXDIFFPCT" "############################################################################### STATISTICS DIFFERENCE REPORT FOR: ................................. TABLE : OAM OWNER : OGG_IAU SOURCE A : User statistics table STAT_TABLE_BEFORE : Statid : : Owner : OGG_IAU SOURCE B : User statistics table STAT_TABLE_AFTER : Statid : : Owner : OGG_IAU PCTTHRESHOLD : 10 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ TABLE / (SUB)PARTITION STATISTICS DIFFERENCE: ............................................. OBJECTNAME TYP SRC ROWS BLOCKS ROWLEN SAMPSIZE ............................................................................... OAM T A 0 0 0 0 B 100000 244 6 100000 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ COLUMN STATISTICS DIFFERENCE: ............................. COLUMN_NAME SRC NDV DENSITY HIST NULLS LEN MIN MAX SAMPSIZ ............................................................................... IAU_ADDITIONALI A 0 0 NO 0 0 NULL B 0 0 NO 100000 0 NULL IAU_ADMINROLENA A 0 0 NO 0 0 NULL B 0 0 NO 100000 0 NULL IAU_AGENTID A 0 0 NO 0 0 NULL B 0 0 NO 100000 0 NULL IAU_AGENTTYPE A 0 0 NO 0 0 NULL B 0 0 NO 100000 0 NULL IAU_APPLICATION A 0 0 NO 0 0 NULL B 0 0 NO 100000 0 NULL IAU_AUTHENTICAT A 0 0 NO 0 0 NULL B 0 0 NO 100000 0 NULL IAU_AUTHENTICAT A 0 0 NO 0 0 NULL B 0 0 NO 100000 0 NULL IAU_AUTHORIZATI A 0 0 NO 0 0 NULL B 0 0 NO 100000 0 NULL IAU_AUTHORIZATI A 0 0 NO 0 0 NULL B 0 0 NO 100000 0 NULL IAU_CLIENTIPADD A 0 0 NO 0 0 NULL B 0 0 NO 100000 0 NULL IAU_CONSTRAINTT A 0 0 NO 0 0 NULL B 0 0 NO 100000 0 NULL IAU_DATASOURCEN A 0 0 NO 0 0 NULL B 0 0 NO 100000 0 NULL IAU_DATASOURCET A 0 0 NO 0 0 NULL B 0 0 NO 100000 0 NULL IAU_EVENTCATEGO A 0 0 NO 0 0 NULL B 0 0 NO 100000 0 NULL IAU_EVENTTYPE A 0 0 NO 0 0 NULL B 0 0 NO 100000 0 NULL IAU_GENERICATTR A 0 0 NO 0 0 NULL B 0 0 NO 100000 0 NULL IAU_GENERICATTR A 0 0 NO 0 0 NULL B 0 0 NO 100000 0 NULL IAU_GENERICATTR A 0 0 NO 0 0 NULL B 0 0 NO 100000 0 NULL IAU_GENERICATTR A 0 0 NO 0 0 NULL B 0 0 NO 100000 0 NULL IAU_GENERICATTR A 0 0 NO 0 0 NULL B 0 0 NO 100000 0 NULL IAU_HOSTIDENTIF A 0 0 NO 0 0 NULL ",
- CSV に出力して比較する
SQL> set sqlformat csv SQL> spool stat_table_before.csv SQL> select * from STAT_TABLE_BEFORE order by statid, type, version, flags, c1, c2, c3, c4, c5; SQL> spool off SQL> spool stat_table_after.csv SQL> select * from STAT_TABLE_AFTER order by statid, type, version, flags, c1, c2, c3, c4, c5; SQL> spool off SQL> ! $ sort stat_table_before.csv > stat_table_before_sorted.csv $ sort stat_table_after.csv > stat_table_after_sorted.csv $ sdiff -w 170 -s stat_table_before_sorted.csv stat_table_after_sorted.csv|head -30 2,191 rows selected | 4,543 rows selected "","C",6,2,"OAM","","","IAU_ADDITIONALINFO","OGG_IAU",0,0,0,,0,,,0,,,,,13-FEB-16,, | "","C",6,2,"OAM","","","IAU_ADDITIONALINFO","OGG_IAU",0,0,0,,100000,,,0,,,,,13-FEB "","C",6,2,"OAM","","","IAU_ADMINROLENAME","OGG_IAU",0,0,0,,0,,,0,,,,,13-FEB-16,,, | "","C",6,2,"OAM","","","IAU_ADMINROLENAME","OGG_IAU",0,0,0,,100000,,,0,,,,,13-FEB- "","C",6,2,"OAM","","","IAU_AGENTID","OGG_IAU",0,0,0,,0,,,0,,,,,13-FEB-16,,,"","" | "","C",6,2,"OAM","","","IAU_AGENTID","OGG_IAU",0,0,0,,100000,,,0,,,,,13-FEB-16,,," "","C",6,2,"OAM","","","IAU_AGENTTYPE","OGG_IAU",0,0,0,,0,,,0,,,,,13-FEB-16,,,""," | "","C",6,2,"OAM","","","IAU_AGENTTYPE","OGG_IAU",0,0,0,,100000,,,0,,,,,13-FEB-16,, "","C",6,2,"OAM","","","IAU_APPLICATIONDOMAINNAME","OGG_IAU",0,0,0,,0,,,0,,,,,13-F | "","C",6,2,"OAM","","","IAU_APPLICATIONDOMAINNAME","OGG_IAU",0,0,0,,100000,,,0,,,, "","C",6,2,"OAM","","","IAU_AUTHENTICATIONPOLICYID","OGG_IAU",0,0,0,,0,,,0,,,,,13- | "","C",6,2,"OAM","","","IAU_AUTHENTICATIONPOLICYID","OGG_IAU",0,0,0,,100000,,,0,,, "","C",6,2,"OAM","","","IAU_AUTHENTICATIONSCHEMEID","OGG_IAU",0,0,0,,0,,,0,,,,,13- | "","C",6,2,"OAM","","","IAU_AUTHENTICATIONSCHEMEID","OGG_IAU",0,0,0,,100000,,,0,,, "","C",6,2,"OAM","","","IAU_AUTHORIZATIONPOLICYID","OGG_IAU",0,0,0,,0,,,0,,,,,13-F | "","C",6,2,"OAM","","","IAU_AUTHORIZATIONPOLICYID","OGG_IAU",0,0,0,,100000,,,0,,,, "","C",6,2,"OAM","","","IAU_AUTHORIZATIONSCHEME","OGG_IAU",0,0,0,,0,,,0,,,,,13-FEB | "","C",6,2,"OAM","","","IAU_AUTHORIZATIONSCHEME","OGG_IAU",0,0,0,,100000,,,0,,,,,1 "","C",6,2,"OAM","","","IAU_CLIENTIPADDRESS","OGG_IAU",0,0,0,,0,,,0,,,,,13-FEB-16, | "","C",6,2,"OAM","","","IAU_CLIENTIPADDRESS","OGG_IAU",0,0,0,,100000,,,0,,,,,13-FE "","C",6,2,"OAM","","","IAU_CONSTRAINTTYPE","OGG_IAU",0,0,0,,0,,,0,,,,,13-FEB-16,, | "","C",6,2,"OAM","","","IAU_CONSTRAINTTYPE","OGG_IAU",0,0,0,,100000,,,0,,,,,13-FEB "","C",6,2,"OAM","","","IAU_DATASOURCENAME","OGG_IAU",0,0,0,,0,,,0,,,,,13-FEB-16,, | "","C",6,2,"OAM","","","IAU_DATASOURCENAME","OGG_IAU",0,0,0,,100000,,,0,,,,,13-FEB "","C",6,2,"OAM","","","IAU_DATASOURCETYPE","OGG_IAU",0,0,0,,0,,,0,,,,,13-FEB-16,, | "","C",6,2,"OAM","","","IAU_DATASOURCETYPE","OGG_IAU",0,0,0,,100000,,,0,,,,,13-FEB "","C",6,2,"OAM","","","IAU_EVENTCATEGORY","OGG_IAU",0,0,0,,0,,,0,,,,,13-FEB-16,,, | "","C",6,2,"OAM","","","IAU_EVENTCATEGORY","OGG_IAU",0,0,0,,100000,,,0,,,,,13-FEB- "","C",6,2,"OAM","","","IAU_EVENTTYPE","OGG_IAU",0,0,0,,0,,,0,,,,,13-FEB-16,,,""," | "","C",6,2,"OAM","","","IAU_EVENTTYPE","OGG_IAU",0,0,0,,100000,,,0,,,,,13-FEB-16,, "","C",6,2,"OAM","","","IAU_GENERICATTRIBUTE1","OGG_IAU",0,0,0,,0,,,0,,,,,13-FEB-1 | "","C",6,2,"OAM","","","IAU_GENERICATTRIBUTE1","OGG_IAU",0,0,0,,100000,,,0,,,,,13- "","C",6,2,"OAM","","","IAU_GENERICATTRIBUTE2","OGG_IAU",0,0,0,,0,,,0,,,,,13-FEB-1 | "","C",6,2,"OAM","","","IAU_GENERICATTRIBUTE2","OGG_IAU",0,0,0,,100000,,,0,,,,,13- "","C",6,2,"OAM","","","IAU_GENERICATTRIBUTE3","OGG_IAU",0,0,0,,0,,,0,,,,,13-FEB-1 | "","C",6,2,"OAM","","","IAU_GENERICATTRIBUTE3","OGG_IAU",0,0,0,,100000,,,0,,,,,13- "","C",6,2,"OAM","","","IAU_GENERICATTRIBUTE4","OGG_IAU",0,0,0,,0,,,0,,,,,13-FEB-1 | "","C",6,2,"OAM","","","IAU_GENERICATTRIBUTE4","OGG_IAU",0,0,0,,100000,,,0,,,,,13- "","C",6,2,"OAM","","","IAU_GENERICATTRIBUTE5","OGG_IAU",0,0,0,,0,,,0,,,,,13-FEB-1 | "","C",6,2,"OAM","","","IAU_GENERICATTRIBUTE5","OGG_IAU",0,0,0,,100000,,,0,,,,,13- "","C",6,2,"OAM","","","IAU_HOSTIDENTIFIERNAME","OGG_IAU",0,0,0,,0,,,0,,,,,13-FEB- | "","C",6,2,"OAM","","","IAU_HOSTIDENTIFIERNAME","OGG_IAU",0,0,0,,100000,,,0,,,,,13 "","C",6,2,"OAM","","","IAU_IDENTITYDOMAIN","OGG_IAU",0,0,0,,0,,,0,,,,,13-FEB-16,, | "","C",6,2,"OAM","","","IAU_IDENTITYDOMAIN","OGG_IAU",0,0,0,,100000,,,0,,,,,13-FEB "","C",6,2,"OAM","","","IAU_ID","OGG_IAU",0,0,0,,0,,,0,,,,,13-FEB-16,,,"","" | "","C",6,2,"OAM","","","IAU_ID","OGG_IAU",100000,0.00001,100000,100000,0,1,100000, "","C",6,2,"OAM","","","IAU_IMPERSONATOR","OGG_IAU",0,0,0,,0,,,0,,,,,13-FEB-16,,," | "","C",6,2,"OAM","","","IAU_ID","OGG_IAU",100000,0.00001,100000,100000,0,1,100000, "","C",6,2,"OAM","","","IAU_INSTANCENAME","OGG_IAU",0,0,0,,0,,,0,,,,,13-FEB-16,,," | "","C",6,2,"OAM","","","IAU_ID","OGG_IAU",100000,0.00001,100000,100000,0,1,100000, "","C",6,2,"OAM","","","IAU_NEWATTRIBUTES","OGG_IAU",0,0,0,,0,,,0,,,,,13-FEB-16,,, | "","C",6,2,"OAM","","","IAU_ID","OGG_IAU",100000,0.00001,100000,100000,0,1,100000, "","C",6,2,"OAM","","","IAU_NEWSETTINGS","OGG_IAU",0,0,0,,0,,,0,,,,,13-FEB-16,,,"" | "","C",6,2,"OAM","","","IAU_ID","OGG_IAU",100000,0.00001,100000,100000,0,1,100000, "","C",6,2,"OAM","","","IAU_OLDATTRIBUTES","OGG_IAU",0,0,0,,0,,,0,,,,,13-FEB-16,,, | "","C",6,2,"OAM","","","IAU_ID","OGG_IAU",100000,0.00001,100000,100000,0,1,100000, "","C",6,2,"OAM","","","IAU_OLDSETTINGS","OGG_IAU",0,0,0,,0,,,0,,,,,13-FEB-16,,,"" | "","C",6,2,"OAM","","","IAU_ID","OGG_IAU",100000,0.00001,100000,100000,0,1,100000,
環境
sql OGG_IAU/oracle@192.168.56.101:1521/orcl SQLcl: Release 4.2.0.15.177.0246 RC on Sat Feb 13 16:25:04 2016 Copyright (c) 1982, 2016, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> !cat /etc/oracle-release Oracle Linux Server release 6.6 SQL> !uname -a Linux yazekats-linux.oracle.com 2.6.39-400.17.1.el6uek.x86_64 #1 SMP Fri Feb 22 18:16:18 PST 2013 x86_64 x86_64 x86_64 GNU/Linux
参考
追記(2016/02/14):
- 保留統計を公開しても、公開されている統計のみに存在する統計は消えないことを確認した、
SQL> select count(1) from DBA_COL_PENDING_STATS COUNT(1) ---------- 1590 SQL> EXEC DBMS_STATS.SET_SCHEMA_PREFS(ownname=>'OGG_IAU', pname=>'PUBLISH', pvalue=>'TRUE'); PL/SQL procedure successfully completed. SQL> select count(1) from dba_tab_statistics where owner = 'OGG_IAU'; COUNT(1) ---------- 30 SQL> select count(1) from dba_tab_col_statistics where owner = 'OGG_IAU'; COUNT(1) ---------- 0 SQL> EXEC DBMS_STATS.DELETE_SCHEMA_STATS('OGG_IAU'); PL/SQL procedure successfully completed. SQL> select count(1) from dba_tab_statistics where owner = 'OGG_IAU'; COUNT(1) ---------- 30 SQL> select count(1) from dba_tab_col_statistics where owner = 'OGG_IAU'; COUNT(1) ---------- 0 SQL> create table added_table(c1 number, c2 number); Table ADDED_TABLE created. SQL> BEGIN 2 DBMS_STATS.GATHER_TABLE_STATS( 3 OWNNAME => 'OGG_IAU', 4 TABNAME => 'ADDED_TABLE', 5 ESTIMATE_PERCENT => 100, 6 METHOD_OPT => 'FOR ALL COLUMNS SIZE 254', 7 GRANULARITY => 'ALL', 8 CASCADE => TRUE, 9 NO_INVALIDATE => FALSE, 10 FORCE => TRUE 11 ); 12 END; 13 / PL/SQL procedure successfully completed. SQL> select count(1) from dba_tab_statistics where owner = 'OGG_IAU'; COUNT(1) ---------- 31 SQL> select count(1) from dba_tab_col_statistics where owner = 'OGG_IAU'; COUNT(1) ---------- 2 SQL> EXEC DBMS_STATS.publish_pending_stats('OGG_IAU', NULL); PL/SQL procedure successfully completed. SQL> select count(1) from dba_tab_statistics where owner = 'OGG_IAU'; COUNT(1) ---------- 31 SQL> select count(1) from dba_tab_col_statistics where owner = 'OGG_IAU'; COUNT(1) ---------- 1592 SQL>
