--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_wrappedops$tkyte@ORA10G> column tablename format a15 word_wrappedops$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)columnsfrom ( 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_namefrom 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';
