oracle-ocp-043试题分类汇总

性能调优1.Youobservethatadatabaseperformancehasdegradedoveraperiodoftime.Whileinvestigatingthereason,youfindthesizeofthedatabasebuffercacheisnotlargeenoughtocachealltheneededdatablocks.Whichadvisorycomponentwoldyoureferto,inordertodeterminethatrequiredsizeofthedatabasebuffercache?A.MemoryAdvisorB.SegmentAdvisorC.SQLTuningAdvisorD.SQLAccessAdvisorE.AutomaticDatabaseDiagnosticMonitor(ADDM)A发现数据库运行一段时间后性能下降了，调查发现原因是因为buffercache不能装下所有需要的数据块，用个工具确定数据库所需要的buffercache的大小。2.Exhibit--显示所有日志在同一个目录下Inyourproductiondatabase,thetotalwaitsandthetimewaitedforlogfileparallelwritearesignificantlyhigh.Whileinvestigatingthereason,youfindthattherearethreeredologgroupswithtwomembersineachgroup,andallredologmembersareplacesonasinglephysicaldisk.Whatactionwouldyoutaketominimizethewaits?A.StartthelogwriterslaveprocessesB.IncreasethenumberofredologfilesC.IncreasethesizeoftheredologbufferD.Placetheredologfilesonthedifferentdisks.E.Increasethenumberoflogwriterprocesses.Answer:D等待并行写日志的时间明显很高。调查发现三个日志文件组，每组由两个成员，都在同一个物理磁盘上。通过什么方法解决问题？--主要是不懂题目意思会出错。24.YouaredesigninganapplicationforCertkiller.comandyouhavebeenaskedtodesignadatabasetabletofacilitatemonthlybillgeneration.Thebillwouldincludedetailsofcustomercalls,listedinchronologicalorder.Whichmethodwouldyoufollowtoachievethisobjectivewithoutincreasingtheoverheadofsortingtherows?A.createahashclustertostorethedataB.createanindexclustertostorethedataC.createapartitionedtabletostorethedataD.createasortedhashclustertostorethedataE.createaheaptablewithrowidtostorethedataAnswer:D27.Inwhichscenarioswouldyourebuildanindex?(Chooseallthatapply.)A.whenyouneedtodisabletheindexusageB.whenyouneedtochangestorageoptionsC.whenyouneedtoenableindexmonitoringD.whenyouneedtomovetheindextoanothertablespaceAnswer:B,D35.Whiledesigningyourdatabase,youhavecreatedtheEMPLOYEEStableasanindex-organized(IOT).YouwanttocreateabitmapindexontheJOD_IDcolumntomakequeriesfaster.Whichtaskmusthavebeencompletedsothatyouareabletocreatethebitmapindex?A.Aprimarykeymusthavebeencreated.B.Amappingtablemusthavebeencreated.C.Anoverflowtablespacemusthavebeenspecified.D.ThePCTTHRESHOLDoptionmusthavebeenspecified.Answer:B51.YouwanttousetheSQLTuningAdvisortogeneraterecommendationsforbadlywrittenSQLstatementsinyourdevelopmentenvironment.Whichthreesourcescanyouselectfortheadvisortoanalyze?(Choosethree.)A.TopSQLB.snapshotsC.SQLTuningsetsD.indexaccesspathE.optimizerstatisticsF.materializedviewlogsAnswer:A,B,C54.Youfindthattheexecutiontimeofreportsinyourdatawarehouseapplicationissignificantlyhigh.Yoususpectthelackofindexestobethereasonforthedegradationinperformance.Whichadvisorycomponentwouldyoureferto,inordertodeterminetheappropriateindexes?A.MemoryAdvisorB.SegmentAdvisorC.SQLAccessAdvisorD.AutomaticWorkloadRepository(AWR)E.AutomaticDatabaseDiagnosticMonitor(ADDM)Answer:C你发现你的数据仓库中的执行报告时间非常慢，你怀疑是缺少相应的索引，可以使用SQLAccessAdvisor（SQL访问指导）SQL访问指导：处理方案问题并确定最佳数据访问路径（如索引和实体化视图）。实体化视图=物化试图物化视图是包括一个查询结果的数据库对像，它是远程数据的的本地副本，或者用来生成基于数据表求和的汇总表。物化视图存储基于远程表的数据，也可以称为快照。68.Younoticedthattheindextablespaceinyourdatabaserequiresarecovery.However,insteadofperformingamediarecovery,youdecidedtore-createtheindexesinanewtablespace.Whichtwooptionswouldyouusetoreducethetimeittakestore-createtheindexes?(Choosetwo.)A.ONLINEB.REVERSEC.PARALLELD.COMPRESSE.NOLOGGINGF.COMPUTESTATISTICSAnswer:C,E关于控制文件恢复3.Oneofthetablespaceisread-onlyinyourdatabase.Thelossofallcontrolfileforcedyoutorecreatethecontrolfile.Whichoperationdoyouneedtoperformafterre-creatingthecontrolfileandopeningthedatabase?A.Dropandre-createtheread-onlytablespacesB.Renametheread-onlydatafiletotheircorrectfilenames.C.Changethetablespacestatusfromread/writetoread-only.D.Re-createtheread-onlytablespacebecauseitisautomaticallyremoved.Answer:B本来就是readonly，控制文件丢失，重建了控制文件，重新打开数据库的时候需要做什么？重命名那个表空间对应的文件就可以了。这里是将readonly,如果是普通情况？道理在什么地方？日志7.Inyourdatabase,onlineredologfilesaremultiplexedandoneofthemembersinagroupislostduetomediafailure?Howwouldyourecoverthelostredologmember?A.importthedatabasefromthelastexportB.restoreallthemembersinthegroupfromthelastbackupC.dropthelostmemberfromthedatabaseandthenaddanewmembertothegroupD.restoreallthedatabasefilesfromthebackupandthenperformacompleterecoveryE.restoreallthedatabasefilesfromthebackupandthenperformanincompleterecoveryAnswer:CRedogroup里如果有多个成员，那么他们是冗余的。每个成员里的内容都一样，所以删除再添加一个即可。19.YouareusinganAutomaticStorageManagement(ASM)instancetomanagethefilesofyourproductiondatabase.Youhavetwodiskgroups,DG1andDG2withonedeviceeach.Intheparameterfileoftheproductiondatabase,thefollowingparametershavebeenspecified:DB_CREATE_ONLINE_LOG_DEST_1='+dg1'DB_CREATE_ONLINE_LOG_DEST_2='+dg2'Whatwouldbetheimpactofthissetting?A.Whenanewloggroupisadded,itwouldhaveonememberineachdiskgroup.B.Whenanewloggroupisadded,itwouldhavetwomembersineachdiskgroup.C.Whenanewtablespaceisadded,itwouldhaveonedatafileineachdiskgroup.D.Whenanewlogfileisadded,itwouldhaveonememberspreadacrossthediskgroups.Answer:ADB_CREATE_ONLINE_LOG_DEST_1='+dg1'DB_CREATE_ONLINE_LOG_DEST_2='+dg2'当一个新的日志组加入了以后，会在每个磁盘组中有个成员。Editor’snotes:DB_CREATE_O