統計情報と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計画ディレクティブ

• 特定スキーマの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;
/

• 特定スキーマの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 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;
/

• 特定スキーマの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 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';