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'   )orderbypieceasc selecta.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_TIME fromSYS.V_$SQLAREAa WHEREPARSING_SCHEMA_NAME='CHEA_FILL'--表空间 orderbya.CPU_TIMEdesc 引用：http://jenniferok.iteye.com/blog/700985从V$SQLAREA中查询最占用资源的查询selectb.usernameusername,a.disk_readsreads,    a.executionsexec,a.disk_reads/decode(a.executions,0,1,a.executions)rds_exec_ratio,    a.sql_textStatementfrom v$sqlareaa,dba_usersbwherea.parsing_user_id=b.user_id anda.disk_reads>100000orderbya.disk_readsdesc;用buffer_gets列来替换disk_reads列可以得到占用最多内存的sql语句的相关信息。v$sql：内存共享SQL区域中已经解析的SQL语句。（即时）列出使用频率最高的5个查询：selectsql_text,executionsfrom(selectsql_text,executions,   rank()over    (orderbyexecutionsdesc)exec_rank   fromv$sql)whereexec_rank<=5;消耗磁盘读取最多的sqltop5：selectdisk_reads,sql_textfrom(selectsql_text,disk_reads,   dense_rank()over     (orderbydisk_readsdesc)disk_reads_rank   fromv$sql)wheredisk_reads_rank<=5;找出需要大量缓冲读取（逻辑读）操作的查询：selectbuffer_gets,sql_textfrom(selectsql_text,buffer_gets,   dense_rank()over     (orderbybuffer_getsdesc)buffer_gets_rank   fromv$sql)wherebuffer_gets_rank<=5;v$sqlarea字段定义：http://happyhou.blog.sohu.com/60494432.htmlSQL_TEXTVARCHAR2(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-08177errors,totalledoverallthechildcursorsCPU_TIMENUMBERCPUtime(inmicroseconds)usedbythiscursorforparsing/executing/fetchingELAPSED_TIMENUMBERElapsedtime(inmicroseconds)usedbythiscursorforparsing/executing/fetchingIS_OBSOLETEVARCHAR2(1)Indicateswhetherthecursorhasbecomeobsolete(Y)ornot(N).Thiscanhappenifthenumberofchildcursorsistoolarge.CHILD_LATCHNUMBERChildlatchnumberthatisprotectingthecursorPROGRAM_IDNUMBERProgramidentifie