?????. DB2 ? XpertMon ??? ???!
? ????????? DB ??????.
???? ?????? ?? XpertMon ????? ??? ??????.
??? ?????? ????? ?? ???? ??? ?????. ??? ????.
???? ????? V8.2 ?? ??? ??? Runstats ? ?? ????? ?????.
Runstats ? ???? ?? ????
?? ?? ??? ??? ??? ??? ??????.

---

V8.2 ??? Runstats I
Runstats ? ???
DB2 optimizer? ??? access path? ???? ?? catalog ??? ?????.
??? catalog??? ???? ?? ??? Runstats utility? ???? ????.
User ???? ??? ?? ? catalog ?? ???? ????? ???? ????.
Runstats ???? ???? ???? ?? ?? ??? ??? Catalog ???? ???????.
Runstats ? ?????? ??? ?? ??? ????? ? ? ????.
???? ?? ???,

• syscat.tables ? CARD= -1 ?? STATS_TIME is NULL ( ???? ?? ???)
• syscat.indexes ? NLEAF=-1 ?? NLEVELS= -1 ?? FULLKEYCARD = -1 ?? STATS_TIME is NULL ( ???? ?? ???)

Runstats ?? ??
??? ?? ???? ???? ???? ?? Runstats ? ???? ???.

• ???? ???? LOAD?? ??? ???? ??? ? - LOAD?? ?? ???? ???? LOAD?? ?? ??? ???? ?? ? ??
• ??? ???? ??? ?
• REORG Utility? ???? ???? ?
• ???? ???? ???? INSERT, DELETE, UPDATE? ??? ?
• Prefetch Size? ??? ?
• Redistribute Database Partition Group Utility? ??? ?

Runstats ?? ????? Query Explain ? ???? ????? ? ? ????.
Runstats ????? Statistics SQL? ??? Package? REBIND ???? ???.
db2rbind ???? ???? ?????? ?? ?? ???? Rebind ? ? ????.
?? ?? Package Cache?? ?? ?? ??? Dynamic SQL?? ???? ??
FLUSH PACKAGE ???? ???? ???.

?? Runstats ??

• ?? ??? ?? ??

  RUNSTATS ON TABLE db2admin.department ON COLUMNS (deptno, deptname)

• Key ??? ?? ?? : index ? ???? ?? ??? ?? ??? ??

  RUNSTATS ON TABLE db2admin.department ON KEY COLUMNS

• ?? ??? Catalog ?? ??

  RUNSTATS ON TABLE db2admin.department AND INDEXES ALL

• ?? ??? 3?? Catalog ?? ??

  RUNSTATS ON TABLE db2admin.department FOR INDEXES db2admin.INX1, db2admin.INX2, db2admin.INX3

• ???? ?? ??

  RUNSTATS ON TABLE db2admin.department FOR INDEXES ALL

With distribution ??
???? ??? ???? ?? ? with distribution ??? ?? Runstats ? ?????. ????? Catalog ?? ???? ???? ?? ? ?? ?? ?? ?? ???
??? ??, optimizer? ??? ?? ? ??? ????? ???? Access path?
?????. ?? ??? ??? ?? ??, ???? ??? ???? ???? ???
?? optimizer? ??? ?? ?? ????.

• ???? ?? distribution ?? ??? ??? ?? ??

  RUNSTATS ON TABLE db2admin.department WITH DISTRIBUTION AND INDEXES ALL

• ?? ??? ?????, ?? ??? distribution ?? ??? ??

  RUNSTATS ON TABLE db2admin.department ON COLUMNS (deptno, deptname) WITH DISTRIBUTION ON COLUMNS (mgrno, admrdept)

frequency, quantile ??
with distribution ???? runstats ? ???? frequency? quantile ?????
???? ???. ???? ???? ??? ???? DB CFG ? num_freqvalues?
num_quantiles ?? ?? ?????.
?? CFG component?? ? ? ?? ??? num_freqvalues? default ?? 10??,
num_quantiles ?? 20???. freqvalues ?? ?? ???? ?? ??? ?? ???
?? ??? ???? ???, ??? 10?? ???? ????? ?????.
quantiles ?? ??? ?? ?? ?? ???? ??? ???? ??? ??? ????
??? ??? 20?? ???? ????? ?????.
runstats ??? frequency? ??? quantile? ??? ?? ???? ???? ? ? ????.
?? ??? ???? Catalog ??? ????, runstats ??? CPU? ????
? ???? ???. ??? ??? ??? ???? ?? ??? ???? ?? ????.
?? ?? ???? ?? ?? ??? ?? ?? ?? ????.
Runstats? ??? ??? ?? ?? ?? ??? ???? ????.

• num_freqvalues = 0 ??, num_quantiles = 0 ?? 1 ? ??
• ? ??? ?? unique? ?
• ????? LONG, LOB, ?????? ??
• ??? NOT-NULL ?? ? ?? ?? ??
• extended index ??? declared temporary table ? ??

Ex) deptname ? frequency=50, quantile = 100?? , deptno? frequency = 5? quantile = 10 ??, ??? idx1,idx2 ? ?? ???? ??

RUNSTATS ON TABLE db2admin.department WITH DISTRIBUTION ON COLUMNS (deptno, deptname NUM_FREQVALUES 50 NUM_QUANTILES 100) DEFAULT NUM_FREQVALUES 5 NUM_QUANTILES 10 AND INDEXES db2admin.IDX1, db2admin.IDX2

?? ?? ?? ??
?? ?? ??? ????, ?? ?? ??? ?? distinct ?? ?? ??? ?????.
?? ???? db2 optimizer ? ??? ????? ? ? ???, ?? ?? ?? ???
?? ? ? ???? db2 optimizer ? multi predicate ? ?? ??? ????? ?????.
???? ?? ?? ??? ???? ????? ???? ????, ?? ?? ???
???? ????.

• ?? ?? ?? ?? : ?? ?? ( deptno,deptname) ? (admrdept,location)

  RUNSTATS ON TABLE db2admin.department ON COLUMNS ((deptno, deptname), deptname, mrgno, (admrdept, location)

LIKE Statistics
Runstats ?? Like ??? ?? SYSIBM.SYSCOLUMNS ???? SUB_COUNT ???
SUB_DELIM_LENGTH ??? ???? ??? ?????.
????? string ??? ?? ??? ????, “column LIKE ‘%abc’ ?
column LIKE ‘%abc%’ “ ? ?? predicate ? optimizer? ?? ??? ??? ??? ?
??? ?????.

• ?? ?? ?? ??.deptname ? ???? LIKE ?? ??

  RUNSTATS ON TABLE db2admin.department ON ALL COLUMNS and COLUMNS (deptname LIKE STATISTICS)

V8.2 ??? ?? - ?? ????? ??
V8.2? ??? ????, ?? ???? ??? ?? ??? ????? ??? ????
profile? ?????. SET PROFILE ??? ?? runstats ???? catalog ????
????? ?????. statistics profile ? update??? UPDATE PROFILE ????
???? ???. ? DELETE PROFILE ? ????.

• ?? ?? ???? ?? statistics profile?? ??

  RUNSTATS ON TABLE db2admin.department AND INDEXES ALL SET PROFILE ONLY

• statistics profile? ???? ??? profile? ???? ?? ?? ??

  RUNSTATS ON TABLE db2admin.department AND INDEXES ALL SET PROFILE

• ?? ?? ???? ?? statistics profile ? ??

  RUNSTATS ON TABLE db2admin.department WITH DISTRIBUTION AND INDEXES ALL UPDATE PROFILE ONLY

• statistics profile ??
  
• statistics profile ? ???? ?? ??

  RUNSTATS ON TABLE db2admin.department USE PROFILE

Sampling Runstats
????? ???? ??????? ?? ?? ???? ?? runstats? ????
?? CPU ? ??? ?? ?? ??? ???? ??? ???.
?? ???? RUNSTATS? ??? ??? FULL ??? ??? ?????.
???, ??? sampling??? ???? ??? ???? ???.
?? ??? ??? ?? ???? ??? ???? ????, ????? ??? ???
??? ???? ????? ?????, data sampling ? FULL ??? ????? ??????.
SAMPLED DETAILED ??? ?? ??? ??? ??? ? sampling? ????,
detailed ??? ????? ??? ??? ????? ???? ???.

• sampling? ??? index detailed ?? ?? ??

  RUNSTATS ON TABLE db2admin.department AND SAMPLED DETAILED INDEXES ALL

V8.2 ??? ?? ? Row-level Bernoulli sampling
Row-level Bernoulli sampling ? sargable predicate ? ?? ??? ???? P percent?
sample? ???? ????. ? ????? I/O ? ????, ???? row? ?????.
?? ? ????? I/O ? ?????? ???, ??? ???? ??? ? ???
CPU ??? ??? ? ????.
?? Runstats? CPU ???? ???? ??? ?? ? ??? ? ? ????.

• Row? 10% ? distribution ?? ??

  RUNSTATS ON TABLE db2admin.department WITH DISTRIBUTION TABLESAMPLE BERNOULLI (10)

V8.2 ??? ?? ? system page-level sampling
system page level sampling ? ROW ?? PAGE? ??? ?? ? ???? P% ??? ????,
100-P% ??? ???? ???.
???? ? ????? I/O ? ?????, Bernoulli ? FULL ??? ???? I/O ?
??? ? ????. ???? ? ?? prefetch? ???? ?????.
Runstats ? REPEATABLE ??? ?? ?? ??? ???? ??? ???? ????
??? ???? ??? Sampling ??? ?????.
??? ??? ? ???? ?? ?? ???, ? ??? ????? sampling ?
????? ?? ?????.

• Row? 10% ? distribution ?? ??. ?? sampleset ? ????? ????

  RUNSTATS ON TABLE db2admin.department WITH DISTRIBUTION TABLESAMPLE BERNOULLI (10) REPEATABLE (1024)

• ??? ???? 10%? system page level sampling? ?? ?? ??.

  RUNSTATS ON TABLE db2admin.department AND INDEXES ALL TABLESAMPLE SYSTEM (10)

---

XpertMon for DB2 UDB V2
DB ???
Tel : 02-2108-1458
Fax : 02-2108-1459
Mobile : 011-896-6545
E-mail : hjlee@iteg.co.kr
URL : http://iteg.co.kr