SQL/DBMS 튜닝과 관련되 오라클 DB 파라메터

     [ Query Optimizing ]

 

      - _optim_peek_user_binds=FALSE

        : _optim_peek_user_binds=TRUE일 경우 Bind Value가 있는 SQL의 해석과

        FALSE일 경우에 해석되는 것에 많은 차이가 있다. TRUE일 경우 Bind Value에 

        인덱스가 있고, 해당 SQL이 Bind Value를 이용해서 해석이 되어야 하는 경우

        Query Optimizer가 Bind Value의 Column Histogram을 참조하여 SQL문을 해석

        하고 실행하게 됨. 이때, 평상시 조회가 되지 않는 조건으로 Binding이 될경우

        평상시의 PLAN과 상이하게 해석될 수 있으며, 해당 SQL의 Cursor가 Aging Out

        되지 않는 동안에는 PLAN이 유지되게 된다. Aging Out 된 이후에는 또다시 

        Column Histogram을 참조하여 PLAN을 세우게 되어 이전과는 상이한 실행계획

        이 수립되어 SQL이 실행될 수 있다. False일 경우에는 일반적인 Query Optimizing

        을 하게 된다. (필수적용사항)

      - _b_tree_bitmap_plans=FALSE

        : Query Optimizer가 SQL 해석할 때 Where절에 여러 조건이 있고, 해당 조건

        컬럼들에 Index가 각각 생성되어 있을 경우 B*tree Index를 Bitmap으로 

        conversion하여 PLAN을 수립하여 실행함. 이럴경우 TYPE이나 Code성 컬럼의

        경우 B*tree Index range scan으로 해석되는 경우보다 성능이 나을수 있으나

        일반적으로 성능이 저하되는 경우가 더 많음.(필수적용사항)

      - optimizer_mode='FIRST_ROWS_100'

        : 10g R1에서는 optimizer_mode를 first_rows로 설정과 관련된 Bug이 있었음.

        그리고, 온라인(OLTP) 환경에서는 First_rows 설정이 아닌 First_rows_100

        정도 세팅하는것이 유리함.

      - _optimizer_sortmerge_join_enabled=FALSE

        : Merge Join Cartesian(Merge Join)을 없애기 위한 파라미터 세팅으로, 

         Cartesian Product는 Join Ker가 없이 Join이 발생할 경우 발생되는게 

         정상이나 비정상적으로 Merge Join Cartesian이 발생되어 SQL들의 실행계획

         이 비정상적으로 수립 및 실행되어 성능저하가 많이 발생하여 적용을 함.

         주의할 점은 cartesian Product가 발생할 경우 Nested Loop로 PLAN에 해석되

         므로, PLAN 해석할 때에 유의하여야 함.(필수적용사항)

 

    [ Scalar Subquery ]

 

      - _query_execution_cache_max_size=4194304 

        : SQL 수행 시 select list clause에서 function call 과다에 의한 성능부하가 심할 

        경우가 많이 있다. 그럴경우에는 function call 부분을 스칼라서브쿼리로 변경하여 

        function call에 의한 부하를 최소화 할 필요가 있다. 이럴경우 스칼라 서브쿼리를 

        위한 buffer cache 영역을 늘려주어야 하며, _query_execution_cache_max_size 로 

        메모리 사이즈를 늘려주어야 한다. 일반적으로 OLTP 환경에서는 4M 정도가 적당하며

        Function Call을 스칼라 서브쿼리로 변경할 경우 스칼라 서브쿼리 buffer cache

        변경외에 또다른 주의할 사항이 있는데, Function Call에 의해 데이터를 리턴받을 때 

        옵티마이저는 리턴받는 데이터를 위하여 varchar2(4000) 만큼 Memory 영역을 점유하게 되므로, 

        substr()로 적정하게 사이즈를 두어 비효율적으로 옵티마이저가 메모리를 점유하지 

        않게 하여야 한다. (필수적용사항)

 

    [ Parallel ]

      - _PX_use_large_pool=TRUE

        : Use Large Pool as source of PX buffers

      - parallel_automatic_tuning=TRUE

      - parallel_execution_message_size=64k (필수적용사항)

      - parallel_instance_group='A'

      - parallel_max_servers=80

 

    [ Index ]

      - _index_join_enabled=FALSE

        : Index Join Disable

      - _fast_full_scan_enabled=TRUE (default가 TRUE)

      - _optimizer_skip_scan_enabled=FALSE

        : Index Skip Scan이 되지 않게 하기위한 파라미터임.

         -> Index Skip Scan이 필요한 경우에는 10g부터 지원되는 opt_param() 힌트를 

         사용하여 SQL Level에서 Index Skip Scan이 되게끔 바꿔주면 됨.

         ( /*+ opt_param('_OPTIMIZER_SKIP_SCAN_ENABLED','FALSE') */ )

         -> Index Skip Scan이 False일 경우 Skip Scan이 되지 않지만, Index Column인

         경우에 Index Filter가 되기때문에 성능상 많은 영향은 없음)

      - optimizer_index_caching=90 (default 0)

        : 이 파라메터는 0~99 사이의 값을 설정 할 수 있으며, 0일 경우에는 Index를 이용해서 

        Access되는 block 들이 SGA의 Buffer Cache 영역에서 찾을 수 있는 비율이 0%란 

        의미이다.

        즉 모든 index access는 DISK I/O 를 발생하여 Physical Reads를 수행 한 후에 Buffer

        cache로부터 Logical reads를 수행한다는 의미가 된다. 이 파라메터는 CBO에서 Index 

        block을 access할때 소요되는 Cost를 계산하기 위한 비율로서 사용되어진다.

      - optimizer_index_cost_adj=10

        : 이 파라메터는 1~10000사이의 값을 설정할수 있으며 Index access와 Full Table Scan

        에서 사용되는 Physical I/O 의 Cost의 상대적인 비율을 설정하는 파라메터이다.

        100인경우에는 두개의 access 모두 동일한 비율로 Cost를 계산하게되며 50으로 설정하

        여  Index access를 하는 경우 기존의 cost의 1/2로 cost를 계산하게 되어, Index 

        access 방식으로 execution plan이 수립되어질 확률이 높아지게 된다. Optimizer_mode

        를 first_rows로 설정하는 경우에는 내부적으로 optimizer_index_cost_adj 는 10으로 

        계산되어진다. 따라서 Index access를 이용하여 execution plan을 수립할 확률이 높아

        지게 되면 Join인 경우에는 Nested Loops Join으로 execution plan이 수립될 가능성이

        높아지게 된다.

 

    [ I/O Count ]

      - _hash_multiblock_io_count=32

      - _sort_multiblock_read_count=32

      - db_file_multiblock_read_count=32

 

    [ DB Link ]

      - open_links=50

        :  한 세션에서 Remote Database로 동시에 연결할 수 있는 최대 수

          -> 8.1.7 이전 버전에서 open_links의 설정값을 255를 초과하여 설정시 

            ORA-600 [k2rcbk: null gti] Internal Error 발생함. open_links 설정시

            255를 초과하면 안됨. (Bug no. 1035278)

          -> ora-600 [npiane0] Internal Error 발생 (Bug 1092735)

            open_links 값을 초과하여 connection 시도시 발생

      - open_links_per_instance=1000

 

    [ Cursor ]

      - open_cursors=300

      - cursor_space_for_time=TRUE

        : 세션에서 사용된 Cursor를 세션이 닫힐 때까지 SGA에 남겨놓는다.

      - session_cached_cursors=50

        : 열려있는 세션이 가질 수 있는 최대 Cursors 개수

        SESSION_CACHED_CURSORS 파라메터는 동일한 SQL을 반복수행하는 경우에 유리하며,

        보통 softer parse 라고 한다.

        단, 리터럴 SQL이 많은 경우에는 사용을 피해야 된다.

        모듈별로 특정 SQL 들을 반복 수행하는 세션들에 설정시 SOFT 파싱부하를 

        감소시켜준다. 시스템이 내부 수행하는 ReCursive SQL 도 포함되므로 최소 30 이하로 

        설정하는것은 효과가 없으며 보통 50 정도를 권장한다.

     - cursor_sharing=force 쓸 경우 쿼리에 rownum = 1인 쿼리가 있다면 이 부분이 rownum := :b1이런식으로 

        바인드 변수 처리가 되어서 부분범위 처리가 되질 못한다.

        이럴 경우를 대비해서 rownum <= 1 이런식으로 SQL 생성 습관을 들이자

 

    [ SGA ]

 

      - db_cache_size=4G

      - db_keep_cache_size=2G

      - java_pool_size=16777216

      - large_pool_size=52428800

      - log_buffer=14473216

      - pga_aggregate_target=4G

      - shared_pool_size=1200M

      - shared_pool_reserved_size=100M

      - sga_max_size=7400M

      - sga_target=7400M