V$SQL SHARED CURSOR

目次

基本情報

 

概要

 

各Child CursorごとCursor共有が行われていない理由を提供するビューです。例えばBind Mismatchが生じた場合には、BIND_MISMATCHカラムの値がYに記録されます。

 

サポート

 

8i以上

 

カラム

 

 名前 タイプ 説明
ADDRESS RAW(4) Address of the child cursor
KGLHDPAR RAW(4) Address of the parent cursor
UNBOUND_CURSOR VARCHAR2(1) N) The existing child cursor was not fully built (in other words, it was not optimized)
SQL_TYPE_MISMATCH VARCHAR2(1) N) The SQL type does not match the existing child cursor
OPTIMIZER_MISMATCH VARCHAR2(1) N) The optimizer environment does not match the existing child cursor
OUTLINE_MISMATCH VARCHAR2(1) N) The outlines do not match the existing child cursor
STATS_ROW_MISMATCH VARCHAR2(1) N) The existing statistics do not match the existing child cursor
LITERAL_MISMATCH VARCHAR2(1) N) Non-data literal values do not match the existing child cursor
SEC_DEPTH_MISMATCH VARCHAR2(1) N) Security level does not match the existing child cursor
EXPLAIN_PLAN_CURSOR VARCHAR2(1) N) The child cursor is an explain plan cursor and should not be shared
BUFFERED_DML_MISMATCH VARCHAR2(1) N) Buffered DML does not match the existing child cursor
PDML_ENV_MISMATCH VARCHAR2(1) N) PDML environment does not match the existing child cursor
INST_DRTLD_MISMATCH VARCHAR2(1) N) Insert direct load does not match the existing child cursor
SLAVE_QC_MISMATCH VARCHAR2(1) N) The existing child cursor is a slave cursor and the new one was issued by the coordinator (or, the existing child cursor was issued by the coordinator and the new one is a slave cursor)
TYPECHECK_MISMATCH VARCHAR2(1) N) The existing child cursor is not fully optimized
AUTH_CHECK_MISMATCH VARCHAR2(1) N) Authorization/translation check failed for the existing child cursor
BIND_MISMATCH VARCHAR2(1) N) The bind metadata does not match the existing child cursor
DESCRIBE_MISMATCH VARCHAR2(1) N) The typecheck heap is not present during the describe for the child cursor
LANGUAGE_MISMATCH VARCHAR2(1) N) The language handle does not match the existing child cursor
TRANSLATION_MISMATCH VARCHAR2(1) N) The base objects of the existing child cursor do not match
ROW_LEVEL_SEC_MISMATCH VARCHAR2(1) N) The row level security policies do not match
INSUFF_PRIVS VARCHAR2(1) N) Insufficient privileges on objects referenced by the existing child cursor
INSUFF_PRIVS_REM VARCHAR2(1) N) Insufficient privileges on remote objects referenced by the existing child cursor
REMOTE_TRANS_MISMATCH VARCHAR2(1) N) The remote base objects of the existing child cursor do not match
LOGMINER_SESSION_MISMATCH VARCHAR2(1) N)
INCOMP_LTRL_MISMATCH VARCHAR2(1) N)

 

注意

 

High Version Count

 

SQL Textは同じですが、共有されていないChild Cursorの数は、V $ SQL_AREA.VERSION_COUNTカラムの値で知ることができます。この列の値が高い場合によくVersion Countが高くなります。高いVersion Countは、次のような問題と関連があります。

 

不要なHard Parse
Library Cacheスペースの無駄
不安定な実行計画

 

Version Countが高すぎる場合には、V$ SQL_SHARED_CURSORビューを利用してCursorが共有されていない理由を把握した後、問題を解決しなければなりません。

 

 

Cursorが共有されていない一般的な理由

 

1.Optimizer Modeの変更(ALL_ROWS / FIRST_ROWS / CHOOSE
  OPTIMIZER_MODE_MISMATCHカラムの値で確認
2.Optimizer Parameterの変更(例えば、OPTIMIZER_INDEX_CACHINGパラメータ)
  OPTIMIZER_MISMATCHカラムの値で確認
3.Trace Event有効(例えば、10046 Event、DBMS_MONITOR、SQL_TRACE)
  STATS_ROW_MISMATCHカラムの値で確認
4.Translation発生(例えば、Schema AとSchema Bが同じ名前のテーブルを使用して)
  AUTH_CHECK_MISMATCH、TRANSLATION_MISMATCHカラムの値で確認
5.Bind Mismatch発生(例えば、長さが他の文字列を使用)
  BIND_MISMATCHカラムの値で確認

 

Bind MismatchによるVersion Count増加

 

同じBind変数のためのさまざまな長さの値を使用すると、Bind Mismatchが発生することがあります。下の例を見て見ましょう。

 

-- 同じTextのSQLをBind変数の長さを変更して実行
var X varchar2(1);
exec :X := 't';
select /* bind_test */ * from bind_test where name = :x;

var X varchar2(150);
exec :X := 't';
select /* bind_test */ * from bind_test where name = :x;

var X varchar2(300);
exec :X := 't';
select /* bind_test */ * from bind_test where name = :x;

 

-- Version Count가 3ですべて共有されなかったことを確認することができます。
SELECT version_count, sql_text FROM v$sqlarea WHERE sql_text like 'select /* bind_test */%';
version_count   sql_text   
----------------   --------------------------------------------------------------------                                                             
3                    select /* bind_test */ * from bind_test where name = :X

 

オラクルは、Varchar型のBind変数の場合、その長さを次のように区間の値に切り上げています。

 

・32
・128
・2000
・4000

 

同じ区間の間に属するBind変数は、同じ長さで扱われるが、区間が異なる場合には、Bind Mismatchが発生します。V$ SQL_BIND_METADATAビューを使用すると、これを確認することができます。

 

select address, max_length, bind_name from v$sql_bind_metadata
     where child_address in (
        SELECT child_address FROM v$sql WHERE sql_text like 'SELECT /* bind_test */%'
     );
address                         max_length            bind_name
-----------------------      ---------------       -------------
0000000084884B10               32                       X
0000000084973280               128                      X
0000000084973E70               2000                     X

 

上記のような現象を回避するには、同じサイズのBind変数を宣言する必要があります。

 

Java言語では、PreparedStatementを使用してBind変数を使用します。この場合にも、同じ問題が発生します。つまり、下の二つのPreparedStatementは、実際には共有ができません。

 

PreparedStatement stmt = null;
String s1 = "a";
stmt = con.prepareStatement("SELECT /* bind_test */ * FROM bind_test WHERE name = ?");
stmt.setString(1, s1);
ResultSet rs = stmt.executeQuery();
stmt.close();

String s2 = "a";
for(int idx=0; idx<50; idx++) s2 = s2+" ";
stmt = con.prepareStatement("SELECT /* bind_test */ * FROM bind_test WHERE name = ?");
stmt.setString(1, s2);
rs = stmt.executeQuery();
stmt.close();

 

これらの現象は、ほとんどの場合、大きな問題にはなりません。しかし、単一のSQL文の数が多くのBind変数を使用して、各Bind変数ごとにBind Mismatchが発生した場合、その数は指数関数的に増えることになります。このような場合には、次のような方法を使用することができます。

 

String X = "x";
// V$SQL_BIND_METADATA ビューを使用して確認した最大値だけDummy Blankを付けます。
for(int i = 0; i < xxxx; i++) X+= " ";
...
// RTRIM Functionを使用して、Dummy Blankが認識されないようにします。
stmt = con.prepareStatement("select /* bind_test */ * from bind_test where name = RTRIM(?)");
..


 

上記のようにコードを変更すると、常に同じサイズの文字列が送信されるので、Bind Mismatchが発生しません。

関連情報

 

1. V$ SQL_BIND_METADATAビュー

 

外部参照

 

1. Library Cache Internals – Julian Dyke