2024Oracle數(shù)據(jù)庫SQL執(zhí)行計劃

SQL執(zhí)行計SQLQuery10053SQLExplain10046/10053v$sql_plan、columnidformat999newlinecolumnoperationformata20columnoptionsformata15columnobject_nameformata22trunccolumnoptimizerformata3truncLPAD('',DEPTH)||operation WHEREsql_id='&sql_id'STARTWITHid=0CONNECTBY(PRIORid=parent_idANDPRIORsql_id=sql_idANDPRIORchild_number=child_number)ORDERsiblingsBYExplainSQL>explainplanforselect*fromscott.empwhereempno>10000;SQL>select*fromtable(dbms_xplan.display);SQL>setautotraceSQL>selectcount(*)fromselectsql_id,child_numberfromv$sqlwheresql_textlikeSELECTFROMDBA_HIST_SQLTEXTht,table(DBMS_XPLAN.DISPLAY_AWR(ht.sql_id,null,null,'ALL'))tfWHEREht.sql_textlike'%TOTO%';出來。selectsql_handle,plan_name,creator,parsing_schema_namefromdba_sql_plan_baselineswheresql_text='select*fromt1wherecol1>900';select*select*fromtable(dbms_xplan.display_sqlset(‘sqlset名altersessionsetaltersessionsetmax_dump_file_size=UNLIMITED;altersessionsettimed_statistics=true;altersessionsetevents'10046tracenamecontextforever,levelaltersessionsetevents'10046tracenamecontextoff';altersessionsetmax_dump_file_size=altersessionsetevents'10053tracenamecontextforever,levelaltersessionsetevents'10053tracenamecontextoff';從StatspackAWR取得SQL執(zhí)行計LEVEL6以上設(shè)定する必要があります。SQLSQLselecte.EMPLOYEE_ID,e.FIRST_NAME||e.LAST_NAMEfromhr.EMPLOYEESe,hr.LOCATIONSlwhereandand|Id| | 0|SELECT 1|NESTED 2 NESTED 3 4 TABLEACCESSBYINDEXROWID||*5 INDEXRANGE | 6 TABLEACCESSBYINDEXROWID||*7 INDEXRANGE ||*8 INDEXRANGE | 9 |PredicateInformation(identifiedbyoperation5-7-8-表的ACCESSTABLEFULLTABLEACCESSBY表的ACCESSTABLEFULLSCANSQL文包含“SELECTCOUNT(*)表的ACCESSFullTableHowaFullTableScanBtree索引的ACCESSBtree索引的ACCESSINDEXUNIQUEINDEXRANGEINDEXFULLINDEXSKIPIndexUniqueIndexRangeINDEXFULLIndexFastFullBitmapBitmapIndexAccessBitmapIndexBitmapIndexAccessCombiningBitmapBitmapIndexAcc