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 #;