sql优化

select * from (select sa.SQL_TEXT, sa.SQL_FULLTEXT, sa.LAST_LOAD_TIME, sa.EXECUTIONS “执行次数”, round(sa.ELAPSED_TIME / 1000000, 2) “总执行时间”, round(sa.ELAPSED_TIME / 1000000 / sa.EXECUTIONS, 2) “平均执行时间”, sa.COMMAND_TYPE, sa.PARSING_USER_ID “用户D”, u.username “用户名”, sa.HASH_VALUE from v$sqlarea sa left join all_users u on sa.PARSING_USER_ID = u.user_id where sa.EXECUTIONS > 0 and sa.LAST_LOAD_TIME>trunc(sysdate) order by (sa.ELAPSED_TIME / sa.EXECUTIONS) desc) where rownum <= 50;

SELECT sq.INST_ID, SQ.SQL_TEXT, /SQL文本/ SE.SID, /会话的唯一标识，通常要对某个会话进行分析前，首先就需要获得该会话的ID。/ SE.BLOCKING_SESSION, SQ.OPTIMIZER_COST AS COST_, /* COST 值/ SE.LAST_CALL_ET CONTINUE_TIME, /执行时间/ SE.EVENT, /等待事件/ SE.LOCKWAIT, /是否等待OCK(SE，)/ SE.MACHINE, /客户端的机器名。WORKGROUP\PC-201211082055)/ SQ.SQL_ID, /SQL_ID/ SE.USERNAME, /创建该会话的用户名/ SE.LOGON_TIME, /登陆时间/ ‘ALTER SYSTEM KILL SESSION ‘’’ || SE.SID || ‘,’ || SE.SERIAL# || ‘’’;’ KILL –若存在锁情况会用到ILL锁释放 FROM gV$SESSION SE, /会话信息。每一个连接到RACLE数据库的会话都能在该视图中对应一条记录，根据该视图中的信息可以查询该会话使用的用户，正在执行或者刚刚执行的QL语句/ /**/ gV$SQLAREA SQ /跟踪所有HARED POOL中的共享URSOR信息，包括执行次数，逻辑读，物理读等/ WHERE SE.SQL_HASH_VALUE = SQ.HASH_VALUE AND SE.STATUS = ‘ACTIVE’ AND SE.SQL_ID = SQ.SQL_ID AND SE.USERNAME = SQ.PARSING_SCHEMA_NAME –过滤条件 AND SE.USERNAME = ‘FWSB’ –用户名 and se.BLOCKING_SESSION is not null;