oracle锁表查询,资源占用,连接会话,低效SQL等性能检查
查询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 #;