南山草舍

0%

查询Oracle数据库连接和资源消耗常用语句

发表于 分类于

查看数据库连接命令

查看数据库最大可用连接数

show parameter processes;

查看数据库最大session数

show parameter sessions;

查看数据库当前session数和活动session

select count(*) from v$session;
select count(*) from v$session where status='ACTIVE';

查看数据库当前使用者

SELECT osuser, a.username,cpu_time/executions/1000000||'s', b.sql_text,machine from v$session a,v$sqlarea b where a.sql_address =b.address order by cpu_time/executions desc;

查看数据库资源使用情况命令

查看各用户的各种资源占用

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
SELECT SE.SID, SES.USERNAME, SES.OSUSER, N.NAME, SE.VALUE
  FROM V$STATNAME N, V$SESSTAT SE, V$SESSION SES
 WHERE N.STATISTIC# = SE.STATISTIC#
   AND SE.SID = SES.SID
   AND SES.USERNAME IS NOT NULL
   AND N.NAME IN ('CPU used by this session',
                  'db block gets',
                  'consistent gets',
                  'physical reads',
                  'free buffer requested',
                  'table scans (long tables)',
                  'table scan rows gotten',
                  'sorts (memory)',
                  'sorts (disk)',
                  'sorts (rows)',
                  'session uga memory max',
                  'session pga memory max')
 ORDER BY SID, N.STATISTIC#;

z

占用资源的SQL top10之类的数据

1
2
3
4
5
6
7
8
9
SELECT B.USERNAME USERNAME,
       A.DISK_READS READS,
       A.EXECUTIONS EXEC,
       A.DISK_READS / DECODE(A.EXECUTIONS, 0, 1, A.EXECUTIONS) RDS_EXEC_RATIO,
       A.SQL_TEXT STATEMENT
  FROM V$SQLAREA A, DBA_USERS B
 WHERE A.PARSING_USER_ID = B.USER_ID
   AND A.DISK_READS > 100000
 ORDER BY A.DISK_READS DESC;

z

使用频率最高的5个查询

1
2
3
4
5
6
SELECT SQL_TEXT, EXECUTIONS
  FROM (SELECT SQL_TEXT,
               EXECUTIONS,
               RANK() OVER(ORDER BY EXECUTIONS DESC) EXEC_RANK
          FROM V$SQL)
WHERE EXEC_RANK <= 5;

z

消耗磁盘读取最多的sql top5

1
2
3
4
5
6
SELECT DISK_READS, SQL_TEXT
  FROM (SELECT SQL_TEXT,
               DISK_READS,
               DENSE_RANK() OVER(ORDER BY DISK_READS DESC) DISK_READS_RANK
          FROM V$SQL)
 WHERE DISK_READS_RANK <= 5;

z

找出需要大量缓冲读取（逻辑读）操作的查询

1
2
3
4
5
6
SELECT BUFFER_GETS, SQL_TEXT
  FROM (SELECT SQL_TEXT,
               BUFFER_GETS,
               DENSE_RANK() OVER(ORDER BY BUFFER_GETS DESC) BUFFER_GETS_RANK
          FROM V$SQL)
 WHERE BUFFER_GETS_RANK <= 5;

z