SQL 튜닝및개발가이드 1
목차 목차...2 OPTIMIZER 관련권장사항요약...4 SQL TUNING 을위한 GUIDE... 6 SQL Tuning 시주의점...6 Execution Plan 보기...7 Execution Plan 보기실행예 (9i)... 7 Instance level 에동적으로 SQL_TRACE Enable/Disable... 8 9iR2 이상의 TKPROF 의향상된기능...9 Cached Execution Plan(V$SQL_PLAN)...10 Cached SQL Information (V$SQL)...10 Setting the Optimizer Mode... 11 Rule Base Ranking...12 Optimizer Basics (Features that Require the CBO)... 12 Default Heuristics Value...13 CBO Optimizer 가참조하는 Parameter (9iR2)... 13 Dynamic Sampling...14 Using System Statistics (>= 9i)... 15 OPTIMIZER 의통계정보 (ANALYZER 정보 ) 를위한권장안...18 Optimizer 의통계정보운영 Guide...18 Optimizer Statistics 정보란?...19 Analyzer 와 DBMS_STATS 의차이점...21 DBMS_STATS Package 의사용법... 21 Analyze & DBMS_STATS 의예... 22 Statistics 의 Maintenance... 24 DBMS_STATS 를이용한 Statistics 운영예... 25 Plan Stability(Stored Outline)... 27 OPTIMIZER 의이해와 ADVANCED SQL 을위한 JOIN METHOD...29 Optimizer 의원리이해... 29 Join Method 별특징... 34 PRO*C PRECOMPILE OPTION 권장안... 39 PRO*C 개발자의주의사항...39 Precompile Option...39 PL/SQL Engine 과 SQL Engine 의 Overhead 를줄이기위한방안...42 RELEASE_CURSOR 의 Option 에따른성능차이... 43 PREFETCH 의효율... 47 Scrollable Cursors (Oracle 9i R2)...50 APPLICATION 의 MODULE 명및단위 ROUTINE 명표시하기... 54 DBMS_APPLICATION_INFO Package 사용의장점... 54 DBMS_APPLICATION_INFO Package 사용의단점... 54 2
DBMS_APPLICATION_INFO Package 의사용예및 Cache 화되는내용... 55 PRO*C 에서의 DYNAMIC SQL 의사용에의한 SQL 공유화방안...57 개요... 57 Dynamic SQL 의구현...58 Dynamic SQL Method 의선택...63 지침... 64 OLTP 환경에서의비공유 SQL(LITERAL SQL) 의문제점... 65 공유 / 비공유 SQL 의장단점... 65 공유 / 비공유 SQL 의실행 TEST 결과... 65 관련사례... 66 SQL TUNING 대상을찾기위한 MONITORING 방법... 68 비효율적인 SQL List 보기 SQL Script (get_sqllist.sql)...68 과다한 Sorting 유발 SQL 찾기및 TEMP Tablespace 의 Sort Space 현황보기...70 현재 I/O 발생 (Full Table Scan 포함 ) Session 정보보기...72 ORACLE DATABASE 9I NEW FEATURES... 74 1. Forced Rewrite... 74 2. Union-All Rewrite of Queries with Grouping Sets... 74 3. Dynamic Sampling for the Optimizer... 75 4. Locally Managed SYSTEM Tablespace...75 5. Data Segment Compression...76 6.Shared Pool Advisory Statistics...76 7.PGA Aggregate Target Advisory... 76 8.FILESYSTEMIO_OPTIONS... 76 9.MTTR Advisory...76 10. Statistics Collection Level...77 11. Segment-Level Statistics... 77 12. Runtime Row Source Statistics... 77 3
Optimizer 관련권장사항요약 다음은일부 Optimizer와관련된 Parameter의주요변경사항을요약하였다. 가장중요한것은 workarea_size_policy 를 AUTO로운영할것인지, MANUAL로운영할것인지이며, AUTO로운영하게되면 *_AREA_SIZE를이용하지않고가능한 Temp I/O 없이 Sort,Hash Memory를이용하므로 Perfoamce의상당한효과를준다. 또한 Plan에영향을주는주요항목이다. 기본적으로아래의 Parameter를설정한환경하에서 Tuning하도록하며, Full Table Scan의규모가자주나서, 가능한 Index Scan위주로변경하고자한다면, optimizer_index_caching=0->20 ~ 40,optimizer_index_cost_adj=100 -> 40~80정도로조정해볼필요가있다. 기존의시스템에비해 DB Block Size가늘어나고, db_file_multiblock_read_count도늘어난다면 Full Table Scan이기존시스템보다커질가능성이아주높다. 그러므로이파라메터에주의를기울여야한다. 항목 1. Oracle Parameter 2. SQL 유형및 Tuning 권장사항내용 db_file_multiblock_read_count=16 or 32 hash_join_enabled=true optimizer_index_caching=0 (OPEN이후 Full Table 비중이너무높을경우 20 ~ 40으로 ) optimizer_index_cost_adj=100 (OPEN이후 Full Table 비중이너무높을경우 40 ~ 80으로 ) pga_aggregate_target= (OS Memory SGA) * 0.2 (==>SGA를제외한 OS Memory의 20% 로 Start) query_rewrite_enabled=true session_cached_cursors=0 (Literal SQL을공유화이후 100정도설정 ) shared_pool_reserved_size=0 shared_pool_size=( 기존값의 1.5배 ) transaction_auditing=false workarea_size_policy=auto (==> *_AREA_SIZE는필요없음 ) optimizer_dynamic_sampling=(1(=>9i), 2(=>10g) skip_unusable_indexes=true (10g Only) statistics_level=typical (9i,10g) 1회성 Literal SQL유형전면수정 (Bind변수로공유화 ) ==> PRO*C의 Method 2,3,4를사용. 비효율적인 SQL에대해 Tuning. 실행빈도가높은 SQL은가능한더정교하게 Tuning (V$SQL의 Execution의역순에의해확인 ) V$SORT_USAGE를통해 TEMP과다발행 SQL은 Tuning Row Chaining% 가높은 Table은신시스템구축시 PCTFREE를기존보다크게준다. HASH Join등을적극활용 4
신기능및새로운 SQL 기법적용. (SQL 1999, GROUPING SET 등 ) LOOP Query 는가능한줄일수있는방법으로변경 계산용도의 SQL 제거. APP단에서처리 불필요한 Function제거 불필요한 Hint제거. Hint는 Optimizer의판단을방해한다. PREFETCH, Bulk Binding,Bulk Collecting,Array Processing등적극활용 3. PRO*C Compile Option PRO*C의 PreCompile option의 Prefetch=100, release_cursor=no, hold_cursor=no를 Default로적용 ( 단관련 Module이 Bind변수화되어있다면 HOLD_CURSOR=YES로적용 ) DBMS_APPLICATION_INFO Package를이용한 SQL의실행 Source를확인할수있는체제구축 5
SQL Tuning 을위한 Guide SQL Tuning시주의점 1. 가능한 Hint는사용하지않는다. 1차적으로 Plan이원하는경우가아닐경우통계정보를확인해본다. DBA_TABLES,DBA_INDEXES,DBA_TAB_COLUMNS 확인, 최종 Analyze시간. 기타 Select 문을이용검토 Column 에대한통계정보 (Histogram) 는안돌리는것을원칙으로한다. Hint 를준다면가능한 Tight 하게주도록한다. 그렇지않을경우향후 plan 이변경될가능성이많기때문. 예 ) /*+ USE_NL(a b) */ ==> /*+ ORDERED USE_NL(a b)... */ Hint 는 Hint 의의미를정확히이해하고합당한 Hint 를주도록한다. 2. 통계정보는운영중에직접돌리지않는다. 집중적인운영시기에통계정보수집을위한실행은 Library Cache Contention 을유발 저녁시간의한가한시간을이용해서돌린다. ( 특히성능 TEST 시기등은조심 ) 3. WORKAREA_SIZE_POLICY=AUTO 이면 *_AREA_SIZE 는이용하지않으며, 설정해봐야의미가없다. 즉 Optimizer 는 *_AREA_SIZE 에의해 Plan 을결정하지않는다. 4. Tuning 기 Plan 적인 Tuning 뿐만아니라구조적인 Tuning 에도집중한다. Execution 이높은것. Loop Query 보완, 최적화 1 회성 (Literal 을사용 ) 비공유 SQL. 특히집중적으로실행되는 SQL 5. Tuning 시 Plan 은상수 (Literal) 로 TEST 하지만실제는 bind 변수로운영되는경우 Plan 이다를수있다. Program 에 bind 변수로되어있다면 bind 변수로 Plan 을확인해봐야한다. 6. 다음의사항도고려한다. Hash Join 을사용할경우 Driving 순서, Row Set 을고려하여사용한다. 6
Chaining % 비율을항상검토하고 Row Chaining 비율이높은 table 에대해서는 Column 의 Data Type 및 Block 의 PCTFREE 등을검토하여, Table 의구조적인문제, 또는업무적인형태를고려하여 REORG 를권장한다. 평균 Row 길이와 Block 당 Row 수도항상주의깊게관찰하여문제점이없는지검토한다. Hash Join 과 Sort Merge Join 시 TEMP 쪽에 I/O 가발생하지않도록한다. PRO*C Application 일경우 BIND 변수의사용여부,RELEASE_CURSOR=NO, PREFETCH=1000(batch), PREFETCH=100(OLTP) 를권장한다. PL/SQL 의 Batch Job 일경우 Bulk Binding, Bulk Collecting 을이용하도록유도한다. Execution Plan 보기 Needs the plan_table table utlxplan.sql PLAN_TABLE 의추가된 COLUMN CPU_COST, IO_COST TEMP_SPACE ACCESS_PREDICATES,FILTER_PREDICATES :(9iR2) Access Path 정보를이용하여 Index 의적절한설정검증에효과적 SQL 을실행하지않고 Trace 만보는방법 EXPLAIN PLAN, SET AUTOTRACE TRACEONLY EXPLAIN 이용 (>= 9i R2) 의향상된기능 :utlxpls.sql(serial) or utlxplp.sql(parallel) SQL> explain plan for 2 select * from emp e, dept d 3 where e.deptno = d.deptno and 4 d.deptno = 10; SQL> select * from table(dbms_xplan.display); SQL> SET AUTOTRACE Traceonly Explain Execution Plan 보기실행예 (9i) SQL> explain plan for 7
2 select * from emp e, dept d where e.deptno = d.deptno and d.deptno = 10; Explained. SQL> select * from table(dbms_xplan.display); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------- ---------------------------------------------------------------------------- Id Operation Name Rows Bytes Cost ---------------------------------------------------------------------------- 0 SELECT STATEMENT 4 248 3 1 NESTED LOOPS 4 248 3 2 TABLE ACCESS BY INDEX ROWID DEPT 1 30 1 * 3 INDEX RANGE SCAN PK_DEPT 1 1 * 4 TABLE ACCESS FULL EMP 5 160 2 ---------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("d"."deptno"=10) 4 - filter("e"."deptno"=10) Note: cpu costing is off =============================================================================== SQL> set autot traceonly explain SQL> SELECT * FROM emp e, dept@scott_9ir2 d 2 where e.deptno = d.deptno and d.deptno = 10 3 order by ename; Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=6 Card=4 Bytes=248) 1 0 SORT (ORDER BY) (Cost=6 Card=4 Bytes=248) 2 1 NESTED LOOPS (Cost=3 Card=4 Bytes=248) 3 2 REMOTE* (Cost=1 Card=1 Bytes=30) SCOTT_9IR2.US.ORACLE.COM 4 2 TABLE ACCESS (FULL) OF 'EMP' (Cost=2 Card=5 Bytes=160) 3 SERIAL_FROM_REMOTE SELECT "DEPTNO","DNAME","LOC" FROM "DEPT" "D " WHERE "DEPTNO"=10 Instance level 에동적으로 SQL_TRACE Enable/Disable At the instance level: Init.ora SQL_TRACE = {True False} SQL> ALTER SYSTEM SET EVENTS 2 10046 trace name context forever,level {1 4 8 12} ; At the Session SQL> ALTER SESSION SET 2 SQL_TRACE = {True False}; SQL> EXECUTE dbms_session.set_sql_trace 2 ({True False}); SQL> EXECUTE 2 dbms_system.set_sql_trace_in_session 3 (session_id, serial_id, {True False}); 8
SQL> oradebug setospid <OS PID> SQL> oradebug event 10046 trace name context forever, level 1 9iR2 이상의 TKPROF 의향상된기능 10046 Trace level 에따라 Wait(level 8, level 12 일경우 ) 정보도표시 각 Row Source (Plan 상의 STEP) 마다 Statistics 표시 9i 에서는 time=xxxxxxxxxx 정보가 1/1000000 초단위. 8i 까지는 1/100 초 Run Time Plan & TKPROF 실행시 Plan 주의 TKPROF. EXPLAIN=xxxx/yyyy 일경우 Plan 이 2 개 (RUN & Tkprof) Rows Row Source Operation ------- --------------------------------------------------- 3 MERGE JOIN (cr=20 r=8 w=0 time=61591 us) 1 SORT JOIN (cr=10 r=8 w=0 time=60764 us) 1 TABLE ACCESS FULL DEPT (cr=10 r=8 w=0 time=60443 us) 3 SORT JOIN (cr=10 r=0 w=0 time=720 us) 14 TABLE ACCESS FULL EMP (cr=10 r=0 w=0 time=472 us) Elapsed times include waiting on following events: Event waited on Times Max. Wait Total Waited ---------------------------------------- Waited ---------- ------- SQL*Net message to client 2 0.00 0.00 global cache cr request 8 0.00 0.00 db file sequential read 3 0.02 0.03 db file scattered read 1 0.00 0.00 SQL*Net message from client 2 7.96 7.96 row cache lock 2 0.00 0.00 select * from emp e,dept d where d.deptno = e.deptno and d.deptno = 10 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.03 0.09 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 2 0.01 0.06 8 20 0 3 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 4 0.04 0.15 8 20 0 3 Misses in library cache during parse: 1 Optimizer goal: RULE Parsing user id: 60 (SCOTT) Rows Row Source Operation ------- --------------------------------------------------- 3 MERGE JOIN (cr=20 r=8 w=0 time=61591 us) 1 SORT JOIN (cr=10 r=8 w=0 time=60764 us) 1 TABLE ACCESS FULL DEPT (cr=10 r=8 w=0 time=60443 us) 9
3 SORT JOIN (cr=10 r=0 w=0 time=720 us) 14 TABLE ACCESS FULL EMP (cr=10 r=0 w=0 time=472 us) Rows Execution Plan ------- --------------------------------------------------- 0 SELECT STATEMENT GOAL: RULE 3 MERGE JOIN 1 SORT (JOIN) 1 TABLE ACCESS (FULL) OF 'DEPT' 3 SORT (JOIN) 14 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'EMP' Elapsed times include waiting on following events: Event waited on Times Max. Wait Total Waited ---------------------------------------- Waited ---------- ------------ SQL*Net message to client 2 0.00 0.00 global cache cr request 8 0.00 0.00 db file sequential read 3 0.02 0.03 db file scattered read 1 0.00 0.00 SQL*Net message from client 2 7.96 7.96 row cache lock 2 0.00 0.00 Cached Execution Plan(V$SQL_PLAN) v$sql_plan dynamic performance view 실제 Run Time 시실행된 plan 정보 PLAN_TABLE 과항목이거의같다. SELECT hash_value, (select sql_text from v$sql s where s.hash_value = p.hash_value and s.address = p.address and rownum <= 1), child_number,id,parent_id, LPAD(' ',2*(depth)) OPERATION DECODE(OTHER_TAG,NULL,'','*') DECODE(OPTIONS,NULL,'',' (' OPTIONS ')') DECODE(OBJECT_NAME,NULL,'',' OF ''' OBJECT_NAME '''') DECODE(OBJECT#,NULL,'','(Obj#' TO_CHAR(OBJECT#) ')') DECODE(ID,0,DECODE(OPTIMIZER,NULL,'',' Optimizer=' OPTIMIZER)) DECODE(COST,NULL,'',' (Cost=' COST DECODE(CARDINALITY,NULL,'',' Card=' CARDINALITY) DECODE(BYTES,NULL,'',' Bytes=' BYTES) ')') SQLPLAN,OBJECT_NODE, PARTITION_START,PARTITION_STOP, PARTITION_ID, CPU_COST, IO_COST, TEMP_SPACE, DISTRIBUTION, OTHER, ACCESS_PREDICATES, FILTER_PREDICATES FROM v$sql_plan p START WITH ID=0 and hash_value = xxxxxxxxxxxx CONNECT BY PRIOR ID=PARENT_ID AND PRIOR hash_value=hash_value AND PRIOR child_number=child_number ORDER BY hash_value,child_number,id,position Cached SQL Information (V$SQL) SQL 문장의실행시 CPU/Elapse Time 정보추가 기타 8i 보다추가된정보 Column Datatype Description 10
CPU_TIME NUMBER CPU time (in microseconds) used by this cursor for parsing/executing/fetching ELAPSED_TIME NUMBER Elapsed time (in microseconds) used by this cursor for parsing/executing/fetching OUTLINE_SID NUMBER Outline session identifier CHILD_ADDRESS RAW(4) Address of the child cursor SQLTYPE NUMBER Denotes the version of the SQL language used for this statement REMOTE VARCHAR2(1) (Y/N) Identifies whether the cursor is remote mapped DB link를사용한 SQL문장을찾을때유리 OBJECT_STATUS VARCHAR2(19) Status of the cursor (VALID/INVALID) LITERAL_HASH_VALUE NUMBER CURSOR_SHARING이사용되지않으면 0. 사용될경우 System에서상수가 Bind변수로바뀔때해당상수 Literal의 Hash Value SQL 문장에대하 hash value는 HASH_VALUE Column. LAST_LOAD_TIME VARCHAR2(19) last loaded time <--> FIRST_LOAD_TIME PLAN_HASH_VALUE NUMBER A numerical representation of the SQL plan for this cursor. Comparing one PLAN_HASH_VALUE to another easily identifies whether or not two plans are the same (rather than comparing the two plans line by line). Setting the Optimizer Mode 9i 에서 FIRST_ROWS_N Optimizer mode 추가되었음. (N: 1,10,100,1000) At the instance level: optimizer_mode = {Choose Rule First_rows First_rows_n All_rows} At the session level: (instance level 에우선 ) ALTER SESSION SET optimizer_mode = {Choose Rule First_rows First_rows_n All_rows} At the statement level: Using hints (Instance, Session level 에우선 ) OPTIMIZER_MODE=CHOOSE 일경우 통계정보가없다면기본적으로 RULE base(rbo) 로 Plan 이결정 RULE, DRIVING_SITE Hint 이외의 Hint 가왔다면 CBO 로결정 Parallel Degree, Partition Table, SAMPLE 절, 등이있으면무조건 CBO OPTIMIZER_MODE=First_rows First_rows_n All_rows 일경우 통계정보가없다면 Heuristics Value 를이용하여 CBO 로 Plan 이결정, PLAN 이비효율적일수있음 11
통계정보가있으나 Optimizer mode 가 RULE 일경우, 다른 hint 가오지않은경우와 Parallel Degree, Partition Table, SAMPLE 절등이나오지않은경우는 RBO 로처리 RULE Hint 와다른 Hint 가오는경우는 CBO 로처리.(Rule 규칙이위반되므로 ) Rule Base Ranking Path 1: Single Row by Rowid Path 2: Single Row by Cluster Join Path 3: Single Row by Hash Cluster Key with Unique or Primary Key Path 4: Single Row by Unique or Primary Key Path 5: Clustered Join Path 6: Hash Cluster Key Path 7: Indexed Cluster Key Path 8: Composite Index Path 9: Single-Column Indexes Path 10: Bounded Range Search on Indexed Columns Path 11: Unbounded Range Search on Indexed Columns Path 12: Sort-Merge Join Path 13: MAX or MIN of Indexed Column Path 14: ORDER BY on Indexed Column Path 15: Full Table Scan *** Path 8,9,10 주의예를들면, 'emp' Table에 'A' Index가 "deptno" 로구성되어있고, 'B' Index가 "deptno + empno" 로구성되어있다면다음과같은 SQL문장의경우는 'A' index를사용. 그럼 (A) 와 (B) 의 Ranking은. (A) ==> Rank 9, (B) ==> Rank 10 조건 select /*+ rule */ * from emp where deptno = 10 and empno between 7888 and 8888; -------------------------+------------------ -----^----- ^ (A) (B) Optimizer Basics (Features that Require the CBO) Partitioned tables (*) Index-organized tables Reverse key indexes Function-based indexes SAMPLE clauses in a SELECT statement (*) Parallel execution and parallel DML Star transformations Star joins Extensible optimizer Query rewrite (materialized views) Progress meter Hash joins Bitmap indexes Partition views (release 7.3) Hint (*) 12
Parallel DEGREE & INSTANCES DEFAULT 도해당 (*) Default Heuristics Value /* Default selectivities are set low to 1. keep cost values low for future resource limiter use 2. keep cost values low for permutation cutoff in kko Defaults are used for bind variables, general expressions and unanalyzed tables, except for equality where defaults are not needed for bind variables. */ #define KKEDSREL 0.05 /* default selectivity for < <= > >= */ #define KKEDSEQ 0.01 /* default selectivity for = */ #define KKEDSNE 0.05 /* default selectivity for!= */ #define KKEDSDF 0.05 /* default selectivity for all other ops */ #define KKEDSIRL 0.009 /* default selectivity for relation on indexed col */ #define KKEDSBRL 0.009 /* def sel for relation with bind var on index col*/ #define KKEDSIEQ 0.004 /* default selectivity for = on indexed col */ #define KKEDHALF 0.5 /* default selectivity for binary preds,like grouping() */ #define KKEDSMAX 1.00 /* default selectivity for predicates with no filter*/ #define KKEDMBR 8 /* default multiblock read factor */ #define KKEDMBW 8 /* default multiblock write factor */ #define KKEDFNR 100.0 /* default - fixed table cardinality */ #define KKEDFRL 20 /* default - fixed table row length */ #define KKEDDNR 2000.0 /* default - remote table cardinality */ #define KKEDDRL 100 /* default - remote table avg row length */ #define KKEDDNB 100 /* default - default # of blocks */ #define KKEDDSC 13.0 /* default - default scan cost */ #define KKEDILV 1 /* default - default index levels */ #define KKEDILB 25 /* default - number of index leaf blocks */ #define KKEDLBK 1 /* default - number leaf blocks/key */ #define KKEDDBK 1 /* default - number of data blocks/key */ #define KKEDKEY 100 /* default - number of distinct keys */ #define KKEDCLF (KKEDDNB*8) /* default - clustering factor */ CBO Optimizer 가참조하는 Parameter (9iR2) 다음의 Parameter 는변경시 Optimizer 에게영향을주므로신중해야한다. OPTIMIZER_FEATURES_ENABLE = 9.2.0 OPTIMIZER_MODE/GOAL = Choose _OPTIMIZER_PERCENT_PARALLEL(Hidden전환) = 101 HASH_AREA_SIZE = 4096000 HASH_JOIN_ENABLED = TRUE HASH_MULTIBLOCK_IO_COUNT = 0 SORT_AREA_SIZE = 2048000 OPTIMIZER_SEARCH_LIMIT = 5 PARTITION_VIEW_ENABLED = FALSE _ALWAYS_STAR_TRANSFORMATION = FALSE _B_TREE_BITMAP_PLANS = TRUE STAR_TRANSFORMATION_ENABLED = FALSE _COMPLEX_VIEW_MERGING = TRUE _PUSH_JOIN_PREDICATE = TRUE PARALLEL_BROADCAST_ENABLED = TRUE OPTIMIZER_MAX_PERMUTATIONS = 2000 OPTIMIZER_INDEX_CACHING = 0 _SYSTEM_INDEX_CACHING = 0 OPTIMIZER_INDEX_COST_ADJ = 100 OPTIMIZER_DYNAMIC_SAMPLING = 1 _OPTIMIZER_DYN_SMP_BLKS = 32 QUERY_REWRITE_ENABLED = FALSE QUERY_REWRITE_INTEGRITY = ENFORCED _INDEX_JOIN_ENABLED = TRUE _SORT_ELIMINATION_COST_RATIO = 0 _OR_EXPAND_NVL_PREDICATE = TRUE _NEW_INITIAL_JOIN_ORDERS = TRUE ALWAYS_ANTI_JOIN = CHOOSE ALWAYS_SEMI_JOIN = CHOOSE _OPTIMIZER_MODE_FORCE = TRUE 13
_OPTIMIZER_UNDO_CHANGES = FALSE _UNNEST_SUBQUERY = TRUE _PUSH_JOIN_UNION_VIEW = TRUE _FAST_FULL_SCAN_ENABLED = TRUE _OPTIM_ENHANCE_NNULL_DETECTION = TRUE _ORDERED_NESTED_LOOP = TRUE _NESTED_LOOP_FUDGE = 100 _NO_OR_EXPANSION = FALSE _QUERY_COST_REWRITE = TRUE QUERY_REWRITE_EXPRESSION = TRUE _IMPROVED_ROW_LENGTH_ENABLED = TRUE _USE_NOSEGMENT_INDEXES = FALSE _ENABLE_TYPE_DEP_SELECTIVITY = TRUE _IMPROVED_OUTERJOIN_CARD = TRUE _OPTIMIZER_ADJUST_FOR_NULLS = TRUE _OPTIMIZER_CHOOSE_PERMUTATION = 0 _USE_COLUMN_STATS_FOR_FUNCTION = TRUE _SUBQUERY_PRUNING_ENABLED = TRUE _SUBQUERY_PRUNING_REDUCTION_FACTOR = 50 _SUBQUERY_PRUNING_COST_FACTOR = 20 _LIKE_WITH_BIND_AS_EQUALITY = FALSE _TABLE_SCAN_COST_PLUS_ONE = TRUE _SORTMERGE_INEQUALITY_JOIN_OFF = FALSE _DEFAULT_NON_EQUALITY_SEL_CHECK = TRUE _ONESIDE_COLSTAT_FOR_EQUIJOINS = TRUE _OPTIMIZER_COST_MODEL = CHOOSE _GSETS_ALWAYS_USE_TEMPTABLES = FALSE DB_FILE_MULTIBLOCK_READ_COUNT = 16 _NEW_SORT_COST_ESTIMATE = TRUE _GS_ANTI_SEMI_JOIN_ALLOWED = TRUE _CPU_TO_IO = 0 _PRED_MOVE_AROUND = TRUE Dynamic Sampling 더낳은 Plan 을경정하기위한목적으로더정확한 Selectivity & Cardinality 를구하기위한방법. 추가적인 Recursive SQL 발생. 전체 Query실행시간대비적은 Sampling 시간일경우사용더정확한 single-table predicate selectivities를확인하기위해서 10053 trace와병행해서사용통계정보가없거나너무오래된경우 table cardinality를예측해볼경우 Table Level로지정하지않고 SQL문장단위로지정 How Dynamic Sampling Works OPTIMIZER_DYNAMIC_SAMPLING= 0 ~ 10(init.ora), DYNAMIC_SAMPLING(0 ~ 10) Hint When to Use Dynamic Sampling A better plan can be found using dynamic sampling. The sampling time is a small fraction of total execution time for the query. The query will be executed many times. How to Use Dynamic Sampling to Improve Performance OPTIMIZER_DYNAMIC_SAMPLING = 0 : dynamic sampling disable. (9.0.x default) OPTIMIZER_DYNAMIC_SAMPLING = 1 (9i R2 default) 다음의조건이모두만족할경우 Sampling Query 에 1 개또는그이상의 Table 이왔을경우 일부 Table 이 Index 가없고통계정보가없을경우 14
통계정보가없는 Table 이상대적으로고비용의 Table 일것으로 Optimizer 가판단한경우 OPTIMIZER_DYNAMIC_SAMPLING >1 (~ 10): more aggressive application of dynamic sampling (analyzed or unanalyzed) & Sampling 할 I/O 의 level 을결정 >>> DYNAMIC_SAMPLING Hint 의 10053 TRACE QUERY select /*+ dynamic_sampling(7) */ deptno from emp where sal *5/8>300.... *** 2003-05-28 18:06:58.000 ** Performing dynamic sampling initial checks. ** ** Dynamic sampling initial checks returning TRUE (level = 7). *** 2003-05-28 18:06:58.000 ** Generated dynamic sampling query: query text : SELECT /*+ ALL_ROWS IGNORE_WHERE_CLAUSE */ NVL(SUM(C1),0), NVL(SUM(C2),0) FROM (SELECT /*+ IGNORE_WHERE_CLAUSE NOPARALLEL("EMP") */ 1 AS C1, CASE WHEN "EMP"."SAL"*5/8>300 THEN 1 ELSE 0 END AS C2 FROM "EMP" "EMP") SAMPLESUB *** 2003-05-28 18:06:58.000 ** Executed dynamic sampling query: level : 7 sample pct. : 100.000000 actual sample size : 14 filtered sample card. : 14 orig. card. : 14 block cnt. : 1 max. sample block cnt. : 256 sample block cnt. : 1 <<<<<<<< _OPTIMIZER_DYN_SMP_BLKS 와 OPTIMIZER_DYNAMIC_SAMPLING의 level에의해 Sampling Block수가결정됨 min. sel. est. : 0.0500 ** Using dynamic sel. est. : 1.00000000 TABLE: EMP ORIG CDN: 14 ROUNDED CDN: 14 CMPTD CDN: 14 Access path: tsc Resc: 2 Resp: 2 BEST_CST: 2.00 PATH: 2 Degree: 1 Using System Statistics (>= 9i) System statistics enable the CBO to use CPU and I/O characteristics. System statistics must be gathered on a regular basis; this does not invalidate cached plans. Gathering system statistics equals analyzing system activity for a specified period of time. import_system_stats으로업무유형별 dictionary에반영 Procedures of the dbms_stats package used to collect system statistics: gather_system_stats,set_system_stats,get_system_stats Automatic gathering Collect statistics for OLTP: 15
SQL> EXECUTE dbms_stats.gather_system_stats - 2 (interval => 120, stattab => 'mystats', statid => 'OLTP'); Collect statistics for OLAP: SQL> EXECUTE dbms_stats.gather_system_stats - 2 (interval => 120, stattab => 'mystats', statid => 'OLAP'); Manual Gathering (start/stop) SQL> EXECUTE dbms_stats.gather_system_stats(gathering_mode => 'START'); SQL> EXECUTE dbms_stats.gather_system_stats (gathering_mode => 'STOP'); SQL> EXECUTE dbms_stats.gather_system_stats - > (gathering_mode => 'START'); PL/SQL procedure successfully completed. SQL> SQL> select * from aux_stats$ ; SNAME PNAME PVAL1 PVAL2 ------------------------------ ------------------------------ ---------- ------------------- SYSSTATS_INFO STATUS MANUALGATHERING SYSSTATS_INFO DSTART 05-29-2003 17:08 SYSSTATS_INFO DSTOP 05-29-2003 17:08 SYSSTATS_INFO FLAGS 1 SYSSTATS_TEMP SBLKRDS 1044 SYSSTATS_TEMP SBLKRDTIM 9000 SYSSTATS_TEMP MBLKRDS 205 SYSSTATS_TEMP MBLKRDTIM 2740 SYSSTATS_TEMP CPUCYCLES 285852 SYSSTATS_TEMP CPUTIM 2095618 SYSSTATS_TEMP JOB 0 SYSSTATS_TEMP MBRTOTAL 3067 12 rows selected. SQL> select * from table(dbms_xplan.display); PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------- -------------------------------------------------------------------- Id Operation Name Rows Bytes Cost -------------------------------------------------------------------- 0 SELECT STATEMENT 24591 768K 43 * 1 TABLE ACCESS FULL TESTEMP10 24591 768K 43 -------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("testemp10"."deptno"=10) Note: cpu costing is off <<<<<<<<<< System Stat을 STOP하기전까지는 사용안함 14 rows selected. SQL> EXECUTE dbms_stats.gather_system_stats - > (gathering_mode => 'STOP'); PL/SQL procedure successfully completed. SQL> explain plan for 2 select * from testemp10 where deptno = 10; Explained. 16
SQL> select * from table(dbms_xplan.display); PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------- ------------------------------------------------------------------------- Id Operation Name Rows Bytes Cost (%CPU) ------------------------------------------------------------------------- 0 SELECT STATEMENT 24591 768K 52 (18) * 1 TABLE ACCESS FULL TESTEMP10 24591 768K 52 (18) ------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("testemp10"."deptno"=10) 17
Optimizer 의통계정보 (Analyzer 정보 ) 를위한권장안 Optimizer의통계정보운영 Guide Oracle의 Optimizer는크게 2가지로나뉘며, CBO(CHOOSE, ALL_ROWS, FIRST_ROWS) 와 RBO(Rule Base Optimizer) 로나뉘게된다. 그러나 RBO에서는 Index가있다면대부분의 SQL문장이범위에관계없이 Index를타게되며, Hash Join, Partition Table, Parallel Processing등은 CBO에서만가능하다. 그리고향후로도 RBO에대한추가적인기능향상은없는상태이다. 그러므로 OPTIMIZER Mode 는 CHOOSE 로운영하도록하며, OLTP 의 Index 의이용효율을높이기 위해, Statistics Management, Stored outline, init.ora 의 parameter(optimizer_index_caching, optimizer_index_cost_adj) 를이용해운영하도록권장한다.(SQL 문마다검증필요 ) 1. Analyze 보다는 DBMS_STATS Package 를이용한다. 2. SYS, SYSTEM 등 Setup 시설정된 User 들에대해서는통계정보를만들지않는다. 3. Database Level, 또는 Schema level 보다는 Table Level 로 DBMS_STATS.GATHER_TABLE_STATS 을이용해수집한다. 4. Column 에대한통계정보는 Where 절에서참조되는 Column 위주로만든다. 또한 Column 에대해서 Histogram 은만들지않는것을기본으로하며한다. 그러므로 WHERE 절에참조되면서 Indexed Column 위주로통계정보 (for all indexed column) 를만들며, Column 에대한분포도가편향되어있는경우는 Bucket Size 를적절히주어 Histogram 정보를만든다. 5. 통계정보를만들기전에기존의통계정보는 EXPORT 로다른 User 의 Schema 로 backup 을받아둔다. 6. GATHER_TABLE_STATS 을이용하되 Size 가큰 Table 에대해서는 estimate_percent 기능을이용해 5% 이하로 Sampling 한다. 5% 이하로돌려도정확도는높다. 가능한 Block Sampling 방식은사용하지않는다. (estimate_percent 는소수점까지줄수있다. 예 : 0.001) 7. 처음은전체적으로모든 Table, Index 에대해서통계정보를수집하나, 초기화이후는자주변경되는 Table (Insert/Update/Delete) 에대해서만통계정보를수집한다. 8. 자동화된 Script 를구성하고 1 주일단위 / 1 개월단위 / 분기 / 반기단위로구분하여통계정보를수집한다. 9. 통계정보가수집된이후모든 Application 에대해서 Plan 의검증이필요하다. 10. 필요에의해 DBMS_STATS 를이용해통계정보를변경해원하는 Plan 을만들어낼수있다. 먼저 TEST 장비에서확인후 Production 에반영한다. 18
11. Plan 의변화가실제어떤영향으로반응할지확인하기위해서는통계정보를 Dictionary 에직접만들지않고일반 User Schema 에만들어 TEST 장비에반영후검증후 Production 에반영한다. 12. 일반적인통계정보를확인목적으로도사용할수있다. (Block 당 Row 수계산, Row 수검증, Index Clustering Factor 확인등 ) Optimizer Statistics정보란? Optimizer Statistics정보는 Oracle Dictionary에관리되며, CBO(Cost Base Optimizer) 에서사용될통계정보를말한다. 즉 CBO는 Oracle Dictionary내의 Statistics정보 (Row수, Block수, Distinct값, Column의분포도,Index Clustering Factor등 ) 를이용해서어떠한 Access Path가최적인지를결정하게되고, 또한어떠한 Join Method를이용하는것이현재의환경에서최적인지를결정하게된다. 또한 CBO는현재의 Dictionary값을이용하므로이값이현실 Data와같지않을경우 Plan이엉뚱하게풀리게되는결과를가져온다. 또한이런점을이용하여 Statistics정보를 Managing하여원하는 Plan으로처리되도록할수있다. 통계정보를만드는 Analyze명령, DBMS_STATS Package는 CBO에서사용할통계정보를만든다. 통계정보를만든 Table 들을 SQL 에서사용될경우 Optimizer Mode 가 RULE 인경우를제외하고 모든 SQL 은 CBO 로처리된다.(CHOOSE,ALL_ROWS,FIRST_ROWS) ALL_ROWS,FIRST_ROWS Optimizer Mode 는통계정보의유무에관계없이무조건 CBO 로처 리된다. 이경우통계정보가없다면 Heuristics Value 를가지고예상통계정보를만들어낸다. 다음의경우를처리하기위해서는반드시 CBO 이거나, 또는 CBO 가아니면고려되지않는다. Features that Require the CBO Partitioned tables Index-organized tables (IOT) Reverse key indexes Function-based indexes SAMPLE clauses in a SELECT statement Parallel execution and parallel DML (Table에 Degree가설정된경우 ) Star transformations Star joins Extensible optimizer Query rewrite (materialized views) Progress meter Hash joins Bitmap indexes Partition views (release 7.3) Hint 19
CBO 의경우 Rule Base 보다 Plan 을작성하는데시간이많이걸릴수가있다. 모든조건의 Plan 을 통계정보를이용해고려해야하기때문이다. 다음은 Analyze 를운영할경우 Oracle Dictionary 에반영되는 Column 들이며, 아래와같은값들을 가지고있다. DBA_TABLES NUM_ROWS,BLOCKS,EMPTY_BLOCKS,AVG_SPACE,CHAIN_CNT, AVG_ROW_LEN,AVG_SPACE_FREELIST_BLOCKS,NUM_FREELIST_BL OCKS, SAMPLE_SIZE,LAST_ANALYZED DBA_INDEXES BLEVEL,LEAF_BLOCKS,DISTINCT_ KEYS,AVG_LEAF_BLOCKS_PER_KEY, AVG_DATA_BLOCKS_PER_KEY,CL USTERING_FACTOR,NUM_ROWS,SAMPLE_SI ZE, LAST_ANALYZED DBA_TAB_COLUMNS NUM_ DISTINCT,L OW_VALUE,HIGH_VALUE,DENSITY,NUM_NULLS,NUM_BUCKET S, LAST_ANALYZED,SAMPLE_SIZE,AVG_COL_LEN DBA_TAB_HISTOGRAMS TABLE_ NAME,COLUMN_ NAME,ENDPOINT_ NUMBER,ENDPOINT_VALUE, ENDPOINT_ACTUAL_VALUE Analyze 는 Table 과 Index 에대해서만실행한다. 또한 Indexed Column 에대해서실행한다. Analyze table [TABLE_NAME] compute statistics for table for indexes for all indexed columns; 분포도가일정하지않은 Column 에대해서만 Column 의 Histogram 을작성한다. 여기서 Bucket Size 는 Distinct 값을고려해지정한다. 가능한 Distinct Value 정도. Analyze table [TABLE_NAME] compute statistics for column [COLUMN_NAME] size [BUCTET_SIZE] for column [COLUMN_NAME] size [BUCTET_SIZE]..; 삭제할경우는다음과같다. Option 을주지않으면 Table,Index,Column 의통계정보가모두삭제 된다. Analyze table [TABLE_NAME] delete statistics; 20
Analyzer 와 DBMS_STATS 의차이점 Analyze Command 에만있는기능 Structural Integrity Check 기능 analyze { index/table/cluster } (schema.){ index/table/cluster } validate structure (cascade) (into schema.table); Chained Rows 수집기능 ANALYZE TABLE order_hist LIST CHAINED ROWS INTO <user_tab>; Analyze Command & DBMS_STATS 의차이점 Analyze 는 Serial Statistics Gathering 기능만있다. DBMS_STATS 은 parallel Gathering 기능이있다.(Index 는 parallel 불가 ) Analyze 는 Partition 의 Statistics 를각 Partition table 과 Index 에대해서수집하고, Global Statistics 는 Partition 정보를가지고계산하므로, 비정확할수있다. 그러므로 DBMS_STATS 사용권장. DBMS_STATS 은전체 Cluster 에대해서는 Statistics 를수집하지않는다. DBMS_STATS 은 CBO 와관련된 Statistics 정보만을수집한다. 즉 EMPTY_BLOCKS,AVG_SPACE,CHAIN_CNT, 등은수집되지않는다. DBMS_STATS 은 user 의 Statistics table 에수집된 Statistics 를저장할수있고, Dictionary 로각 Column,Table,Index,Schema 등을반영할수있다. DBMS_STATS 은 IMPORT/EXPORT 기능및추가적인기능이많다.(manual 참조 ) DBMS_STATS Package 의사용법 각각의 Statistics 을 Set 또는 get 을할수있는기능이있다. Dictionary 와일반 User 의 Schema 로 statistics 정보를 Import/Export 할수있는기능이있다. Optimizer 가필요한 statistics 정보만수집한다. dbms_stats.gather_table_stats ('SCOTT' -- schema,'emp' -- table, NULL -- partition 21
, 20 -- sample size(%), FALSE -- block sample?,'for ALL COLUMNS' -- column spec, 4 -- degree of //,'DEFAULT' -- granularity, TRUE -- cascade to indexes ); Procedure GATHER_ INDEX_STATS GATHER_TABLE_STATS GATHER_SCHEMA_STATS GATHER_DATABASE_STATS Description Collects index statistics Collects table, column, and index statistics Collects statistics for all objects in a schema Collects statistics for all objects in a database DBMS_STATS 는 CLUSTER에대해서는통계정보를만들지않으므로 CLUSTER로구성된각각의 Table에대해서통계정보를만들어야한다. Table에대한통계정보의수집은 parallel 또는 Serial로수집할수있으나, Index는 Serial만가능하다. estimate statistics방법은 block samples 방법과 row samples방법이있는데 Block에값들이편향되어있다면 Row Samples방법을사용하여야한다. 속도면에서는 Block Sample방식이빠르다. GATHER_TABLE_STATS 을이용할경우 Column에대한통계수집 Option을지정할수있다. GATHER_TABLE_STATS 의 CASCADE option을사용하여 index statistics도동시에수집할수있다. Analyze & DBMS_STATS 의예 analyze table testemp compute statistics; table, 모든 index, 모든 Column 에대한 Statistics 정보수집.Column 의 Histogram 수집 Histogram 의 size 가 1 로된다. Histogram 의의미가없다. 즉 MIN,MAX 값으로된다. analyze table testemp delete statistics; table, 모든 index, 모든 Column 에대한 Statistics 과 Column 의 Histogram 정보모두삭제된다. 22
analyze table testemp compute statistics for table; table에대해서만 Statistics정보수집. analyze index testemp_idx01 compute statistics; index에대해서만 Statistics정보수집. analyze table testemp compute statistics for columns empno; 지정된 Column에대해서만 Statistics정보수집. Column의 Histogram도수집된다. Histogram의 size가 Default 75로된다. Column의 Distinct값보다 Bucket값이크면, Distinct의개수만큼 Bucket을만든다. analyze table testemp compute statistics for table for all indexed columns; table, 모든index, 모든Column에대한 Statistics정보수집. Column의 Histogram도 Default Bucket인 75를사용한다. BEGIN DBMS_STATS.GATHER_TABLE_STATS ('scott', 'testemp'); END; / table, 모든 Column 에대한 Statistics 정보수집.(Index 제외 ) Column 의 Histogram 수집.(Size 1) EMPTY_BLOCKS,AVG_SPACE,CHAIN_CNT 등은수집되지않는다.(Analyze 와다른점 ) BEGIN DBMS_STATS.GATHER_TABLE_STATS ('scott', 'testemp', method_opt => 'for all columns', cascade => TRUE); END; / table, 모든 index, 모든 Column 에대한 Statistics 정보수집.Column 의 Histogram 수집. Column 의 Histogram 도 Default Bucket 인 75 를사용한다. 23
Statistics 의 Maintenance Copy Statistics Between Databases Data dictionary 2 Copy from DD to user table User-defined statistics table 1 Export and import user table 3 User-defined statistics table Copy from user table to DD 4 Data dictionary DBMS_STATS package 를이용하여 Production 에서 TEST 장비로반영하여, TEST 장비에서도 Production 과같은 Plan 이생성될수있도록하는데유용하다. 1. DBMS_STATS.CREATE_STAT_TABLE procedure 를이용하여 Production 내에사용자정의의 statistics table 을만든다. 2. DBMS_STATS.EXPORT_SCHEMA_STATS procedure 를이용하여 production 내의 dictionary 내의통계정보를사용자정의의 statistics table 로 EXPORT 한다. 3. Oracle 의 export 와 import utilities 를이용하여사용자정의의 statistics table 을 TEST 장비로 Import 한다. 4. DBMS_STATS.IMPORT_SCHEMA_STATS procedure 를이용하여 TEST database 내의 Dictionary 로 Statistics 정보를 IMPORT 한다. 도한 DBMS_STATS 을이용해기존의 Statistics 정보를 backup 받는용도로도사용한다. 만일 Plan 이원하지않는형태로작성된다면다시 backup 받은 Statistics 정보를 IMPORT 할수있다. begin dbms_stats.create_stat_table('scott','scott_stat','users'); end; / begin 24
dbms_stats.export_schema_stats('scott','scott_stat',1,'scott'); end; / select * from scott_stat where c1 like 'TESTEMP%'; STA TID TYPVERSIFLA C C N N E ON GS C1 2 3 C4 C5 N1 N2 3 N4 N5 N6 N7 N8 9 SCO 179 179 1T 4 0TESTEMP TT 2 22 40 2 TESTEMP SCO 179 179 1I 4 0_IDX01 TT 2 8 14 1 22 308 1 2 SCO 179128 1C 4 0TESTEMP COMM TT 4 0.25 4 2 0 0 1400 2 DEPT SCO 0.33333 179 1C 4 0TESTEMP NO TT 3 3333 3 2 0 10 30 2 EMPN SCO 0.07142 179 1C 4 0TESTEMP O TT 14 8571 14 2 0 7369 7934 3 ENAM SCO 0.07142 179 3.38884 4.53055 1C 4 0TESTEMP E TT 14 8571 14 2 0 E+35 E+35 5 HIRED SCO 0.07692 179 1C 4 0TESTEMP ATE TT 13 3077 13 2 0 2444591 2445347 7 SCO 179 3.39086 4.32285 1C 4 0TESTEMP JOB TT 5 0.2 5 2 0 E+35 E+35 7 SCO 0.16666 179 1C 4 0TESTEMP MGR TT 6 6667 6 2128 7566 7902 3 1C 4 0TESTEMP SAL SCO TT 12 0.08333 3333 12 179 2 0 800 5000 3 N1 0 N1 N1 1 2 D1 R1 R2 2001-09- 21 17:50 2001-09- 21 17:50 2001-09- 21 17:50? 2001-09- 21 17:50?? 2001-09- 21 17:50헖F 헠 # 2001-09-ADAM 21 17:50S WARD 2001-09- 21 17:50w? w? 2001-09-ANAL SALES 21 17:50YST MAN 2001-09- 21 17:50헚C 헠 2001-09- 21 17:50?? CH 1 DBMS_STATS 를이용한 Statistics 운영예 Procedure CREATE_STAT_TABLE DROP_STAT_TABLE EXPORT_object_STATS IMPORT_object_STATS Description Creates a user-defined table capable of holding statistics Drops a user-defined statistics table Exports statistics from the data dictionary to a user-defined table Imports statistics from a user-defined Table to the data dictionary object 는 COLUMN, INDEX, TABLE, or SCHEMA 이될수있다. Copying Statistics dbms_stats.create_stat_table ('SST' -- schema,'stats' -- statistics table name,'users' -- tablespace ); 25
dbms_stats.export_table_stats ('SST' -- schema,'courses' -- table name, NULL -- no partitions,'stats' -- statistics table name,'crs demo' -- id for statistics, TRUE -- index statistics ); Gathering Statistics begin dbms_stats.create_stat_table ('SST', 'STATS'); dbms_stats.gather_table_stats ('SST', 'COURSES',stattab => 'STATS'); end; begin dbms_stats.delete_table_stats ('SST', 'COURSES'); dbms_stats.import_table_stats ('SST', 'COURSES',stattab => 'STATS'); end; Setting Column Statistics declare srec dbms_stats.statrec; pt dbms_stats.numarray := dbms_stats.numarray(1,15); begin srec.epc := 2; -- two end points, no values between srec.bkvals := null; dbms_stats.prepare_column_values(srec, pt); dbms_stats.set_column_stats ( ownname => 'sst', tabname => 'employees', colname => 'salary', distcnt => 20 -- distinct values, srec => srec); end; 26
Plan Stability(Stored Outline) 특정 SQL 문장이들어오면, 모든 SQL 문장이같을경우를전제로특정 Plan 으로처리되도록하는 기능이다. coenect sys/manager grant create any outline to scott; connect scott/tiger alter session set CREATE_STORED_OUTLINES=TESTOUTLINE; select * from emp,dept where emp.deptno = dept.deptno; alter session set CREATE_STORED_OUTLINES=false; connect outln/outln select * from OL$; sleect * from OL$HINTS; connect scott/tiger alter session set USE_STORED_OUTLINE=TESTOUTLINE; select * from emp,dept where emp.deptno = dept.deptno; Select * from emp,dept where emp.deptno = dept.deptno; connect sys/manager select * from V$SQL; SQL> select * from emp,dept where emp.deptno = dept.deptno; 1792 개의행이선택되었습니다. Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=7 Card=1792 Bytes=89600) 1 0 HASH JOIN (Cost=7 Card=1792 Bytes=89600) 2 1 TABLE ACCESS (FULL) OF 'DEPT' (Cost=1 Card=4 Bytes=72) 3 1 TABLE ACCESS (FULL) OF 'EMP' (Cost=4 Card=1792 Bytes=57344) OL$ OL_NAME SQL_TEXT TEXTLE SIGNATU HASH_VAL CATEGORY VERSIO CREATO TIMESTAMP FLAG HINTCOUN N RE UE N R S T SYS_OUTLINE_01082310 select * from emp,dept 54 3720055558TESTOUTLI 8.1.6.2.0 SCOTT 2001-08-23 1 10 12210000 where emp.deptno = dept.deptno NE 10:12 OL$HINTS OL_NAME HINT# CATEGORY HINT_ TYPE HINT_TEXT STAGE # NODE # TABLE_NAMTABLE_TI TABLE_PO E N S 27
SYS_OUTLINE_010823101221 0000 1TESTOUTLINE 0NO_EXPAND 3 1 0 0 SYS_OUTLINE_010823101221 0000 2TESTOUTLINE PQ_DISTRIBUTE(EMP NONE 0NONE) 3 1EMP 1 0 SYS_OUTLINE_010823101221 0000 3TESTOUTLINE 0USE_HASH(EMP) 3 1EMP 1 0 SYS_OUTLINE_010823101221 0000 4TESTOUTLINE 0ORDERED 3 1 0 0 SYS_OUTLINE_010823101221 0000 5TESTOUTLINE 0NO_FACT(EMP) 3 1EMP 1 0 SYS_OUTLINE_010823101221 0000 6TESTOUTLINE 0NO_FACT(DEPT) 3 1DEPT 2 0 SYS_OUTLINE_010823101221 0000 7TESTOUTLINE 0FULL(EMP) 3 1EMP 1 2 SYS_OUTLINE_010823101221 0000 8TESTOUTLINE 0FULL(DEPT) 3 1DEPT 2 1 SYS_OUTLINE_010823101221 0000 9TESTOUTLINE 0NOREWRITE 2 1 0 0 SYS_OUTLINE_010823101221 0000 10TESTOUTLINE 0NOREWRITE 1 1 0 0 V$SQL SQL_TEXT select * from emp,dept where emp.deptno = dept.deptno Select * from emp,dept where emp.deptno = dept.deptno OUTLINE_CATEGORY TESTOUTLINE 28
Optimizer 의이해와 Advanced SQL 을위한 Join Method 운영시스템에서는 SQL문장각각이중요한역할을하므로 Advanced SQL을작성하기위해서 Application개발자는 Oracle의 Optimizer의기본원리를이해하는것이무엇보다중요하다. OLTP 와 DW의차이점및 Bind변수를사용하여 SQL을공유하여사용하는것에대한장점및단점, 어떤곳에 Literal이유리한지에대한인지하는것이효과적인업무개발의기본이라할수있을것이다. Optimizer의원리이해 Oracle의 Query처리단계는크게 5단계로볼수있으며, Optimizer의하는역할은 sub-queries 와 views의 Merge를수행하고 OR expansion작업을수행하는 Query Rewrite 단계, Query에대한 access path를결정하는 Query Optimization 단계로이루어지며, CBO에서는 Query Execution Plan을구하기위하여 RBO보다복잡한단계를거치게된다. Query Parse Optimizer Query Rewrite Query Optimization RBO CBO Results Query Execution QEP Generation Parse 단계 Query Rewrite 단계 Optimization 단계 QEP Generation 단계 syntax, security, semantics 의 Check및 simple transformations을수행한다. sub-queries 와 views의 Merge를수행하고 OR expansion작업을수행한다. Query에대한 access path를결정한다. Query 를실행하는데필요한상세한정보를만들며, 이를 (Query Execution plan) QEP 라고한다. 29
Query Execution 단계 QEP 에따라 SQL 문장을실행한다. 참고 1) simple transformations 최적의 QEP 를만들어내기위하여내부적으로다음과같은유형의 Query 들을내부적으로 Transformation 시켜서최적의 QEP 를찾는것을의미한다. Example Expression ename LIKE WARD ename IN ( KING, WARD ) ename=any/some( KING, WARD ) deptno!= ALL(10,20) sal BETWEEN 2000 and 3000 NOT(sal<1000 OR comm is null) Transformation ename= WARD ename= KING OR ename= WARD ename= KING OR ename= WARD deptno!= 10 AND deptno!= 20 sal >=2000 AND sal <= 3000 sal >= 1000 and comm is not null 참고 2) sub-queries 와 View Merging sub-queries 와 View Merging 이란 Optimizer 가보다효과적인 QEP 를찾기위하여 Query Rewrite 단계에서수행되는부분이다. View Merging 예 create view emp_d10 as select * from emp where deptno=10; select empno from emp_d10 where empno > 11910; select empno from emp where deptno = 10 and empno > 11910; Sub-Query Merging 예 ( Single Row Sub Query) deptno = (select deptno from emp where empno < 12501); 30
select... from dept where deptno = evaluated_value; QEP(Query Execution Plan) Oracle Optimizer는주어진 Query에대해서실행하는데필요한상세한정보인 Query Execution Plan을생성하게되며, Query Execution Plan은 Serial Plan과 Parallel Plan이있다. Serial Plan이란 Query에대해서 Parallel이적용되지않은 Plan이며, Parallel Plan이란 Query에대해서 Parallel로실행할정보를생성해내는것이다. 경우에따라서 Serial Plan만생성하거나, Serial과 Parallel Plan 을동시에생성하기도한다. Oracle의 Serial Plan은 Query가 Parallel로수행할정보가없을경우, 즉 Table의 Degree나 Hint등이없는경우는 Serial Plan만만들게되며, Parallelism이적용될경우 Serial Plan과 Parallel Plan을만들게된다. Oracle의 Serial Plan을 RSO Tree라하며, Parallel Plan을 DFO Tree라고한다. RSO = Row Source Operator (Serial) DFO = Data Flow Operator (Parallel) 예를들어 select count(*) from big_emp; 에대한 Query Plan을보면다음과같은 serial execution plan을얻을것이며 Query Plan ---------- 0-SELECT STATEMENT 1- SORT AGGREGATE 2- TABLE ACCESS FULL BIG_EMP RSO 와 DFO Tree 는다음과같을것이다. RSO Tree select 0 DFO Tree sort aggregate table scan BIG_EMP 1 2 1 SELECT /*+ ROWID(A1) PIV_SSF */ COUNT(*) FROM "BIG_EMP" A1 WHERE ROWID BETWEEN :B1 AND :B2 31
Parallel 로실행할경우 Execute 시 Resource 의부족으로원하는 Degree 의 Parallelism 으로실행할 수없는경우 RSO Tree 를쓰기도한다. OLPT 와 DW 의특징 OLTP 의특징 GOAL => Fast Response Time, Small operational datasets Parsing Time을최소화하고 SQL등이공유될수있도록 Bind 변수의사용을해야한다. Index의사용율이높아야한다. Sorting을최소화해야한다. Nested Loop Join(FIRST_ROWS) 방식으로많이유도한다. DW의특징 GOAL => Best Throughput, Large operational datasets Index의참조는중요한사항이아니다. Sorting 또는 Aggregate Function등이중요한역할을한다. Hash Join등을많이사용하도록유도한다. Parsing Time등은그리중요하지않으며 Bind변수의사용이문제가될수있다. Parallel Query등의사용율을높인다. Optimizer에영향을줄수있는 Parameters 항목 Optimizer가 Plan을수립하는데영향을줄수있는 Parameter값이무엇인지를알고있는것이무엇보다중요하다. 다음에나오는항목은 Query가수행당시의 Parameter중 Optimizer가 Plan을수립하기위해참조된항목이다. (Version마다다름.) OPTIMIZER_PERCENT_PARALLEL (Default=0) Optimizer_Percent_parallel의 Parameter는 Cost Base Optimizer가 Cost를계산하는데영향을주는 parameter이다. 즉수치가높을수록 Parallel을이용하여 Full Table Scan으로 Table을 Access하려고한다. 이값이 0인경우는최적의 Serial Plan이나 Parallel Plan을사용하며, 1~100일경우는 Cost의계산에서 Object의 Degree를사용한다. 예를들어 Optimizer_Percent_parallel=50 Table에대한 Scan Cost=1000 Table의 Degree=5 일경우 32
Cost = 1000/(5*(50/100)) = 400 이므로 RSO(Serial) 보다는 DFO(Parallel) 로가도록한다. OPTIMIZER_MODE/GOAL (Default=Choose) HASH_AREA_SIZE,HASH_JOIN_ENABLED,HASH_MULTIBLOCK_IO_COUNT 위의 parameter 의값에따라서 Hash Join 으로유도할수있다. OPTIMIZER_SEARCH_LIMIT (Default=5) Optimizer에게 Join Cost를계산할경우 From절에나오는 Table의개수에따라서 Join의경우의수가있을수있으며, Optimizer는이들각각의경우의수에대한 Join Cost를계산하게된다. 물론일부예외사항은있다. 예를들어 Cartesian Production Join등은우선순위가낮으므로뒤로미루게되어있다. 이 parameter의값이 5일경우 From절에 5개의 Table에대해서모든 Join의경우의수를가지고 Cost를계산하게되며, 그개수는 5!=120개의경우의수에대한 Join Cost를계산하게되므로 Optimizer가많은시간을소모하게되므로 Performance에영향을미칠수도있다. SORT_AREA_SIZE, SORT_MULTIBLOCK_READ_COUNT 위의 parameter 의값에따라서 Sort Merge Join 으로유도할수있다. PARTITION_VIEW_ENABLED = TRUE PARTITION_VIEW_ENABLED의 Parameter는 Partition View나 Partition Table을사용할경우에 Optimizer가불필요한 Table의 Access를 Skip하도록하기위한기능이며, 전체 Table의 Cardinality를계산하는방식이아니고각 Partition Table의 Cardinality를이용하게하는기능으로 Partition Table이나 View의 Cost를작게계산되도록하여 Plan을유도하는데사용한다. _FAST_FULL_SCAN_ENABLED 이값이 TRUE 일경우 Index 에대한 DB_FILE_MULTIBLOCK_READ_COUNT 를통한 Full Scan 을가능하도록한다. 단 Index 의해당 Column 에는 Not Null 과해당 Column 이모두 Index 로구성이되어야한다. DB_FILE_MULTIBLOCK_READ_COUNT 이 Parameter의수치가클수록 Index Scan보다는 Full Table Scan의비중이높아진다. OPTIMIZER_INDEX_CACHING (Default = 0) Cost-base Optimizer가 nested loop join을선호하도록조절하는 parameter. Nested loop join시 buffer cache내에 inner table의 index를 cache화하는비율 (%) 를지정하므로 nested loop join시성능의향상을가져오며, Optimizer는 Cost계산시이비율을반영하여 Nested Loop Join을선 33
호하도록 Plan 이선택된다.(0~100) 100 에근접할수록 Index Access Path 가결정될가능성이높 다. OPTIMIZER_INDEX_COST_ADJ (Default =100) Optimizer가 Index를사용하는위주의 Plan으로풀릴것인지또는가능한사용하지않을쪽으로풀릴것인지의비중을지정한다. Cost-base optimizer는 Rule Base처럼 Index를사용하도록 Plan이주로만들어지게되나, 반드시 index가있다고 rule-base처럼 index를이용한 plan으로처리되는것은아니다. index를이용한 plan위주로하고자한다면 100(%) 이하를, 가능한 index 를사용하지않고자한다면 100이상을지정한다. (1 ~ 10000) _ALWAYS_STAR_TRANSFORMATION (HIDDEN) TRUE 일경우 Optimizer 는항상 star transformation 의사용을선호한다. Join Method별특징 Oracle의 Join Method는 Nested Loop Join(NLJ), Sort Merge Join(SMJ), Hash Join(HJ) 의 3가지가있다. SQL의 Join Method별특징을정확히알고 Table들의 Data량과조건절의조건값에따라적절한 Join Method를사용하여야한다. Nested Loop Join(NLJ) NLJ 는순차적인처리로 Fetch 의단위 (ArraySize,PrefetchSize) 마다결과 Row 를 Return 받을수있다. NLJ 은 Driving Table 에서많은 Row 들이 Filtering 되어 Inner Table 로찾아들어가는부분을줄여야하므로 Driving 순서가중요하다. Inner Table 은 driving Table 의 Return 되는모든 Row 들에대해서반복실행하므로 Access 의효율이좋아야한다. 즉대부분의경우 Inner Table 은 Index 가있어야한다. 또한 Index 의효율이좋아야한다. Index 의효율이좋지않아전체의 Index Range Scan 과같은경우는최악의조건이다. NLJ 는주로 Index 위주의 Single Block I/O 의 Random I/O 위주이므로 OLTP 에서적은 Data 범위처리에주로사용된다. 즉전체의 15% 이상의경우는 Full Table Scan 을이용한 Sort Merge 또는 Hash Join 을이용한다. NLJ 도 Driving Table 이 Full Table Scan 에 Parallel 로처리되면 Inner Table 도 Parallel 로종속적으로처리된다. 34
Inner Table 에 Index 가있을경우 SELECT a.fld1,..., b.fld1,... FROM TAB1 a, TAB2 b WHERE a.key1 = b.key2 AND a.fld1 = 'AB' AND b.fld2 = '10' FLD1 ='AB' TABLE ACCESS BY ROWID KEY2= KEY1 TABLE ACCESS BY ROWID FLD2 ='10' check o 순차적 ( 부분범위처리가능 ) 종속적 ( 먼저처리되는테이블 의처리범위에따라처리량결정 ) 랜덤 (Random) 액세스위주 연결고리상태에따라영향이큼 주로좁은범위처리에유리 Cost(NLJ)=Read(S) + [rs *Read(B) ] 에비례 o o x 운반단위 INDEX INDEX (FLD1) TAB1 (KEY2) TAB2 Inner Table 에 Index 가없을경우 SELECT a.fld1,..., b.fld1,... FROM TAB1 a, TAB2 b WHERE a.key1 = b.key2 AND a.fld1 = 'AB' AND b.fld2 = '10' FLD1 ='AB' TABLE ACCESS BY ROWID TABLE ACCESS BY Full Table Scan FLD2 ='10' check o inner Table 에 join Key 에대한 index 가없을경우 Return 되는 Row 마다 Full Table Scan 발생 (NLJ 의연결고리이상발생 ) o o TAB2 의 KEY2 컬럼에대한 Index 가없어서 Driving Table 의각 Row 마다 Full Table Scan 발생 운반단위 INDEX (FLD1) TAB1 TAB2 35
Sort Merge Join(SMJ) 전체범위. 즉전체 Row 들을가지고어떤 Operation( 모든 Rows 들을 Join Key 로 Sorting) 하기전까지는어떠한 Row 들도 Return 할수없음. NLJ 과같이 Driving Table 의 Return 되는 Row 수와 Inner Table 의 Access Pattern 에의에 Access 의효율이좌우되지않으며, Join Table 간의자신의처리범위로만처리량을결정하므로독립적이다. Sort 의 CPU 사용에대한 Overhead 가있다. 그러므로많은 Row 들과전체적으로 Select List 의 Size 의합이큰 Table 의 Join 에는문제가있다. 즉 Disk Sort 를피할수가없으며, Sort 의 CPU 비용이많이든다. Disk Sort 만발생하지않는다면넓은범위처리에유리하다. Disk Sort 를피할수없는경우라면 SORT_AREA_SIZE, SORT_MULTIBLOCK_READ_COUNT 를 SQL 마다 Session Level 에할당해서사용하도록한다. 또한 TEMP Tablespace 의 Extent Size 도충분히크게주도록한다. ALTER SESSION SET SORT_AREA_SIZE= 104857600; ALTER SESSION SET SORT_MULTIBLOCK_READ_COUNT=128; Sort Memory 의 Size 는 (= Target rows * (total selected column s bytes) * 2) 이상설정하되 PGA 의 Memory 의한계로인해 TEST 를통해 PGA Memory Allocation Error 가발생하지않는범위내에서설정하도록한다. ( 현재 100MB 까지는이상없었음 ). 필요시 10032 Trace 를이용해점검한다. ALTER SESSION SET EVENTS 10032 TRACE NAME CONTEXT FOREVER; SELECT /*+ use_merge(a b) */ a.fld1,..., b.fld2,... FROM TAB1 a, TAB2 b WHERE a.key1 = b.key2 AND a.fld1 = 'AB' AND b.fld2 = '10' FLD1 ='AB' TABLE ACCESS BY ROWID a.key1= b.key2 를조건으로 Merge TABLE ACCESS BY ROWID FLD2 ='10' 동시적 ( 무조건전체범위처리 ) 독립적 ( 자기의처리범위만 으로처리량결정 ) 스캔 (Scan) 액세스위주 연결고리상태에영향이없음 S O R T S O R T 주로넓은범위처리에유리 Cost(SMJ)=Read(S) + Write(SortRuns SortRuns(S)) + Read(B) + Write(SortRuns SortRuns(B)) + Merge(S,B) + CPUSortCost(S + B) 에비례 INDEX (FLD1) TAB1 운반단위 TAB2 INDEX (FLD2) 36
Hash Join(HJ) Hash Join 은두개의 Join Table 중 Small Table(Where 조건에의해 Filtering 된 Row 수가작은 Table) 을가지고 HASH_AREA_SIZE 에지정된 Memory 내에 Hash Table 을만든다. Hash Table 을만든이후부터는부분범위처리형태이다. 그러므로 NLJ 과 SMJ 의장점을가지고있다. NLJ 과같이 Driving Table 의 Return 되는 Row 수와 Inner Table 의 Access Pattern 에의에 Access 의효율이좌우되지않으며, Join Table 간의자신의처리범위로만처리량을결정하므로독립적이다. SMJ 의단점인많은 Row 들과전체적으로 Select List 의 Size 의합이큰 Table 의 Join 시 Sort 의 CPU 사용에대한 Overhead 및 Disk Sort 와같은문제점은없다. 그러므로최소한 SMJ 보다는우수하다. 한 Table 은작은 Size(Return 되는 Row 수와 Select List 기준 ), 한 Table 은아주큰 Size 의 Join 에유리하다. 이러한경우는반드시작은 Size 를가지고 Hash Table 을만들어야한다. Hint 를잘못주어서 Big Table 부터 Driving(Build Table) 된다면 HASH_AREA_SIZE 의 Memory 부족으로 TEMP Disk I/O 가발생한다. 그러므로 Hint 를줄경우반드시 Driving 순서를정확히주어야한다. Disk I/O 를피할수없는경우라면 HASH_AREA_SIZE(default : =SORT_AREA_SIZE * 2) 를 SQL 마다 Session Level 에할당해서사용하도록한다. 또한 TEMP Tablespace 의 Extent Size 도충분히크게주도록한다. HASH_MULTIBLOCK_IO_COUNT 는 Optimizer 에게자동조정하도록설정하지않는다. ALTER SESSION SET HASH_AREA_SIZE= 104857600; Hash Memory 의 Size 는 (= Small Table 의 Target rows * (total selected column s bytes) * 1.5) 이상설정하되 PGA 의 Memory 의한계로인해 TEST 를통해 PGA Memory Allocation Error 가발생하지않는범위내에서설정하도록한다. ( 현재 100MB 까지는이상없었음 ). 필요시 10104 Trace 를이용해점검한다. ALTER SESSION SET EVENTS 10104 TRACE NAME CONTEXT FOREVER; 37
SELECT /*+ use_hash(a b) */ a.fld1,..., b.fld2,... FROM TAB1 a, TAB2 b WHERE a.key1 = b.key2 AND a.fld1 = 'AB' AND b.fld2 = '10' Table R (build input) Table S (probe) 순차적 ( 부분범위처리가능 ) 독립적 ( 자기의처리범위만 으로처리량결정 ) 스캔 (Scan) 액세스위주 연결고리상태에영향이없음 넓은범위처리에유리 Cost(HJ) = Read(S) + Build Hash Table in Memory (cpu( cpu) ) + Read(B) + Perform In memory Join(cpu cpu) 비례 Hash Table & bitmap filter in memory Disk Rows Hash Join 예 Key ANDY POONAM DEEPAK RUSS SUNITHA RAMESH JONATHAN DE3525B1 RICHARD Hash1 267B4DCD 7AF8C9A7 EE5FFFFE 4403A5DA 32C9A7C6 3A754F8D DF970C35 Assuming, Partition# (2 partitions) 1 1 0 0 0 1 1 1 Partition# mask = 0x00000001 Bitvec Position mask = Hash Bucket mask = Hash2 807F5A3E 3E5CEEDC D61F8268 AB31D505 C9F92A85 E9000EFB 10C2B4BC 16A2D766 0x0000001F 0x00000007 Bitvec Pos (32 bits) 1E 1C 08 05 05 1B 1C 06 1. Build Table단계 : smaller table (English) 을 Scan하여 Memory상에 Hash Table을만든다. Hash Table은 partitioned, bit vectors, Hash Bucket으로구성되며, Hash Table을만드는과정에서 Hash memory가부족할경우 TEMP Disk로 partition의일부또는전부를보관한뒤 2번째단계에서처리하게된다. Bucket# (8 bkts) 6 4 0 5 5 3 4 6 0 1 2 3 4 5 6 7 English Table Name ANDY RUSS JONATHAN RICHARD hash buckets (kkrhht) 2. Probing Table단계 : Hash Table을만든이후, larger table (BugEsc) 을 Scan하여 Join Key에대한 Hash Function #1,#2를적용하여해당 partition 및 Bucket에같은 Join Key가있는지확인하여있으면, 즉 Join이성립되면 Row들을 Return하고없으면 Row를버리게된다. ANDY POONAM DEEPAK RUSS SUNITHA RAMESH JONATHAN Partition# ANDY 1 1E RUSS 0 05 JONATHAN 1 1C RICHARD 1 06 Bit Vector Passes bitvec. Found on chain 6 Row returned. Partition 1 and bitvec (1C) is set. However, row not found on chain 4. Partition 0 but bitvec (08) unset. Passes bitvec. chain 5. Row returned. Partition 0, bitvec bitvec 5 set, however, not found on chain 5. Partition 1 but bitvec (1B) unset. partition 1 on chain 4. Row returned. BugEsc Table Name ANDY POONAM DEEPAK RUSS SUNITHA RAMESH JONATHAN row headers (kkrhlst) select /*+ use_hash(bugesc) */ English.Name from English, BugEsc where English.Name=BugEsc.Name; Partition 0 bit vector filter (kkrhpbit) 00000100 00000000 00000000 00000000 row data RUSS Partition 1 bit vector filter (kkrhpbit) 00000010 00000000 00000000 00001010 row data ANDY JONATHAN RICHARD 38
PRO*C Precompile Option 권장안 1. User 의입력조건에따라 SQL 문장이만들어지는 Dynamic SQL 이라도 Bind 변수를사용한공유형태의 SQL 문장으로작성한다. 특히사용빈도가아주높은곳에는반드시적용한다. 단. 변경이없는상수 (Literal) 는문제가없다. (Application) 2. Bind 변수를사용하지않는곳에는 Pro*C Compile Option 중 RELEASE_CURSOR=YES, HOLD_CURSOR=NO 의 Option 으로 Compile 한다. RELEASE_CURSOR=NO 로 Compile 할경우는모든부분이 Bind 변수를사용하여공유화할경우만사용한다. (Application) 3. PRO*C 의경우 Oracle 8i 부터제공되는 PREFETCH 를 Compile Option 을 100 정도로사용한다. Array Fetch 를사용한기존의 Program 은그대로사용한다. ODBC,JDBC,OLEDB,OO4O 와같은다른 DB 접속방식도 PREFETCH 기능을제공하므로이기능을충분히활용한다. (Application) PRO*C 개발자의주의사항 MAXOPENCURSORS :maxopencursors 가 Application 에서실행되는 SQL 을수용할정도로충분하고 OPEN_CURSORS(init.ora) 이내라면 hold_cursor=yes / release_cursor=no 와 hold_cursor=no / release_cursor=no 의차이는없음. hold_cursor=yes,release_cursor=no : SQL 문장이대부분 Literal SQL( 비공유 SQL) 일경우 Cursor Cache 의크기가무한정늘어나서 max open cursor 문제주의. release_cursor=yes : CPU 사용률증가및성능저하가많이발생하기때문에가능하면사용하지않도록한다. 즉 Literal SQL 을수정하고공유화한후에 release_cursor=no 를사용한다. session_cached_cursors(init.ora) : hold_cursor 와유사한역할을하며, Bind 변수를사용한경우만적용한다. Dynamic SQL (Method #1 ~ #4) :compile option 과상관없이무조건 parse call 이발생. 즉 Statistics SQL 처럼고정된형태가아니라매번 SQL 문장이변경될수있으므로 Parse Call 은발생한다. 그러나 Dynamic SQL 도 Hard Parsing 만큼은일어나지않도록하기위해 Bind 변수를사용해야한다. Precompile Option 39
Release Cursor (default : NO) 정의 : SQL 문장이실행되어질때연관된 cursor cache 와 private SQL area 간의 link 를 control 하는 Option. If release_cursor=yes Then : SQL 문장실행후 cursor 가 close 되고 link 가 remove 되며메모리가 free 되어진다. If release_cursor=no and hold_cursor=yes Then : link 가유지되고 precompliler 는 open cursor 수가 MAXOPENCURSORS 를넘지않는한 link 를재사용하지않는다. 일반적으로빈번하게수행되는 OLTP application 에서는 NO 로사용하여처리성능을높힐수있다. 단반드시 Bind 변수를사용한경우에가능하다. ( 단전제조건으로반드시 Bind 변수를사용한곳에적용을한다. Literal 을사용한곳은 YES 로사용한다.) Hold cursor (default : NO) 정의 : SQL 문장이실행되어질때연관된 cursor 와 cursor cache entry 간의 link 상태를 control 하는 Option. (cursor cache entry 는문장을 processing 할때필요한정보를저장하는곳이다 ). If hold_cursor=no Then : SQL 문장을실행한후 cursor 가 close 되고 precompiler 는 link 를 reusable 상태로 mark, 그리고는다른 SQL 문장을위해그 link 를바로사용할수있고 Private SQL area 에할당된메모리를 free 시킨다. If hold_cursor=yes and release_cursor=no Then : link 가유지되고 precompliler 는다른 SQL 을위해그 link 를다시사용하지않는다. reparsing 할필요가없고 private sql area 에메모리를할당할필요가없어 performance 가좋아진다. Release_cursor=Yes 가 Hold_cursor=Yes 에우선하고 Hold_cursor=NO 는 release_cursor=no 에우선한다. Release cursor option 과마찬가지로빈번하게수행되는 OLTP,BATCH application 에서는 YES 로사용하여처리성능을높힐수있다. 단해당 SQL 을재사용이자주되는경우만지정한다. ( 단전제조건으로반드시 Bind 변수를사용한경우만 YES 로적용을한다. Literal 을사용한곳은 NO 로사용한다.) MaxOpenCursors (Deafult : 10) Precompiler 가 cache 하려는동시에 open 되는 cursor 의수를지정하는 option 이다. Maxopencusors 는 SQLLIB cursor cache 의 initial size 를지정한다. Free cache 엔트리가존재하지않을때새로운 cursor 가필요하다면 entry 를 reuse 할것이다. 그러나 reuse 하는것은 hold_cursor, release_cursor 파라미터와관련한 cursor cache entry 의상태에따라불가능할수도있어 reuse 가불가할시에는새로운 cursor cache entry 을할당한다. 필요하다면 open_cursors 에지정된 limit 에도달하거나메모리를다사용할때까지 cursor cache entry 를추가할것이다. 40