Oracle常用的系统查询语句整理

查找使用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 #;