Oracle Tips

Oracle Tips　

ただの覚書です。間違っていたらすみません。

オラクルのオンラインマニュアルは OTN (http://otn.oracle.co.jp/document/index.html) から取得可能。

INDEX (検索は、ブラウザの CTRL+F 等を使用してください）

1. テーブルの構造を調べるには？
2. ロールバックセグメントが増えすぎて困ってます。
3. デフラグみたいなのは無い？(断片化の最適化)
4. テーブル一覧が見たい
 5. 索引について
 6. 接続しているユーザーを見たい。強制切断したい
 7. 任意のソート順で、先頭ｎ行だけをとりたい
 8. SQL*Plus の初期設定をしたい
 9. インポートをコマンドラインから行う
 10. エクスポートをコマンドラインから行う
 11. 表に項目を追加する、削除する
 12. PL/SQL でファイルの入出力を行いたい
 13. チューンしたい（アプリケーション編）
14. チューンしたい（オラクル設定編）
15. システムこけたんで、DB戻したいんですが（RECOVER編）
16. SQL*Loader を使う
 17. 新しいインスタンスを作りたい
 18. PL/SQL のデバッグする。
19. 表領域の中でユーザーごとの使用量を知りたい。

■ テーブルの構造を調べるには？

DESC テーブル名　/* 単純 */

SELECT /* 項目整理したバージョン */
UPPER(TABLE_NAME),
COLUMN_NAME,
UPPER(DATA_TYPE),
NVL(DATA_PRECISION, CHAR_COL_DECL_LENGTH),
DATA_SCALE
FROM USER_TAB_COLUMNS
ORDER BY TABLE_NAME, COLUMN_ID

データディクショナリ

項目	内容
user_catalog	ユーザが作成した表、ビュー、シノニム、順序の情報
user_constraints	ユーザが作成した表制約の情報
user_indexes	ユーザが作成した索引の情報
user_tab_columns	ユーザが作成した表の列情報
user_tables	ユーザが作成した表の情報
all_tables	ユーザがアクセス可能な表の情報
dba_tables	データベース内全ての表の情報
dba_tablespases	データベース内全ての表領域の情報
dba_users	データベース内全てのユーザの情報

■ ロールバックセグメントが増えすぎて困っています。

　　・標準では設定されていない optimal サイズを指定する。（DBA STUDIOから作成では未設定）

ALTER ROLLBACK SEGMENTロールバックセグメント名 STORAGE（OPTIMAL　最適サイズ）；

○縮小のタイミング　

割り当ての解除はトランザクション終了後すぐには行われない。
次のトランザクションが書き込みを開始し、エクステントから次のエクステントに移動するとき（エクステント拡張の可能性があるタイミング）
ロールバックセグメントのサイズがOPTIMALを越えていればOPTIMALと等しくなるまでエクステントを解除しようとする。
　　
＊連続した非アクティブなエクステントでないと解除されない　→断片化を解除(デフラグ）

　　・ shrink でサイズを強制的に縮小する

ALTER　ROLLBACK　SEGMENT　ロールバックセグメント名　SHRINK　TO　サイズ；

■ デフラグみたいなのは無い？(断片化の最適化)

・インデックス用

ALTER　INDEX　索引名　COALESCE；

・テーブルスペース用

ALTER TABLESPACE テーブルスペース名 COALESCE;

・断片化を調べる(テーブルスペース）

SELECT TABLESPACE_NAME,BLOCK_ID,BYTES,BLOCKS FROM DBA_FREE_SPACE WHERE TABLESPACE_NAME=テーブルスペース名 ORDER BY BLOCK_ID;

■ テーブル一覧が見たい

SELECT * FROM TAB; /* 接続ユーザーで見れるオブジェクト(表、ビュー） */

SELECT * FROM USER_TABLES; /* 接続ユーザーが作成した表 */

■ 索引について

・標準B*ツリー
　　多量データ検索（たとえば10万件のトランザクションデータを検索）には向いているが、数件しかないデータには不向き。

・ビットマップ索引(R7.3.2以降)
　　列値の種類が少ない列（フラグ、性別など）が多く、OR 条件で抽出することが多いデータに向く。

・逆キー索引(R8.0.x以降)

■ 接続しているユーザーを見たい。トランザクション制御情報が見たい。強制切断したい

SELECT * FROM V$SESSION

SELECT * FROM V$TRANSACTION

ALTER SYSTEM KILL SESSION ’セッションＩＤ(SID),シリアル番号（SERIAL#)’;

■ 任意のソート順で、先頭１０行だけをとりたい

副問合せとROWNUMを使う（大量データだと検索が遅いけど）

SELECT * FROM (SELECT * FROM 入荷Ｆ WHERE ほにゃらら ORDER BY 入荷№) WHERE ROWNUM <= 10

「コストベース・オプティマイザの拡張：最初のN行の最適化」 (9i 以降) もある。

OPTIMIZERヒントを使ってSQLにヒントを含ませる。

ヒントが使用されると、すべてのセッションおよび初期化設定よりも優先されます。

SELECT /*+ FIRST_ROWS(N) */ ....

■ SQL*Plus の初期設定をしたい

SQL*Plus 実行ファイルと同じフォルダ(たとえば、C:\Oracle\Ora81\bin)に login.sql を作成し、実行スクリプトを作成する。

define_editor=c:\progra~1\hidemaru\hidemaru.exe ← 編集用のエディタを秀丸にする set line 2000

■ エクスポートをコマンドラインから行う

exp username/password@database file=expdat.dmp

■ インポートをコマンドラインから行う

imp username/password@database fromuser=srcuser touser=destuser file=expdat.dmp

■ 表に項目を追加する、削除する

ALTER TABLE テーブル名 ADD ( 　　　　追加する列名　　タイプ )

ALTER TABLE 表名 DROP COLUMN 列名 ;　 /* 1列のみ */ ALTER TABLE 表名 DROP (列名, 列名…) ;　　 /* 複数列可能 */

■ PL/SQL でファイルの入出力を行いたい

UTL_FILE を使用すると、テキストファイル入出力を PL/SQL で行える。

１．init.ora にファイルＩ／Ｏが行えるフォルダ指定をする

utl_file_dir=C:\LOG,C:\TEMP

２.サンプルがここに

■ チューンしたい（アプリケーション編）

1. 索引の使用とSQLキャッシュ

LIKE を使って中間一致での検索、|| や、SUBSTR などで索引を加工しても索引が利用されない。

コストベースの場合のSQLを書く上での注意点

索引中にNull値が存在しないため全表走査となります
!=, <> を指定すると全表走査になります
索引列に対して計算式を適用すると全表走査になります
OR検索は、指定列全てに索引がないと全表走査になります
結合索引を使用する場合、順序に注意する
Like句で、前に'%','_'を付けたり、Not Likeを指定すると全表走査されます。|| や、SUBSTR などで索引を加工しても索引が利用されない。
Order By句では、表にNot Null指定されてる場合に限り索引が使用されます。
Group By句を使用すると、常に全表走査されます。

SQL の意味としては、

・SELECT * FROM F_ZAIKO;　と Select * from f_zaiko; は、同意です。

しかしOracleの解析エンジンは一字一句同じでなければ、別物になる。名称のつけ方にルールを付ける！
→共有プールでのキャッシュの効果がない。字句解析、コンパイルが発生する。

表Ｔの索引がIDX1,IDX2とすると。

T.IDX1 = 1 とか、T.IDX1 = 1 AND T.IDX2 = 2 は良い？、T.IDX2 = 2 は索引が使われない。

２. 使われていない索引があるかどうか、付与されている索引が妥当であるかをチェックする

　・索引がある → SELECT 時に効果あり。
　・索引がつく → INSERT 時に遅くなる。（大量のデータ追加の場合は一時的にキーをはずす）

　・不要な索引は削除する。

　・未使用索引の監視(9i以降)

　　→ USED 列が NO の個所は使われていないことになる。

SELECT * FROM V$OBJECT_USAGE;

３. 索引が使われているかをチェックする（AUTOTRACE編）

Oracleがトレースモードで動いていなくても実行可能な解析方法である。

事前準備

SQL> connect sys/change_on_install@expserver 接続されました。 SQL> @K:\oracle\ora81\sqlplus\admin\plustrce.sql /* plustrace ロールを作成 */ ... SQL>grant plustrace to scott; /* AUTOTRACEを取得するユーザにロールを付加する */ 権限付与が成功しました。SQL>connect scott/tiger@expserver 接続されました。 SQL> @K:\oracle\ora81\RDBMS\ADMIN\utlxplan /* 実行計画を保存するためのテーブルを作成 */表が作成されました。

AUTOTRACE を使う

・SET AUTOTRACE ON /* 結果セット、実行計画、統計情報 */
・SET AUTOTRACE TRACEONLY /* 実行計画、統計情報 */
・SET AUTOTRACE TRACEONLY EXPLAIN /* 実行計画 */
・SET AUTOTRACE TRACEONLY STATISTICS /* 統計情報 */

SQL> SET AUTOTRACE ON
SQL> select e.empno, e.ename, e.deptno, d.dname from dept d, emp e 2 where 3 d.deptno = e.deptno 4 and e.deptno = 20; EMPNO ENAME DEPTNO DNAME ---------- ---------- ---------- -------------- 7369 SMITH 20 RESEARCH 7566 JONES 20 RESEARCH 7788 SCOTT 20 RESEARCH 7876 ADAMS 20 RESEARCH 7902 FORD 20 RESEARCH 実行計画 ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 NESTED LOOPS 2 1 TABLE ACCESS (FULL) OF 'EMP' 3 1 TABLE ACCESS (BY INDEX ROWID) OF 'DEPT' 4 3 INDEX (UNIQUE SCAN) OF 'PK_DEPT' (UNIQUE) 統計 ---------------------------------------------------------- 0 recursive calls 4 db block gets 12 consistent gets 0 physical reads 0 redo size 781 bytes sent via SQL*Net to client 425 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 5 rows processed

SQL> set autotrace off /* AUTO TRACE を使用しない */

→ ここから何がわかるのか？

統計情報

項目	意味
`recursive calls`
`db block gets`	CURRENT ブロックが要求された回数
`consistent gets`
`physical reads`
`redo size`	生成された REDO の合計バイト数
`bytes sent via SQL*Net to client`
`bytes received via SQL*Net from client`	Oracle Net
`SQL*Net roundtrips to/from client`	クライアントに送られた Oracle Net メッセージとクライアントから受信した Oracle Net のメッセージの合計数
`sorts (memory)`	メモリー内で完全に実行され、ディスク書き込みを必要としなかったソート操作の数
`sorts (disk)`	少なくとも１回のディスク書き込みを必要としたソート操作の数
`rows processed`	操作中に処理された行数

４. 索引が使われているかをチェックする（SQL Trace編）

５. 索引を再作成する

レコードを削除してもインデックスの内容は更新されない。
レコード上削除されても、インデックスには残っている！

たとえば、年度更新で昨年のトランザクションデータを削除していても
インデックスには残っており、アクセス対象となっている！
データを削除してもパフォーマンスのためのインデックスが逆効果になる。

→頻繁に更新される場合はインデックスの再構築を行う

ALTER INDEX 索引名 REBUILD; /* 明示的に再構築を行う */

DROP INDEX 索引名; CREATE INDEX ～ /* 索引を削除し、作成することで再構築と同意 */

６. 更新するときに、ROWID を使ってレコードを指定する
PL/SQL の場合は、CURRENT OF カーソル名を使って高速化する。
そのときに SELECT は、FOR UPDATE を使用してロックする必要がある。

■ チューンしたい（オラクル設定編）

アプリケーションチューンを先に行う！（効果大）
それでもだめなら or とりあえずやるなら、メモリの使用が標準だとへぼいので色々とチューンしてやりましょう。

１. ANALYZE コマンド

Oracleのオプティマイザにはコストベースとルールベースがある。

・ルールベース（デフォルト）

　OracleはSQL文のWhere句などの構文情報(Where句の順序には影響されない)と索引情報からアクセスパスを1～15のランク付けを行い、一番高いランクのパスを選択することになります。
　ルールベースでは、コストベースと違い、データ容量や分布率が変動しようとも、同じSQL文では毎回同じアクセスパスを選択し、例え全表走査の方が高速な場合でも索引が使用できる状態であれば、索引を使ったアクセス・パスが選択されます。

・コストベース

ANALYZEコマンドにより採取した統計情報を基に実行計画を立てる。
→ データ量が大幅に変動した際に手動で ANALYZE 等を行う必要がある。

コストベースオプティマイザを使用するためには、INIT.ORAの初期化パラメータのOPTIMIZER_MODEをCHOOSE(デフォルト)にセットし
、対象となる表に対してANALYZEを実行する。
ANALYZEには、すべてのデータを読み取る方法と一部のデータをサンプリングする方法がある。
データサイズが大きく全データを調べていたのでは時間がかかりすぎる場合には、10%程度でサンプリングしてみる。

表の全データを読み取り統計情報を収集する

ANALYZE TABLE 表名 COMPUTE STATISTICS;

10%のデータをサンプリングし統計情報を収集する

ANALYZE TABLE 表名 ESTIMATE STATISTICS SAMPLE 10 PERCENT;

表の統計情報を削除する（コストベースからルールベースに戻す）

ANALYZE TABLE 表名 DELETE STATISTICS;

コストベースの情報を確認する。

SELECT TABLE_NAME, NUM_ROWS, AVG_ROW_LEN, BLOCKS FROM USER_TABLES;

２. メモリ設定

１.状況確認

SQL*Plus に system/manager でログインします。
事前に設定情報を確認するのに show parameters/parameters buffer/sga コマンドで表示します。

SQL> connect system/manager@expserver 接続されました。 SQL> show parameters NAME TYPE VALUE ------------------------------------ ------- ------------------------------ O7_DICTIONARY_ACCESSIBILITY boolean TRUE active_instance_count integer always_anti_join string NESTED_LOOPS always_semi_join string standard aq_tm_processes integer 0 audit_trail string NONE background_core_dump string partial background_dump_dest string E:\oracle\admin\ORCL\bdump backup_tape_io_slaves boolean FALSE bitmap_merge_area_size integer 1048576 blank_trimming boolean FALSE ～ SQL> show parameters buffer NAME TYPE VALUE ------------------------------------ ------- ------------------------------ buffer_pool_keep string buffer_pool_recycle string db_block_buffers integer 9802 log_buffer integer 163840 use_indirect_data_buffers boolean FALSE
～
SQL> show sga Total System Global Area 201480220 bytes Fixed Size 75804 bytes Variable Size 120934400 bytes Database Buffers 80297984 bytes Redo Buffers 172032 bytes

2. 設定ファイル init.ora の設定
init.oraファイル中のtimes_statisticsをTRUEに設定する．

timed_statistics = true

3. Shared Pool Size の決定

■ バックアップをとりたい（バックアップ編）

Oracle のマニュアルの
「Oracle9i バックアップおよびリカバリ概要」
「Oracle9i ユーザー管理バックアップおよびリカバリ・ガイド」に解説がある。

１．OSレベルでバックアップをとる(構造を同じにして、ファイル自体をコピーし、退避する）
２．Oracle 8 以降付属の Recovery Manger (RMAN) を使用する。
３．Export でデータをバックアップする。

■ システムこけたんで、DB戻したいんですが（リカバリ編）

Oracle のマニュアルの
「Oracle9i バックアップおよびリカバリ概要」
「Oracle9i ユーザー管理バックアップおよびリカバリ・ガイド」に解説がある。

ARCHIVELOG運用かNOARCHIVELOG運用かでリカバリ方法が異なる。
SQL*Plus の RECOVER DATABASE コマンドを使う。

■ SQL*Loader を使う

■ 新しいインスタンスを作る

DB Assistant で作成する（簡単）

コマンドをたたいて手動で作成する（面倒）
init.ora 複製、設定
ORADIM コマンド

netstat start で確認

Create DATABASE

CREATE ROLLBACK SEGMENT RB1
ALTER ROLLBACK SEGMENT RB1 ONLINE;

CREATE TABLESPACE TP1
ALTER USER SYSTEM TEMPORARY TABLESPACE TP1

CREATE TABLESPACE USER1
ALTER USER SYSTEM DEFAULT TABLESPACE USER1

データディクショナリ作成 catalog.sql　...

一般ユーザ作成

■ PL/SQL のデバッグ

DBMS_OUTPUT パッケージの PUT_LINE メソッドを使うことで、スタックに追加される。
SQL*Plus で SET SERVEROUTPUT ON でスタックからコンソールに表示される。
このメッセージでデバックを行う(VB の DEBUG.PRINT みたいなもの）
事前にスタックはサイズを拡張（サイズを指定）しておく必要がある。
リリースのときは、DBMS_OUTPUT.PUT_LINE はコメントアウトしておく。
（SQL*Plus から見ていなくてもバッファサイズを超えるとスタックバッファがオーバーフローする！）

DBMS_OUTPUT.ENABLE(1000000); /* スタックサイズを指定サイズ(BYTE)まで拡張 */ DBMS_OUTPUT.PUT_LINE('てすと'); /* スタックに追加 */

■ 表領域の中でユーザーごとの使用量を知りたい。

SELECT TABLESPACE_NAME ,OWNER ,TO_CHAR(SUM(BYTES), '99,999,999,999') TOTALSIZE FROM DBA_SEGMENTS GROUP BY TABLESPACE_NAME, OWNER ORDER BY TABLESPACE_NAME, OWNER

■ キャッシュのヒット率

SELECT (1-(SUM(Reloads)/
(SUM(Pins)+SUM(Reloads))))*100 AS LibHitRate
FROM V$LIBRARYCACHE;

SELECT (1-(SUM(Getmisses)/
(SUM(Gets)+SUM(Getmisses))))*100 AS DicHitRate
FROM V$ROWCACHE;

■ 重複する行を調べる

集合演算子 INTERSECT を使用する。

SELECT c10, c11, c12 FROM table1 INTERSECT SELECT c20, c21, c22 FROM table2;

■ 片方に存在する行を調べる

集合演算子 MINUS を使用する。（table1 にあって table2 にはない行が返される）

SELECT c10, c11, c12 FROM table1 MINUS SELECT c20, c21, c22 FROM table2 ;

■ ノートＰＣに Oracle 8i を入れましたがメモリがいっぱいです。

java_pool_size

■ SQL*Loader の使い方

カンマ区切りデータ用の制御ファイル例

カンマ区切りでも基本は同じです。今度の例ではREPLACEの代わりにAPPENDを使っているので、既存の行は削除せず追加しようとします。「PRESERVE BLANKS」はカンマの前後の空白も削除せずに、データの一部とみなす指令です。

LOAD DATA
INFILE '../data/users.dat'
APPEND
PRESERVE BLANKS
INTO TABLE USERS
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
EMPNO, USERNAME, KANJI_NAME
)

固定長データ用の制御ファイル例

最後に固定長の場合です。下のように、

LOAD DATA
INFILE '../data/users.dat'
APPEND PRESERVE BLANKS
INTO TABLE USERS
(
EMPNO            POSITION(1:10)    ZONED,
KANJI_NAME    POSITION(11:30)   CHAR,
T_DATE        POSITION(31:38)   DATE "YYYYMMDD",
SURYO            POSITION(47:53)   ZONED(7,1),
SECTION_NAME POSITION(54:75)   CHAR,
H_DATE        POSITION(86:89)   CHAR
    "DECODE(:H_DATE, '    ', '    ',
DECODE(SUBSTR(:H_DATE, 1, 1), '9', '19', '20') || :H_DATE)",
JOB_NAME      CONSTANT 'UNDEFINED',
PROCESS_CODE CONSTANT 'A'
)

数値はZONED、文字列はCHARなどを使います。日時型は上のように、書式を添えて指定することになるでしょう。定数はCONSTANT句で定義できます。

文字コードについて

SQL*Loaderのデータファイルの文字コードは、環境変数NLS_LANGで制御できます。ですから、内部コードが日本語EUCのOracleデータベースに、シフトJISのデータファイルからデータを投入することも可能です。

UNIX上のOracleで注意が必要なのは改行コードです。データファイルの改行コードがCR+LF(Win32形式)だと、うまくいきません。FTPの際に必ず改行コードをLF(UNIX形式) に変換しておく必要があります。

日付データ

SQL*Loaderで固定の日付や現在の日時をカラムにセットするには、次のようにします。"CHAR" が分かりづらいですね。

DATE1 CHAR "TO_DATE('20030331', 'YYYYMMDD')",
DATE2 SYSDATE,