oracle查询及性能

表被锁
+++++++++++++++++++++++++++++++++++++++++++++++++++
--锁表查询
SELECT object_name, machine, s.sid, s.serial#
  FROM gv$locked_object l, dba_objects o, gv$session s
 WHERE l.object_id　 = o.object_id
   AND l.session_id = s.sid;


--过程被锁
select va.sid,va.OBJECT from v$access va where object like 'PKG_MONTHLY_INVOICE%'    


   
--杀进程
ALTER system kill session '463, 27072'; 


+++++++++++++++++++++++++++++++++++++++++++++++++++




查询JOB运行与否
+++++++++++++++++++++++++++++++++++++++++++++++++++
--查询正在运行的JOB
SELECT d.WHAT, t.* FROM DBA_JOBS_RUNNING t, dba_jobs d where t.JOB = d.JOb;
   
--杀进程
ALTER system kill session '463, 27072'; 
 
+++++++++++++++++++++++++++++++++++++++++++++++++++ 




查询执行计划
+++++++++++++++++++++++++++++++++++++++++++++++++++


通过
--查SQL的历史快照
select t.*
  from dba_hist_snapshot t, dba_hist_sqlstat b
 where t.snap_id = b.snap_id
   and t.dbid = b.dbid
   and b.sql_id = 'b6b450bubr8t3'






--查历史
select * from table(dbms_xplan.display_awr('sql_id'));
 
--查当前
select * from table(dbms_xplan.display_cursor('sql_id'));
+++++++++++++++++++++++++++++++++++++++++++++++++++ 




--临时表空间大小
select tablespace_name, sum(bytes) / 1024 / 1024 mb
  from dba_temp_files
 group by tablespace_name;
-- 高水位
select tablespace_name, sum(bytes_cached)/1024/1024 mb
from v$temp_extent_pool
group by tablespace_name;




-- 当前使用表空间
select ss.tablespace_name,
       sum((ss.used_blocks * ts.blocksize)) / 1024 / 1024 mb
  from gv$sort_segment ss, sys.ts$ ts
 where ss.tablespace_name = ts.name
 group by ss.tablespace_name;






====================================


--查询字段里面是否有中文


select *
  from TT_EBIL_CONFIRM_FEE_FOR_BRIM T
 where asciistr(T.BG_CODE) like '%\%'
   and instr(T.BG_CODE, '\') <= 0;




========================================
--查询SQL绑定变量的值


select instance_number,  sql_id,name, datatype_string, last_captured,value_string from 
dba_hist_sqlbind where sql_id='fahv8x6ngrb50'order by LAST_CAPTURED,POSITION;






select sql_id,name, datatype_string, last_captured,value_string from 

v$sql_bind_capture where sql_id='dxfcacn4t4ppw' order by LAST_CAPTURED,POSITION;

--性能分析

--根据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';