sql server where 性能 sql查询性能

转载
数据小香 2023-07-05 09:12:40
文章标签 sql server where 性能 sql sed SQL 文章分类 SQL Server 数据库
--1、耗时长sql查证：--
select * from (select SQL_TEXT,round(ELAPSED_TIME/1000000,2),round(ELAPSED_TIME/1000000/EXECUTIONS,2) perELAPSED_time,EXECUTIONS
 
--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(+);





dden; overflow-y: hidden;">
--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';
--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';
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。