40個DBA日常維護的SQL腳本

json_huang 2016-12-02

展開全文

1、查詢碎片程度高的表

條件為什么block>100，因為一些很小的表，只有幾行數(shù)據(jù)實際大小很小，但是block一次性分配就是5個（11g開始默認一次性分配1M的block大小了，見create table storged的NEXT參數(shù)），5個block相對于幾行小表數(shù)據(jù)來說就相差太大了。

算法中/0.9是因為塊的pfree一般為10%，所以一個塊最多只用了90%，而且一行數(shù)據(jù)大于8KB時容易產(chǎn)生行鏈接，把一行分片存儲，一樣的一個塊連90%都用不滿、

AVG_ROW_LEN還是比較準的，比如個人實驗情況一表6個字段，一個number，其他5個都是char(100)但是實際數(shù)據(jù)都是’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)<>

order by (NUM_ROWS*AVG_ROW_LEN/1024/1024/0.9)/(BLOCKS*8192/1024/1024) desc

2、查詢索引碎片的比例

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;

3、集群因子clustering_factor高的表

集群因子越接近塊數(shù)越好，接近行數(shù)則說明索引列的列值相等的行分布極度散列，可能不走索引掃描而走全表掃描

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||'%' '集群因子接近行數(shù)'

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

4、根據(jù)sid查spid或根據(jù)spid查sid

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.sid=XX or p.spid=YY

5、根據(jù)sid查看具體的sql語句

select username,sql_text,machine,osuser 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;

6、根據(jù)spid查詢具體的sql語句

select ss.SID, pr.SPID, ss.action, sa.SQL_FULLTEXT, ss.TERMINAL, ss.PROGRAM, ss.SERIAL#, ss.USERNAME, ss.STATUS, ss.OSUSER, ss.last_call_et

from v$process pr, v$session ss, v$sqlarea sa

where ss.status='ACTIVE' and ss.username is not null and pr.ADDR = ss.PADDR

and ss.SQL_ADDRESS = sa.ADDRESS and ss.SQL_HASH_VALUE = sa.HASH_VALUE

and pr.spid = XX

7、查看歷史session_id的SQL來自哪個IP

(當然這是個誤解，都是歷史的了，怎么可能還查到spid，其實查看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中出現(xiàn)如下

*** 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服務器上執(zhí)行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

出現(xiàn)兩個，說明來自220，連接了228數(shù)據(jù)庫服務器，但是又通過228服務器的dblink去連接了16服務器

8、查詢DML死鎖會話sid，及引起死鎖的堵塞者會話blocking_session

select sid, blocking_session, LOGON_TIME,sql_id,status,event,seconds_in_wait,state, BLOCKING_SESSION_STATUS from v$session where event like 'enq%' and state='WAITING' and BLOCKING_SESSION_STATUS='VALID'

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

可以在v$session.LOGON_TIME上看到引起死鎖的堵塞者會話比等待者要早

如果遇到RAC環(huán)境，一定要用gv$來查，并且執(zhí)行alter system kill session 'sid,serial#'要到RAC對應的實例上去執(zhí)行

或如下也可以

select

(select username from v$session where sid=a.sid) blocker,

a.sid,

a.id1,

a.id2,

' is blocking ' 'IS BLOCKING',

(select username from v$session where sid=b.sid) blockee,

b.sid

from v$lock a, v$lock b

where a.block = 1

and b.request > 0

and a.id1 = b.id1

and a.id2 = b.id2;

9、查詢DDL鎖的sql

SELECT sid, event, p1raw, seconds_in_wait, wait_time

FROM sys.v_$session_wait

WHERE event like 'library cache %'

p1raw結果為'0000000453992440'

SELECT s.sid, kglpnmod 'Mode', kglpnreq 'Req', s.LOGON_TIME

FROM x$kglpn p, v$session s

WHERE p.kglpnuse=s.saddr

AND kglpnhdl='0000000453992440';

結果為671 0 3 2011-11-1 12:00:00

525 2 0 2011-11-4 12:00:00

10、查詢鎖住的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

11、查詢當前正在執(zhí)行的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

12、查詢正在執(zhí)行的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

13、查詢正在執(zhí)行的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

14、查詢一個會話session、process平均消耗多少內存，查看下面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;

15、TOP 10 執(zhí)行次數(shù)排序

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

16、TOP 10 物理讀排序

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

（不要使用DISK_READS/ EXECUTIONS來排序，因為任何一條語句不管執(zhí)行幾次都會耗邏輯讀和cpu，可能不會耗物理讀（遇到LRU還會耗物理讀，LRU規(guī)則是執(zhí)行最不頻繁的且最后一次執(zhí)行時間距離現(xiàn)在最久遠的就會被交互出buffer cache），是因為buffer cache存放的是數(shù)據(jù)塊，去數(shù)據(jù)塊里找行一定會消耗cpu和邏輯讀的。Shared pool執(zhí)行存放sql的解析結果，sql執(zhí)行的時候只是去share pool中找hash value，如果有匹配的就是軟解析。所以物理讀邏輯讀是在buffer cache中，軟解析硬解析是在shared pool）

17、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 <>

（不要使用BUFFER_GETS/ EXECUTIONS來排序，原因同16）

18、TOP 10 CPU排序

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

（不要使用CPU_TIME/ EXECUTIONS來排序，原因同16）

19、查詢等待事件

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

20、查詢當前正在消耗temp空間的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

21、查詢需要使用綁定變量的sql,10G以后推薦第二種

（任何一條執(zhí)行過的語句不管執(zhí)行了幾次在V$SQL中都只有一條記錄，V$SQL中會記錄執(zhí)行了幾次。兩條一模一樣的語句但是在不同的schema下執(zhí)行的兩種結果，如select * from t1.test在sye、system下執(zhí)行則V$SQL只有一條記錄(誰先執(zhí)行則PARSING_SCHEMA_NAME顯示誰)。如在sys和system都執(zhí)行select * from test則V$SQL中有兩條記錄，兩條記錄的CHILD_NUMBER和PARSING_SCHEMA_NAME不一樣。同一個用戶下執(zhí)行一樣的語句如果大小寫不一樣或加了hint的話則會出現(xiàn)多個V$SQL記錄，說明V$SQL對應的sql語句必須一模一樣，如果alter system flush shared_pool（主站慎用）后再執(zhí)行一樣的語句，發(fā)現(xiàn)語句在V$SQL中的SQL_ID和HASH_VALUE與之前的一樣，說明SQL_ID和HASH_VALUE應該是oracle自己的一套算法來的，只是根據(jù)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<>

第二種

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)

22、查看數(shù)據(jù)文件可用百分比

select b.file_id,b.tablespace_name,b.file_name,b.AUTOEXTENSIBLE,

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

order by b.tablespace_name;

23、查看數(shù)據(jù)文件可用百分比

select b.file_id,b.tablespace_name,b.file_name,b.AUTOEXTENSIBLE,

ROUND(b.MAXBYTES/1024/1024/1024,2) ||'G' '文件最大可用總容量',

ROUND((b.bytes-sum(nvl(a.bytes,0)))/1024/1024/1024,2)||'G' '文件已用容量',

ROUND(((b.MAXBYTES/1024/1024/1024)-((b.bytes-sum(nvl(a.bytes,0)))/1024/1024/1024))/(b.MAXBYTES/1024/1024/1024),2)*100||'%' '文件可用百分比'

from dba_free_space a,dba_data_files b

where a.file_id=b.file_id and b.file_id>4

group by b.tablespace_name,b.file_name,b.file_id,b.bytes,b.AUTOEXTENSIBLE,b.MAXBYTES

order by b.tablespace_name;

24、查看表空間可用百分比

select b.tablespace_name,a.total,b.free,round((b.free/a.total)*100) '% Free' from

(select tablespace_name, sum(bytes/(1024*1024)) total 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';

25、查看臨時表空間使用率

SELECT temp_used.tablespace_name,total,used,

total - used as 'Free',

round(nvl(total-used, 0) * 100/total,3) '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(bytes)/1024/1024 total

FROM dba_temp_files

GROUP BY tablespace_name) temp_total

WHERE temp_used.tablespace_name = temp_total.tablespace_name

26、查詢undo表空間使用情況

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

27、查看ASM磁盤組使用率

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

28、統(tǒng)計每個用戶使用表空間率

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 '用戶','表空間名'

29、查看閃回區(qū)\快速恢復區(qū)空間使用率

select sum(percent_space_used)||'%' '已使用空間比例' from V$RECOVERY_AREA_USAGE

30、查看僵死進程，分兩種

alter system kill session一執(zhí)行則session即標記為KILLED，但是如果會話產(chǎn)生的數(shù)據(jù)量大則這個kill可能會比較久，在這個過程中session標記為KILLED但是這個會話還在V$session中，則V$session.paddr還在，所以可以匹配到V$process.addr，所以process進程還在；當kill過程執(zhí)行完畢，則這個會話即不在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')

再根據(jù)上述結果中的SPID通過如下命令可以查看到process的啟動時間

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

31、查看行遷移或行鏈接的表

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.

32、數(shù)據(jù)緩沖區(qū)命中率

LECT 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';

33、共享池命中率

以下兩者應該都可以，看個人怎么理解

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

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

34、查詢歸檔日志切換頻率

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;

35、查詢lgwr進程寫日志時每執(zhí)行一次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'

36、查詢沒有索引的表

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)

37、查詢7天的db time

TH sysstat AS

(select sn.begin_interval_time begin_interval_time,

sn.end_interval_time end_interval_time,

ss.stat_name stat_name,

ss.value e_value,

lag(ss.value, 1) over(order by ss.snap_id) b_value

from dba_hist_sysstat ss, dba_hist_snapshot sn

where trunc(sn.begin_interval_time) >= sysdate - 7

and ss.snap_id = sn.snap_id

and ss.dbid = sn.dbid

and ss.instance_number = sn.instance_number

and ss.dbid = (select dbid from v$database)

and ss.instance_number = (select instance_number from v$instance)

and ss.stat_name = 'DB time')

select to_char(BEGIN_INTERVAL_TIME, 'mm-dd hh24:mi') ||

to_char(END_INTERVAL_TIME, ' hh24:mi') date_time,

stat_name,

round((e_value - nvl(b_value, 0)) /

(extract(day from(end_interval_time - begin_interval_time)) * 24 * 60 * 60 +

extract(hour from(end_interval_time - begin_interval_time)) * 60 * 60 +

extract(minute from(end_interval_time - begin_interval_time)) * 60 +

extract(second from(end_interval_time - begin_interval_time))),

0) per_sec

from sysstat

where (e_value - nvl(b_value, 0)) > 0

and nvl(b_value, 0) > 0

38、查詢產(chǎn)生熱塊較多的對象

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

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)="">

WHERE e.relative_fno = b.dbarfil

AND e.block_id <=>

AND e.block_id + e.blocks > b.dbablk;

39、導出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));

40、查詢某個SQL的執(zhí)行計劃

select a.hash_value,a.* from v$sql a where sql_id='0n4qfzbqfsjm3'

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

含順序的

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

不過要先創(chuàng)建xplan包，再執(zhí)行

SQL> CREATE PUBLIC SYNONYM XPLAN FOR SYS.XPLAN;

SQL> grant execute on sys.xplan to public;

這一次，只為你而來！