性能调优-课件第1周

DB2设计与性能优 讲师

DATAGURU专业数据分析社【】和幻灯片为炼数成金网络课程的教程以外范围散播，违者将可能被法律和经济课程详情炼数成金培训 DB2设计与性能优 讲

DATAGURU专业数据分析社DB2设计与性能优 讲师

DATAGURU专业数据分析社 本周内 DB2产Editions&DB2BufferAutomaticDB2设计与性能优 讲

BreakfreewithDATAGURU专业数据分析社 DB2版本介

Express&

DatabaseEnterpriseDeveloperAllowsdeveloperstodevelopandperformqualityassurance.TheeditionisaproductbundlethatincludesmanyDB2features.DB2设计与性能优 讲 DB2和Oracle版本对 DB2Express-nocostdownloadanddeploy-optionallicenseWindows,Linux,Solaris(Beta:MacOS2GBmemory,2NoDatabasesizeDB2ExpressWindows,4GBmemory,4IncludesDB2WorkgroupServerWindows,Linux,16GBmemory,4DB2EnterpriseServerWindows,Linux,Solaris,IBMAIX®,HP-LinuxonsystemNolimitonmemoryand

OracleExpressNocostandWindows,Linux,1GBmemory,1Upto4GBuserOracleStandardOneWindows,Linux,2DoesnotincludeOracleStandardWindows,Linux,4OracleEnterpriseWindows,Linux,NolimitonmemoryandDB2设计与性能优 讲师

DATAGURU专业数据分析社DB2引擎包含主引擎处理器的引擎分派单元(EDU)以线程的形式存在并执行各种任 EDU作为应用程序 ，负责执行各种任务，包括处理数据库应用程序请DB2保持多 存在来保证当有服务请求时能够及时响两种主要 :协 ,DB2设计与性能优 讲

DATAGURU专业数据分析社独立的DB2环DB2设计与性能优 讲师

DATAGURU专业数据分析社DB2一些有关实例的指以下多数指令可以在控制中心中进指令指令描例启动默认停止当前db2stop-创建实db2icrt–udb2fenc1删除实db2idrop–f列出所有升级DB2后迁移一个实db2imigr–udb2fenc1安装补丁后升级实db2iupdt–udb2fenc1DB2设计与性能优 讲

DATAGURU专业数据分析社查看数据库管理器配db2getdbmcfgshow更改数据库管理器配db2updatedbmcfgusinghealth_mon查看数据db2getdbcfgfortestdbdb2connecttotestdbdb2getdbcfgshow更改一个数据库设db2updatedbcfgusinglogprimaryDB2设计与性能优 讲

DATAGURU专业数据分析社DB2通过DIAGLEVEL参数来控制日志的数存放于 该日志包含DB2所有错误和警告日志信日志存放于多个文件中(最多5该日志包含性能统计数据和DB2的优化结DB2设计与性能优 讲

DATAGURU专业数据分析社作为实例用户来 中添 节db2db2catalogtcpipnodedb2noderemoteSERVER1server hostname/IP port你也可以db2db2catalogadmintcpipnodedb2dasremote完成以上操作后，可以开始正常使 数据库db2db2catalogdbSAMPLEatnodedb2nodeauthenticationdb2db2connecttoSAMPLEuserdb2inst1usingDB2设计与性能优 讲

DATAGURU专业数据分析社 使用以下命令来显示本 中的结点，DAS和数据库信息databaseadminnodenodeDB2设计与性能优 讲

DATAGURU专业数据分析社

DB2设计与性能优 讲

DATAGURU专业数据分析社 系统管理空数据存放在文件系统 除非需要适用于小型，个人数据库以 空间变化频繁的数据 要求较CREATECREATETABLESPACEtbsp1MANAGEDBYUSING('d:\acc_tbsp','e:\acc_tbsp',DB2设计与性能优 讲

DATAGURU专业数据分析社 数据库管理空 CREATETABLESPACEtbsp2PAGESIZE8KCREATETABLESPACEtbsp2PAGESIZE8KMANAGEDBYUSING(FILE'/storage/dms1'10M)AUTORESIZECREATETABLESPACEtbsp1PAGESIZE8KCREATETABLESPACEtbsp1PAGESIZE8KMANAGEDBYUSING(FILEFILEDB2设计与性能优 讲

DATAGURU专业数据分析社 自 表空数据库允许使用自 数据库管理器能够自动分配容器(物 地址自动调整表空间的大对普通/大型表空间来使用数据库对用户或系统临时表空间使用系统db2db2createdatabaseMYDBon/ASPATHdbpathonCONNECTTOCREATETABLESPACEtbsp1MANAGEDBYAUTOMATICDB2设计与性能优 讲

DATAGURU专业数据分析社 DB2设计与性能优 讲

DATAGURU专业数据分析社 每个表空间 有一个同样页面大小的缓冲 OLTP应用程序设置的表空间需要一个较大的缓冲如果创建表空间时，没有特别指定缓冲池，那么数据库会使用默认的IBMDEFAULTBP缓冲bp4kPAGESIZEtbsp1PAGESIZE4KDB2设计与性能优 讲

DATAGURU专业数据分析社 工具和产品对 IBMDataIBMOptimDatabaseDB2ControlDB2PerformanceSQLIBMDataSQLandProcedureOracleNETNodecataloging/connecting, Queryperformance QueryvisualexplanationHealthMonitorDB2EventMonitor DB2WorkloadWorkloadOracleDataHighAvailabilityandDisasterDB2设计与性能优 讲

DATAGURU专业数据分析社

OracleSGA(shared

AnOracleSIDcanaccessonlyonedatabaseataCommon“Instance”Memorystructureandthebackgroundprocessesusedtoaccessdatafroma“Database”physicalfilesusedtostoreDATAGURU专业数据分析社DB2设计与性能优 讲 DB2和Oracle术语对

AlertDataDatadictionaryCatalogDDataLargeUtilityPfile/spfileDB/DBMAutomaticclientDynamicviews(V_$AdministrationviewsDB2设计与性能优 讲

DATAGURU专业数据分析社 DB2安装与需 Installationdb2setupResponseMinimum1Ghz1GhzTempDB2设计与性能优 讲

DATAGURU专业数据分析社 结

OracleProductdirectoryDatabase

sqlnet.ora,etc.DB2设计与性能优 讲

DATAGURU专业数据分析社 结 Linux/UNIX(Automatic/

DB2InstancedirectoryInstancesoftwaredirectorieslinkedtomainDB2

StoredProcedureDirectory–ExternalandInternalDB2diagnosticlogsandotherlogsInitializationprofileforUnixsInstanceconfigurationparametersbinarySystemDatabasedirectory–CatalogsarekepthereDatabasesarecreatedunderthisnodeLocalDatabaseDatabaseID(directoryforSAMPLEdatabase)AutomaticStoragedirectory(forSAMPLEdatabase)T0000000,T0000001,T0000002–System,Temporary,Usertablespaces B2installlocationMainDB2softwareDATAGURU专业数据分析社DB2设计与性能优

参数配置比 DB29.7hasnearly210PFILEsareneededfordatabaseduringinstance/databasecreationOracleneedseitherPFILESortemplatestocreateaDB2AUTOCONFIGUREfeaturesuggestsdatabaseparametervaluesatdatabasecreationPFILEtoSPFILEconversionis Noconversionisnecessary.TheDBDBMhavethatfunctionalityby DB2设计与性能优 讲

DATAGURU专业数据分析社变量和环境变量比 DB2registry Managedbyusingdb2setortheConfiguration

CommunicationsmanagerSQLsearchespath

db2set-

db2set

CurrentinstanceDefaultinstanceDB2directory

db2set-all

AssignavaluetoavariableResetsavariabletoit'sdefaultDisplaysallvariablesOracleenvironment

[i][i]

CurrentworkingPathtoinitializationparameterfile(init.ora)HomedirectoryinwhichOracleisinstalledOracleDBinstanceonthehostcomputerTop-levelOracledirectory

[g][g]DB2设计与性能优 讲

DATAGURU专业数

[e]EnvironmentVariables–OS[g]GlobalRegistryVariables– tire[i]InstanceRegistryVariables–effectscurrent 数据库创 SetenvironmentCreatepasswordSTARTUPCREATE

SetregistryDB2INST,CREATECreatedataCreateAddtheSID

DB2usesexternalfilenecessaryDB2automaticallyenableSelfTunningManagerenabledbydefault

U

DB2uses“autoconfigure”Nofiles(likeOraclepfile's)areneededDuringthedatabasecreation,whenthecompatibilityvectorisenabled,DB2automaticallybuildsthedictionaryviewsand DB2设计与性能优 讲 实例和数据库管 STARTUP

Startingan

Startinganinstanceand

DB2ACTIVATEALTERDATABASEALTERDATABASE

Startinga

DB2ACTIVATESHUTDOWN

an

SHUTDOWN

aninstance

DB2STOPALTERSYSTEM SESSION DB2设计与性能优 讲

KillingaDATAGURU专业数据分析社

FORCEAPPLICATION[ALL| 管理命 OtherStartthedefaultdb2stop-Createandb2icrt–udb2fenc1Dropandb2idrop–fListallMigrateaninstanceafterupgradingdb2imigr–udb2fenc1db2iupdt–udb2fenc1ViewDatabaseManagerdb2getdbmcfgshowChangeaDatabaseManagerdb2updatedbmcfgusinghealth_mon ViewDatabase

db2getdbcfgfordb2connecttodb2getdbcfgshowChangeaDB DATAGUudDB2设计与性能优 讲 DB2Sample数据 TocreatethesampledatabasepopulatedwithbothrelationaldataandXMLVerifythedatabasecreationbysimplyconnectingandqueryingthedb2sampldb2sampl–sqldb2connecttoForremotedb2db2catalogdatabasesampleasatnodedb2connecttoDB2设计与性能优 讲

DATAGURU专业数据分析社日志: TroubleshootinganddiagnosticLocatedin$DB2INSTANCE_HOME/sqllib/db2dump/byGenerallogwhichcontainsallDB2errorsandUse“db2diag”tooltofilterandformadb2diaglog2

Singlediagnosticlogfile(db2diag.log)

logfilessingleactivelogfilethatgrows y.DEFAULTbehaviorConfiguration

setoffilesthattheactivelogfileclosesandopensdb2diag.N+1.logwhenitreachesthelimitsizeDiagsize:sizeofthelogfilesforrotatinglogfilesform;0forsinglelogfileDiagpath:LocationofthelogDiaglevel:TypesoferrorstobewrittentoDATAGURU专业数据分析社DB2设计与性能优 讲 Oracle字典vsDB2系统编 Oracledictionaryviewswillbecreatedbysettingthe registryvariableOracleDataSYS.xx$-BaseSYS.GV_$-AdminviewsSYS.V_$-AdminviewsUSER_$-UsersALL_$-ExpandedusersviewsDBA_$-DatabaseAdminviewsSELECT<column>FROM

DB2SystemSYSIBM-BasetablesSYSIBMADM–AdminviewsSYSCAT–CatalogbasetablesSYSSTAT–UpdateableviewsSELECT<column>FROMWHEREOWNER=userANDSELECT<column>FROMsyscat.tablesWHEREOWNER=userANDDB2设计与性能优 讲

DATAGURU专模型比

Table

Logical

Table

DB2设计与性能优 讲

DATAGURU专业数

模型比较–表空 Table–

PermanentTableSmallfile(severalsmallTemporaryTableLocallyManagedUndoTable

SystemManagedDB2设计与性能优 讲

DATAGURU专业数据分析社 DB2表空间类 Catalogtablespaces(1CatalogtableswithMustDB2设计与性能优 讲

Systemtemporarytablespace(1forexample:join,sortsUserTableSpaces(1ormoreDefaultusertableCanbetabl

Usertemporarytable(1ormoreStoretempdatafromtablescreatedwithDECLARETEMPORARYTABLE DB2表空间管 SystemManagedOraclehasNODatastoredinfilesrepresentingdataSpaceisallocatedonAccessto ontrolledusingstandardI/OfunctionsoftheIdealfor aldatabasesanddatabasesthatgrow/shrinkLowerperformance,astheOShandlestheCREATECREATETABLESPACEbyeoracleMANAGEDBYUSING('d:\acc_tbsp','e:\acc_tbsp',AllOracletablespacesworkslikeDB2'sDatastoredinfilesoronraw–Storagespacepre-allocatedinfilesystem,typicallycontiguousphysicallyIdealforperformance-sensitiveapplicationsCREATETABLESPACEbyeoracleMANAGEDCREATETABLESPACEbyeoracleMANAGEDBYUSING(FILE'/db2/file1'1M,FILE'/db2/file2'1AUTORESIZEYESINCREASESIZE2MMAXSIZE100DB2设计与性能优 讲

DATAGURU专业数据分析社 DB2自 表空 AutomaticStorageTableDBMcreatesandextendscontainersasneededupthelimitsimposedbythestoragepathsassociatedwiththedatabaseAutomaticallyhandlesresizingtableCreatesaDMStablespaceforregular/largetableCreatesaSMStablespaceforuserorsystemtemporarytableNewDB&arehandledbyautomaticstoragebyDEFAULTdb2createdatabaseMYDBon/ASPATHdbpathon/DBPATHDB2设计与性能优 讲

DATAGURU专业数据分析社AnIllustrativeLet’swalkthroughthefollowingsetdb2createdatabaseMYDBon/ASPATHdbpathondb2createtablespaceTS1managedbysystemusing('/MYDIR1','/MYDIR2')extentsize4DB2设计与性能优 讲

DATAGURU专业数据分析社AnIllustrativeLet’swalkthroughthefollowingset ;db2createdatabaseMYDBon/ASPATHdbpathondb2createtablespaceTS1managedbysystemusing('/MYDIR1','/MYDIR2')extentsize4DB2设计与性能优 讲

DATAGURU专业数据分析社WhatGetsCreatedOnDisksetDB2INSTANCE=MYINST;db2createdatabaseMYDBon/ASPATHdbpathon/DBPATH/MYINST/NODE0000/SQL00001SQLOGDIR/event

//bufferpoolinfo//tablespaceinfo//automaticstoragepathinformation//databaseconfiguration//recoveryhistoryfile//logcontrolfile//usedwithlog//tablespaceloghistory//logdirectory//logfilesgo//defaultpathforeventmonitor/ASPATH/MYINST/NODE0000/MYDBT0000000/C0000000.CATT0000001/C0000000.TMP/T0000002/C0000000.USR

//initialcatalogtablespace//initialsystemtemptablespace//initialusertablespaceDB2设计与性能优 讲

DATAGURU专业数据分析社AnIllustrativeLet’swalkthroughthefollowingsetDB2INSTANCE=MYINST;db2createdatabaseMYDBon/ASPATHdbpathon/DBPATHdb2createtablespaceTS1managedbysystemusing('/MYDIR1','/MYDIR2')extentsizeDB2设计与性能优 讲

DATAGURU专业数据分析社WhatGetsCreatedOnDiskdb2createtablespaceTS1managedbyusing('/MYDIR1','/MYDIR2')extentsizeFirstFirstExtentofDataPagesforT1SecondExtentofDataPagesforT1DB2设计与性能优 讲

DATAGURU专业数据分析社 AreaofmainmemoryusedtocachetableandindexEachdatabasemusthaveatleastonebufferBufferpoolscanbecreated,droppedorSYSCAT.BUFFERPOOLScatalogviewaccessesinformationforthebufferpoolsdefinedintheEverytablespaceassociatesaspecificbufferpoolofthesamepagesizeMatchbufferpoolsizewithpurposeoftabletoincreasehitratioCREATECREATEBUFFERPOOLbp4kPAGESIZECREATETABLESPACEtbsp1PAGESIZE4KBUFFERPOOLDB2设计与性能优 讲

DATAGURU专业数据分析社 DB2ConfigurationAdvisorusesexpertheuristicstoperformanceandtobalancememoryAutomaticenvironmentcharacteristicUserdescriptionofbasicoperationalCombinedwithmathematicalmodelofeachconfigurationparameter,basedonexpertheuristicsTousetheConfigurationAdvisor,specifytheAUTOCONFIGUREcommandforanexistingdatabase,TheAUTOCONFIGUREisrunbydefaultwhenyouissuetheCREATEDATABASEcommand.Inside

ofthe

n

hasonlyafewdatabasetemplatesandwonthelpyoutosettheperformanceparametersanonce,inonetoolDBCA

chesettingsall1–createaparameterandpasswordfiles.2-Usetheparameterfiletobuildaninstance. 3-IssuetheCREATEDB2设计与性能优 讲

4RunSQLscriptstogeneratethedatadictionary.5RunSQLscriptstogeneratetheEnterpriseManagerDatabase Toolthatsuggestsmodificationstothedatabase'sphysicaldesigntoimproveAllowstodisplay,edit,orsavethenewdatabaseobjectcreation Invokedwiththedb2advisImplementationof mendationsbyIndexesonthebaseIndexesontheMaterializedQueryConvertingregulartablesintoMulti-DimensionalClustering(MDC) Re-partitioning(DPF)existingtableswithanewsetofpartitioningkeyand/orinanewtablespace.DB2设计与性能优 讲

DATAGURU专业数据分析社 CreateadatabasewithautomaticstorageCREATEDATABASE<dbname>AUTOMATICYESON/data/storagePath1,

DB2willcreatethecontainersinsidethesepathsCreateatablespacewithautomaticstorageCREATETABLESPACETS2MANAGEDBYAUTOMATICSTORAGEINITIALSIZE500KINCREASESIZE100K MAXSIZE100M

resizeofDMStablespacesDB2设计与性能优 讲

DATAGURU专业数据分析社 DatabasemaintenanceisessentialforensuringoptimizedperformanceandTherefore,settingupautomaticmaintenanceremovesburdenfromDBAtohavetomanuallydothesetasksAUTOMAINTparameteristhemasteron/offIndividualChildparameterscanbesettoON/OFFandthesettingsarepersistedinthedatabaseconfigurationfile.Enabled/disabledthroughdatabaseconfigurationparameterAutomaticAutomaticdatabasebackupAutomaticAutomaticstatisticsprofilingAutomaticreorganizationDATAGURU专业数据分析社DB2设计与性能优 讲 SelfTuningMemoryManager(STMM)simplifiesthetaskofmemoryOptimizestheperformanceofyourdatabasebyautomaticallyadjustingthevaluesMemoryconfigurationparametersininstanceMemoryconfigurationparametersindatabaseSizeofbufferHands-offonlinememoryRequiresnoDBASensestheunderlyingworkloadandtunesthememorybasedonCanadaptquicklytoworkloadshiftsthatrequirememoryDB2设计与性能优 讲

DATAGURU专业数据分析社 IfupgradingfromolderversionofDB2orpreviouslyturnedoffTurnonthemasterupdatedbcfgfor<db_name>usingSELF_TUNING_MEMSeteachparameterthatyouwishtotunetoupdatedbcfgfor<db_name>using<param>ForbufferpoolstobetunedbyAllnewlycreatedbufferpoolsdefaulttoFormigrateddatabase'salterbufferpool<bp_name>sizeToquerywhetherornotSTMMistuningyourConnecttotheGetDatabaseConfigurationwithgetdbcfgfor<db_name>showCheckthevalueforIfsettoON(ACTIVE)thensystemisbeingIfsettoOFF,orON(INACTIVE)thensystemisnotbeingDB2设计与性能优 讲

DATAGURU专业数据分析社STMMworksintwomodesdependingontheconfigurationoftheDATABASE_MEMORYparameter–TotalamountofmemoryusedbyaparticularDB2databasecangroworshrinkovertimememoryto,theOSas

COMPUTEDor–Memorytuningstilloccursbuttotalmemoryusedbydatabaseisconstant–ForoneheaptogrowanotherheapmustshrinkDB2设计与性能优 讲

DATAGURU专业数据分析社 DATABASE_MEMORY=1)Scenarioinvolvesachangeinworkloadthatnowrequiresmorememoryfor

MemoryusedotherTotalSystemTotalSystemDB2databasememoryDB2databasememoryOtherDBmemoryDB2设计与性能优 讲

DATAGURU专业数据分析社 DATABASE_MEMORY=ScenarioinvolvesachangeinworkloadthatnowrequiresmorememoryforDB2requestsandgetsmorememoryfromtheOS,thereforeshrinkingthefreememoryinthesystem

MemoryusedotherinsystemTotalSystemDB2TotalSystemDB2databasememoryOtherDBmemoryDB2设计与性能优 讲

DATAGURU专业数据分析社 DATABASE_MEMORY=ScenarioinvolvesachangeinworkloadthatnowrequiresmorememoryforTotalTotalSystemDB2usesnewlyacquiredmemoryinsortheap

MemoryusedotherinsystemDatabasesortDB2databasememoryOtherDB2databasememoryDB2设计与性能优 讲

DATAGURU专业数据分析社 DATABASE_MEMORY=COMPUTEDor1)Scenarioinvolvesachangeinworkloadthatnowrequiresmorememoryfor

MemoryusedotherTotalSystemTotalSystemDB2databasememoryDB2databasememoryDB2设计与性能优 讲

DATAGURU专业数据分析社

OtherDBmemory DATABASE_MEMORY=COMPUTEDorScenarioinvolvesachangeinworkloadthatnowrequiresmorememoryforDB2databaseissetatfixedmemoryusage,andthuscannottakememoryfromOS.Thereforeidentifiesanothermemoryconsumerinthedatabasethatdoesnotneeditsmemoryanymore(e.g.locklist)

MemoryusedotherinsystemTotalSystemDB2TotalSystemDB2databasememoryDatabaselockDB2设计与性能优 讲

DATAGURU专业数据分析社

OtherDBmemo