Oracle锁表会影响查询效率么,oracle锁表查询，资源占用，连接会话，低效SQL等性能检查...

查找使用CPU多的用户session

selecta.sid,

spid,

status,

substr(a.program,1, 40) prog,

a.terminal,

osuser,

value/ 60 / 100valuefromv$session a, v$process b, v$sesstat cwhere c.statistic# = 12

and c.sid =a.sidand a.paddr =b.addrorder by value desc;

Oracle数据库查询效率低的SQL

检查低效率的语句

selectexecutions,

disk_reads,

buffer_gets,round((buffer_gets - disk_reads) / buffer_gets, 2) hit_radio,round(disk_reads / executions, 2) reads_per_run,

sql_textfromv$sqlareawhere 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 *

fromv$session_longops swhere 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

selecta.program,

a.session_id,

a.user_id,

d.username,

s.sql_text,sum(a.wait_time +a.time_waited) total_wait_timefromv$active_session_history a, v$sqlarea s, dba_users dwhere a.sample_time between sysdate - 30 / 2880 andsysdateand a.sql_id =s.sql_idand a.user_id = d.user_id

group by a.program, a.session_id, a.user_id, s.sql_text, d.username;

查看消耗资源最多的sql

selecthash_value, executions, buffer_gets, disk_reads, parse_callsfromv$sqlareawhere buffer_gets > 10000000

or disk_reads > 1000000

order by buffer_gets + 100 * disk_reads desc;

最占用资源的查询

selectb.username username,

a.disk_reads reads,

a.executionsexec,

a.disk_reads/ decode(a.executions, 0, 1, a.executions) rds_exec_ratio,

a.sql_text Statementfromv$sqlarea a, dba_users bwhere a.parsing_user_id = b.user_id

and a.disk_reads > 100000

order by a.disk_reads desc;

查看某条sql语句的资源消耗

selecthash_value, buffer_gets, disk_reads, executions, parse_callsfromv$sqlareawhere hash_value = 228801498

and address = hextoraw('cbd8e4b0');

查询会话执行的实际SQL

selecta.sid, a.username, s.sql_textfromv$session a, v$sqltext swhere a.sql_address =s.addressand a.sql_hash_value =s.hash_valueand a.status = 'ACTIVE'

order by a.username, a.sid, s.piece;

占用最多内存的sql语句

selectb.username username,

a.buffer_gets gets,

a.executionsexec,

a.buffer_gets/ decode(a.executions, 0, 1, a.executions) rds_exec_ratio,

a.sql_text Statementfromv$sqlarea a, dba_users bwhere a.parsing_user_id = b.user_id

and a.buffer_gets > 100000

order by a.buffer_gets desc;

使用频率最高的5个查询语句

selectsql_text, executionsfrom (selectsql_text,

executions,

rank()over(order by executions desc) exec_rankfromv$sql)where exec_rank <= 5;

消耗磁盘读取最多的sql

selectdisk_reads, sql_textfrom (selectsql_text,

disk_reads,

dense_rank()over(order by disk_reads desc) disk_reads_rankfromv$sql)where disk_reads_rank <= 5;

需要大量缓冲读取(逻辑读)操作的查询

selectbuffer_gets, sql_textfrom (selectsql_text,

buffer_gets,

dense_rank()over(order by buffer_gets desc) buffer_gets_rankfromv$sql)where buffer_gets_rank <= 5;

Oracle数据库查询当前连接会话数

selects.value, s.sid, a.usernamefromv$sesstat s, v$statname n, v$session awhere n.statistic# =s.statistic#and name = 'session pga memory'

and s.sid =a.sidorder by s.value;

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

selectl.session_id sid,

s.serial#,

l.locked_mode,

l.oracle_username,

l.os_user_name,

s.machine,

s.terminal,

o.object_name,

s.logon_timefromv$locked_object l, all_objects o, v$session swhere l.object_id = o.object_id

and l.session_id =s.sidorder by sid, s.serial#;

哪个sql语句导致锁表的

selectl.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.actionfromv$sqlarea a, v$session s, v$locked_object lwhere l.session_id =s.sidand s.prev_sql_addr =a.addressorder by sid, s.serial#;

selectb.sql_textfromv$session a, v$sql bwhere a.sid = 6 --session_id

and a.SQL_ADDRESS = b.ADDRESS(+);

锁表查询

select count(*) fromv$locked_object;select * from v$locked_object;

哪个表被锁

select b.owner, b.object_name, a.session_id, a.locked_modefromv$locked_object a, dba_objects bwhere b.object_id = a.object_id;

哪个session引起的

selectb.username, b.sid, b.serial#, b.logon_timefromv$locked_object a, v$session bwhere a.session_id =b.sidorder 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 (selecttablespace_name,round(sum(bytes) / (1024 * 1024), 2) total_bytes,round(max(bytes) / (1024 * 1024), 2) max_bytesfromsys.dba_free_spacegroup bytablespace_name) f,

(selectdd.tablespace_name,round(sum(dd.bytes) / (1024 * 1024), 2) tot_grootte_mbfromsys.dba_data_files ddgroup bydd.tablespace_name) dwhere d.tablespace_name =f.tablespace_nameorder by 1;

查看空间占用多的表或索引，

SELECTsegment_name 对象,

segment_type 对象类型,

bytes/ 1024 / 1024MB,

tablespace_name 表空间名称FROMuser_segmentsORDER BYbytesDESC;

注意：查看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 tableins.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

等待最多的用户

SELECTs.sid,

s.username,sum(a.wait_time +a.time_waited) total_wait_timeFROMv$active_session_history a,

v$session sWHEREa.sample_timeBETWEEN sysdate - 30 / 2880

ANDsysdateGROUP BYs.sid,

s.usernameORDER BYtotal_wait_timeDESC;

具有最高等待的对象

selecto.owner,

o.object_name,

o.object_type,

a.event,sum(a.wait_time +a.time_waited) total_wait_timefromv$active_session_history a, dba_objects owhere a.sample_time between sysdate - 30 / 2880 andsysdateand a.current_obj# = o.object_id

group by o.owner, o.object_name, o.object_type, a.eventorder by total_wait_time desc;

selecta.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_timefromv$active_session_history a, dba_objects o, v$session swhere a.sample_time between sysdate - 30 / 2880 andsysdateand a.current_obj# = o.object_id

and a.session_id =s.sidgroup byo.owner,

o.object_name,

o.object_type,

a.event,

a.session_id,

s.program,

s.machine,

s.osuserorder by total_wait_time desc;

Oracle数据库查看各用户资源占用的SQL语句

SELECTse.sid,

ses.username,

ses.osuser,

n. NAME,

se.VALUEFROMv$statname n,

v$sesstat se,

v$session sesWHEREn.statistic #=se.statistic#AND se.sid =ses.sidAND 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 BYsid,

n.statistic #;