16:25:06  > !cat sqlbind.sql

set lines 150 pages 50 verif off trimspool on
col VAR_NAME form A18
col TYPE_VALUE form A30
col sess_param  form A80

-- 20080506  jailee

accept sql_id      prompt 'Please enter the value for Sql_id : '

select *
from (select decode(no,1,'VAR ','exec :')||replace(name,':','') VAR_NAME,decode(no,1,datatype_string, ' := '||''''||value_string||''';') TYPE_VALUE
      from v$sql_bind_capture b
          ,(select 1 no from dual union all select 2 from dual) t
      where sql_id = '&sql_id'
      order by 1,2
     )
union all
select '--DBA_HIST_SQLBIND','' from dual
union all
select *
from (select decode(no,1,'VAR ','exec :')||replace(name,':',''),decode(no,1,datatype_string, ' := '||''''||value_string||''';')
      from DBA_HIST_SQLBIND b
          ,(select 1 no from dual union all select 2 from dual) t
      where  dbid = (select dbid from v$database)
      and sql_id = '&&sql_id'
      and (snap_id , dbid) = (select max(snap_id) , max(b.dbid)
                              from DBA_HIST_SQLBIND, (select dbid from v$database )  b
                              where sql_id =  '&&sql_id')
      order by b.snap_id,1,2
     )
;

select --SQL_ID,
       CHILD_ADDRESS,isdefault,'"'||NAME||'" = '||value||' ;' sess_param
from v$sql_optimizer_env b
where sql_id = '&&sql_id'
--and isdefault = 'NO'
order by isdefault,name
;

16:26:10 > @sqlbind
Please enter the value for Sql_id : 55ynxfrzdhzhw

VAR_NAME           TYPE_VALUE
------------------ ------------------------------
VAR B1             CHAR(32)
VAR B2             CHAR(32)
VAR B3             CHAR(32)
VAR B4             CHAR(32)
exec :B1            := '001';
exec :B2            := '2008-05-01';
exec :B3            := '2008-05-02';
exec :B4            := '107983481   ';
--DBA_HIST_SQLBIND
VAR B1             CHAR(32)
VAR B2             CHAR(32)
VAR B3             CHAR(32)
VAR B4             CHAR(32)
exec :B1            := '001';
exec :B2            := '2008-05-01';
exec :B3            := '2008-05-02';
exec :B4            := '107981134   ';

19 rows selected.

Elapsed: 00:00:00.55

CHILD_ADDRESS    ISD SESS_PARAM
---------------- --- --------------------------------------------------------------------------------
C00000076A201F58 NO  "_b_tree_bitmap_plans" = false ;
C00000076A201F58 NO  "_bloom_filter_enabled" = false ;
C00000076A201F58 NO  "_db_file_optimizer_read_count" = 128 ;
C00000076A201F58 NO  "_gby_hash_aggregation_enabled" = false ;
C00000076A201F58 NO  "_index_join_enabled" = false ;
C00000076A201F58 NO  "_optimizer_compute_index_stats" = false ;
C00000076A201F58 NO  "_optimizer_sortmerge_join_enabled" = false ;
C00000076A201F58 NO  "_pga_max_size" = 2097152 KB ;
C00000076A201F58 NO  "optimizer_secure_view_merging" = false ;
C00000076A201F58 NO  "sort_area_size" = 2147483647 ;
C00000076A201F58 YES "active_instance_count" = 3 ;
C00000076A201F58 YES "bitmap_merge_area_size" = 1048576 ;
C00000076A201F58 YES "cpu_count" = 14 ;
C00000076A201F58 YES "cursor_sharing" = exact ;
C00000076A201F58 YES "hash_area_size" = -2 ;
C00000076A201F58 YES "optimizer_dynamic_sampling" = 2 ;
C00000076A201F58 YES "optimizer_features_enable" = 10.2.0.3 ;
C00000076A201F58 YES "optimizer_index_caching" = 0 ;
C00000076A201F58 YES "optimizer_index_cost_adj" = 100 ;
C00000076A201F58 YES "optimizer_mode" = all_rows ;
C00000076A201F58 YES "parallel_ddl_mode" = enabled ;
C00000076A201F58 YES "parallel_dml_mode" = disabled ;
C00000076A201F58 YES "parallel_execution_enabled" = true ;
C00000076A201F58 YES "parallel_query_mode" = enabled ;
C00000076A201F58 YES "parallel_threads_per_cpu" = 2 ;
C00000076A201F58 YES "pga_aggregate_target" = 51200000 KB ;
C00000076A201F58 YES "query_rewrite_enabled" = true ;
C00000076A201F58 YES "query_rewrite_integrity" = enforced ;
C00000076A201F58 YES "skip_unusable_indexes" = true ;
C00000076A201F58 YES "sort_area_retained_size" = 0 ;
C00000076A201F58 YES "star_transformation_enabled" = false ;
C00000076A201F58 YES "statistics_level" = typical ;
C00000076A201F58 YES "workarea_size_policy" = auto ;

select * from table(dbms_xplan.display_awr('803b7z0t84sp7', NULL, NULL, 'basic rows bytes cost'));

V$SQL BIND CAPTURE

SQL> CREATE TABLE bind_test(id INT);
SQL> var x number;
SQL> EXEC :x := 1;
SQL> select * from bind_test where id = :x';

SQL> SELECT name, position, was_captured, 
	to_char(last_captured,'yyyy/mm/dd hh24:mi:ss'), value_string
   FROM v$sql_bind_capture
   WHERE sql_id = (SELECT sql_id FROM v$sql WHERE sql_text =
   ('select * from bind_test where id = :x')
   
NAME	POSITION	WAS_CAPTURED	LAST_CAPTURED		VALUE_STRING
----	--------	----------	--------------		------------
:X	1		YES		2007/10/13 00:16:19	1

1. SQL*Plus Autotrace 기능 활성화
   1-1 EXPLAIN PLAN 이란?
   사용자들이 SQL 문의 액세스 경로를 확인하고 튜닝을 할 수 있도록 SQL 문을 분석하고 해석하여 실행 계획을 수립한 후 실행 계획을 테이블(PLAN_TABLE)에 저장하도록 해주는 명령어 입니다.
   
   1-2 PLUSTRACE 권한 생성
   SQL> conn / as sysdba
   Connected.
   SQL> @?/sqlplus/admin/plustrce.sql
   SQL>
   SQL> drop role plustrace;
   Role dropped.
   SQL> create role plustrace;
   Role created.
   SQL>
   SQL> grant select on v_$sesstat to plustrace;
   Grant succeeded.
   SQL> grant select on v_$statname to plustrace;
   Grant succeeded.
   SQL> grant select on v_$mystat to plustrace;
   Grant succeeded.
   SQL> grant plustrace to dba with admin option;
   Grant succeeded.
   SQL>
   SQL> set echo off
   
   1-3 해당 사용자에게 PLUSTRACE 권한 부여
   SQL> grant PLUSTRACE to scott;
   Grant succeeded.
   
   1-4 해당 사용자로 로그인 후 PLAN Table 생성
   SQL> conn scott/oracle
   Connected.
   SQL> @?/rdbms/admin/utlxplan.sql
   Table created.
   
   
   
   
   2. SQL*Plus Autotrace 사용 방법
   2-1 Autotrace Mode 설정
   SQL> show user;
   USER is "SCOTT"
   SQL> set autotrace on
   SQL> set autotrace traceonly
   SQL> set autotrace traceonly explain
   SQL> set autotrace traceonly statistics
   
   
   2-2 EXPLAIN PLAN SQL 실행
   SQL> conn scott/oracle
   Connected.
   SQL> select dname from dept;
   DNAME
   --------------
   ACCOUNTING
   RESEARCH
   SALES
   OPERATIONS
   
   Execution Plan
   ----------------------------------------------------------
   Plan hash value: 3383998547
   --------------------------------------------------------------------------
   | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
   --------------------------------------------------------------------------
   | 0 | SELECT STATEMENT | | 4 | 40 | 3 (0)| 00:00:01 |
   | 1 | TABLE ACCESS FULL| DEPT | 4 | 40 | 3 (0)| 00:00:01 |
   --------------------------------------------------------------------------
   Statistics
   ----------------------------------------------------------
   68 recursive calls
   0 db block gets
   22 consistent gets
   8 physical reads
   0 redo size
   488 bytes sent via SQL*Net to client
   385 bytes received via SQL*Net from client
   2 SQL*Net roundtrips to/from client
   0 sorts (memory)
   0 sorts (disk)
   4 rows processed
   
   
   2-3 Autotrace Mode 해제
   SQL> set autotrace off;
   
   
   SQL 튜닝을 하게 되면 기본적을 가장 많이 사용 하는 툴입니다.
   다음은 SQL Trace의 기능에 대해서 설명 하도록 하겠습니다.

######  What are the major tuning areas in database performance tuning  #######

  - Memory - shared pool, large pool, buffer cache, redo log buffer, and sort area size.
  - I/O - distributing I/O, striping, multiple DBWn processes, and DBWn I/O slaves.
  - CPU - CPU utilization.
  - Space management - extent allocation and Oracle block efficiency.
  - Redo log and checkpoint - redo log file configuration, redo entries, and checkpoint.
  - Rollback segment - sizing rollback segments.
  - Network

###### 조정해야할 메모리 영역
전체 SGA 영역 : 2.4G
shared_pool_size = ???
large_pool_size = ???
java_pool_size = ???
db_cache_size = ???
SHARED_POOL_RESERVED_SIZE=???

##################################################
### PGA 성능(할당량) 조정
##################################################
PGA target advice => v$pga_target_advice를 이용하여 적당 할당량을 조사한다.

-- PGA 어드바이스
select round(pga_target_for_estimate/1024/1024) as target_size_MB,
bytes_processed,estd_extra_bytes_rw as est_rw_extra_bytes,
estd_pga_cache_hit_percentage as est_hit_pct,
estd_overalloc_count as est_overalloc
from v$pga_target_advice;

=>위의 쿼리를 실행해서 최적의 PGA할당량을 찾아낸다

PGA Memory Advisory  DB/Inst: KMSMESV1/KMSMESV1  End Snap: 886
-> When using Auto Memory Mgmt, minimally choose a pga_aggregate_target value
   where Estd PGA Overalloc Count is 0

                                       Estd Extra    Estd PGA   Estd PGA
PGA Target    Size           W/A MB   W/A MB Read/      Cache  Overalloc
  Est (MB)   Factr        Processed Written to Disk     Hit %      Count
---------- ------- ---------------- ---------------- -------- ----------
        94     0.1        326,688.8         11,869.3     96.0      2,140
       188     0.3        326,688.8         11,869.3     96.0      2,139
       375     0.5        326,688.8          2,393.2     99.0        646
       563     0.8        326,688.8            110.8    100.0          0
       750     1.0        326,688.8            110.8    100.0          0
       900     1.2        326,688.8            110.8    100.0          0
     1,050     1.4        326,688.8            110.8    100.0          0
     1,200     1.6        326,688.8            110.8    100.0          0
     1,350     1.8        326,688.8            110.8    100.0          0
     1,500     2.0        326,688.8            110.8    100.0          0
     2,250     3.0        326,688.8            110.8    100.0          0
     3,000     4.0        326,688.8            110.8    100.0          0
     4,500     6.0        326,688.8            110.8    100.0          0
     6,000     8.0        326,688.8            110.8    100.0          0
          -------------------------------------------------------------

:::::::::::::::::::::::         결과          ::::::::::::::::::::::::::::::
=========> PGA 사이즈 조정 : 현재 사이즈 적정 

##################################################
### SGA 크기 조정
##################################################

SGA Target Advisory  DB/Inst: KMSMESV1/KMSMESV1  End Snap: 886

SGA Target SGA Size   Est DB      Est DB   Est Physical
  Size (M)   Factor Time (s) Time Factor          Reads
---------- -------- -------- ----------- --------------
       752       .5   78,897         1.0      5,109,138
     1,128       .8   78,044         1.0      4,873,605
     1,504      1.0   77,494         1.0      4,723,249
     1,880      1.3   77,293         1.0      4,667,665
     2,256      1.5   77,238         1.0      4,653,967
     2,632      1.8   77,238         1.0      4,653,967
     3,008      2.0   77,239         1.0      4,653,967
-------------------------------------------------------------

:::::::::::::::::::::::         결과          ::::::::::::::::::::::::::::::
=========> SGA 사이즈 조정 : 1.5GB -> 2.5GB로 사이즈 조정 
★★★★ statspack분석 결과에 의해  SGA_TARGET 파라메타 2.5GB로 재조정.
alter system set SGA_MAX_SIZE=(2.5GB)

##################################################
### shared pool 성능(할당량) 조정
##################################################

1.먼저 현재 사용중인 데이타베이스에 대해 라이브러리 캐시영역에 대한 크기가 적정한지 조사한다.

select namespace,gets,gethits,gethitratio
from v$librarycache
where namespace = 'SQL AREA';

=> 참조 : 만약 gethitratio 컬럼의 값이 90%이상이라면 라이브러리 캐쉬영역이 개발자들의 SQL 파싱정보를 저장하기에 충분한 메모리 공간을
          확보하고 있음을 의미하며 만약 90% 이하라면 성능이 저하 될 수도 있다는 것을 의마한다.

=> Quality DB의 경우 다음과 같은 결과가 나옴.
NAMESPACE             GETS    GETHITS GETHITRATIO
--------------- ---------- ---------- -----------
SQL AREA            885978     878552  .991618302
=========> 99%

## 부가적으로 pins에 대한 reloads의 비율을 확인한다.

select sum(pins),sum(reloads),sum(reloads) / sum(pins)
from v$librarycache
where namespace = 'SQL AREA';

=> 완성차 DB의 경우 다음과 같이 나옴.
 SUM(PINS) SUM(RELOADS) SUM(RELOADS)/SUM(PINS)
---------- ------------ ----------------------
  85931701          506             5.8884E-06

========> 참조 : PINS에 대한 reloads에 대한 비율이 1%미만일 경우 라이브러리 캐쉬 영역의 크기가 SQL 파싱정보를
                 저장하기에 충분하다는 의미이다.

현재 할당된 shared_pool 사이즈를 조사한다.
select pool, sum(bytes)/1024/1024 Mbytes from v$sgastat group by pool;

=> 완성차 DB의 경우 다음과 같이 나옴.
POOL             MBYTES
------------ ----------
             863.996956
shared pool  511.985039
streams pool 48.0495529
large pool           32
java pool            64

2. Shared pool advice => v$shared_pool_advice 를 이용해서 oracle의 사이즈 advice를 조사한다.

SELECT shared_pool_size_for_estimate "Size of Shared Pool in MB",
       shared_pool_size_factor "Size Factor",estd_lc_time_saved "Time Saved in sec"
  FROM v$shared_pool_advice;

Shared Pool Advisory  DB/Inst: KMSMESV1/KMSMESV1  End Snap: 886
-> SP: Shared Pool     Est LC: Estimated Library Cache   Factr: Factor
-> Note there is often a 1:Many correlation between a single logical object
   in the Library Cache, and the physical number of memory objects associated
   with it.  Therefore comparing the number of Lib Cache objects (e.g. in
   v$librarycache), with the number of Lib Cache Memory Objects is invalid

                                        Est LC Est LC  Est LC Est LC
    Shared    SP   Est LC                 Time   Time    Load   Load      Est LC
      Pool  Size     Size       Est LC   Saved  Saved    Time   Time         Mem
  Size (M) Factr      (M)      Mem Obj     (s)  Factr     (s)  Factr    Obj Hits
---------- ----- -------- ------------ ------- ------ ------- ------ -----------
       256    .5       72        4,370 #######    1.0  33,605    1.4  32,095,562
       320    .6      132        7,162 #######    1.0  28,485    1.2  32,102,312
       384    .8      195       12,092 #######    1.0  24,534    1.0  32,102,864
       448    .9      204       13,130 #######    1.0  24,534    1.0  32,102,864
       512   1.0      204       13,130 #######    1.0  24,534    1.0  32,102,864
       576   1.1      204       13,130 #######    1.0  24,534    1.0  32,102,864
       640   1.3      204       13,130 #######    1.0  24,534    1.0  32,102,864
       704   1.4      204       13,130 #######    1.0  24,534    1.0  32,102,864
       768   1.5      204       13,130 #######    1.0  24,534    1.0  32,102,864
       832   1.6      204       13,130 #######    1.0  24,534    1.0  32,102,864
       896   1.8      204       13,130 #######    1.0  24,534    1.0  32,102,864
       960   1.9      204       13,130 #######    1.0  24,534    1.0  32,102,864
     1,024   2.0      204       13,130 #######    1.0  24,534    1.0  32,102,864
          -------------------------------------------------------------+ The above output shows the current setting of the shared pool is
  512M (for which Size factor is 1).

+ It also shows decreasing the size of the shared pool to the 50% of its
  current value will also be equally efficient as the current value.
+ Also doubling the size of the shared pool will save extra 2300 sec in parsing.
+ Using this view a DBA has the correct picture to design Shared pool.

========> 결론 : 현재 PEMS DB의 shared_pool 사이즈를 init*.ora 파일에서 늘려준다.
shared_pool_size = 800M(????)

### 현재 SGA에 대한 할당 조사
select current_size from v$buffer_pool;
select pool, sum(bytes)/1024/1024 Mbytes from v$sgastat group by pool;

3. SHARED_POOL_RESERVED_SIZE에 대한 성능 조절

=> SHARED_POOL_RESERVED_SIZE는 PL/SQL 블록으로 실행된 sql문의 구문분석 정보를 저장할때 사용되는 공유 풀 영역의 크기를
   지정하는 파라메타이다
=> 기본값은 SHARED_POOL_SIZE의 10%이며 최대값은 SHARED_POOL_SIZE 값의 1/2 이다.
=> v$shared_pool_reserved 자료사전의 REQUEST_FAILURES의 컬럼값이 0이 아니거나 계속 증가값을 보일때 이파라메타 값을
   크게 해준다.

select request_failures from v$shared_pool_reserved;

REQUEST_FAILURES
----------------
               0

========> 결론 :SHARED_POOL_RESERVED_SIZE 변경 없음
SHARED_POOL_RESERVED_SIZE = 25M(?????)

##################################################
#### DB Cache Size 성능 조정
##################################################

1. DB cache advice => v$db_cache_advice 동적 성능 뷰를 이용하여 db cache 사이즈를 시뮬레이션 해본다.

column size_for_estimate         format 999,999,999,999 heading 'Cache Size (m)'
column buffers_for_estimate      format 999,999,999 heading 'Buffers'
column estd_physical_read_factor format 999.90 heading 'Estd Phys|Read Factor'
column estd_physical_reads       format 999,999,999 heading 'Estd Phys| Reads'

SELECT size_for_estimate, buffers_for_estimate,estd_physical_read_factor, estd_physical_reads
  FROM V$DB_CACHE_ADVICE
 WHERE name = 'DEFAULT'
   AND block_size    = (SELECT value FROM V$PARAMETER
                         WHERE name = 'db_block_size')
   AND advice_status = 'ON';

    Size for  Size      Buffers   Read     Phys Reads     Est Phys % dbtime
P    Est (M) Factr  (thousands)  Factr    (thousands)    Read Time  for Rds
--- -------- ----- ------------ ------ -------------- ------------ --------
D         80    .1           10    2.1          9,703       21,737     28.0
D        160    .2           20    1.1          5,315        5,540      7.1
D        240    .3           30    1.1          5,029        4,485      5.8
D        320    .4           40    1.0          4,948        4,186      5.4
D        400    .5           50    1.0          4,897        4,001      5.2
D        480    .6           59    1.0          4,862        3,869      5.0
D        560    .6           69    1.0          4,829        3,750      4.8
D        640    .7           79    1.0          4,796        3,628      4.7
D        720    .8           89    1.0          4,761        3,497      4.5
D        800    .9           99    1.0          4,740        3,419      4.4
D        864   1.0          107    1.0          4,723        3,359      4.3
D        880   1.0          109    1.0          4,709        3,306      4.3
D        960   1.1          119    1.0          4,668        3,152      4.1
D      1,040   1.2          129    1.0          4,649        3,083      4.0
D      1,120   1.3          139    1.0          4,621        2,979      3.8
D      1,200   1.4          149    1.0          4,607        2,929      3.8
D      1,280   1.5          159    1.0          4,599        2,899      3.7
D      1,360   1.6          169    1.0          4,594        2,882      3.7
D      1,440   1.7          178    1.0          4,594        2,880      3.7
D      1,520   1.8          188    1.0          4,592        2,875      3.7
D      1,600   1.9          198    1.0          4,580        2,829      3.7
          -------------------------------------------------------------

========> 결론 : 현재 PEMS DB의 db_cache 사이즈를 1.6GB로 변경
db_cache_size = 1.6GB(???)

##################################################
#### Redo buffer Size 성능 조정
##################################################

Check the statistic redo buffer allocation retries in the V$SYSSTAT view.
If this value is high relative to redo blocks written, try to increase the LOG_BUFFER size.
 
Query for the same is
 
select * from v$sysstat where name like 'redo buffer allocation retries'
or
select * from v$sysstat where name like 'redo blocks written';

혹은 v$sysstat 자료사전에서 서버 프로세스가 로그 정보를 저장했던 로그버퍼의 블록 수(REDO ENTRIES)와 로그버퍼의 경합으로
인해 발생한 대기상태에서 다시 로그 버퍼공간을 할당 받았던 불록 수(redo buffer allocation entries)를 확인한다.

=>이 SQL문에 의한 실행 결과
select name,value
from v$sysstat
where name in ('redo buffer allocation retries','redo entries');

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo entries                                                       23465374
redo buffer allocation retries                                           91=>0일 수록 좋은것

========> 결론 : 현재 quality DB의 log_buffer 사이즈를 14MB * 2 이상으로 init*.ora 파일에서 늘려준다.
log_buffer = 30M  
       
##################################################
#### java_pool Size 성능 조정
##################################################

** Java Pool advice => v$java_pool_advice

select JAVA_POOL_SIZE_FOR_ESTIMATE,JAVA_POOL_SIZE_FACTOR,ESTD_LC_LOAD_TIME
from v$java_pool_advice

JAVA_POOL_SIZE_FOR_ESTIMATE JAVA_POOL_SIZE_FACTOR ESTD_LC_LOAD_TIME
--------------------------- --------------------- -----------------
                          4                     1              9493
                          8                     2              9493

========> 결론 : 현재 PEMS DB의 java_pool_size 사이즈를 8MB 이상으로 init*.ora 파일에서 늘려준다.
java_pool_size=128M(20971520)

##################################################
#### Redo-log file  Size 성능 조정
##################################################
FAST_START_MTTR_TARGET='숫자값'으로 설정한다(V$MTTR_TARGET_ADVICE)
alter system set FAST_START_MTTR_TARGET=300

SQL> select ACTUAL_REDO_BLKS,TARGET_REDO_BLKS,TARGET_MTTR,ESTIMATED_MTTR,
    OPTIMAL_LOGFILE_SIZE,CKPT_BLOCK_WRITES  from v$instance_recovery;

ACTUAL_REDO_BLKS TARGET_REDO_BLKS TARGET_MTTR ESTIMATED_MTTR OPTIMAL_LOGFILE_SIZE
---------------- ---------------- ----------- -------------- --------------------
          942            18432          71             59                     49     
388462

The recommended optimal redolog file size is 49 MB as seen from column -OPTIMAL_LOGFILE_SIZE.
This is as per the setting of "fast_start_mttr_target" = 170

각 리두로그 사이즈 600M로 조정

$@# !! SQL 튜닝 전에

SQL>alter session set timed_statistics=true;
SQL>alter session set sql_trace=true;
요거 켜주기......

### REDO LOG 파일 재배치 해야함.(물리적으로 서로 다른 디스크 경로에 변경 배치한다)

ALTER DATABASE DROP LOGFILE GROUP 1;
ALTER DATABASE ADD LOGFILE GROUP 1 ('/ora_log/KMSMESV1/rdo1/redo01a.log','/ora_dump/KMSMESV1/rdo2/redo01b.log') SIZE 500M

ALTER DATABASE DROP LOGFILE GROUP 2;
ALTER DATABASE ADD LOGFILE GROUP 2 ('/ora_log/KMSMESV1/rdo1/redo02a.log','/ora_dump/KMSMESV1/rdo2/redo02b.log') SIZE 500M

ALTER DATABASE DROP LOGFILE GROUP 3;
ALTER DATABASE ADD LOGFILE GROUP 3 ('/ora_log/KMSMESV1/rdo1/redo03a.log','/ora_dump/KMSMESV1/rdo2/redo03b.log') SIZE 500M

ALTER DATABASE DROP LOGFILE GROUP 4;
ALTER DATABASE ADD LOGFILE GROUP 4 ('/ora_log/KMSMESV1/rdo1/redo04a.log','/ora_dump/KMSMESV1/rdo2/redo04b.log') SIZE 500M

ALTER DATABASE DROP LOGFILE GROUP 5;
ALTER DATABASE ADD LOGFILE GROUP 5 ('/ora_log/KMSMESV1/rdo1/redo05a.log','/ora_dump/KMSMESV1/rdo2/redo05b.log') SIZE 500M

ALTER DATABASE DROP LOGFILE GROUP 6;
ALTER DATABASE ADD LOGFILE GROUP 6 ('/ora_log/KMSMESV1/rdo1/redo06a.log','/ora_dump/KMSMESV1/rdo2/redo06b.log') SIZE 500M

### ADDITIONAL 1 : Disk I/O 튜닝

select tablespace_name,file_name,phyrds, phywrts
from dba_data_files df,v$filestat fs
where df.file_id = fs.file#;

===============> 결론 : 쿼리 결과 system 테이블 스페이스와 undo table space를 분리해야함. 
1.DB SHUTDOWN

2.undo datafile 이동 ex)F:\->H:\
 =>이동 후 기존 datafile 삭제 
3.db startup(mount까지)

alter database rename file 'E:\ora_data1\KMSMESP1\UNDOTBS01.DBF' to 'H:\ora_data4\KMSMESP1\undo_data\UNDOTBS01.DBF';
alter database rename file 'E:\ora_data1\KMSMESP1\UNDOTBS02.DBF' to 'H:\ora_data4\KMSMESP1\undo_data\UNDOTBS02.DBF';
alter database rename file 'E:\ora_data1\KMSMESP1\UNDOTBS03.DBF' to 'H:\ora_data4\KMSMESP1\undo_data\UNDOTBS03.DBF';

4.DB OPEN  

##### redolog 변경 샘플

ALTER DATABASE DROP LOGFILE GROUP 1;
ALTER DATABASE ADD LOGFILE GROUP 1 ('I:\ora_log\KMSMESQ1\rdo1\REDO01A.LOG', 'H:\ora_dump\KMSMESQ1\rdo2\REDO01B.LOG') SIZE 200M;

ALTER DATABASE DROP LOGFILE GROUP 2;
ALTER DATABASE ADD LOGFILE GROUP 2 ('I:\ora_log\KMSMESQ1\rdo1\REDO02A.LOG', 'H:\ora_dump\KMSMESQ1\rdo2\REDO02B.LOG') SIZE 200M;

ALTER DATABASE DROP LOGFILE GROUP 3;
ALTER DATABASE ADD LOGFILE GROUP 3 ('I:\ora_log\KMSMESQ1\rdo1\REDO03A.LOG', 'H:\ora_dump\KMSMESQ1\rdo2\REDO03B.LOG') SIZE 200M;

SHARED POOL SIZE의 계산방법
==============================
PURPOSE
---------
다음은 ORACLE DATABASE 의 SHARED POOL SIZE를 계산하는 방법에 대하여 알아보기로 한다.

Explanation
------------
오라클 instance를 background process + SGA라고 말할 때 처음 instance가 기동되면 SGA가 할당된다. 이 때 이 SGA를 구성하는 메모리 영역은 크게 3부분으로 구성된다고 할 수 있다.
- Shared pool, Redo log buffer, DB buffer cache.

여기서는 SGA를 구성하는 shared pool의 size를 시스템에 맞게 산출하는 방법에 대해서 알아본다.
Shared pool 영역 구성은 MTS 방식으로 접속된 session의 PGA, Shared SQL area 그리고 dynamic하게 할당되는 data structure로 구성된다.

1. Shared Pool
Shared pool은 ?/dbs/initSID.ora(parameter file)의 SHARED_POOL_SIZE라는 parameter로 그 크기를 지정하는데 default값은 3.5M를 갖게 된다.
일반적으로 shared pool이 얼마나 사용되는가 하는 문제는 application dependent하므로 각

application을 조사하는 게 필요하다.
시스템에서 필요로 하는 크기를 검사하기 위해 아주 큰 크기로 parameter file에 지정하여 dynamic하게 할당되는 SGA가 충분히 큰 값을 갖게 한 후, 검사가 끝난 다음 아래에서 계산된 size로
변경 해 주도록 한다.

2. 계산 공식
Session 당 최대 메모리 사용량(Max Session Memory)
* 동시 접속하는 User의 수
+ Shared SQL 영역으로 사용되는 메모리 양
+ Shared PLSQL을 위해 사용하는 메모리 영역
+ 최소 30%의 여유 공간
= Minimum Shared Pool

3. 계산 예제
(1) 적당한 user session에 대한 session id를 찾는다.

SVRMGR> select sid from v$process p, v$session s
where p.addr=s.paddr and s.username=''SCOTT'';
SID
----------
29
1 rows selected.

(2) 이 session id에 대한 maximum session memory를 찾는다.

SVRMGR> select value from v$sesstat s, v$statname n
where s.statistic# = n.statistic#
and n.name = ''session uga memory max''
and sid=29;
VALUE
-----------
273877
1 rows selected.

(3) Total shared SQL area를 구한다.

SVRMGR> select sum(sharable_mem) from v$sqlarea;
SUM(SHARAB
------------------
8936625
1 row selected.

(4) PLSQL sharable memory area를 구한다.

SVRMGR> select sum(sharable_mem) from v$db_object_cache;
SUM(SHARAB
------------------
4823537
1 row selected.

(5) Shared pool size를 계산한다.

274K shared memory * 400 users
+ 9M Shared SQL Area
+ 5M PLSQL Sharable Memory
+ 60M Free Space (30%)
= 184M Shared Pool

이 예제에서는 Shared pool의 size는 184M가 적당하다고 할 수 있다.

4. Shared Memory 부족 (ORA-4031)에 대한 대처 Ora-4031이 발생하는 원인은 2가지이다. 첫째 절대적으로 shared pool size가 작아서 나는 경우와, 둘째로 memory는 있으나 적재하려 하는

PL/SQL package가 너무커서 연속된 shared pool영역을 점유하지 못하는 경우가 있다.
만일 첫번의 경우라면 적당한 계산 과정으로 계산하여 parameter file에서 SHARED_POOL_SIZE를 늘려주고 ,두 번째 경우라면 다음과 같은 방법으로 에러를 피해 갈 수 있다.
- "Sys.dbms_shared_pool.keep" procedure사용.

[참고] 위 package를 사용하려면 ?/rdbms/admin/dbmspool.sql,
prvtpool.plb를 수행시켜 package를 create시킨 후 사용한다.

1. sga_target

이 parameter는 사용자가 원하는 크기만큼 sga를 지정하면 oracle kernel이 알아서(process수 etc)
shared_pool, db_cache_size등을 동적으로 할당하게 됩니다.
그냥 단순히 원하는 size만 지정하면 됩니다.
사용 예)
init.ora 에
sga_target = 300m
변경은 alter system set sga_target = 200m; 이런식으로 하면 됩니다만..
sga_target < sga_max_size입니다. sga_max_size는 oracle 10g에서도 변경 불가 입니다.

2. OLTP 속성에 관련된 parameter(shared_pool_size , db_cache_size)
sga_target , db_cache_size, shared_pool_size는 동적으로 크기를 변경할 수 있습니다.
단,db_cache_size + shared_pool_size <= sga_target 여야 합니다.
또한, sga_target < sga_max_size(변경불가) 입니다.
만약 sga_target = 300m로 지정하고

alter system set shared_pool_size = 100m;
alter system set db_cahce_size = 150m;

이렇게 하면 원하는 크기만큼 설정이 됩니다.

물론, shared_pool_size와 db_cache_size를 지정하지 않으면 오라클이 알아서(?)
내부적으로 shared_pool_size와 db_cache_size를 할당합니다.

가) sga_target만 설정한 경우
. sqlplus 에서 "show parameter sga_target" --- 지정된 값
. sqlplus 에서 "show parameter shared_pool_size" -- 0
. sqlplus 에서 "show parameter db_cache_size" -- 0

. alter system set sga_target = 200m; 으로 변경하면
. sqlplus 에서 "show parameter sga_target" --- 200m
. sqlplus 에서 "show parameter shared_pool_size" -- 0
. sqlplus 에서 "show parameter db_cache_size" -- 0

나) sga_targe=300m, shared_pool_size, db_cache_size를 지정한 경우
. sqlplus 에서 "show parameter sga_target" --- 지정된 값
alter system set shared_pool_size = 100m;
alter system set db_cahce_size = 150m;

. sqlplus 에서 "show parameter shared_pool_size" -- 100m
. sqlplus 에서 "show parameter db_cache_size" -- 150m

3. Batch 속성에 관련된 parameter(pga_aggregate_target)
Batch 속성을 갖는 DB는 주로 sorting 작업을 많이 하기 때문에 pga 영역의 확보가 절대적으로 필요합니다.
따라서 alter system set pga_aggregate_target = 1000m; 등등으로 설정하면 됩니다.

4. 그러면 얼만큼 메모리를 할당하면 될까요?
가) 일반적으로 OLTP인경우
SGA (65%) + PGA (15%) + O/S Overhead (20%)
예를 들어 전체 메모리가 10G 인경우
SGA ( 6G) , PGA( 2G ), O/S (2G)

나) 일반적으로 BATCH인경우
SGA (30%) + PGA (50%) + O/S Overhead (20%)
예를 들어 전체 메모리가 10G 인경우
SGA ( 3G) , PGA( 5G ), O/S (2G)

5. 그러면 4.에서 권고한 만큼 메모리를 할당/재할당 해야 하나요?
- 그건 그때 그때 달라요.. 경험이 필요하겠죠
원칙은 오라클에 가급적 많음 메모리를 할당하되, page나 swap이 빈번하게 발생하면 않되겠쬬!!!!!

OWI를 활용한 shared pool 진단 및 튜닝

아직도 Shared pool 설정에 어려움을 느끼십니까?

작성자 : 한민호(blubeard@nate.com)

필자가 Oracle Internal에 관심을 가지게 된 것은 얼마 되지 않지만 공부를 하면서 이렇게 자료를 만들어 정리를 해보는 것도 좋은 경험이 되고 여러 사람들과 지식을 공유하고 싶었기 때문에 OKM에 기고하게 되었다.

SGA에서 shared pool만큼 사이즈를 설정하기 어려운 Parameter가 없다. 그리고 이 shared pool의 크기는 너무 커도, 너무 작아도 문제가 된다. 즉, 얼마나 적절히 설정하느냐에 따라 DB의 성능을 좌우하게 된다. Parameter만 잘 설정해도 성능이 좋아진다니 이렇게 편하고 좋은 튜닝 방법이 어디 있을까 하는 생각도 든다. 하지만 shared pool의 크기를 아무 지식과 진단 없이 적절히 설정하기란 여간 까다로운 일이 아닐 수 없다. 특히 Row cache와 Library cache는 크기를 각각 설정하는 것이 불가능하기 때문에 초보자에겐 이런 것들이 어려움으로 다가올 수 있다. Shared pool을 자세히 알아 봄으로써 그러한 걱정들을 이번 기회에 덜 수 있다면 훌륭한 DBA가 되는데 도움이 되리라 생각된다. 이에 Shared Pool을 설정함에 있어 진단의 지표로 OWI를 사용할 것이다.

- Wait Event의 중요성

 Programming을 해본 사람이라면 동기화 문제에 대해 매우 잘 알고 있을 것이다. Oracle역시 수많은 Transaction에 의해 작업이 되기 때문에 이 때 발생하는 동기화 문제들을 해결하기 위해 수 많은 Latch, Lock, Pin을 사용하고 있다. 이 동기화란 것은 Serial한 작업이기 때문에 성능에 막대한 영향을 주게 된다. Wait Event는 이러한 동기화 작업에 있어서 Critical Section에 들어가지고 못하고 대기하는 작업들의 대기시간 동안 발생하는 이벤트이다. 이 때문에 Wait Event 발생을 줄이는 것은 중요한 일이고 이를 잘 분석하여 Tuning하는 것은 매우 효과적인 방법인 것이다. 그럼 이제 Shared Pool을 Wait Event의 관점에서 진단하고 분석해보기로 하겠다.

- Shared pool의 목적

Shared pool에 대해 간략히 설명을 해보자면 shared pool의 목적은 실행된 Cursor를 공유하여 CPU나 memory를 효율적으로 사용하는 데 있다. Cursor란 SQL의 경우 실행할 때 필요한 실행계획 같은 실행 정보를 담고 있는 SGA상에 할당된 Heap Memory를 말한다. 물론 공유 할 수 있는 것들은 다양하다. 공유할 수 있는 정보들을 나열하자면 SQL구문, 실행계획, PL/SQL소스와 그것들의 실행정보, table, view 같은 object 등이 있다. 이것들을 공유한다면 동일한 PL/SQL이나 SQL을 실행함에 있어 매번 실행계획을 만들며 hard parsing이 일어나는 부하를 예방할 수 있다.

- Shared pool의 구성요소

shared pool을 구성하고 있는 구성요소에 대해 알아보겠다. 우선 shared pool의 구성요소는 4가지로 나뉜다. Process목록, Session목록, Enqueue목록, Transaction목록 등이 할당된 Permanent Area와 SQL문을 수행하는데 필요한 모든 객체 정보를 관리하는 Library cache, dictionary 정보를 관리하는 Row Cache, 그리고 마지막으로 동적 메모리 할당을 위한 공간인 Reserved Area로 나눌 수 있다.

- Heap Manager를 통한 메모리 관리

메모리에 대한 할당 및 해제 작업은 Heap Manager를 통해 동적으로 관리가 된다. 이 Heap Manager에 대해 간략히 알아보면 Top-level의 Heap과 그 하위에 여러 개의 Sub-Heap을 포함하는 구조를 이루고 있다. 이 Heap은 또한 linked list구조의 Extent들로 구성이 되어 있으며 Extent는 여러 개의 chunk로 구성되어있다. 실제적으로 chunk의 사용 현황에 대해 알고 싶다면 X$KSMSP라는 View를 통해 관찰 할 수 있을 것이다.

- Chunk의 관리

Chunk는 4가지 상태로 관리가 된다. 그 4가지 상태는 Free, Recreatable, Freeable, Permanent다. 이러한 chunk들의 상태에 따라 linked list가 구성되는 것이다. 상태의 이름만으로도 그것이 어떤 상태인지 알 수 있을 것이다. 더 정확히 설명 하자면 Free는 즉시 사용 가능한 상태를 말한다. 이 Free 상태의 chunk들로 묶여 있는 linked list가 free list인 것이다. 구체적으로 설명하면 이것은 255개의 bucket이 있고 각 bucket당 free chunk들이 linked list구조로 연결되어있다. 이때 bucket은 각각의 정해진 기준의 크기 이하의 chunk들로만 구성되어 있다. 이러한 이유로 bucket이 아래로 갈수록 chunk들의 크기가 크다. Recreatable은 재생성 가능한 상태이다. 뒤에서 다시 설명하겠지만 이것은 unpinned(현재 사용되고 있지 않은)일 때 재사용이 가능하다. 쉽게 말하자면 이것은 사용이 되었었지만 다시 사용될 확률이 낮아져서 재사용이 가능한 상태가 된 것이며, 현재 사용 중이 아니라면 chunk를 재사용할 수 있도록 이러한 상태의 chunk를 묶어 LRU list로 관리한다. (관련 뷰 : X$KGHLU) 그리고 Freeable은 session이나 call 동안에만 필요한 객체를 저장하고 있는 상태이며 이는 session등이 금방 끊길 수도 있기 때문에 chunk가 필요할 때 할당의 대상이 되지는 못한다. Permanent는 말 그대로 영구적인 객체를 저장하고 있는 상태이며 이것 역시 사용할 수 없는 chunk다.

실제 Heap Dump를 이용하면 R이 앞에 붙어서 상태가 정의 되어 있는 것을 볼 수 있는데 이것은 SHARED_POOL_RESERVED_SIZE란 Parameter를 통해 발생한 chunk들이다. 이 chunk도 적절히 사용하면 매우 중요한 튜닝 요소가 될 수 있다. 이것에 대해 oracle 매뉴얼에서는 PL/SQL block이 많이 사용되는 경우를 예로 들고 있다. 즉, 이것은 large chunk를 위해 할당된 공간인 것이다. Parameter를 정해 주지 않는다면 설정된 shared_pool_size의 5%가 default value로 분류된다. Steve Adams의 Oracle Internal이나 매뉴얼에서도 5000byte 이상의 큰 object를 위한 공간이라고 설명한다. 이는 large chunk가 요구되지 않는다면 굳이 설정할 필요가 없다는 말도 되는 것이다. 이러한 튜닝 요소에 초점을 맞추어 설정하면 되는 parameter인 것이다. 이것들 역시 linked list로 관리되며 명칭은 Reserved Free list라고 부른다.

(아래 그림 1을 참조한다면 이해하는 데에 도움이 될 것이다.)

[그림 1] Shared Pool Heap 구조(출처 : Advanced OWI in Oracle 10g / ㈜엑셈)

정리를 해보자면 chunk는 사용할 수 있는 것과 없는 것으로 나눌 수 있다. 그 중에 실제 chunk 할당 과정에서 latch경합을 벌여 Wait Event가 발생하기 때문에 free list와 LRU list에서 관리되고 있는 chunk들에 주목을 할 필요가 있을 것이다. 왜냐하면 운영 시 peak time때의 할당된 chunk의 총 크기가 곧 shared pool의 size를 적절히 결정하는 데에 중요한 역할을 하기 때문이다. 이것에 대한 설명은 Wait Event의 발생과 연관 지어 이야기 해 보겠다.

- Shared Pool의 관리

Chunk를 할당하는 과정에서 반드시 필요한 것이 shared pool latch의 획득이다. 하지만 이러한 shared pool latch는 아쉽게도 shared pool당 단 1개 밖에 존재하지 않는다. 이것은 즉, chunk를 동시에 할당 받아야 할 상황이라면 이것을 획득하는 과정에서 경합을 벌이게 된다는 의미이다. 무엇 때문에 이렇게 shared pool latch 수를 적게 만들어 놓았는지 정확히 알 수는 없지만 여러 개를 만들어 놓았다면 역시나 동기화 문제를 관리하는데 있어 어려움이 있기 때문이 아닐까 싶다. 다행히도 Oracle 9i부터는 Hidden Parameter인 _KGHDSIDX_COUNT를 이용하여 하나의 shared Pool을 7개까지의 Sub-Pool로 나누어 관리하는 것이 가능해졌다. 그리고 더 반가운 소식은 각각의 Sub-pool당 독자적인 free list, LRU list, shared pool latch를 가짐으로 인해 부족했던 자원에 대한 경합을 그나마 감소시킬 수 있게 되었다. 그러나 경합은 감소했을지 몰라도 CPU의 개수나 SHARED_POOL_SIZE가 충분히 크지 않다면 ORA-4031에러의 발생위험이 1개의 shared pool로 관리됐을 때보다 더 높다는 것이다. 그래서 권장하는 방법이 CPU 4개에 SHARED_POOL_SIZE가 250m이상일 때 Sub-pool을 사용하는 것이다. 한 때는 하나의 Sub-pool에 할당 가능한 chunk공간이 없으면 다른 Sub-pool에 사용 가능한 free chunk가 있음에도 찾지 못했던 버그도 있었다. shared pool latch에 대해 좀 더 알아보자면 실제적으로 shared pool latch를 소유하는 시간이 shared pool latch를 대기하게 하는 중요한 이유이다. 때문에 latch를 획득한 후에 일어나는 작업들을 알면 경합의 포인트를 알 수 있을 것이다.

 - Chunk의 할당과정

Shared pool latch를 획득하게 되면 우선 free chunk를 찾기 위해 free list를 탐색한다. 그리고 적절한 free chunk가 있다면 할당을 받지만 없다면 LRU list를 탐색하게 된다. 이것마저도 찾지 못한다면 Reserved Free List를 탐색하고 이것 역시 실패하면 Spare Free Memory를 탐색하게 된다. 이 모든 과정이 실패가 되었을 때 ORA-4031에러가 발생하게 되는 것이다. 이 과정에서 할당을 받게 된다면 딱 필요한 크기만 할당을 받고(split) 나머지는 다시 free list에 등록이 되기 때문에 free list가 할당이 된다고 해서 반드시 짧아지는 것은 아니다. 그리고 적절한 chunk를 찾기 위해 위에서와 같이 여러 과정은 거치지만 이 과정은 생각보다 매우 빠른 시간 안에 이루어진다. 하지만 이것들이 다수의 작업이 된다면 경합에 대한 wait time은 피부로 느껴질 것이다. 여기서 이제껏 언급이 없었던 Spare Free Memory에 대해 궁금해하는 분이 많을 꺼 같아 간단하게 설명하자면(이 내용은 Steve Adams의 Oracle Internal에 내용이 있다) instance가 start up 되었을 때 shared pool size에 정해진 크기의 절반만이 shared pool에 할당된다. 이것은 성능을 극대화 하는데 에도 연관이 있으리란 생각이 든다. Chunk의 수가 줄면 그 만큼 free list가 짧아지기 때문에 그에 대한 탐색시간도 짧아지고 shared pool latch의 소유 시간 역시 짧아지기 때문에 메모리를 숨겨놓지 않았을까 생각된다. 위의 과정들을 미루어 짐작해볼 때 shared pool latch의 소유시간은 free list의 길이와 얼마나 빨리 적절한 chunk를 찾느냐에 따라서 결정된 다는 것을 알 수 있을 것이다. 그럼 free list의 길이가 길어지는 것은 어떠한 경우 일까? 바로 그것은 chunk split가 다량으로 발생하여 단편화(fragmentation) 되었을 때이다. 이러한 경우 free list의 길이가 길어지게 되는 것이다. 단편화는 hard parsing에서 일어나는 것인데 hard parsing에 대해 모르는 독자들을 위하여 간단히 설명하면 처음 실행하는 SQL문이 있다면 이것에 대한 실행정보를 저장하고 있는 Heap Memory 할당이 필요한데 이 Heap이 바로 chunk인 것이다. 이러한 실행계획을 만들고 chunk에 할당하는 과정은 매우 부하가 있기 때문에 hard parsing이라고 이름 붙여진 것이다. 이런 과도한 단편화로 인해 shared pool latch의 경합만 가중 시키는 것이 아니다. 큰 chunk 할당이 요구되는 hard parsing이 이루어 질 때 적절한 free chunk를 찾지 못하여 ORA-4031에러를 유발하게 된다.

- Shared Pool Size 설정

지금까지 설명했던 것들을 가지고 shared pool size에 대해 결론을 내 보면, 첫째로, memory가 무조건 크다는 생각으로 shared pool을 늘리면 안 된다고 볼 수 있다. 이것은 오히려 free list의 길이만 늘어나게 되기 때문이다. 그리고 V$SGASTAT를 통해 확인한 shared pool의 free memory가 작다고 해서 SHARED_POOL_SIZE를 늘려서는 안 된다. Free memory는 단지 free chunk의 합이기 때문이다. 이는 즉 LRU list, reserved list, spare memory도 있기 때문에 크게 문제가 되는 것은 아니라는 말이다. 지금까지 설명한 것을 고려해 본다면 적절한 크기를 정하는 데는 매우 도움이 될 것이다. 만약 초보 DBA라면 Oracle에서 제공하는 advice를 이용하는 것도 괜찮은 방법일 듯싶다. OEM구성을 하여 Enterprise Manager를 보면 memory tab에서 shared pool 부분의 advice버튼만 클릭하면 적절한 shared pool 크기에 대한 지침을 제공하고 있다.(그림 2를 참조하시오) 이것이 아주 정확한 척도가 되지는 못할지라도 초보 DBA에게는 매우 매력 있는 기능임에는 틀림이 없다. 이 지침은 되도록이면 peak time 이후에 이용하는 것을 권장한다.

\

[그림 2] Enterprise Manager의 Shared pool advice

Shared Pool Size를 적절히 줄이게 되면 free list 탐색시간의 감소로 인해 hard parsing에 의한shared pool latch의 경합을 줄이는 효과를 볼 수 있지만 ORA-4031에러의 위험이나 상주할 수 있는 공유 객체의 수가 줄어들어 LRU list를 자주 이용하기 때문에 오히려 부가적인 hard parsing을 발생시킬 수 있음에 유의해야 한다. 이때 오르내리는 객체가 프로시저나 패키지라면 그 부하는 상당할 것이다. 이에 대비하여 DBMS_SHARED_POOL.KEEP을 이용하여 메모리에 고정시키는 방법도 매우 유용하다. Shared pool을 flush해도 내려가지 않기 때문이다. Shared pool latch가 발생하는 것을 가장 줄일 수 있는 방법은 bind변수의 사용이나 CURSOR_SHARING Parameter를 설정하는 것이다. CURSOR_SHARING parameter는 SQL문장을 자동으로 bind변수치환을 해주는 변수이다. 위에서 언급했던 Flush Shared pool에 대하여 잠시 설명을 하고 가면 alter system flush shared_pool;이란 명령을 통해 shared pool을 flush 시킬 수 있다. 이 작업은 단편화된 free chunk에 대해 coalesce 작업이 이루어 지기 때문에 유용하지만 NOCACHE 옵션이 없는 sequence가 있다면 예상치 못한 gap이 생길 수도 있기 때문에 유의해야 한다.

 - Library Cache Latch와 Shared Pool Latch의 관계

Shared pool을 튜닝 하는데 있어 반드시 shared pool latch 획득만이 문제가 되는 것은 아니다.  바로 shared pool latch 획득 이전에 library cache latch의 획득이 먼저 있기 때문이다. 이것을 비롯한 parsing에 대해 좀더 이해를 돕기 위해 아래의 그림 3을 참조하기 바란다.

[그림 3]  parsing시 shared pool latch와 library cache latch

 그림 3를 보면 알 수 있듯이 hard parsing이 soft parsing 보단 부하가 큰 작업 임을 알 수 있다. 또한 library cache latch와 shared pool latch 획득 시점을 미루어 보아 동시에 많은 세션이 library cache latch를 획득하려고 하게 되면 이것에 대한 병목 현상으로 shared pool latch에 대한 경합은 상대적으로 줄어들 수 있을 것이란 예상도 가능하다. 그렇다면 이렇게 shared pool latch에 영향을 주는 library cache latch에 대해서도 자세히 알아볼 것이다.

- Library Cache의 구조와 관리

Library cache에 할당 받는 Heap memory는 shared pool latch를 걸고 할당 받은 free chunk이다. 이때 Library Cache Manager(KGL)에 의해 관리되는데 이는 내부적으로 Heap Manager(KGH)를 이용하는 것이다. 이때 할당된 free chunk는 LCO(Library Cache Object)와 handle을 저장하는데 사용된다. Library Cache Memory는 크게 hash function, bucket, handle list, LCO로 구성되어 있다. 하나씩 설명을 해보면 hash function은 bucket을 결정하기 위한 연산을 수행하는 함수로 보면 된다. 객체에 따라 SQL의 경우 SQL TEXT를 그대로 numeric 값으로 변환하여 bucket을 결정하고 SQL외의 객체들은 schema, object name, DB link를 numeric 값으로 변환하여 bucket을 결정한다. Bucket의 성장은 LCO의 크기가 매우 많아져서 성장이 불가피할 때 성장하게 되는데 이때 대략 2배 크기의 소수로 확장하게 된다. 그리고 bucket의 초기 값은 _KGL_BUCKET_COUNT로 설정이 가능하다.

아래 그림 4, 5는 필자가 그린 handle과 LCO의 구조, 그리고 그것에 대한 간략한 설명이다.

[그림 4] Handle의 구조

[그림 5] LCO의 구조

[그림 6] Library cache 구조 (출처 : Advanced OWI in Oracle 10g / ㈜엑셈)

위의 그림 4, 5를 숙지하였다면 handle이 무엇이고 LCO가 무엇인지, 또 이것이 저장하는 정보에 대하여 알 수 있었을 것이라고 예상 된다. 이제 그림 6을 보면 대략적인 구조가 머리 속에 들어 올 것이다. 여기서 특징적인 것은 SQL의 경우 child table을 갖는 다는 점이고 그 child table이 저장하고 있는 실제적 자식 handle과 LCO는 익명 리스트로 관리되고 있다는 점이다. 물론 PL/SQL의 경우는 조금 다르다. 이것의 구조는 그림 7,8을 보면 좀더 명확히 알 수 있다.

[그림 7] SQL의 LCO구조

[그림 8] PL/SQL의 LCO구조

PL/SQL과 일반 SQL은 Heap Memory를 사용하는데 있어서도 차이점이 있다는 것을 금방 알 수 있을 것이다.

- Oracle의 놀라운 메커니즘 Invalidation 자동화

Oracle의 장점 중 하나인 invalidation 자동화에 대해 잠시 설명을 해 보겠다. 이에 대한 내용이 자세히 언급된 곳은 많지 않았을 것이다. 우선 그림 7을 보면 SQL LCO의 구조에 대해 잘 보여주고 있다. Parent LCO와 두 개의 child LCO가 보일 것이다. 보통 이런 방식으로 저장되는 경우는 각각 다른 schema에 같은 이름의 table을 가지고 있을 때 동일한 SQL을 query할 수 있을 것이다. 쉽게 예를 들면 scott1과 scott2란 두 user가 있는데 이들이 각각 emp라는 table을 가지고 있고 select * from emp;라는 동일한 query를 두 user가 전송했을 때 이러한 구조로 LCO가 생성되게 되는 것이다.

이때 child LCO의 Dependency Table에는 scott1의 경우 scott1.emp table의 LCO를 참조하고 cott2의 경우 scott2.emp table의 LCO를 참조하게 되는 것이다.

이 참조 과정에서 handle의 lock hold/wait list, pin hold/wait list를 이용하게 된다. 이때 중요한 것이 바로 lock이다. 참조하는 LCO의 lock을 shared mode로 잡아 사용하고 해제하는 것이 아니라 null mode로 유지하는 것이다. 이것이 바로 나중에 참조하는 table이 변경되었을 때 이 lock list를 없애 버림으로 인해 SQL Cursor(select * from emp;)를 따로 어떤 프로세스를 통해 invalidation한 상태로 만들지 않고 자동으로 invalidation하게 하는 것이다.

- Library cache에서 발생하는 Wait Event 소개

그럼 이제는 library cache에서 발생하는 주요 Wait Event들에 대해 알아보자. library cache에서 일어나는 자주 발생하는 Wait Event에는 3가지가 있다. 그것은 latch:library cache, library cache lock event, library cache pin event이다. 이것은 명칭에서도 알 수 있듯이 latch, lock, pin을 소유하기 위해 대기하는 event 이다. 이 동기화 자원들에 대하여 자세히 알아보자.

- Library Cache Latch

 우선 library cache latch에 대해 알아보면 이 latch는 library cache 영역을 탐색하고 관리하는 모든 작업을 보호하는 데에 그 목적이 있다. Latch의 수는 일반적으로 shared pool latch의 수 보다는 많다. 왜냐하면 CPU개수보다 큰 소수 중 가장 작은 소수로 설정되어 있기 때문이다. 이 때문에 library cache latch를 획득하려는 프로세스가 CPU개수 보다 적다면 library cache latch 자원은 손쉽게 획득하는 대신에 shared pool latch를 가지고 경합할 확률이 높을 것이고 library cache latch의 개수보다 많은 프로세스가 획득하려 한다면 library cache latch를 가지고 경합을 하느라 shared pool latch의 경합은 상대적으로 줄어들 수 있다. 그렇다면 이러한 library cache latch 경합을 가중시키는 작업엔 어떤 것이 있을까? 바로 hard parsing이나 soft parsing이 과다한 경우와 자식 LCO가 많아 anonymous list의 탐색시간이 증가하는 경우이다. 그리고 SGA영역이 page out되는 극히 드문 경우를 예로 들어 볼 수 있다. 이에 대한 해결 책으로 PL/SQL block 내에서 자주 실행되는 SQL에 대해서는 Static SQL을 사용하면 된다.(Dynamic SQL은 안됨) LCO를 pin하여 soft parsing 없이도 cursor를 계속 재사용 할 수 있는 효과를 볼 수 있다.

그리고 SESSION_CACHED_CURSORS Parameter를 이용하여 3회 이상 수행된 SQL에 대해서는 PGA영역에 cursor의 주소 값과 SQL text를 저장하여 cursor 탐색 시 성능향상을 기대할 수 있다.(library cache latch도 획득해야 하고 soft parsing도 발생하지만 library 탐색시간이 매우 짧기 때문에 성능향상이 된다.) 하지만 application에서 SQL 수행 시 마다 log on/off하는 경우 이 parameter는 세션이 끊어지면 소용이 없기 때문에 성능 향상을 기대하기 어렵다. 때문에 connection pool을 함께 이용하는 것이 현명한 방법이다. 그리고 마지막으로 SGA영역의 page out의 경우는 잘 발생하지 않지만 만약을 대비해서 LOCK_SGA값을 TRUE로 하여 고정시켜 놓는 것이 좋다.

- Library Cache Lock

 Library cache lock(관련 뷰 : X$KGLLK, 10g-DBA_DDL_LOCKS, DBA_KGLLOCK)에 대해 설명하면 이것은 handle에 대해 획득하는 lock이라 볼 수 있다. 이것의 목적은 동일 object의 접근 및 수정에 대해 다른 client들로부터 예방하는 것이다. Lock은 세가지 모드를 갖게 되는데 shared, exclusive, null mode가 있다. Shared로 획득하는 경우는 parsing과 실행단계이고 exclusive는 procedure 생성이나 변경의 경우, recompile시와 table 변경의 경우가 있다. 보통 참조하는 LCO에 대해 exclusive모드와 shared모드로 각각 획득하려는 경합으로 인해 waiting이 발생하게 된다. null mode는 보통 실행 후에 참조하는 객체에 대해 null mode로 lock을 소유하게 된다. 

- Library Cache Pin

 마지막으로 library cache pin(관련 뷰 : X$KGLPN, 10g-DBA_KGLLOCK)에 대해 설명하면 Heap data에 pin을 꽂아 변경되지 않도록 보장하는데 그 목적이 있다. 이것은 반드시 library cache lock을 획득한 후에 획득해야 한다. 이것은 shared와 exclusive 두 mode가 지원되며 이렇게 획득하는 경우를 살펴보면, shared mode로 획득하는 경우는 Heap Data를 읽을 때 pin을 걸어 object들의 변경을 예방하며 exclusive mode로 획득하는 경우는 Heap Data를 수정할 때이다. Heap data를 수정하는 경우는 procedure recompile이나 hard parsing 발생 시 execution plan을 세우는 과정에서 참조하는 LCO가 변경되면 안되기 때문에 pin을 걸어 보호한다.

 이때 발생하는 library cache lock이나 library cache pin은 V$SESSION_WAIT의 P1, P2, P3 column과 X$KGLOB View를 이용하여 object 정보를 구할 수 있다. P1=handle address, P2=lock address, P3=mode*100+namespace (lock mode : 1=null, 2=shared, 3=exclusive)이기 때문에 V$SESSION_WAIT을 조회하여 P1값을 구한 후 P1과 X$KGLOB의 kglhdadr column과 비교하여 kglnaobj column을 query해서 object의 이름을 구할 수 있다.

- 맺음말

위의 library caches에 관한 내용들을 종합적으로 정리해서 결론을 내려보면 Wait Event를 통해 그 Event가 왜 발생하였는가를 인지할 수 있다면 정확한 진단 역시 가능함을 알 수 있다. 그리고 덧붙여 내부적인 Event의 발생과정을 아는 것이 튜닝을 하게 될 때 넓은 시야를 가질 수 있도록 도와주고 좀더 효율적이고 정확한 튜닝을 할 수 있는 계기가 되리라 확신한다.

참고자료

OWI를 활용한 오라클 진단 & 튜닝 / ㈜엑셈 역

Advanced Oracle Wait Interface in 10g / 저자 조동욱 / ㈜엑셈

Oracle 8i Internal Services for Waits, Latches, Locks, and Memory / Steve Adams / O’Reilly

Manual

Oracle Database Concepts 10g R2

Oracle Database Reference 10g R2

sqlplus를 이용하여 sysdba 계정에서 아래의 파일을 실행합니다.

SQL> @dbmsutil.sql <== ORACLE_HOME\rdbms\admin 에 파일이 존재함
SQL> @prvtutil.plb <== ORACLE_HOME\rdbms\admin 에 파일이 존재함

SQL> grant execute on dbms_system to system;

- DBMS_SYSTEM.set_sql_trace_in_session(sid, serial#, TRUE)
지정된 session에 대해 SQL trace를 실행하여 user_dump_dest parameter에 지정된 위치에 trace file이 생성된다.

- DBMS_SYSTEM.set_sql_trace_in_session(sid, serial#, FALSE)
지정된 session에 대한 SQL trace를 해제한다.

[예제] system user에서 scott user를 지정하여 SQL trace 생성하기.

sqlplus system/manager

SQL> select sid, serial#
from v$session
where username = 'SCOTT';

SID SERIAL#
--------- ---------
8 12

SQL> exec sys.dbms_system.set_sql_trace_in_session(8, 12, TRUE);

-> scott user에 대한 SQL trace가 실행된다.

SQL> exec sys.dbms_system.set_sql_trace_in_session(8, 12, FALSE);

-> scott user에 걸린 SQL trace가 해제된다.

==> 위와 같이 trace 처리된 내역은 DBMS가 설치된 server의 user_dump_dest 경로에 trc 파일로 생성됩니다.

이렇게 해서 생성된 trace 파일을 TKPROF를 이용하여 가독이 용이한 형태로 내용을 변환하여 확인하면 됩니다.

ex) tkprof '원본파일' '변환파일' [option] sys=no aggregate=no explain=계정ID/계정PWD
--> tkprof C:\oracle\admin\orcl\udump\orcl_ora_3012.trc c:\test.text sys=no aggregate=no explain=scott/tiger

SQL 튜닝, 대기현상(내부 매커니즘)을 분석 할때 사용하는 툴

즉, 개발환경에서 문제없이 수행 되는 SQL 문장이 운영환경에서 느린 성능을 보이는 경우 SQL Trace 를 이용하면 원인 분석이 가능하다.

10046 이벤트를 사용하며 4가지 레벨로 제어 한다

LEVEL 1 : SQL 문장의 실행정보(Parse, Execute, Fetch 단계와 Row source operation 결과)만을 제공한다

LEVEL 4 : LEVEL 1 + 바인드 변수값을 제공한다

LEVEL 8 : LEVEL 1 + 대기정보를 제공한다

LEVEL 12 : LEVEL 4 + LEVEL 8, 즉 SQL 문장의 실행정보와 바인드 변수 값, 대기정보를 제공한다

현재 세션 Trace

SQL> ALTER SESSION SET sql_trace=TRUE; -- trace file 시작
SQL> ALTER SESSION SET sql_trace=FALSE; -- trace file 종료

SQL> ALTER SESSION SET EVENTS '10046 trace name context forever, level 12';
SQL> ALTER SESSION SET EVENTS '10046 trace name context off';

다른 세션 Trace

- dbms_system 사용

SQL> EXEC DBMS_SYSTEM.set_sql_trace_in_session(sid=>123, serial#=>1234, sql_trace=>TRUE); --sql trace 입장
SQL> EXEC DBMS_SYSTEM.set_sql_trace_in_session(sid=>123, serial#=>1234, sql_trace=>FALSE);

SQL> EXEC DBMS_SYSTEM.set_ev(si=>123, se=>1234, ev=>10046, le=>8, nm=>' '); -- 이벤트 입장

SQL> EXEC DBMS_SYSTEM.set_ev(si=>123, se=>1234, ev=>10046, le=>0, nm=>' '); -- nm은 name이다.

--SQL> exec dbms_system.set_ev(7, 10, 65535, 10, 'controlf');
--SQL> exec dbms_system.set_ev(8,1056,65535,10,'PROCESSSTATE');

- oradebug 사용

SQL> CONN sys/password AS SYSDBA; --반드시 sysdba 권한 유저로 실행
(SQL> ORADEBUG SETMYPID; -- Debug current session.)

or
SQL> ORADEBUG SETOSPID 1234; -- Debug session with the specified OS process.

or
SQL> ORADEBUG SETORAPID 123456; -- Debug session with the specified Oracle process ID.

SQL> ORADEBUG unlimit; -- Trace file 크기제한 無

SQL> ORADEBUG EVENT 10046 TRACE NAME CONTEXT FOREVER, LEVEL 12;
SQL> ORADEBUG TRACEFILE_NAME; -- Display the current trace file.

/u01/app/oracle/product/10.2.0/db_1/rdbms/log/dhdb_ora_10803.trc
SQL> ORADEBUG EVENT 10046 TRACE NAME CONTEXT OFF;

10g 부터 SQL Trace 로 캡쳐 불가능한 작업을 dbms_monitor 패키지를 사용하여 Client ID, Service/Module/Action 등을 Trace 할 수 있다

이때, 여러 개의 세션에 대한 Trace 파일이 생성되며, trcsess 툴을 이용해 하나의 Trace 파일로 만들 수 있다

exec dbms_monitor.client_id_trace_enable(client_id=>'12345', waites=>true, binds=>true);

exec dbms_monitor.serv_mod_act_trace_enable(123456);

exec dbms_monitor.session_trace_enable(1234567);

또한, dbms_monitor 패키지를 이용하면 세션이 아닌 Client ID 나 Service/Module/Action 명을 기준으로 통계정보(Statistics)를 수집 할 수 있다

client_id_stat_enable, serv_mod_act_stat_enable 을 통해 수집된 정보는 V$CLIENT_STATS, V$SERV_MOD_ACT_STATS 뷰를 통해 관찰 가능하다

cf.alter session set events 'immediate trace name controlf level 10; --> 이 경우는 event 번호가 없으므로 name으로 trace를 생성한다.

=====================================================

출처 및 참고자료 : Advanced Oracle Wait Interface in 10g

=====================================================

special thank's to eddy

CPU부하 급증 SQL 찾기
먼저 시스템 자원현황을 살펴보기 위해서 unix에서 top을 실행한다.
[KAMCO:/oracle/app/oracle/product/806/work]# top
load averages:  1.54,  1.47,  2.07                                                         12:24:08
1461 processes:1457 sleeping, 2 stopped, 2 on cpu
CPU states:     % idle,     % user,     % kernel,     % iowait,     % swap
Memory: 9216M real, 211M free, 9434M swap in use, 7976M swap free
  PID USERNAME THR PRI NICE  SIZE   RES STATE   TIME    CPU COMMAND
17334 oracle     1  51    0 2510M 2488M sleep  36:46  2.24% oracle
29538 root       5  55    0 4808K 3632K sleep   3:50  1.48% save
29536 root       5  53    0 8048K 6864K sleep   3:34  1.47% save
29537 root       5  60    0 4768K 3648K sleep   0:22  1.35% save
24582 root       1   0    0  414M 1288K sleep 150.0H  0.86% rtf_daemon
9781 oracle    11  58    0 2510M 2481M sleep 933:20  0.74% oracle
6993 oracle     1  20    0 2509M 2485M cpu9   83.3H  0.57% oracle
2208 oracle     1  50    0 2515M 2492M sleep   0:01  0.52% oracle
2211 oracle     1   0    0 2592K 1712K cpu8    0:00  0.36% top
  476 tuxkigum  11  50    0 2524M 2491M sleep  45:13  0.32% oracle
  470 tuxkigum  12   2    0 2522M 2491M sleep  45:24  0.12% oracle
  474 tuxkigum  12  58    0 2524M 2490M sleep  41:19  0.10% oracle
25911 kamzone   11  14    2 2510M 2486M sleep   2:00  0.10% oracle
8824 xwnts     39  23   12  322M   51M sleep  82:17  0.10% java
17692 oracle     1  25    0 2515M 2491M sleep 111:29  0.09% oracle

이중에서 cpu의 사용량이 많은 프로세스(17334)에 대해서 어떤 SQL이 사용되고 있는지
살펴보자. 아래의 SQL을 cpu_overhead.sql로 저장하고 실행한다.
---------------------------------------------------------------------------------------
-- programed by Lee Chang Kie --
ttitle 'Cpu Overhead SQL Check'
clear screen
set verify  off
set pagesize 200
set linesize 110
set embedded off
set feedback off
col col0 format a25 heading "Sid-Serial"
col col1 format a10 heading "UserName"
col col2 format a10 heading "Schema"
col col3 format a10 heading "OsUser"
col col4 format a10 heading "Process"
col col5 format a10 heading "Machine"
col col6 format a10 heading "Terminal"
col col7 format a20 heading "Program"
col col8 format 9 heading "Piece"
col col9 format a8 heading "Status"
col col10 format a64 heading "SQL"
!rm -f ./cpu_overhead.lst
spool cpu_overhead.lst
Select A.sid||','||A.serial# col0,
       A.username col1,
       A.schemaname col2,
       A.osuser col3,
       A.process col4,
       A.machine col5,
       A.Terminal col6,
       upper(A.program) col7,
       C.piece col8,
       A.status col9,
       C.sql_text col10
  From v$session A, v$process B, v$sqltext C
Where B.spid = '&1'
   and A.paddr = B.addr
   and C.address = A.sql_address
order by C.piece;
spool off
----------------------------------------------------------------------------------------
[KAMCO:/oracle/app/oracle/product/806/work]# sqlplus internal
SQL*Plus: Release 8.1.7.0.0 - Production on Fri Mar 4 13:01:40 2005
(c) Copyright 2000 Oracle Corporation.  All rights reserved.
Connected to:
Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production
With the Partitioning option
JServer Release 8.1.7.4.0 - Production
SQL> @cpu_overhead
그러면 다음과 같이 프로세스 번호를 입력하라고 뜰 것이다.
Enter value for 1:
top명령을 실행했을 때 가장 상위에 나타는 프로세스ID(17334)를 입력한다.
그러면 아래와 같이 부하를 가중시키는 SQL이 검출될 것이다.
필요시 힌트, 인덱스정책, 실행계획등이나 트레이스를 떠서 필요한 튜닝을
수행해야 할 것이다.
                                            Cpu Overhead SQL Check
Sid-Serial                UserName   Schema     OsUser     Process    Machine    Terminal
------------------------- ---------- ---------- ---------- ---------- ---------- ----------
Program              Piece Status   SQL
-------------------- ----- -------- ----------------------------------------------------------------
664,9791                  KAMCO      KAMCO      tuxkigum   23914      KAMCO
SVZIPSND@KAMCO (TNS      0 ACTIVE   SELECT A.LOAN_NO LOAN_NO,A.LOAN_TYPE LOAN_TYPE,NVL(A.SANGYE_DATE
V1-V3)

664,9791                  KAMCO      KAMCO      tuxkigum   23914      KAMCO
SVZIPSND@KAMCO (TNS      1 ACTIVE   ,' ') SANGYE_DATE,NVL(A.RUPT_DATE,' ') RUPT_DATE,NVL(A.SANSIL_DAV1-V3) 

2. 위에서 나온 sid를 확인하여 아래 쿼리 수행 후 cpu 부하 유발 sql을 골라내어 튜닝대상으로 지정한다.

select s.sid, q.sql_text        
  from v$sql q, v$session s       
  where q.address=s.sql_address   
    and q.hash_value=s.sql_hash_value
    and s.sid in (&sid)
    ;