59izt

Oracle 性能查询语句

1. 查看总消耗时间最长的前10条SQL语句

   select *
   from (select v.sql_id,
                v.child_number,
                v.sql_text,
                v.elapsed_time,
                v.cpu_time,
                v.disk_reads,
                rank() over(order by v.elapsed_time desc) elapsed_rank
         from v$sql v) a
   where elapsed_rank <= 10;

2. 查看 Oracle 当前所有进程执行进度

   SELECT A.USERNAME,
         A.TARGET,
         A.SID,
         a.SERIAL#,
         A.OPNAME,
         ROUND(A.SOFAR * 100 / A.TOTALWORK, 0) || '%' AS "status",
         A.TIME_REMAINING "last_time",
         TRUNC(A.TIME_REMAINING / 60, 2) "last_min",
         TRUNC(A.TIME_REMAINING / 60/60, 2) "last_hour",
         B.SQL_TEXT "sql_text",
         B.LAST_ACTIVE_TIME "start_time"
   FROM V$SESSION_LONGOPS A, V$SQLAREA B
   WHERE A.TIME_REMAINING <> 0
   AND A.SQL_ADDRESS = B.ADDRESS
   AND A.SQL_HASH_VALUE = B.HASH_VALUE;

3. 查看CPU消耗时间最多的前10条SQL语句

   select *
     from (select v.sql_id,
                 v.child_number,
                 v.sql_text,
                 v.elapsed_time,
                 v.cpu_time,
                 v.disk_reads,
                 rank() over(order by v.cpu_time desc) elapsed_rank
             from v$sql v) a
   where elapsed_rank <= 10;

4. 查看消耗磁盘读取最多的前10条SQL语句

   select *
     from (select v.sql_id,
                 v.child_number,
                 v.sql_text,
                 v.elapsed_time,
                 v.cpu_time,
                 v.disk_reads,
                 rank() over(order by v.disk_reads desc) elapsed_rank
             from v$sql v) a
   where elapsed_rank <= 10;

5. IOps和IO吞吐量 （oracle 11G）

   select sum(decode(name,
                     'physical read IO requests',
                     value,
                     'physical write IO requests',
                     value,
                     0)) as iops,
          sum(decode(name,
                     'physical read bytes',
                     value,
                     'physical write bytes',
                     value,
                     0)) / 1024 / 1024 as mbps
     from v$sysstat
   where name in ('physical read IO requests',
                   'physical write IO requests',
                   'physical read bytes',
                   'physical read total bytes',
                   'physical write bytes',
                   'physical write total bytes',
                   'physical read total IO requests',
                   'physical write total IO requests');

6. 内存使用情况–SGA / PGA 使用情况– pctused: 使用率

   select name,
         total,
         round(total - free, 2) used,
         round(free, 2) free,
         round((total - free) / total * 100, 2) pctused
     from (select 'SGA' name,
                 (select sum(value / 1024 / 1024) from v$sga) total,
                 (select sum(bytes / 1024 / 1024)
                     from v$sgastat
                   where name = 'free memory') free
             from dual)
   union
   select name,
         total,
         round(used, 2) used,
         round(total - used, 2) free,
         round(used / total * 100, 2) pctused
     from (select 'PGA' name,
                 (select value / 1024 / 1024 total
                     from v$pgastat
                   where name = 'aggregate PGA target parameter') total,
                 (select value / 1024 / 1024 used
                     from v$pgastat
                   where name = 'total PGA allocated') used
             from dual);

7. Oracle 内存信息查询 (SGA是系统全局区，是Oracle中可以共享的内存。)

   select 'SGA' AS NAME,
         ROUND(sum(value) / 1024 / 1024, 2) || 'M' AS "SIZE(M)"
     from v$sga
   UNION
   select 'PGA' AS NAME, ROUND(value / 1024 / 1024, 2) || 'M' AS "SIZE(M)"
     from v$pgastat
   where name = 'total PGA allocated'
   UNION
   select 'TOTAL' AS NAME,
         ((SELECT ROUND(sum(value) / 1024 / 1024, 2) from v$sga) +
         (select ROUND(value / 1024 / 1024, 2)
             from v$pgastat
             where name = 'total PGA allocated')) || 'M' AS "SIZE(M)"
     FROM DUAL
   UNION
   SELECT NAME, TO_CHAR(VALUE)
     FROM V$PGASTAT
   WHERE NAME = 'process count';

8. 查看会话内存占用情况

   SELECT server "conn_type",
         s.username "username",
         OSUSEr "sys_user",
         NAME,
         VALUE / 1024 / 1024 "use_mem(M)",
         s.SID "session_ID",
         s.serial# session_id,
         spid "process_ID",
         p.PGA_USED_MEM,
         p.PGA_ALLOC_MEM,
         p.PGA_FREEABLE_MEM,
         p.PGA_MAX_MEM
     FROM v$session s, v$sesstat st, v$statname sn, v$process p
   WHERE st.SID = s.SID
     AND st.statistic# = sn.statistic#
     AND sn.NAME LIKE 'session pga memory'
     AND p.addr = s.paddr
   ORDER BY VALUE DESC;

   #查杀进程

   alter system kill session 'sid,serial#';

9. 查询表空间使用情况

   SELECT a.tablespace_name "tablespace_name",
         total/(1024 * 1024) "tablespace_total(M)",
         free/(1024 * 1024) "tablespace_free(M)",
         (total - free)/(1024 * 1024) "tablespace_used(M)",
         Round((total - free) / total, 4) * 100 "pctused %"
     FROM (SELECT tablespace_name, Sum(bytes) free
             FROM DBA_FREE_SPACE
           GROUP BY tablespace_name) a,
         (SELECT tablespace_name, Sum(bytes) total
             FROM DBA_DATA_FILES
           GROUP BY tablespace_name) b
   WHERE a.tablespace_name = b.tablespace_name;

10. Oracle根据表注释找表

    SELECT * FROM user_tab_comments where comments like '%员工信息表%';