ORACLE常用性能監控SQL【一】
文章目錄
系列
死鎖后的解決辦法
生成Kill Session語句
查看導致死鎖的 SQL
查看誰鎖了誰
ORA-00054 資源正忙，要求指定 NOWAIT
查詢綁定變量使用的實際值
監控事例的等待
回滾段的爭用情況
查看回滾段名稱及大小
查看控制文件
查看日志文件
查看前臺正在發出的SQL語句
數據表占用空間大小情況
查看表空間碎片大小
查看表空間占用磁盤情況
查看表的大小，倒序排列
查看表空間物理文件的名稱及大小
查看oracle 表空間使用率
查看Temp 表空間實際使用磁盤大小
查看session使用回滾段
查看當前臨時表空間使用大小與正在占用臨時表空間的sql語句
Temp表空間上進程的查詢
查看SGA區剩余可用內存
監控表空間I/O比例
監控SGA命中率
監控 SGA 中字典緩沖區的命中率
監控 SGA **享緩存區的命中率，應該小于1%
監控 SGA 中重做日志緩存區的命中率，應該小于1%
監控內存和硬盤的排序比率，最好使它小于 .10
監控字典緩沖區
非系統用戶建在SYSTEM表空間中的表
性能最差的SQL
讀磁盤數超100次的sql
查找消耗資源比較的sql語句
最頻繁執行的sql
查詢使用CPU多的用戶session
當前每個會話使用的對象數
查看數據庫庫對象
查看數據庫的版本
查看數據庫的創建日期和歸檔方式
檢查角色和權限設置
根據用戶名進行授權的對象級特權
根據被授權人進行授權的對象級特權
根據用戶名進行授予的系統級特權
根據被授權人進行授予的系統級特權
根據用戶名授予的角色
根據被授權人授予的角色
用戶名及已被授予的相應權限
查詢用戶名及相應的配置文件、默認的表空間和臨時表空間
等待事件V$視圖
馬上該誰等待--查詢`V$SESSION_WAIT / V$SESSION`
馬上該誰等待；SPECIFIC Waits--查詢V$SESSION_WAIT
誰在等待 - 最后10 個等待數--查詢V$SESSION_WAIT_HISTORY
查找P1, P2, P3代表什么--查詢 V$EVENT_NAME
會話開始后的所有等待數--查詢 V$SESSION_EVENT
類的所有會話等待數--查詢V$SESSION_WAIT_CLASS
系統啟動后的所有等待數--查詢V$SYSTEM_EVENT
類的系統等待數--查詢V$SYSTEM_WAIT_CLASS
類的系統等待數--查詢V$ACTIVE_SESSION_HISTORY
自動工作量倉庫(AWR) 的基本信息
獲取生成的trace文件
系列
ORACLE常用性能監控SQL【一】
ORACLE常用性能監控SQL【二】
Oracle-動態性能視圖解讀
死鎖后的解決辦法
如果死鎖不能自動釋放，就需要我們手工的 kill session
生成Kill Session語句
查看有無死鎖對象，如有 kill session
SELECT 'alter system kill session ''' || sid || ',' || serial# || ''';' "Deadlock" FROM v$session WHERE sid IN (SELECT sid FROM v$lock WHERE block = 1);
1
2
3
4
5
如果有，會返回類似與如下的信息：
alter system kill session '761,876'; .....
1
2
kill session：
執行 alter system kill session ‘761,876’(sid 為 761);
注意： 應當注意對于 sid 在 100 以下的應當謹慎，可能該進程對應某個application，如對應某個事務,可以 kill
查看導致死鎖的 SQL
SELECT s.sid, q.sql_text FROM v$sqltext q, v$session s WHERE q.address = s.sql_address AND s.sid = &sid -- 這個&sid 是第一步查詢出來的 ORDER BY piece;
1
2
3
4
執行后，輸入對應的sid即可查看對應的sql.
查看誰鎖了誰
SELECT s1.username || '@' || s1.machine || ' ( SID=' || s1.sid || ' ) is blocking ' || s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status FROM v$lock l1, v$session s1, v$lock l2, v$session s2 WHERE s1.sid = l1.sid AND s2.sid = l2.sid AND l1.BLOCK = 1 AND l2.request > 0 AND l1.id1 = l2.id1 AND l2.id2 = l2.id2;
1
2
3
4
5
6
7
8
9
10
或者
推薦這個，因為使用的是 v$locked_object
SELECT LPAD(' ', DECODE(l.xidusn, 0, 3, 0)) || l.oracle_username User_name, o.owner, o.object_name, o.object_type, s.sid, s.serial# FROM v$locked_object l, dba_objects o, v$session s WHERE l.object_id = o.object_id AND l.session_id = s.sid ORDER BY o.object_id, xidusn DESC;
1
2
3
4
5
6
7
8
9
10
V$LOCKED_OBJECT只能報發生等待的表級鎖,不能報發生等待的行級鎖。
ORA-00054 資源正忙，要求指定 NOWAIT
演示：
select * from emp for update ;--通過for update 獲取一個排它鎖
1
SQL>select object_name as 對象名稱,s.sid,s.serial#,p.spid as 系統進程號 from v$locked_object l , dba_objects o , v$session s , v$process p where l.object_id=o.object_id and l.session_id=s.sid and s.paddr=p.addr; 對象名稱 SID SERIAL# 系統進程號 -------------------------------------------------------------------------------- ---------- ---------- ------------------------ EMP 1411 8865 32720
1
2
3
4
5
6
7
8
在另外一個會話中執行
ALTER SYSTEM KILL SESSION '1411,8865';
1
查詢綁定變量使用的實際值
1, SQL還在shared pool中，沒有被aged out 替換SQL ID 值即可
select sql_id, name, datatype_string, last_captured, value_string from v$sql_bind_capture where sql_id = '7nqt558g5gmyr' order by LAST_CAPTURED, POSITION;
1
2
3
2.請自行替換sql_id，此時是從awr中查詢(sql 被 aged out 出 shared pool)
select instance_number, sql_id, name, datatype_string, last_captured, value_string from dba_hist_sqlbind where sql_id = 'fahv8x6ngrb50' order by LAST_CAPTURED, POSITION;
1
2
3
4
5
6
7
8
9
監控事例的等待
select event,sum(decode(wait_Time,0,0,1)) "Prev", sum(decode(wait_Time,0,1,0)) "Curr",count(*) "Tot" from v$session_Wait group by event order by 4 ;
1
2
3
4
5
回滾段的爭用情況
select name, waits, gets, waits / gets "Ratio" from v$rollstat a, v$rollname b where a.usn = b.usn;
1
2
3
4
查看回滾段名稱及大小
SELECT segment_name, tablespace_name, r.status, (initial_extent / 1024) initialextent, (next_extent / 1024) nextextent, max_extents, v.curext curextent FROM dba_rollback_segs r, v$rollstat v WHERE r.segment_id = v.usn(+) ORDER BY segment_name;
1
2
3
4
5
6
7
8
9
10
查看控制文件
SELECT NAME FROM v$controlfile;
1
查看日志文件
SELECT MEMBER FROM v$logfile;
1
2
查看前臺正在發出的SQL語句
select user_name,sql_text from v$open_cursor where sid in (select sid from (select sid,serial#,username,program from v$session where status='ACTIVE'));
1
2
3
4
5
數據表占用空間大小情況
select segment_name, tablespace_name, bytes, blocks from user_segments where segment_type = 'TABLE' ORDER BY bytes DESC, blocks DESC;
1
2
3
4
5
查看表空間碎片大小
select tablespace_name,round(sqrt(max(blocks)/sum(blocks))* (100/sqrt(sqrt(count(blocks)))),2) FSFI from dba_free_space group by tablespace_name order by 1;
1
2
3
4
查看表空間占用磁盤情況
select b.file_id 文件ID號, b.tablespace_name 表空間名, b.bytes 字節數, (b.bytes-sum(nvl(a.bytes,0))) 已使用, sum(nvl(a.bytes,0)) 剩余空間, sum(nvl(a.bytes,0))/(b.bytes)*100 剩余百分比 from dba_free_space a,dba_data_files b where a.file_id=b.file_id group by b.tablespace_name,b.file_id,b.bytes order by b.file_id;
1
2
3
4
5
6
7
8
9
10
11
查看表的大小，倒序排列
每張表都是作為“段”來存儲的，可以通過user_segments視圖查看其相應信息。
段（segments）的定義：如果創建一個堆組織表，則該表就是一個段
SELECT SEGMENT_NAME, SUM(BYTES) / 1024 / 1024 MBYTESE FROM USER_SEGMENTS WHERE SEGMENT_TYPE = 'TABLE' GROUP BY SEGMENT_NAME order by MBYTESE desc;
1
2
3
4
5
查看表空間物理文件的名稱及大小
SELECT tablespace_name, file_id, file_name, round(bytes / (1024 * 1024), 0) total_space FROM dba_data_files ORDER BY tablespace_name;
1
2
3
4
5
6
查看Oracle 表空間使用率
SELECT D.TABLESPACE_NAME, SPACE || 'M' "SUM_SPACE(M)", SPACE - NVL (FREE_SPACE, 0) || 'M' "USED_SPACE(M)", ROUND ( (1 - NVL (FREE_SPACE, 0) / SPACE) * 100, 2) || '%' "USED_RATE(%)", FREE_SPACE || 'M' "FREE_SPACE(M)" FROM ( SELECT TABLESPACE_NAME, ROUND (SUM (BYTES) / (1024 * 1024), 2) SPACE, SUM (BLOCKS) BLOCKS FROM DBA_DATA_FILES GROUP BY TABLESPACE_NAME) D, ( SELECT TABLESPACE_NAME, ROUND (SUM (BYTES) / (1024 * 1024), 2) FREE_SPACE FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME) F WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+) UNION ALL --如果有臨時表空間 SELECT D.TABLESPACE_NAME, SPACE || 'M' "SUM_SPACE(M)", USED_SPACE || 'M' "USED_SPACE(M)", ROUND (NVL (USED_SPACE, 0) / SPACE * 100, 2) || '%' "USED_RATE(%)", NVL (FREE_SPACE, 0) || 'M' "FREE_SPACE(M)" FROM ( SELECT TABLESPACE_NAME, ROUND (SUM (BYTES) / (1024 * 1024), 2) SPACE, SUM (BLOCKS) BLOCKS FROM DBA_TEMP_FILES GROUP BY TABLESPACE_NAME) D, ( SELECT TABLESPACE_NAME, ROUND (SUM (BYTES_USED) / (1024 * 1024), 2) USED_SPACE, ROUND (SUM (BYTES_FREE) / (1024 * 1024), 2) FREE_SPACE FROM V$TEMP_SPACE_HEADER GROUP BY TABLESPACE_NAME) F WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+) ORDER BY 1;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
SELECT a.tablespace_name "表空間名", total "表空間大小", free "表空間剩余大小", (total - free) "表空間使用大小", total / (1024 * 1024 * 1024) "表空間大小(G)", free / (1024 * 1024 * 1024) "表空間剩余大小(G)", (total - free) / (1024 * 1024 * 1024) "表空間使用大小(G)", round((total - free) / 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 WHERE a.tablespace_name = b.tablespace_name
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
查看Temp 表空間實際使用磁盤大小
Select f.tablespace_name, d.file_name "Tempfile name", round((f.bytes_free + f.bytes_used) / 1024 / 1024, 2) "total MB", round(((f.bytes_free + f.bytes_used) - nvl(p.bytes_used, 0)) / 1024 / 1024, 2) "Free MB", round(nvl(p.bytes_used, 0) / 1024 / 1024, 2) "Used MB", round((round(nvl(p.bytes_used, 0) / 1024 / 1024, 2) / round((f.bytes_free + f.bytes_used) / 1024 / 1024, 2)) * 100, 2) as "Used_Rate(%)" from SYS.V_$TEMP_SPACE_HEADER f, DBA_TEMP_FILES d, SYS.V_$TEMP_EXTENT_POOL p where f.tablespace_name(+) = d.tablespace_name and f.file_id(+) = d.file_id and p.file_id(+) = d.file_id;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
查看session使用回滾段
SELECT r.name 回滾段名, s.sid, s.serial#, s.username 用戶名, t.status, t.cr_get, t.phy_io, t.used_ublk, t.noundo, substr(s.program, 1, 78) 操作程序 FROM sys.v_$session s,sys.v_$transaction t,sys.v_$rollname r WHERE t.addr = s.taddr and t.xidusn = r.usn ORDER BY t.cr_get,t.phy_io;
1
2
3
4
5
6
7
8
9
10
11
12
13
查看當前臨時表空間使用大小與正在占用臨時表空間的sql語句
select sess.SID, segtype, blocks * 8 / 1000 "MB", sql_text from v$sort_usage sort, v$session sess, v$sql sql where sort.SESSION_ADDR = sess.SADDR and sql.ADDRESS = sess.SQL_ADDRESS order by blocks desc;
1
2
3
4
5
6
Temp表空間上進程的查詢
select a.tablespace, b.sid, b.serial#, a.blocks,c.sql_text from v$sort_usage a,v$session b,v$sqltext c where a.session_addr = b.saddr and b.sql_address = c.address order by a.tablespace,b.sid,b.serial#,c.address, c.piece;
1
2
3
4
5
查看SGA區剩余可用內存
select name, sgasize/1024/1024 "Allocated(M)", bytes/1024 "**空間(K)", round(bytes/sgasize*100, 2) "**空間百分比(%)" from (select sum(bytes) sgasize from sys.v_$sgastat) s, sys.v_$sgastat f where f.name = 'free memory';
1
2
3
4
5
6
監控表空間I/O比例
select df.tablespace_name name,df.file_name "file",f.phyrds pyr, f.phyblkrd pbr,f.phywrts pyw, f.phyblkwrt pbw from v$filestat f, dba_data_files df where f.file# = df.file_id order by df.tablespace_name;
1
2
3
4
5
監控SGA命中率
select a.value + b.value "logical_reads", c.value "phys_reads", round(100 * ((a.value+b.value)-c.value) / (a.value+b.value)) "BUFFER HIT RATIO" from v$sysstat a, v$sysstat b, v$sysstat c where a.statistic# = 38 and b.statistic# = 39 and c.statistic# = 40 ;
1
2
3
4
5
6
7
監控 SGA 中字典緩沖區的命中率
select parameter, gets,Getmisses , getmisses/(gets+getmisses)*100 "miss ratio", (1-(sum(getmisses)/ (sum(gets)+sum(getmisses))))*100 "Hit ratio" from v$rowcache where gets+getmisses <>0 group by parameter, gets, getmisses ;
1
2
3
4
5
監控 SGA **享緩存區的命中率，應該小于1%
select sum(pins) "Total Pins", sum(reloads) "Total Reloads", sum(reloads)/sum(pins) *100 libcache from v$librarycache;
1
2
3
監控 SGA 中重做日志緩存區的命中率，應該小于1%
SELECT name, gets, misses, immediate_gets, immediate_misses, Decode(gets,0,0,misses/gets*100) ratio1, Decode(immediate_gets+immediate_misses,0,0, immediate_misses/(immediate_gets+immediate_misses)*100) ratio2 FROM v$latch WHERE name IN ('redo allocation', 'redo copy');
1
2
3
4
5
監控內存和硬盤的排序比率，最好使它小于 .10
SELECT name, value FROM v$sysstat WHERE name IN ('sorts (memory)', 'sorts (disk)') ;
1
2
3
監控字典緩沖區
SELECT SUM(GETS) "DICTIONARY GETS",SUM(GETMISSES) "DICTIONARY CACHE GET MISSES" FROM V$ROWCACHE ;
1
2
非系統用戶建在SYSTEM表空間中的表
SELECT owner,table_name FROM DBA_TABLES WHERE tablespace_name in('SYSTEM','USER_DATA') AND owner NOT IN('SYSTEM','SYS','OUTLN', 'ORDSYS','MDSYS','SCOTT', 'HOSTEAC');
1
2
3
4
性能最差的SQL
SELECT * FROM ( SELECT PARSING_USER_ID EXECUTIONS,SORTS,COMMAND_TYPE,DISK_READS,sql_text FROM v$sqlarea ORDER BY disk_reads DESC) WHERE ROWNUM<100;
1
2
3
4
讀磁盤數超100次的sql
select * from sys.v_$sqlarea where disk_reads>100;
1
查找消耗資源比較的sql語句
Select se.username, se.sid, su.extents, su.blocks * to_number(rtrim(p.value)) as Space, tablespace, segtype, sql_text from v$sort_usage su, v$parameter p, v$session se, v$sql s where p.name = 'db_block_size' and su.session_addr = se.saddr and s.hash_value = su.sqlhash and s.address = su.sqladdr order by se.username, se.sid;
1
2
3
4
5
6
7
8
9
10
11
12
13
最頻繁執行的sql
select * from sys.v_$sqlarea where executions>100;
1
查詢使用CPU多的用戶session
select a.sid,spid,status,substr(a.program,1,40) prog,a.terminal,osuser,value/60/100 value from v$session a,v$process b,v$sesstat c where c.statistic#=12 and c.sid=a.sid and a.paddr=b.addr order by value desc;
1
2
3
4
5
6
當前每個會話使用的對象數
SELECT a.sid,s.terminal,s.program,count(a.sid) FROM V$ACCESS a,V$SESSION s WHERE a.owner <> 'SYS'AND s.sid = a.sid GROUP BY a.sid,s.terminal,s.program ORDER BY count(a.sid) ;
1
2
3
4
5
查看數據庫庫對象
SELECT owner, object_type, status, COUNT(*) count# FROM all_objects GROUP BY owner, object_type, status;
1
2
3
4
查看數據庫的版本
SELECT version FROM product_component_version WHERE substr(product, 1, 6) = 'Oracle';
1
2
3
查看數據庫的創建日期和歸檔方式
SELECT created, log_mode, log_mode FROM v$database;
1
檢查角色和權限設置
根據用戶名進行授權的對象級特權
select b.owner || '.' || b.table_name obj, b.privilege what_granted, b.grantable, a.username from sys.dba_users a, sys.dba_tab_privs b where a.username = b.grantee order by 1, 2, 3;
1
2
3
4
5
6
7
8
根據被授權人進行授權的對象級特權
Select owner || '.' || table_name obj, privilege what_granted, grantable, grantee from sys.dba_tab_privs where not exists (select 'x' from sys.dba_users where username = grantee) order by 1, 2, 3;
1
2
3
4
5
6
7
根據用戶名進行授予的系統級特權
select b.privilege what_granted, b.admin_option, a.username from sys.dba_users a, sys.dba_sys_privs b where a.username = b.grantee order by 1, 2;
1
2
3
4
根據被授權人進行授予的系統級特權
select privilege what_granted, admin_option, grantee from sys.dba_sys_privs where not exists (select 'x' from sys.dba_users where username = grantee) order by 1, 2;
1
2
3
4
5
6
根據用戶名授予的角色
select b.granted_role || decode(admin_option, 'YES', ' (With Admin Option)', null) what_granted, a.username from sys.dba_users a, sys.dba_role_privs b where a.username = b.grantee order by 1;
1
2
3
4
5
6
7
根據被授權人授予的角色
select granted_role || decode(admin_option, 'YES', ' (With Admin Option)', null) what_granted, grantee from sys.dba_role_privs where not exists (select 'x' from sys.dba_users where username = grantee) order by 1;
1
2
3
4
5
6
7
用戶名及已被授予的相應權限
select a.username, b.granted_role || decode(admin_option, 'YES', ' (With Admin Option)', null) what_granted from sys.dba_users a, sys.dba_role_privs b where a.username = b.grantee UNION select a.username, b.privilege || decode(admin_option, 'YES', ' (With Admin Option)', null) what_granted from sys.dba_users a, sys.dba_sys_privs b where a.username = b.grantee UNION select a.username, b.table_name || '-' || b.privilege || decode(grantable, 'YES', ' (With Grant Option)', null) what_granted from sys.dba_users a, sys.dba_tab_privs b where a.username = b.grantee order by 1;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
查詢用戶名及相應的配置文件、默認的表空間和臨時表空間
Select username, profile, default_tablespace, temporary_tablespace, created from sys.dba_users order by username;
1
2
3
等待事件V$視圖
在Oracle 10g中V$SESSION_WAIT中的所有等待事件列現在都在V$SESSION中。因此，確保查詢等待信息的 V$SESSION，因為它是一個更快的視圖。V$ACTIVE_SESSION_HISTORY (ASH)將許多重要統計數據合并為一個視圖或一個報表(ASH報表)。
馬上該誰等待–查詢V$SESSION_WAIT / V$SESSION
select event, sum(decode(wait_time, 0, 1, 0)) "Waiting Now", sum(decode(wait_time, 0, 0, 1)) "Previous Waits", count(*) "Total" from v$session_wait group by event order by count(*);
1
2
3
4
5
6
7
馬上該誰等待；SPECIFIC Waits–查詢V$SESSION_WAIT
SELECT /*+ ordered */ sid, event, owner, segment_name, segment_type, p1, p2, p3 FROM v$session_wait sw, dba_extents de WHERE de.file_id = sw.p1 AND sw.p2 between de.block_id and de.block_id + de.blocks - 1 AND (event = 'buffer busy waits' OR event = 'write complete waits') AND p1 IS NOT null ORDER BY event, sid;
1
2
3
4
5
6
7
8
9
10
11
誰在等待 - 最后10 個等待數–查詢V$SESSION_WAIT_HISTORY
SELECT /*+ ordered */ sid, event, owner, segment_name, segment_type, p1, p2, p3 FROM v$session_wait sw, dba_extents de WHERE de.file_id = sw.p1 AND sw.p2 between de.block_id and de.block_id + de.blocks - 1 AND (event = 'buffer busy waits' OR event = 'write complete waits') AND p1 IS NOT null ORDER BY event, sid;
1
2
3
4
5
6
7
8
查找P1, P2, P3代表什么–查詢 V$EVENT_NAME
select event#, name, parameter1 p1, parameter2 p2, parameter3 p3 from v$event_name where name in ('buffer busy waits', 'write complete waits');
1
2
3
會話開始后的所有等待數–查詢 V$SESSION_EVENT
select sid, event, total_waits, time_waited, event_id from v$session_event where time_waited > 0 order by time_waited;
1
2
3
4
5
類的所有會話等待數–查詢V$SESSION_WAIT_CLASS
select sid, wait_class, total_waits from v$session_wait_class;
1
系統啟動后的所有等待數–查詢V$SYSTEM_EVENT
select event, total_waits, time_waited, event_id from v$system_event where time_waited > 0 order by time_waited;
1
2
3
4
5
類的系統等待數–查詢V$SYSTEM_WAIT_CLASS
select wait_class, total_waits from v$system_wait_class order by total_waits desc;
1
2
3
4
類的系統等待數–查詢V$ACTIVE_SESSION_HISTORY
–In the query below, the highest count session is leader in non-idle wait events.
select session_id, count(1) from v$active_session_history group by session_id order by 2;
1
2
3
4
–In the query below, find the SQL for the leader in non-idle wait events.
select c.sql_id, a.sql_text from v$sql a, (select sql_id, count(1) from v$active_session_history b where sql_id is not null group by sql_id order by 2 desc) c where rownum <= 5 order by rownum;
1
2
3
4
5
6
7
8
自動工作量倉庫(AWR) 的基本信息
自動工作量倉庫(AWR)在默認情況下，倉庫用小時填充，保留期是7天。
AWR使用多少空間
SQL> Select occupant_name,occupant_desc,space_usage_kbytes from v$sysaux_occupants where occupant_name like '%AWR%'; OCCUPANT_NAME OCCUPANT_DESC SPACE_USAGE_KBYTES ----------------- ---------------------------------- ------------------ SM/AWR Server Manageability - Automatic Workload Repository 215616 SQL>
1
2
3
4
5
6
7
系統上最原始的AWR信息是什么？
SQL> select dbms_stats.get_stats_history_availability from dual; GET_STATS_HISTORY_AVAILABILITY ------------------------------------------------------------- 20-OCT-16 12.04.49.088829000 AM -04:00
1
2
3
4
5
什么是AWR信息的保留期？
SQL> select dbms_stats.get_stats_history_retention from dual; GET_STATS_HISTORY_RETENTION --------------------------- 31
1
2
3
4
5
將AWR信息的保留期更改為15天？
SQL> EXEC dbms_stats.alter_stats_history_retention(15); PL/SQL 過程已成功完成。
1
2
3
獲取生成的trace文件
開啟SQL跟蹤后，會生成一個trace文件，通過初始化參數user_dump_dest配置其所在目錄，該參數的值可以通過下面方法獲取到：
select name, value from v$parameter where name = 'user_dump_dest'
1
2
trace文件的名字是獨立于版本和平臺的，在大部分常見的平臺下，命名結構如下：
{instance name}_{process name}_{process id}.trc
1
1）instance name
初始化參數instance_name的小寫值。通過v$instance視圖的instance_name列可以得到這個值。
2）process name
產生跟蹤文件進程的名字的小寫值。對于專有服務器進程，使用ora，對于共享服務器進程，可以通過v$diapatcher或v$shared_server視圖的name列獲得。對于并行從屬進程，可以通過v$px_process視圖server_name列獲得，對于其他多數后臺進程來說，可以通過v$bgprocess視圖的name列獲得。
3）process id
操作系統層面的進程標記。這個值可以通過v$process視圖的spid列獲取。
根據這些信息，可以通過下面的方式獲取trace文件名：
select s.SID, s.SERVER, lower(case when s.SERVER in ('DEDICATED', 'SHARED') then i.INSTANCE_NAME || '_' || nvl(pp.SERVER_NAME, nvl(ss.NAME, 'ora')) || '_' || p.SPID || '.trc' else null end) as trace_file_name from v$instance i, v$session s, v$process p, v$px_process pp, v$shared_server ss where s.PADDR = p.ADDR and s.SID = pp.SID(+) and s.PADDR = ss.PADDR(+) and s.TYPE = 'USER' and s.SID = 'your sid' order by s.SID
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
將上面的’your sid’替換為你的session的sid就可以查出指定session生成的trace文件的名字，session的sid在v$session視圖中得到，或者直接查詢當前session的sid：
select userenv('sid') from dual 或者 select sid from v$mystat a where rownum=1 ;
1
2
3
將路徑（user_dump_dest）和文件名結合在一起，我們就得到了trace文件的完整路徑。
而在Oracel 11g中，查詢當前會話生成的trace文件則非常簡單：
select value from v$diag_info where name = 'Default Trace File'
1
Oracle SQL
版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。
相關文章
