지난 3회동안주로애플리케이션프로그래밍측면, 즉 SQL문의성능저하요인을알아보았다. 프로젝트에서일어나는문제는문법이복잡다단하게어려운 SQL문이아니라데이터건수가많은것에대한경험부족인경우가많다. 그러므로지난호에기술한 10가지정도의유형에맞춰스스로사례분석을해보기바란다. 이번호에서는오라클에서제공하는성능뷰와딕셔너리를이용해문제를일으키는요인을찾는연습을하자. 발자들은일반적으로오라클의딕셔너리구조에대해친숙하지않다. 오라클은 v$ 로시작하는성능뷰와 DBA_, ALL_, USERS_ 로시작하는딕셔너리를가지고있다. 여기에대한정보를파악하고어떻게이용하느냐에따라여러분의작업환경은많이개선될것이다. 이보아왔다. 내프로그램이잘수행되고있는지알고싶을때, 내프로그램이어느 SQL문을수행하고있는지알고싶을때, 내프로그램이수행하고있는세션을중단하고싶을때등일일이 DBA의도움을청하기도미안하다. 자신이작성하고수행하는프로그램에대해서는다음과같은절차로원인분석을해보길바란다. 중요딕셔너리 < 표 1> 은개발자들도꼭알아두었으면하는성능뷰와자주참조하는딕셔너리목록이다. DICTIONARY, DICT_COLUMNS를조회하면용도가자세히나온다. < 표 1> 에나오는내용을외울필요는없다. naming rule이눈에들어오면자연스럽게외워진다. 수시로 SQLPLUS상에서 DESC하여해당컬럼구성을눈에익히고오늘기술할성능관련 SQL 스크립트에서사용되는컬럼은레퍼런스책을통해의미를꼭알아두기바란다. 세션별로문제원인찾기지난시간에도간략히언급한바있는데, 자신이작성한프로그램을수행시켰을때예상외로시간이많이걸리고있는데도원인찾기를포기한채 DBA에게의뢰하거나끝날때까지기다리는개발자를많 OS 상의점검 top, glance, sar 명령을통한 CPU 및메모리점유율확인일단프로그램의수행이느린경우 OS 나 DBMS에대한부하나이상으로인해느린지아니면해당애플리케이션때문에느린지를빨리판별하는것이중요하다. 간단한명령으로 top, glance, sar 명령등을통해서버의부하가많은지를판별할수있다. ps -ef grep을이용오라클관련프로그램은반드시대응되는오라클서버프로세스가있다. MTS(Multi Thread Server) 환경으로설정되어있지않다면프로그램과오라클서버프로세스는대부분 1:1이다. 만약리스너 (Listener) 를통하지않았다면프로그램 ID를가지고오라클서버프로세스를간단히찾을수있다. SQLPLUS를실행시킨다음 SQLPLUS에서! 명령으로 OS로빠져와서 < 화면 2> 와같이실행시켜보자. < 화면 2> 의 a와같이 SQLPLUS의프로세스 ID 24463을가지고 ps -ef 명령을수행하면 24463을모프로세스로하는오라클서버프 251
프로그램수행 수행지연 OS 점검세션점검락점검 top, glance sar ps -ef top : 점유율상위순위에따라보여줌 b 자신의터미널번호 세션 ID 로찾기 세션수행 SQL 문찾기 SQL 문분석 PLAN 작성 TRACE 수행 세션정보모니터링 락모니터링 블러킹모니터링 웨이팅이벤트분석 sar : 서버의 CPU, I/O 현황을시간대별로간략히보여줌 glance : 서버의 CPU, I/O 현황을보여줌 a 프로그램과오라클서버프로세스 c 실행되는동안 CPU 가증가됨 로세스가 24466으로나타난다. 이세션에서 DB와관련된작업이이루어진다면 c부분에 CPU가증가되는것이보인다. 만약이 CPU 변화량이낮다면시스템자원을제대로활용을못하거나락에의해수행되지못한다는것이고 CPU 변화량이많음에도불구하고프로그램이느리다면이는잘못된액세스경로로 SQL문이수행되고있을소지가많다. 만약 SQLPLUS를많은유저에서수행하였다면 OS 유저나 b에서처럼터미널번호를가지고해당프로그램을찾으면된다. 기억할것이또하나있다. c부분을보면오라클서버프로세스에 LOCAL=YES라고되어있는부분이있다. 이는해당로컬서버에서리스너를통하지않고직접연결된것이다. 만약사용자 PC나다른서버에서접속하거나 @ 서비스명 을사용한다면 LOCAL=NO 라고나타나며상위프로세스번호가 1로보일것이다. 이런경우에는오라클딕셔너리정보를찾아오라클서버프로세스번호를찾아내야한다. 그리고 LOCAL=YES인데상위프로세스번호가 1이라면이는비정상적으로연결이끊어진프로세스이다. 이런프로세스가락을잡고다른프로세스가수행하는것을방해할수있으니상태확인후 OS 명령 kill 을사용해서삭제해야한다. 세션점검 세션 ID 찾기여러분이만약세션에대한정보를찾고자한다면세션에관련된 ID, 즉 SID(System IDentifier), SERIAL# 를먼저알아야관련된정보를찾기편하다. 세션의기본데이터는 V$SESSION이라는성능뷰를통해알수있다. V$SESSION에대한세부내역은오라클의 USER s REFERENCE 또는 DICT_COLS를통해확인하기바란다. 중요컬럼은 < 화면 3> 과같다. 권한문제로해당예제가실행되지않을수있다. 해당 DBA에게 SELECT ANY TABLE 권한을임시 로부여해달라고요청하기바란다. 되도록이면 PC라도좋으니자기야한다. < 화면 3> 에서처럼 SID, SERIAL# 을구한다음 ALTER 가맘대로환경을구현하는환경이면좋겠다. 이런조그마한 SQL문 SYSTEM KILL SESSION SID, SERIAL# ; 명령어로정상적으들때문에시스템이나 DB가멈추진않지만괜히의심받기때문이다. 로종료시키는것이좋다. < 화면 3> 에서프로세스번호, 프로그램명, 실행서버, 터미널, 접속주의할것은 V$SESSION과 V$PROCESS의연결고리는사용자등의정보를통해해당세션의 SID를구할수있을것이다. V$SESSION의 PADDR과 V$PROCESS의 ADDR이라는것이다. 만약프로그램이강제킬 (kill) 되어롤백중이라면 Serial# 의번호가 < 화면 4> 에서왼쪽의 PROCESS는수행한프로그램자체의프로세계속바뀔것이다. 그다음오라클서버프로세스번호를알고싶다면스번호를뜻하며, SPID는이와연결된오라클서버프로세스의번 V$PROCESS를이용하면된다. 데이터베이스프로그램이나오라클호이다. 만약 TKPROF를수행하기위해 SQL_TRACE 옵션을수행서버프로세스를 OS 차원에서강제로킬시키는일은되도록자제해하였다면 udump 디렉토리에 trace 파일이쌓이는데이오라클서버 분류 성능뷰 / 딕셔너리 딕셔너리 분류 성능뷰 / 딕셔너리 딕셔너리 세션과관련된정보 V$SESSION 세션에대한전반적인정보를보여준다. 대한정보를보여준다. V$SESSSTAT 세션의현황에대한통계정보를보여준다. DBA_OBJECTS 모든오브젝트에대한정보를보여준다. V$SESSION_WAIT 세션의 waiting 통계정보를보여준다. 테이블스페이스 V$TABLESPACE 테이블스페이스에대한정보를보여준다. V$SESSION_EVENT 세션의현재 waiting 이벤트를보여준다. DBA_TABLESPACES 테이블스페이스에대한정보를보여준다. V$SESS_IO 세션의 IO 현황을보여준다. DBA_DATA_FILES 테이블스페이스를구성하고있는데이터 V$STATNAME SESSSTAT의 STATUS의이름을보여 파일에대한정보를보여준다. 준다. DBA_FREE_SPACE 아직사용되지않은영역에대한정보를 성능관련정보 V$SYSTAT 시스템전반의성능통계정보를보여준다. 보여준다. V$SYSTEM_EVENT 시스템의 waiting 이벤트별통계정보를 DBA_EXTENTS 할당된익스텐트의정보를보여준다. 보여준다. DBA_TS_QUOTAS QUOTA가설정된정보를보여준다. V$LIBRARYCACHE 라이브러리캐시사용정보를보여준다. 테이블 DBA_TABLES 테이블에대한정보를보여준다. V$ROWCACHE 데이터딕셔너리의사용정보를보여준다. DBA_TAB_COLUMNS 테이블을구성하는컬럼에대한정보를보 V$LATCH LATCH에대한정보를보여준다. 여준다. V$LOCK 락에대한정보를보여준다. DBA_TAB_COMMENTS 테이블의설명에대한정보를보여준다. V$LOCKED_OBJECT 락이걸린오브젝트에대한정보를보여준다. DBA_PART_TABLES 파티션테이블에대한정보를보여준다. V$SQLAREA SQLAREA에대한정보를보여준다. DBA_PART_KEY_ 파티션을구성하는기준컬럼에대한정보 V$WAITSTAT 시스템의현재 waiting 현황을보여준다. COLUMNS 를보여준다. SQL 관련 V$SQL parse된 SQL 문장을보여줌 DBA_COL_COMMENTS 컬럼의설명에대한정보를보여준다. V$SQLTEXT 라인별로 SQL 문장을보여줌 인덱스 DBA_INDEXES 인덱스에대한정보를보여준다. V$SQLTEXT_WITH_ Newline 을포함하여 SQL 문장을보여 DBA_PART_INDEXES 파티션된인덱스에대한정보를보여준다. NEWLINESS 준다. DBA_IND_COLUMNS 인덱스를구성하는컬럼에대한정보를보 시스템구성정보 V$SGA SGA의정보를보여준다. 여준다. V$PARAMETER InitSID.ora 등에서설정된파라미터, 즉 CONSTRAINT DBA_CONSTRAINTS 테이블에걸려있는제약조건을보여준다. 데이터베이스가구동되었을때의환경파라미터정보이다. DBA_CONS_COLUMNS 제약조건을구성하는컬럼에대한조건을보여준다. V$CONTROLFILE 컨트롤파일에대한정보를보여준다. 뷰 DBA_VIEWS 뷰를정의한정보를보여준다. V$DATAFILE 데이터파일에대한정보를보여준다. 시노님 DBA_SYNONYMS 시노님에대한정보를보여준다. V$LOG, V$LOGFILE redo 로그에대한정보를보여준다. 시퀀스 DBA_SEQUENCES 시퀀스에대한정보를보여준다. 사용자 DBA_USERS 데이터베이스사용자에대한정보를보여준다. DB LINK DBA_DB_LINKS DB 링크에대한정의를보여준다. 권한 DBA_ROLES 롤에대한정보를보여준다. 트리거 DBA_TRIGGERS 트리거에대한정의를보여준다. DBA_TAB_PRIVS 테이블에대한권한이설정된정보를보여준다. DBA_TRIGGER_COLS 컬럼단위로작성된트리거에대한정의를 DBA_SYS_PRIVS 시스템권한이설정된정보를보여준다. 보여준다. DBA_ROLE_PRIVS 롤에대한권한이설정된정보를보여준다. ROLLBACK DBA_ROLLBACK_SEGS 롤백세그먼트에대한정보를보여준다. DBA_COL_PRIVS 컬럼단위로권한이설정된정보를보여준다. FUNCTION, DBA_SOURCE FUNCTION, PROCEDURE,PACKAGE 세그먼트 & 오브젝트 DBA_SEGMENTS 세그먼트 ( 저장공간이있는오브젝트 ) 에 PROCEDURE, PACKAGE 를구성하는 PL/SQL 소스코드를보여준다. 252 253
프로세스의번호가이름에포함된다. 이를통해 tkprof를수행시킬대상파일임을알수있다. 세션에서수행중인 SQL문찾기프로그램이느리다고생각한경우어느부분에서느린지몰라답답해하는사람들이많다. 물론요즈음은국산으로개발된 DB 모니터링툴이많지만개발자들에게까지이툴을사용하게할만큼충분한라이선스를구매해주는프로젝트는없다. 에서 SID를구했다면여러분은간단한 SQL 문장을통해해당세션에서수행되고있는 SQL 문을조회할수있다. < 화면 5> 와같이 SQL 찾는 SQL문을계속반복해서수행해보면현재문제를일으키는 SQL문을금방조회할수있다. 권한문제로실행을못하는독자는 DBA 계정을가진이에게 grant select on V_$SQLTEXT_WITH_NEWLINES to scott; 와같이권한을할당해줄것을요청하기바란다. SQL문분석 번에서문제가될만한 SQL문을찾았다면 EXPLAIN 문장을써서해당 SQL문을 PLAN 작성해분석해야한다. 플랜작성은지난 3회동안많은지면을할애해설명했으니다시상기해보자. SQLPLUS의오토트레이스기능활용 EXPLAIN 명령과 PLAN 테이블조회 tkprof 유틸리티활용방법만약아직도이방법에대해모른다면튜닝은할생각도말아야한다. 기초중의기초임을다시한번강조한다. tkprof를활용해플랜을작성하고싶으나 SQL_TRACE=TRUE 옵션없이수행되는프로그램에대해서도트레이스 (TRACE) 를작성할수있는방법이있다. EXPLAIN dbms_session.set_sql_trace_in_session(sid, serial#, true); col name for a50 select a.sid,b.name,a.value from v$sesstat a,v$statname b where a.statistic#=b.statistic# and a.sid = &SID order by 1 세션이벤트통계정보각세션에발생된이벤트에대한통계치를누적하여보여준다 col event format a30 col t_wait format 999999 col t_out format 99999 col t_waitd format 99999 col m_wait format 99999 select event, total_waits t_wait, total_timeouts t_out, time_waited t_waited, average_wait a_wait, max_wait m_wait from v$session_event where sid = &SID 세션이벤트 wait 정보세션이지금 waiting하고있는상태를보여준다 (< 화면 11>). TRACE 파일이어디쌓이는지모르는독자가있다면 USER_ DUMP_DEST에지정된파일경로를찾는다 (USER_DUMP_DEST 는 V$PARAMETER를조회하면된다 ). 세션모니터링아마 SQL문으로인한잘못이었다면 까지의과정을거쳐지금현재어느 SQL문이문제를일으키는지를파악할수있을것이다. 그렇다면이 SQL문이 CPU를과도하게수행하는지, I/O를과도하게일으키는지는세션을모니터링하는정보를가지고파악할수있다. 이는문제가발생할때마다경우의수가많아딱집어설명하기가어렵다. 서버튜닝에대한책자와웹사이트를통해자료를스스로찾아보기바란다. 단지이번호에는관련스크립트를보여줄테니성능이의심스러울경우수행하여그때나타나는현상을잘정리해두기바란다. 세션통계정보해당세션의통계정보를보여주는스크립트이다. 각세션별로 CPU, 메모리등의전반적인상태를모니터링할수있다 (< 화면 6>). col p1text format a10 col p2text format a10 col p3text format a10 col e_name format a20 col p3 format 999 select SID, substr(event, 1, 20) e_name, P1, P1RAW, P3TEXT, P3 from v$session_wait WHERE SID=&SID 락모니터링 락걸린테이블찾기데이터베이스는데이터의무결성을보장하기위해서락이라는개념을사용한다. 락을가장일반적으로설명하면같은데이터를동시에수정이불가하다고정의를내리는것이이해하기가편하리라본다. 데이터를생성, 수정, 삭제하는프로그램이 CPU나메모리사용률이 증가하지않고 I/O도증가하지않는다면제일먼저락을의심해보는것이좋다. 다른프로세스가같은데이터를조작하고있는것이다. 개발자들은막연히어딘가락이걸려있다고의심해보지만모니터링할지몰라 DBA를찾는경우가많다. 그래서큰목소리로 혹시어느테이블수정하고있는사람있나요? 라고다른개발자들을향해소리치곤한다. 자신의 PC 에다른화면에서커밋이나롤백을하지않고있음에도불구하고. 또한프로그램이비정상적으로종료하여롤백하는동안에도락이걸리기때문에 ps -ef로못찾는경우도종종있다. 다음의예로락을찾아보자. 두개의화면을열고모니터링해보도록하자. 한화면에서업데이트를해본다. 그다음다른화면에서같은 SQL문을수행해보자. 여기서필자는첫번째는 SCOTT 유저로로긴해서하였고다음다른화면은 SYSTEM으로로긴하여 UPDATE SCOTT.DEPT SET DNAME = 연구소 WHERE DEPTNO = 10; 을수행했다. 254 255
< 화면 10> 처럼 MODE_HELD가익스클루시브모드인 SID 8번의프로세스가다른프로세스를수행못하도록블러킹하고있다고보면된다. 반대로 MODE_REQUEST가익스클루시브인 SID 7번은락이풀리기를기다리는프로세스이다. 자 V$SESSION 을이용하여해당정보를모니터링해보자. 앞에서수행했던 SQL문을응용하여 < 화면 11> 과같이수행하였다. 현재 SID 7번이 enqueue 이벤트를일으키고있으며락이풀리기를기 < 화면 8> 에서커밋이나롤백을하지않았기때문에분명두번째화다리는액티브한상태이다. Enqueue 이벤트는락을기다리는전형적면은실행되지않을것이다. < 화면 9> 와같이실행시켜락걸린오브인이벤트이다. 락은단지트랜젝션간의데이터조작뿐만아니라젝트를찾아보자. DDL문, 과도한익스텐트발생으로인한락등다양한형태를지니고있다. 이런다양한형태의락은 < 화면 10> 의 SQL문에서 LOCK_TYPE 락으로인해수행되지못하는블러킹세션모니터링란을조회해본다면원인이무엇인지쉽게알수있다. 물론용어가아 < 화면 9> 의 SQL문은락이걸린오브젝트를쉽게찾아준다. 하지만직낯설것이지만 TX, TM, ST, TS가대부분발생한다. 이 SQL문도어느세션이다른세션을수행하지못하도록블러킹하고있는지판단을내릴수있는정보를주지않는다. 블러킹을하는세션 waiting 이벤트분석이어느세션인지알려면 < 화면 10> 의 SQL문을실행해주면된다. 지 < 화면 10> 에서수행했던 SQL문에서보듯 v$session_event의이벤난호에도설명했던중요한 SQL문이다. 반드시기억해두기바란다. 트는현재작업이발생되고있는행위를나타내준다. 반드시 wait 이 이벤트명 상태 대처방안 SQL*Net message from client Idle 상태를의미하며클라이언트로부터작업요구를기다림을의미한다. SQL*Net message to client 클라이언트로작업결과를내보내고있음을의미 db file scattered read 테이블의인덱스스캔시나타남 db file sequential read 테이블풀스캔시나타남 enqueue 트랜젝션간의락킹현상, 익스텐트과도발생등으로인하여발생 프로세스의간섭현상최소화, 적절한익스텐트할당등 Latch free 주로 SGA에대한자원경합이발생할경우발생 SQL문의표준화, BIND 변수사용등 Buffer cache waits 여러세션이동시에같은블럭의데이터를읽거나변경하고자할때주로발생 SQL문튜닝을통해불필요데이터를읽지않도록함 블럭당 rows 수를줄이도록 PCTFREE, PCTUSED 조정 undo에대한경합은롤백세그먼트의개수와크기를증가시킬것 Free buffer waits, DBWR의백그라운드프로세스가작성하는대기시간 Async-IO 또는멀티 DBWR를사용 (Async-IO를추천 ) write complete waits Redo buffer space redo 로그를할당하기위해대기중인이벤트 로그버퍼의크기증가 Redo file parallel write LGWR이 redo 버퍼를 redo 로그파일에기록하도록요청한이벤트 redo 로그발생량을최대로줄이고디스크를분산시킴 벤트가일어난다고해서느린것은아니다. 하지만원인분석에많은도움을준다. 이에해당하는이벤트를모두기술하자면상당한분량이될것이다. < 표 2> 를통해개발자들이알아두어야할이벤트에대해간단히알아보자. < 표 2> 에서기술한이벤트가빈번하게지속적으로나타난다면원인을찾아조치를취해야할것이다. 하지만이런이벤트도 SQL문을잘못써서데이터베이스에부담을주는현상이이런이벤트로나타나는경우가많기때문에무턱대고파라미터를늘려잡는것은바람직하지않음을명심해야한다. 데이터베이스전체모니터링지금까지세션단위로모니터링하면서문제점을발견해나가는연습을했다. 이제내친김에전체적으로모니터링해보자. 이것은서버튜닝의맛배기로생각하고편안하게즐기기바란다. 그리고서버튜닝에대한자세한내용은책자를읽으면서각자심도있게공부하기바란다. 사실이영역은개발자의영역을벗어난다. 하지만요즘개발자가프로그램만코딩하고설계하란법이있는가? 기왕여기까지온이상서버튜닝영역에도도전해보자. 서버튜닝은자신이환경을구축하고마음대로설정할수있는환경이필요하다. 인스톨부터해보는것이좋다. 다음은서버튜닝이라기보다데이터베이스의현황을전반적으로점검해보는스크립트를살펴본다. 필자가다니는회사홈페이지 (www.surotech.com) 자료실에올려놓을것이니다운받아필요할때사용하기바란다. 사용목적에따른 SQL문을소개한다. 현재클로즈안된커서가액세스하고있는오브젝트의정보를얻고자하는경우, 현재사용되고있는오브젝트를알고자할경우 select sid, owner, object, type from v$access order by 1, 2, 3, 4; 데이터딕셔너리의상세현황을보고자할경우 select cache#, type, subordinate#, parameter, count, usage, fixed, gets, getmisses Get Misses, scans, scanmisses Scan Misses, scancompletes Scan Completes, modifications, flushes from v$rowcache; 데이터딕셔너리의요약현황을보고자할경우 select sum(count) Count, sum(usage) Usage, sum(fixed) Fixed, sum(gets) Gets, sum(getmisses) Get Misses, sum(scans) Scans, sum(scanmisses) Scan Misses, sum(scancompletes) Scan Completes, sum(modifications) Modifications, sum(flushes) Flushes, from v$rowcache; select round(sum(gets)/(sum(gets)+sum(getmisses)) * 100, 2) from v$rowcache; DB 블럭버퍼에서읽혀진횟수를보고자할경우 # Returns a count of gets in the db block buffer. where name in ( db block gets, consistent gets ); DB 블럭버퍼의활용도를보고자하는경우 select round((1-(pr.value/(bg.value+cg.value)))*100,2) pr, v$sysstat bg, v$sysstat cg where pr.name = physical reads and bg.name = db block gets and cg.name = consistent gets ; DB 블럭버퍼의전반적인리포팅 column phys_read heading Physical Reads format 99999999990 column block_get heading Block Gets format 99999999990 column consi_get heading Consistent Gets format 99999999990 column bchr heading BCHR format 999.90 select pr.value phys_read, bg.value block_get, cg.value consi_get, (1 - ( pr.value/(bg.value+cg.value) ) ) * 100 bchr pr, v$sysstat bg, v$sysstat cg where pr.name = physical reads and bg.name = db block gets and cg.name = consistent gets ; DB 블럭의사용현황을요약하고자할경우 select decode(state, 0, Free, 1, Read and Modified, 2, Read and Not Modified, 3, Currently Being Read, Other ), count(*) 256 257
from x$bh group by decode(state, 0, Free, 1, Read and Modified, 2, Read and Not Modified, 3, Currently Being Read, Other ); 디스크로부터가장많이읽혀지는 SQL 문장을알고싶은경우 from v$datafile d, v$filestat s where d.file# = s.file# order by d.file#; Free list wait 일어난비율 ( 낮을수록좋음 ) 래치상세현황 select ln.name, lh.pid, l.immediate_gets, l.immediate_misses, from v$statname n, v$sesstat s where n.statistic# = s.statistic# and name = session uga memory max ; select sql_text select round((sum(decode(w.class, free list,count, 0)) / (sum(decode(name, db block gets, value, 0)) + sum(decode(name, consistent gets, value, 0)))) l.gets, l.misses, l.sleeps 오픈트랜잭션횟수 select sum(xacts) from v$rollstat; and username is not null * 100, 2) from v$latch l, v$latchholder lh, v$latchname ln and disk_reads/executions = (select max(disk_reads/executions) and username is not null and executions > 0); 버퍼에서가장많이읽혀지는 SQL 문장을알고싶은경우 select sql_text and username is not null from v$waitstat w, v$sysstat; 시스템테이블스페이스내인덱스생성현황 select count(*) from sys.dba_indexes i where i.tablespace_name = SYSTEM and i.owner not in ( SYS, SYSTEM ); 네트워크부하 (bytes) where l.latch# = ln.latch# and l.addr = lh.laddr(+) order by l.level#, l.latch#; 래치효율성평가 (100에가까울수록좋음 ) select round(((sum(l.immediate_gets) + sum(l.misses) + sum(l.gets)) / (sum(l.immediate_gets) + sum(l.immediate_misses) + sum(l.gets) + sum(l.misses))) from v$latch l; Parse 효율성 select round(sum(decode(name, opened cursors cumulative, value, 0)) / sum(decode(name, parse count, value,0)) * 100, 2) ; Parse 현황 select ptc.value Parse Time CPU, pte.value Parse Time Elapsed, pc.value Parse Count ptc, v$sysstat pte, v$sysstat pc and buffer_gets/executions = (select max(buffer_gets/executions) and username is not null); where name like bytes%sql*net% ; 데이터파일로부터물리적 I/O 횟수 래치종합현황 select sum(l.immediate_gets), sum(l.immediate_misses), sum(l.gets), sum(l.misses), where ptc.statistic#=96 and pte.statistic#=97 and pc.statistic#=98; 물리적읽기횟수 익스텐트현황을알고싶은경우 select owner, segment_name, segment_type, count(*) numext, round(sum(bytes)/1024/1024,1) MB where owner not in ( SYS, SYSTEM ) group by segment_name, segment_type order by segment_type, round(sum(bytes)/1024/1024,1) desc, segment_name; 익스텐트가가장많이일어난횟수 select max(extent_id) + 1 where owner not in ( SYS, SYSTEM ); 익스텐트가가장많이일어난세그먼트 select owner, segment_name where owner not in ( SYS, SYSTEM ) and extent_id = (select max(extent_id) where owner not in ( SYS, SYSTEM )); 데이터파일별액세스유형별횟수 select name, phyrds Total Reads, phywrts Total Writes, phyblkrd Blocks Read, phyblkwrt Blocks Written select sum(phyrds) + sum(phywrts) Total I/O from v$filestat; I/O의종합현황 select sum(decode(name, db block changes, value,0)) Block Changes, (sum(decode(name, db block gets, value,0)) + sum(decode(name, consistent gets, value,0))) Buffer Gets, sum(decode(name, physical reads, value, 0)) Physical Reads, (sum(decode(name, db block gets, value,0)) + sum(decode(name, consistent gets, value,0))) / sum(decode(name, physical reads, value, 0)) Gets / Reads ; 래치 (Latch) 로인한경합률 (0에가까울수록좋음 ) select round(greatest( (sum(decode(ln.name, cache buffers lru chain, misses,0)) / greatest(sum(decode(ln.name, cache buffers lru chain, gets,0)),1)), (sum(decode(ln.name, enqueues, misses,0)) / greatest(sum(decode(ln.name, enqueues, gets,0)),1)), (sum(decode(ln.name, redo allocation, misses,0)) / greatest(sum(decode(ln.name, redo allocation, gets,0)),1)), (sum(decode(ln.name, redo copy, misses,0)) / greatest(sum(decode(ln.name, redo copy, gets,0)),1))) from v$latch l, v$latchname ln where l.latch# = ln.latch#; sum(l.sleeps) from v$latch l, v$latchholder lh, v$latchname ln where l.latch# = ln.latch# and l.addr=lh.laddr(+); 라이브러리캐시효율성 (100에가까울수록좋음 ) select round(sum(pinhits)/sum(pins) from v$librarycache; 라이브러리캐시상세현황 select namespace name, gets, gethits, round(gethitratio*100,2) GetHit Percentage, pins, pinhits, round(pinhitratio*100,2) PinHit Percentage, reloads, invalidations from v$librarycache order by 1; 메모리앨로케이트현황 from v$statname n, v$sesstat s where n.statistic# = s.statistic# and name = session uga memory ; where name = physical reads ; 리커시브콜횟수 select value where name = recursive calls ; redo 로그래치경합 select round(greatest( (sum(decode(ln.name, redo copy, misses,0)) / greatest(sum(decode(ln.name, redo copy, gets,0)),1)), (sum(decode(ln.name, redo allocation, misses,0)) / greatest(sum(decode(ln.name, redo allocation, gets,0)),1)), (sum(decode(ln.name, redo copy, immediate_misses,0)) / greatest(sum(decode(ln.name, redo copy, immediate_gets,0)) + sum(decode(ln.name, redo copy, immediate_misses,0)),1)), (sum(decode(ln.name, redo allocation, immediate_misses,0)) / greatest(sum(decode(ln.name, redo allocation, immediate_gets,0)) + sum(decode(ln.name, redo allocation, immediate_misses,0)),1))) from v$latch l, v$latchname ln where l.latch# = ln.latch#; redo 로그정보 select value where name = redo log space waittime ; select sum(decode(name, redo blocks written, value,0)) Block Writes, sum(decode(name, redo entries, value, 0)) Entries, 258 259
sum(decode(name, redo size, value, 0)) Size, sum(decode(name, redo log space requests, value, 0)) Space Requests, sum(decode(name, redo synch writes, value,0)) Synch Writes, sum(decode(name, redo writes, value,0)) Writes ; Sort_Area 효율성 select round((sum(decode(name, sorts (memory), value, 0)) / (sum(decode(name, sorts (memory), value, 0)) + sum(decode(name, sorts (disk), value, 0)))) 시스템테이블스페이스에생성된테이블수 select count(*) from sys.dba_tables t where t.tablespace_name = SYSTEM and t.owner not in ( SYS, SYSTEM ); ; 라이브러리캐시활용도 Next 익스텐트가프리영역보다큰경우 select round((1 - (sum(reloads) / sum(pins))) * 100, 2) from v$librarycache; 롤백세그먼트경합률 select round(sum(waits)/sum(gets),2) from v$rollstat; 롤백세그먼트현황 select n.usn, n.name, s.username Name, s.osuser, rs.extents, rs.wraps, rs.rssize Size (Bytes) from v$rollname n, v$rollstat rs, v$session s, v$transaction t where t.addr = s.taddr(+) and rs.usn(+) = n.usn and t.xidusn(+) = n.usn and rs.status = ONLINE order by n.usn; SGA 프리스페이스현황 select sum(decode(name, free memory, bytes, 0)) from v$sgastat; select round((sum(decode(name, free memory, bytes, 0)) / sum(bytes)) * 100,0) from v$sgastat; 소트현황 select username Name, osuser, sd.value Disk Sorts, sm.value Memory Sorts, sr.value Rows Sorted from v$session s, v$sesstat sd, v$sesstat sm, v$sesstat sr where s.sid = sd.sid and s.sid = sm.sid and s.sid = sr.sid and sd.statistic# = 101 and sm.statistic# =100 and sr.statistic# =102 and s.type!= BACKGROUND ; 소트건수 where statistic#=102); SQL AREA 활용현황 select username, sql_text, sorts, disk_reads Reads, buffer_gets Gets from v$sqlarea s, sys.dba_users u where s.parsing_user_id = u.user_id and users_executing > 0 select s.segment_name Segment Name, s.tablespace_name Tablespace Name, s.next_extent Next Extent, f.free_bytes Free Bytes from dba_segments s, (select tablespace_name, sum(bytes) free_bytes from dba_free_space group by tablespace_name) f where f.tablespace_name = s.tablespace_name and s.next_extent > f.free_bytes; 테이블스페이스프리스페이스현황 select tablespace_name Name, sum(bytes) Bytes, sum(blocks) Blocks, count(*) Number of Files from sys.dba_free_space group by tablespace_name; 테이블스페이스조각 (Fragmentation) 현황 select f.tablespace_name Tablespace Name, file_name File Name, block_id, Block Id f.blocks Number of Blocks, f.bytes Number of Bytes from dba_free_space f, dba_data_files d where f.file_id = d.file_id order by f.tablespace_name ASC, file_name ASC, f.blocks DESC; http://www.devtools.co.kr C++ 용 WinpkFilter, TDI Samples, NDIS Hooking, IM Driver Samples - 네트웍 RawEather - 네트웍 ZipTV - 압축 Pegasus 이미징관련등등 델파이용 SUIPack - UI 관련 ZipTV - 압축 XceedZip - 압축 ElPack - UI 관련 Athlant - 버전제어 FIBPlus - DB Advanced App Control Direct Oracle Access - DB ShellPlus - Shell 확장 SVCom - 서비스어플리케이션 FastReport - 리포트등등 SGA 사이즈 from v$sga; 셰어드풀의리로드횟수및비율 select sum(reloads) from v$librarycache where namespace in ( SQL AREA, TABLE/PROCEDURE, BODY, TRIGGER ); select round(sum(reloads) / sum(pins) from v$librarycache where namespace in ( SQL AREA, TABLE/PROCEDURE, BODY, TRIGGER ); order by 1; V$SYSSTAT를이용한테이블스캔현황 select value where name = table scans (long tables) ; select value where name = table scans (short tables) ; select s.value + l.value s, v$sysstat l where s.name = table scans (short tables) and l.name = table scans (long tables) ; 다음에는유형별튜닝사례를지금까지총 4회에걸쳐부족하지만개발자들이꼭알아야할튜닝포인트를기술했다. 고급튜닝기술자는많은어려움을고민하고극복한끝에탄생된다. 여러가지로부족하지만프로젝트를수행하면서어려운용어와두꺼운책자에기가질려첫걸음도떼지못하는개발자에게조금이나마도움을주고자용기를내어여기까지이강좌를이끌어왔다. 이론은이번호까지마치고자한다. 다음5회에는총정리를하기위해실제로일어났던사례를중심으로마무리를하고자한다. 특히지난 3회에기고하였던튜닝유형을중심으로사례를모아기고할것이니좋은사례가있으면메일을통해여러분의참여를기다린다. a m o s.net 용 HTML TextBox ActiveMail Active Calendar Active Admin WebControls Suite NetAdvantage Suite ActiveReports 등등 클립아트 ArtExplosion 등등 ( 주 ) 데브툴즈 전화 : (02)521-7900 팩스 : (02)2297-7900 260 261