Dion Cho – Oracle Performance Storyteller

I hit following OTN forum article which was a discussion on typical SQL sharing problem.

There are so many reasons why Oracle does not share the same statement. Oracle records the reason in v$sql_shared_cursor view. In my local 10.2.0.1 database, v$sql_shared_cursor has 53 reasons of unshareability.

But I have a big complaint on v$sql_shared_cursor view. It has 53 columns whose value is ‘Y’ or ‘N’. It’s very annoying to display these values and find the columns whose value is ‘Y’. It’s eye-consuming job.

So I built up my own version of v$sql_shared_cursor, like following:

ed shared_cursor

declare
  c         number;
  col_cnt   number;
  col_rec   dbms_sql.desc_tab;
  col_value varchar2(4000);
  ret_val    number;
begin
  c := dbms_sql.open_cursor;
  dbms_sql.parse(c,
      'select q.sql_text, s.*
      from v$sql_shared_cursor s, v$sql q
      where s.sql_id = q.sql_id
          and s.child_number = q.child_number
          and q.sql_text like ''&1''',
      dbms_sql.native);
  dbms_sql.describe_columns(c, col_cnt, col_rec);

  for idx in 1 .. col_cnt loop
    dbms_sql.define_column(c, idx, col_value, 4000);
  end loop;

  ret_val := dbms_sql.execute(c);

  while(dbms_sql.fetch_rows(c) > 0) loop
    for idx in 1 .. col_cnt loop
      dbms_sql.column_value(c, idx, col_value);
      if col_rec(idx).col_name in ('SQL_ID', 'ADDRESS', 'CHILD_ADDRESS',
                    'CHILD_NUMBER', 'SQL_TEXT') then
        dbms_output.put_line(rpad(col_rec(idx).col_name, 30) ||
                ' = ' || col_value);
      elsif col_value = 'Y' then
        dbms_output.put_line(rpad(col_rec(idx).col_name, 30) ||
                ' = ' || col_value);
      end if;

    end loop;

    dbms_output.put_line('--------------------------------------------------');

   end loop; 

  dbms_sql.close_cursor(c);          

end;
/

The powerful dbms_sql package enables us to display only the columns whose value is ‘Y’. The result is like following.

@shared_cursor 'select /* share_test */%'

SQL_TEXT                       = select /* share_test */ * from t1 where c1 = :b1 and c2 = :b2
SQL_ID                         = 9wbx9pbrmhsxm
ADDRESS                        = 2BB29C74
CHILD_ADDRESS                  = 2BC811D4
CHILD_NUMBER                   = 0
--------------------------------------------------
SQL_TEXT                       = select /* share_test */ * from t1 where c1 = :b1 and c2 = :b2
SQL_ID                         = 9wbx9pbrmhsxm
ADDRESS                        = 2BB29C74
CHILD_ADDRESS                  = 2AB8DA4C
CHILD_NUMBER                   = 1
OPTIMIZER_MODE_MISMATCH        = Y
--------------------------------------------------
SQL_TEXT                       = select /* share_test */ * from t1 where c1 = :b1 and c2 = :b2
SQL_ID                         = 9wbx9pbrmhsxm
ADDRESS                        = 2BB29C74
CHILD_ADDRESS                  = 2AAB1798
CHILD_NUMBER                   = 2
AUTH_CHECK_MISMATCH            = Y
LANGUAGE_MISMATCH              = Y
OPTIMIZER_MODE_MISMATCH        = Y
--------------------------------------------------                                                  

Very intuitive. Following is a demonstration to detect cursor sharing problem using this script.

UKJA@ukja102> create table t1(c1 int, c2 varchar2(100));

Table created.

UKJA@ukja102> alter system flush shared_pool;

System altered.

UKJA@ukja102> var b1 number;
UKJA@ukja102> var b2 varchar2(10);
UKJA@ukja102>
UKJA@ukja102> exec :b1 := 1;

PL/SQL procedure successfully completed.

UKJA@ukja102> exec :b2 := '0';

PL/SQL procedure successfully completed.

UKJA@ukja102> select /* share_test */ * from t1 where c1 = :b1 and c2 = :b2;

no rows selected

UKJA@ukja102> --This is my SQL
UKJA@ukja102> @shared_cursor 'select /* share_test */%'
SQL_TEXT                       = select /* share_test */ * from t1 where c1 = :b1 and c2 = :b2
SQL_ID                         = 9wbx9pbrmhsxm
ADDRESS                        = 2BB29C74
CHILD_ADDRESS                  = 2BC811D4
CHILD_NUMBER                   = 0
--------------------------------------------------                                                  

PL/SQL procedure successfully completed.

UKJA@ukja102> --Change the optimizer mode from all_rows to first_rows
UKJA@ukja102> alter session set optimizer_mode = first_rows;

Session altered.

UKJA@ukja102> select /* share_test */ * from t1 where c1 = :b1 and c2 = :b2;

no rows selected

UKJA@ukja102> -- Yes, mismatch in optimizer mode
UKJA@ukja102> @shared_cursor 'select /* share_test */%'
SQL_TEXT                       = select /* share_test */ * from t1 where c1 = :b1 and c2 = :b2
SQL_ID                         = 9wbx9pbrmhsxm
ADDRESS                        = 2BB29C74
CHILD_ADDRESS                  = 2BC811D4
CHILD_NUMBER                   = 0
--------------------------------------------------
SQL_TEXT                       = select /* share_test */ * from t1 where c1 = :b1 and c2 = :b2
SQL_ID                         = 9wbx9pbrmhsxm
ADDRESS                        = 2BB29C74
CHILD_ADDRESS                  = 2AB8DA4C
CHILD_NUMBER                   = 1
OPTIMIZER_MODE_MISMATCH        = Y
--------------------------------------------------                                                  

PL/SQL procedure successfully completed.

UKJA@ukja102> --Change NLS setting
UKJA@ukja102> alter session set nls_sort = 'KOREAN_M';

Session altered.

UKJA@ukja102> select /* share_test */ * from t1 where c1 = :b1 and c2 = :b2;

no rows selected

UKJA@ukja102> -- Mismatch in language mode and authentication
UKJA@ukja102> @shared_cursor 'select /* share_test */%'
SQL_TEXT                       = select /* share_test */ * from t1 where c1 = :b1 and c2 = :b2
SQL_ID                         = 9wbx9pbrmhsxm
ADDRESS                        = 2BB29C74
CHILD_ADDRESS                  = 2BC811D4
CHILD_NUMBER                   = 0
--------------------------------------------------
SQL_TEXT                       = select /* share_test */ * from t1 where c1 = :b1 and c2 = :b2
SQL_ID                         = 9wbx9pbrmhsxm
ADDRESS                        = 2BB29C74
CHILD_ADDRESS                  = 2AB8DA4C
CHILD_NUMBER                   = 1
OPTIMIZER_MODE_MISMATCH        = Y
--------------------------------------------------
SQL_TEXT                       = select /* share_test */ * from t1 where c1 = :b1 and c2 = :b2
SQL_ID                         = 9wbx9pbrmhsxm
ADDRESS                        = 2BB29C74
CHILD_ADDRESS                  = 2AAB1798
CHILD_NUMBER                   = 2
AUTH_CHECK_MISMATCH            = Y
LANGUAGE_MISMATCH              = Y
OPTIMIZER_MODE_MISMATCH        = Y
--------------------------------------------------                                                  

PL/SQL procedure successfully completed.

UKJA@ukja102> -- Now I change the length of the bind variable from 10 to 2000!!!
UKJA@ukja102> var b2 varchar2(2000);
UKJA@ukja102> exec :b2 := '0';

PL/SQL procedure successfully completed.

UKJA@ukja102> select /* share_test */ * from t1 where c1 = :b1 and c2 = :b2;

no rows selected

UKJA@ukja102> -- Mismatch in bind variable
UKJA@ukja102> @shared_cursor 'select /* share_test */%'
SQL_TEXT                       = select /* share_test */ * from t1 where c1 = :b1 and c2 = :b2
SQL_ID                         = 9wbx9pbrmhsxm
ADDRESS                        = 2BB29C74
CHILD_ADDRESS                  = 2BC811D4
CHILD_NUMBER                   = 0
--------------------------------------------------
SQL_TEXT                       = select /* share_test */ * from t1 where c1 = :b1 and c2 = :b2
SQL_ID                         = 9wbx9pbrmhsxm
ADDRESS                        = 2BB29C74
CHILD_ADDRESS                  = 2AB8DA4C
CHILD_NUMBER                   = 1
OPTIMIZER_MODE_MISMATCH        = Y
--------------------------------------------------
SQL_TEXT                       = select /* share_test */ * from t1 where c1 = :b1 and c2 = :b2
SQL_ID                         = 9wbx9pbrmhsxm
ADDRESS                        = 2BB29C74
CHILD_ADDRESS                  = 2AAB1798
CHILD_NUMBER                   = 2
AUTH_CHECK_MISMATCH            = Y
LANGUAGE_MISMATCH              = Y
OPTIMIZER_MODE_MISMATCH        = Y
--------------------------------------------------
SQL_TEXT                       = select /* share_test */ * from t1 where c1 = :b1 and c2 = :b2
SQL_ID                         = 9wbx9pbrmhsxm
ADDRESS                        = 2BB29C74
CHILD_ADDRESS                  = 2B10FC70
CHILD_NUMBER                   = 3
BIND_MISMATCH                  = Y
--------------------------------------------------        

The last example clearly shows that the length of bind variable of varchar2 type can be a source of cursor unshareability. Julian Dyke provides a wonderful explanation on this problem.

Unshareable SQL statements have some well-known problems.

• Library cache contention due to increased scan depth
• Execution plan change due to new optimization
• Waste of system resource due to increased hard parse count

Don’t forget v$sql_shared_cursor. It has the answer why your statements are not shared!