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

SELECT a.ksppinm "Parameter", b.ksppstvl "Session Value",
  2         c.ksppstvl "Instance Value"
  3    FROM x$ksppi a, x$ksppcv b, x$ksppsv c
  4   WHERE a.indx = b.indx AND a.indx = c.indx
  5         AND a.ksppinm LIKE '%kgl_large_heap%';

Parameter
--------------------------------------------------------------------------------

Session Value
--------------------------------------------------------------------------------
--------------------
Instance Value
--------------------------------------------------------------------------------
--------------------
_kgl_large_heap_warning_threshold
52428800
52428800

출처 : http://gampol.tistory.com/entry/Memory-Notification-Library-Cache-Object-loaded-into-SGA

조치방법: (다음과 같이 조치후 서버 restart)
sqlplus “/as sysdba”
alter system set "_kgl_large_heap_warning_threshold"=8388608 scope=spfile ;
shutdown immediate
startup

 
원인: (Cause 항목 참조)
해당 오류메시지는 Error 가 아닌 Warning 입니다.
이는 shared pool 영역중 libraray cache 영역의 단편화가 심해 Free Memory를 찾는데 오랜 시간이 걸릴 때 
위와 같은 오류가 발생합니다.
새로운 트랜잭션이 발생시 SQL의 파싱결과 등이 저장되는 library cache영역에 단편화로 인한 메시지로 
10g R2의 메모리 관리 메커니즘에 따른 메시지로 보시면됩니다.
 
 
=======================================================================
Applies to:
Oracle Server - Enterprise Edition - Version: 
This problem can occur on any platform.
Symptoms
The following messages are reported in alert.log after 10g Release 2 is installed.
       Memory Notification: Library Cache Object loaded into SGA
       Heap size 2294K exceeds notification threshold (2048K)
Changes
Installed / Upgraded to 10g Release 2
Cause
These are warning messages that should not cause the program
responsible for these errors to fail.  
They appear as a result of new event messaging mechanism
and memory manager in 10g Release 2. The meaning is that the
process is just spending a lot of time in finding free memory
extents during an allocate as the memory may be heavily fragmented.
Real memory messages should be ORA-403x when  a real memory allocation problem 
occurs.
Solution
In 10g we have a new undocumented parameter that sets the KGL heap size
warning threshold.   This parameter was not present in 10gR1.
Warnings are written if heap size exceeds this threshold.
   
Set  _kgl_large_heap_warning_threshold  to a reasonable high value or zero
to prevent these warning messages. Value needs to be set in bytes. 
If you want to set this to 8192 (8192 * 1024) and are using an spfile: 
(logged in as "/ as sysdba")
SQL> alter system set "_kgl_large_heap_warning_threshold"=8388608 scope=spfile ; 
SQL> shutdown immediate SQL> startup 
SQL> show parameter _kgl_large_heap_warning_threshold
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
_kgl_large_heap_warning_threshold integer 8388608 
If using an "old-style" init parameter, 
Edit the init parameter file and add 
_kgl_large_heap_warning_threshold=8388608
In 10.2.0.2,  the threshold is increased to 50MB after regression tests, so this should be a reasonable and recommended value.
References

http://surachartopun.com/2008/07/memory-notification-library-cache.html

10.2.0.1 Annoying message "Memory Notification: Library Cache Object loaded into SGA"
Posted: March 5, 2006 by Frits Hoogland in Oracle EE, Oracle XE 
9In the 10.2.0.1 version of the oracle database a new heap checking mechanism, together with a new messaging system is introduced. 
This new mechanism reports memory allocations above a threshold in the alert.log, together with a tracefile in the udump directory.

In certain situations it can be handy to know if large allocations are being done in the sga heap (shared pool), 
but only to troubleshoot memory allocation problems (which eventually will appear as the infamous ORA-4031). 
For normal day to day work, I doubt the effectivity of these messages.

The default limit is set at 2048K. It appears normal usage can result in messages from the memory manager. 
During my last installation (with all options set default, all options installed), 
this resulted in 125 messages from the memory manager.

Oracle gives a solution on metalink in note 330239.1:
Set _kgl_large_heap_warning_threshold to a “reasonable high” value. This parameter is set in bytes, 
and oracle recommends the value 8388608 (8192K).

Wednesday, July 30, 2008
Memory Notification: Library Cache Object loaded into SGA
 
Today, I checked alert.log file. I found:

Memory Notification: Library Cache Object loaded into SGA
Heap size 9426K exceeds notification threshold (8192K)

When I found out on metalink:
These messages are report in alert.log after 10g Release 2 is installed.

Cause:

These are warning messages that should not cause the program responsible for these errors to fail. They appear as a result of new event messaging mechanism and memory manager in 10g Release 2.

The meaning is that the process is just spending a lot of time in finding free memory extents during an allocate as the memory may be heavily fragmented. Fragmentation in memory is impossible to eliminate completely, however, continued messages of large allocations in memory indicate there are tuning opportunities on the application.

The messages do not imply that an ORA-4031 is about to happen.

Solution:

In 10g we have a new undocumented parameter that sets the KGL heap size warning threshold. This parameter was not present in 10gR1. Warnings are written if heap size exceeds this threshold.

Set _kgl_large_heap_warning_threshold to a reasonable high value or zero to prevent these warning messages. Value needs to be set in bytes.

If you want to set this to 8192 (8192 * 1024) and are using an spfile:

(logged in as "/ as sysdba")

SQL> alter system set "_kgl_large_heap_warning_threshold"=10485760 scope=spfile ;

SQL> shutdown immediate

SQL> startup

SQL> show parameter _kgl_large_heap_warning_threshold

NAME TYPE VALUE

--------------------------------- ------- ------------------------------
_kgl_large_heap_warning_threshold integer 10485760

If using an "old-style" init parameter,

Edit the init parameter file and add

_kgl_large_heap_warning_threshold=10485760

1. Linux 커널 매개변수의 설정

2. Shell Limit 설정

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의 기능에 대해서 설명 하도록 하겠습니다.

Maxgauge와 같은 툴이 Oracle Hang 상황에서도 데이터를 수집할 수 있는 것은 DMA(Direct Memory Access)를 사용하기 때문입니다. 이 방법은 Oracle의 COE(Center Of Expertise) 팀이 극한의 상황, 즉 SQL로 필요한 정보를 수집하지 못할 때 사용하던 방법입니다. 이것이 Maxgauge와 같은 툴 덕분에 보편화된 것입니다.

만일 Maxgauge와 같은 툴이 없다면 어떻게 해야 할까요? Oracle이 제공하는 ASHDUMP 기능을 Preliminary Connection과 함께 사용할 수 있습니다.

• Preliminary Connection이란 SQL*Net 방식이 아닌 Direct Memory Access 방식으로 Connection을 맺는 것을 말합니다.
• ASHDUMP는 액티브 세션 목록의 메모리 버전인 ASH(Active Session History)를 텍스트 파일에 기록하는 기능입니다.

아래에 간단한 예제가 있습니다.

우선 Preliminary Connection을 맺습니다.

더 자세한 정보들은 아래 문서를 참조하세요.

• Preliminary Connection과 Direct Access
• ML 노트: 243132.1

출처 : http://ukja.tistory.com/310

모니터링스크립트.txt

오라클모니터링.doc

불량SQL을 찾을때와 락문제를 해결할때 도움이 될 것입니다.

유용하게 사용되길 바라며...
 

SELECT * FROM dba_External_Locations
SELECT * FROM nls_database_parameters

######  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;