这篇文章主要介绍“dba_indexes视图的性能有哪些”，在日常操作中，相信很多人在dba_indexes视图的性能有哪些问题上存在疑惑，小编查阅了各式资料，整理出简单好用的操作方法，希望对大家解答”dba_indexes视图的性能有哪些”的疑惑有所帮助！接下来，请跟着小编一起来学习吧！

selectcasewhenstatus='UNUSABLE'then'alterindex'||owner||'.'||index_name||'rebuildonlinecomputestatistics;'whento_number(degree)>1then'alterindex/*'||degree||'*/'||owner||'.'||index_name||'noparallel;'endcasefrom(select*fromdba_indexeswheredegree<>‘DEFAULT')awherestatus='UNUSABLE'orto_number(degree)>1andownernotin('SYS','SYSTEM','MANAGER','WMSYS');

语句运行的速度很快，但是从statspack中发现这条语句的逻辑读单次高达26846。使用set autotrace比较了下9i和10g的执行计划和统计信息，发现9i查询这个视图的代价非常的高，而10g则有了一定的改善。在Oracle9i中，optimizer_mode默认是CHOOSE，所以查询数据字典使用了RBO，而Oracle10g则默认为ALL_ROWS，所以采用了CBO。

SQL>select*fromv$version;BANNER----------------------------------------------------------------Oracle9iEnterpriseEditionRelease9.2.0.6.0-64bitProductionPL/SQLRelease9.2.0.6.0-ProductionCORE9.2.0.6.0ProductionTNSforIBM/AIXRISCSystem/6000:Version9.2.0.6.0-ProductionNLSRTLVersion9.2.0.6.0-ProductionSQL>setautottraceSQL>select*fromdba_indexes;1242rowsselected.ExecutionPlan----------------------------------------------------------0SELECTSTATEMENTOptimizer=CHOOSE10NESTEDLOOPS(OUTER)21NESTEDLOOPS(OUTER)32NESTEDLOOPS43NESTEDLOOPS54NESTEDLOOPS(OUTER)65NESTEDLOOPS76NESTEDLOOPS(OUTER)87NESTEDLOOPS98TABLEACCESS(FULL)OF'OBJ$'108TABLEACCESS(BYINDEXROWID)OF'IND$'1110INDEX(UNIQUESCAN)OF'I_IND1'(UNIQUE)127TABLEACCESS(BYINDEXROWID)OF'OBJ$'1312INDEX(UNIQUESCAN)OF'I_OBJ1'(UNIQUE)146TABLEACCESS(BYINDEXROWID)OF'OBJ$'1514INDEX(UNIQUESCAN)OF'I_OBJ1'(UNIQUE)165TABLEACCESS(CLUSTER)OF'USER$'1716INDEX(UNIQUESCAN)OF'I_USER#'(NON-UNIQUE)184TABLEACCESS(CLUSTER)OF'USER$'1918INDEX(UNIQUESCAN)OF'I_USER#'(NON-UNIQUE)203TABLEACCESS(CLUSTER)OF'USER$'2120INDEX(UNIQUESCAN)OF'I_USER#'(NON-UNIQUE)222TABLEACCESS(CLUSTER)OF'SEG$'2322INDEX(UNIQUESCAN)OF'I_FILE#_BLOCK#'(NON-UNIQUE)241TABLEACCESS(CLUSTER)OF'TS$'2524INDEX(UNIQUESCAN)OF'I_TS#'(NON-UNIQUE)Statistics----------------------------------------------------------0recursivecalls0dbblockgets42924consistentgets0physicalreads0redosize98000bytessentviaSQL*Nettoclient1558bytesreceivedviaSQL*Netfromclient84SQL*Netroundtripsto/fromclient0sorts(memory)0sorts(disk)1242rowsprocessed

SQL>select*fromv$version;BANNER----------------------------------------------------------------OracleDatabase10gEnterpriseEditionRelease10.2.0.4.0-64biPL/SQLRelease10.2.0.4.0-ProductionCORE10.2.0.4.0ProductionTNSforIBM/AIXRISCSystem/6000:Version10.2.0.4.0-ProductioNLSRTLVersion10.2.0.4.0-Production21:32:11SYS@coll>setautottrace21:32:15SYS@coll>select*fromdba_indexes;1162rowsselected.ExecutionPlan----------------------------------------------------------Planhashvalue:3901056803----------------------------------------------------------------------------------------------|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|----------------------------------------------------------------------------------------------|0|SELECTSTATEMENT||1164|330K|237(3)|00:00:03||*1|HASHJOINRIGHTOUTER||1164|330K|237(3)|00:00:03||2|TABLEACCESSFULL|TS$|9|189|4(0)|00:00:01||*3|HASHJOINRIGHTOUTER||1164|306K|232(3)|00:00:03||4|TABLEACCESSFULL|SEG$|2635|102K|18(0)|00:00:01||*5|HASHJOIN||1164|261K|214(3)|00:00:03||6|TABLEACCESSFULL|USER$|35|560|2(0)|00:00:01||*7|HASHJOIN||1164|243K|211(3)|00:00:03||8|TABLEACCESSFULL|USER$|35|560|2(0)|00:00:01||*9|HASHJOINRIGHTOUTER||1164|225K|208(2)|00:00:03||10|TABLEACCESSFULL|USER$|35|560|2(0)|00:00:01||*11|HASHJOIN||1164|206K|206(2)|00:00:03||*12|HASHJOINOUTER||1164|172K|174(2)|00:00:03||13|MERGEJOIN||1164|142K|142(2)|00:00:02||*14|TABLEACCESSBYINDEXROWID|IND$|1164|104K|109(0)|00:00:02||15|INDEXFULLSCAN|I_IND1|1164||2(0)|00:00:01||*16|SORTJOIN||10589|341K|33(7)|00:00:01||*17|TABLEACCESSFULL|OBJ$|10589|341K|32(4)|00:00:01||18|TABLEACCESSFULL|OBJ$|10592|279K|31(0)|00:00:01||19|TABLEACCESSFULL|OBJ$|10592|310K|31(0)|00:00:01|----------------------------------------------------------------------------------------------PredicateInformation(identifiedbyoperationid):---------------------------------------------------1-access("I"."TS#"="TS"."TS#"(+))3-access("I"."FILE#"="S"."FILE#"(+)AND"I"."BLOCK#"="S"."BLOCK#"(+)AND"I"."TS#"="S"."TS#"(+))5-access("IO"."OWNER#"="IU"."USER#")7-access("U"."USER#"="O"."OWNER#")9-access("ITO"."OWNER#"="ITU"."USER#"(+))11-access("I"."BO#"="IO"."OBJ#")12-access("I"."INDMETHOD#"="ITO"."OBJ#"(+))14-filter(BITAND("I"."FLAGS",4096)=0)16-access("O"."OBJ#"="I"."OBJ#")filter("O"."OBJ#"="I"."OBJ#")17-filter(BITAND("O"."FLAGS",128)=0)Statistics----------------------------------------------------------0recursivecalls0dbblockgets876consistentgets0physicalreads0redosize92582bytessentviaSQL*Nettoclient1339bytesreceivedviaSQL*Netfromclient79SQL*Netroundtripsto/fromclient1sorts(memory)0sorts(disk)1162rowsprocessedselect/*+rule*/*fromdba_indexes;1162rowsselected.Elapsed:00:00:00.55ExecutionPlan----------------------------------------------------------Planhashvalue:2107813288--------------------------------------------------------------|Id|Operation|Name|--------------------------------------------------------------|0|SELECTSTATEMENT|||1|NESTEDLOOPSOUTER|||2|NESTEDLOOPSOUTER|||3|NESTEDLOOPS|||4|NESTEDLOOPS|||5|NESTEDLOOPSOUTER|||6|NESTEDLOOPS|||7|NESTEDLOOPSOUTER|||8|NESTEDLOOPS|||*9|TABLEACCESSFULL|OBJ$||*10|TABLEACCESSBYINDEXROWID|IND$||*11|INDEXUNIQUESCAN|I_IND1||12|TABLEACCESSBYINDEXROWID|OBJ$||*13|INDEXUNIQUESCAN|I_OBJ1||14|TABLEACCESSBYINDEXROWID|OBJ$||*15|INDEXUNIQUESCAN|I_OBJ1||16|TABLEACCESSCLUSTER|USER$||*17|INDEXUNIQUESCAN|I_USER#||18|TABLEACCESSCLUSTER|USER$||*19|INDEXUNIQUESCAN|I_USER#||20|TABLEACCESSCLUSTER|USER$||*21|INDEXUNIQUESCAN|I_USER#||22|TABLEACCESSCLUSTER|SEG$||*23|INDEXUNIQUESCAN|I_FILE#_BLOCK#||24|TABLEACCESSCLUSTER|TS$||*25|INDEXUNIQUESCAN|I_TS#|--------------------------------------------------------------PredicateInformation(identifiedbyoperationid):---------------------------------------------------9-filter(BITAND("O"."FLAGS",128)=0)10-filter(BITAND("I"."FLAGS",4096)=0)11-access("O"."OBJ#"="I"."OBJ#")13-access("I"."INDMETHOD#"="ITO"."OBJ#"(+))15-access("I"."BO#"="IO"."OBJ#")17-access("ITO"."OWNER#"="ITU"."USER#"(+))19-access("U"."USER#"="O"."OWNER#")21-access("IO"."OWNER#"="IU"."USER#")23-access("I"."TS#"="S"."TS#"(+)AND"I"."FILE#"="S"."FILE#"(+)AND"I"."BLOCK#"="S"."BLOCK#"(+))25-access("I"."TS#"="TS"."TS#"(+))Note------rulebasedoptimizerused(considerusingcbo)Statistics----------------------------------------------------------0recursivecalls0dbblockgets25254consistentgets26physicalreads0redosize93977bytessentviaSQL*Nettoclient1339bytesreceivedviaSQL*Netfromclient79SQL*Netroundtripsto/fromclient0sorts(memory)0sorts(disk)1162rowsprocessed

到此，关于“dba_indexes视图的性能有哪些”的学习就结束了，希望能够解决大家的疑惑。理论与实践的搭配能更好的帮助大家学习，快去试试吧！若想继续学习更多相关知识，请继续关注亿速云网站，小编会继续努力为大家带来更多实用的文章！