oracle中查找执行效率低下的SQL

oracle中查找执行效率低下的SQLv$sqltext：存储的是完整的SQL,SQL被分割v$sqlarea：存储的SQL和一些相关的信息，比如累计的执行次数，逻辑读，物理读等统计信息（统计）v$sql：内存共享SQL区域中已经解析的SQL语句。（即时）根据sid查找完整sql语句：selectsql_textfromv$sqltextawherea.hash_value=(selectsql_hash_valuefromv$sessionbwhereb.sid='&sid')orderbypieceascselecta.CPU_TIME,--CPU时间百万分之一（微秒）a.OPTIMIZER_MODE,--优化方式a.EXECUTIONS,--执行次数a.DISK_READS,--读盘次数a.SHARABLE_MEM,--占用sharedpool的内存多少a.BUFFER_GETS,--读取缓冲区的次数a.COMMAND_TYPE,--命令类型(3:select,2:insert;6:update;7delete;47:pl/sql程序单元)a.SQL_TEXT,--Sql语句a.SHARABLE_MEM,a.PERSISTENT_MEM,a.RUNTIME_MEM,a.PARSE_CALLS,a.DISK_READS,a.DIRECT_WRITES,a.CONCURRENCY_WAIT_TIME,a.USER_IO_WAIT_TIMEfromSYS.V_$SQLAREAaWHEREPARSING_SCHEMA_NAME='CHEA_FILL'--表空间orderbya.CPU_TIMEdesc引用：$SQLAREA中查询最占用资源的查询selectb.usernameusername,a.disk_readsreads,a.executionsexec,a.disk_reads/decode(a.executions,0,1,a.executions)rds_exec_ratio,a.sql_textStatementfromv$sqlareaa,dba_usersbwherea.parsing_user_id=b.user_idanda.disk_reads100000orderbya.disk_readsdesc;用buffer_gets列来替换disk_reads列可以得到占用最多内存的sql语句的相关信息。v$sql：内存共享SQL区域中已经解析的SQL语句。（即时）列出使用频率最高的5个查询：selectsql_text,executionsfrom(selectsql_text,executions,rank()over(orderbyexecutionsdesc)exec_rankfromv$sql)whereexec_rank=5;消耗磁盘读取最多的sqltop5：selectdisk_reads,sql_textfrom(selectsql_text,disk_reads,dense_rank()over(orderbydisk_readsdesc)disk_reads_rankfromv$sql)wheredisk_reads_rank=5;找出需要大量缓冲读取（逻辑读）操作的查询：selectbuffer_gets,sql_textfrom(selectsql_text,buffer_gets,dense_rank()over(orderbybuffer_getsdesc)buffer_gets_rankfromv$sql)wherebuffer_gets_rank=5;v$sqlarea字段定义：(1000)FirstthousandcharactersoftheSQLtextforthecurrentcursorSQL_IDVARCHAR2(13)SQLidentifieroftheparentcursorinthelibrarycacheSHARABLE_MEMNUMBERAmountofsharedmemoryusedbyacursor.Ifmultiplechildcursorsexist,thenthesumofallsharedmemoryusedbyallchildcursors.PERSISTENT_MEMNUMBERFixedamountofmemoryusedforthelifetimeofanopencursor.Ifmultiplechildcursorsexist,thefixedsumofmemoryusedforthelifetimeofallthechildcursors.RUNTIME_MEMNUMBERFixedamountofmemoryrequiredduringexecutionofacursor.Ifmultiplechildcursorsexist,thefixedsumofallmemoryrequiredduringexecutionofallthechildcursors.SORTSNUMBERSumofthenumberofsortsthatweredoneforallthechildcursorsVERSION_COUNTNUMBERNumberofchildcursorsthatarepresentinthecacheunderthisparentLOADED_VERSIONSNUMBERNumberofchildcursorsthatarepresentinthecacheandhavetheircontextheap(KGLheap6)loadedOPEN_VERSIONSNUMBERThenumberofchildcursorsthatarecurrentlyopenunderthiscurrentparentUSERS_OPENINGNUMBERNumberofusersthathaveanyofthechildcursorsopenFETCHESNUMBERNumberoffetchesassociatedwiththeSQLstatementEXECUTIONSNUMBERTotalnumberofexecutions,totalledoverallthechildcursorsEND_OF_FETCH_COUNTNUMBERNumberoftimesthiscursorwasfullyexecutedsincethecursorwasbroughtintothelibrarycache.Thevalueofthisstatisticisnotincrementedwhenthecursorispartiallyexecuted,eitherbecauseitfailedduringtheexecutionorbecauseonlythefirstfewrowsproducedbythiscursorarefetchedbeforethecursorisclosedorre-executed.Bydefinition,thevalueoftheEND_OF_FETCH_COUNTcolumnshouldbelessorequaltothevalueoftheEXECUTIONScolumn.USERS_EXECUTINGNUMBERTotalnumberofusersexecutingthestatementoverallchildcursorsLOADSNUMBERNumberoftimestheobjectwasloadedorreloadedFIRST_LOAD_TIMEVARCHAR2(19)TimestampoftheparentcreationtimeINVALIDATIONSNUMBERTotalnumberofinvalidationsoverallthechildcursorsPARSE_CALLSNUMBERSumofallparsecallstoallthechildcursorsunderthisparentDISK_READSNUMBERSumofthenumberofdiskreadsoverallchildcursorsDIRECT_WRITESNUMBERSumofthenumberofdirectwritesoverallchildcursorsBUFFER_GETSNUMBERSumofbuffergetsoverallchildcursorsAPPLICATION_WAIT_TIMENUMBERApplicationwaittimeCONCURRENCY_WAIT_TIMENUMBERConcurrencywaittimeCLUSTER_WAIT_TIMENUMBERClusterwaittimeUSER_IO_WAIT_TIMENUMBERUserI/OWaitTimePLSQL_EXEC_TIMENUMBERPL/SQLexecutiontimeJAVA_EXEC_TIMENUMBERJavaexecutiontimeROWS_PROCESSEDNUMBERTotalnumberofrowsprocessedonbehalfofthisSQLstatementCOMMAND_TYPENUMBEROraclecommandtypedefinitionOPTIMIZER_MODEVARCHAR2(25)ModeunderwhichtheSQLstatementwasexecutedPARSING_USER_IDNUMBERUserIDoftheuserthathasparsedtheveryfirstcursorunderthisparentPARSING_SCHEMA_IDNUMBERSchemaIDthatwasusedtoparsethischildcursorKEPT_VERSIONSNUMBERNumberofchildcursorsthathavebeenmarkedtobekeptusingtheDBMS_SHARED_POOLpackageADDRESSRAW(4|8)AddressofthehandletotheparentforthiscursorHASH_VALUENUMBERHashvalueoftheparentstatementinthelibrarycacheOLD_HASH_VALUENUMBEROldSQLhashvalueMODULEVARCHAR2(64)ContainsthenameofthemodulethatwasexecutingatthetimethattheSQLstatementwasfirstparsedassetbycallingDBMS_APPLICATION_INFO.SET_MODULEMODULE_HASHNUMBERHashvalueofthemodulethatisnamedintheMODULEcolumnACTIONVARCHAR2(64)ContainsthenameoftheactionthatwasexecutingatthetimethattheSQLstatementwasfirstparsedassetbycallingDBMS_APPLICATION_INFO.SET_ACTIONACTION_HASHNUMBERHashvalueoftheactionthatisnamedintheACTIONcolumnSERIALIZABLE_ABORTSNUMBERNumberoftimesthetransactionfailstoserialize,producingORA-08177err