Pages

Monday, January 10, 2011

Cursor_Sharing Parameter




CURSOR_SHARING determines what kind of SQL statements can share the same cursors.


Example:

SET TIMI ON


SHOW PARAMETER CURSOR_SHARING

NAME                                 TYPE        VALUE                                                        
------------------------------------ ----------- ------------------------------                                
cursor_sharing                       string      EXACT                                                        


SQL> ALTER SYSTEM FLUSH SHARED_POOL;

System altered.

Elapsed: 00:00:00.18
SQL> ALTER SYSTEM FLUSH BUFFER_CACHE;

System altered.

Elapsed: 00:00:10.37

SQL> CONN SCOTT/*****
Connected.

SQL> SELECT * FROM EMP WHERE DEPTNO=10;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO                         
---------- ---------- --------- ---------- --------- ---------- ---------- ----------                         
      9001 SCOTT      ANALYST                              1500                    10                         
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10                         
      7839 KING       PRESIDENT            17-NOV-81       5000                    10                         
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10                          

Elapsed: 00:00:00.06
SQL> SELECT * FROM EMP WHERE DEPTNO=20;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO                         
---------- ---------- --------- ---------- --------- ---------- ---------- ----------                         
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20                         
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20                          
      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20                         
      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20                         
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20                         

Elapsed: 00:00:00.01
SQL> SELECT * FROM EMP WHERE DEPTNO=30;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO                         
---------- ---------- --------- ---------- --------- ---------- ---------- ----------                         
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30                         
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30                         
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30                         
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30                         
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30                         
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30                          

6 rows selected.

Elapsed: 00:00:00.01
SQL> SELECT * FROM EMP WHERE DEPTNO=40;

no rows selected

Elapsed: 00:00:00.00
SQL>
SQL>
SQL> CONN SYS/****** AS SYSDBA
Connected.
SQL> DESC v$SQL
 Name                                                        Null?    Type
 ----------------------------------------------------------- -------- -----------------------------------------
 SQL_TEXT                                                             VARCHAR2(1000)
 SQL_FULLTEXT                                                         CLOB
 SQL_ID                                                               VARCHAR2(13)
 SHARABLE_MEM                                                         NUMBER
 PERSISTENT_MEM                                                       NUMBER
 RUNTIME_MEM                                                          NUMBER
 SORTS                                                                NUMBER
 LOADED_VERSIONS                                                      NUMBER
 OPEN_VERSIONS                                                        NUMBER
 USERS_OPENING                                                        NUMBER
 FETCHES                                                              NUMBER
 EXECUTIONS                                                           NUMBER
 PX_SERVERS_EXECUTIONS                                                NUMBER
 END_OF_FETCH_COUNT                                                   NUMBER
 USERS_EXECUTING                                                      NUMBER
 LOADS                                                                NUMBER
 FIRST_LOAD_TIME                                                      VARCHAR2(19)
 INVALIDATIONS                                                        NUMBER
 PARSE_CALLS                                                          NUMBER
 DISK_READS                                                           NUMBER
 DIRECT_WRITES                                                        NUMBER
 BUFFER_GETS                                                          NUMBER
 APPLICATION_WAIT_TIME                                                NUMBER
 CONCURRENCY_WAIT_TIME                                                NUMBER
 CLUSTER_WAIT_TIME                                                    NUMBER
 USER_IO_WAIT_TIME                                                    NUMBER
 PLSQL_EXEC_TIME                                                      NUMBER
 JAVA_EXEC_TIME                                                       NUMBER
 ROWS_PROCESSED                                                       NUMBER
 COMMAND_TYPE                                                         NUMBER
 OPTIMIZER_MODE                                                       VARCHAR2(10)
 OPTIMIZER_COST                                                       NUMBER
 OPTIMIZER_ENV                                                        RAW(797)
 OPTIMIZER_ENV_HASH_VALUE                                             NUMBER
 PARSING_USER_ID                                                      NUMBER
 PARSING_SCHEMA_ID                                                    NUMBER
 PARSING_SCHEMA_NAME                                                  VARCHAR2(30)
 KEPT_VERSIONS                                                        NUMBER
 ADDRESS                                                              RAW(4)
 TYPE_CHK_HEAP                                                        RAW(4)
 HASH_VALUE                                                           NUMBER
 OLD_HASH_VALUE                                                       NUMBER
 PLAN_HASH_VALUE                                                      NUMBER
 CHILD_NUMBER                                                         NUMBER
 SERVICE                                                              VARCHAR2(64)
 SERVICE_HASH                                                         NUMBER
 MODULE                                                               VARCHAR2(64)
 MODULE_HASH                                                          NUMBER
 ACTION                                                               VARCHAR2(64)
 ACTION_HASH                                                          NUMBER
 SERIALIZABLE_ABORTS                                                  NUMBER
 OUTLINE_CATEGORY                                                     VARCHAR2(64)
 CPU_TIME                                                             NUMBER
 ELAPSED_TIME                                                         NUMBER
 OUTLINE_SID                                                          NUMBER
 CHILD_ADDRESS                                                        RAW(4)
 SQLTYPE                                                              NUMBER
 REMOTE                                                               VARCHAR2(1)
 OBJECT_STATUS                                                        VARCHAR2(19)
 LITERAL_HASH_VALUE                                                   NUMBER
 LAST_LOAD_TIME                                                       VARCHAR2(19)
 IS_OBSOLETE                                                          VARCHAR2(1)
 CHILD_LATCH                                                          NUMBER
 SQL_PROFILE                                                          VARCHAR2(64)
 PROGRAM_ID                                                           NUMBER
 PROGRAM_LINE#                                                        NUMBER
 EXACT_MATCHING_SIGNATURE                                             NUMBER
 FORCE_MATCHING_SIGNATURE                                             NUMBER
 LAST_ACTIVE_TIME                                                     DATE
 BIND_DATA                                                            RAW(2000)

SQL> SELECT SQL_TEXT FROM v$SQL WHERE SQL_TEXT LIKE '%SELECT%*%FROM%EMP%';

SQL_TEXT                                                                                                      
---------------------------------------------------------------------------------------------------------------
SELECT * FROM EMP WHERE DEPTNO=20                                                                             
SELECT * FROM EMP WHERE DEPTNO=40                                                                             
SELECT * FROM EMP WHERE DEPTNO=30                                                                             
SELECT * FROM EMP WHERE DEPTNO=10                                                                             
SELECT SQL_TEXT FROM v$SQL WHERE SQL_TEXT LIKE '%SELECT%*%FROM%EMP%'                                          

Elapsed: 00:00:00.03
SQL>
SQL>
SQL>
SQL> ALTER SYSTEM SET cursor_sharing=SIMILAR;

System altered.

Elapsed: 00:00:00.12
SQL>
SQL>
SQL> CONN SCOTT/TIGER
Connected.
SQL> SELECT * FROM EMP WHERE EMPNO=7788;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO                         
---------- ---------- --------- ---------- --------- ---------- ---------- ----------                         
      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20                         

Elapsed: 00:00:00.01
SQL> SELECT * FROM EMP WHERE EMPNO=7839;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO                         
---------- ---------- --------- ---------- --------- ---------- ---------- ----------                         
      7839 KING       PRESIDENT            17-NOV-81       5000                    10                         

Elapsed: 00:00:00.00
SQL> SELECT * FROM EMP WHERE EMPNO=9002;

no rows selected

Elapsed: 00:00:00.00
SQL> SELECT * FROM EMP WHERE EMPNO=9001;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO                         
---------- ---------- --------- ---------- --------- ---------- ---------- ----------                         
      9001 SCOTT      ANALYST                              1500                    10                         

Elapsed: 00:00:00.00


SQL> CONN / AS SYSDBA
Connected.


SQL> SELECT SQL_TEXT FROM v$SQL WHERE SQL_TEXT LIKE '%SELECT%*%FROM%EMP%';

SQL_TEXT                                                                                                      
---------------------------------------------------------------------------------------------------------------
SELECT * FROM EMP WHERE DEPTNO=20                                                                             
SELECT * FROM EMP WHERE DEPTNO=40                                                                              
SELECT * FROM EMP WHERE DEPTNO=30                                                                             
SELECT * FROM EMP WHERE EMPNO=:"SYS_B_0"                                                                       
SELECT * FROM EMP WHERE DEPTNO=10                                                                             
SELECT SQL_TEXT FROM v$SQL WHERE SQL_TEXT LIKE '%SELECT%*%FROM%EMP%'                                          

6 rows selected.