오라클 19c sqlplus의 statementcache(애플리케이션 커서 캐시)

OS환경 : Oracle Linux 7.6 (64bit)

 

DB 환경 : Oracle Database 19.3.0.0

 

방법 : 오라클 19c sqlplus의 statementcache(애플리케이션 커서 캐시)

statementcache는 12cR2 신기능으로 sqlplus에서 사용가능한 명령어임
SET STATEMENTC[ACHE] {0 | n} 명령으로 statementcache(문장 캐시) 크기를 설정할수 있음
기본값은 0임(statement cache 미사용)
이 값을 설정하면 반복되는 문장을 캐싱할 수 있어 다시 파싱할 필요가 없으므로 성능이 향상된다고 함

 

 

값의 범위는 0에서 32767 사이의 값으로 설정 가능함
그리고 파라미터 파일(init, spfile)에 open_cursors 파라미터에 대해 지정된 값보다 작은 캐시 크기를 지정해야함
statement_cache 파일 의 설정은 oraaccess.xml 에 기본적으로 존재한다고 하고, sqlplus의 SET STATEMENTCACHE 설정을 재정의할 수 있다고함
($ORACLE_HOME에서 oraaccess.xml를 찾아봤지만 demo 샘플 파일밖에 존재하지 않았음)
(oraaccess.xml 파일이 더 궁금한 경우 Oracle Call Interface Programmer's Guide를 참고하길 바람)

 

 

테스트
테스트에선 emp, dept 테이블을 이용함

 

 

기존 파라미터 확인

1 2	SQL> show statementcache statementcache is 0

현재 0임

 

 

trace 설정

1 2 3 4 5 6 7

SQL> alter session set tracefile_identifier='TEST1';   Session altered.   SQL> alter session set events '10046 trace name context forever, level 12';   Session altered.

 

 

파싱시간이 오래 걸리는 쿼리 2회 실행

(길어서 파일로 대체)

sqlplus_statementcache_sql.txt

0.06MB

 

 

trace 종료

1 2 3

SQL> alter session set events '10046 trace name context off';   Session altered.

 

 

생성된 trace 경로 확인

1 2 3 4 5 6 7 8 9 10

SQL> set lines 200 pages 1000 col name for a20 col value for a100 select name, value from v$diag_info where name = 'Default Trace File';   NAME                 VALUE -------------------- -------------------------------------------------------------------------------- Default Trace File   /ORA19/app/oracle/diag/rdbms/oracle19/oracle19/trace/oracle19_ora_65165_TEST1.trc

 

 

tkprof 실행

1 2 3 4 5

$ tkprof /ORA19/app/oracle/diag/rdbms/oracle19/oracle19/trace/oracle19_ora_65165_TEST1.trc /ORA19/app/oracle/diag/rdbms/oracle19/oracle19/trace/oracle19_ora_65165_TEST1_tkprof.txt sys=no   TKPROF: Release 19.0.0.0.0 - Development on Mon Feb 19 15:53:25 2024   Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

 

 

트레이스 확인

1 2 3 4 5 6 7 8 9 10 11 12 13

$ vi /ORA19/app/oracle/diag/rdbms/oracle19/oracle19/trace/oracle19_ora_65165_TEST1_tkprof.txt call     count       cpu    elapsed       disk      query    current        rows ------- ------  -------- ---------- ---------- ---------- ----------  ---------- Parse        2     12.74      12.79          4       2294          0           0 Execute      2      0.00       0.00          0          0          0           0 Fetch        4      0.00       0.00          2        122          0          20 ------- ------  -------- ---------- ---------- ---------- ----------  ---------- total        8     12.74      12.80          6       2416          0          20   Misses in library cache during parse: 1 Optimizer mode: ALL_ROWS Parsing user id: 78 Number of plan statistics captured: 2

처음 실행시 12초 정도가 소요되었고 두번째는 소프트 파싱되어 빠르게 출력됨
2번 실행했기 때문에 Parse call이 2로 나오고 처음 이후에는 Sort Parse 이기 때문에
Misses in library cache during parse 부분이 1로 나옴
Number of plan statistics captured 부분도 2로 표시됨

 

 

정확한 테스트를 위해 db 재기동

1 2 3 4 5 6 7 8 9 10

SQL> startup force ORACLE instance started.   Total System Global Area 3221222464 bytes Fixed Size                  8901696 bytes Variable Size            2063597568 bytes Database Buffers         1140850688 bytes Redo Buffers                7872512 bytes Database mounted. Database opened.

 

 

statementcache 설정(15)

1 2	SQL> conn imsi/imsi SQL> set statementcache 15

 

 

statementcache 확인

1 2	SQL> show statementcache statementcache is 15

15로 설정됨

 

 

trace 설정

1 2 3 4 5 6 7

SQL> alter session set tracefile_identifier='TEST2';   Session altered.   SQL> alter session set events '10046 trace name context forever, level 12';   Session altered.

 

 

쿼리 2회 실행

1	SQL> (길어서 생략, 위에서 실행한 쿼리 복사 후 재실행)

 

 

trace 종료

1 2 3

SQL> alter session set events '10046 trace name context off';   Session altered.

 

 

생성된 trace 경로 확인

1 2 3 4 5 6 7 8 9 10

SQL> set lines 200 pages 1000 col name for a20 col value for a100 select name, value from v$diag_info where name = 'Default Trace File';   NAME                 VALUE -------------------- -------------------------------------------------------------------------------- Default Trace File   /ORA19/app/oracle/diag/rdbms/oracle19/oracle19/trace/oracle19_ora_423_TEST2.trc

 

 

tkprof 실행

1 2 3 4 5

$ tkprof /ORA19/app/oracle/diag/rdbms/oracle19/oracle19/trace/oracle19_ora_423_TEST2.trc /ORA19/app/oracle/diag/rdbms/oracle19/oracle19/trace/oracle19_ora_423_TEST2_tkprof.txt sys=no   TKPROF: Release 19.0.0.0.0 - Development on Mon Feb 19 16:00:32 2024   Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

 

 

트레이스 확인

1 2 3 4 5 6 7 8 9 10 11 12 13

$ vi /ORA19/app/oracle/diag/rdbms/oracle19/oracle19/trace/oracle19_ora_423_TEST2_tkprof.txt call     count       cpu    elapsed       disk      query    current        rows ------- ------  -------- ---------- ---------- ---------- ----------  ---------- Parse        1     12.55      12.59          4       2294          0           0 Execute      2      0.00       0.00          0          0          0           0 Fetch        4      0.00       0.00          2        122          0          20 ------- ------  -------- ---------- ---------- ---------- ----------  ---------- total        7     12.55      12.59          6       2416          0          20   Misses in library cache during parse: 1 Optimizer mode: ALL_ROWS Parsing user id: 78 Number of plan statistics captured: 1

처음 실행시 12초 정도가 소요되었고 두번째는 소프트 파싱되어 빠르게 출력됨
이전 테스트와는 다르게 Parse call이 1로 나옴
그리고 Number of plan statistics captured 부분도 기존에는 2였는데 1로 표시됨

 

 

추가로 라이브러리 캐시에 캐싱된 정보가 만약 사라진경우(flush 또는 aging out) 어떻게 되는지 테스트해봄
각각 재기동 후 진행함
동일 테스트 진행시 샘플 쿼리 1번 실행 후 trace 종료 전 flush 명령 실행, 이후 샘플 쿼리 재실행

1 2 3 4 5 6 7 8 9

트레이스 걸고 샘플쿼리 1회 실행(구문 생략)   SQL> alter system flush shared_pool;   System altered.   샘플쿼리 1회 실행(구문 생략)   트레이스 종료 및 트레이스 경로 확인

 

 

10046 트레이스 확인

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28

#statementcache 미설정 시(0) trace call     count       cpu    elapsed       disk      query    current        rows ------- ------  -------- ---------- ---------- ---------- ----------  ---------- Parse        2     25.36      25.52          4       4588          0           0 Execute      2      0.00       0.00          0          0          0           0 Fetch        4      0.00       0.00          2        122          0          20 ------- ------  -------- ---------- ---------- ---------- ----------  ---------- total        8     25.36      25.52          6       4710          0          20   Misses in library cache during parse: 2 Optimizer mode: ALL_ROWS Parsing user id: 78 Number of plan statistics captured: 2   #statementcache 설정 시(15) trace call     count       cpu    elapsed       disk      query    current        rows ------- ------  -------- ---------- ---------- ---------- ----------  ---------- Parse        1     12.75      12.79          4       2294          0           0 Execute      2     12.63      12.68          0       2294          0           0 Fetch        4      0.00       0.00          2        122          0          20 ------- ------  -------- ---------- ---------- ---------- ----------  ---------- total        7     25.39      25.47          6       4710          0          20   Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: ALL_ROWS Parsing user id: 78 Number of plan statistics captured: 1

쿼리 실행시 두 경우 모두 비슷하게 25초 가량 소요됨

 

먼저 statementcache가 0일때는 첫번째 파싱시 12초 정도 소요되고, 두번째 파싱시에도 12초 정도 소요됨
총 Parse call이 2번 발생하고 파싱시 25초가 소요됨
그리고 Misses in library cache during parse 가 2로 표시됨(shared pool을 flush 했기 때문)

 

두번째로 statementcache가 15일때는 파싱시 12초 정도 소요되고, 특이하게도 Execute 시에 12초가 소요됨
총 Parse call이 1번 발생하고 파싱시 12초, Execute 시 12초가 소요되어 statementcache 미사용시와 동일한 총 25초가 소요됨
그리고 Misses in library cache during parse 이 1로 나오고
추가로 Misses in library cache during execute 도 1로 표시됨

 

 

결론 :
이렇게 sqlplus의 statementcache(문장캐시 또는 파싱캐시)를 이용하면 동일 쿼리는 파싱을 또 진행하지 않고
캐시에 있는 내용을 가지고 쿼리를 실행시킬 수 있음
소프트 파싱이 라이브러리 캐시에 있는 정보를 가지고 하드파싱을 두번 하지 않고 기존 파싱한 내용을 재사용하게 해주는 기능인데,
sqlplus의 statementcache(파싱캐시) 기능도 이와 비슷한 역할을 하는것으로 보임
+오라클 성능고도화 원리 1권 297p에서 설명되어 있는 어플리케이션 커서 캐싱이 이 기능과 동일하거나 비슷한 기능인것으로 보임

 

 

테스트 전 이 기능을 보고 든 생각은
만약 파싱 시간이 오래걸리는 쿼리를 sqlplus에서 실행하는 경우 이 방법을 사용해 작업 시간을 단축 시킬수 있을것 같다고 생각했지만
실제로 테스트 해본 결과 shared pool flush(또는 aging out)시 큰 효과를 보지 못했음(Parse 시에는 시간이 줄었지만 Execute에서 시간이 그만큼 소요됨)
성능고도화 원리에 설명되어 있는것처럼 PL/SQL 사용시 Dynamic SQL을 사용하거나 Cursor Variable(Ref Cursor)을 사용할때 이 기능을 같이 사용하면 성능상 좋을것으로 판단됨

 

 

참조 : 

https://docs.oracle.com/en/database/oracle/oracle-database/21/sqpug/SET-system-variable-summary.html#GUID-04F43010-45D3-4B11-BC33-C6A87A9E5AE3
https://blog.naver.com/hanccii/220574878884
https://docs.oracle.com/en/database/oracle/oracle-database/19/lnoci/build-and-configure-oci-applications.html#GUID-CD599644-135A-4116-8B3B-40A9BA172E5C
오라클 성능고도화 원리 1권 297p
THE LOGICAL OPTIMIZER 서적