Dion Cho – Oracle Performance Storyteller

We are natural born scientists

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!

About these ads

Written by Dion Cho

March 5, 2009 at 5:27 am

Posted in Cursor

Tagged with ,

9 Responses

Subscribe to comments with RSS.

  1. Hi Dion,
    I can’t reproduce your testcase to obtain mismatch in language mode and authentication by changing NLS setting.

    Follow output of my testcase

    Thanks in advance.


    test> create table t1(c1 int, c2 varchar2(100));
    Table created.

    test> alter system flush shared_pool;
    System altered.

    test> var b1 number;
    test> var b2 varchar2(10);
    test> exec :b1 := 1;
    PL/SQL procedure successfully completed.

    test> exec :b2 := '0';
    PL/SQL procedure successfully completed.

    test> select /* share_test */ * from t1 where c1 = :b1 and c2 = :b2;
    no rows selected

    test> @c:\temp\s 'select /* share_test */%'
    old 14: and q.sql_text like ''&1''',
    new 14: and q.sql_text like ''select /* share_test */%''',
    SQL_TEXT = select /* share_test */ * from t1 where c1 = :b1 and c2 = :b2
    SQL_ID = 9wbx9pbrmhsxm
    ADDRESS = 1CEF42F4
    CHILD_ADDRESS = 1B9F5438
    CHILD_NUMBER = 0
    --------------------------------------------------

    PL/SQL procedure successfully completed.

    test> alter session set nls_sort = 'KOREAN_M';
    Session altered.

    test> select /* share_test */ * from t1 where c1 = :b1 and c2 = :b2;
    no rows selected

    test> @c:\temp\s 'select /* share_test */%'
    old 14: and q.sql_text like ''&1''',
    new 14: and q.sql_text like ''select /* share_test */%''',
    SQL_TEXT = select /* share_test */ * from t1 where c1 = :b1 and c2 = :b2
    SQL_ID = 9wbx9pbrmhsxm
    ADDRESS = 1CEF42F4
    CHILD_ADDRESS = 1B9F5438
    CHILD_NUMBER = 0
    --------------------------------------------------

    NO MISPATCH!!!

    sandro

    March 6, 2009 at 5:35 pm

    • Sandro.

      Your exact version?

      select * from v$version

      The reason is maybe your current nls_sort setting is ‘KOREAN_M’?
      What happens if you execute above scripts in the new session?
      What happens when you set nls_sort to SPANISH?
      Or you can play other nls setting like NLS_LANGUAGE = FRENCH.

      Dion Cho

      March 6, 2009 at 11:46 pm


  2. select * from v$version;
    BANNER
    ----------------------------------------------------------------
    Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod
    PL/SQL Release 10.2.0.4.0 - Production
    CORE 10.2.0.4.0 Production
    TNS for 32-bit Windows: Version 10.2.0.4.0 - Production
    NLSRTL Version 10.2.0.4.0 - Production
    ------
    select * from nls_database_parameters;
    PARAMETER VALUE
    ------------------------------ -----------------------------
    NLS_LANGUAGE AMERICAN
    NLS_TERRITORY AMERICA
    NLS_CURRENCY $
    NLS_ISO_CURRENCY AMERICA
    NLS_NUMERIC_CHARACTERS .,
    NLS_CHARACTERSET WE8MSWIN1252
    NLS_CALENDAR GREGORIAN
    NLS_DATE_FORMAT DD-MON-RR
    NLS_DATE_LANGUAGE AMERICAN
    NLS_SORT BINARY
    NLS_TIME_FORMAT HH.MI.SSXFF AM
    NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
    NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
    NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
    NLS_DUAL_CURRENCY $
    NLS_COMP BINARY
    NLS_LENGTH_SEMANTICS BYTE
    NLS_NCHAR_CONV_EXCP FALSE
    NLS_NCHAR_CHARACTERSET AL16UTF16
    NLS_RDBMS_VERSION 10.2.0.4.0
    test> drop table t1;
    Table dropped.
    test> create table t1(c1 int, c2 varchar2(100));
    Table created.
    test> alter system flush shared_pool;
    System altered.
    test> var b1 number;
    test> var b2 varchar2(10);
    test> exec :b1 := 1;
    PL/SQL procedure successfully completed.
    test> exec :b2 := '0';
    PL/SQL procedure successfully completed.
    test> select /* share_test */ * from t1 where c1 = :b1 and c2 = :b2;
    no rows selected
    test> @c:\temp\s 'select /* share_test */%'
    old 14: and q.sql_text like ''&1''',
    new 14: and q.sql_text like ''select /* share_test */%''',
    SQL_TEXT = select /* share_test */ * from t1 where c1 = :b1 and c2 = :b2
    SQL_ID = 9wbx9pbrmhsxm
    ADDRESS = 1B164490
    CHILD_ADDRESS = 19DE4550
    CHILD_NUMBER = 0
    --------------------------------------------------
    PL/SQL procedure successfully completed.
    test> alter session set nls_sort = 'SPANISH';
    Session altered.
    test> select /* share_test */ * from t1 where c1 = :b1 and c2 = :b2;
    no rows selected
    test> @c:\temp\s 'select /* share_test */%'
    old 14: and q.sql_text like ''&1''',
    new 14: and q.sql_text like ''select /* share_test */%''',
    SQL_TEXT = select /* share_test */ * from t1 where c1 = :b1 and c2 = :b2
    SQL_ID = 9wbx9pbrmhsxm
    ADDRESS = 1B164490
    CHILD_ADDRESS = 19DE4550
    CHILD_NUMBER = 0
    --------------------------------------------------
    PL/SQL procedure successfully completed.

    IN ANOTHER SESSION


    test> var b1 number;
    test> var b2 varchar2(10);
    test> exec :b1 := 1;
    PL/SQL procedure successfully completed.
    test> exec :b2 := '0';
    PL/SQL procedure successfully completed.
    test> select /* share_test */ * from t1 where c1 = :b1 and c2 = :b2;
    no rows selected
    test> @c:\temp\s 'select /* share_test */%'
    old 14: and q.sql_text like ''&1''',
    new 14: and q.sql_text like ''select /* share_test */%''',
    SQL_TEXT = select /* share_test */ * from t1 where c1 = :b1 and c2 = :b2
    SQL_ID = 9wbx9pbrmhsxm
    ADDRESS = 1B164490
    CHILD_ADDRESS = 19DE4550
    CHILD_NUMBER = 0
    --------------------------------------------------
    PL/SQL procedure successfully completed.
    test> alter session set nls_sort = 'SPANISH';
    Session altered.
    test> select /* share_test */ * from t1 where c1 = :b1 and c2 = :b2;
    no rows selected
    test> @c:\temp\s 'select /* share_test */%'
    old 14: and q.sql_text like ''&1''',
    new 14: and q.sql_text like ''select /* share_test */%''',
    SQL_TEXT = select /* share_test */ * from t1 where c1 = :b1 and c2 = :b2
    SQL_ID = 9wbx9pbrmhsxm
    ADDRESS = 1B164490
    CHILD_ADDRESS = 19DE4550
    CHILD_NUMBER = 0
    --------------------------------------------------

    sandro

    March 9, 2009 at 11:19 pm

    • Sandro. Thanks for the test.

      It seems to be the version problem.

      Maybe as of 10.2.0.4, Oracle implemented enhanced sql invalidation mechanism. This might be one of them.

      In my local 11.1.0.6 database, I could reproduce your case. It seems that Oracle tries to invalidate the SQL statements as less frequently as possible. Let me post about it when something meaningful found.

      God, I can’t believe how aggressively Oracle changes(but actually enhances) its basic behavior.

      Dion Cho

      March 9, 2009 at 11:31 pm

  3. I was very excited when I saw you making script about v$sql_shared_cursor!
    I also have thought it’s very eye-consuming job to see that. Your script made very intuitive.
    Thanks for your writing. And congratulatons to be the Oracle Ace again^^*.

    Bruce Lee

    March 17, 2009 at 5:36 am

  4. Metalink 296377.1 – the greatest explanation on cursor sharing problem.

    Dion Cho

    March 23, 2009 at 1:48 am

  5. Very nice! I started writing the same script and after a few minutes decided to do a search and found your post. I think yours is much better than I would have come up with.

    Kerry

    Kerry Osborne

    May 13, 2009 at 3:14 pm

  6. [...] I have used several scripts in this post: unstable_plans.sql – this one shows statements that have multiple plans with large variance in execution time awr_plan_change.sql – history of how the statement plan changes over time awr_plan_stats.sql – aggregate statement stats (like elapsed_time, lio, etc) grouped by plan find_sql_acs.sql – A queries v$sql and shows ACS related columns mismatch3.sql – A variation of Dion Cho’s script to display data from v$sql_shared_cursor (see his post here) [...]


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 58 other followers

%d bloggers like this: