Oracle Database 11g

筆者が使用したことのある「Oracle Database 11g」について掲載する。
特にOracleは、PostgreSQL・MySQLに比べ環境依存が高いように思える。

学習目的で使用するOracle Database [2012-11-18]
ログ（ADR） [2012-12-02]
ダーティリード（dirty read）について [2013-07-04]
よく使うOracleコマンド（HP-UX） [2013-05-31]
よく使うSQLメモ [2016-02-25]
性能分析＆チューニング [2014-04-06]
トラブルシューティング [2013-05-31]

学習目的で使用するOracle Database

OTN-Jでは、Oracle11gの30日間試用版を配布しているが、
OTN-USでは学習目的であれば無償（期間制限なし）に使用できる。
ダウンロードするにはOTN-USの会員登録が必要。
＊OTNとはOracle Technology networkの略

OTN-US

ログ（ADR）

Oracle Database 11gでは、トレースファイルなどの出力先が異なっている。
これは、ADR 機能追加による、ログファイル一元管理化のためらしい。
以下サイトに詳細が記載されているので参考に。
11gからの新管理機構「ADR」を理解しよう（1/4）－＠IT

ダーティリード（dirty read）について

Oracle Databaseはダーティリード（dirty read）はない。
そもそもダーティリードとは「変更済だけれどコミットはしていない情報にアクセスすること」という定義のことを言っている。
Oracleは以下のように「ロールバックセグメントを使用して変更前の情報をみせる」から、「ダーティリード（という定義）」ではないということ。
Oracle「ダーティリード（dirty read）はない」の仕組み

よく使うOracleコマンド（HP-UX）

Oracleの起動と停止

					# Oracle起動
					dbstart $ORACLE_HOME
					
					# Oracle停止
					dbshut $ORACLE_HOME

エクスポート(export)とインポート(import)

					# エクスポート(export)
					exp [ユーザ名]/[パスワード]@[リスナ] file=[ダンプファイル名] owner=[エクスポート対象のユーザ名]

					# インポート(import)
					imp [ユーザ名]/[パスワード]@[リスナ] file=[ダンプファイル名] touser=[インポート対象のユーザ名] log=import_output.log

					# インポート（テーブル単位）
					imp [ユーザ名]/[パスワード]@[リスナ] file=[ダンプファイル名] touser=[インポート対象のユーザ名] tables=[作成に失敗したテーブル名]

ポートの確認

					${ORACLE_HOME}/install/portlist.ini

Oracleリスナ接続情報の確認

					lsnrctl status [Oracleリスナ]

Oracleプロセスの確認

					ps -ef | grep ora_.*_[インスタンス名] | grep -v grep

Oracleインスタンス状態の確認

					SELECT INSTANCE_NAME,STATUS FROM V$INSTANCE;

					# 使用例）
					su - oracle -c "export ORACLE_SID=[インスタンス名]; sqlplus / as sysdba" << EOF
					SELECT INSTANCE_NAME,STATUS FROM V$INSTANCE;
					EOF

よく使うSQLメモ

テーブル一覧を表示

以下二つのどちらか（必要に応じてWHERE句で条件を絞り込む）

					SELECT tname FROM tab;
					SELECT table_name FROM user_tables;

テーブル定義を表示

					DESCRIBE [テーブル名]

同時最大接続数の取得

					SELECT sessions_highwater FROM v$license;

セッション数の確認

					SELECT machine, program, count(*) FROM v$session WHERE username != 'SYS' GROUP BY machine, program;

ロックのセッション、ユーザ、オブジェクト及びプログラム名を表示（Oracle8i以上）

						SELECT V$SESSION.SID, DBA_OBJECTS.OBJECT_NAME, V$SESSION.OSUSER, V$SESSION.PROGRAM
						FROM V$LOCKED_OBJECT 
						LEFT JOIN DBA_OBJECTS on V$LOCKED_OBJECT.OBJECT_ID = DBA_OBJECTS.OBJECT_ID
						LEFT JOIN V$SESSION ON V$LOCKED_OBJECT.SESSION_ID = V$SESSION.SID
						ORDER BY V$SESSION.SID, DBA_OBJECTS.OBJECT_NAME

ロックの基本情報を表示

						SELECT SID,TYPE,LMODE,REQUEST,CTIME FROM V$LOCK WHERE TYPE IN ('TX','TM')

ロックを起こしているセッションのSQLを表示

						SELECT V$SQLAREA.SQL_TEXT,V$SQLAREA.ADDRESS
						FROM V$SQLAREA, V$SESSION, V$LOCK
						WHERE V$SQLAREA.ADDRESS = V$SESSION.SQL_ADDRESS
						AND V$SESSION.SID = V$LOCK.SID
						AND V$LOCK.TYPE IN ('TX','TM')

セッションの強制終了

ロックの解消ロックの解消は、ロックが発生したセッションを開放します。

						ALTER SYSTEM KILL SESSION 'sid,serial#';

オープンカーソル

						SELECT OC.USER_NAME, ST.SQL_TEXT, OC.NUM_CURSORS
						FROM V$SQL ST INNER JOIN (
							SELECT USER_NAME, ADDRESS, HASH_VALUE, COUNT(*) NUM_CURSORS
							FROM V$OPEN_CURSOR
							GROUP BY USER_NAME, ADDRESS, HASH_VALUE
							HAVING COUNT(*) > 1 ) OC
							ON OC.ADDRESS = ST.ADDRESS
							AND OC.HASH_VALUE = ST.HASH_VALUE
						ORDER BY OC.NUM_CURSORS;

ロック時間で並び替え

						SELECT a.SID sid,
						 a.USERNAME username,
						 a.SERIAL# serialno,
						 b.TYPE type, 
						 a.PROGRAM program,
						 TO_CHAR(b.CTIME/60,'999990.9') lock_time,
						 c.SQL_TEXT SQL
						  FROM V$SESSION a,
						   V$LOCK b,
						   V$SQLAREA c
						  WHERE a.SID = b.SID
						AND b.TYPE IN ('TX','TM')
						AND a.SQL_ADDRESS = c. ADDRESS
						  ORDER BY lock_time;

性能分析＆チューニング

実行計画

SQL*Plus には実行計画と統計情報を取得するには、SQL発行時に AUTOTRACE を設定する。

AUTOTRACE オプション	内容
SET autotrace on explain	実行結果と実行計画を表示する
SET autotrace on	実行結果と実行計画、統計情報を表示する
SET autotrace traceonly	実行計画と統計を表示する。実行結果は表示しない
SET autotrace off	autotrace をオフにする

以下が参考になる。
SQL*Plusで実行計画を取得する

						（例）
						SQL> SET lines 200
						SQL> COL plan_plus_exp format a200
						SQL> SET pages 0
						SQL> SET autotrace on
						SQL> SELECT num FROM test2 WHERE num = 1;

Statspackを用いたチューニング方法: 以下Oracleが公開しているPDFがとても参考になる。
Oracle® Statspackを用いたデータベース稼働診断とチューニング

トラブルシューティング

起動しない場合

以下の起動ログファイルを確認してエラー内容をチェックする

					/opt/oracle/app/oracle/product/11.2.0/dbhome_1/startup.log

ディスク容量不足による起動エラー

「暫定」として手動でOracleログやトランザクションファイルを削除して対応する場合
oracle起動中にこのファイルを削除しても大丈夫(と思われる)

					# 以下トレースファイルを削除する
					/usr/oracle/app/oracle/diag/rdbms/orcl/orcl/trace
					
					# 以下アラートログを削除する
					/usr/oracle/app/oracle/diag/tnslsnr/[ホスト名]/listener/alert
					
					# 以下リスナーログを削除する
					/usr/oracle/app/oracle/diag/tnslsnr/[ホスト名]/listener/trace/listener.log

▲ページの先頭へ

TOYATAKU WEB 備忘録