本文主要向大家介绍了Oracle数据库之oracle锁表查询，资源占用，连接会话，低效SQL等性能检查，通过具体的内容向大家展现，希望对大家学习Oracle数据库有所帮助。

查询oracle用户名，机器名，锁表对象

复制代码
select l.session_id sid,
      s.serial#,
      l.locked_mode,
      l.oracle_username,
      l.os_user_name,
      s.machine,
      s.terminal,
      o.object_name,
      s.logon_time
 from v$locked_object l, all_objects o, v$session s
where l.object_id = o.object_id
  and l.session_id = s.sid
       order by sid, s.serial#;
复制代码
查询导致锁表的SQL语句

复制代码
select l.session_id sid,
      s.serial#,
      l.locked_mode,
      l.oracle_username,
      s.user#,
      l.os_user_name,
      s.machine,
      s.terminal,
      a.sql_text,
      a.action
 from v$sqlarea a, v$session s, v$locked_object l
where l.session_id = s.sid
  and s.prev_sql_addr = a.address
order by sid, s.serial#;
复制代码
select b.sql_text
 from v$session a, v$sql b
where a.sid = 6 --session_id
         and a.SQL_ADDRESS = b.ADDRESS(+);
锁表查询

select count(*) from v$locked_object;
select * from v$locked_object;
查询哪个表被锁

select b.owner, b.object_name, a.session_id, a.locked_mode
 from v$locked_object a, dba_objects b
       where b.object_id = a.object_id;
查询哪个session引起的锁表

select b.username, b.sid, b.serial#, b.logon_time
 from v$locked_object a, v$session b
where a.session_id = b.sid
       order by b.logon_time;
杀掉进程

--3028:SID,15898:SERIAL#
alter system kill session '3028,15898';
显示正在等待锁的所有会话

select * from dba_waiters;
查询表空间使用情况（最常用）

复制代码
select upper(f.tablespace_name) "表空间名",
        d.tot_grootte_mb "表空间大小(m)",
        d.tot_grootte_mb - f.total_bytes "已使用空间(m)",
        to_char(round((d.tot_grootte_mb - f.total_bytes) /d.tot_grootte_mb * 100,2),'990.99') || '%' "使用比",
        f.total_bytes "空闲空间(m)",
        f.max_bytes "最大块(m)"
 from (select tablespace_name,
              round(sum(bytes) / (1024 * 1024), 2) total_bytes,
              round(max(bytes) / (1024 * 1024), 2) max_bytes
         from sys.dba_free_space
        group by tablespace_name) f,
         (select dd.tablespace_name,
                 round(sum(dd.bytes) / (1024 * 1024), 2) tot_grootte_mb
            from sys.dba_data_files dd
           group by dd.tablespace_name) d
where d.tablespace_name = f.tablespace_name
order by 1;
复制代码
查看空间占用多的表或索引

复制代码
SELECT
    segment_name 对象,
    segment_type 对象类型,
    bytes / 1024 / 1024 MB,
    tablespace_name 表空间名称
FROM
    user_segments
ORDER BY
    bytes DESC;
复制代码
注意：查看ins用户下的表的空间要使用ins用户登录数据库

表空间不足如何处理？

1、查看表在哪个表空间

select tablespace_name,table_name from user_tables where table_name='TB_BASE_USER';
2、获取用户的默认表空间

select username,default_tablespace from dba_users where username='SCOTT'
3、找出该表空间对应的数据文件及路径

select * from dba_data_files t where t.tablespace_name = 'USERS'
4、查看表空间使用情况

5、方案一：扩展表空间

alter database datafile 'D:\ORACLE\PRODUCT\11.2.0\DBHOME_1\DATABASE\TEST_SPACE.DBF' resize 500M
--自动增长
alter database datafile 'D:\ORACLE\PRODUCT\11.2.0\DBHOME_1\DATABASE\TEST_SPACE.DBF' autoextend onnext 50m maxsize 500m;
--增加数据文件
alter tablespace tablespace_name add datafile 'D:\ORACLE\PRODUCT\11.2.0\DBHOME_1\DATABASE\TEST_SPACE.DBF'  size 500M
6、方案二：移动至其他表空间

alter table ins.app_iops_dev_daily_nj move tablespace TBS_OSSMOB_ANDROID;
alter index index_name rebuild tablespace tablespace_name;--如果有索引的话必须重建索引
表空间尽量让free百分比保持在10%以上，如果低于10%就增加datafile或者resize datafile，一般数据文件不要超过2G

查找使用CPU多的用户session

复制代码
select a.sid,
       spid,
       status,
       substr(a.program, 1, 40) prog,
       a.terminal,
       osuser,
       value / 60 / 100 value
  from v$session a, v$process b, v$sesstat c
 where c.statistic# = 12
   and c.sid = a.sid
   and a.paddr = b.addr
 order by value desc;
复制代码
Oracle数据库查询效率低的SQL

检查低效率的语句

复制代码
select executions,
       disk_reads,
       buffer_gets,
       round((buffer_gets - disk_reads) / buffer_gets, 2) hit_radio,
       round(disk_reads / executions, 2) reads_per_run,
       sql_text
  from v$sqlarea
 where executions > 1 --1表示执行次数超过一次
   and buffer_gets > 0
   and (buffer_gets - disk_reads) / buffer_gets < 0.8 -- disk_reads是读硬盘的数量
 order by 4 desc;
复制代码
检查执行时间7月1日以后超过30秒的语句

select *
  from v$session_longops s
 where s.start_time > to_date('2017-07-01','yyyy-mm-dd')
   and s.LAST_UPDATE_TIME - s.START_TIME > 30/1440/60 
   order by start_time;
检查目前打开的游标情况

select sql_text, count(1) from v$open_cursor where user_name = 'X' group by sql_text order by count(1) desc
检查指定时间的语句情况

select * from v$sql s where first_load_time < '2017-07-01/00:00:00' order by s.EXECUTIONS desc;
监控当前数据库谁在运行什么SQL语句

select osuser, username, sql_text from v$session a, v$sqltext b where a.sql_address = b.address order by address, piece;
等待最多的sql

复制代码
select a.program,
      a.session_id,
       a.user_id,
       d.username,
       s.sql_text,
       sum(a.wait_time + a.time_waited) total_wait_time
  from v$active_session_history a, v$sqlarea s, dba_users d
 where a.sample_time between sysdate - 30 / 2880 and sysdate
   and a.sql_id = s.sql_id
   and a.user_id = d.user_id
 group by a.program, a.session_id, a.user_id, s.sql_text, d.username;
复制代码
查看消耗资源最多的sql

select hash_value, executions, buffer_gets, disk_reads, parse_calls
  from v$sqlarea
 where buffer_gets > 10000000
    or disk_reads > 1000000
 order by buffer_gets + 100 * disk_reads desc;
最占用资源的查询

复制代码
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;
复制代码
查看某条sql语句的资源消耗

select hash_value, buffer_gets, disk_reads, executions, parse_calls
  from v$sqlarea
 where hash_value = 228801498
   and address = hextoraw('cbd8e4b0');
查询会话执行的实际SQL

复制代码
select a.sid, a.username, s.sql_text
  from v$session a, v$sqltext s
 where a.sql_address = s.address
   and a.sql_hash_value = s.hash_value
   and a.status = 'ACTIVE'
 order by a.username, a.sid, s.piece;
复制代码
占用最多内存的sql语句

复制代码
select b.username username,
       a.buffer_gets gets,
       a.executions exec,
       a.buffer_gets / 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.buffer_gets > 100000
 order by a.buffer_gets desc;
复制代码
使用频率最高的5个查询语句

复制代码
select sql_text, executions
  from (select sql_text,
               executions,
               rank() over(order by executions desc) exec_rank
          from v$sql)
 where exec_rank <= 5;
复制代码
消耗磁盘读取最多的sql

复制代码
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;
复制代码
需要大量缓冲读取（逻辑读）操作的查询

复制代码
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;
复制代码
Oracle数据库查询当前连接会话数

复制代码
select s.value, s.sid, a.username
  from v$sesstat s, v$statname n, v$session a
 where n.statistic# = s.statistic#
   and name = 'session pga memory'
   and s.sid = a.sid
 order by s.value;
复制代码
等待最多的用户
复制代码
SELECT
    s.sid,
    s.username,
    sum(a.wait_time + a.time_waited) total_wait_time
FROM
    v$active_session_history a,
    v$session s
WHERE
    a.sample_time BETWEEN sysdate - 30 / 2880
AND sysdate
GROUP BY
    s.sid,
    s.username
ORDER BY
    total_wait_time DESC;
复制代码
具有最高等待的对象

复制代码
select o.owner,
       o.object_name,
       o.object_type,
       a.event,
       sum(a.wait_time + a.time_waited) total_wait_time
  from v$active_session_history a, dba_objects o
 where a.sample_time between sysdate - 30 / 2880 and sysdate
   and a.current_obj# = o.object_id
 group by o.owner, o.object_name, o.object_type, a.event
 order by total_wait_time desc;
复制代码
复制代码
select a.session_id,
       s.osuser,
       s.machine,
       s.program,
       o.owner,
       o.object_name,
       o.object_type,
       a.event,
       sum(a.wait_time + a.time_waited) total_wait_time
  from v$active_session_history a, dba_objects o, v$session s
 where a.sample_time between sysdate - 30 / 2880 and sysdate
   and a.current_obj# = o.object_id
   and a.session_id = s.sid
 group by o.owner,
          o.object_name,
          o.object_type,
          a.event,
          a.session_id,
          s.program,
          s.machine,
          s.osuser
 order by total_wait_time desc;
复制代码
Oracle数据库查看各用户资源占用的SQL语句

复制代码
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 #;    

本文由职坐标整理并发布，希望对同学们学习Oracle有所帮助，更多内容请关注职坐标数据库Oracle数据库频道！