oracle sql性能查证

--1、耗时长sql查证：--

select * from (select SQL_TEXT,round(ELAPSED_TIME/1000000,2),round(ELAPSED_TIME/1000000/EXECUTIONS,2) perELAPSED_time,EXECUTIONS
  from (select * from v$sql order by ELAPSED_TIME desc) where rownum<26) order by perELAPSED_time desc;

--2、占用cpu时间长的sql查证：--

select * from (select SQL_TEXT,round(CPU_TIME/1000000,2),round(CPU_TIME/1000000/EXECUTIONS,2) percpu_time,EXECUTIONS
  from (select * from v$sql order by CPU_TIME desc) where rownum<26) order by percpu_time desc;

--3、磁盘读数据量大sql查证：--

select sql_text,disk_reads,EXECUTIONS from (select * from v$sqlarea order by disk_reads desc) where rownum<26;

--4、获取超时sql--
select username,sid,opname, round(sofar*100 / totalwork,0) progress, time_remaining,sql_text
 from v$session_longops , v$sql where sql_address = address and sql_hash_value = hash_value
  order by start_time;

--5、查看表空间大小和使用情况all--

SELECT D.TABLESPACE_NAME,SPACE "SUM_SPACE(M)",BLOCKS SUM_BLOCKS,SPACE-NVL(FREE_SPACE,0) "USED_SPACE(M)",
ROUND((1-NVL(FREE_SPACE,0)/SPACE)*100,2) "USED_RATE(%)",FREE_SPACE "FREE_SPACE(M)"
FROM 
(SELECT TABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024),2) SPACE,SUM(BLOCKS) BLOCKS
FROM DBA_DATA_FILES
GROUP BY TABLESPACE_NAME) D,
(SELECT TABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024),2) FREE_SPACE
FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F
WHERE  D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)
UNION ALL  --if have tempfile 
SELECT D.TABLESPACE_NAME,SPACE "SUM_SPACE(M)",BLOCKS SUM_BLOCKS, 
USED_SPACE "USED_SPACE(M)",ROUND(NVL(USED_SPACE,0)/SPACE*100,2) "USED_RATE(%)",
NVL(FREE_SPACE,0) "FREE_SPACE(M)"
FROM 
(SELECT TABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024),2) SPACE,SUM(BLOCKS) BLOCKS
FROM DBA_TEMP_FILES
GROUP BY TABLESPACE_NAME) D,
(SELECT TABLESPACE_NAME,ROUND(SUM(BYTES_USED)/(1024*1024),2) USED_SPACE,
ROUND(SUM(BYTES_FREE)/(1024*1024),2) FREE_SPACE
FROM V$TEMP_SPACE_HEADER
GROUP BY TABLESPACE_NAME) F
WHERE  D.TABLESPACE_NAME = F.TABLESPACE_NAME(+);

--6、查证外键没有建索引的情况--

ps$tkyte@ORA10G> column columns format a30 word_wrapped
ops$tkyte@ORA10G> column tablename format a15 word_wrapped
ops$tkyte@ORA10G> column constraint_name format a15 word_wrapped

select table_name, constraint_name,
cname1 || nvl2(cname2,','||cname2,null) ||
nvl2(cname3,','||cname3,null) || nvl2(cname4,','||cname4,null) ||
nvl2(cname5,','||cname5,null) || nvl2(cname6,','||cname6,null) ||
nvl2(cname7,','||cname7,null) || nvl2(cname8,','||cname8,null)
columns
from ( select b.table_name,
b.constraint_name,
max(decode( position, 1, column_name, null )) cname1,
 max(decode( position, 2, column_name, null )) cname2,
 max(decode( position, 3, column_name, null )) cname3,
 max(decode( position, 4, column_name, null )) cname4,
 max(decode( position, 5, column_name, null )) cname5,
 max(decode( position, 6, column_name, null )) cname6,
 max(decode( position, 7, column_name, null )) cname7,
 max(decode( position, 8, column_name, null )) cname8,
 count(*) col_cnt
 from (select substr(table_name,1,30) table_name,
 substr(constraint_name,1,30) constraint_name,
 substr(column_name,1,30) column_name,
 position
 from user_cons_columns ) a,
 user_constraints b
 where a.constraint_name = b.constraint_name
 and b.constraint_type = 'R'
 group by b.table_name, b.constraint_name
 ) cons
 where col_cnt > ALL
 ( select count(*)
 from user_ind_columns i
 where i.table_name = cons.table_name
 and i.column_name in (cname1, cname2, cname3, cname4,
 cname5, cname6, cname7, cname8 )
 and i.column_position <= cons.col_cnt
 group by i.index_name
 ) order by table_name;

--7、检查被锁的对象--

select l.OBJECT_ID,l.SESSION_ID,l.oracle_username,l.LOCKED_MODE,o.object_name
from v$locked_object l,dba_objects o
where l.OBJECT_ID=o.OBJECT_ID;

--8、查看临时表空间情况--

SELECT  d.status "Status", d.tablespace_name "Name", d.contents "Type", d.extent_management "Extent Management",
                TO_CHAR(NVL(a.bytes / 1024 / 1024, 0),'99,999,990.900') "Size (M)",
                NVL(t.bytes, 0)/1024/1024 ||'/'||NVL(a.bytes/1024/1024, 0) "Used (M)",
                TO_CHAR(NVL(t.bytes / a.bytes * 100, 0), '990.00') "Used %"
          FROM  sys.dba_tablespaces d,
                (select tablespace_name, sum(bytes) bytes from dba_temp_files group by tablespace_name) a,
                (select tablespace_name, sum(bytes_cached) bytes from v$temp_extent_pool group by tablespace_name) t
         WHERE  d.tablespace_name = a.tablespace_name(+)
           AND  d.tablespace_name = t.tablespace_name(+)
           AND  d.extent_management like 'LOCAL'
           AND  d.contents like 'TEMPORARY';

--9. 查看并解除死锁 --         
select l.OBJECT_ID,l.SESSION_ID,l.oracle_username,l.LOCKED_MODE,o.object_name,t.serial#
from v$locked_object l,dba_objects o,v$session t
where l.OBJECT_ID=o.OBJECT_ID and l.session_id=t.sid;

alter system kill session 'SESSION_ID,t.serial#';