oracle sql性能查证

梦里梦外; 2021-11-22 16:36 278阅读 0赞

\--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][ps_tkyte_ORA10G]> column columns format a30 word\_wrapped  
[ops$tkyte@ORA10G][ops_tkyte_ORA10G]> column tablename format a15 word\_wrapped  
[ops$tkyte@ORA10G][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\#';

转载于:https://www.cnblogs.com/BradMiller/archive/2010/06/06/1752709.html

[ps_tkyte_ORA10G]: mailto:ps%24tkyte@ORA10G
[ops_tkyte_ORA10G]: mailto:ops%24tkyte@ORA10G