数据库程序员面试分类真题15.docx

《数据库程序员面试分类真题15.docx》由会员分享，可在线阅读，更多相关《数据库程序员面试分类真题15.docx（26页珍藏版）》请在课桌文档上搜索。

1、数据库程序员面试分类真题15倚答题1. 执行计划里的access和filter有什么区别？正确答案：如下所示：PredicateInformation（identifiedbyop（江南博哥）erationid）:4- access（*EMPNO=B.MGR）filter（*A*.*EMPNO*=*B*.*MGR*）5-filterB*.*MGR*ISNOTNU1.1.）一般而言，access表示这个谓词条件的值将会影响数据的访问路径（表还是索引）：filter表示谓词条件的值不会影响数据的访问路径，只起到过滤的作用。NOTIN或VIN函数等容易产生filler操作。对于filter而言，如果

2、只有一个子节点，那么就是简单过滤操作（独立操作）。如果有两个或更多子节点，那么就是类似WStCd1.ooPS操作，只不过与NeSted1.OoPS差别在于，filter内部会构建HASH表,对于重复匹配的，不会再次进行循环查找，而是利用已有结果，提高效率。但是一旦重复匹配的较少，循环次数多，那么，filter操作将严重影响性能的操作，可能会导致目标SQ1.几天都执行不完。考点SQ1.优化2. 统计信息包括哪几类？正确答案：Oracle数据库里的统计信息是一组存储在数据字典里，且从多个维度描述了数据库里对象的详细信息的一组数据。当OraCIe数据库工作在CBo（COStBasedOptimiza

3、tion,基于代价的优化器）模式下时，优化器会根据数据字典中记录的对象的统计信息来评估SQ1.语句的不同执行计划的成本，从而找到最优或者是相对最优的执行计划。所以，可以说，SQ1.语句的执行计划由统计信息来决定，若没有统计信息则会采取动态采样的方式来生成执行计划。统计信息决定着SQ1.的执行计划的正确性，属于SQ1.执行的指导思想。统计信息主要包括6种类型，其中表、列和索引的统计信息也可以统称为普通对象的统计信息，如下图所示。分炎髓睚三H懒硒懒懿字尴依目内蜀悔出2表列蚓帆W包括Ii(NI礼RoWS)、赧B1.OCKS).疗平均长H做RW1.1.EN)插蒯的i魄G小包助帆T的敦R(NuM_DIS

4、BNCThNUU侦的S(NIA1.NU1.1.S)./(JffiMSToGRAM,遭括分植的晦雷就(就旅)、姒,姆，傩、r,期娥计G(httndcdSUBtiQ)雕用脚悻做I幽网0(1.EAF.B1.OCKS).费切(BIBrE1.).样”的个收lDisnctKeys).矍fTRUE);阿痈表聊t区进欣籁确强;DBMS.STATSGffliERTAB1.ESTATStUSEKTBNAMEiPARfNAME=TJkrAS,GRANU1.三V=WKlT110y,CASCAbE=W蚓e三fDBMS_STArSMKrjniWliSERJMJENAMEl;EXECDBNB-STATSGfflER.SYs

5、TCMST欣n,:船麟斤郑桐ExecdbxkstaisgwjerSYSTB1.ST加,螂)或iEXECDBMS_SMR_GTHER_SYSTEMSTATGA11三NGMODEOlXTERW1.NIRmM)-三RW1.为邮SM长,I助州全股矩DBMSStatsgatheR.D)CnONARY疝DBMS.STATS.GATHERTAB1.E.SlTS颈媒DBMSSTATS.GATHER.FlXEDOeJECTSSWS例依:DBMS_STArSGXrHERTABlf_STArSdBmsStxtsdeletetableSTA耶HVTBNAME)DBmsstaideleteindexSMlS(USERj

6、NDXNAMEIH弓！修改：DBMS_STATSSCTSysiblstar1:DBMS_STANDE1.ETzSYSTB1.sTANDBMSSlXNDfl三DICnCNARY.STAIS网$_STATS.DE1.ETEFIXEDOJECIS,STS考点统计信息3. OracleIOg和Ilg的自动统计信息收集机制有哪些不同？正确答案：在OraCleIOg之前并没有自动收集统计信息的机制，从OraCIeIOg开始引入了自动收集统计信息的功能，这个功能在OraCle】0g中被称为自动统计信息收集(AUtomatiCStatisticsGathering),在OraCIeIIg中被称为自动优化器统计

7、信息收集(AUtomatiCOptimizerStatisticsCollection)t.自动统计信息收集作业能够每天收集普通对象和数据字典的统计信息，但不会收集乂$系列表的内部对象统计信息。Oracle的初始化参数STTISTCSJ.EVEl.控制收集统计信息的级别，有三个参数值：1)BASIC：收集基本的统计信息。2)TYPICA1.：收集大部分统计信息(数据库的默认设置).3)A1.1.：收集全部统计信息。当使用OracleFl动收集统计信息时，必须要确保OraCle的参数STAT1ST1CS_1.EVE1.的值为TYPICA1.或者A1.1.。默认值为TYPICA1.,该值可以确保数

8、据库自我管理功能所需求的所有主要统计信息的正确收集，及提供最好的综合性能。这个默认值能胜任大多数的环境，并且OraCIe不推荐去修改该值。DBA可以根据Oracle提供的脚本$0RAC1.EJoMErdbmsadmin/CalmWin.sq查看统计信息收集作业的整体搭建流程。有兴趣的读者可以研究下此脚本的内容。OracleIOg和IIg的自动统计信息收集机制有所不同,详见下表。mOwleIlg中的自燃加收箝业联自动运疗任务GATHERSgPROG亲魏，次竦酬候鞋先生成名为ORASA1.OSgTlXxX的作也断瞰惚州妹的徽财驰，财.她研DeMSSTANGATHERDXrABASESraS0JOB

9、JROC,哦序斯朝DeMvUTOJASjMMH)IATEGm风0P11112ER-SATS料做政iH1JJOB关腼令可疑护冒口有再个.Whkmgiotndow利WEEXENiWNXW,H同为DWIm0期第二瓶晟6苴,艘8例2)所有的麻喇跚摊天政(ffiitw,三三6.)OlXk撤拥我用Wl如能城液林独髓林Caid(Il中怕就懒ft臬判却何以现辨股级娴 ExecdbmsSCHEDllEilESAB邸YSiGmERS1JOBtA1.TER疝SET”OPTIMIZERAUTOSTArSjoB卡位SCoPE=SPF execdBmsSchediierdisablecsys.GmIER_飒颂 A1.TH

10、SSTENfSETIoPTMZERAUT0SW.J0e,toqxinwNUll,wii)jorume*Nll1.); EXECDSMS_AuTOjAS“DMNDBAB1.EO； EXECDBMIAUT6j就ADMINDlSABIWJ三=actoqti三tscolfcctxn,j)perajcnNll1.wiown三ne=NUm一SE1.ECTOJEKrKME,STATUSFROMDBA_AiTOUSK_CUENTWHERECUE-NAEattcc三zcrSSSCoIlaSo11;SE1.ECT三DOWNAMETOCHAR(WN)OWNEXTIMIYYYY-MM-DDHH24iHSS)OPTIM

11、IZERSTTRUE)普通表DBMS_STATS.GATHER_TAB1.E_STATS(USER,TB.NAME,PARTNAME=PT_PART_NAME,GRANU1.ARITY=PARTITION*,CASCADE=TRUE);一针对分区表的单个各区进行收集统计信息除此之外，还有一些其他的用法，如卜丁DEXECDBMSSTATS.GATHERDATABASESTATS(USER)；收集当前数据库下所有用户的统计信息。2)EXECDBMS_STATS.GATHER_SCHEMA_STATS(USER)：收集当前数据库用户下所有对象的林计信息。当系统的分区表数据量很大时，如果每次都收集全部

12、的分区必然会导致统计信息的收集非常慢，在OraCleIlg之后可以通过设置INCREMENTA1.来只针对数据有变动的分区做收集：EXECDBMS_STATS.SET_TAB1.E_PREFS(USER,TAB1.E_NAME,INCREMENTA1.*,TRUE*);-只收集数据变的的分区SE1.ECTDBMSSTATS.GETPREFS(,INCREMENTA1.*,NU1.1.,TAB1.ENME,)FROMDUA1.;一查看分区表INCREVETA1.的值考点统计信息5. 什么是动态采样(DynamiCSampling)?正确答案：时于没有收集统计信息的表，OraCIe为了能够得到相对

13、准确的执行计划，会在执行SQ1.之前对SQ1.语句涉及的衣做动态采样(DynamicSampling,从OraclellgR2开始称为DynamiCStatistic).有两种方法可以开启动态采样：1)将参数OPTlVIZEkDYNAMIJSAMP1.NG的值设为大于或等于1,从OracleIog开始，该值默认为2,若设置为0,则禁用动态采样。2)使用动态采样的Hint：DYNAMICSAMP1.ING(T1.EVE1.)该Hint表示对目标表T强制使用等级为参数Ievel指定值的动态采样。默认采样数据块数量受隐含参数“OPTlMIZERDYNSMPB1.KS”的控制，其默认值是32,表示动态

14、采样时默认采样数据块数量为32考点统计信息6. 动态采样的作用有哪几个方面？正确答案：引入动态采样有如下几方面的作用：I)CBO依赖的是充分的统计信息，但是并不是每个用户都会非常认真、及时地去对每个表做分析。为了保证执行计划都尽可能地准确，Oracle需要使用动态采样技术来帮助CBO获取尽可能多的信息。2）全局临时表。通常来讲，临时表的数据是不做分析的，但是当一个查询关联到这样的临时表时，CBO要想获得临时表上的统计信息分析数据，就只能依赖于动态采样了。3）为了相对准确地估算出当目标SQ1.语句WHERE条件中出现有关联关系的列时整个WHERE条件的组合可选择率，进而能相对准确地估算出返回结果

15、集的Cardinality%动态采样除了可以在段对象没有分析时，给CBO提供分析数据之外，还可以对不同列之间的相关性做统计。考点统计信息7. 直方图（HiStogram）的意义是什么？直方图的适用场合有哪些？正确答案：在Oracle数据库中，CBO会默认认为H标列的数据在其最小值（IQWA1.UE）和最大值（HIGI1.VA1.UE）之间是均匀分布的，并且会按照这个均匀分布原则来计和对目标列施加WHERE查询条件后的可选择率以及结果集的CardinaIiIy,进而据此来计算成本值并选择执行计划。但目标列的数据是均匀分布这个原则并不总是正确的，在实际的系统中，可以很容易地发现有一些目标列的数据分

16、布是不均匀的，甚至是极度倾斜、分布极度不均衡的。对这样的列如果还按照均匀分布的原则去计算可选择率与CardinaIity,并据此来计算成木、选择执行计划，那么CBo所选择的执行计划就可能是不合理的，甚至是错误的，所以此时应该收集列的直方图。直方图是-种列的特殊的统计信息，主要用来描述列上的数据分布情况。当数据分布倾斜时，直方图可以有效地提升Cardinality评估的准确度。构造直方图最主要的原因就是帮助优化器在式中数据严重偏斜时做出更好的规划。例如，表中的某个列上，其中的某个值占据了数据行的80FORCO1.UMNSS1.SIZE1”，但这却得再次收集表的统计信息，十分不合理。所以，在Ora

17、CIeIlg中，有如下方法可以直接删除宜方图信息：EXECDBMS_STATS.DE1.ETE_CO1.UMN_STATS(USER,T_ST_20170604_1.HR,SA1.,CO1._STAT_TYPE=H1STOGR,);蓑中，Co1.SATJYPE默认五Al1.表示删除列的基本统计信息和直方图信息。考点统计信息11. 基表CO1.USAGES的作用是什么？正确答案：从OraCIe9i开始引入了SYS.CO1.USAGE$表用来跟踪列的使用情况,该功能通过隐含参数“工O1.UMNJrRACKlM1.1.EVE1.”来控制。若隐含参数“_CO1.l；MN_TRACKNG_1.EVE1.

18、”的植为。则取消该功能，若隐含参数m_CO1.IMN_TKACK1NG_1.EVE1.的值为1则表示该功能生效。默认情况下，该功能谑生效的，并且CBO负责将SQ1.语句中WHERE条件的查询谓词信息保存在该衣中，数据库在执行SHUTDoWNNoRMA1.或者SHUTDOWNIMvEDIATE会自动将该表中的数据清空。考点统计信息12. 新建索引后统计信息是否自动收集？正确答案：在OraCIeIOg后有个隐含参数“OPTIMIZERCOMPUTEINDEXSTATSv,意思是是否对新建索引收集统计信息，该参数默认是TRUE,表示默认收集新建索引的统计信息。考点统计信息13. 什么是基数反馈(Ca

19、rdinaIityFeedback)?正确答案：基数反馈(CardinalityFeedback,CFB)是OraCIellgR2出现的一个新特性,它的出现是为了帮助Oracle优化器依据更精准的基数生成更加优秀的执行计划。基数的评估准确与否，对于优化器异常重要，直接影响到后续的JOINCoST等市要的成本计算评估。若评估不当则会造成CBO选择不当的执行计划。此技术对于仅执行一次的SQ1.无效，在SQ1.第一次执行时，记录存储实际的基数和评估的基数之间的差异，如果差异较大，在第二次执行时，优化器会依据实际的基数市新决策生成执行计划，但是需要注意的是，当使用更准确的基数重新生成执行计划时，生成的

20、执行计划与第一次时使用的执行计划完全有可能是相同的。这个技术的出现是由于优化器在一些情况卜不能很好地去计算基数的数值，比如统计信息缺失或陈旧、多谓词、直方图缺失等。Oracle只针对下面情况开启CFB：1)没有收集表的统计信息，并且动态采样(DynamiCSamPling)也没有开启2)查询条件复杂(比如条件有函数)或者涉及多列，但却没有收集扩展的统计信息(EXtendedStatistics)在这几种情况下，CBO是无法估算出准确的CardinaliIy的。针对上述情况，OraCle会监控操作的实际行数(A-RoW),然后对比CBO估弊的行数(E-Row)o如果两个值相差很大，那么就记录实际

21、行数(A-RoW),做上标记。下次执行时再次进行硬解析，根据实际行数来重新生成执行计划。如果两个值相差不大，那么CBo就不再监控这条SQ1.语句。OracleIlgR2针对此特性，也专门在丫$SQ1.SHAREDCURSOR中增加了USE_FEEDBACK_STATS列来记录SQ1.是否使用了基数反馈。基数反馈的开启和关闭通过一个隐含参数OPTIMIZER一USE-FEEDBACK”来控制，该参数默认为TRUE,表示开启基数反袍特性。此参数除了可以在SESSIoN和SYSTEM级别进行设置之外，还可以在SQ1.语句级使用Hint进行开启和关闭，如下：SE1.ECT*+01y-PRM(,_OBT

22、1MIZER_USEJ-EEDBACK,FA1.SE)*COUNT(*)FROMTEST;SE1.ECT*+0PT_PARAM(_OPTIV【ZER_USE_FEEDBACKTRUE)*COUNT(*)FROMTEST;需要注意的是，如果动态采样被启用，那么是不会使用基数反馈特性的。若使用了该特性则在执行计划的Note部分可以看到“cardinalityfeedbackusedforthisStatement”字样。基数反馈在OraCIe12c上得到更进一步的扩展,称为统计反馈(StatiStiCSFeedbaCk),并且成为OraCle12c自动重新优化(AUtOmatiCReoPtimiZ

23、ation)的一部分。但处由于CFB的评估结果数据只存在内存中(重启之后就会丢失)，在会话之间是不可共用的，并且由于在OracleIlg中存在过多的Bug,常见的问题就是在第二次执行SQ1.时候性能下降很多。因此在OraCIeIlg的数据库中往往会时11.2.0.4以下的数据库将该特性关闭。考点统计信息14. 如何查询表和索引的历史统计信息?正确答案：从OraClc；0g开始，当收集表的统计信息的时候，旧的统计数据被保留，如果因为新的统计信息而出现性能问题，旧的统计信息就可以被恢复。历史统计信息保存在以下几张表中：DWRISOPTSTATTABHlSToRY表的统计信息。2) WRISOPTS

24、TATINDHISToRY索引的统计信息。3)WR1$J)PTSTATjnSTHEADjnSTORY列的统计信息。4)WRisOptstatjistgrmjistory直方图的信息。从视图DBA_TAB_STATS_HISTORY可以查询历史收集统计信息的时间，但是不能查询到行数，所以需要结合基表来查闻，查询的SQ1.语句如下：SE1.ECTB.OWNER,B,OBJECT_NAMETAB1.E_NAME,TO_CHAR(D.ANA1.YZET1ME,YYYY-MM-DDHH24:M1:SS)1.SIANA1.YZE71ME,TO.CHAR(D.SAVTIME,YYYY-MM-DDHH24zMIzSS,)CURR.ANA1.YZETIME,D.ROWCNTFROMSYS.WRl$_OPTSTAT_TAB_HISTORYD,DBAJ)BJECTSBWHERED.OBJ=