Oracle Transparent Gateway / Oracle Connectivity→ Oracle Gateway

Oracle Connectivityと呼ばれていた機能が、Oracle Transparent Gatewayと合わさり、Oracle Gatewayという名前に変えて、11gより展開されている。無償という話だが、本当か。

Oracle & MySQLで検証してみるか。

DRIVING_SITE memo

DRIVING_SITEヒントを使用すると、問合せが実行されるサイトを指定可能。
オプティマイザを無効にする場合は、手動で実行サイトを指定。

データベースリンク間で非効率な実行計画が実行されている場合、本ヒントも有効。
もしくはジョインをどちらかのテーブルに寄せる。 これは設計が元々悪いのか！

スキーマ・オブジェクトの依存性

依存オブジェクトと参照オブジェクトについては良く言われている。
昔の時代は、依存性が正しい形であっても、実行時エラーで落ちるような時代があった認識している。（昔からオラクルを触っているメンバーは、皆そう言う人が多い。）
 しかし、今のマニュアルを参照する限り、実行時もしくは参照時に検証、再コンパイル行うの、問題がないと記述されている。但し、その影響として、以下2点があると書かれている。

1. 無効になったオブジェクトが多数ある場合は、初期実行時の待機時間が長時間になる可能性がある。 （但し、逆を言えば、依存性を見つつ、再コンパイルしているということ、と理解している。）
2. 無効になったオブジェクトを別セッションで同時に実行していた場合、以下のORAエラーが発生する。
  ORA-04061 -- stringの既存状態は無効になりました。
      原因: プロシージャが変更または削除されたため、無効になった既存状態またはストアド・プロ
              シージャと矛盾が発生した既存状態を使用して、ストアド・プロシージャの実行を再開しよ
              うとしました。
      処置: 再試行してください。このエラーでは、すべてのパッケージの既存状態に再初期化が
              必要です。
  ORA-04064 --- 実行されませんでした。stringは無効になりました  
      原因: 無効になったストアド・プロシージャを実行しようとしました。 
      処置: 再コンパイルしてください。
  ORA-04065 ---実行されませんでした。stringを変更または削除しています
    　原因: 変更または削除されたストアド・プロシージャを実行しようとしたため、コール元プロシー
              ジャからのコールができません。
      処置: その依存関係を再コンパイルしてください。
  ORA-04068 ---パッケージstringstringstringの既存状態は廃棄されました。  
      原因: ストアド・プロシージャを実行しようとして、4060から4067のいずれかのエラーが発生し
      した。  
      処置: アプリケーション状態を完全に再初期化してから、プロシージャを再試行してください。

上のマニュアルを信じる場合、間違ったオブジェクトをUpせず、アプリ処理を無風状態にしておけば、どんなにInvalidが増えても、時間をかければ自動的にValidになるということで、昔の時代の認識は、もう過去のものということになる。本当かな？

あと、コンパイル系の検証、再コンパイルは、DBMS_UTILITY(11gのリンク)、UTL_RECOMP(11gのリンク）が有効。

DataPump 11g R2

11gR2版。TABLE_EXISTS_ACTIONのパラメータの動きに注意。

Oracle Data Pump Import(impdp) のパラメータ
http://docs.oracle.com/cd/E16338_01/server.112/b56303/dp_import.htm#i1010670

Oracle Data Pump Export(impdp) のパラメータ
http://docs.oracle.com/cd/E16338_01/server.112/b56303/dp_export.htm#i1006293

あと、方法としては以下の3つ。通常使うのは１番が多いけど。PL/SQL内で完結させることもできるということがわかった。

1. コマンドライン・クライアントexpdpおよびimpdp
2. PL/SQLパッケージDBMS_DATAPUMP
3. PL/SQLパッケージDBMS_METADATA

SQL Plan Management

共有SQL領域から実行計画をロードすることも可能。便利だ。

DECLARE
  my plan PLS_INTEGER
BEGIN
  my plan := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE( sql_id = > 'SQL_ID' );
END;
/




oracle manual

[構文]

DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE (
   sql_id            IN  VARCHAR2,
   plan_hash_value   IN  NUMBER   := NULL,
   sql_text          IN  CLOB,
   fixed             IN  VARCHAR2 := 'NO',
   enabled           IN  VARCHAR2 := 'YES')
 RETURN PLS_INTEGER;

DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE (
   sql_id            IN  VARCHAR2,
   plan_hash_value   IN  NUMBER   := NULL,
   sql_handle        IN  VARCHAR2,
   fixed             IN  VARCHAR2 := 'NO',
   enabled           IN  VARCHAR2 := 'YES')
 RETURN PLS_INTEGER;

DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE (
   sql_id            IN  VARCHAR2,
   plan_hash_value   IN  NUMBER   := NULL,
   fixed             IN  VARCHAR2 := 'NO',
   enabled           IN  VARCHAR2 := 'YES')
 RETURN PLS_INTEGER;

DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE (
   attribute_name   IN VARCHAR2,
   attribute_value  IN VARCHAR2,
   fixed            IN VARCHAR2 := 'NO',
   enabled          IN VARCHAR2 := 'YES')
  RETURN PLS_INTEGER;

【パラメータ】

フィルタ述語とアクセス述語

基本をメモ

フィルタ述語：　行頭の番号に対応するオペレーションの実行時に、取得したデータから 対象データを抜粋（フィルタ）する処理

アクセス述語： 行頭の番号に対応するオペレーションの実行時に、表示された述語を使用して データにアクセスしたことを示す。一般にINDEX Scan系の処理の場合に表示される。

アクセス述語の場合、データが選択的に選ばれるため、ブロックアクセス量は少なく、
フィルター処理はデータにアクセスした後に絞込みをかける為、アクセス述語に比べるとブロック数
は多い。

CURSOR_SHARING Memo

============================================
CURSOR_SHARINGでは、同じカーソルを共有するSQL文の種類を判断。
    FORCE
　　    既存のカーソルを共有する場合、またはカーソル・プランが最適ではない場合に、新しいカーソルの作成が許可される。
    SIMILAR
　　    リテラルがわずかに異なっても、その他が同じSQL文であれば、
　　　　その異なるリテラルがSQL文の意味または計画が最適化される程度のいずれかに
　　　　影響しないかぎり、カーソルが共有される。
    EXACT
 　　   同一のテキストを含む文のみに、前述のカーソルの共有が許可される。
============================================

9i当時はSimilarにすることで、複数のリテラル値でも共有カーソル化されると売りだったが、
現状のオラクルコメントは以下の通り。「優れたカーソル共有」機能には更に分析が必要。

CURSOR_SHARING=SIMILARの設定は11.1以降のリリースでは推奨されません。
次のリリース(12.1)ではこの値は廃止される可能性があります。
11.1以降のリリースではCURSOR_SHARING=FORCEを設定し、3.でも紹介した11.1の
新機能「優れたカーソル共有」を利用することが推奨されています

SQL Developer Custom Report 001 - v$sql_monitor -

SQL Developerでのレポート作成実施。v$sql_monitorからsqlを抽出し、その実行計画を子レポートで出力する。

ダウンロードして、SQL Developerのレポートタブで取り込んでください。
間違っていたりしたら、改善ポイントを御指摘頂けると、嬉しいです

RealtimeSQLMonitor_ExecutePlan.xml

RAC その①

少しまとめ。

1.HA構成       
    アクティブ-スタンバイ構成 。CPUライセンスは最小になる可能性は高い。
　　スタンバイ側はリソースをDLPARで設定して最小限しておく。

2. 共有ディスク構成       
    シェアード・エブリシング   
    ノード追加時のI/O競合がネック。H/WのSpecを考慮する。

3. 非共有ディスク構成       
    シェアード・ナッシング   
    H/W故障時に一部のデータにアクセス不可。可用性に弱点。

4. RAC（共有ディスク + キャッシュ・フュージョン）       
    共有ディスク単体に比べれば、一定の数までノードを追加できる。
    100ノードまでのスケジュールアウトを実現(オラクル社の公式見解）
　　キャッシュ・フュージョンは、インターコネクトを使用したメモリ内に展開されているデータの同　　
　　期。データファイルではない。

DECODE

 DECODE 
      数値の等価、大小関係を評価するには SIGN 関数。
      SIGN ( expr ) 
      Return:[+1:整数、0:ゼロ、-1:負数]

 　　最小 、最大を評価するには、GREATEST関数、LEAST関数
　　 GREATEST ( expr_list )
　　 LEAST ( expr_list )
      Return: 最も小さい、大きい数。文字列。

Oracle DB Cache Clear

10gからの進歩ですね。忘れずに。

共有プｰﾙ クリア
 (ライブラリキャッシュ<解析済のSQLやPL/SQL等>、データディクショナリキャッシュ)

ALTER SYSTEM FLUSH SHARED_POOL;

データベースバッファ キャッシュ クリア(10g～)
(データファイルから読み込まれたデータ・ブロックのコピーが格納される領域）

ALTER SYSTEM FLUSH BUFFER_CACHE ;  

V$SQL_PLAN

V$SQLから調査したいSQLを探し出した後、ADDRESS、HASH_VALUEやSQL_IDでV$SQL_PLANから実行計画を取得し、整形するスクリプト（小田さんのページから借用）

【パターン①】
column id format 999 newline
column operation format a20
column options format a15
column object_name format a22 trunc
column optimizer format a3 trunc

select id
, lpad (' ', depth) || operation operation
, options
, object_name
, optimizer
, cost
from v$sql_plan
where hash_value = &hash_value
and address = '&address'
start with id = 0
connect by
(prior id = parent_id
and prior hash_value = hash_value
and prior child_number = child_number
)
order siblings by id, position;

【パターン②】
column id format 999 newline
column operation format a20
column options format a15
column object_name format a22 trunc
column optimizer format a3 trunc

select id
, lpad (' ', depth) || operation operation
, options
, object_name
, optimizer
, cost
from v$sql_plan
where sql_id = &sql_id
start with id = 0
connect by (prior id = parent_id
and prior sql_id = sql_id
and prior child_number = child_number
)
order siblings by id, position;

DBMS_METADATA.GET_GRANTED_DDL

XXXスキーマに付与されたすべてのシステム権限付与を示すDDLの取得

SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','XXX')
FROM DUAL;

全部取得できるのかと思ったら、抜けがある。注意！

ITL Slot lack

ORA-00060のデッドロックが発生する際に、INITRNSの設定が小さいことからITL Slotが不足してデッドロックが発生する。通常はMAXTRANSで設定した値によって、ITL Slotを拡張するが、設定値が低い場合に大量の並列化処理が同一データブロックに走った場合に、ITL Slot獲得待ちのデッドロックが発生する。

INITRNSを予め大きめに設定しておくことが対応策としてあるが、1スロット24KBの確保が必要なので、確保し過ぎるとブロックのデータ格納量が不足する。スロット数の理想値は、格納される行数。


trcファイル上では、TXエンキューにおいて、holds X、waits Sが出力されており、AWR上は「enq: TX -allocate ITL Entry」が出力されている。

DB停止障害の初期調査

簡単に確認できることなので、停止障害時には最初の手順として 先ず組み入れること

1. インスタンスが起動しているかを確認
       ps -ef  | grep <Oracleプロセス>

2. ログインできるかを確認
　　　sqlplus <user>/<pass>

3. リスナーに接続できるかを確認
　　　tnsping <接続文字列> <試行回数>
　　　lsnrctl status

4. alert.log

PL/SQL階層プロファイラ

PL/SQL Profilerが進歩し、いつのまにかPL/SQL階層プロファイラになっているとは。。。恐るべきPL/SQL.....

PL/SQL階層プロファイラ

和暦変換

select to_char(sysdate,'eeyy/mm/dd','NLS_CALENDAR=''Japanese Imperial''') 
from dual;

忘れがちなので、とりあえず。

Size between JA16SJIS and JA16SJISTIDE remote connection

JA16SJISとJA16SJISTIDE、SJISとEUC等の間をDBリンクでリモート接続する場合、実はSELECT時のVARCHAR2やCharのサイズが2倍以上になる。但し、「_keep_remote_column_size」と呼ばれる隠しパラメータがデフォルト(true)で効いているので影響がない。

推奨は同一文字コード。 JA16SJISとJA16SJISTIDEもSJISはSJISだが、全く異なる文字コードである為、注意。

統計情報が欠落、陳腐化しているテーブル・インデックスオブジェクトは？

SELECT table_name FROM dba_tab_statistics
WHERE  stale_stats   = 'YES'
OR     last_analyzed IS NULL;


INDEXの場合は、INDEX_NAMEで、DBA_IND_STATISTICS静的ビュー。

他にも 有効な列情報はあるので、Case by Caseで追加。

データ・ディクショナリ

当たり前すぎて、忘れてしまうので。メモ＆コピー

データ・ディクショナリのすべての実表とユーザー・アクセス可能ビューは、Oracle DatabaseユーザーSYSが所有している。データ・ディクショナリの実表内のデータは、Oracle Databaseを機能させるために必要。このため、データ・ディクショナリの情報はOracle Databaseによってのみ書込みまたは変更される必要がある。したがって、Oracle Databaseのユーザーは、SYSスキーマに含まれている行またはスキーマ・オブジェクトを変更できない。変更すると、データ整合性が損われることがある。セキュリティ管理者は、このアカウントを厳しく管理する必要があり。

実表

データベースに関する情報を格納する、基礎となる表です。これらの表に読取り/書込みできるのはOracle Databaseのみです。これらの表は正規化され、データのほとんどは、暗号形式で格納されているため、ユーザーが直接アクセスすることはほとんどありません

ビュー

これらのビューは、実表にあるデータを、ユーザー名や表の名前などの実用的な情報にデコードし、結合とWHERE句を使用して情報を簡略化します。これらのビューには、データ・ディクショナリ内のすべてのオブジェクトの名前と説明が含まれています。すべてのユーザーがアクセスできるビューもいくつかありますが、その他のビューは管理者のみが使用するように設計されています。

表6-1 データ・ディクショナリ・ビューのセット

接頭辞	ユーザー・アクセス	内容	注意
DBA_	データベース管理者	すべてのオブジェクト	一部のDBA_ビューには、管理者にとって有用な情報を含む列が追加されています。
ALL_	すべてのユーザー	ユーザーが権限を持つオブジェクト	ユーザーが所有するオブジェクトが含まれています。これらのビューは有効化されている現在の一連のロールに従います。
USER_	すべてのユーザー	ユーザーによって所有されているオブジェクト	接頭辞がUSER_のビューには、通常、列OWNERは含まれません。この列は、問合せを発行するユーザーとして、USER_ビューに暗黙的に含まれています。