性能问题定位脚本

--查当前会话sql  定位查询较慢的sql
select s.SID,
       s.SERIAL#,
       s.SECONDS_IN_WAIT,
       round((sysdate - s.SQL_EXEC_START) * 3600 * 24) as sql_exec_seconds,
       q.SQL_TEXT,
       q.SQL_FULLTEXT,
       s.SQL_ID,
       q.HASH_VALUE,
       s.USERNAME,
       s.EVENT,
       s.WAIT_CLASS,
       s.ROW_WAIT_OBJ#,
       s.MACHINE,
       s.MODULE,
       d.owner,
       d.object_name,
       d.object_type,
       l.locked_mode
                /*,
                s.p1text,
                s.p1,
                s.p2text,
                s.p2,
                s.p3text,
                s.p3 */
  from v$session s
  left join v$sqlarea q
    on s.SQL_ID = q.SQL_ID
  left join dba_objects d
    on s.row_wait_obj# = d.object_id
  left join v$locked_object l
    on s.row_wait_obj# = l.object_id
   and s.sid = l.session_id
   and s.con_id = l.con_id
 where s.STATUS = 'ACTIVE'
   and s.USERNAME is not null
 order by s.SID, s.SERIAL#, sql_exec_seconds desc;
 

 

--查询表收集信息的时间
SELECT TABLE_NAME, PARTITION_NAME, LAST_ANALYZED, NUM_ROWS
  FROM USER_TAB_STATISTICS
 ORDER BY LAST_ANALYZED DESC NULLS LAST;