ORACLE常用性能SQL

--根据sql id查看执行计划aanqrwv9p5x5s
select * from table(dbms_xplan.display_cursor('9axn49wsxyn64'));


-- 查看花费时间
SELECT T.TARGET,
       T.SQL_ID,
       T.TIME_REMAINING,
       T.ELAPSED_SECONDS,
       T.SOFAR,
       T.TOTALWORK,
       T.START_TIME,
       T.LAST_UPDATE_TIME,
       T.SID,
       T.SERIAL#,       
       T.MESSAGE
  FROM V$SESSION_LONGOPS T
 WHERE T.SQL_ID IN ('9axn49wsxyn64');


--查看SQL等待状态
select *
  from V$SESSION_WAIT t
 where t.SID IN
       (SELECT v.SID FROM V$SESSION V WHERE V.SQL_ID IN ('d0qs5muru61k8'/*,'dq47ryxhrccm0','d0qs5muru61k8'*/));




-- 重建索引
alter index IDX_TT_BILLING_WAYBILL_1 REBUILD;


-- 重建分区索引
alter index IDX_TT_BILLING_WAYBILL_3 REBUILD partition BILLING_W_201012;


--表分析
analyze table TT_BILLING_WAYBILL compute statistics (sample 20%); 


BEGIN
 dbms_stats.gather_table_stats(ownname=>'exp5',tabname=>'TT_BILLING_WAYBILL',estimate_percent=>30,cascade=> TRUE);
END;


--索引分析
analyze index indexname compute statistics;


-- 分析表
analyze table ... validate structure;


-- 查看表空间
--col tablespace_name format a10;
SELECT F.TABLESPACE_NAME,
       A.TOTAL,
       U.USED,
       F.FREE,
       ROUND((U.USED / A.TOTAL) * 100) "% used",
       ROUND((F.FREE / A.TOTAL) * 100) "% Free"
  FROM (SELECT TABLESPACE_NAME, SUM(BYTES / (1024 * 1024)) TOTAL
          FROM DBA_DATA_FILES
         GROUP BY TABLESPACE_NAME) A,
       (SELECT TABLESPACE_NAME, ROUND(SUM(BYTES / (1024 * 1024))) USED
          FROM DBA_EXTENTS
         GROUP BY TABLESPACE_NAME) U,
       (SELECT TABLESPACE_NAME, ROUND(SUM(BYTES / (1024 * 1024))) FREE
          FROM DBA_FREE_SPACE
         GROUP BY TABLESPACE_NAME) F
 WHERE A.TABLESPACE_NAME = F.TABLESPACE_NAME
   AND A.TABLESPACE_NAME = U.TABLESPACE_NAME;
   
-- 查询锁定的表
SELECT /*+ rule */
 S.USERNAME,
 DECODE(L.TYPE, 'TM', 'TABLE LOCK', 'TX', 'ROW LOCK', NULL) LOCK_LEVEL,
 O.OWNER,
 O.OBJECT_NAME,
 O.OBJECT_TYPE,
 S.SID,
 S.SERIAL#,
 S.TERMINAL,
 S.MACHINE,
 S.PROGRAM,
 S.OSUSER,
 S.LOGON_TIME,
 S.SID
  FROM V$SESSION S, V$LOCK L, DBA_OBJECTS O
 WHERE L.SID = S.SID
   AND L.ID1 = O.OBJECT_ID(+)
   AND S.USERNAME IS NOT NULL




--session正在执行的 sql
SELECT /*+ ORDERED */
 SQL_TEXT
  FROM V$SQLTEXT A
 WHERE (A.HASH_VALUE, A.ADDRESS) IN
       (SELECT DECODE(SQL_HASH_VALUE, 0, PREV_HASH_VALUE, SQL_HASH_VALUE),
               DECODE(SQL_HASH_VALUE, 0, PREV_SQL_ADDR, SQL_ADDRESS)
          FROM V$SESSION B
         WHERE B.SID = '1549') /* 此处233 为SID*/
 ORDER BY PIECE ASC;




--通过TOAD的WAITE事件查正在等待的什么


SELECT *


  FROM DBA_EXTENTS E


 WHERE E.FILE_ID = 1054


   AND E.BLOCK_ID < 268553


   AND E.BLOCK_ID + E.BLOCKS > 268553


--查看临时表空间使用
select b.SID,
b.SQL_ID,
b.MACHINE,
b.USERNAME,
sum(blocks) * 8 / 1024 / 1024 temp_size
from v$tempseg_usage a, v$session b
where a.SESSION_NUM = b.SERIAL#
group by  b.SID,
               b.SQL_ID,
               b.MACHINE,
               b.USERNAME,SESSION_NUM


--历史执行计划 
select to_char(x.end_interval_time, 'YYYYMMDD HH24:MI:SS'),
       Y.executions_total,


       Y.*


  from dba_hist_snapshot x, dba_hist_sqlstat y


 where x.snap_id = y.snap_id


   and y.sql_id in ('2acyfc35xtftf')


   and x.instance_number = y.instance_number


   and x.end_interval_time >


       to_date('2013-05-22 00:30', 'yyyy-mm-dd hh24:mi')


  -- and y.executions_delta <> 0


   and x.instance_number = y.instance_number


 order by 2 desc,1 asc;






--查询事务回滚段的使用情况，数值有增加则在写数据否则在回滚


select sw.inst_id,
       s.sid,
       s.sql_hash_value,
       s.osuser,
       to_char(s.LOGON_TIME, 'YYYY-MM-DD HH24:MI:SS') logontime,
       sw.event,
       s.username,
       t.xidusn,
       round(t.used_ublk * x.VALUE / 1024 / 1024, 2) "used_undo M"
  from gv$transaction t, gv$session s, gv$session_wait sw, v$parameter x
where t.ADDR = s.TADDR
   and t.INST_ID = s.INST_ID
   and s.inst_id = sw.inst_id
   and s.sid = sw.sid
   and x.name = 'db_block_size'                   
 order by "used_undo M" desc;




--查询SQLID的绑定变量值


 select sql_id,
        name,
        datatype_string,
        case datatype
          when 180 then --TIMESTAMP
           to_char(ANYDATA.accesstimestamp(t.value_anydata),
                   'YYYY/MM/DD HH24:MI:SS')
          else
           t.value_string
        end as bind_value,
        last_captured
   from v$sql_bind_capture t
  where sql_id = '5c2gq85rvycay';