Re: Cursor sharing

On Thursday, May 22, 2014 4:39:39 AM UTC-6, vsevolod afanassiev wrote:
> I've checked V$SQL: it doesn't show any ALTER SESSION SET CURSOR_SHARING statements.

Those statements won't be in V$SQL, although the setting for a given session SID will be in V$SES_OPTIMIZER_ENV:

SQL> select id, name, value
  2 from v$ses_optimizer_env
  3 where name like 'cursor%'
  4 and sid=125;

        ID NAME                                     VALUE
---------- ---------------------------------------- -------------------------
        48 cursor_sharing                           exact

SQL> To prove the value isn't always the same:

SQL> select id, name, value
  2 from v$ses_optimizer_env
  3 where name like 'cursor%'
  4 /

        ID NAME                                     VALUE
---------- ---------------------------------------- -------------------------
        48 cursor_sharing                           exact
        48 cursor_sharing                           exact
        48 cursor_sharing                           exact
        48 cursor_sharing                           exact
        48 cursor_sharing                           exact
        48 cursor_sharing                           exact
        48 cursor_sharing                           exact
        48 cursor_sharing                           uninit
        48 cursor_sharing                           similar
        48 cursor_sharing                           exact
        48 cursor_sharing                           exact

        ID NAME                                     VALUE
---------- ---------------------------------------- -------------------------
        48 cursor_sharing                           exact
        48 cursor_sharing                           exact
        48 cursor_sharing                           exact
        48 cursor_sharing                           exact
        48 cursor_sharing                           exact
        48 cursor_sharing                           exact
        48 cursor_sharing                           exact
        48 cursor_sharing                           exact
        48 cursor_sharing                           exact
        48 cursor_sharing                           exact
        48 cursor_sharing                           exact

        ID NAME                                     VALUE
---------- ---------------------------------------- -------------------------
        48 cursor_sharing                           exact
        48 cursor_sharing                           uninit
        48 cursor_sharing                           exact
        48 cursor_sharing                           exact
        48 cursor_sharing                           uninit
        48 cursor_sharing                           exact
        48 cursor_sharing                           exact
        48 cursor_sharing                           uninit

30 rows selected.

SQL> The cursor_sharing parameter is set to exact in my local database; uninit is usually found for 'shadow' sessions not listed in v$session:

SQL> select sid, id, name, value
  2 from v$ses_optimizer_env
  3 where name like 'cursor%'
  4 and value like 'un%'
  5 /

       SID         ID NAME                                     VALUE
---------- ---------- ---------------------------------------- -------------------------
        10         48 cursor_sharing                           uninit
       191         48 cursor_sharing                           uninit
       194         48 cursor_sharing                           uninit
       197         48 cursor_sharing                           uninit

SQL> select username from v$session where sid in (10,191, 194, 197)   2 /

no rows selected

SQL> All other cases should report the current setting for the given parameter for the listed SID.

David Fitzjarrell Received on Thu May 22 2014 - 16:45:11 CEST