データベースの勉強

パフォマンスチューニング
DB管理
リカバリ-


パフォマンスチューニング


インデックス
複数の列に対するインデックスを作成する場合は
検索条件に使用される可能性の高い列から記述

create index インデックス名 on テーブル名(検索条件に使用される可能性の高い列,列);
alter table テーブル名 add primary key(検索条件に使用される可能性の高い列,列);


複数の表を結合する列にもインデックスは有効（結合のパフォマンスが向上する）

ヒント句
 /*+ INDEX(INDEX名) */
 
---------------------------------------------------------------------------
shared_poolの取得失敗数確認
select FREE_SPACE,REQUEST_FAILURES from v$shared_pool_reserved;

ライブラリーキャッシュのヒット率
select NAMESPACE,PINS,RELOADS,GETHITRATIO from V$LIBRARYCACHE;

ディクショナリキャッシュのヒット率
select PARAMETER,GETS,GETMISSES from v$rowcache;

ディスクソートの割合
SELECT A.VALUE * 100 / (A.VALUE+B.VALUE) MEM_SORT_RATE FROM V$SYSSTAT A,V$SYSSTAT B
WHERE A.NAME='sorts (memory)' AND B.NAME='sorts (disk)';

アナライズしていないテーブル、INDEXの確認
select OWNER,TABLE_NAME,LAST_ANALYZED from dba_tables
where LAST_ANALYZED is null;

select OWNER,INDEX_NAME,LAST_ANALYZED from dba_indexes
where LAST_ANALYZED is null;


SQLトレース(実行計画確認)
EXECUTE sys.dbms_system.set_sql_trace_in_session(sid ,シリアル ,TRUE);
または
ALTER SESSION SET SQL_TRACE=TRUE;
実行計画を確認したいSQL文実行後、セッション切断。 　
oracle% tkprof udump配下にできた.trcファイル 実行計画出力ファイル EXPLAIN= 対象ユーザ名/パスワード sys=no 

バインド変数使用時の実行計画の実際の値を知りたい時(開発者向け)
ALTER SESSION SET EVENTS '10046 trace name context forever, level 4'; (待機イベントなら LEVEL 8 を指定)
SQL文
ALTER SESSION SET EVENTS '10046 trace name context off';


DB管理


ユーザロック解除
ALTER USER ユーザ名 ACCOUNT UNLOCK;

ソートエリアの設定
alter system set sort_area_size=20971520 DEFERRED;
alter system set sort_area_retained_size=20971520 DEFERRED;

パーティションテーブル関係
select OWNER,TABLE_NAME,PARTITIONING_TYPE from DBA_PART_TABLES;
select OWNER,INDEX_NAME,TABLE_NAME,PARTITIONING_TYPE from DBA_PART_INDEXES;
alter table <表名> truncate partition <パーティション名1> ;

キャラクターセット
select * from NLS_DATABASE_PARAMETERS where PARAMETER = 'NLS_NCHAR_CHARACTERSET';

TEMPセグメント開放 ディクショナリ管理時のみ使用可
alter tablespace temp default storage ( next 100m);

テーブル複製作成(データは無し、テーブル定義のみ)
CREATE TABLE 新表 AS SELECT * FROM 元表 WHERE 1=2;

PLSQL
PLSQLソースの確認
select TEXT from dba_source
where NAME = 'PLSQLプログラム名'

DBブロック確認(ほとんど使いません^ ^; )
SELECT SEGMENT_NAME, HEADER_FILE, HEADER_BLOCK
FROM DBA_SEGMENTS
WHERE SEGMENT_NAME = '    ';

2フェーズコミットの確認
select * from dba_2pc_pending;

データベースリンク
select OWNER,DB_LINK,USERNAME,HOST from DBA_DB_LINKS;

alter session close database link dblink名;

リスナー パスワード設定
lsnrctl
> change_password
> save_config


STATSPACK snapid 設定
exec statspack.snap

ロック情報
select sid,serial#,substr(machine,1,11),PROGRAM,status from V$SESSION
where sid =ANY (select session_id from V$LOCKED_OBJECT);

ロールバックセグメント情報
select name,RSSIZE,HWMSIZE,OPTSIZE,SHRINKS,STATUS from v$rollstat,v$rollname
where v$rollname.USN = v$rollstat.USN;

RBSのシュリンク
alter rollback segment "ロールバックセグメント" shrink to "num"m

表領域の空き容量
select TABLESPACE_NAME,sum(BYTES/1024/1024) from dba_free_space
group by TABLESPACE_NAME having TABLESPACE_NAME like 'R%';　← 表領域名 Rで始まるもの

統計取得
analyze table &tablename  COMPUTE  STATISTICS;
analyze table &tablename  estimate statistics sample 10 percent;
exec dbms_stats.gather_schema_stats('&username',5,options=>'GATHER')

システム権限
select * from user_sys_privs;
select * from user_role_privs;

制約
dba_constraints

UTL_FILE_DIRの設定
initSID.ora に追加
UTL_FILE_DIR = /パス/

oratab
/etc/oratab
$ORACLE_SID:$ORACLE_HOME:[Y|N]
例
orcl:/opt/oracle/product:Y

NLS_LANG
JAPANESE_JAPAN.JA16EUC
JAPANESE_JAPAN.JA16SJIS
JAPANESE_JAPAN.AL32UTF8 

REDOログ
REDOログのメンバーは、メンバーの状態に関わらず
メンバーを追加可能

トリガー

sql loader

sqlplus　tips

「&&置換変数」
 毎回、値をに入力をするではなく、初回のみ入力して
 以前の変数を再利用することができます
 &置換変数 再実行ごとに値を入力
 &&置換変数 初回実行時のみ値入力、以降実行時、値入力不要。
 
 置換変数 で文字型を参照する場合は '&置換変数' で囲む。

リカバリ-

8Iまでのリカバリ
SVRMGR> set autorecovery on
Autorecovery                    ON
SVRMGR> recover database;
媒体回復が完了しました。
SVRMGR> alter database open;
文が処理されました。

データファイル単位のリカバリー
recover automatic datafile 'XXX.dbf';

制御ファイル作成SQL出力 udumpに出力
alter database backup controlfile to trace;
制御ファイルバイナリ
alter database backup controlfile to '/test.dmp';

リカバリーマネージャー
RMAN> configure channel device type disk format 'c:/ora_df%t_%s_%p';
RMAN> show all
RMAN>

障害対応 切分け

Oracle サーバのプロセスIDからv$session で表示されるSID,.SERIAL#,
を特定する方法(v$sessionのみではクライアントマシンのプロセスIDしかわからない)

select s.USERNAME,s.SID,s.SERIAL#,s.PROGRAM from v$session s, v$process p
where s.paddr = p.addr and p.spid = &SPID;
spidに値を入力してください:       (←Oracleサーバ上のプロセスID）


障害対応


coreファイル スタックトレース取得

REDOログのダンプ　udumpに出力
alter system dump logfile '/REDOログのパス/REDOログ';

システムステートダンプ udumpに出力
alter session set events 'immediate trace name systemstate level 10';

DBハング時のみ使用 udumpに出力
oradebug setmypid
oradebug unlimit
oradebug dump systemstate 10