查找使用 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;
查询 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
等待最多的用户
- 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 #;
来源: https://www.cnblogs.com/xyhero/p/283d2f21cca31a6af04bf56ed303f4c9.html