DeployPHP 시리즈, 제 1 부: PHP와 오라클의 최적화 | 프로그래밍? 2005/09/30 11:42
http://blog.naver.com/bunny121/140017860301
DeployPHP 시리즈, 제 1 부: PHP와 오라클의 최적화
by John Lim
DeployPHP 시리즈의 제 1 부에서는, 강력하고 확장성 있는 오라클 기반 PHP 애플리케이션을 개발하기 위해서 필요한 사항들을 점검해 봅니다.
아티클 관련 다운로드:
Oracle Database 10g
Oracle Instant Client
Oracle JDeveloper PHP Extension
야후, 루프트한자, 디즈니 온라인 등, 세계 유수 기업들이 오픈 소스 언어인 PHP를 이용하여 웹 사이트를 구축하고 있습니다. PHP는 매우 단순한 구조로 이루어진 언어라는 점을 감안한다면, 이러한 현상은 더욱 주목할 만 합니다.
웹 서버가 HTTP 요청을 처리하는 거대한 공장이라고 가정해 봅니다. 그리고 PHP 인터프리터는 요청을 처리하는 작업 인력이라고 상상해 봅시다. 각각의 작업자들은 자신에게 할당된 위치에서 홀로 작업하며 다른 작업자들과 이야기하거나 협력하는 것이 허락되지 않습니다. 이 작업자들은 모두 자신이 이전에 한 일을 전혀 기억하지 못합니다. HTTP 요청을 처리하려면, 먼저 데이타 저장소에서 세션 정보를 가져 와야 합니다. 그리고 작업이 끝나면 세션 정보를 다시 저장소에 저장합니다.
이처럼 기억력도 없이 격리된 상태에서 일하는 작업자들이 오히려 더 높은 확장성을 보인다는 것은 무척 역설적인 일입니다. PHP 프로세스는 다른 프로세스로부터 완전하게 독립적이며 상태 정보를 전혀 관리하지 않습니다. 물론 데이타를 동기화하고 세션 정보를 저장하는 작업이 필요하지만, 이것은 오라클 데이타베이스와 같은 별도의 외부 모듈이 전담하도록 하면 그만입니다. Rasmus Lerdorf는 OTN 아티클 " PHP 하십니까?"에서 이렇게 말합니다. “PHP로 구현된 환경을 확장하는 것은 매우 쉽습니다. 공유 데이타 저장소가 필요한 경우라면, 데이타베이스 복제 환경을 이용하여 그 규모를 필요한 만큼 확장할 수 있습니다."
PHP 개발자 여러분들은 PHP와 인터페이스하는 컴포넌트 중 가장 중요한 것이 바로 데이타베이스라는 사실을 잘 알고 계실 것입니다. DeployPHP 시리즈의 제 1 부에서는, 오라클 환경에서 강력하고 확장성 있는 PHP 애플리케이션을 개발하고 구축하는 방법을 설명하기로 합니다. 여기서는 독자가 (오라클 환경을 제외한) PHP와 SQL에 대해서는 어느 정도의 지식을 가지고 있는 것으로 가정합니다:
적절한 extension API를 선택하자I.
PreFetch와 Hint를 활용하자.
Bind 변수를 활용하자.
Class Library를 사용하자.
적절한 Optimizer Hint와 Index를 선택하자.
데이타와 비즈니스 로직을 효과적으로 관리하자.
Persistent Connection을 관리하자.
구축 작업이 완료된 후 PHP 코드를 최적화하는 방법에 대해서는 제 2 부에서 설명 드리게 될 것입니다.
1. 적절한 Extension API를 선택하자
PHP는 오라클 연결을 위해 다양한 API(application programming interface)를 제공합니다. 먼저 Oracle extension, OCI8 extension이 있습니다. 또 PHP 5.1에서는 PDO extension이 제공됩니다. 아래 설명을 통해 확인하실 수 있듯, PHP는 기능적인 면에서는 손색이 없지만, 매우 엉성한 명명 규칙(naming convention)을 사용하고 있습니다. 어떤 extension을 선택할 것인지 결정할 때 참고해야 할 사항이 다음과 같습니다:
“Oracle” extension은 원래 Oracle7 환경을 위해 설계된 것이며, 가능한 한 사용을 자제하는 것이 좋습니다. 더 이상 사용되지 않고 있을 뿐 아니라 LOB(Large Object) 지원과 같은 중요한 기능이 지원되지 않습니다.
PHP Data Objects (PDO)는 PHP 5.1에서 새롭게 제공되는 포터블 데이타베이스 API입니다. 하지만 아직 성숙된 기술은 아닙니다.
Windows에서는 ODBC(Open Database Connectivity) extension을 활용할 수도 있습니다. ODBC는 커넥션 풀링(connection pooling) 기능을 내장하고 있다는 점이 장점으로 꼽힙니다. 하지만 API가 제한되어 있어, LOB를 처리하거나 저장 프로시저(stored procedure)에서 IN OUT 매개변수를 사용하는 경우 곤란한 문제를 겪을 수 있습니다.
OCI8 extension은 기능적인 면에서 가장 뛰어나며 Oracle Call Interface (OCI)을 가장 근접하게 매핑하고 있습니다. OCI8에서 “8”이라는 숫자는 큰 의미가 없습니다. OCI8은 Oracle8/8i, Oracle9i, 그리고 Oracle 10g 환경에서 모두 사용 가능합니다. 최고의 성능을 필요로 하는 환경이라면 이 API를 추천합니다.
PHP 개발자 센터에 방문하시면 Linux, Windows, Mac OS X 환경에서 오라클과 PHP를 설치하는 자세한 방법을 확인하실 수 있습니다. 오라클과 PHP를 통합하기 위한 패키지 솔루션을 선호하신다면, 곧 공개될 Zend Core for Oracle 라이브러리를 눈 여겨 보시기 바랍니다.
2. PreFetch와 Hint를 활용하자
PHP 개발 과정에서는 오라클에서 데이타를 가져오는 작업이 빈번하게 사용됩니다. PHP 애플리케이션의 코딩을 본격적으로 시작하기 전에 알아 두면 좋을 몇 가지 중요한 테크닉을 소개합니다.
먼저, 이론적인 부분부터 말씀 드리겠습니다. 오라클 환경에서 SQL 구문을 실행하는 경우, 오라클의 파서(parser)는 SQL 구문을 점검하고 이전에 컴파일된 쿼리가 있는지를 먼저 확인합니다. 만일 매치되는 쿼리가 있다면 컴파일된 쿼리를 사용합니다 (이를 “soft-parse”라 합니다). 매치되는 쿼리가 없다면 옵티마이저를 통해 최적의 실행 계획(execution plan)을 결정한 뒤 사용자의 쿼리를 컴파일합니다 (이를 “hard-parse”라 합니다).
Pagination. 조회 대상 레코드를 페이지 단위로 나누고 현재 페이지에 해당되는 레코드만을 가져오는 것을 “pagination”이라 합니다. “nested” SELECT 구문과 ROWNUM 변수를 사용하면 이와 같은 작업이 가능합니다. 아래 코드는 페이지 단위로 레코드를 가져오는 방법을 예시하고 있습니다. $currentPage는0부터 시작된다고 가정합니다:
$firstRec = $currentPage * $recordsPerPage + 1; $lastRec = ($currentPage+1) * $recordsPerPage; $myquery = "SELECT * FROM customer WHERE type='HIGHVAL' ORDER BY name"; $sql = " SELECT * FROM ( SELECT a.*, RowNum as rNum FROM ( $myquery ) a WHERE RowNum <= $lastRec ) WHERE $firstRec <= rNum";
오라클로부터 전달되는 각 레코드에는 row number가 할당됩니다. 이 정보를 이용하여 조회 작업을 중단시킬 수 있습니다. 예를 들어, “$firstRec <= rNum”을 시작 조건으로 “RowNum<= $lastRec”을 종료 조건으로 사용할 수 있습니다.
레코드 카운트. 생각보다 구현이 어려운 작업 중 하나로 조회 대상 레코드의 수를 확인하는 작업이 있습니다. 다른 고성능 데이타베이스와 마찬가지로, 오라클은 결과 셋에 레코드의 수를 반환하지 않습니다. 따라서 많은 리소스를 사용하는 별도의 쿼리를 수행하지 않고는 쿼리를 통해 몇 개의 레코드가 반환되었는지 알 수가 없습니다.
아래 그림과 같이 스크롤 링크를 사용하여 레코드 셋을 표시해야 하는 경우를 생각해 봅시다:
가장 간단한 방법은 쿼리를 실행한 후 반환된 레코드의 수를 세는 것입니다. 더 나은 방법은 쿼리를 아래와 같이 변경하는 것입니다.
변경 전: SELECT id, msg_subject, msg_contents FROM largetable
변경 후: SELECT COUNT(*) FROM (SELECT id, msg_subject, msg_contents FROM largetable)
데이타가 아주 많거나 쿼리를 수정하기 곤란한 경우라면, 별도의 카운터 테이블을 만들고 INSERT/DELETE 트리거를 사용하여 카운터 테이블을 업데이트하도록 할 수도 있습니다. 그리고 필요할 때는 언제나 카운터 테이블을 조회하도록 할 수 있습니다.
힌트의 활용. 오라클의 쿼리 옵티마이저가 언제나 최적의 판단을 내리는 것은 아닙니다. 옵티마이저가 오판할 가능성을 최소화하기 위해 힌트(hint)를 활용할 수 있습니다. 그 예가 다음과 같습니다:
$sql = "SELECT * FROM ( SELECT /*+ FIRST_ROWS INDEX(customer ctype) */ * FROM customer WHERE type='High Value' ORDER BY name ) WHERE ROWNUM <= $lastRecordToDisplay";
옵티마이저 힌트는 SELECT 키워드 바로 뒤에 사용되며 “/*+” 특수 기호로 코멘트 처리 되어 있습니다.
위 쿼리에서는 두 가지 힌트가 사용되었습니다:
FIRST_ROWS 옵티마이저에게 모든 로우(row)를 가져오지 않을 것임을 알리고, 전체 레코드 중 극히 일부만을 가져오는 환경에 맞도록 실행 계획을 최적화시킵니다.
INDEX(tablename indexname) 옵티마이저에게 특정 인덱스를 사용하도록 지시합니다.
위 두 가지 힌트는 필자가 개인적으로 자주 사용하는 것들입니다. 옵티마이저 힌트에 대한 자세한 정보는 Oracle Database SQL Reference의 Chapter 2, “Comments” 섹션에서 확인하실 수 있습니다.
Prefetch. OCI8 함수의 prefectch 기능을 사용하여 오라클 클라이언트 버퍼에 저장되는 레코드의 수를 설정하는 방법으로 네트워크 트래픽을 대폭 절감할 수 있습니다. 디폴트 값인 1은 너무 작습니다. 이 설정을 사용하는 경우 개별 레코드 단위로 데이타를 가져오기 때문에 상당한 양의 네트워크 트래픽이 발생하게 됩니다. 이 값을 페이지 사이즈에 맞게 (예: 20~50) 설정하는 방법만으로도 쿼리 성능을 2배에서 4배 수준으로 개선할 수도 있습니다.
PHP 코드 예가 다음과 같습니다:
$conn = OCILogon($user,$pwd); $stmt = OCIParse($conn,$sql); OCISetPrefetch($stmt, 20); OCIExecute($stmt); While (OCIFetchInto($stmt,$arr)) { /* Process $arr */ }
3. Bind 변수를 활용하자
다음과 같은 SQL 구문에 대해 생각해 봅시다.
SELECT * FROM customer WHERE custid=1 SELECT * FROM customer WHERE custid=249 SELECT * FROM customer WHERE custid=6380
오라클은 기본적으로 각각의 구문 별로 다른 실행 계획을 사용합니다. 이 쿼리들이 모두 동일한 구문을 기반으로 하는 것임을 감안하면, 이러한 방법은 시간과 리소스 면에서 낭비일 수 있습니다. 그 대신, 쿼리에 바인드 변수(bind variable; “:var”)를 적용하고 1, 249, 6380의 값을 적용하는 방법을 사용할 수 있습니다. 이렇게 하면 모든 SQL 구문이 동일한 실행 계획을 공유하도록 할 수 있습니다:
변수에 바인드 된 값 SQL
1
249 SELECT * FROM customer WHERE custid=:var
6380
$value를 :var 에 바인딩 PHP 코드 예제가 다음과 같습니다:
OCIParse($conn, 'SELECT * FROM customer WHERE custid=:var'); OciBindByName($stmt, ":var", $value,32); # 32 is an arbitrary large size $value = 1; OCIExecute($stmt); DisplayResults($stmt); $value = 249; OCIExecute($stmt); DisplayResults($stmt); $value = 6380; OCIExecute($stmt); DisplayResults($stmt);
바인딩은 “SQL injection” 공격의 피해를 줄일 수 있다는 또 다른 장점이 있습니다 (공격자들은 SQL 구문을 마음대로 조작하기 어렵게 됩니다).
Cursor sharing. 바인딩을 사용하지 않는 SQL 구문이 이미 많이 사용되고 있다면, Oracle8 이후 버전에서 제공되는 CURSOR_SHARING 매개변수를 사용하여 컴파일 과정에 수반되는 오버헤드를 줄일 수 있습니다. 아래 SQL 구문을 실행하는 경우 오라클은 모든 쿼리를 바인드 변수를 사용하도록 재작성합니다:
ALTER SESSION SET CURSOR_SHARING='FORCE'
따라서 아래 구문은 파서(parser)에 전달되기 전에 바인딩이 가능한 구문으로 자동 변환됩니다:
변경 전: SELECT * FROM customer WHERE custid=6380
변경 후: SELECT * FROM customer WHERE custid=:SYS_B_0
CURSOR_SHARING을 이용하면 실행 계획의 공유 및 재활용 수준을 개선할 수 있습니다. 하지만 그 효과는 제한적입니다. 각 구문 별로 직접 바인드 변수를 적용하는 것이 더 효과적입니다. 또 SQL injection 공격의 방지 효과를 기대할 수 없다는 문제도 있습니다.
Oracle9i 이후 버전에서는 CURSOR_SHARING='SIMILAR'를 활용할 수 있습니다. 이 매개변수는 보다 지능적인 알고리즘을 사용하며, 바인드 변수의 적용여부를 결정하기 전에 테이블 통계정보를 참고합니다.
ALTER SESSION 명령은 현재 세션에만 영향을 미칩니다. 따라서 데이타베이스에 연결을 설정할 때마다 다시 호출되어야 합니다. 영구적으로 변경 사항을 적용하려면 DBA에게 요청하여 데이타베이스 매개변수 파일(init.ora 또는 spfile)을 변경해야 합니다.
다음 섹션에서는, 벤치마크 테스트를 통해 바인드 변수 적용에 따른 성능의 차이를 비교해 보도록 하겠습니다. 예제로 사용된 INSERT 구문에서 100% 이상의 성능 개선이 있었음을 확인하실 수 있을 것입니다.
4. Class Library를 사용하자
애플리케이션 코딩 작업을 본격적으로 시작하기 전에, OCI8 extension의 기능을 인캡슐레이트(encasulate)한 PHP 함수를 미리 생성해 두는 것도 좋은 방법입니다. 이 함수를 직접 만들 수도 있고, 오픈 소스 라이브러리를 활용할 수도 있습니다. 가장 널리 사용되는 라이브러리로 PEAR DB와 ADOdb가 있습니다.
아래 코드는 “a, b, c” 세 가지 필드로 구성된 테이블을 업데이트하는 예를 보여주고 있습니다.
$conn = ocilogon('scott','tiger'); $stmt = OCIParse("insert into abc (a,b,c) values (?,?,?)"); OCIBindByName($stmt, ":a", $a, 32); OCIBindByName($stmt, ":b", $b, 32); OCIBindByName($stmt, ":c", $c, 32); for ($i=0; $i<TIMES; $i++) { $a = $i; $b = "b".rand(); $c = "c".rand(); ociexecute($stmt,OCI_DEFAULT); } ocicommit($conn);
이 코드를 PEAR DB 버전으로 변경한 결과가 아래와 같습니다:
include('DB.php'); $DB = &DB::Connect('oci8://scott:tiger@/';); $DB->autoCommit(false); $stmt = $DB->prepare("insert into abc (a,b,c) values (?,?,?)"); for ($i=0; $i<TIMES; $i++) { $DB->execute($stmt,array($i,"b".rand(),"c".rand())); } $DB->commit();
다음은 ADOdb 버전입니다:
include('/path/to/adodb/adodb.inc.php'); $DB = NewADOConnection('oci8://scott:tiger@/';); $DB->BeginTrans(); $stmt = $DB->Prepare("insert into abc (a,b,c) values (:0,:1,:2)"); for ($i=0; $i<TIMES; $i++) { $DB->_Execute($stmt,array($i,"b".rand(),"c".rand())); } $DB->CommitTrans();
ADOdb 라이브러리에서는 OCIBindByName에 관련한 세부적인 내용이 모두 숨겨져 있음을 확인하실 수 있습니다. 또 라이브러리가 제공하는 디버깅 모드를 활용하여 로그와 SQL 출력 구문을 생성하고 문제의 원인을 쉽게 진단할 수 있습니다.
위의 코드를 1000회 반복한 벤치마크 결과가 아래와 같습니다. PHP 4.3.11, Oracle Database 9.2, Windows XP 환경에서 트랜잭션이 시작되는 시점에서 종료되는 시점까지의 시간을 측정하였습니다.
OCI8 (바인드 사용) 0.18초
ADOdb 0.27초
PEAR DB 0.35초
OCI8 (바인드 사용 안 함) 0.41초
위에서도 바인드 변수를 사용하지 않는 경우의 오버헤드가 매우 높게 나타나는 것을 확인할 수 있습니다. (단일 레코드에 대한 INSERT / UPDATE 구문이 실행되는 경우에 특히 이러한 현상이 뚜렷하게 나타납니다). 라이브러리를 사용하는 경우에도 어느 정도 오버헤드가 발생합니다. 따라서 라이브러리를 사용하기 전에 사용 편의성, 유지보수성의 문제와 성능적인 이익을 비교하여 판단할 필요가 있습니다.
그 밖에도 재미있는 사실이 한 가지 확인되었습니다. autocommit이 활성되된 상태에서 벤치마크를 수행하는 경우, INSERT 구문이 한 번 실행될 때마다 자동으로 커밋 작업이 수행되고 이로 인해 성능이 급격하게 느려집니다. 바인드 변수를 사용한 OCI8 환경의 경우 2.92 초가 걸렸습니다. 16배나 느린 결과입니다.
ADOdb의 경우 다음과 같은 부가적인 장점을 제공합니다:
자동으로 OCISetPreFetch를 설정해 줍니다.
가능한 경우 오라클 옵티마이저 힌트를 자동으로 삽입하여 코드 실행 속도를 개선합니다.
저장 프로시저의 IN OUT 매개변수와 LOB를 지원합니다.
자주 사용되는 쿼리의 레코드셋을 캐시에 저장합니다. 이 기능은 자주 변경되지 않는 값을 빈번하게 사용하는 경우 매우 유용합니다.
별도로 제공되는 세션 관리 유틸리티를 이용하여 세션 정보를 LOB에 저장할 수 있습니다.
SGA Trace보다 한층 뛰어난 튜닝 인프라스트럭처를 제공합니다. 모든 SQL을 캡처하여 HTTP 요청, 실행 비용, 실행 빈도 등을 기준으로 비교 분석할 수 있습니다. 이 기능을 활용하면 SQL 쿼리의 성능 문제를 보다 신속하게 확인할 수 있습니다.
(뒷부분에서 좀 더 자세하게 설명하겠습니다.)
디폴트로 설치되는 ADOdb 라이브러리는 PHP 코드로만 작성되어 있습니다. 성능 개선을 원하는 경우 별도 제공되는 C 기반 ADOdb extension을 설치할 수도 있습니다.
그 밖에도 Zend Accelerator, eAccelerator, APC와 같은 PHP accelerator cache를 사용하여 PHP 코드 라이브러리의 컴파일/코딩 시간을 단축할 수 있습니다.
BLOB 관리. 오라클의 LOB API는 대용량(4,000 바이트 이상)의 데이타를 저장하기 위한 목적으로 사용되지만 사용하기에 무겁다는 단점이 있습니다. ADOdb는 보다 간단한 UpdateBlob, UpdateClob 함수를 제공합니다:
$db->Execute("insert into aTable (id, name, ablob) values (999, 'Name', null)"); $db->UpdateBlob('aTable', 'ablob', $blobValue, 'id=999');
위 코드는 ATABLE 테이블에 새로운 레코드를 삽입한 후, UpdateBlob을 호출하여 id=999인 레코드의 ablob필드에 $blobValue 변수의 값을 저장하고 있습니다.
5.적절한 Optimizer Hint와 Index를 선택하자
애플리케이션을 테스트하다 보면 예상치도 못했던 성능 병목과 문제들을 발견하게 됩니다. 엔드 유저들은 기대하지도 않았던 곳에서 이상한 버그들을 찾아내는 묘한 재주를 갖고 있습니다. SQL에 관련한 문제를 확인하는 가장 좋은 방법은 모든 SQL 구문과 그 실행시간을 로그에 저장하고, 가장 많은 시간을 사용한 SQL 구문의 실행 계획(execution plan)을 확인하는 것입니다.
데이타가 올바르게 정규화된 환경이라면, 쿼리 옵티마이저에 힌트를 적용하고 인덱스 설정을 변경하는 것만으로도 쿼리 성능을 대폭적으로 향상시킬 수 있을 것입니다.
오라클의 EXPLAN PLAN 명령을 이용하면 실행 계획에 대한 정보를 확인할 수 있습니다. $sql 변수에 대해 EXPLAIN PLAN 명령을 실행하기 위해 임의의 $id 변수 값을 설정하고 아래와 같이 코드를 작성하였습니다:
$id = 'MyID'; $explainSQL = "EXPLAIN PLAN SET STATEMENT_ID='$id' FOR $sql"; $stmt = OCIParse($conn, $explainSQL); OCIExecute($stmt);
실행 계획은 PLAN_TABLE 테이블에 저장되며 statement_id를 기준으로 조회할 수 있습니다.
CREATE TABLE PLAN_TABLE ( STATEMENT_ID VARCHAR2(30), TIMESTAMP DATE, REMARKS VARCHAR2(80), OPERATION VARCHAR2(30), OPTIONS VARCHAR2(30), OBJECT_NODE VARCHAR2(128), OBJECT_OWNER VARCHAR2(30), OBJECT_NAME VARCHAR2(30), OBJECT_INSTANCE NUMBER(38), OBJECT_TYPE VARCHAR2(30), OPTIMIZER VARCHAR2(255), SEARCH_COLUMNS NUMBER, ID NUMBER(38), PARENT_ID NUMBER(38), POSITION NUMBER(38), COST NUMBER(38), CARDINALITY NUMBER(38), BYTES NUMBER(38), OTHER_TAG VARCHAR2(255), PARTITION_START VARCHAR2(255), PARTITION_STOP VARCHAR2(255), PARTITION_ID NUMBER(38), OTHER LONG, DISTRIBUTION VARCHAR2(30) );
앞에서 이미 설명한 것처럼, ADOdb는 실행된 SQL 구문을 캡처하여 로그에 저장합니다. EXPLAIN PLAN의 실행 결과는 웹 브라우저를 통해 확인할 수 있습니다. ADOdb에서 로깅을 활성화하려면 LogSQL 메소드를 실행해야 합니다:
$DB->LogSQL(true);
로깅이 활성화되면, SQL 구문, 실행시간, HTTP 요청의 URL 경로 등의 정보가 ADODB_LOGSQL 테이블에 덤프 됩니다. 아래 코드를 실행하여 ADOdb의 성능 모니터링 UI를 호출하고 로그에 저장된 SQL 정보를 확인할 수 있습니다:
<?php include_once('/path/to/adodb.inc.php'); session_start(); # session variables required for monitoring $conn = ADONewConnection('oci8'); $conn->Connect($tnsname,$user,$pwd); $perf =& NewPerfMonitor($conn); $perf->UI($pollsecs=5); ?>
H실행된 UI의 스크린샷이 아래 그림과 같습니다:
위 스크린에서 첫 번째 SQL 구문을 클릭하면, ADOdb가 해당 구문에 대해 EXPLAIN PLAN 명령을 실행하게 됩니다:
select * from (SELECT /*+FIRST_ROWS*/ * FROM myhist WHERE accno ='7314028790' ORDER BY POSTDT desc) where rownum <= :adodb_offset
ADOdb는 EXPLAIN PLAN의 실행 결과로 저장된 PLAN_TABLE 데이타를 아래와 같이 출력합니다:
OPERATION OBJECT_NAME COST CARDINALITY BYTES SELECT STATEMENT 63 456 39216 --COUNT STOPKEY ----VIEW 63 456 39216 ------SORT ORDER BY STOPKEY 63 456 39216 --------TABLE ACCESS FULL 55 456 39216 Scripts Affected: 4 localhost/juris/z/tloan/myloan.php
위의 경우, 하나의 ACCNO(account number)를 기준으로 레코드를 조회했음에도 불구하고 쿼리가 인덱스를 사용하지 않고 풀 스캔(TABLE ACCESS FULL)을 수행했음을 확인할 수 있습니다. 따라서 MYHIST.ACCNO 테이블에 인덱스를 추가해야 한다고 판단할 수 있습니다.
“COUNT STOPKEY”는 " rownum <= :adodb_offset"의 조건에서 쿼리의 실행이 중단됨을 의미합니다. 또 SQL이 실행된 페이지(myloan.php)가 SQL 구문의 실행 숫자(위의 경우 4회)만큼 반복되어 표시되었음을 확인할 수 있습니다. “Cardinality”는 영향 범위에 있는 레코드의 수를 의미합니다. “Byte”는 처리된 전체 바이트 수를 의미합니다.
ACCNO 필드에 인덱스를 추가하기 위해 다음과 같이 명령을 실행합니다:
CREATE INDEX ON myhist(accno)
인덱스를 설정한 후 다시 EXPLAIN PLAN을 실행합니다. 오라클이 새로 생성된 인덱스를 사용하고 있음을 확인할 수 있습니다:
OPERATION OBJECT_NAME COST CARDINALITY BYTES SELECT STATEMENT 10 456 39216 --COUNT STOPKEY ----VIEW 10 456 39216 ------SORT ORDER BY STOPKEY 2 456 39216 --------TABLE ACCESS BY INDEX ROWID MYHIST 2 456 39216 ----------INDEX RANGE SCAN ACCNO 1 182
인덱스 덕분에 쿼리의 실행 비용(cost)이 63에서 10으로 줄었음을 확인하실 수 있습니다.
6. 데이타와 비즈니스 로직을 효과적으로 관리하자
코딩 작업을 시작하기 전에 데이타베이스 설계에 각별히 주의를 기울일 필요가 있습니다. 데이타가 어떤 구조로 설계되었는가에 따라 데이타를 처리 결과가 크게 달라질 수 있기 때문입니다. 이에 관련하여 몇 가지 팁을 소개합니다:
Use auto-incrementing 필드의 확용. “auto-incrementing”는 trigger 또는 sequence를 이용하여 구현되며, primary key로 활용되는 경우 유용합니다.
ATABLE에 대해 auto-incrementing 필드 A_ID를 생성하는 예가 다음과 같습니다.
CREATE SEQUENCE aSEQUENCE; CREATE OR REPLACE TRIGGER A_ID BEFORE INSERT ON ATABLE FOR EACH ROW BEGIN SELECT aSEQUENCE.nextVal INTO :NEW.A_ID FROM dual; END;
새로운 레코드를 생성할 때마다 트리거가 동작하여 A_ID 필드에 새로운 값을 써넣게 됩니다. PHP에서 마지막으로 INSERT된 값을 확인하기 위해 aSEQUENCE의 현재 값을 조회하는 방법이 다음과 같습니다 (ADOdb를 사용한 경우):
$id = $DB->GetOne('SELECT aSEQUENCE.currVal FROM DUAL');
DUAL은 함수 호출 결과를 레코드셋으로 저장하고자 하는 경우 오라클에서 사용하는 스페셜 테이블입니다t.
데이타의 정규화. 데이타의 정규화(normalization)는 이 문서의 논의 범위에서 벗어나는 주제입니다. 여기에서는 모든 레코드에 프라이머리 키(primary key)가 설정되어 있어야 하며, 단일 테이블 안의 레코드들이 1:N의 관계를 갖는 경우 두 개 이상의 테이블로 분리해야 한다는 정도만 언급하기로 하겠습니다.
AUTHOR 테이블이 다음과 같은 필드를 갖는다고 가정해 봅시다:
AUTHOR: AuthorName Address Title1 ISBN1 Title2 ISBN2 Title3 ISBN3
Book1, Book2, Book3 필드는 AuthorName 필드와 1:N의 관계를 갖습니다. 이러한 구조는 나중에 문제가 될 수 밖에 없습니다. 예를 들어, 3개의 타이틀을 가진 저자의 경우 3개의 레코드가 중복적으로 삽입되게 됩니다.
이러한 문제를 예방하려면 이 테이블을 AUTHOR 테이블과 BOOK 테이블로 분리해야 합니다:
AUTHOR: AuthorName Address BOOK: ISBN AuthorName Title
인덱스의 활용. 인덱스의 사용에 주의해야 합니다. 인덱스는 쿼리의 성능에 가장 큰 영향을 미치는 요소입니다.
초보자들에게서 자주 발견되는 실수 중 하나가 중요한 필드마다 개별적으로 인덱스를 생성하는 것입니다. 하지만 자주 사용되는 쿼리가 세 개의 필드를 한꺼번에 조회하는 경우, 오라클은 (3개의 인덱스를 모두 사용하는 것이 아니라) 3개 중 하나의 인덱스만을 사용하게 될 확률이 높습니다. 따라서 세 개의 필드를 모두 포함하는 compound 인덱스를 생성하는 것이 보다 바람직합니다.
앞에서 설명한 것처럼 EXPLAIN PLAN 명령과 SQL 로깅 기능을 이용하여 인덱스를 적용한 후의 쿼리 수행 성능을 확인하시기 바랍니다.
Index-organized Table의 활용. 데이타를 일정 그룹 단위로 빈번하게 조회하는 환경이라면, index-organized table(IOT)를 적용하는 방안을 고려해 볼 수 있습니다. IOT는 “clustered index”라 불리기도 합니다. 일반적인 테이블의 프라이머리 키 인덱스(primary key index)와 데이타는 서로 다른 파일에 저장됩니다. 하지만 IOT에서는 데이타가 프라이머리 키 인덱스 내부에 저장됩니다. 데이타는 인덱스 리프(leaf)의 순서에 따라 순차적으로 저장되며, 따라서 프라이머리 키의 일정 범위를 기준으로 데이타를 조회하는 경우 쿼리의 실행 속도가 빨라지게 됩니다.
IOT가 유용한 경우를 예로 들어 설명해 보겠습니다. PHP를 이용하여 게시판을 구현하고 쓰레드(thread)를 이용하여 답변을 디스플레이하고자 합니다. 게시물과 게시물에 대한 답변을 함께 저장한다면, 한 번의 디스크 읽기 작업만으로도 전체 쓰레드를 가져올 수 있을 것입니다. 이와 같은 경우, 아래와 같은 compound key를 사용하여 IOT를 생성할 수 있습니다:
(id of the first post of the thread, the actual id of the post)
비즈니스 로직의 관리. 핵심 비즈니스 로직을 PHP로 작성할 수도 있습니다. 하지만 비즈니스 로직을 PL/SQL로 구현하는 경우 다음과 같은 장점이 있습니다:
핵심 비즈니스 로직의 중앙집중적인 관리가 가능합니다. 서버 팜을 운영하는 경우, 변경 사항을 적용하기 위해 모든 서버의 PHP 스크립트를 업데이트할 필요가 없습니다.
PL/SQL과 오라클 데이타베이스가 긴밀하게 통합되어 있기 때문에, PHP로 작성된 코드에 비해 PL/SQL로 작성된 코드가 로직을 이해하기 쉽습니다.
Java, Python, Visual Basic 등 다른 언어를 사용해서 동일한 함수에 접근할 수 있습니다.
장바구니 애플리케이션을 개발하는 경우를 가정해 봅시다. 많은 수의 상품이 있고, 일부 상품은 번들로 판매되는 경우 할인 혜택이 적용됩니다. 또 사용자 별로 할인 혜택을 적용 받는 경우가 있기 때문에 복잡한 가격 산정 과정을 거쳐야만 합니다.
먼저 CalcShoppingCartTotal($cartID)라는 이름의 PHP 함수에 비즈니스 로직을 구현하였습니다. 이 함수는 장바구니에 담긴 상품의 전체 가격을 계산해 줍니다. 하지만 시간이 흐르면서 이 함수가 성능 병목으로 작용하기 시작했습니다. 또 Java 기반 소프트웨어에서도 이 함수를 사용해야 할 필요성이 생겼습니다. 이러한 경우라면 함수를 PL/SQL로 마이그레이션하는 것이 가장 이상적인 방법입니다:
CREATE OR REPLACE FUNCTION CalcShoppingCartTotal( cartid IN number) RETURN number IS begin /* some code */ end;
다음에는 ADOdb를 이용하여 PL/SQL로 프로시저를 호출합니다:
$total = $DB->GetOne("SELECT CalcShoppingCartTotal(:0) FROM DUAL",array($cartid));
이제 새로운 비즈니스 요구사항이 생겼습니다. CalcShoppingCartTotal()에서 반환하는 결과에 "shipping code”와 “total”이 추가되어야 합니다. 그렇다면, 코드를 두 개의 OUT 매개변수를 갖는 프로시저로 수정하면 됩니다:
CREATE OR REPLACE PROCEDURE CalcShoppingCartTotal( cartid IN number, shipcode OUT number, total OUT number) IS begin /* some code */ end;
ADOdb에서 위의 저장 프로시저를 실행하는 방법이 아래와 같습니다:
$sql = "begin CalcShoppingCartTotal(:cartid, :shipcode, :total); end;"; $stmt = $DB->PrepareSP($sql); $DB->InParameter($stmt, $cartid, 'cartid'); $DB->OutParameter($stmt, $shipCode, 'shipcode'); $DB->OutParameter($stmt, $cartTotal, 'total'); $DB->Execute($stmt); print "<p>total = $cartTotal, shipping code=$ shipCode</p>";
7. Persistence Connection을 관리하자
데이타베이스 로그온 과정에서는 많은 시간과 리소스가 사용됩니다. 일반적인 웹 페이지의 실행 시간이 수 분의 일 초에 불과하다는 사실을 감안한다면, 이것은 상당히 큰 오버헤드로 작용합니다.
PHP는 “persistence connection”을 이용하여 이러한 오버헤드를 최소화하고 있습니다. PHP는 persistent 연결을 위한 풀(pool)을 관리하고 필요한 PHP 스크립트에 이를 할당합니다. Persistent 연결의 리사이클 작업은 OCI8 함수인 OCIPLogon과 OCINLogon, 또는 ADOdb 함수인 PConnect 또는 NConnect를 실행하는 시점에 수행됩니다.
PHP에서 persistent 연결을 사용하는 경우 매우 많은 수의 데이타베이스 연결이 생성되게 됩니다. 그 수는 대략 다음과 같이 계산됩니다:
(Apache 또는 FastCGI 프로세스의 수) * (사용 중인 오라클 userid의 수)
예를 들어, 256개의 child 프로세스를 실행 중인 Apache를 통해 두 개의 오라클 userid를 사용하고 있는 데이타베이스에 접근한다면, 웹 서버 당 256 * 2 = 512개의 연결이 생성될 것이라고 예상할 수 있습니다.
서버 팜의 웹 서버의 수가 늘어나면서, 데이타베이스 연결의 수도 이에 비례하여 증가하게 됩니다. 오라클은 각각의 연결에 2~3 MB에 달하는 리소스를 할당하므로, 결과적으로 데이타베이스 연결의 수가 많아질 수록 데이타베이스의 성능이 저하될 수 밖에 없습니다.
A또 한 가지 문제로 데이타베이스를 재시작하는 경우, 웹 서버도 함께 재시작 되어야 한다는 점을 들 수 있습니다. 기존의 persistent 연결이 더 이상 동작하지 않기 때문입니다.
APHP에서 persistent 연결을 사용하는 대신, 미들웨어 또는 데이타베이스의 커넥션 풀링(connection pooling)을 이용할 수도 있습니다. PHP에서 “nonpersistent” 연결을 사용하고자 하는 경우, OCI8의 OCILogon 함수 또는 ADOdb의 Connect 함수를 사용하여 연결하면 됩니다.
ODBC 또는 오픈 소스 소프트웨어 SQLRelay와 같은 미들웨어가 제공하는 커넥션 풀링 기능은 보다 지능적인 형태로 관리됩니다. ODBC와 SQLRelay가 제공하는 드라이버에서는 persistent 연결의 최대 수를 제한하도록 설정하는 것이 가능합니다. 하지만 이와 같이 하려면 ODBC API 또는 SQLRelay API에 대해 번거로운 코딩 작업이 수반되어야 합니다.
또 다른 대안으로 오라클의 Shared Server 테크놀로지를 이용하는 방법이 있습니다 (이 기능은 과거 MTS라 불리기도 했습니다). Shared Server는 공유 데이타베이스 프로세스의 풀을 생성하고 네트워크 요청을 접수할 때마다 리사이클 작업을 수행합니다. 데이타베이스 서버 내의 리소스가 리사이클 된다는 것은 PHP의 persistent 연결과 비교했을 때 분명한 차이점입니다. 이 기능을 사용하려면 먼저 오라클 DBA와 상의하는 과정이 필요하겠지만, 많은 수의 연결을 처리해야 하는 환경에서는 상당한 효과를 볼 수 있을 것입니다.
마지막 조언
Apache 또는 IIS를 웹 서버로 사용하는 환경에서 PHP와 오라클을 활용하는 경우, Apache를 prefork 모드로 실행하거나 IIS에서 PHP와 함께 FastCGI를 실행하는 경우 최적의 성능을 얻을 수 있습니다.
이와 같이 설정하는 경우 PHP 인터프리터는 완전히 독립된 프로세스로써 실행됩니다. PHP를 멀티쓰레드 모드(multithreaded mode)로 실행할 수도 있지만, 락 문제가 발생할 가능성이 높다는 문제, 그리고 일부 PHP extension의 경우 멀티쓰레드 모드에서 정상적으로 동작하지 않는다는 문제 때문에 성능과 안정성이 저하될 수 있습니다.
Apache 1.3은 UNIX 환경에서 디폴트로 prefork 모드를 사용합니다. Apache 2.0을 사용하는 경우에는 제품문서를 참고하여 prefork 모드로 설정할 수 있습니다. Windows 환경에서 FastCGI와 PHP를 설정하는 방법은 phplens.com/phpeverywhere/fastcgi-php에서 참고하실 수 있습니다. Zend에서 개발한 상용 패키지인 WinEnabler는 Windows 환경을 위한 FasCGI Installer로써 활용됩니다.
Good luck, and speedy querying!
|
|
|
