Dion Cho – Oracle Performance Storyteller

We are natural born scientists

Posts Tagged ‘mismatch

v$sql_shared_cursor

with 9 comments

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!

Written by Dion Cho

March 5, 2009 at 5:27 am

Posted in Cursor

Tagged with ,