Oracle慢常用SQL

1.查看当前用户下的活动session

https://www.cndba.cn/hbhe0316/article/106589

sys@TESTDB 08:15:47> select count(*) from v$session ss where ss.username='SYS' and ss.status='ACTIVE';

  COUNT(*)
----------
         2

2.Oracle查看SGA、PGA等使用率https://www.cndba.cn/hbhe0316/article/106589

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)
  union
select name,round(total,2) total,round((total-free),2) used,round(free,2) free,round((total-free)/total*100,2) pctused from (
select 'Shared pool' name,(select sum(bytes/1024/1024) from v$sgastat where pool='shared pool')total,
(select bytes/1024/1024 from v$sgastat where name='free memory' and pool='shared pool') free from dual)
union
select name,round(total,2)total,round(total-free,2) used,round(free,2) free,round((total-free)/total,2) pctused from (
select 'Default pool' name,( select a.cnum_repl*(select value from v$parameter where name='db_block_size')/1024/1024 total from x$kcbwds a, v$buffer_pool p
  where a.set_id=p.LO_SETID and p.name='DEFAULT' and p.block_size=(select value from v$parameter where name='db_block_size')) total,
(select a.anum_repl*(select value from v$parameter where name='db_block_size')/1024/1024 free from x$kcbwds a, v$buffer_pool p
where a.set_id=p.LO_SETID and p.name='DEFAULT' and p.block_size=(select value from v$parameter where name='db_block_size')) free from dual)
union
  select name,nvl(round(total,2),0)total,nvl(round(total-free,2),0) used,nvl(round(free,2),0) free,nvl(round((total-free)/total,2),0) pctused from (
select 'KEEP pool' name,(select a.cnum_repl*(select value from v$parameter where name='db_block_size')/1024/1024 total from x$kcbwds a, v$buffer_pool p
  where a.set_id=p.LO_SETID and p.name='KEEP' and p.block_size=(select value from v$parameter where name='db_block_size')) total,
(select a.anum_repl*(select value from v$parameter where name='db_block_size')/1024/1024 free from x$kcbwds a, v$buffer_pool p
where a.set_id=p.LO_SETID and p.name='KEEP' and p.block_size=(select value from v$parameter where name='db_block_size')) free from dual)
union
select name,nvl(round(total,2),0)total,nvl(round(total-free,2),0) used,nvl(round(free,2),0) free,nvl(round((total-free)/total,2),0) pctused from (
select 'RECYCLE pool' name,( select a.cnum_repl*(select value from v$parameter where name='db_block_size')/1024/1024 total from x$kcbwds a, v$buffer_pool p
  where a.set_id=p.LO_SETID and p.name='RECYCLE' and p.block_size=(select value from v$parameter where name='db_block_size')) total,
(select a.anum_repl*(select value from v$parameter where name='db_block_size')/1024/1024 free from x$kcbwds a, v$buffer_pool p
where a.set_id=p.LO_SETID and p.name='RECYCLE' and p.block_size=(select value from v$parameter where name='db_block_size')) free from dual)
union
select name,nvl(round(total,2),0)total,nvl(round(total-free,2),0) used,nvl(round(free,2),0) free,nvl(round((total-free)/total,2),0) pctused from(
select 'DEFAULT 16K buffer cache' name,(select a.cnum_repl*16/1024 total from x$kcbwds a, v$buffer_pool p
  where a.set_id=p.LO_SETID and p.name='DEFAULT' and p.block_size=16384) total,
  (select a.anum_repl*16/1024 free from x$kcbwds a, v$buffer_pool p
where a.set_id=p.LO_SETID and p.name='DEFAULT' and p.block_size=16384) free from dual)
union
select name,nvl(round(total,2),0)total,nvl(round(total-free,2),0) used,nvl(round(free,2),0) free,nvl(round((total-free)/total,2),0) pctused from(
select 'DEFAULT 32K buffer cache' name,(select a.cnum_repl*32/1024 total from x$kcbwds a, v$buffer_pool p
  where a.set_id=p.LO_SETID and p.name='DEFAULT' and p.block_size=32768) total,
  (select a.anum_repl*32/1024 free from x$kcbwds a, v$buffer_pool p
where a.set_id=p.LO_SETID and p.name='DEFAULT' and p.block_size=32768) free from dual)
union
select name,total,total-free used,free, (total-free)/total*100 pctused from (
select 'Java Pool' name,(select sum(bytes/1024/1024) total from v$sgastat where pool='java pool' group by pool)total,
( select bytes/1024/1024 free from v$sgastat where pool='java pool' and name='free memory')free from dual)
union
select name,Round(total,2),round(total-free,2) used,round(free,2) free, round((total-free)/total*100,2) pctused from (
select 'Large Pool' name,(select sum(bytes/1024/1024) total from v$sgastat where pool='large pool' group by pool)total,
( select bytes/1024/1024 free from v$sgastat where pool='large pool' and name='free memory')free from dual)
order by pctused desc;


NAME                                                                                  TOTAL       USED       FREE    PCTUSED
-------------------------------------------------------------------------------- ---------- ---------- ---------- ----------
Java Pool
SGA                                                                              3135.99731    2992.52     143.47      95.42
Shared pool                                                                             608     528.04      79.96      86.85
PGA                                                                                     782     502.19     279.81      64.22
Large Pool                                                                               16        .47      15.53       2.93
Default pool                                                                         554.41     263.53     290.88        .48
DEFAULT 16K buffer cache                                                                  0          0          0          0
DEFAULT 32K buffer cache                                                                  0          0          0          0
KEEP pool                                                                                 0          0          0          0
RECYCLE pool                                                                              0          0          0          0

10 rows selected.

Elapsed: 00:00:00.01

3.Oracle查看当前执行的SQLhttps://www.cndba.cn/hbhe0316/article/106589https://www.cndba.cn/hbhe0316/article/106589https://www.cndba.cn/hbhe0316/article/106589

SELECT   SUBSTR (s.username, 1, 18) username,
           s.sid,
           s.serial#,
           s.machine,
           y.sql_text
    FROM   v$session s,
           v$process p,
           v$transaction t,
           v$rollstat r,
           v$rollname n,
           v$sql y
   WHERE       s.paddr = p.addr
           AND s.taddr = t.addr(+)
           AND t.xidusn = r.usn(+)
           AND r.usn = n.usn(+)
           AND s.username IS NOT NULL
           AND s.sql_address = y.address
--and s.sid=56
ORDER BY   s.sid,
           s.serial#,
           s.username,
           s.status;

4.Oracle查看表空间使用率https://www.cndba.cn/hbhe0316/article/106589

select * from ( 
Select a.tablespace_name, 
to_char(a.bytes/1024/1024,'99,999.999') total_bytes, 
to_char(b.bytes/1024/1024,'99,999.999') free_bytes, 
to_char(a.bytes/1024/1024 - b.bytes/1024/1024,'99,999.999') use_bytes, 
to_char((1 - b.bytes/a.bytes)*100,'99.99') || '%' use 
from (select tablespace_name, 
sum(bytes) bytes 
from dba_data_files 
group by tablespace_name) a, 
(select tablespace_name, 
sum(bytes) bytes 
from dba_free_space 
group by tablespace_name) b 
where a.tablespace_name = b.tablespace_name 
union all 
select c.tablespace_name, 
to_char(c.bytes/1024/1024,'99,999.999') total_bytes, 
to_char( (c.bytes-d.bytes_used)/1024/1024,'99,999.999') free_bytes, 
to_char(d.bytes_used/1024/1024,'99,999.999') use_bytes, 
to_char(d.bytes_used*100/c.bytes,'99.99') || '%' use 
from 
(select tablespace_name,sum(bytes) bytes 
from dba_temp_files group by tablespace_name) c, 
(select tablespace_name,sum(bytes_cached) bytes_used 
from v$temp_extent_pool group by tablespace_name) d 
where c.tablespace_name = d.tablespace_name 
) ;

TABLESPACE_NAME      TOTAL_BYTES FREE_BYTES  USE_BYTES   USE
-------------------- ----------- ----------- ----------- -------
SYSTEM                 1,080.000       3.375   1,076.625  99.69%
SYSAUX                 1,470.000     103.875   1,366.125  92.93%
UNDOTBS1                 690.000     646.750      43.250   6.27%
OGG_TBS                5,120.000   5,117.063       2.938    .06%
USERS                  1,288.750   1,285.000       3.750    .29%
TEMP                     130.000       1.000     129.000  99.23%

6 rows selected.

5.查看最慢的SQLhttps://www.cndba.cn/hbhe0316/article/106589

select *
  from ( select sa.SQL_TEXT,
        sa.SQL_FULLTEXT,
        sa.EXECUTIONS "执行次数",
        round(sa.ELAPSED_TIME / 1000000, 2) "总执行时间",
        round(sa.ELAPSED_TIME / 1000000 / sa.EXECUTIONS, 2) "平均执行时间",
        sa.COMMAND_TYPE,
        sa.PARSING_USER_ID "用户ID",
        u.username "用户名",
        sa.HASH_VALUE
      from v$sqlarea sa
      left  join all_users u
       on sa.PARSING_USER_ID = u.user_id
      where sa.EXECUTIONS > 0
      order  by (sa.ELAPSED_TIME / sa.EXECUTIONS)  desc)
  where rownum <= 50;

6.Oracle查询SQL语句执行的耗时https://www.cndba.cn/hbhe0316/article/106589https://www.cndba.cn/hbhe0316/article/106589

select *
  from ( select s.SQL_TEXT,
        s.EXECUTIONS "执行次数",
        s.PARSING_USER_ID "用户名",
        rank()  over( order  by EXECUTIONS  desc) EXEC_RANK
      from v$sql s
      left  join all_users u
       on u.USER_ID = s.PARSING_USER_ID) t
  where exec_rank <= 100;

7.Oracle查询SQL语句执行的耗时https://www.cndba.cn/hbhe0316/article/106589

版权声明：本文为博主原创文章，未经博主允许不得转载。

oracle