ablog

不器用で落着きのない技術者のメモ

統計情報とSQL計画ディレクティブを操作するコマンド集

統計情報

  • ユーザー統計表を作成する
exec dbms_stats.create_stat_table(ownname=>'SCOTT', stattab=>'STAT_TAB_20160223');
  • ユーザー統計表に表の統計をエクスポートする。
exec dbms_stats.export_table_stats(ownname=>'SCOTT', tabname=>'EMP', stattab=>'STAT_TAB_20160223', cascade=>true);
  • ユーザー統計表のスキーマ名と表名を変更する
update STAT_TAB set c1 = 'EMP', c5='SCOTT';
commit;
  • ユーザー統計をインポートする
exec dbms_stats.import_table_stats(ownname=>'SCOTT', tabname=>'EMP', stattab=>'STAT_TAB_MOD', cascade=>true, no_invalidate=>false);
  • 表の統計を削除する
exec dbms_stats.delete_table_stats(ownname=>'SCOTT', tabname=>'EMP'e, no_invalidate=>false);
  • 拡張統計を削除する
exec dbms_stats.drop_extended_stats(ownname=>'SCOTT', tabname=>'EMP', extension=>'(PER_NAME_ENT_YEAR,ACTIVE_FLG)');
exec dbms_stats.delete_column_stats(ownname=>'SCOTT', tabname=>'EMP', colname=>'X_PARTITION_CONC_KEY', col_stat_type=>'HISTOGRAM'e, no_invalidate=>false);
  • 統計情報をリストアする。
exec dbms_stats.restore_table_stats(ownname=>'SCOTT', tabname=>'EMP', as_of_timestamp=>'2016-03-02 12:00:00.000000000', no_invalidate=>false);
  • プリファレンスを確認する
select dbms_stats.get_prefs(pname=>'publish', ownname=>'SCOTT', tabname=>'EMP') from dual;

実行計画

  • 共有カーソルをパージする
col hash_value for 99999999999999
col address  for a100
select hash_value, address from v$sqlarea where sql_id = 'cwxqhpbqj4vzy';
exec sys.dbms_shared_pool.purge('0000003A9155F7F8, 3977408510','C');
  • 実行統計取得
alter session set statistics_level=all;
select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

SQL計画ディレクティブ

DECLARE
	CURSOR CU IS SELECT DISTINCT A.DIRECTIVE_ID DIRECTIVE_ID
		FROM DBA_SQL_PLAN_DIRECTIVES A, DBA_SQL_PLAN_DIR_OBJECTS B
	WHERE B.OWNER = 'SCOTT'
		AND A.DIRECTIVE_ID = B.DIRECTIVE_ID;
BEGIN
	FOR REC IN CU LOOP
		BEGIN
			DBMS_SPD.DROP_SQL_PLAN_DIRECTIVE(REC.DIRECTIVE_ID);
		EXCEPTION WHEN OTHERS THEN
			DBMS_OUTPUT.PUT_LINE('Failed to drop DIRECTIVE_ID:'||REC.DIRECTIVE_ID||', '||SQLCODE||':'||SQLERRM);
		END;
	END LOOP;
END;
/
DECLARE
	CURSOR CU IS SELECT DISTINCT A.DIRECTIVE_ID DIRECTIVE_ID
		FROM DBA_SQL_PLAN_DIRECTIVES A, DBA_SQL_PLAN_DIR_OBJECTS B
	WHERE OWNER = 'SCOTT'
BEGIN
	FOR REC IN CU LOOP
		BEGIN
			DBMS_SPD.ALTER_SQL_PLAN_DIRECTIVE(REC.DIRECTIVE_ID,'ENABLED','NO');
		EXCEPTION WHEN OTHERS THEN
			DBMS_OUTPUT.PUT_LINE('Failed to disable DIRECTIVE_ID:'||REC.DIRECTIVE_ID||', '||SQLCODE||':'||SQLERRM);
		END;
	END LOOP;
END;
/
DECLARE
	CURSOR CU IS SELECT DISTINCT A.DIRECTIVE_ID DIRECTIVE_ID
		FROM DBA_SQL_PLAN_DIRECTIVES A, DBA_SQL_PLAN_DIR_OBJECTS B
	WHERE OWNER = 'SCOTT'
BEGIN
	FOR REC IN CU LOOP
		BEGIN
			DBMS_SPD.ALTER_SQL_PLAN_DIRECTIVE(REC.DIRECTIVE_ID,'ENABLED','YES');
		EXCEPTION WHEN OTHERS THEN
			DBMS_OUTPUT.PUT_LINE('Failed to disable DIRECTIVE_ID:'||REC.DIRECTIVE_ID||', '||SQLCODE||':'||SQLERRM);
		END;
	END LOOP;
END;
/
  • SQL計画ディレクティブをインポートする
impdp SCOTT/Welcome1 directory=DP_DIR dumpfile=PROD_SQL_DIRECTIVE_20160210.dmp logfile=imp_PROD_SQL_DIRECTIVE_20160224.log;
select dbms_spd.unpack_stgtab_directive(table_name=>'PROD_SQL_DIRECTIVE_20160210',table_owner=>'SCOTT') from dual;
  • SQL計画ディレクティブをバックアップする
begin
  dbms_spd.create_stgtab_directive(
    table_name => 'DEV_SQL_DIRECTIVE_20160224',
    table_owner => 'SCOTT',
    tablespace_name => 'USERS');
end;
/
select dbms_spd.pack_stgtab_directive(table_name=>'DEV_SQL_DIRECTIVE_20160224',table_owner=>'SCOTT') from dual;
select count(1) from DEV_SQL_DIRECTIVE_20160224;
exec dbms_stats.gather_table_stats(ownname=>'SCOTT', tabname=>'DEV_SQL_DIRECTIVE_20160224', cascade=>true, no_invalidate=>false);
!expdp SCOTT/ tables=DEV_SQL_DIRECTIVE_20160224 directory=dp_dir dumpfile=DEV_SQL_DIRECTIVE_20160224.dmp logfile=exp_DEV_SQL_DIRECTIVE_20160224.log

メモ

set echo on
set time on
set timing on
set pagesize 50000
set linesize 32767
alter session set current_schema=SCOTT;
  • OPTIMIZER_FEATURES_ENABLE変更
alter session set OPTIMIZER_FEATURES_ENABLE = '11.2.0.3';