歡迎光臨
每天分享高質量文章
當前位置：知識星球 > 後端 > 正文

史上最全近百條Oracle DBA日常維護SQL指令碼指令

2019-04-26 分類：後端 閱讀(2204) 評論(0)

查詢碎片程度高（實際使用率小於30%）的表

 

可以收縮的表條件為什麼block>100，因為一些很小的表，只有幾行資料實際大小很小，但是block一次性分配就是5個（11g開始預設一次性分配1M的block大小了，見create table storged的NEXT引數），5個block相對於幾行小表資料來說就相差太大了。


演演算法中/0.9是因為塊的pfree一般為10%，所以一個塊最多隻用了90%，而且一行資料大於8KB時容易產生行連結，把一行分片儲存，一樣的一個塊連90%都用不滿 ，AVG_ROW_LEN還是比較準的，比如個人實驗情況一表6個欄位，一個number，其他5個都是char(100)但是實際資料都是’1111111’7位，AVG_ROW_LEN顯示依然為513 。

SELECT TABLE_NAME,(BLOCKS*8192/1024/1024)"理論大小M", (NUM_ROWS*AVG_ROW_LEN/1024/1024/0.9)"實際大小M", round((NUM_ROWS*AVG_ROW_LEN/1024/1024/0.9)/(BLOCKS*8192/1024/1024),3)*100||'%' "實際使用率%"  FROM USER_TABLES where blocks>100 and (NUM_ROWS*AVG_ROW_LEN/1024/1024/0.9)/(BLOCKS*8192/1024/1024)<0.3 order by (NUM_ROWS*AVG_ROW_LEN/1024/1024/0.9)/(BLOCKS*8192/1024/1024) desc

 

查詢索引碎片的比例

 

索引刪除行數除以索引總行數的百分比>30%即認為索引碎片大，也就是需要重建的索引 

select name,del_lf_rows,lf_rows, round(del_lf_rows/decode(lf_rows,0,1,lf_rows)*100,0)||'%' frag_pct from index_stats where round(del_lf_rows/decode(lf_rows,0,1,lf_rows)*100,0)>30;

叢集因子clustering_factor高的表 

 

叢集因子越接近塊數越好，接近行數則說明索引列的列值相等的行分佈極度雜湊，可能不走索引掃描而走全表掃描 ：

 

方法一

select tab.table_name,tab.blocks,tab.num_rows,ind.index_name,ind.clustering_factor, round(nvl(ind.clustering_factor,1)/decode(tab.num_rows,0,1,tab.num_rows),3)*100||'%' "叢集因子接近行數" from user_tables tab, user_indexes ind where tab.table_name=ind.table_name and tab.blocks>100 and nvl(ind.clustering_factor,1)/decode(tab.num_rows,0,1,tab.num_rows) between 0.35 and 3

方法二

select tab.owner,tab.table_name,tab.blocks,tab.num_rows,ind.index_name,ind.clustering_factor, round(nvl(ind.clustering_factor,1)/decode(tab.num_rows,0,1,tab.num_rows),3)*100||'%' "叢集因子接近行數" from dba_tables tab, dba_indexes ind where tab.table_name=ind.table_name and tab.owner  not in ('SYS','SYSTEM','WMSYS','DBSNMP','CTXSYS','XDB','ORDDATA','SYSMAN','CATALOG','APEX_030200','MDSYS','OLAPSYS','EXFSYS') and tab.blocks>100 and nvl(ind.clustering_factor,1)/decode(tab.num_rows,0,1,tab.num_rows) between 0.35 and 3

根據sid查spid或根據spid查sid 

select s.sid,s.serial#,p.spid,s.terminal,s.LOGON_TIME,s.status,s.PROGRAM,s.CLIENT_IDENTIFIER,s.machine,s.action,s.MODULE,s.PROCESS "客戶端機器行程號",s.osuser from v$session s,v$process p where  s.paddr=p.addr and s.sid=XX or p.spid=YY

根據sid檢視具體的sql陳述句，不要加條件v$session.status=’ ACTIVE’，比如toad對同一資料庫開兩個連線會話，都執行了一些陳述句，其中一個視窗查詢select * from v$session時會發現另一個視窗在v$session.status是INACTIVE，並不代表另一個視窗沒有執行過sql陳述句，而當前視窗是active狀態，對應的sql_id對應的陳述句就是select * from v$session而不是之前執行過的sql陳述句，ACTIVE表示當前正在執行sql。

 

一個sid可能執行過很多個sql，所以有時需要的sql透過如下查不到是正常的，比如查詢到某死鎖源sid，透過如下查詢可能只是個select陳述句，而真正引起死鎖的sql卻查不到，是因為可能這個sid持續了很長時間，這個sid之前執行的一些sql在v$sql可能已經被清除了。

 

方法一

select username,sid,SERIAL#,LOGON_TIME,status,PROGRAM,CLIENT_IDENTIFIER,machine,action,PROCESS "客戶端機器行程號",osuser,sql_text from v$session a,v$sqltext_with_newlines b where DECODE(a.sql_hash_value, 0, prev_hash_value, sql_hash_value)=b.hash_value and a.sid=&sid order by piece; 

 

方法二

select username,sid,SERIAL#,LOGON_TIME,status,sql_fulltext,PROGRAM,CLIENT_IDENTIFIER,machine,a.action,PROCESS "客戶端機器行程號",osuser from v$session a,v$sql b where  DECODE(a.sql_hash_value, 0, prev_hash_value, sql_hash_value)=b.hash_value and a.sid=&sid

 

如果上面陳述句執行太慢，則按如下兩步

 

select  sql_hash_value, prev_hash_value, username,sid,SERIAL#,LOGON_TIME,status, PROGRAM,CLIENT_IDENTIFIER,machine,action,PROCESS "客戶端機器行程號",osuser from v$session where  sid=&sidselect  sql_fulltext from v$sql where  hash_value=XX

 

–XX為上面 sql_hash_value，如果 sql_hash_value為0，則XX為上面 prev_hash_value

根據spid查詢具體的sql陳述句(不要加條件v$session.status=’ ACTIVE’,比如toad對同一資料庫開兩個連線會話，都執行了一些陳述句，其中一個視窗查詢select * from v$session時會發現另一個視窗在v$session.status是INACTIVE，並不代表另一個視窗沒有執行過sql陳述句，而當前視窗是active狀態，對應的sql_id對應的陳述句就是select * from v$session而不是之前執行過的sql陳述句，ACTIVE表示當前正在執行sql。)

Select ss.SID,ss.SERIAL#,ss.LOGON_TIME,pr.SPID,sa.SQL_FULLTEXT,ss.machine, ss.TERMINAL,ss.PROGRAM,ss.USERNAME,ss.CLIENT_IDENTIFIER,ss.action,ss.PROCESS "客戶端機器行程號", ss.STATUS, ss.OSUSER,ss.status,ss.last_call_et,sa.sql_text  from v$process pr, v$session ss, v$sql sa  where pr.ADDR = ss.PADDR  and  DECODE(ss.sql_hash_value, 0, prev_hash_value, sql_hash_value)=sa.hash_value and pr.spid=&spid;

檢視歷史session_id的SQL來自哪個IP

 

檢視trace檔案名就可以知道spid,trace檔案裡面有sid和具體sql，如果trace存在incident，那trace就看不到具體sql，但是可以在incident檔案中看到具體的sql，如DW_ora_17751.trc中17751就是spid，裡面有這樣的內容Incident 115 created, dump file: /XX/incident/incdir_115/DW_ora_17751_i115.trc，那麼在DW_ora_17751_i115.trc就可以看到具體的sql陳述句)

DB_ora_29349.trc中出現 

*** SESSION ID:(5057.12807) 2016-10-26 14:45:52.726

透過表V$ACTIVE_SESSION_HISTORY來查

select a.sql_id,a.machine,a.* from V$ACTIVE_SESSION_HISTORY a where a.session_id=5057 and a.SESSION_SERIAL#=12807 

 

查詢上面的machine的IP

select s.sid,s.serial#,s.LOGON_TIME,s.machine,p.spid,p.terminal from v$session s,v$process p where  s.paddr=p.addr and s.machine='localhost' 

 

透過上面的spid在oracle伺服器上執行netstat -anp |grep spid即可 

[oracle@dwdb trace]$ netstat -anp |grep 17630 tcp      210      0 192.168.64.228:11095        192.168.21.16:1521          ESTABLISHED 17630/oracleDB tcp        0      0 ::ffff:192.168.64.228:1521  ::ffff:192.168.64.220:59848 ESTABLISHED 17630/oracleDB

出現兩個，說明來自220，連線了228資料庫伺服器，但是又透過228伺服器的dblink去連線了16伺服器 

查詢死鎖堵塞的會話sid 

最簡單的一個SQL 

select * from V$SESSION_BLOCKERS select * from dba_waiters

最常用的一個SQL 

select sid,status,LOGON_TIME,sql_id,blocking_session "死鎖直接源",FINAL_BLOCKING_SESSION "死鎖最終源",event,seconds_in_wait "會話鎖住時間_S",LAST_CALL_ET "會話STATUS持續時間_S" from v$session where state='WAITING' and BLOCKING_SESSION_STATUS='VALID' and FINAL_BLOCKING_SESSION_STATUS='VALID'

可以把兩者SID放入v$session，發現LOGON_TIME欄位FINAL_BLOCKING_SESSION比SID要早 

BLOCKING_SESSION:Session identifier of the blocking session. This column is valid only if BLOCKING_SESSION_STATUS has the value VALID.
FINAL_BLOCKING_SESSION:Session identifier of the blocking session. This column is valid only if FINAL_BLOCKING_SESSION_STATUS has the value VALID. 

如果遇到RAC環境，一定要用gv$來查，並且執行alter system kill session ‘sid,serial#’要到RAC對應的實體上去執行 

把上面被堵塞會話的sid代入如下陳述句，可以發現鎖住的物件和物件的哪一行(如果sid是堵塞源的會話，則 row_wait_obj#=-1，表示鎖持有者，就是死鎖源了 ) 

select s.sid,s.username,d.owner,d.object_name,s.row_wait_obj#,s.row_wait_row#,s.row_wait_file#,s.row_wait_block# from v$session s,dba_objects d where s.row_wait_obj#=d.object_id and s.sid  in(XX,XX) 

 

查詢鎖住的DDL物件 

select d.session_id,s.SERIAL#,d.name from dba_ddl_locks d,v$session s where d.owner='MKLMIGEM' and d.SESSION_ID=s.sid

查詢超過兩個小時的不活動會話 

select s.sid,s.serial#,p.spid,s.LOGON_TIME,s.LAST_CALL_ET,s.status,s.PROGRAM,s.CLIENT_IDENTIFIER,s.machine,s.terminal,s.action,s.PROCESS "客戶端機器行程號",s.osuser from v$session s,v$process p  where  s.paddr=p.addr and s.sid in (select sid from v$session where machine<>&DB;伺服器名稱 and status='INACTIVE' and sql_id is null and LAST_CALL_ET>7200)

查詢堵塞別的會話超過30分鐘且自身是不活動的會話 

select username,sid,serial#,status,seconds_in_wait,LAST_CALL_ET from v$session where sid in (select FINAL_BLOCKING_SESSION from v$session  where state='WAITING' and BLOCKING_SESSION_STATUS='VALID' and FINAL_BLOCKING_SESSION_STATUS='VALID') and status='INACTIVE' and sql_id is null and seconds_in_wait>1800

查詢可能存在連線池空閑初始配置過大的連線（來自同一臺機器的同一個程式的狀態為INACTIVE的連線非常多） 

select count(ss.SID),ss.machine,ss.status,ss.TERMINAL,ss.PROGRAM,ss.USERNAME,ss.CLIENT_IDENTIFIER  from v$session ss group by ss.machine,ss.status,ss.TERMINAL,ss.PROGRAM,ss.USERNAME,ss.CLIENT_IDENTIFIER having count(ss.SID)>10

查詢當前正在執行的sql 

SELECT s.sid,s.serial#,s.username,spid,v$sql.sql_id,machine,s.terminal,s.program,sql_text  FROM v$process,v$session s,v$sql   WHERE addr=paddr and s.sql_id=v$sql.sql_id AND sql_hash_value=hash_value and s.STATUS='ACTIVE'

查詢正在執行的SCHEDULER_JOB 

select owner,job_name,sid,b.SERIAL#,b.username,spid from ALL_SCHEDULER_RUNNING_JOBS,v$session b,v$process  where session_id=sid and paddr=addr

查詢正在執行的dbms_job 

select job,b.sid,b.SERIAL#,b.username,spid from DBA_JOBS_RUNNING a ,v$session b,v$process  where a.sid=b.sid and paddr=addr

查詢一個會話session、process平均消耗多少PGA記憶體，檢視下麵avg_used_M值 

select round(sum(pga_used_mem)/1024/1024,0) total_used_M, round(sum(pga_used_mem)/count(1)/1024/1024,0) avg_used_M, round(sum(pga_alloc_mem)/1024/1024,0) total_alloc_M, round(sum(pga_alloc_mem)/count(1)/1024/1024,0) avg_alloc_M from v$process; 

 

TOP 10 執行次數排序 

select * from (select executions,username,PARSING_USER_ID,sql_id,sql_text       from v$sql,dba_users where user_id=PARSING_USER_ID order by executions desc) where rownum <=5;

TOP 10 物理讀排序（消耗IO排序，即最差效能SQL、低效SQL排序） 

select * from (select DISK_READS,username,PARSING_USER_ID,sql_id,ELAPSED_TIME/1000000,sql_text       from v$sql,dba_users where user_id=PARSING_USER_ID order by DISK_READS desc) where rownum <=5;

註意：不要使用DISK_READS/ EXECUTIONS來排序，因為任何一條陳述句不管執行幾次都會耗邏輯讀和cpu，可能不會耗物理讀（遇到LRU還會耗物理讀，LRU規則是執行最不頻繁的且最後一次執行時間距離現在最久遠的就會被互動出buffer cache），是因為buffer cache存放的是資料塊，去資料塊裡找行一定會消耗cpu和邏輯讀的。Shared pool執行存放sql的解析結果，sql執行的時候只是去share pool中找hash value，如果有匹配的就是軟解析。所以物理讀邏輯讀是在buffer cache中，軟解析硬解析是在shared pool。

TOP 10 邏輯讀排序（消耗記憶體排序） 

select * from (select BUFFER_GETS,username,PARSING_USER_ID,sql_id,ELAPSED_TIME/1000000,sql_text       from v$sql,dba_users where user_id=PARSING_USER_ID order by BUFFER_GETS desc) where rownum <=5;

註意：不要使用BUFFER_GETS/ EXECUTIONS來排序，因為任何一條陳述句不管執行幾次都會耗邏輯讀和cpu，可能不會耗物理讀（遇到LRU還會耗物理讀，LRU規則是執行最不頻繁的且最後一次執行時間距離現在最久遠的就會被互動出buffer cache），是因為buffer cache存放的是資料塊，去資料塊裡找行一定會消耗cpu和邏輯讀的。Shared pool執行存放sql的解析結果，sql執行的時候只是去share pool中找hash value，如果有匹配的就是軟解析。所以物理讀邏輯讀是在buffer cache中，軟解析硬解析是在shared pool）

TOP 10 CPU排序(單位秒=cpu_time/1000000) 

select * from (select CPU_TIME/1000000,username,PARSING_USER_ID,sql_id,ELAPSED_TIME/1000000,sql_text       from v$sql,dba_users where user_id=PARSING_USER_ID order by CPU_TIME/1000000 desc) where rownum <=5;

註意：不要使用CPU_TIME/ EXECUTIONS來排序，因為任何一條陳述句不管執行幾次都會耗邏輯讀和cpu，可能不會耗物理讀（遇到LRU還會耗物理讀，LRU規則是執行最不頻繁的且最後一次執行時間距離現在最久遠的就會被互動出buffer cache），是因為buffer cache存放的是資料塊，去資料塊裡找行一定會消耗cpu和邏輯讀的。Shared pool執行存放sql的解析結果，sql執行的時候只是去share pool中找hash value，如果有匹配的就是軟解析。所以物理讀邏輯讀是在buffer cache中，軟解析硬解析是在shared pool。

查詢等待事件 

select event,sum(decode(wait_time,0,0,1)) "之前等待次數", sum(decode(wait_time,0,1,0))  "正在等待次數",count(*) from v$session_wait  group by event order by 4 desc

 

查詢當前等待事件對應的物件

select distinct wait_class#,wait_class from v$session_wait_class order by 1

 

以上sql發現wait_class#=6的是空閑等待

select * from(select sid,event,p1text,p1,p2text,p2,p3text,p3,WAIT_TIME,SECONDS_IN_WAIT,wait_class# from v$session_wait where wait_class# <> 6 order by wait_time desc)where rownum <=10;

 

能查出等待的物件是否來自資料檔案（如果以上查到p1text是file#或file number）

 

select * from(select owner,segment_name,segment_type,block_id,bytes from dba_extents where file_id=p1 and block_idorder="" by="" block_id="" desc)

where rownum<2

 

把上面第二個sql結果的p1、p2值代入上述sql的file_id、block_id

透過AWR的top sql或v$sql.sql_text檢視是否有該物件的陳述句，檢查該陳述句的執行計劃就可以查出問題所在。

查詢當前正在消耗臨時空間的sql陳述句

 

方法一：

Select distinct se.username,         se.sid,         su.blocks * to_number(rtrim(p.value))/1024/1024 as space_G,         su.tablespace,         sql_text    from V$TEMPSEG_USAGE su, v$parameter p, v$session se, v$sql s   where p.name = 'db_block_size'     and su.session_addr=se.saddr     and su.sqlhash=s.hash_value     and su.sqladdr=s.address     and se.STATUS='ACTIVE'

 

方法二：

select v$sql.sql_id,v$sql.sql_fulltext,swa.TEMPSEG_SIZE/1024/1024 TEMPSEG_M, swa.*  from v$sql_workarea_active swa,v$sql where swa.sql_id=v$sql.sql_id and swa.NUMBER_PASSES>0

查詢因PGA不足而使用臨時表空間的最頻繁的10條SQL陳述句 

select * from  ( select OPERATION_TYPE,ESTIMATED_OPTIMAL_SIZE,ESTIMATED_ONEPASS_SIZE, sum(OPTIMAL_EXECUTIONS) optimal_cnt,sum(ONEPASS_EXECUTIONS) as onepass_cnt, sum(MULTIPASSES_EXECUTIONS) as mpass_cnt,s.sql_text from V$SQL_WORKAREA swa, v$sql s  where swa.sql_id=s.sql_id  group by OPERATION_TYPE,ESTIMATED_OPTIMAL_SIZE,ESTIMATED_ONEPASS_SIZE,sql_text having sum(ONEPASS_EXECUTIONS+MULTIPASSES_EXECUTIONS)>0  order by sum(ONEPASS_EXECUTIONS) desc )  where rownum<10

查詢正在消耗PGA的SQL 

select s.sql_text, sw.EXPECTED_SIZE, sw.ACTUAL_MEM_USED,sw.NUMBER_PASSES, sw.TEMPSEG_SIZE from v$sql_workarea_active sw, v$sql s where sw.sql_id=s.sql_id;

查詢需要使用系結變數的sql，10G以後推薦第二種 

註意：任何一條執行過的陳述句不管執行了幾次在V$SQL中都只有一條記錄，V$SQL中會記錄執行了幾次。兩條一模一樣的陳述句但是在不同的schema下執行的兩種結果，如select * from t1.test在sye、system下執行則V$SQL只有一條記錄(誰先執行則PARSING_SCHEMA_NAME顯示誰)。如在sys和system都執行select * from test則V$SQL中有兩條記錄，兩條記錄的CHILD_NUMBER和PARSING_SCHEMA_NAME不一樣。

 

同一個使用者下執行一樣的陳述句如果大小寫不一樣或加了hint的話則會出現多個V$SQL記錄，說明V$SQL對應的sql陳述句必須一模一樣，如果alter system flush shared_pool（主站慎用）後再執行一樣的陳述句，發現陳述句在V$SQL中的SQL_ID和HASH_VALUE與之前的一樣，說明SQL_ID和HASH_VALUE應該是oracle自己的一套演演算法來的，只是根據sql陳述句內容來進行轉換，sql陳述句不變則SQL_ID和HASH_VALUE也不變。

第一種 

select * from ( select count(*),sql_id, substr(sql_text,1,40) from v$sql group by sql_id, substr(sql_text,1,40) having count(*) > 10 order by count(*) desc) where rownum<10

第二種 

count(1)>10表示類陳述句運行了10次以上 select sql_id, FORCE_MATCHING_SIGNATURE, sql_text from v$SQL where FORCE_MATCHING_SIGNATURE in (select /*+ unnest */ FORCE_MATCHING_SIGNATURE from v$sql where FORCE_MATCHING_SIGNATURE > 0 and FORCE_MATCHING_SIGNATURE != EXACT_MATCHING_SIGNATURE group by FORCE_MATCHING_SIGNATURE having count(1) > 10)

 檢視資料檔案可用百分比

 

dba_free_space並不會包含所有file_id，如果該資料檔案滿了，則 dba_free_space.file_id沒有該資料檔案，所以以下sql中 a.file_id=b.file_id的條件過濾後是不會有所有file_id的 

 

select b.file_id,b.tablespace_name,b.file_name,b.AUTOEXTENSIBLE, ROUND(b.MAXBYTES/1024/1024/1024,2) ||'G'  "檔案最大可用總容量", ROUND(b.bytes/1024/1024/1024,2) ||'G'  "檔案總容量", ROUND((b.bytes-sum(nvl(a.bytes,0)))/1024/1024/1024,2)||'G' "檔案已用容量", ROUND(sum(nvl(a.bytes,0))/1024/1024/1024,2)||'G' "檔案可用容量", ROUND(sum(nvl(a.bytes,0))/(b.bytes),2)*100||'%' "檔案可用百分比" from dba_free_space a,dba_data_files b where a.file_id=b.file_id group by b.tablespace_name,b.file_name,b.file_id,b.bytes,b.AUTOEXTENSIBLE,b.MAXBYTES order by b.tablespace_name;

–如下為標準版 

select b.file_id,b.tablespace_name,b.file_name,b.AUTOEXTENSIBLE, ROUND(b.MAXBYTES/1024/1024/1024,2) ||'G'  "檔案最大可用總容量", ROUND(b.bytes/1024/1024/1024,2) ||'G'  "檔案當前總容量", ROUND((b.bytes-sum(nvl(a.bytes,0)))/1024/1024/1024,2)||'G' "檔案當前已用容量", ROUND((decode(AUTOEXTENSIBLE,'NO',b.BYTES,b.MAXBYTES)+sum(nvl(a.bytes,0))-b.bytes)/1024/1024/1024,2)||'G' "檔案可用容量", ROUND((decode(AUTOEXTENSIBLE,'NO',b.BYTES,b.MAXBYTES)+sum(nvl(a.bytes,0))-b.bytes)/(decode(AUTOEXTENSIBLE,'NO',b.BYTES,b.MAXBYTES)),2)*100||'%' "檔案可用百分比" from dba_free_space a,dba_data_files b where a.file_id=b.file_id group by b.tablespace_name,b.file_name,b.file_id,b.bytes,b.AUTOEXTENSIBLE,b.MAXBYTES order by decode(AUTOEXTENSIBLE,'NO',b.BYTES,b.MAXBYTES)+sum(nvl(a.bytes,0))-b.bytes;

檢視資料庫檔案的實際總量，單位G 

select a.datafile_size+b.tempfile_size-c.free_size from  (select sum(bytes/1024/1024/1024) datafile_size from dba_data_files ) a, (select sum(bytes/1024/1024/1024) tempfile_size from dba_temp_files ) b, (select sum(bytes/1024/1024/1024) free_size from dba_free_space ) c

查看錶空間可用百分比（ dba_free_space不會包含所有tablespace，如果一個表空間的資料檔案都滿了，則這個表空間不會出現在dba_free_space中 ） 

select b.tablespace_name,a.maxsize max_M,a.total total_M,b.free free_M,round((b.free/a.total)*100) "% Free" from (select tablespace_name, sum(bytes/(1024*1024)) total ,sum(MAXBYTES/(1024*1024)) maxsize from dba_data_files group by tablespace_name) a, (select tablespace_name, round(sum(bytes/(1024*1024))) free from dba_free_space group by tablespace_name) b WHERE a.tablespace_name = b.tablespace_name order by "% Free";

–如下為標準版 

select b.tablespace_name,a.maxsize max_M,a.total total_M,b.free free_M,round(((a.maxsize+b.free-a.total)/a.maxsize)*100) "% Free" from (select tablespace_name, sum(bytes/(1024*1024)) total ,sum((decode(AUTOEXTENSIBLE,'NO',BYTES,MAXBYTES))/(1024*1024)) maxsize from dba_data_files group by tablespace_name) a, (select tablespace_name, round(sum(bytes/(1024*1024))) free from dba_free_space group by tablespace_name) b WHERE a.tablespace_name = b.tablespace_name order by "% Free";

檢視臨時表空間使用率 

 

方法一

SELECT temp_used.tablespace_name,round(total),used,            round(total - used) as "Free",            round(nvl(total-used, 0) * 100/total,1) "Free percent"       FROM (SELECT tablespace_name, SUM(bytes_used)/1024/1024 used               FROM GV$TEMP_SPACE_HEADER              GROUP BY tablespace_name) temp_used,            (SELECT tablespace_name, SUM(decode(autoextensible,'YES',MAXBYTES,bytes))/1024/1024 total               FROM dba_temp_files              GROUP BY tablespace_name) temp_total      WHERE temp_used.tablespace_name = temp_total.tablespace_name

方法二

SELECT a.tablespace_name, round(a.BYTES/1024/1024) total_M, round(a.bytes/1024/1024 - nvl(b.bytes/1024/1024, 0)) free_M, round(b.bytes/1024/1024) used,round(b.using/1024/1024) using   FROM (SELECT   tablespace_name, SUM (decode(autoextensible,'YES',MAXBYTES,bytes)) bytes FROM dba_temp_files GROUP BY tablespace_name) a,        (SELECT   tablespace_name, SUM (bytes_cached) bytes,sum(bytes_used) using FROM v$temp_extent_pool GROUP BY tablespace_name) b WHERE a.tablespace_name = b.tablespace_name(+)

查詢undo表空間使用情況 

select tablespace_name,status,sum(bytes)/1024/1024 M from dba_undo_extents group by tablespace_name,status

查詢使用undo比較多的SQL 

 

 select *from (  select maxqueryid, round(sum(undoblks )*8/1024) consumed_size_MB from v$undostat    group by maxqueryid order by  consumed_size_MB desc ) where rownum<10; 

估計undo需要多大 

SELECT (UR * (UPS * DBS)) AS "Bytes"  FROM (select max(tuned_undoretention) AS UR from v$undostat),  (SELECT undoblks/((end_time-begin_time)*86400) AS UPS  FROM v$undostat  WHERE undoblks = (SELECT MAX(undoblks) FROM v$undostat)),  (SELECT block_size AS DBS  FROM dba_tablespaces  WHERE tablespace_name = (SELECT UPPER(value) FROM v$parameter WHERE name = 'undo_tablespace'));

產生undo的當前活動會話是哪些 

 

方法一

 

SELECT a.inst_id, a.sid, c.username, c.osuser, c.program, b.name, a.value, d.used_urec, d.used_ublk FROM gv$sesstat a, v$statname b, gv$session c, gv$transaction d WHERE a.statistic# = b.statistic# AND a.inst_id = c.inst_id AND a.sid = c.sid AND c.inst_id = d.inst_id AND c.saddr = d.ses_addr AND b.name = 'undo change vector size' AND a.value>0 ORDER BY a.value DESC

 

方法二

 

select s.sid,s.serial#,s.sql_id,v.usn,r.status, v.rssize/1024/1024 mbfrom dba_rollback_segs r, v$rollstat v,v$transaction t,v$session sWhere r.segment_id = v.usn and v.usn=t.xidusn and t.addr=s.taddrorder by 6 desc;

 

檢視ASM磁碟組使用率 

select name,round(total_mb/1024) "總容量",round(free_mb/1024) "空閑空間",round((free_mb/total_mb)*100) "可用空間比例" from gv$asm_diskgroup

統計每個使用者使用表空間率 

SELECT c.owner                                  "使用者",        a.tablespace_name                        "表空間名",        total/1024/1024                          "表空間大小M",        free/1024/1024                           "表空間剩餘大小M",        ( total - free )/1024/1024               "表空間使用大小M",        Round(( total - free ) / total, 4) * 100 "表空間總計使用率   %",        c.schemas_use/1024/1024                  "使用者使用表空間大小M",        round((schemas_use)/total,4)*100         "使用者使用表空間率  %"         FROM   (SELECT tablespace_name,                Sum(bytes) free         FROM   DBA_FREE_SPACE         GROUP  BY tablespace_name) a,        (SELECT tablespace_name,                Sum(bytes) total         FROM   DBA_DATA_FILES         GROUP  BY tablespace_name) b,        (Select owner ,Tablespace_Name,                 Sum(bytes) schemas_use          From Dba_Segments          Group By owner,Tablespace_Name) c WHERE  a.tablespace_name = b.tablespace_name and a.tablespace_name =c.Tablespace_Name order by "使用者","表空間名" 

 

檢視閃回區\快速恢復區空間使用率 

select sum(percent_space_used)||'%' "已使用空間比例" from V$RECOVERY_AREA_USAGE 或 select round(100*(a.space_used/space_limit),2)||'%' "已使用空間比例",a.* from v$recovery_file_dest a;

檢視僵死行程，分兩種（一種是會話不在的，另一種是會話標記為killed的但是會話還在的） 


alter system kill session一執行則session即標記為KILLED，但是如果會話產生的資料量大則這個kill可能會比較久，在這個過程中session標記為KILLED但是這個會話還在V$session中，則V$session.paddr還在，所以可以匹配到V$process.addr，所以process行程還在；當kill過程執行完畢，則這個會話即不在V$session中

會話不在的 

select * from v$process where addr not in (select paddr from v$session) and pid not in (1,17,18)

會話還在的，但是會話標記為killed 

select * from v$process where addr in (select paddr from v$session where status='KILLED') 

 

再根據上述結果中的SPID透過如下命令可以檢視到process的啟動時間 

ps auxw|head -1;ps auxw|grep SPID

32. 檢視行遷移或行連結的表 

select * From dba_tables where nvl(chain_cnt,0)<>0 chain_cnt ：Number of rows in the table that are chained from one data block to another or that have migrated to a new block, requiring a link to preserve the old rowid. This column is updated only after you analyze the table.

資料緩衝區命中率（百分比小於90就要加大db_cache_size） 

SELECT a.VALUE+b.VALUE logical_reads, c.VALUE phys_reads,  round(100*(1-c.value/(a.value+b.value)),2)||'%' hit_ratio  FROM v$sysstat a,v$sysstat b,v$sysstat c  WHERE a.NAME='db block gets'  AND b.NAME='consistent gets'  AND c.NAME='physical reads';

方法二

SELECT DB_BLOCK_GETS+CONSISTENT_GETS Logical_reads,PHYSICAL_READS phys_reads, round(100*(1-(PHYSICAL_READS/(DB_BLOCK_GETS+CONSISTENT_GETS))),2)||'%' "Hit Ratio" FROM V$BUFFER_POOL_STATISTICS WHERE NAME='DEFAULT';

共享池命中率（百分比小於90就要加大shared_pool_size） 

以下兩者可以根據個人理解運用

select sum(pinhits)/sum(pins)*100 from v$librarycache; select sum(pinhits-reloads)/sum(pins)*100 from v$librarycache;

查詢歸檔日誌切換頻率 

select sequence#,to_char(first_time,'yyyymmdd_hh24:mi:ss')  firsttime,round((first_time-lag(first_time) over(order by first_time))*24*60,2) minutes from  v$log_history where first_time > sysdate - 3 order by first_time,minutes; 或 select sequence#,to_char(first_time,'yyyy-mm-dd hh24:mi:ss') First_time,First_change#,switch_change# from v$loghist where first_time>sysdate-3 order by 1; 或 SELECT TO_CHAR(first_time, 'MM/DD') DAY, SUM(DECODE(TO_CHAR(first_time, 'HH24'), '00', 1, 0)) H00, SUM(DECODE(TO_CHAR(first_time, 'HH24'), '01', 1, 0)) H01, SUM(DECODE(TO_CHAR(first_time, 'HH24'), '02', 1, 0)) H02, SUM(DECODE(TO_CHAR(first_time, 'HH24'), '03', 1, 0)) H03, SUM(DECODE(TO_CHAR(first_time, 'HH24'), '04', 1, 0)) H04, SUM(DECODE(TO_CHAR(first_time, 'HH24'), '05', 1, 0)) H05, SUM(DECODE(TO_CHAR(first_time, 'HH24'), '06', 1, 0)) H06, SUM(DECODE(TO_CHAR(first_time, 'HH24'), '07', 1, 0)) H07, SUM(DECODE(TO_CHAR(first_time, 'HH24'), '08', 1, 0)) H08, SUM(DECODE(TO_CHAR(first_time, 'HH24'), '09', 1, 0)) H09, SUM(DECODE(TO_CHAR(first_time, 'HH24'), '10', 1, 0)) H10, SUM(DECODE(TO_CHAR(first_time, 'HH24'), '11', 1, 0)) H11, SUM(DECODE(TO_CHAR(first_time, 'HH24'), '12', 1, 0)) H12, SUM(DECODE(TO_CHAR(first_time, 'HH24'), '13', 1, 0)) H13, SUM(DECODE(TO_CHAR(first_time, 'HH24'), '14', 1, 0)) H14, SUM(DECODE(TO_CHAR(first_time, 'HH24'), '15', 1, 0)) H15, SUM(DECODE(TO_CHAR(first_time, 'HH24'), '16', 1, 0)) H16, SUM(DECODE(TO_CHAR(first_time, 'HH24'), '17', 1, 0)) H17, SUM(DECODE(TO_CHAR(first_time, 'HH24'), '18', 1, 0)) H18, SUM(DECODE(TO_CHAR(first_time, 'HH24'), '19', 1, 0)) H19, SUM(DECODE(TO_CHAR(first_time, 'HH24'), '20', 1, 0)) H20, SUM(DECODE(TO_CHAR(first_time, 'HH24'), '21', 1, 0)) H21, SUM(DECODE(TO_CHAR(first_time, 'HH24'), '22', 1, 0)) H22, SUM(DECODE(TO_CHAR(first_time, 'HH24'), '23', 1, 0)) H23, COUNT(*) TOTAL FROM (SELECT ROWNUM RN, FIRST_TIME FROM V$LOG_HISTORY WHERE first_time>sysdate-18 and FIRST_TIME>ADD_MONTHS(SYSDATE,-1) ORDER BY FIRST_TIME) GROUP BY TO_CHAR(first_time, 'MM/DD') ORDER BY MIN(RN); 

 

查詢lgwr行程寫日誌時每執行一次lgwr需要多少秒，在state是waiting的情況下，某個等待編號seq#下，seconds_in_wait達多少秒，就是lgwr行程寫一次IO需要多少秒 

select event,state,seq#,seconds_in_wait,program from v$session where program like '%LGWR%'  and state='WAITING'

查詢沒有索引的表 

Select table_name from user_tables where table_name not in (select table_name from user_indexes) Select table_name from user_tables where table_name not in (select table_name from user_ind_columns)

查詢一個AWR週期內的平均session數、OS平均負載、平均db time、平均每秒多少事務 

select to_char(max(BEGIN_TIME),'yyyy-mm-dd hh24:mi')||to_char(max(end_time),'--hh24:mi') time, snap_id,     trunc(sum(case metric_name when 'Session Count' then average end),2) sessions, trunc(sum(case metric_name when 'Current OS Load' then average end),2) OS_LOAD, (trunc(sum(case metric_name when 'Database Time Per Sec' then average end),2)/100)*(ceil((max(end_time)-max(BEGIN_TIME))*24*60*60)) Database_Time_second, trunc(sum(case metric_name when 'User Transaction Per Sec' then average end),2) User_Transaction_Per_Sec from dba_hist_sysmetric_summary group by snap_id order by snap_id;

–Database Time Per Sec對應值的單位是百分一秒/每秒 
–(/100)*(ceil((max(end_time)-max(BEGIN_TIME))*24*60*60))是代表每個snap週期內的總秒數，oracle 兩個時間相減預設的是天數,*24*60*60 為相差的秒數 
–這個SQL查到的DB TIME比較準確，和awr上面的db time比較一致 

查詢產生熱塊較多的物件 

x$bh .tch(Touch)表示訪問次數越高，熱點快競爭問題就存在 

SELECT e.owner, e.segment_name, e.segment_type FROM dba_extents e, (SELECT * FROM (SELECT addr,ts#,file#,dbarfil,dbablk,tch FROM x$bh ORDER BY tch DESC) WHERE ROWNUM < 11) b WHERE e.relative_fno = b.dbarfil AND e.block_id <= b.dbablk AND e.block_id + e.blocks > b.dbablk;

手工建立快照的陳述句 

exec dbms_workload_repository.create_snapshot; 

 

AWR設定每隔30分鐘收集一次報告，保留14天的報告 

exec DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(retention=>14*24*60, interval=>30); select * from dba_hist_wr_control;

AWR基線檢視和建立 

select * from dba_hist_baseline; exec DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE(start_snap_id=>7550,end_snap_id=>7660,baseline_name=>'am_baseline');

匯出AWR報告的SQL陳述句 

select * from dba_hist_snapshot select * from table(dbms_workload_repository.awr_report_html(DBID, INSTANCE_NUMBER, startsnapid,endsnapid)) select * from TABLE(DBMS_WORKLOAD_REPOSITORY.awr_diff_report_html(DBID, INSTANCE_NUMBER, startsnapid,endsnapid, DBID, INSTANCE_NUMBER, startsnapid,endsnapid));

匯出最新ADDM的報告（需要sys使用者） 

select dbms_advisor.get_task_report(task_name) from dba_advisor_tasks where task_id =( select max(t.task_id) from dba_advisor_tasks t, dba_advisor_log l where  t.task_id=l.task_id and t.advisor_name='ADDM' and l.status='COMPLETED' ); select task_id,task_name,description from dba_advisor_tasks order by 1 desc select dbms_advisor.get_task_report(task_name) from dba_advisor_tasks where task_id =XX 

 

查詢某個SQL的執行計劃 

select * from table(dbms_xplan.display_cursor('sql_id',0,' advanced '));

上面的0表示v$sql.child_number為0，如果一個sql_id在v$sql中有多行說明有多個child_number，要看哪兒child_number的執行計劃，就寫哪個的值，比如要看child_number為2的執行計劃，就把上面sql的0改為2 。

 

官方檔案對display_cursor這個函式的說明裡面沒有advanced這個引數值，只有BASIC、TYPICAL、ALL這幾個，不過實踐中發現advanced這個引數值顯示的內容比這幾個引數值顯示的都多。

 

select * from table(xplan.display_cursor('v$sql.sql_id',0,'advanced'));

建立xplan包，再執行 

SQL> CREATE PUBLIC SYNONYM XPLAN FOR SYS.XPLAN; SQL> grant execute on sys.xplan to public;

查詢Rman的配置資訊 

SELECT NAME,VALUE FROM V$RMAN_CONFIGURATION;

查詢Rman備份集詳細資訊（未過期的，過期並已刪除的查不到） 

SELECT B.RECID BackupSet_ID,        A.SET_STAMP,         DECODE (B.INCREMENTAL_LEVEL,                 '', DECODE (BACKUP_TYPE, 'L', 'Archivelog', 'Full'),                 1, 'Incr-1級',                 0, 'Incr-0級',                 B.INCREMENTAL_LEVEL)            "Type LV",         B.CONTROLFILE_INCLUDED "包含CTL",         DECODE (A.STATUS,                 'A', 'AVAILABLE',                 'D', 'DELETED',                 'X', 'EXPIRED',                 'ERROR')            "STATUS",         A.DEVICE_TYPE "Device Type",         A.START_TIME "Start Time",         A.COMPLETION_TIME "Completion Time",         A.ELAPSED_SECONDS "Elapsed Seconds",         A.BYTES/1024/1024/1024 "Size(G)",         A.COMPRESSED,         A.TAG "Tag",         A.HANDLE "Path"    FROM GV$BACKUP_PIECE A, GV$BACKUP_SET B   WHERE A.SET_STAMP = B.SET_STAMP AND A.DELETED = 'NO' ORDER BY A.COMPLETION_TIME DESC; 

 

查詢Rman備份進度 

SELECT SID, SERIAL#, opname,ROUND(SOFAR/TOTALWORK*100)||'%' "%_COMPLETE", TRUNC(elapsed_seconds/60) || ':' || MOD(elapsed_seconds,60) elapsed, TRUNC(time_remaining/60) || ':' || MOD(time_remaining,60) remaining, CONTEXT,target,SOFAR, TOTALWORK FROM V$SESSION_LONGOPS WHERE OPNAME LIKE 'RMAN%' AND OPNAME NOT LIKE '%aggregate%' AND TOTALWORK != 0 AND SOFAR <> TOTALWORK;

查詢執行過全表掃描的sql陳述句的SQL_ID和sql_fulltext 

select s.sid,s.serial#,s.inst_id,s.sql_id,s.username,s.target,s.ELAPSED_SECONDS,s.START_TIME,s.LAST_UPDATE_TIME,v.sql_fulltext   from gv$session_longops s,gv$sql v where s.OPNAME = 'Table Scan'    and s.SQL_PLAN_OPERATION = 'TABLE ACCESS'    and s.SQL_PLAN_OPTIONS = 'FULL'    and s.sql_id=v.sql_id    order by s.LAST_UPDATE_TIME desc

查詢死事務需要多長的回滾時間 

X$KTUXE：[K]ernel [T]ransaction [U]ndo Transa[x]tion [E]ntry (table)

X$KTUXE表的一個重要功能是，可以獲得無法透過v$transaction來觀察的死事務資訊，當一個資料庫發生異常中斷，或者進行延遲事務恢復時，資料庫啟動後，無法透過V$TRANSACTION來觀察事務資訊，但是X$KTUXE可以幫助我們獲得這些資訊。該表中的KTUXECFL代表了事務的Flag標記，透過這個標記可以找到那些Dead事務：

SQL> select distinct KTUXECFL,count(*) from x$ktuxe group by KTUXECFL;     KTUXECFL                  COUNT(*)     ------------------------ ----------     DEAD                              1 NONE                          2393

KTUXESIZ用來記錄事務使用的回滾段塊數，可以透過觀察這個欄位來評估恢復進度,例如如下事務回滾經過測算需要大約3小時：

SQL> select ADDR,KTUXEUSN,KTUXESLT,KTUXESQN,KTUXESIZ from x$ktuxe where  KTUXECFL ='DEAD';     ADDR              KTUXEUSN  KTUXESLT  KTUXESQN  KTUXESIZ     ---------------- ---------- ---------- ---------- ---------- FFFFFFFF7D07B91C        10        39    2567412    1086075 
SQL> select ADDR,KTUXEUSN,KTUXESLT,KTUXESQN,KTUXESIZ from x$ktuxe where  KTUXECFL ='DEAD';     ADDR              KTUXEUSN  KTUXESLT  KTUXESQN  KTUXESIZ     ---------------- ---------- ---------- ---------- ----------     FFFFFFFF7D07B91C        10        39    2567412    1086067 
SQL> declare    l_start number;    l_end    number;    begin     select ktuxesiz into l_start from x$ktuxe where  KTUXEUSN=10 and KTUXESLT=39;     dbms_lock.sleep(60);     select ktuxesiz into l_end from x$ktuxe where  KTUXEUSN=10 and KTUXESLT=39;     dbms_output.put_line('time_H:'|| round(l_end/(l_start -l_end)/60,2));   end;   / 
time_H:3

把XXX使用者下麵的某些YYY表賦權給user,XXX\YYY要大寫 

set serveroutput on --XXX要大寫 declare tablename varchar2(200);         begin     for x IN (SELECT * FROM dba_tables where owner='XXX' and table_name like '%YYY%') loop       tablename:=x.table_name;     dbms_output.put_line('GRANT SELECT ON XXX.'||tablename||' to user');     EXECUTE IMMEDIATE 'GRANT SELECT ON XXX.'||tablename||' TO user';      end loop; end; 

 

Oracle查出一個使用者具有的所有系統許可權和物件許可權 

系統許可權（和使用者自己查詢select * from session_privs的結果一致） 

SELECT * FROM DBA_SYS_PRIVS WHERE GRANTEE = '使用者名稱' UNION ALL SELECT * FROM DBA_SYS_PRIVS WHERE GRANTEE IN (SELECT GRANTED_ROLE FROM DBA_ROLE_PRIVS WHERE GRANTEE = '使用者名稱');

 
物件許可權（和使用者自己查詢select * FROM TABLE_PRIVILEGES where GRANTEE=’當前使用者’的結果一致） 

SELECT * FROM DBA_TAB_PRIVS WHERE GRANTEE = '使用者名稱' UNION ALL SELECT * FROM DBA_TAB_PRIVS WHERE GRANTEE IN (SELECT GRANTED_ROLE FROM DBA_ROLE_PRIVS WHERE GRANTEE = '使用者名稱');

 
查詢某個使用者擁有的角色 

select * from dba_role_privs where GRANTEE='使用者名稱';

查詢擁有DBA角色許可權的使用者 

select * from dba_role_privs where GRANTED_ROLE='DBA';

查詢某個角色擁有的系統許可權 

select * from ROLE_SYS_PRIVS where role='角色名'

清除某個SQL的執行計劃 

Exec DBMS_SHARED_POOL.PURGE('v$sqlarea.ADDRESS,v$sqlarea.HASH_VALUE','c')

查詢密碼是否有過期限制，預設是180天，一般修改為unlimited 

select * from dba_profiles where profile='DEFAULT' and RESOURCE_NAME like 'PASSWORD%'; ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED

查詢和修改隱含引數（必須在sysdba許可權下操作） 

select a.ksppinm name, b.ksppstvl value, a.ksppdesc description   from x$ksppi a, x$ksppcv b  where a.indx = b.indx and a.ksppinm like '%_small_table_threshold%' alter system set "_small_table_threshold"=value scope=both sid='*';

不加sid則說明在預設在RAC的所有實體中修改 
需要註意的是一定要加上雙引號, 另外引號內不能有空格, 只能包含引數的名字 

評估PGA該設定多少 

select PGA_TARGET_FOR_ESTIMATE from (select  * from V$PGA_TARGET_ADVICE where ESTD_OVERALLOC_COUNT=0 order by 1) where rownum=1; 

 

評估SGA該設定多少 

select SGA_SIZE from (select * from V$SGA_TARGET_ADVICE where ESTD_DB_TIME_FACTOR=1 order by 1) where rownum=1;

檢視shared pool還剩多少 

select * from v$sgastat where name='free memory' and pool='shared pool';

統計所有表的容量大小(含分割槽欄位、LOB欄位) 
一般先執行select distinct SEGMENT_TYPE  from dba_segments where owner<>’SYS’ and tablespace_name<>’SYSAUX’檢視到所有的segment_type 

如下SQL就足夠了 

SELECT    owner,table_name, TRUNC(sum(bytes)/1024/1024) Meg FROM (SELECT segment_name table_name, owner, bytes  FROM dba_segments  WHERE segment_type = 'TABLE'  UNION ALL SELECT s.segment_name table_name, pt.owner, s.bytes  FROM dba_segments s, dba_part_tables pt  WHERE s.segment_name = pt.table_name  AND   s.owner = pt.owner  AND   s.segment_type = 'TABLE PARTITION'  UNION ALL  SELECT i.table_name, i.owner, s.bytes  FROM dba_indexes i, dba_segments s  WHERE s.segment_name = i.index_name  AND   s.owner = i.owner  AND   s.segment_type = 'INDEX'  UNION ALL  SELECT pi.table_name, pi.owner, s.bytes  FROM dba_part_indexes pi, dba_segments s  WHERE s.segment_name = pi.index_name  AND   s.owner = pi.owner  AND   s.segment_type = 'INDEX PARTITION'  UNION ALL  SELECT l.table_name, l.owner, s.bytes  FROM dba_lobs l, dba_segments s  WHERE s.segment_name = l.segment_name  AND   s.owner = l.owner  AND   s.segment_type = 'LOBSEGMENT'  UNION ALL  SELECT l.table_name, l.owner, s.bytes  FROM dba_lobs l, dba_segments s  WHERE s.segment_name = l.index_name  AND   s.owner = l.owner  AND   s.segment_type = 'LOBINDEX'  union all  SELECT l.table_name, l.owner, s.bytes  FROM dba_lobs l, dba_segments s  WHERE s.segment_name = l.segment_name  AND   s.owner = l.owner  AND   s.segment_type = 'LOB PARTITION' ) GROUP BY  owner,table_name HAVING SUM(bytes)/1024/1024 > 10   ORDER BY SUM(bytes) desc

 

檢視當前會話的SID 

select * from V$MYSTAT where rownum<2

查詢某個SID的某個統計資訊，比如consistent gets一致性讀 

select A.SID,A.STATISTIC#,A.VALUE SID_VALUE,B.NAME,B.VALUE ALL_SID_VALUE from V$SESSTAT A ,V$SYSSTAT B where A.STATISTIC#=B.STATISTIC# and A.SID=1187 and B.NAME='consistent gets' V$SYSSTAT統計整個DB的統計資訊，V$SYSSTAT已經取代了V$STATNAME，並且多了VALUE這一列 V$SESSTAT統計每個使用者的統計資訊

 

查詢某個SID的某個等待事件的資訊，比如log file sync 

select A.SID,A.EVENT,C.NAME,C.PARAMETER1,C.PARAMETER2,C.PARAMETER3, A.TIME_WAITED SID_TIMEWAITED,B.TIME_WAITED ALL_SID_TIMEWAITED,A.TOTAL_WAITS SID_TOTALWAITS,B.TOTAL_WAITS ALL_SID_TOTALWAITS from V$SESSION_EVENT A ,V$SYSTEM_EVENT B,V$EVENT_NAME C where A.EVENT=B.EVENT and A.EVENT=C.NAME and A.SID=1 and C.NAME='log file sync'  V$SESSION_EVENT描述每個使用者的等待事件資訊 V$SYSTEM_EVENT描述整個DB等待事件資訊 V$EVENT_NAME描述等待事件信本身的資訊(比如V$ACTIVE_SESSION_HISTORY的P1TEXT、P2TEXT、P2TEXT匹配V$EVENT_NAME的PARAMETER1、PARAMETER2、PARAMETER3)

RAC跨節點殺會話 

alter system kill session 'SID,serial#,@1'  --殺掉1節點的行程 alter system kill session 'SID,serial#,@2'  --殺掉2節點的行程

Truncate 分割槽的SQL 

ALTER TABLE table_name TRUNCATE PARTITION p1 DROP STORAGE UPDATE GLOBAL INDEXES;

Drop分割槽的SQL 

ALTER TABLE table_name DROP PARTITION p1 UPDATE GLOBAL INDEXES;

DATAGUARD主備延遲多少時間的查詢方法 

備 庫sqlplus>select value from v$dataguard_stats where name='apply lag' 或 備庫sqlplus>select ceil((sysdate-next_time)*24*60) "M" from v$archived_log where applied='YES' AND SEQUENCE#=(SELECT MAX(SEQUENCE#)  FROM V$ARCHIVED_LOG WHERE applied='YES');

檢視某個包或儲存過程是否正在被呼叫,如果如下有結果，則此時不能編譯，否則會鎖住 

select * from V$DB_OBJECT_CACHE where pin>0 and name=upper('XX')

 

查詢資料庫打補丁的記錄

select * from dba_registry_history;

 

查詢某表的索引欄位的distinct行數和CLUSTERING_FACTOR資訊

select a.table_name,a.index_name,b.COLUMN_NAME,a.blevel,a.distinct_keys,A.CLUSTERING_FACTOR,A.NUM_ROWS,trunc((a.distinct_keys/A.NUM_ROWS),2)*100||'%' "distinct%",trunc((a.CLUSTERING_FACTOR/A.NUM_ROWS),2)*100||'%' "CLUSTERING_FACTOR%" from DBA_IND_STATISTICS a,DBA_IND_COLUMNS b where a.table_name='XX' and a.INDEX_NAME=b.index_name order by 5 desc

 

查詢某表的所有欄位的distinct行數

select a.table_name,b.num_rows,a.column_name,a.data_type,a.data_length,a.num_distinct,trunc((a.num_distinct/b.num_rows),2)*100||'%' from dba_TAB_COLS a,dba_tables b where a.table_name='XX' and a.table_name=b.table_name order by 6 desc

 

查詢5G以上空閑空間可以進行收縮的資料檔案

 

select 'alter database datafile ''' || a.file_name || ''' resize ' ||round(a.filesize - (a.filesize - c.hwmsize) * 0.8) || 'M;',a.filesize || 'M' as "資料檔案的總大小",c.hwmsize || 'M' as "資料檔案的實用大小"from (select file_id, file_name, round(bytes / 1024 / 1024) as filesizefrom dba_data_files) a,(select file_id, round(max(block_id) * 8 / 1024) as HWMsizefrom dba_extents group by file_id) cwhere a.file_id = c.file_idand a.filesize - c.hwmsize > 5000;

 

原文：http://blog.itpub.net/30126024/viewspace-2057474/

    已同步到看一看
    贊(0)
    標籤：

    相關推薦

    熱門標籤

    iOS (11238)微軟 (4955)Linux (4274)安全 (4180)Python (4161)效能 (3165)運維 (2774)最佳化 (2419).NET (2262)Google (2136)機器學習 (1795)併發 (1613)分散式 (1559)叢集 (1240)SQL (1174)Mysql (1060)區塊鏈 (1017)Docker (977)微服務 (922)面試 (919)Apache (743)NLP (725)Redis (719)Android (668)Git (640)架構師 (632)Nginx (630)Facebook (599)JVM (595)爬蟲 (476)

    熱門文章

    分享創造快樂