PERFORMANCE TUNING에는 3가지면에서의 APPROACH가 가능하다.
———————————————————–
. SYSTEM LEVEL : HARDWARE AND SOFTWARE
. DATABASE CONFIGURATION LEVEL : INIT.ORA AND STRUCTURE
. APPLICATION LEVEL : APPLICATION SPECIFIC DEVELOPMENT
다음에서는 위의 3가지 접근방법을 혼합하여 PERFORMANCE TUNING에 관하여 살펴본다.
INIT.ORA
——–
INTERNAL PARAMETER중에서 ORACLE PERFORMANCE에 중요한 영향을 미치는 3가지의 PARAMETER를 살펴본다.
. DB_BLOCK_BUFFERS
. SHARED_POOL_SIZE
. SORT_AREA_SIZE
< DB_BLOCK_BUFFERS >
DB_BLOCK_BUFFERS는 DATA CACHING을 위해 SGA로 할당되는 MEMORY를 지정한다.
이 숫자가 증가 될수록 PERFORMANCE는 향상된다. SQL 문장이 실행될때, 만약 CACHE 내에 RECORD가 없다면 실DATA를 읽어들여 CACHE에 올려놓는다.
CACHE내에 올려진 DATA들은 LRU 알고리즘에 따라 삭제될때까지 SGA내에 위치하게 된다. 필요한 자료를 DISK로부터 읽어오지 않고 MEMORY내에서 찾아오는 비율이 높을수록 PERFORMANCE가 높다. DB_BLOCK_BUFFERS는 실제의 할당되는 MEMORY를 지정하는 것이 아니라, BLOCK수를 지정한다. 실제로 지정되는 MEMORY의 양은 DB_BLOCK_BUFFERS * DB_BLOCK_SIZE의 값이된다. DB_BLOCK_BUFFERS의 DEFAULT 값은 HARDWARE에 따라 다르게 지정된다.
일반적으로 실제 MAIN MEMORY의 25%이하로 잡도록 한다.
< SHARED_POOL_SIZE>(Data Dictionary Cache, Shared Sql Area)
SHARED_POOL_SIZE는 SGA내에 DATA DICTIONARY CACHING과 STORED SQL문장을 위한 MEMORY의 할당량을 지정한다. DATA DICTIONARY CACHE는 DATA DICTIONARY 요소의 BUFFERING과 관련되어 우 중요하다.
하나의 SQL문장이 실행될때 ORACLE이 DATA DICTIONARY자료를 여러번 참조하기 때문에 DATABASE와 APPLICATION SCHEMA와 STRUCTURE가 MEMORY에 많이 올라와 있을수록, DISK로 부터 읽어들여오는 자료가 적어지게된다.
DATA DICTIONARY도 DB_BLOCK_BUFFERS의 경우와 마찬가지의 방법으로 CACHING 된다. 가장 이상적인 것은 모든 DATA DICTIONARY정보가 MEMORY에 올라와 있는 것이다. SHAED SQL AREA는 ORACLE INTERNAL이 실행하는 것이나 USER의 특별한 실행 문장을 갖고있다. SQL문장이 실행될때, SHARED SQL STATEMENT AREA에 같은 문장이 있는지를 확인하여 보아 있으면 그 문장이 실행된다. 이것은 PARSING TIME을 줄여 전체적인 PERFORMANCE를 향상시킨다. 이때 문장이 같다는 것은 문자열과 SPACE까지도 같아야 하는것을 의미한다. 만약, 문장이 다르다면 새로운 문장이 PARSING되어 실행되고 SHARED SQL AREA에 다시 저장된다.
<예>
SQL> select name,customer
2 from customer_information;
SQL> select name,customer
2 FROM customer_information;
위의 두 문장은 결과를 RETURN하지만, ‘FROM’절의 대소문자 때문에 ORACLE 에서는 다른 문장으로 인식하여 SHARED SQL AREA내의 있는 CURSOR를 재사용하지 않게된다. 이와같이 SHARED SQL AREA내의 자료에 대한 재 이용율을 높이려면, STORED PROCEDURE를 가능한 많이 사용하는 것이 좋다. SHARED_POOL_SIZE PATAMETER의 값은 BYTE 단위로 지정된다. DEFAULT값은 SYSTEM에 따라 다양하다.
< SORT_AREA_SIZE >
SORT_AREA_SIZE는 USER당 SORTING DATA에 필요한 MEMORY의 양을 지정한다. 이것은 SGA 외부에 존재하며, 필요시에만 사용된다. SORT_AREA_SIZE PARAMETER의 값은 BYTE단위로 지정하며, USER당의 PROCESS에 해당하는 값이다.
< DATABASE STRUCTURE ANALYSIS >
ORACLE KERNAL의 기능과 저장 능력이 증가됨에 따라 각기 다른 TABLESPACE로 ORACLE STRUCTURE를 나누고, 다중 DISK와 CONTROLLER를 통하여 성능을 향상시키고, TABLESPACE의 FRAGMENTATION을 감소하는것이 중요하게 대두되고 있다.
ORACLE을 INSTALL하면 DEFAULT로 ROLLBACK SEGMENT
TABLESPACE(ROLLBACK_DATA), TEMPORARY TABLESPACE(TEMPORARY_DATA),USER TABLESPACE(USER_DATA)가 생성된다. 가능하면 각기 다른 DISK와 CONTROLLER로 분산 시켜놓는 것이 좋다. 여기에 더하여 USER가 생성하는 APPLICATION이나 OBJECT들도 분산하여 TABLESPACE간의 DISK I/O를 분산시키는 것이 좋다.
< CLIENT-SERVER ANALYSIS >
. Database Communication Protocol
현재 ORACLE과 접속하기 위해서는 ODBC를 통하거나 ORACLE의 SQL*NET PRODUCT를 통하여 가능하다. PERFORMANCE 뿐아니라 ERROR TRAPPING, MESSAGE HANDLING을 위해서 ORACLE의 SQL*NET을 쓰는것을 권장한다.
. Client Versus Server Processing
CLIENT-SERVER환경을 생각할때 PC나 MACINTOSH WORKSTATION을 SERVER에 CONNECT 하는 것 뿐아니라 UNIX SYSTEM을 SQL*NET을 통하여 다른 UNIX와 CONNECT 하는 것까지도 포함하여 생각하여야 한다. EXECUTION에 대하여 CLINET 대 SERVER PERFORMANCE의 차이점을 TEST하려면, QUERY문을 CLIENT SQL*PLUS에서 (SET TIMING ON 상태에서) 실행하여 보고, 그 문장을 SERVER에 전송하여 SERVER SQL *PLUS에서 (SET TIMING ON 상태에서) 실행하여 본다. SERVER SIZE에서 수행하는 것의 주요한 잇점은 실행시간이 짧아지는 것이고, 결국 그것은 SYSTEM의 PERFORMANCE를 향상시키는 결과를 갖게된다.
. Client System Configuration
CLIENT 에서 DML 속도는 매우 중요하다. 더 빠른 PROCESSOR가 부가적인 차이를 가져오게된다. 그러나, 큰 차이점은 RAM의 증가에서 온다.
. Location Of Products And Source Code
대부분의 CLIENT-SERVER 구성은 NOVELL과 같은 NETWORK O/S를 FILE SERVER로 이용한다. 이 구성은 PRODUCT와 SOURCE CODE를 NETWORK DRIVE위에 놓고, CLIENT 에서는 참조만 하도록 한다. 이러한 구성은 제품의 UPGRADE시간의 절약이나 VERSION 관리에 용이하다. 그러나 SOURCE CODE를 포함하여 제품을 PC CLIENT에 INSTALL하는 것이 속 는 더 빠르다. NETWORK I/O가 감소되기 때문이다.
. Windows Swap File
WINDOWS SWAP FILE은 다른 것에의해 참조되는 FILE들이므로, CLIENT WORKSTATION 에서 가장 빠른 DRIVE에 위치하게 하는 것을 권장한다. 다른 방법은 별도의 MEMORY를 추가하여 (16M추 를 권장한다) 16M VIRTUAL DRIVE를 생성하여 WINDOWS SWAP FILE을 이 DRIVE에 생성하는 것도 좋다. 이 방법은 DISK I/O를 줄이고 MEMORY를 ACCESS하게 한다. 이러한 방법은 과거에 많이 사용하였으나, 몇몇 문제를 야기하기도 한다.
. Stored Procedures, Functions And Database Triggers vs. Pl/Sql In Source
STORED PROCEDURE나 DATABASE TRIGER는 이미 PARSING되어 있어 SQL문장이나 PL/SQL BLOCK을 직접 호출하는 것보다 실행에 필요한 OVERHEAD를 줄여준다.
. Eliminating Network I/O And Repetitiveness
CLIENT-SERVER 구성에서 PERFORMANCE를 향상시키는 또 다른 방법은 DATABASE를 호출하여 NETWORK OVERHEAD를 초래하는 반복적인 PROGRAM의 LOGIC을 재구성하는 방법이 있다. 이 방법은 각 RECORD를 조작하는 것이 아니라 한 문장에 의하여 DATA를 조작하는 방법을 간구하는 것이 . 이것은 DATABASE CALL의 숫자도 줄이는 결과를 가져온다.
다음의 방법에서 보면
Method A
1) Start;
2) Fetch Invoice Record;
3) Print Invoice Record;
4) Update Invoice Record To Printed;
5) Loop To Start;
Method B
1) Update all invoices not Printed to In Process;
2) Start;
3) Fetch Invoice record;
4) Print Invoice Record;
5) Loop To Start;
6) Update Invoice Record To Printed;
1000건의 해당 DATA가 있다면 , METHOD A에 의해서는 DATA가 1건씩 FETCH되어 1000번의 UPDATE가 발생하게 다. 반면 METHOD B의 경우, 한번의 UPDATE에 의해 1000건의 DATA가 UPDATE되게된다.
. RULE BASED vs. COST BASED OPTIMIZATION
ORACLE 7에 들어오면서, COSE BASED OPTIMIZER라는 새로운 ORACLE QUERY OPTIMIZER 개념이 도입되었다. ORACLE 7이전에는 실행 PLAN을 결정하는데 14가지 정도의 RULE에 따르는 RULE BASED OPTIMIZER가 사용되어져 왔다. 이 방법은 SQL OPTIMIZATION에 중요한 요소가 되는 DATA의 분산정도에 대해서는 전혀 인식하지 못하는 방법이었다. DATA의 분산정도는 QUERY 실행의 방법을 결정하는데 중요한 요소이 . 결국, ORACLE7 이전에 있어서의 PERFORMANCE TUNING은 SQL문장을 쓰는 개발자의 방법 설정에 의해 결정 되어져 왔다. COST BASED OPTIMIZER를 사용하는 ORACLE7 에서도 OPTIMIZER HINT를 추가함으로써 RULE BASED OPTIOMIZER에 의한 방법의 사용이 가능하다.
ORACLE RULE BASED OPTIMIZER는 INIT.ORA FILE의 OPTIMIZER_MODE PARAMETER를 ‘RULE’로 SETTING 함으로써도 사용가능하다. 이 PARAMERER의 DEFAULT 값은 ‘CHOOSE’이다. COST BASED OPTIMIZER는 DATA DICTIONARY에 저장되어 있는 DATA 분산 현황을 사용한다. 현황은 다음의 TABLE에서 확인할 수 있다.
USER_TABLES ALL_TABLES DBA_TABLES
USER_TAB_COLUMNS ALL_TAB_COLUMNS DBA_TAB_COLUMNS
USER_INDEXES ALL_INDEXES DBA_INDEXES
USER_CLUSTERS DBA_CLUSTERS
현황 정보는 ‘ANALYZE’ COMMAND에 의하여 생성된다. 이 COMMAND는 각각의 TABLE 에 대하여 실행 되어야 한다. ANALYZE COMMAND에는 DATA DICTIONARY저장되는 현황의 종류를 결정 할 수 있는 몇개의 OPTION이 있다.
< ANALYZE COMMAND의 예 : OPTION포함 >
SQL*PLUS에서 실행한다.
1. TABLE 대한 ESTIMATED STATISTICS의 생성
: 1064 개의 RECORD를 RANDOM하게 SAMPLING한다.
SQL> ANALYZE TABLE TABLE_NAME ESTIMATE STATISTICS;
: DATA의 SAMPLING을 위한 2가지의 OPTION이 있다.
SQL> ANALYZE TABLE TABLE_NAME ESTIMATE STATISTICS SAMPLE 5000 ROWS;
SQL> ANALYZE TABLE TABLE_NAME ESTIMATE STATISTICS SAMPLE 25 PERCENT;
2. TABLE에 대한 COMPLETE STATISTICS의 생성
: TABLE의 모든 ROW에 대한 TABLE과 INDEX의 자료현황을 생성한다.
SQL> ANALYZE TABLE TABLE_NAME COMPUTE STATISTICS;
3. TABLE에 대한 STATISTICS의 DELETE
: TABLE과 INDEX의 자료현황을 삭제한다.
SQL> ANALYZE TABLE TABLE_NAME DELETE STATISTICS;
4. CHAINED_ROWS TABLE에 CHAINED ROW를 등록
: UTLCHAIN.SQL SCRIPT(CHAINED_ROWS TABLE을 생성한다)를 아래의 COMMAND를 실행 하기전에 수행하여야 한다. CHAINED_ROWS TABLE에는 CHAINED ROW의 ROWID를 포함한 각각의 ROW들이 INSERT 된다. 이 ROWID를 이용하여 원래의 TABLE로 부터 CHAINED된 ROW들을 DELETE한 후, CHAINED_ROWS TABLE의 ROW를 이용하여 새로운 ROW를 생성하여 CHAIN을 없앨 수 있다.
SQL> ANALYZE TABLE TABLE_NAME LIST CHAINED ROWS;
5. TABLE과 INDEX에 CORRUPTION이 있는지를 VALIDATE
: 만약 CORRUPTION이 존재한다면, 다음 COMMAND를 실행 하였을때 ‘DROP AND RECREATE’ MESSAGE가 표시된다.
SQL> ANALYZE TABLE TABLE_NAME VALID STRUCTURE CASCADE;
ANALYZE COMMAND가 실행되면, SHARED SQL AREA에 존재하면서 참조되던 OBJECT 들은 다음에 새로운 현황으로 참조되기 위해 SHARED SQL AREA에서 삭제된다.
ANALYZE COMMAND는 위에서와 같이 한 TABLE씩에 대하여 실행될 수도 있으나, ORACLE에서 공하는 PACKAGE PROCEDURE인 DBMS_UTILITY.ANALYZE_SCHEMA를 호출하여 한 USER의 전체 OBJECT에 대해서 전체적으로 실행 할 수도 있다.
SQL> BEGIN
2 DBMS_UTILITY.ANALYZE_SCHEMA(‘USER_NAME’,’COMPUTE’);
3 END;
4 /
ANALYZE되니 않은 DATA DICTIONAY TABLE에 대해서는 RULE BASED OPTIMIZER가 사용된다. COST BASED OPTIMIZER를 사용하게 하기 위해서는, 위의 PACKAGED PROCEDURE를 SYS의 SCHEMA에 대해 실행 되어져야 한다.
. THE ORACLE MONITOR AND ORACLE V$TABLES
ORACLE MONITOR는 SQLDBA UTILITY상 서 가능하다. MONITOR UTILITY는 ORACLE의 V$VIEW이나 여러개의 V$VIEW를 JOIN하여 정보를 가져온다.
. INDEXING STRATEGIES AND REVIEW
TABLE에 대한 INDEXING은 SYSTEM PERFORMANCE에 중요한 KEY가 되며, INDEXING의 생성에는 주의를 요한다. 한 TABLE에 대하여 INDEX의 갯수를 3개까지로 제한하는 것이 좋으며, 가능한한 5개 이상은 만들지 않는 것이 좋다.
3가지 TYPE의 TABLE에 대하여 각기 일반적인 INDEX기법에 대하여 다음에서 살펴본다.
1. 만약 TABLE이 QUERY만을 위한 것이라면, 위에서의 경우보다 많은 수의 INDEX 를 생성하여도 좋다.
2. 만약 TABLE에 대한 QUERY는 적고, INSERT나 UPDATE가 주로 이루어 진다면 INDEX를 적게 가져가
도록 권장한다. (DATA의 운용은 TABLE의 DATA뿐만아니라 TABLE에 대한 INDEX까지 UPDATE가 이루어
져야 하기때문이다)
3. 만약 TABLE이 INSERT,UPDATE,QUERY가 모두 필요하다면, INDEXING에 매우 주의를 요하나, INDEX은
꼭 필요하다.
. EXPLAIN PLAN
EXPLAIN PLAN COMMAND는 ORACLE OPTIMIZER가 SQL문의 실행에 사용할 EXECUTION PLAN을 개발자에게 제공해준다. 이 COMMAND는 SQL문장이 실제로 실행 되지는 않고, 실행 PLAN이나 OUTLINE만이 ORACLE TABLE에 저장되므로 SQL문장의 PERFORMANCE를 높이는데 매우 도움이된다. EXPLAIN PLAN COMMAND를 실행하기 전에 COMMAND를 수행할 ORACLE USER에 의해 UTLPLAN.SQL이 먼저 수행되어져야 한다. 이 SCRIPT는 PLAN_TABLE 생성한다.
SQL> EXPLAIN PLAN
2 SET STATEMENT_ID = ‘QUERY1’ FOR
3 SELECT CUST_ID, NAME FROM CUST_INFO WHERE CUST_ID = ‘199’;
이 TABLE의 자료를 조회하기 위해서는 SQL문을 사용하여야 한다.
SQL> SELECT Operation, Options, Object_Name, Id, Parent_Id, Position
2 FROM PLAN_TABLE
3 WHERE STATEMENT_ID = ‘QUERY1’
4 ORDER BY Id;
< OUTPUT >
OPERATION OPT OBJECT_NAME ID P_ID POS
————– —- ————– — —- —
SELECT STATEMENT 0
TABLE ACCESS FULL CUST_INFO 1 0
SQL> SELECT Lpad(‘ ‘,2*(Level-1))||
2 Operation||’ ‘||
3 Options||’ ‘||
4 Object_Name||’ ‘||
5 Decode(Id,0,’Cost = ‘||Position) ‘Query Plan’
6 FROM PLAN_TABLE
7 START WITH ID = 0
8 AND STATEMENT_ID = ‘QUERY1’
9 CONNECT BY PRIOR_ID = PARENT_ID
10 AND STATEMENT_ID = ‘QUERY1’;
< OUTPUT >
QUERY PLAN
——————————————
SELECT STATEMENT Cost =
TABLE ACCESS FULL CUST_INFO