oracle

Oracle Database 便利SQL

Oracle Database 便利SQL

1.インスタンス作成

pfile起動でnomount状態へDB起動した上で、CREATE DATABASE実行

インスタンス作成sample
startup nomount pfile='/tmp/sul.ora'

CREATE DATABASE SUL
   USER SYS IDENTIFIED BY premera
   USER SYSTEM IDENTIFIED BY premera
   LOGFILE GROUP 1 ('+DG01/SUL/ONLINELOG/redo_grp1','+DG02/SUL/ONLINELOG/redo_grp1') SIZE 200M BLOCKSIZE 512,
           GROUP 2 ('+DG01/SUL/ONLINELOG/redo_grp2','+DG02/SUL/ONLINELOG/redo_grp2') SIZE 200M BLOCKSIZE 512,
           GROUP 3 ('+DG01/SUL/ONLINELOG/redo_grp3','+DG02/SUL/ONLINELOG/redo_grp3') SIZE 200M BLOCKSIZE 512
   MAXLOGHISTORY 1
   MAXLOGFILES 16
   MAXLOGMEMBERS 3
   MAXDATAFILES 1024
   CHARACTER SET AL32UTF8
   NATIONAL CHARACTER SET AL16UTF16
   EXTENT MANAGEMENT LOCAL
   ARCHIVELOG
   DATAFILE '+DG03/SUL/DATAFILE/SYSTEM' SIZE 700M
   SYSAUX DATAFILE '+DG03/SUL/DATAFILE/SYSAUX' SIZE 800M
   DEFAULT TABLESPACE sul01 DATAFILE '+DG03/SUL/DATAFILE/SUL01' SIZE 3036M
   DEFAULT TEMPORARY TABLESPACE tempts1 TEMPFILE '+DG03/SUL/DATAFILE/TEMPTS01' SIZE 512M
   UNDO TABLESPACE undotbs1 DATAFILE '+DG03/SUL/DATAFILE/UNDOTBS01' SIZE 512M
   USER_DATA TABLESPACE users DATAFILE '+DG03/SUL/DATAFILE/USERS' SIZE 300M;
alter database mount;
alter database open;
@?/rdbms/admin/catalog.sql
@?/rdbms/admin/catproc.sql

conn system/premera
@?/sqlplus/admin/pupbld.sql
conn / as sysdba
create spfile='+DG03/SUL/spfileSUL.ora' from pfile='/tmp/sul.ora';
shutdown immediate;
startup mount;
alter database archivelog;
alter database open;

2.セッションとサーバープロセスの確認

セッションの確認
select sid,serial#,username,program,type from V$session;
セッションとサーバープロセスの関係
select s.sid,s.serial#,s.username,s.program "PROGRAM(Client)",p.pid,p.spid,p.program "PROGRAM(Oracle)" from V$session s, V$process p where s.paddr = p.addr AND p.background IS NULL;
※SQL項目一覧
列名 説明
sid セッション識別子
serial# セッションシリアル番号。各セッションはsidとserial#の組み合わせで識別される
username セッション接続しているユーザー名
type セッションタイプの識別。 「USER」は通常のセッション、「BACKGROUND」がバックグランドプロセスによる接続を表す
spid OS上での該当セッションのプロセスID

3.SQL調査関係

10分以内に実行されたSQL調査
col SQL_ID format a20;
col SQL_TEXT format a100;
col FIRST_LOAD_TIME format a20;
col PARSING_SCHEMA_NAME format a20;
SELECT SQL_ID,FIRST_LOAD_TIME, PARSING_SCHEMA_NAME,SQL_TEXT FROM V$SQL WHERE PARSING_SCHEMA_NAME= 'MARIO' AND FIRST_LOAD_TIME BETWEEN TO_CHAR(SYSDATE - (10 / 24 / 60), 'YYYY-MM-DD/HH24:MI:SS') AND TO_CHAR(SYSDATE, 'YYYY-MM-DD/HH24:MI:SS') ORDER BY FIRST_LOAD_TIME;

※全く同一のSQL文は初めのものしか出ない。

4. Extent管理に関して

オブジェクトが使っているエクステント
set pagesize 1000 linesize 10000
col SEGMENT_NAME for a24
select SEGMENT_NAME, TABLESPACE_NAME, EXTENT_ID, BYTES, BLOCKS 
from USER_EXTENTS order by 1,2,3;  

●エクステント管理方式によって以下相違点があり(ローカル管理前提)
自動割当てエクステント「AUTOALLOCATE」を設定した場合、次のような動作となります。
・記号セグメント・サイズが1MBまでのエクステント・サイズは64KB
・セグメント・サイズが64MBまでのエクステント・サイズは1MB
・セグメント・サイズがそれ以上の場合のエクステント・サイズは8MB or 64MB
 (検証の結果、1GB越えるとエクステントサイズ64MBになることも・・・)

一方で「UNIFORM」指定の表領域の場合はセグメントのサイズに関わらずエクステントサイズが一定

create table時のinitial指定だが、initialに指定したサイズを最適に割り当てるようにエクステントが割り当てされる。(520Mだと64M×8+8MB×1のような)
※truncateしてもinitial値まではエクステントの解放はされない。
※alter table ・・・ shrink spaceだと使っていないブロックレベルまで解放する。その後の領域追加は、元と同じようにエクステント確保。
create table時のnextは指定しても無視される。

5. HWM調査

オブジェクトのHWM
set pagesize 1000 linesize 10000
col TABLE_NAME for a30
col BLOCKS for a40
SELECT TABLE_NAME,BLOCKS * 8 / 1024 as "HWM(MB)" from user_tables;

※注意点として、上記SQL上としては最新の統計情報を取らないと正確な情報は得られない。

3.各Database構成コンポーネント

①制御ファイル

■制御ファイルパス確認

制御ファイルのパス確認
col name format a100;
select name from v$controlfile;

■制御ファイル多重化手順
1.制御ファイルのパス変更

SPFIL初期化パラメータ変更
ALTER SYSTEM SET control_files = '<new path1>','<new path2>' SCOPE = SPFILE;

2.データベースを停止
3.存在している制御ファイルを新しい場所へコピー
4.データベースを起動

②オンラインREDOログファイル

■オンラインREDOログファイルのパス確認

オンラインREDOログファイルのパス確認
col member format a100;
SELECT A.GROUP#,A.BYTES,A.STATUS,B.TYPE,B.MEMBER from v$LOG A right outer join v$logfile B ON A.group# = B.group#;

■オンラインREDOログファイル多重化手順
1.データベースをMOUNT状態で起動
2.REDOロググループ作成

オンラインREDOロググループ作成
alter database add logfile group x ('<newパス>') SIZE xxxM;

3.REDOログメンバー作成

オンラインREDOログファイルメンバー追加
alter database add logfile member '<newパス>' to group x;

4.データベースOPEN

※REDOログバッファからREDOログファイル書き込みタイミング
・トランザクションcommit時
・DBWnがREDOデータの書き込みを要求した時
・3秒ごとのタイムアウトが発生した時
・REDOログバッファが不足した時
・未書き込みのREDOデータがREDOログバッファの3分の1を超えた時

※強制ログスイッチ実行
alter system switch logfile;
→振り逃げ
alter system archive log current;
→アーカイブログ完了ををもって終了。バックアップ前等に推奨。
RMANではバックアップ時に内部的に利用されている。

③アーカイブREDOログファイル

アーカイブログモード状態確認
ARCHIVE LOG LIST;
SELECT NAME,LOG_MODE from V$DATABASE;
アーカイブログモードへの切り替え
ALTER DATABASE ARCHIVELOG;
アーカイブREDOログファイルの確認
col name format a100;
SELECT name,sequence# from V$ARCHIVED_LOG order by 2;

④表領域・データファイルの状態確認

表領域の状態確認
col TABLESPACE_NAME for a15
col EXTENT_MANAGEMENT for a17
col ALLOCATION_TYPE for a15
col SEGMENT_SPACE_MANAGEMENT for a24
col BIGFILE for a7
select TABLESPACE_NAME,EXTENT_MANAGEMENT,ALLOCATION_TYPE,SEGMENT_SPACE_MANAGEMENT,BIGFILE,STATUS from DBA_TABLESPACES;
データファイルの状態確認
col TABLESPACE_NAME  format a10
col FILE_NAME        format a50
col STATUS           format a10
col MBYTES           format 9,999,990
col INCRE            format 9,999,990
col AUTOEXTENSIBLE   format a5
col ONLINE_STATUS    format a6
select tablespace_name,file_name,status,bytes/1024/1024 mbytes,increment_by,autoextensible,online_status from dba_data_files;
表領域使用率確認
select
   tablespace_name,
   to_char(nvl(total_bytes / 1024,0),'999,999,999') as "Total(KB)",
   to_char(nvl((total_bytes - free_total_bytes) / 1024,0),'999,999,999') as "Used(KB)",
   to_char(nvl(free_total_bytes/1024,0),'999,999,999') as "Free(KB)",
   round(nvl((total_bytes - free_total_bytes) / total_bytes * 100,100),2) as "Utilization(%)"
from
   ( select tablespace_name,sum(bytes) total_bytes
     from dba_data_files group by tablespace_name)
   left join 
   ( select tablespace_name as free_tablespace_name,sum(bytes) free_total_bytes
     from dba_free_space group by tablespace_name)
 on tablespace_name = free_tablespace_name order by 1;
一時表領域の使用率確認
col TABLESPACE_NAME format a30;
select * from dba_temp_free_space;

◆dba_temp_free_spaceのカラム説明
TABLESPACE_SIZE:一時表領域のサイズ
ALLOCATED_SPACE:使用中領域+再利用可能領域
FREE_SPACE:再利用可能領域+未使用領域(shrink spaceで圧縮可能なサイズ)

⑤メモリ使用状況の確認

SGAの使用量確認
select * from v$sga;          #簡易
select * from v$sgastat;    #詳細

SGAの障害の問題切り分けのために必要な情報
◆データベース・バッファ・キャッシュ
-データベース・バッファ・ヒット率
 
◆共有プール
 -ライブラリ・キャッシュ・ヒット率
 -ディクショナリ・キャッシュ・ヒット率

●V$BUFFER_POOL_STATISTICS
DB_BLOCK_GETS 読み込んだブロック数
CONSISTENT_GETS メモリから読み込めたブロック数
PHYSICAL_READS ディスクから読み込んだブロック数

バッファキャッシュヒット率計算
SET SERVEROUTPUT ON;
DECLARE
  d_gets NUMBER;
  c_gets NUMBER;
  p_reads NUMBER;
  result NUMBER;
BEGIN
  select DB_BLOCK_GETS into d_gets from V$BUFFER_POOL_STATISTICS where NAME='DEFAULT';
  select CONSISTENT_GETS into c_gets from V$BUFFER_POOL_STATISTICS where NAME='DEFAULT';
  select PHYSICAL_READS into p_reads from V$BUFFER_POOL_STATISTICS where NAME='DEFAULT';
result:=round((1-(p_reads/(d_gets+c_gets))),3)*100;
DBMS_OUTPUT.PUT_LINE('Database Buffer Cache Hit =>>' || result || '(%)');
END;
/

●V$LIBRARYCACHE
PINS
データ・オブジェクトに関する情報要求の合計件数
RELOADS
あるオブジェクトの任意のPIN(ただし、オブジェクト・ハンドルの作成後に実行された最初のPIN以外)。ディスクからそのオブジェクトをロードするように要求する。

ライブラリキャッシュヒット率計算
SET SERVEROUTPUT ON;
DECLARE
  pins NUMBER;
  reloads NUMBER;
  result NUMBER;
BEGIN
  select SUM(PINS) into pins from V$LIBRARYCACHE;
  select SUM(RELOADS) into reloads from V$LIBRARYCACHE;
result:=round((1-(reloads/pins)),3)*100;
DBMS_OUTPUT.PUT_LINE('Library Cache Hit =>>' || result || '(%)');
END;
/

●V$ROWCACHE;
GETS
データ・オブジェクトに関する情報要求の合計件数
GETMISSES
結果的にキャッシュ・ミスになったデータ要求の数

ディクショナリキャッシュヒット率計算
SET SERVEROUTPUT ON;
DECLARE
  gets NUMBER;
  getmisses NUMBER;
  result NUMBER;
BEGIN
  select SUM(GETS) into gets from V$ROWCACHE;
  select SUM(GETMISSES) into getmisses from V$ROWCACHE;
result:=round((1-(getmisses/gets)),3)*100;
DBMS_OUTPUT.PUT_LINE('Dictionary Cache Hit =>>' || result || '(%)');
END;
/
PGAの使用量確認
select * from v$pgastat;

⑥STATSPACK利用

SNAP_ID確認
select snap_id, to_char(snap_time, 'yy-mm-dd hh24:mi:ss') snap_time from stats$snapshot order by snap_id;
情報取得レベル閲覧
select snap_level from stats$statspack_parameter;
SNAP実行
execute statspack.snap;
execute statspack.snap(i_snap_level=> 6)
レポート出力
@?/rdbms/admin/spreport.sql;

ご参考
https://qiita.com/sugitk/items/7a7183e75bd9872d380f

★STATSPACK見方

オプティマイザに関して
⑴テーブルには適切なサンプル・サイズの統計
-テーブルのオプティマイザ統計収集にはソート処理を行う必要があり、大規模テーブルでは非常に時間がかかる
-通常は5%程度で構わない。
-Oracle10gからOracle内部で最適な値を決めるようになったのでそれに任せるも可
(estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE)

最後に統計情報取得された際のテーブルの行数、サンプルサイズ、日時
select num_rows,sample_size,last_analyzed from dba_tables where table_name='<TABLE名>';

⑵索引には完全な統計
-索引はソート済みになっている(ソート処理が必要ない)ので、完全に(100%で)収集
-Oracle Database 10gからは索引作成または再構築時にオプティマイザ統計が自動的に収集される。
-DBMS_STATS パッケージのパラメータの値も、Oracle Database 10gからのデフォルトはcascade=>DBMS_STATS.AUTO_CASCADE(索引の統計を収集する必要があるかどうかと最適なサンプル・サイズを Oracle が決定する)になる

最後に統計情報取得された際の索引のサンプルサイズ、日時
select index_name,sample_size,last_analyzed from dba_indexes where table_name='<TABLE名>';

⑶データに偏りがある列はヒストグラム統計
-データの分布に偏りがある列をWHERE句の条件に使用したSQL文は実行計画が最適でない可能性がある(苗字が'鈴木'さんと'林'さんでは該当件数が異なりますので、最適な実行計画も異なる可能性がある)
-DBMS_STATS パッケージのパラメータの値も、通常はmethod_opt=>'FOR ALL COLUMNS SIZE AUTO'(Oracle Database 10gR2からのデフォルト)で問題ありません(Oracleが自動的にどの列がヒストグラムを必要とするかどうかと、各ヒストグラムが必要とするバケットの数(多いほど精度が高い)を判別します)。

最後に統計情報取得された際の列統計
select num_backets from dba_tab_columns where table_name='<TABLE名>' and column_name='<列名>'

99.高度な例

表領域暗号化(Advanced Security)

https://www.insight-tec.com/mailmagazine/ora3/vol366.html
https://enterprisezine.jp/dbonline/detail/3538?p=2

Wallet自動OPEN
$ ls $ORACLE_BASE/wallet
ewallet.p12
$ orapki wallet create -wallet $ORACLE_BASE/wallet -pwd "wallet_pass" -auto_login
$ ls $ORACLE_BASE/wallet
cwallet.sso  ewallet.p12
表領域の暗号化情報の確認
 SELECT t.name, e.encryptionalg algorithm  FROM  v$tablespace t, v$encrypted_tablespaces e WHERE t.ts# = e.ts#;

便利スクリプト集
http://www.doppo1.net/index.html

100.テストデータ作成編

テストテーブルの作成
CREATE TABLE EXAMPLES (
  ID INTEGER
 ,LABEL     VARCHAR2(30)   NOT NULL
 ,SCORE     NUMBER         NOT NULL
 ,RATE      NUMBER         NOT NULL
 ,BIRTHDAY  DATE           NOT NULL
 ,PRIMARY KEY (ID)
);