查找使用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 cwherec.statistic#=12andc.sid=a.sidanda.paddr=b.addrorderbyvaluedesc;
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$sqlareawhereexecutions>1--1表示执行次数超过一次andbuffer_gets>0and(buffer_gets-disk_reads)/buffer_gets<0.8-- disk_reads是读硬盘的数量orderby4desc;
检查执行时间7月1日以后超过30秒的语句
select*fromv$session_longops swheres.start_time>to_date('2017-07-01','yyyy-mm-dd')ands.LAST_UPDATE_TIME-s.START_TIME>30/1440/60orderbystart_time;
检查目前打开的游标情况
selectsql_text,count(1)fromv$open_cursorwhereuser_name='X'groupbysql_textorderbycount(1)desc
检查指定时间的语句情况
select*fromv$sql swherefirst_load_time<'2017-07-01/00:00:00'orderbys.EXECUTIONSdesc;
监控当前数据库谁在运行什么SQL语句
selectosuser, username, sql_textfromv$session a, v$sqltext bwherea.sql_address=b.addressorderbyaddress, 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 dwherea.sample_timebetweensysdate-30/2880andsysdateanda.sql_id=s.sql_idanda.user_id=d.user_idgroupbya.program, a.session_id, a.user_id, s.sql_text, d.username;
查看消耗资源最多的sql
selecthash_value, executions, buffer_gets, disk_reads, parse_callsfromv$sqlareawherebuffer_gets>10000000ordisk_reads>1000000orderbybuffer_gets+100*disk_readsdesc;
最占用资源的查询
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 bwherea.parsing_user_id=b.user_idanda.disk_reads>100000orderbya.disk_readsdesc;
查看某条sql语句的资源消耗
selecthash_value, buffer_gets, disk_reads, executions, parse_callsfromv$sqlareawherehash_value=228801498andaddress=hextoraw('cbd8e4b0');
查询会话执行的实际SQL
selecta.sid, a.username, s.sql_textfromv$session a, v$sqltext swherea.sql_address=s.addressanda.sql_hash_value=s.hash_valueanda.status='ACTIVE'orderbya.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 bwherea.parsing_user_id=b.user_idanda.buffer_gets>100000orderbya.buffer_getsdesc;
使用频率最高的5个查询语句
selectsql_text, executionsfrom(selectsql_text, executions, rank()over(orderbyexecutionsdesc) exec_rankfromv$sql)whereexec_rank<=5;
消耗磁盘读取最多的sql
selectdisk_reads, sql_textfrom(selectsql_text, disk_reads, dense_rank()over(orderbydisk_readsdesc) disk_reads_rankfromv$sql)wheredisk_reads_rank<=5;
需要大量缓冲读取（逻辑读）操作的查询
selectbuffer_gets, sql_textfrom(selectsql_text, buffer_gets, dense_rank()over(orderbybuffer_getsdesc) buffer_gets_rankfromv$sql)wherebuffer_gets_rank<=5;
Oracle数据库查询当前连接会话数
selects.value, s.sid, a.usernamefromv$sesstat s, v$statname n, v$session awheren.statistic#=s.statistic#andname='session pga memory'ands.sid=a.sidorderbys.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 swherel.object_id=o.object_idandl.session_id=s.sidorderbysid, 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 lwherel.session_id=s.sidands.prev_sql_addr=a.addressorderbysid, s.serial#;
selectb.sql_textfromv$session a, v$sql bwherea.sid=6--session_idanda.SQL_ADDRESS=b.ADDRESS(+);
锁表查询
selectcount(*)fromv$locked_object;select*fromv$locked_object;
哪个表被锁
selectb.owner, b.object_name, a.session_id, a.locked_modefromv$locked_object a, dba_objects bwhereb.object_id=a.object_id;
哪个session引起的
selectb.username, b.sid, b.serial#, b.logon_timefromv$locked_object a, v$session bwherea.session_id=b.sidorderbyb.logon_time;
杀掉进程
--3028:SID,15898:SERIAL#altersystemkillsession'3028,15898';
显示正在等待锁的所有会话
select*fromdba_waiters;
查询表空间使用情况
selectupper(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_spacegroupbytablespace_name) f, (selectdd.tablespace_name,round(sum(dd.bytes)/(1024*1024),2) tot_grootte_mbfromsys.dba_data_files ddgroupbydd.tablespace_name) dwhered.tablespace_name=f.tablespace_nameorderby1;
查看空间占用多的表或索引，
SELECTsegment_name 对象, segment_type 对象类型, bytes/1024/1024MB, tablespace_name 表空间名称FROMuser_segmentsORDERBYbytesDESC;
注意：查看ins用户下的表的空间要使用ins用户登录数据库
表空间不足如何处理？
1、查看表在哪个表空间
selecttablespace_name,table_namefromuser_tableswheretable_name='TB_BASE_USER';
2、获取用户的默认表空间
selectusername,default_tablespacefromdba_userswhereusername='SCOTT'
3、找出该表空间对应的数据文件及路径
select*fromdba_data_files twheret.tablespace_name='USERS'
4、查看表空间使用情况
5、方案一：扩展表空间
alterdatabasedatafile'D:\ORACLE\PRODUCT\11.2.0\DBHOME_1\DATABASE\TEST_SPACE.DBF'resize 500M
--自动增长alterdatabasedatafile'D:\ORACLE\PRODUCT\11.2.0\DBHOME_1\DATABASE\TEST_SPACE.DBF'autoextend onnext 50m maxsize 500m;
--增加数据文件altertablespace tablespace_nameadddatafile'D:\ORACLE\PRODUCT\11.2.0\DBHOME_1\DATABASE\TEST_SPACE.DBF'size 500M
6、方案二：移动至其他表空间
altertableins.app_iops_dev_daily_nj move tablespace TBS_OSSMOB_ANDROID;alterindexindex_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_timeBETWEENsysdate-30/2880ANDsysdateGROUPBYs.sid, s.usernameORDERBYtotal_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 owherea.sample_timebetweensysdate-30/2880andsysdateanda.current_obj#=o.object_idgroupbyo.owner, o.object_name, o.object_type, a.eventorderbytotal_wait_timedesc;
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 swherea.sample_timebetweensysdate-30/2880andsysdateanda.current_obj#=o.object_idanda.session_id=s.sidgroupbyo.owner, o.object_name, o.object_type, a.event, a.session_id, s.program, s.machine, s.osuserorderbytotal_wait_timedesc;
Oracle数据库查看各用户资源占用的SQL语句
SELECTse.sid, ses.username, ses.osuser, n. NAME, se.VALUEFROMv$statname n, v$sesstat se, v$session sesWHEREn.statistic #=se.statistic#ANDse.sid=ses.sidANDses.usernameISNOTNULLANDn. NAMEIN('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')ORDERBYsid, n.statistic #;
网友评论