ORACLE - Cursor_sharing

/* ORACLE ADMIN */
/* TUNING - CURSOR_SHARIG PARAMETER
   CURSOR_SHARING = EXACT
*/
/* SHOW PARAMETER CURSOR_SHARING >>>>>>>>>  동일한 조건
-- SELECT NAME, VALUE FROM V$PARAMETER WHERE UPPER(NAME)='CURSOR_SHARING';
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cursor_sharing                       string      EXACT
*/

ALTER SYSTEM FLUSH SHARED_POOL; -- SHARED_POLL 비움

SELECT * FROM scott99.DEPT WHERE DEPTNO=10;
SELECT * FROM scott99.DEPT WHERE DEPTNO=20;

SELECT SQL_TEXT, VERSION_COUNT, LOADS, INVALIDATIONS, PARSE_CALLS, SORTS
FROM V$SQLAREA
WHERE SQL_TEXT NOT LIKE '%$%' AND COMMAND_TYPE IN(2,3,6,7) -- 2=IN,3=UP,6=SE,7=DE
ORDER BY FIRST_LOAD_TIME DESC;

/*
SQL_TEXT  VERSION_COUNT LOADS INVALIDATIONS PARSE_CALLS SORTS
-------------------------------------------------------------------------
DEPTNO=20  1            1     0              1         0
DEPTNO=10  1            1     0              1         0
*/

-- CURSOR_SHARING = SIMILAR
ALTER SYSTEM SET CURSOR_SHARING = SIMILAR SCOPE=MEMORY;
ALTER SYSTEM FLUSH SHARED_POOL;
/* SHOW PARAMETER CURSOR_SHARING >>>>>>>>> SQL문만 같고, 조건절의 BIND VAR는
                                           다른 값을 가져도 공유
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cursor_sharing                       string      SIMILAR
*/
/*
VARIABLE V_DEPT NUMBER
EXECUTE :V_DEPT :=40
SELECT * FROM SCOTT99.DEPT WHERE DEPTNO=:V_DEPT;
/

    DEPTNO DNAME          LOC
---------- -------------- -------------
        30 SALES          CHICAGO

EXECUTE :V_DEPTNO :=40
SELECT * FROM SCOTT99.DEPT WHERE DEPTNO=:V_DEPTNO;
/

    DEPTNO DNAME          LOC
---------- -------------- -------------
        40 OPERATIONS     BOSTON

*/

SELECT SQL_TEXT, VERSION_COUNT, LOADS, INVALIDATIONS, PARSE_CALLS, SORTS
FROM V$SQLAREA
WHERE SQL_TEXT NOT LIKE '%$%' AND COMMAND_TYPE IN(2,3,6,7)
ORDER BY FIRST_LOAD_TIME DESC;

/*
SQL_TEXT                VERSION_COUNT LOADS INVALIDATIONS PARSE_CALLS SORTS
------------------------------------------------------------------------------
SELECT *                      1         1       0           2         0
FROM TUNE.DEPT                                                *****
WHERE DEPTNO=:V_DEPTNO                                        
------------------------------------------------------------------------------
*/

-- CURSOR_SHARING = FORCE
ALTER SYSTEM SET CURSOR_SHARING = FORCE SCOPE=MEMORY;
ALTER SYSTEM FLUSH SHARED_POOL;
/* SHOW PARAMETER CURSOR_SHARING >>>>>>>>> SQL문만 같고, 조건절의 상수는
                                           다른 값을 가져도 공유
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cursor_sharing                       string      FORCE
*/

SELECT * FROM SCOTT99.EMP WHERE JOB='CLERK';
SELECT * FROM SCOTT99.EMP WHERE JOB='SALESMAN';

SELECT SQL_TEXT, VERSION_COUNT, LOADS, INVALIDATIONS, PARSE_CALLS, SORTS
FROM V$SQLAREA
WHERE SQL_TEXT NOT LIKE '%$%' AND COMMAND_TYPE IN(2,3,6,7)
ORDER BY FIRST_LOAD_TIME DESC;

/*
SQL_TEXT                VERSION_COUNT LOADS INVALIDATIONS PARSE_CALLS SORTS
------------------------------------------------------------------------------
SELECT *                        1      1        0           2        0
FROM TUNE.EMP                                                *******
WHERE JOB=:"SYS_B_0"
------------------------------------------------------------------------------
*/

/****************************************************************************

 튜닝해야 할 SQL문의 분석(DISK I/O)
 
 DISK_READ FROM V$SQLAREA --> 값이 큰 것은 다른 SQL문을 강구하자

****************************************************************************/

ALTER SYSTEM FLUSH SHARED_POOL;

SELECT * FROM SCOTT.EMP;
SELECT * FROM HR.EMPLOYEES;

SELECT SQL_TEXT, DISK_READS
FROM V$SQLAREA
WHERE SQL_TEXT NOT LIKE '%$%' AND COMMAND_TYPE IN(2,3,6,7)
ORDER BY DISK_READS DESC; 

-- 결과 확인 --> 교재 215p

/****************************************************************************

 튜닝해야 할 SQL문의 분석(MEMORY)
 
 BUFFER_GET FROM V$SQLAREA --> 값이 높을 수록 버퍼 캐시 영역을 많이 점유
                               DISK I/O와 마찬가지로 집중 관리
 테이블 검색을 위해 사용한 메모리 영역의 BLOCK 수
****************************************************************************/
ALTER SYSTEM FLUSH SHARED_POOL;

SELECT * FROM SCOTT.EMP;
SELECT * FROM HR.EMPLOYEES;
 
SELECT SQL_TEXT, BUFFER_GETS
FROM V$SQLAREA
WHERE SQL_TEXT NOT LIKE '%$%' AND COMMAND_TYPE IN(2,3,6,7)
ORDER BY BUFFER_GETS DESC;
-- 결과 확인