?????. DB2 ? XpertMon ??? ???!
? ????????? DB ??????.
????? ???? ??? V8.2??? Runstats? ??? ????? ?????.
?? ?? ??? ??? ??? ??? ??????.
V8.2 ??? Runstats II
???? ??? ??? ?? ????
? ??? ??? ????? ???? ??? ?? ? ??? Reorgchk? ???? ????.
- ?? ???? ?? ??? ??? ? reorgchk ??
REORGCHK UPDATE STATISTICS ON TABLE ALL
|
?? ?? update statistics ??? ???? ?? ???? ??? default ????
runstats ? ?????.
- ?? ???? ?? ??? ??? ? reorgchk ??
REORGCHK UPDATE STATISTICS ON SCHEMA systools
|
? ??? load replace ???? Index ???? ??? ??? ? ????.
load ? ????, runstats? ???? ??? load ? ????? ??? ???? ??
?? ??? ?? ? ????. load? statistics use profile ??? ???? ???.
statistics yes ??? ??? ?????.
- Load ? ??? ? ????? ??
LOAD FROM inputfile.del OF DEL REPLACE INTO db2admin.department STATISTICS USE PROFILE
|
???? ?? index? ??? ? runstats? ???? ???, index? ?????
????? ?? ??? ? ????.
- Index ??? ?? ????? ??
CREATE INDEX db2admin.inx1 ON db2admin.department (deptno) COLLECT STATISTICS
|
- sampling? ???? detail? ????? ??
CREATE INDEX db2admin.inx3 ON db2admin.department (deptname) COLLECT SAMPLED DETAILED STATISTICS
|
Runstats ??? ?? ??
??? ??? ??? ????? ???.
- sysadm, sysctrl, sysmaint, dbadm, ?? ???? CONTROL ??
?? ???? Runstats? ???? ?? ???? Access ? ? ?? ??? ????
??? ??? ????.
Runstats ??? ALLOW READ ACCESS ??? ??, Runstats ?? ?? ?? ????
???? ?? READ ? ??? ? ????.
ALLOW WRITE ACCESS ??? ?? Runstats?? ?? ?? ???? ???? ??
READ,WRITE? ?? ??? ? ????.
Partition DB??? Runstats
Runstats ???? ?? Partition?? ????, ?? ???? ? Partition?
???? ?? ?? Partition?? Runstats? ?????.
?? ?? Partition? ???? ???? ???, ?? ???? ?? PartitionGroup?
?? ??? Partition ?? Runstats? ?????.
??? ?? Runstats? ?? Partition? ??? ??? ???? ?? ???? ????.
? Partition??? ????, ? ??? ???? ?? ?? Partition? ??? ????
???? ??? ???? ?? Partition ? ?? ??? ???? ?????.
DB CFG ? STAT_HEAP_SZ ? ??
DB CFG? stats_heap_sz ?? runstats ??? ???? ???? ????? ?????.
? ???? ?? Runstats ????? ??? ? ????, ??? ? ?????.
? ????? private agent ??????. ??? ?? ???? ?? ???? ?????
? ? ?? ???? ?? ????. SAMPLED DETAILED ??? ???? ?? Runstats?
????? ???? ??? ????? 2MB? ? ?????.
RUNSTATS? ???? ??? ??? ???
- ??? ?? ?? ???? ??, ?? ?? ????? ????? ??
- ?? ??? ???? ??. distribution ??? ??? ??? ????, predicate? ???? ??? ??
- Multi partition? ?? ? partition? ???? ??? partition ?? ???? ??
- ?? Workload? ??? ??? ???? ???? ??
- ???? ????? ???? Runstats? ??? ??
- ??? ??? ?? ??? ??
??? ??? ?? ?? ?? RUNSTATS? ????? ???? ?? ?? ???? ???
????? ?? ?? ?????. ??? 24*7 ???? ???? ??? ??? ???
?? ??? ?? ? ????.
?? ?? ??? ??? ? ?? ??? ??? throttling ??? ???? ????.
throttling ? ????? Resource ? ?? ???? ?????.
DBM CFG ? util_impact_lim ????? Runstats ??? UTIL_IMPACT_PRIORITY ?
?? ??????? throttling? ???? ???.
DBM CFG? util_impact_lim ?? ????? workload?? ????? ???? ?????.
?? util_impact_lim? 100(default?) ???, ?? ????? throttling ?? ????.
?? util_impact_lim? 10?? ????, Runstats? ?? workload? 10%?? Resource?
???? ?? ???.
- UTIL_IMPACT_PRIORITY ??
RUNSTATS ON TABLE db2admin.department AND INDEXES ALL UTIL_IMPACT_PRIORITY 10
|
DBM CFG ? util_impact_lim ?? 100? ?? ? 10%? ?? ?? ??? throttle??
Runstats ? ?????.
?? UTIL_IMPACT_PRIORITY ?? ???? ?? util_impact_lim ?? 100? ????
Runstats? default ?? 50 ?? throttle?? ?????.
UTIL_IMPACT_PRIORITY ??? ?? ???, ?? 0?? ???? throttle ?? ????.
throttle ? ???? ????? ?????, ???? ?? ??? ?????.
declared global temporary table ? ?? ???? ??
Runstats? Declared global temporary table (DGTT) ? ??? ? ????.
?? DGTT ??? ??? catalog ? ???? ?? ???, ???? ?? Catalog ?
???? ????. ? ??? DGTT ? ??? ???? ???? ?? ?????.
??? ?? ???? ?? DGTT? ??? Runstats ? ???? ?? ???? ? ????.
V8.2 ??? - ?? ?? ??
V8.2 ??? ?? ?? ????? ??? ???????.
???? ?? ?? ??? ??? ?? ????? ?? ??? ???? ?? ????.
?? ??? ????? ???? ??? Workload? ?? DB2 ? ?????.
?? ?? ??? ????? ???? ?????? Runstats? ?????.
?? ?? ??? ????? DB CFG ????? ?????.
- DB CFG ???? ??
db2 update db cfg for SAMPLE using AUTO_MAINT ON
db2 update db cfg for SAMPLE using AUTO_TBL_MAINT ON
db2 update db cfg for SAMPLE using AUTO_RUNSTATS ON
|
- ?? ???? ???? Hierarchy
?? ?? ?????? ????? AUTO_STATS_PROF ? AUTO_PROF_UPD ?? ON?? ?????.
?? ?? ?????? ??, ??? ??? ??????? ??? ?? ???
????? ???(query feedback warehouse) ? ???? ?? ???? ????
??? ?? ????? ?????.
?, ?? ?? ?????? SMP, MPP, federate ????? ???? ???,
serial ????? ?????.
query feedback warehouse ???? ????? ??? ?? Stored procedure? ???? ???.
- SYSINSTALLOBJECTS ? ???
call SYSINSTALLOBJECTS ( toolname , action , tablespacename , schemaname )
|
- SYSINSTALLOBJECTS ? ??
call SYSINSTALLOBJECTS ('ASP', 'C', 'USERSPACE1', '')
|
- Toolname : ASP ?? AUTO STATS PROFILING
- action : C(Create), D(Drop)
- Schemaname : ?? ???? ??
- ??? Query Feedback Wareshouse ??? ???
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