--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#';

oracle sql性能查證