현재 위치

홈 ›› Forums ›› 공부 ›› 프로그래밍 QnA

오라클 데이터베이스 레코드 입력속도 향상

gilsion의 이미지
글쓴이: gilsion / 작성시간: 목, 2005/01/20 - 10:19오전

안녕하세요. 제목에 대한 고민을 하고 있는 중입니다.

현재 상황은 이러합니다. 20만개에서 70만개 정도의 레코드가 분류별로 바이너리 파일에 있습니다. 이것을 아스키파일로 분해해서 파일로 만든다음 sqlldr 을 이용하여 DB 에 저장하고 있습니다.

대략 분류가 80개 정도되는데 대충 레코드를 다 집어넣으면 4000만개 정도 됩니다.

오라클의 엄청난 성능으로 원하는 사항에 대한 select 쿼리는 만족할만 합니다만 저 자료를 다 집어넣는데 시간이 좀 걸립니다. 대략 6~7시간은 걸리는것 같습니다.

아래는 16번 분류까지 실행하는데 걸리는 시간을 측정한결과입니다.
파일파싱 하는 시간도 포함이긴 하지만 과정을 눈으로 볼때 파싱시간은 엄청 짧으므로 SQLLDR을 이용한 입력시간이 대부분이라 생각됩니다.

분류     레코드수          소요시간
01       775,038         00::05::09
02       704,378         00::05::07
03       650,994         00::05::09
04       610,902         00::05::51
05       109,554         00::01::58
06       568,322         00::05::26
07       574,137         00::05::30
08       527,638         00::04::58
09       606,733         00::05::32
10       296,314         00::03::47
11       384,178         00::03::53
12       353,837         00::04::14
13       329,741         00::04::21
14       337,265         00::04::25
15       326,020         00::04::14
16       328,140         00::04::27

위에는 표시되어 있지 않지만 분가 80번에 다다르면 레코드가 10만개 정도로 작아지는데도 소요시간은 4~5분대를 지속적으로 유지합니다. 레코드가 작아도 시간은 계속 오래걸리는군요.

상황이 이러할땐 어떤방법으로 데이터입력 시간을 줄일수 있을까요.

앗. 프로그래밍 Qna로 썻군요. 이동바랍니다.

Forums: 
프로그래밍 QnA
익명 사용자의 이미지

글쓴이: 익명 사용자 / 작성시간: 목, 2005/01/20 - 10:57오전

사이즈는 틀리지만 한 800만개 넣는데 10분 내외로 걸렸던것 같은데...
네트웍을 고려하셔서 로칼에서 하시면 좋을것 같고 트랜잭션과 로그파일에 대한 고려 버퍼를 메뉴얼을 보시고 고려하시면 쾌청한 속도가 나올것 같습니다.

eminency의 이미지

글쓴이: eminency / 작성시간: 목, 2005/01/20 - 12:03오후

테이블 만들 때 인덱스를 먼저 걸어 놓으신게 아닌지요?

대량 레코드를 집어넣을 때는 인서트 후에 인덱스를 거는게 퍼포먼스가 낫다고 알고 있는데요.

노루가 사냥꾼의 손에서 벗어나는 것 같이, 새가 그물치는 자의 손에서 벗어나는 것 같이 스스로 구원하라 -잠언 6:5

kihlle의 이미지

글쓴이: kihlle / 작성시간: 목, 2005/01/20 - 12:58오후

ETL(혹은 ETT)작업을 하시는듯 하군요.
일반적으로 대량레코드를 넣을때 제 경험에 비추어 말씀드리겠습니다.
(아마 다 아시는 내용을 구구절절 설명드리는게 아닌가 ... ㅡ.ㅡ;; )

0. 병렬작업
어떤 플랫폼에서 작업하시는지는 모르겠지만 시스템자원을 최대한 쓰신게 아니라면 동시에 여러작업을 병렬로 돌리심이 좋습니다. (프로세서의 iowait점유율이 급격히 증가하지 않을정도까지)

1. SAM파일 생성과정
혹시 이 부분도 고민이시라면 프로그래밍 QnA에 올린게 맞습니다. :)

2. SQL Loader 적재과정
direct path, parallel, streamsize, multithreading, skip_index 같은 옵션들을 최대한 활용해야 합니다. 기본적으로 direct는 적용하셨나요?

결과를 보면 100만건도 안되는데 5분이 넘는걸보니 웬지 컨벤셔널로 돌리셨던가 하드웨어가 보통PC에 IDE저장장치일지도 모른다는 생각이 드는군요. (혹시 저게 금융권 DB서버에서 돌린건가요?) 어떤사양의 어떤플랫폼에서 나온 결과인지를 말씀해주시면 좀더 구체적인 참고가 될텐데요.

homeless

gilsion의 이미지

글쓴이: gilsion / 작성시간: 목, 2005/01/20 - 2:16오후

kihlle wrote:
ETL(혹은 ETT)작업을 하시는듯 하군요.
일반적으로 대량레코드를 넣을때 제 경험에 비추어 말씀드리겠습니다.
(아마 다 아시는 내용을 구구절절 설명드리는게 아닌가 ... ㅡ.ㅡ;; )

0. 병렬작업
어떤 플랫폼에서 작업하시는지는 모르겠지만 시스템자원을 최대한 쓰신게 아니라면 동시에 여러작업을 병렬로 돌리심이 좋습니다. (프로세서의 iowait점유율이 급격히 증가하지 않을정도까지)

1. SAM파일 생성과정
혹시 이 부분도 고민이시라면 프로그래밍 QnA에 올린게 맞습니다. :)

2. SQL Loader 적재과정
direct path, parallel, streamsize, multithreading, skip_index 같은 옵션들을 최대한 활용해야 합니다. 기본적으로 direct는 적용하셨나요?

결과를 보면 100만건도 안되는데 5분이 넘는걸보니 웬지 컨벤셔널로 돌리셨던가 하드웨어가 보통PC에 IDE저장장치일지도 모른다는 생각이 드는군요. (혹시 저게 금융권 DB서버에서 돌린건가요?) 어떤사양의 어떤플랫폼에서 나온 결과인지를 말씀해주시면 좀더 구체적인 참고가 될텐데요.

네. 주어진 데이터를 가지고 Analysis를 하고있습니다.
또한 예상하신 바와 같이 일반 워크스테이션 PC를 사용하고 있습니다.

0. 오라클에 대해 무지해서 그럽니다만. 병렬작업이라 함이 SQLLDR 을 한번에 여러개 돌려라 라는 의미인가요?

1. SAM파일 생성과정이 미치는 영향에 대해 알지못해 이해하지 못했습니다.

2. 나름대로 옵션을 기입하긴 했습니다.
sqlldr80 userid=user/passwd, direct=y ,control='C:\TEST\TEST.CTL', log='C:\TEST\TEST', bad='C:\TEST\TEST', errors=10000
가능하시면 언급하신 옵션에 대해 설명좀 부탁드립니다. 기본적으로 제공되는 sqlldr80 help만보면 이해하기가 좀 힘들더군요.

첫질문에 있는 속도결과는 파일파싱 하는 부분까지 포함되어있어서 현재 다시 속도측정중에 있습니다.조금은 더 작게 나오겠죠^^

지온의 이미지

글쓴이: 지온 / 작성시간: 목, 2005/01/20 - 4:33오후

저 또한 무지합니다.
그런데 문득 생각이 나서..
sam 파일을 쪼개서 pipe를 이용한 parallel로 실행하시면....

kihlle의 이미지

글쓴이: kihlle / 작성시간: 금, 2005/01/21 - 10:38오전

스토리지가 ide가 맞다면 그정도가 나오는것이 당연할지도 모르겠습니다.

병렬작업이라 함은 한번에 여러개수행을 말씀드린 것이 맞습니다.sqlldr1.sh & sqlldr2.sh & sqlldr3.sh & ...와 같은 식으로요. 동시수행갯수는 iostat로 모니터링했을때 iowait가 심하게 증가하지 않을정도로만 하시는것이 좋습니다.

oci에 익숙한 프로그래머라면 sam파일 생성과 sqlldr적재작업을 분리시키지 않고 바로 버퍼의 내용을 direct path loading을 이용하여 적재가 가능합니다. 중간생성되는 sam파일에 소모되는 io read/write 시간을 없앨수 있습니다. 관심있으시면 오라클웹사이트에서 oci프로그래밍문서를 참조하시기 바랍니다.
http://download-west.oracle.com/docs/cd/B14117_01/appdev.101/b10779/toc.htm
sqlldr의 옵션에 관한 설명은 헬프화면정도가 아니라 오라클의 공식문서를 참조하셔야 합니다. 아래의 utilities문서에서 sql*loader관련부를 찾으세요. case study가 도움이 될것입니다.
http://download-west.oracle.com/docs/cd/B14117_01/server.101/b10825/toc.htm

정보통신부의 우편번호테이블(엑셀파일로 제공되지요) 정도의 scheme에 건수는 100만건쯤되는 테이블로 작업한다고 가정할때,PC사양이 펜4 2GHz, U320scsi, 커널2.4, Oracle9i쯤되는데서 적재한다면 30초 (넉넉잡아) 안에는 끝나야합니다.

펜3 500, ide 같은채널의 같은 스토리지파티션에 오라클테이블스페이스와 sam파일이 모두 존재하며(최악의상황을 가정했습니다), 오라클이고 시스템이고 죄다 튜닝이 안되어있다면 gilsion님과 같은 결과가 나올수도 있을거라 생각합니다. :)

netsky wrote:
sam 파일을 쪼개서 pipe를 이용한 parallel로 실행하시면....
sqlldr은 named pipe만이 동작하고 stdin으로는 정상으로 작동치 않습니다. 그리고 direct path loading에서는 pipe를 사용할수 없습니다. (제가 잘못알고 있는것이면 답변부탁드리겠습니다)

homeless

gilsion의 이미지

글쓴이: gilsion / 작성시간: 금, 2005/01/21 - 3:35오후

감사합니다. 오라클/데이터베이스 지식을 넓히는데 많은 도움이 될 것 같습니다.

조금 깊이 있게 공부를 하면 개선된 결과를 얻을수 있을것 같습니다.

아 그리고 위에 제시된 결과는 믿을게 되지 못하는군요.
바이너리파일 파싱만 해보니 예상보다 시간이 너무 많이 걸렸었습니다.
아래가 sqlldr 테스트 결과입니다.

시스템 : 2.8G Xeon/1G mem/SCSI 하드

순번	레코드수	레코드누적		exec 시간
1	775,038 	775,038 		00:27.25
2	775,038 	1,550,076 	00:36.59
3	775,038 	2,325,114 	00:55.62
4	775,038 	3,100,152 	01:16.01
5	775,038 	3,875,190 	01:02.12
6	775,038 	4,650,228 	01:08.04
7	775,038 	5,425,266 	01:16.22
8	775,038 	6,200,304 	01:36.89
9	775,038 	6,975,342 	01:44.31
10	775,038 	7,750,380 	01:45.97

뭔가가 속도에 대한 개선이 있으면 댓글로 결과를 첨부하도록 하겠습니다.

gilsion의 이미지

글쓴이: gilsion / 작성시간: 화, 2005/02/01 - 10:34오전

고민한 결과 어쨋든 속도향상의 결과가 있었습니다.

데이터 입력의 상황은 이러합니다. 많으면 2백만개, 적으면 50만개의 레코드를필요마다 삽입해야합니다. 컬럼도 제법됩니다. NUMBER 형으로 12개쯤 되는군요.
데이터가 계속 누적되어 현재까지 입력되어있는 데이터의 갯수를 보면 대략 4000만개 정도 됩니다.

상황이 이러다보니 다시 50만건 정도의 데이터를 누적저장 시키면 5분 이상이 소요되었습니다.

kihlle님께서 알려주신데로 OCI 프로그래밍도 해보았지만(sample 코드는 대충 sqlloader 소스코드 이더군요) 크게 이득을 보지 못했었습니다.

결론적으로는 테이블생성시 데이터파일을 조건별로 분할시켜 저장했습니다.
데이터파일을 10개정도로 나누고 조건별로 저장을 하여 골고루 분산시켜보니 총 입력시간으로 본다면 거의 1/10 정도로 해결이 되는군요.

문제도 대충 해결한것 같고 지식도 넓힌것 같습니다. :)

댓글 달기

텍스트 포맷에 대한 자세한 정보

Filtered HTML

  • 텍스트에 BBCode 태그를 사용할 수 있습니다. URL은 자동으로 링크 됩니다.
  • 사용할 수 있는 HTML 태그: <p><div><span><br><a><em><strong><del><ins><b><i><u><s><pre><code><cite><blockquote><ul><ol><li><dl><dt><dd><table><tr><td><th><thead><tbody><h1><h2><h3><h4><h5><h6><img><embed><object><param><hr>
  • 다음 태그를 이용하여 소스 코드 구문 강조를 할 수 있습니다: <code>, <blockcode>, <apache>, <applescript>, <autoconf>, <awk>, <bash>, <c>, <cpp>, <css>, <diff>, <drupal5>, <drupal6>, <gdb>, <html>, <html5>, <java>, <javascript>, <ldif>, <lua>, <make>, <mysql>, <perl>, <perl6>, <php>, <pgsql>, <proftpd>, <python>, <reg>, <spec>, <ruby>. 지원하는 태그 형식: <foo>, [foo].
  • web 주소와/이메일 주소를 클릭할 수 있는 링크로 자동으로 바꿉니다.

BBCode

  • 텍스트에 BBCode 태그를 사용할 수 있습니다. URL은 자동으로 링크 됩니다.
  • 다음 태그를 이용하여 소스 코드 구문 강조를 할 수 있습니다: <code>, <blockcode>, <apache>, <applescript>, <autoconf>, <awk>, <bash>, <c>, <cpp>, <css>, <diff>, <drupal5>, <drupal6>, <gdb>, <html>, <html5>, <java>, <javascript>, <ldif>, <lua>, <make>, <mysql>, <perl>, <perl6>, <php>, <pgsql>, <proftpd>, <python>, <reg>, <spec>, <ruby>. 지원하는 태그 형식: <foo>, [foo].
  • 사용할 수 있는 HTML 태그: <p><div><span><br><a><em><strong><del><ins><b><i><u><s><pre><code><cite><blockquote><ul><ol><li><dl><dt><dd><table><tr><td><th><thead><tbody><h1><h2><h3><h4><h5><h6><img><embed><object><param>
  • web 주소와/이메일 주소를 클릭할 수 있는 링크로 자동으로 바꿉니다.

Textile

  • 다음 태그를 이용하여 소스 코드 구문 강조를 할 수 있습니다: <code>, <blockcode>, <apache>, <applescript>, <autoconf>, <awk>, <bash>, <c>, <cpp>, <css>, <diff>, <drupal5>, <drupal6>, <gdb>, <html>, <html5>, <java>, <javascript>, <ldif>, <lua>, <make>, <mysql>, <perl>, <perl6>, <php>, <pgsql>, <proftpd>, <python>, <reg>, <spec>, <ruby>. 지원하는 태그 형식: <foo>, [foo].
  • You can use Textile markup to format text.
  • 사용할 수 있는 HTML 태그: <p><div><span><br><a><em><strong><del><ins><b><i><u><s><pre><code><cite><blockquote><ul><ol><li><dl><dt><dd><table><tr><td><th><thead><tbody><h1><h2><h3><h4><h5><h6><img><embed><object><param><hr>

Markdown

  • 다음 태그를 이용하여 소스 코드 구문 강조를 할 수 있습니다: <code>, <blockcode>, <apache>, <applescript>, <autoconf>, <awk>, <bash>, <c>, <cpp>, <css>, <diff>, <drupal5>, <drupal6>, <gdb>, <html>, <html5>, <java>, <javascript>, <ldif>, <lua>, <make>, <mysql>, <perl>, <perl6>, <php>, <pgsql>, <proftpd>, <python>, <reg>, <spec>, <ruby>. 지원하는 태그 형식: <foo>, [foo].
  • Quick Tips:
    • Two or more spaces at a line's end = Line break
    • Double returns = Paragraph
    • *Single asterisks* or _single underscores_ = Emphasis
    • **Double** or __double__ = Strong
    • This is [a link](http://the.link.example.com "The optional title text")
    For complete details on the Markdown syntax, see the Markdown documentation and Markdown Extra documentation for tables, footnotes, and more.
  • web 주소와/이메일 주소를 클릭할 수 있는 링크로 자동으로 바꿉니다.
  • 사용할 수 있는 HTML 태그: <p><div><span><br><a><em><strong><del><ins><b><i><u><s><pre><code><cite><blockquote><ul><ol><li><dl><dt><dd><table><tr><td><th><thead><tbody><h1><h2><h3><h4><h5><h6><img><embed><object><param><hr>

Plain text

  • HTML 태그를 사용할 수 없습니다.
  • web 주소와/이메일 주소를 클릭할 수 있는 링크로 자동으로 바꿉니다.
  • 줄과 단락은 자동으로 분리됩니다.
댓글 첨부 파일
이 댓글에 이미지나 파일을 업로드 합니다.
파일 크기는 8 MB보다 작아야 합니다.
허용할 파일 형식: txt pdf doc xls gif jpg jpeg mp3 png rar zip.
CAPTCHA
이것은 자동으로 스팸을 올리는 것을 막기 위해서 제공됩니다.