搜索
5DBA3468createtable()DBA5.1:(p1an)(create)(monitor)(tune):(culturalprocess)(managementprocess)(technology)1):DBA2):3):DBA5.2DBADBA/:•••98••SQL•••••DBADBADBADBA5.3?5.3.125developmentsystemtest(stresstest)acceptancetestproduction)OracleDesignerCASECASECASEDBA5.3.2OracleCONNECTRESOURCEDBACONNECTRESOURCEDBA1.CONNECTCONNECTCREATESESSI0NCONNECT:ALTERSESSIONCREATECLUSTERCREATEDATABASELINKCREATESEQUENCECREATESYNONYMCREATETABLECREATEVIEW()RESOURCE9CONNECTCONNECTAPPLICATION_USERCREATESESSI0N:2.RESOURCERESOURCE:CREATECLUSTERCREATEINDEXTYPECREATEOPERATORCREATEPROCEDURECREATESEQUENCECREATETABLECREATETRIGGERCREATETYPERESOURCEUNLIMITEDTABLESPACERESOURCEPL/SQLObjectsOptionRESOURCECREATETYPE599RESOURCERESOURCECONNECTRESOURCE3.DBADBAwithadminoptionwithadminoptionDBADBADBADBADBA4.DBARESOURCE5.3.3?deliverable••••••••1.(entityrelationshipdiagramE-R)E-RE-R12.(physicaldatabasediagram)100DDL1I/O33.(spacerequirement)5.3.74.DBADBA5.(9Oracle)DBADBA6.(10)7.(Executionplan)8explainplansetautotrace51018.5.3.4Oracle8iOracle8iDBAstoredoutlineDatabaseResourceManagerDatabaseResourceManagerDBA1.DatabaseResourceManagerDatabaseResourceManager75%CPU25%DatabaseResourceManagerresourceplanresourceconsumergroupresourceplandirectivecatproc.sqlcatrm.sqlDatabaseResourceManagerDatabaseResourceManager(pendingarea)DBMS_RESOURCE_MANAGECREATE_PENDING_AREAVALIDATE_PENDING_AREASUBMIT_PENDING_AREACLEAR_PENDING_AREAexecuteDBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();CREATE_PENDING_AREADBMS_RESOURCE_MANAGERCREATE_PLANOracle8iPL/SQLSQLCREATE_PLAN102PlanCPUCPUDEVELOPERS-SQL*PlusADMINISTER_RESOURCE_MANAGERDBAwithadminoptionDBADBMS_RESOURCE_MANAGER_PRIVSGRANT_SYSTEM_PRIVILEGEMARTHADatabaseResourceManagerDBMS_RESOURCE_MANAGER_PRIVSREVOKE_SYSTEM_PRIVILEGEMARTHAADMINISTER_RESOURCE_MANAGERDBMS_RESOURCE_MANAGERCREATE_CONSUMER_GROUPCREATE_CONSUMER_GROUPDBMS_RESOURCE_MANAGERCREATE_PLAN_DIRECTIVECREATE_PLAN_DIRECTIVE5103CREATE_PLAN_DIRECTIVECPUCPUCPU75%CPU50%50%CPUCREATE_PLAN_DIRECTIVECPUDEVELOPERSOnline_developersBatch_developersCPU12DBMS_RESOURCE_MANAGERSET_INITIAL_CONSUMER_GROUPSET_INITIAL_CONSUMER_GROUPSET_INITIAL_CONSUMER_GROUPDEFAULT_CONSUMER_GROUPResourceManagerinit.oraRESOURCE_MANAGER_PLAN104RESOURCE_MANAGER_PLANaltersystemsetinitial_consumer_groupDAYTIME_USERSBATCH_USERSaltersystemsetinitial_consumer_group=DAYTIME_USERS;altersystemsetinitial_consumer_group=BATCH_USERSUPDATE_PLANDELETE_PLANDELETE_PLAN_CASCADEUPDATE_CONSUMER_GROUPDELETE_CONSUMER_GROUPUPDATE_PLAN_DIRECTIVEDELETE_PLAN_DIRECTIVEDBMS_RESOURCE_MANAGERCREATE_PENDING_AREAVALIDATE_PENDING_AREASUBMIT_PENDING_AREACLEAR_PENDING_AREAexecuteDBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA();executeDBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();2.1)2)3)4)5)OracleOracleOracle8istoredoutline5105init.oraCREATE_STORED_OUTLINESTRUECREATE_STORED_OUTLINESTRUEDEFAULTinit.oraCREATE_STORED_OUTLINESCREATE_STORED_OUTLINES=developmentDEVELOPMENTCREATEANYOUTLINEcreateoutlineinit.oraCREATE_STORED_OUTLINESTRUEOraclecreateoutlineDMLcreatetableasselectalteroutlinerebuildalteroutlineYTD_SALESrebuild;alteroutlinerenamealteroutlineYTD_SALESrenametoYTD_SALES_REGION;changecategoryalteroutlineYTD_SALES_REGIONchangecategorytoDEFAULT;init.oraUSE_STORED_OUTLINESTRUEDEVELOPMENTaltersessionUSE_STORED_OUTLINES.OUTLN_PKGOUTLN_PKG•••OUTLN_PKGDROP_UNUSEDexecuteOUTLN_PKG.DROP_UNUSED;1065107DROP_BY_CATDROP_BY_CATDEVELOPMENTUPDATE_BY_CAT5.3.5pctfreepctused1.1)2)3)I/O4)Oracle2.OracleOracleOracleOracle1)Oracle2)Oracle3)Oracle4KBinitialnextpctincreaseSmallTab7K7K0MediumTab103K103K20DBAOracleSmallTabDBA7KB4KBOracle8KB8KBOracle20KBOraclenext7KBMediumTabinitial103KBOracle104KB26OracleOracle302630OracleMediumTabMediumTabOracle30120KBMediumTabOraclenextMediumTab20pctincrease20%Oracle36301.240Oraclenextnext103KB20%123.6KB4KB123.6KB30.93135SmallTabMediumTabinitialnextpctincreaseSmallTab7K7K020KB20KB20KBMediumTab103K103K20120KB120KB140KBDBASmallTab14KB40KBMediumTab206KB240KBMediumTabpctincrease20%16.6%OracleOracleOracle3.I/OOracleRowIDRowIDOracleRowIDOracleinit.oraDB_FILE_MULTIBLOCK_READ_COUNTI/O4KBI/O64KB16DB_FILE_MULTIBLOCK_READ_COUNT16Oracle108I/O64KB64KB1064KBI/O64KBOracle1064KB640KBOracle10I/O640KB80KBOracle64KB16KBOracle1610860%!1)I/OI/O2)I/OI/O64KB64KB128KB192KB256KB4.Oracle100KBOracle100KB100KBpctincreaseOracle100KBOracle100KB60KBOracle100KB60KB40KB40%64KB128KB192KB256KB320KB384KB•64KB•128KB64KB•192KB128KB•192KB256KB•320KB384KB256KB510964KB128KB256KB512KB1MB2MB4MB8MB16MB32MBI/O5.Oracle4KB1632641282565121024204840968192Oracle2035651302605151025205041008195Oracle,Oracle32Oracle3232356.••pctfree••init.oraDB_BLOCK_SIZEcreatedatabase90204819584096400681928102pctfreeinsertpctfree4KBpctfree104006-(0.14006)=3605.436053605DATENUMBERVARCHAR2DATENUMBER31101060036053605/600=62500025000/6=416641661)16MB(4096)initial512KB(128)next2)32MBinitial422441667.••pctfree••init.oraDB_BLOCK_SIZE161204818874096393581928031pctfreepctfreepctfree4KBpctfree3935-(0.023935)=3856.338563856DATENUMBER4VARCHAR2DATENUMBER351111738563856/17=2262500025000/226=111111444KB1)256KBinitial64KBnextminexte