2024Oracle数据库SQL执行计划

SQL执行计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执行计LEVEL6以上設定する必要があります。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