database 概況資訊檢查
# 檢查 database 基本資訊
select * from v$version;
select name ,open_mode,log_mode from v$database;
select instance_number,instance_name ,status from gv$instance;
show parameter cpu_count
show parameter block_size
select group#,thread#,members,bytes/1024/1024 from gv$log;
show sga
select count(*) from v$controlfile
select count(*) from v$tempfile;
select count(*) from v$datafile;
檢視資料檔案資訊
# 檢查臨時表空間資料檔案資訊
col tablespace_name for a30
select tablespace_name , sum(bytes)/1024/1024 from dba_temp_files group by tablespace_name;
# 檢查表空間
SELECT
TABLESPACE_NAME,EXTENT_MANAGEMENT,ALLOCATION_TYPE,SEGMENT_SPACE
_MANAGEMENT FROM DBA_TABLESPACES;
# 檢查資料檔案狀态
select count(*),status from v$datafile group by status;
# 檢查表空間使用情況
select total.tablespace_name,
round(total.MB, 2) as Total_MB,
round(total.MB - free.MB, 2) as Used_MB,
round((1 - free.MB / total.MB) * 100, 2) || '%' as Used_Pct from
(select tablespace_name,sum(bytes) / 1024 / 1024 as MB from dba_free_space group by tablespace_name) free,
(select tablespace_name,sum(bytes) / 1024 / 1024 as MB from dba_data_files group by tablespace_name) total
where free.tablespace_name = total.tablespace_name order by used_pct desc;
# 查詢臨時 segment 使用情況
COL username FORMAT a10;
COL segtype FORMAT a10;
SELECT username, segtype, extents "Extents Allocated"
,blocks "Blocks Allocated"
FROM v$tempseg_usage;
# 檢視臨時表空間大小
select tablespace_name,file_name,bytes/1024/1024 "file_size(M)",autoextensible from dba_temp_files;
select status,enabled, name, bytes/1024/1024 file_size from v$tempfile;
# 檢視臨時表空間的使用情況
SELECT temp_used.tablespace_name,
total - used as "Free",
total as "Total",
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
# 檢視目前臨時表空間使用大小與正在占用臨時表空間的sql語句
SELECT
se.sid
, se.serial#
, su.segtype
, su.contents
, su.blocks * 8 / 1024 "MB"
, s.sql_id
, s.child_number
, s.sql_text
FROM v$sort_usage su
, v$session se
, v$sql s
WHERE su.session_addr = se.saddr
AND s.address = se.sql_address
ORDER BY;
# 檢視資料檔案資訊 , 若檔案較多可以根據需要字段進行排序 輸出 top 10
col datafile for a60
SELECT fs.phyrds "Reads", fs.phywrts "Writes"
,fs.avgiotim "Average I/O Time", df.name "Datafile"
FROM v$datafile df, v$filestat fs WHERE df.file# = fs.file#;
# 檢視所有資料檔案 i/ o 情況 , 若檔案太多 , 可以改寫 為 top 10 select *( order by xx desc) where
rownum<=10。其中phyrds為實體讀的次數極為Reads，phywrts為實體寫的次數極為Writes，phyblkrd為實體塊讀的次數即為br，phyblkwrt為實體寫的次數即為bw。readtime為耗費在實體讀上的總時間極為RTime，writetim為耗費在實體寫上的總時間即為WTime。這兩個值隻有在參數timed_statistics參數為true時才有效。
COL ts FORMAT a10 HEADING "Tablespace";
COL reads FORMAT 999990;
COL writes FORMAT 999990;
COL br FORMAT 999990 HEADING "BlksRead";
COL bw FORMAT 999990 HEADING "BlksWrite";
COL rtime FORMAT 999990;
COL wtime FORMAT 999990;
SELECT ts.name AS ts, fs.phyrds "Reads", fs.phywrts "Writes"
,fs.phyblkrd AS br, fs.phyblkwrt AS bw
,fs.readtim "RTime", fs.writetim "WTime"
FROM v$tablespace ts, v$datafile df, v$filestat fs
WHERE ts.ts# = df.ts# AND df.file# = fs.file#
UNION
SELECT ts.name AS ts, ts.phyrds "Reads", ts.phywrts "Writes",ts.phyblkrd AS br, ts.phyblkwrt AS bw
,ts.readtim "RTime", ts.writetim "WTime"
FROM v$tablespace ts, v$tempfile tf, v$tempstat ts
WHERE ts.ts# = tf.ts# AND tf.file# = ts.file# ORDER BY 1;
# 擷取 top 10 熱 segment
set linesize 180
col object_name for a40
select * from
(select
ob.owner, ob.object_name, sum(b.tch) Touchs
from x$bh b , dba_objects ob
where b.obj = ob.data_object_id
and b.ts# > 0
group by ob.owner, ob.object_name
order by sum(tch) desc)
where rownum <=10
# 判斷實體讀最多的 object
select * from (select owner,object_name,value from v$segment_statistics where
statistic_name='physical reads' order by value desc) where rownum<=10
# 檢視熱點資料檔案 ( 從單塊讀取時間判斷 )
col FILE_NAME for a60
set linesize 180
SELECT t.file_name,
t.tablespace_name,
round(s.singleblkrdtim/s.singleblkrds, 2) AS CS,
s.READTIM,
s.WRITETIM
FROM v$filestat s, dba_data_files t
WHERE s.file# = t.file_id and s.singleblkrds <>0 and rownum<=10 order by cs desc
#估算表空間大小
select a.tablespace_name,
round(a.s,2) "CURRENT_TOTAL(MB)" ,
round((a.s - f.s),2) "USED(MB)" ,
f.s "FREE(MB)" ,
round(f.s / a.s * 100, 2) "FREE%" ,
g.autoextensible,
round(a.ms,2) "MAX_TOTAL(MB)"
from ( select d.tablespace_name,
sum (bytes / 1024 / 1024) s,
sum (decode(maxbytes, 0, bytes, maxbytes) / 1024 / 1024) ms
from dba_data_files d
group by d.tablespace_name) a,
( select f.tablespace_name, sum (f.bytes / 1024 / 1024) s
from dba_free_space f
group by f.tablespace_name) f,
( select distinct tablespace_name, autoextensible
from DBA_DATA_FILES
where autoextensible = 'YES'
union
select distinct tablespace_name, autoextensible
from DBA_DATA_FILES
where autoextensible = 'NO'
and tablespace_name not in
( select distinct tablespace_name
from DBA_DATA_FILES
where autoextensible = 'YES' )) g
where a.tablespace_name = f.tablespace_name
and g.tablespace_name = f.tablespace_name order by "FREE%" ;
檢視redo
# 檢查日志切換頻率
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 - 1 order by first_time ,minutes;
# 檢查 lgwr i/o 性能 (time_waited/total_waits:表示平均lgwr寫入完成時間若>1表示寫入過慢 )
select total_waits,time_waited,average_wait,time_waited/total_waits as avg from
v$system_event where event = 'log file parallel write';
# 查詢 redo block size
select max(lebsz) from x$kccle;
# 檢視 user commit 次數
select to_number(value,99999999999) from v$sysstat where name='user commits';
# 檢視系統運作時間
select (sysdate - startup_time)*24*60*60 as seconds from v$instance
# 計算出每秒使用者送出次數
select user_commit 次數 / 系統運作時間 from dual;
# 計算出每個事務平均處理多少個 redo block
select value from v$sysstat where name = 'redo blocks written';
select a.redoblocks/b.trancount from (select value redoblocks from v$sysstat where name='redo
blocks written') a ,(select value trancount from v$sysstat where name='user commits') b
# 計算每天産生了多少日志
SELECT TO_CHAR (TRUNC (COMPLETION_TIME), 'yyyy-mm-dd') "日期",
SUM (blocks * BLOCK_SIZE) / 1024 / 1024 / 1024 "日志量(G)"
FROM V$ARCHIVED_LOG
WHERE dest_id = 1
GROUP BY TRUNC (COMPLETION_TIME)
ORDER BY TRUNC (COMPLETION_TIME) DESC;
sga,pga, 命中率
# sga,pga, 命中率
# 檢查 sga
show sga
select * from v$sga;
# 檢視buffer cache設定建議
select size_for_estimate, estd_physical_read_factor,
to_char(estd_physical_reads,99999999999999999999999) as"estd_physical_reads" from
v$db_cache_advice where name = 'DEFAULT';
COL pool FORMAT a10;
SELECT (SELECT ROUND(value/1024/1024,0) FROM v$parameter
WHERE name = 'db_cache_size') "Current Cache(Mb)"
,name "Pool", size_for_estimate "Projected Cache(Mb)"
,ROUND(100-estd_physical_read_factor,0) "Cache Hit Ratio%"
FROM v$db_cache_advice
WHERE block_size = (SELECT value FROM v$parameter
WHERE name = 'db_block_size')
ORDER BY 3;
# 檢視 cache 池
show parameter cache
# 檢視 buffer cache 中 defalut pool 命中率
select name,1-(physical_reads)/(consistent_gets+db_block_gets)
from v$buffer_pool_statistics;
# 檢查 shared pool
show parameter shared
# 檢查 shared pool 中 library cache
select namespace,pinhitratio from v$librarycache;
# 檢查整體命中率 (library cache)
select sum(pinhits)/sum(pins) from v$librarycache;
select sum(pins) "hits",
sum(reloads) "misses",
sum(pins)/(sum(pins)+sum(reloads)) "Hits Ratio"
from v$librarycache;
# 檢查 shared pool free space
SELECT * FROM V$SGASTAT
WHERE NAME = 'free memory'
AND POOL = 'shared pool';
# 每個子shared pool 由單獨的 shared pool latch保護，檢視他們的命中率 shared pool latch，用于shared pool空間回收配置設定使用的latch
col name format a15
select addr,name,gets,misses,1-misses/gets from v$latch_children where name='shared pool';
# 使用 v$shared_pool_advice 計算不同 shared pool 大小情況下，響應時間， S 機關
SELECT 'Shared Pool' component,
shared_pool_size_for_estimate estd_sp_size,
estd_lc_time_saved_factor parse_time_factor,
CASE
WHEN current_parse_time_elapsed_s + adjustment_s < 0 THEN
ELSE
current_parse_time_elapsed_s + adjustment_s
END response_time
FROM (SELECT shared_pool_size_for_estimate,
shared_pool_size_factor,
estd_lc_time_saved_factor,
a.estd_lc_time_saved,
e.VALUE / 100 current_parse_time_elapsed_s,
c.estd_lc_time_saved - a.estd_lc_time_saved adjustment_s FROM v$shared_pool_advice a,
(SELECT * FROM v$sysstat WHERE NAME = 'parse time elapsed') e,
(SELECT estd_lc_time_saved FROM v$shared_pool_advice
WHERE shared_pool_size_factor = 1) c)
/
# 檢視 shared pool 中 各種類型的 chunk 的大小數量
SELECT KSMCHCLS CLASS, COUNT(KSMCHCLS) NUM, SUM(KSMCHSIZ) SIZ,
To_char( ((SUM(KSMCHSIZ)/COUNT(KSMCHCLS)/1024)),'999,999.00')||'k' "AVG
SIzE"
FROM X$KSMSP GROUP BY KSMCHCLS;
# 檢視是否有庫緩沖有關的等待事件
select sid,seq#,event,p1,p1raw,p2,p2raw,p3,p3raw,state from v$session_wait where event like
'library%';
# 查詢 sga 中各個 pool 情況
COL name FORMAT a32;
SELECT pool, name, bytes FROM v$sgastat
WHERE pool IS NULL
OR pool != 'shared pool' OR (pool = 'shared pool'
AND (name IN('dictionary cache','enqueue','library
cache','parameters',
'processes','sessions','free memory')))
ORDER BY pool DESC NULLS FIRST, name;
SELECT * FROM V$SGAINFO;
# 檢視使用 shard_pool 保留池情況
SELECT request_misses, request_failures, free_space
FROM v$shared_pool_reserved;
Oracle 專門從共享池内置出一塊區域來來配置設定記憶體保持這些大塊。這個保留共享池的預設大小是共享池的5%(_shared_pool_reserved_pct 5 控制 ) oracle 建設定為 10% 。大小通過參數 SHARED_POOL_RESERVED_SIZE 改。它是從共享池中配置設定，不是直接從 SGA 中配置設定的，它是共享池的保留部分，專門用于存儲大塊段#shared pool 中記憶體大于 _SHARED_POOL_RESERVED_MIN_ALLOC 将放入 shared pool 保留池 , 保留池維護一個單獨的 freelist,lru ，并且不會在 lru 清單存recreatable 類型 chunks ，普通 shared pool 的釋放與 shared pool 保留池無關。
# 關于設定 SHARED_POOL_RESERVED_SIZE
#1.如果系統出現ora-04031, 發現請求記憶體都是大于 _SHARED_POOL_RESERVED_MIN_ALLOC (default 10GR2 4400) , 且v$shared_pool_reserved 中有大量 REQUEST_MISSES( 并且可以看下LAST_MISS_SIZE )表示 SHARED_POOL_RESERVED_SIZE 太小了需要大的記憶體的請求失敗 , 那麼需要加大SHARED_POOL_RESERVED_SIZE
#2. 如果 ora-04031 請求記憶體出現在 4100-4400 并造成 shared pool lru 合并 , 老化換出記憶體 , 可以調小 _SHARED_POOL_RESERVED_MIN_ALLOC 讓此部分記憶體進入shared reserved pool, 相應的加大SHARED_POOL_RESERVED_SIZE
#3. 從 v$shared_pool_reserved 來判斷 , 如果 REQUEST_FAILURES>0( 出現過 ora-04031) 且LAST_FAILURE_SIZE( 最後請求記憶體大小 )>_SHARED_POOL_RESERVED_MIN_ALLOC表示 shared reserved pool 缺少連續記憶體 , 可以加大 SHARED_POOL_RESERVED_SIZE, 減少 _SHARED_POOL_RESERVED_MIN_ALLOC 少放對象 , 并相對加大 shared_pool_size
# 要是反過來 REQUEST_FAILURES>0( 出現過 ora-04031) 且 LAST_FAILURE_SIZE( 最後請求記憶體大小)<_SHARED_POOL_RESERVED_MIN_ALLOC, 表示 在 shared pool 中缺少連續記憶體 , 可以加減少_SHARED_POOL_RESERVED_MIN_ALLOC 多放入一些對象 , 減少 sharedpool 壓力 , 适當加大shared_pool_size,SHARED_POOL_RESERVED_SIZE
# 查詢還保留在 library cache 中，解析次數和執行次數最多的 sql( 解析 * 執行 )
COL sql_text FORMAT A38;
SELECT * FROM(
SELECT parse_calls*executions "Product", parse_calls
"Parses"
,executions "Execs", sql_text FROM v$sqlarea ORDER BY 1 DESC)
WHERE ROWNUM <= 10;
# 檢視 pga
show parameters area_size
SELECT * FROM v$pgastat;
# 檢視pga建議
SELECT (SELECT ROUND(value/1024/1024,0) FROM v$parameter
WHERE name = 'pga_aggregate_target') "Current Mb"
, ROUND(pga_target_for_estimate/1024/1024,0) "Projected Mb"
, ROUND(estd_pga_cache_hit_percentage) "%"
FROM v$pga_target_advice
ORDER BY 2;
# 檢視資料庫 cache 或 keep 了哪些 object
COL table_name FORMAT A16
COL index_name FORMAT A16
SELECT table_name AS "Table", NULL, buffer_pool, cache FROM
user_tables
WHERE buffer_pool != 'DEFAULT' OR TRIM(cache)='Y'
UNION
SELECT table_name, index_name, NULL, buffer_pool FROM
user_indexes
WHERE buffer_pool != 'DEFAULT'
ORDER BY 1, 2 NULLS FIRST;
# 取消 cache 或 keep(keep pool)
ALTER TABLE XX NOCACHE;
SELECT 'ALTER INDEX '||index_name||' STORAGE(BUFFER_POOL DEFAULT);'
FROM USER_INDEXES WHERE BUFFER_POOL!='DEFAULT';
檢查undo
show parameter undo_
# 檢查 undo rollback segment 使用情況
select name ,rssize,extents,latch,xacts,writes,gets,waits from v$rollstat a,v$rollname b where
a.usn=b.usn order by waits desc;
select a.redoblocks/b.trancount from (select value redoblocks from v$sysstat where name='redo
blocks written') a ,(select value trancount from v$sysstat where name='user commits') b;
# 計算每秒鐘産生的 undoblk 數量
select sum(undoblks)/sum((end_time-begin_time)*24*60*60) from v$undostat;
#Undospace=UR*UPS*blocksize + overload(10%), 計算 undo tablespace 大小
show parameter block_size
show parameter undo_retention
# 計算undo表空間大小
#select undo_retention* 每 秒 産 生 undoblk 數 量 *block_size/1024/1024/1024+
(1+1undo_retention* 每秒産生 undoblk 數量 *block_size/1024/1024/1024*0.1) from dual;
# 查詢 undo 具體資訊
COL undob FORMAT 99990;
COL trans FORMAT 99990;
COL snapshot2old FORMAT 9999999990;
SELECT undoblks "UndoB", txncount "Trans"
,maxquerylen "LongestQuery", maxconcurrency "MaxConcurrency"
,ssolderrcnt "Snapshot2Old", nospaceerrcnt "FreeSpaceWait"
FROM v$undostat;
# 在記憶體中排序比率 ( 最優排序 )
SELECT 'Sorts in Memory ' "Ratio"
, ROUND(
(SELECT SUM(value) FROM V$SYSSTAT WHERE name = 'sorts (memory)')
/ (SELECT SUM(value) FROM V$SYSSTAT
WHERE name IN ('sorts (memory)', 'sorts (disk)')) * 100, 5)
||'%' "Percentage"
FROM DUAL;
# 檢視目前系統undo使用情況
SELECT DISTINCT STATUS "狀态",
COUNT(*) "EXTENT數量",
SUM(BYTES) / 1024 / 1024 / 1024 "UNDO大小"
FROM DBA_UNDO_EXTENTS
GROUP BY STATUS;
# 檢視目前系統和undo相關的會話
SELECT
r.name undo_seg_name
, se.sid sid
, se.serial#
, se.username
, se.machine
, t.start_time
, t.status
, t.used_ublk
, t.used_urec
, t.cr_get
, t.cr_change
, t.log_io
, t.phy_io
, t.noundo
, g.extents
, se.program
, s.sql_id
, s.child_number
, s.sql_text
FROM
v$session se
, v$transaction t
, v$rollname r
, v$rollstat g
, v$sql s
WHERE t.addr = se.taddr
AND t.xidusn = r.usn
AND r.usn = g.usn
AND s.address = se.sql_address
ORDER BY t.used_ublk DESC;
檢視對象
# 檢查資料庫中無效對象
SELECT owner, object_type,count(object_name) FROM dba_objects WHERE status= 'INVALID'group by owner,object_type;
# 檢查是否有禁用限制
SELECT owner, constraint_name, table_name, constraint_type, status
FROM dba_constraints
WHERE status ='DISABLE' and constraint_type='P'
# 檢查是否有禁用 trigger
col owner for a10
col taigger_name for a10
cok table_name for a30
col table_name for a30
SELECT owner, trigger_name, table_name, status FROM dba_triggers WHERE status =
'DISABLED';
# 在某個表下找的索引情況
col column_name for a12
set linesize 180
select user_indexes.table_name, user_indexes.index_name,uniqueness, column_name
from user_ind_columns, user_indexes
where user_ind_columns.index_name = user_indexes.index_name
and user_ind_columns.table_name = user_indexes.table_name
AND user_indexes.table_name='&tb_name'
order by user_indexes.table_type, user_indexes.table_name,
user_indexes.index_name, column_position;
# 檢查與索引相關的字段
select * from user_ind_columns where index_name=upper('&index_name');
# 檢查索引的唯一性的
col uniq format a10 heading 'Uniqueness' justify c trunc
col indname format a40 heading 'Index Name' justify c trunc
col colname format a25 heading 'Column Name' justify c trunc
break -
on indname skip 1 -
on uniq
select
ind.uniqueness uniq,
ind.owner||'.'||col.index_name indname,
col.column_name colname
from
dba_ind_columns col,
dba_indexes ind
where
ind.owner = upper('&ixowner')
and
ind.table_name = upper('&tabname')
and
col.index_owner = ind.owner
and
col.index_name = ind.index_name
order by
col.index_name,
col.column_position
檢視目前系統狀态
# 檢查系統中排行前10的等待事件，不包括空閑等待事件
select * from (select sid,event,p1,p2,p3,p1text,WAIT_TIME,SECONDS_IN_WAIT,state from v$session_wait where wait_class# <> 6
order by wait_time desc) where rownum <=10;
# 檢視經常被使用而沒有pin在記憶體中的對象
# 形成生成pin住共享池中目前沒有被pin住的對象的sql語句。在執行exec sys.DBMS_SHARED_POOL.keep('JXXXT.IN_GZ_LOGS','P');可能會報出未定義的錯誤，需要在sqlplus下執行腳本$ORACLE_HOME/rdbms/admin/dbmspool.sql
select 'exec sys.DBMS_SHARED_POOL.keep('||chr(39)||owner||'.'||NAME||chr(39)||','||chr(39)||'P'||chr(39)||');' as sql_to_run
from V$DB_OBJECT_CACHE where TYPE in ('PACKAGE','FUNCTION','PROCEDURE') and loads > 50 and kept='NO' and executions > 50;
# 檢視使用了超過10MB記憶體 而沒有pin的對象
SELECT owner,name,sharable_mem,kept FROM V$DB_OBJECT_CACHE
WHERE sharable_mem > 102400 AND kept = 'NO' ORDER BY sharable_mem DESC;
# 檢視大的沒有被pin住的對象.
set linesize 150
col sz for a10
col name for a100
col keeped for a6
select to_char(sharable_mem / 1024,'999999') sz_in_K, decode(kept, 'yes','yes ','') keeped,
owner||','||name||lpad(' ',29 - (length(owner) + length(name))) || '(' ||type||')'name,
null extra, 0 iscur from v$db_object_cache v where sharable_mem > 1024*1000;
# 檢視大的沒有被pin住的過程，包和函數
col type for a25
col name for a40
col owner for a25
select owner,name,type,round(sum(sharable_mem/1024),1) sharable_mem_K from v$db_object_cache where kept = 'NO'
and (type = 'PACKAGE' or type = 'FUNCTION' or type = 'PROCEDURE')
group by owner,name,type order by 4;
需要被pin入記憶體中的對象主要有：常用的較大的存儲對象，如standard、diutil包；編譯的常用的triggers；sequences。
最好在開機時就将其pin入記憶體中。這樣，既是使用指令alter system flush shared_pool時，也不會講這些object flush掉。具體pin對象到記憶體的方法使用DBMS_SHARED_POOL.keep存儲過程。可以用unkeep方法解除其pin狀态。
db_object_cache和碎片化
碎片化造成在共享池中雖然有許多小的碎片可以使用，但沒有足夠大的連續空間，這在共享池中是普遍的現象。消除共享池錯誤的關鍵就是即将加載對象的大小是否可能會産生問題。一旦知道了這個存在問題的PL/SQL，那麼就可以在資料庫啟動時(這時共享池是完全連續的)就将這個代碼固定。這将確定在調用大型包時，它已經在共享池裡，而不是在共享池中搜尋連續的碎片(在使用系統時，這些碎片可能就不複存在)。可以查詢V$DB_OBJECT_CACHE視圖來判斷PL/SQL是否很大并且還沒有被辨別為"kept"的标記。今後需要加載這些對象時，可能會産生問題(因為它們的大小和需要占用大量連續的記憶體)。通過查詢V$DB_OBJECT_CACHE表，可以發現那些沒有固定，但由于所需空間太大而很有可能導緻潛在問題的對象。
# 查詢一下復原段的使用情況，其中USED_UREC為undo記錄的使用條目數，USED_UBLK為undo塊的使用數目
set linesize 180
SELECT a.sid, a.username, b.xidusn, b.used_urec, b.used_ublk
from v$session a, v$transaction b
WHERE a.saddr = b.ses_addr;
# 檢視目前活動的會話
SELECT
p.username AS process_username
, se.username AS schema_name
, se.status AS status
, se.sid AS ssid
, se.serial# AS serial#
, p.spid AS spid
, s.sql_text AS sql_txt
FROM
v$process p
, v$session se
, v$sqlarea s
WHERE p.addr = se.paddr
AND se.sql_address = s.address(+)
AND se.sql_hash_value = s.hash_value(+)
AND se.status = 'ACTIVE'
AND se.username IS NOT NULL;
# sql的綁定變量
SELECT
s.sid
, s.username
, sq.sql_text
, s.sql_hash_value
, s.sql_id
, s.sql_child_number
, spc.name
, spc.value_string
, last_captured
FROM
v$sql_bind_capture spc
, v$session s
, v$sql sq
WHERE s.sql_hash_value = spc.hash_value
AND s.sql_address = spc.address
AND sq.sql_id = s.sql_id
AND spc.was_captured = 'YES'
AND s.TYPE <> 'BACKGROUND'
--AND s.sql_id = '&sql_id'
--AND s.status = 'ACTIVE'
;
# 檢視sql記憶體的使用情況
SELECT
b.sid
, name
, value/(1024 * 1024) "MB"
, s.serial#
, s.sid ssid
, s.status
, s.username
, s.schemaname
, s.osuser
, s.machine
, s.terminal
, s.program
, s.module
, state
, logon_time
, p.spid
, sa.sql_text sql_txt
FROM
v$statname a
, v$sesstat b
, v$session s
, v$process p
, v$sqlarea sa
WHERE a.statistic# = b.statistic#
AND b.sid = s.sid
AND osuser! = 'oracle'
AND p.addr = s.paddr
AND s.sql_address = sa.address(+)
AND s.sql_hash_value = sa.hash_value(+)
AND NAME LIKE '%a memory'
ORDER BY status,mb DESC,sid, name;
# 檢視鎖住對象的會話資訊，作業系統程序資訊
set linesize 180
select object_name,machine,s.sid,s.serial#,p.spid
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
# 根據程序檢視sql
select sql_text
from v$sqltext_with_newlines
where (hash_value,address) in (select sql_hash_value,sql_address from v$session where sid=(select ses.sid from v$session ses,v$process pro
where pro.spid=&spid
and ses.paddr=pro.addr)) order by address,piece;
# 檢視被鎖的表的被鎖時間
set linesize 180
select b.username,b.sid,b.serial#,logon_time
from v$locked_object a,v$session b
where a.session_id = b.sid order by b.logon_time;
# 檢視被鎖的對象和引起鎖的sql
select a.sid,a.username,d.object_name, b.sql_text
from v$session a,v$sql b, v$locked_object c,dba_objects d
where a.sql_hash_value=b.hash_value
and a.sid = c.session_id
and d.object_id = c.object_id;
# 檢視鎖定的會話資訊
select b.username,b.sid,b.serial#,logon_time
from v$locked_object a,v$session b
where a.session_id = b.sid order by b.logon_time
# 殺死相關會話
alter system kill session 'sid,serial#';
# 如果出現ora-00031錯誤，則
alter system kill session 'sid,serial#' immediate；
# 亦可先查詢該會話相對應的作業系統程序，在作業系統上進行kill
# 查找需要使用綁定變量的sql
select substr(sql_text,1,40), count(*)
from v$sqlarea
group by substr(sql_text,1,40) having count(*) > 50;
再 select sql_text from v$sqlarea where sql_text like 'insert into test %'; 找出具體的sql代碼
檢查Latch的相關SQL
# 查詢目前資料庫最繁忙的Buffer，TCH(Touch)越大表示通路次數越高
SELECT *
FROM ( SELECT addr,
ts#,
file#,
dbarfil,
dbablk,
tch
FROM x$bh
ORDER BY tch DESC)
WHERE ROWNUM < 11;
# 檢視latch的命中率
SQL>SELECT name, gets, misses, sleeps,
immediate_gets, immediate_misses
FROM v$latch
WHERE name = 'cache buffers chains';
#查找資料塊中的熱點塊
SELECT *
FROM (SELECT O.OWNER, O.OBJECT_NAME, O.OBJECT_TYPE, SUM(TCH) TOUCHTIME
FROM X$BH B, DBA_OBJECTS O
WHERE B.OBJ = O.DATA_OBJECT_ID
AND B.TS# > 0
GROUP BY O.OWNER, O.OBJECT_NAME, O.OBJECT_TYPE
ORDER BY SUM(TCH) DESC)
WHERE ROWNUM <= 10;
#根據檔案号和塊号查找資料庫對象
select owner, segment_name, partition_name, tablespace_name
from dba_extents
where relative_fno = &v_dba_rfile and &v_dba_block between block_id and block_id + blocks - 1;
# 如果在Top 5中發現latch free熱點塊事件時，可以從V$latch_children中查詢具體的子Latch資訊
SELECT *
FROM (SELECT addr, child#, gets, misses, sleeps, immediate_gets igets,
immediate_misses imiss, spin_gets sgets
FROM v$latch_children
WHERE NAME = 'cache buffers chains'
ORDER BY sleeps DESC)
WHERE ROWNUM < 11;
# 檢視引起latch: cache buffers chains的sql
SQL> select * from (select
count(*),
sql_id,
nvl(o.object_name,ash.current_obj#) objn,
substr(o.object_type,0,10) otype,
3 4 5 6 CURRENT_FILE# fn,
CURRENT_BLOCK# blockn
from v$active_session_history ash
, all_objects o
where event like 'latch: cache buffers chains'
and o.object_id (+)= ash.CURRENT_OBJ#
group by sql_id, current_obj#, current_file#,
current_block#, o.object_name,o.object_type
order by count(*) desc )where rownum <=10;
日常管理 #檢視目前使用者的trace檔案
SELECT u_dump.VALUE
|| '/'
|| db_name.VALUE
|| '_ora_'
|| v$process.spid
|| NVL2 (v$process.traceid, '_' || v$process.traceid, NULL)
|| '.trc'
"Trace File"
FROM v$parameter u_dump
CROSS JOIN
v$parameter db_name
CROSS JOIN
v$process
JOIN
v$session
ON v$process.addr = v$session.paddr
WHERE u_dump.name = 'user_dump_dest'
AND db_name.name = 'db_name'
AND v$session.audsid = SYS_CONTEXT ('userenv', 'sessionid')
#查詢某段時間内執行過的sql
select a.sql_id,dbms_lob.substr(b.sql_text,4000,1) from dba_hist_active_sess_history a, dba_hist_sqltext b
where sample_time between to_date('20100930:09:00','yyyymmdd:hh24:mi')
and to_date('20100930:09:01','yyyymmdd:hh24:mi') and b.sql_id=a.sql_id
union all
select a.sql_id ,dbms_lob.substr(b.sql_text,4000,1)from v$active_session_history a ,v$sqlarea b
where sample_time between to_date('20100930:09:00','yyyymmdd:hh24:mi') and
to_date('20100930:09:01','yyyymmdd:hh24:mi') and b.sql_id=a.sql_id
由于v$active_session_history和dba_hist_active_sess_history的資料來源于awr和ash采樣，記錄并不完全，故查詢結果并不準确。
#檢視sql的實際執行計劃
SELECT sql_id, address, hash_value FROM v$sql
WHERE sql_text like ¨%TAG%¨;
SQL_ID ADDRESS HASH_VALUE
-------- -------- ----------
40qhh45kcnfbv 82157784 1224822469
#通過sqlid查詢庫緩沖區中的sql執行計劃
SELECT * FROM TABLE(dbms_xplan.display_cursor('40qhh45kcnfbv',0));
#檢視全面的執行計劃
select /*+ gather_plan_statistics */ count(*) from scott.emp;
select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
# 查找你的session資訊
SELECT SID, OSUSER, USERNAME, MACHINE, PROCESS
FROM V$SESSION WHERE audsid = userenv('SESSIONID');
# 當machine已知的情況下查找session
SELECT SID, OSUSER, USERNAME, MACHINE, TERMINAL
FROM V$SESSION
WHERE terminal = 'pts/tl' AND machine = 'rgmdbs1';
# 查找目前被某個指定session正在運作的sql語句。尋找被指定session執行的SQL語句是一個公共需求，如果session是瓶頸的主要原因，那根據其目前在執行的語句可以檢視session在做些什麼。 假設sessionID為100
select b.sql_text
from v$session a,v$sqlarea b
where a.sql_hashvalue=b.hash_value and a.sid=100
#檢視sql執行狀态
select status,last_call_et,event from v$session where sid=&id;
#檢視用戶端和應用資訊
select osuser,machine,terminal,process,program,client_info,action,module from v$session
where sid=&id and seq#=&seq
#檢視會話消耗資源的情況，以CPU資源為例，不同的資源可以根據v$statname和v$sesstat關聯進行查詢，常用的有session logical reads, CPU used by this session, db block changes, redo size,
physical writes, parse count (hard), parse count (total), sorts (memory), and sorts (disk)等
set linesize 200
col prog for a35
col name for a30
col TERMINAL for a15
col machine for a25
col SPID for 999999999
select a.sid,spid,status,substr(a.program,1,40) prog,a.machine, a.terminal,osuser,c.value value,d.name
from v$session a,v$process b,v$sesstat c,v$statname d
where d.name='CPU used by this session'
and c.sid=a.sid and a.paddr=b.addr and d.statistic#=c.statistic#
order by c.value ;
注意:v$sysstat 和v$sesstat差别如下：
v$sesstat隻儲存session資料，而v$sysstat則儲存所有sessions的累積值。
v$sesstat隻是暫存資料，session退出後資料即清空。v$sysstat則是累積的，隻有當執行個體被shutdown才會清空。
v$sesstat不包括統計項名稱，如果要獲得統計項名稱則必須與v$sysstat或v$statname連接配接查詢獲得。
#通過sqlid查詢庫AWR中的sql執行計劃
SELECT * FROM table (DBMS_XPLAN.DISPLAY_AWR ('40qhh45kcnfbv'));
#檢視某使用者的PID和SPID
select pid,spid from v$process
where addr in (select paddr from v$session where username='SYSTEM');
#檢視隐含參數
select x.ksppinm name, y.ksppstvl value, x.ksppdesc describ
from sys.x$ksppi x , sys.x$ksppcv y
where x.indx = y.indx
and x.ksppinm like '%&par%';
#檢視對象大小，對象大小以已配置設定的extent統計
select segment_name, bytes/1024/1024 MB
from user_segments
where segment_type = 'TABLE';
或者
Select SEGMENT_NAME,Sum(bytes)/1024/1024 From User_Extents where segment_name='BIG' group by segment_name;
# 檢視表的統計資訊
select table_name,num_rows,blocks,empty_blocks,avg_space,chain_cnt,avg_row_len,global_stats,user_stats,sample_size,to_char(last_analyzed,'yyyy-mm-dd hh24:mi:ss')
from dba_tables where owner = upper(nvl('&Owner',user)) and table_name=upper('&table_name');
# 檢視分區的統計資訊
select table_name,PARTITION_NAME ,composite,SUBPARTITION_COUNT,num_rows,blocks,empty_blocks,avg_space,chain_cnt,avg_row_len,global_stats,user_stats,sample_size,to_char(last_analyzed,'yyyy-mm-dd hh24:mi:ss')
from dba_tab_partitions where and table_name=upper('&table_name');
# 檢視子分區的統計資訊
select table_name,PARTITION_NAME ,subpartition_name,subpartition_position,num_rows,blocks,empty_blocks,avg_space,chain_cnt,avg_row_len,global_stats,user_stats,sample_size,to_char(last_analyzed,'yyyy-mm-dd hh24:mi:ss')
from dba_TAB_SUBPARTITIONS where table_name=upper('&table_name');
#檢視列上的統計資訊
select
COLUMN_NAME,
decode(t.DATA_TYPE,
'NUMBER',t.DATA_TYPE||'('||
decode(t.DATA_PRECISION,
null,t.DATA_LENGTH||')',
t.DATA_PRECISION||','||t.DATA_SCALE||')'),
'DATE',t.DATA_TYPE,
'LONG',t.DATA_TYPE,
'LONG RAW',t.DATA_TYPE,
'ROWID',t.DATA_TYPE,
'MLSLABEL',t.DATA_TYPE,
t.DATA_TYPE||'('||t.DATA_LENGTH||')') ||' '||
decode(t.nullable,
'N','NOT NULL',
'n','NOT NULL',
NULL) col,
NUM_DISTINCT,
DENSITY,
NUM_BUCKETS,
NUM_NULLS,
GLOBAL_STATS,
USER_STATS,
SAMPLE_SIZE,
HISTOGRAM,
AVG_COL_LEN,
to_char(t.last_analyzed,'yyyy-mm-dd hh24:mi:ss')
from dba_tab_columns t
where
table_name = upper('&Table_name')
and owner = upper(nvl('&Owner',user));
# 檢視分區表列上的統計資訊
select
TABLE_NAME,
PARTITION_NAME,
COLUMN_NAME,
NUM_DISTINCT,
DENSITY,
NUM_BUCKETS,
NUM_NULLS,
GLOBAL_STATS,
USER_STATS,
SAMPLE_SIZE,
to_char(t.last_analyzed,'YYYY-MM-DD HH24:MI:SS')
from
dba_PART_COL_STATISTICS t
where
table_name = upper('&Table_name')
and owner = upper(nvl('&Owner',user))
/
# 檢視子分區上列的統計資訊
select
p.PARTITION_NAME,
t.SUBPARTITION_NAME,
t.COLUMN_NAME,
t.NUM_DISTINCT,
t.DENSITY,
t.NUM_BUCKETS,
t.NUM_NULLS,
t.GLOBAL_STATS,
t.USER_STATS,
t.SAMPLE_SIZE,
to_char(t.last_analyzed,'YYYY-MM-DD HH24:MI:SS')
from
dba_SUBPART_COL_STATISTICS t,
dba_tab_subpartitions p
where
t.table_name = upper('&Table_name')
and t.owner = upper(nvl('&Owner',user))
and t.subpartition_name = p.subpartition_name
and t.owner = p.table_owner
and t.table_name=p.table_name;
#索引的統計資訊
select
INDEX_NAME,
UNIQUENESS,
BLEVEL BLev,
LEAF_BLOCKS,
DISTINCT_KEYS,
NUM_ROWS,
AVG_LEAF_BLOCKS_PER_KEY,
AVG_DATA_BLOCKS_PER_KEY,
CLUSTERING_FACTOR,
GLOBAL_STATS,
USER_STATS,
SAMPLE_SIZE,
to_char(t.last_analyzed,'YYYY-MM-DD HH24:MI:SS')
from
dba_indexes t
where
table_name = upper('&Table_name')
and table_owner = upper(nvl('&Owner',user));
#檢視分區索引的統計資訊
select
i.INDEX_NAME,
i.COLUMN_NAME,
i.COLUMN_POSITION,
decode(t.DATA_TYPE,
'NUMBER',t.DATA_TYPE||'('||
decode(t.DATA_PRECISION,
null,t.DATA_LENGTH||')',
t.DATA_PRECISION||','||t.DATA_SCALE||')'),
'DATE',t.DATA_TYPE,
'LONG',t.DATA_TYPE,
'LONG RAW',t.DATA_TYPE,
'ROWID',t.DATA_TYPE,
'MLSLABEL',t.DATA_TYPE,
t.DATA_TYPE||'('||t.DATA_LENGTH||')') ||' '||
decode(t.nullable,
'N','NOT NULL',
'n','NOT NULL',
NULL) col
from
dba_ind_columns i,
dba_tab_columns t
where
i.table_name = upper('&Table_name')
and owner = upper(nvl('&Owner',user))
and i.table_name = t.table_name
and i.column_name = t.column_name
order by index_name,column_position;
# 檢視子分區索引的統計資訊
select
t.INDEX_NAME,
t.PARTITION_NAME,
t.SUBPARTITION_NAME,
t.BLEVEL BLev,
t.LEAF_BLOCKS,
t.DISTINCT_KEYS,
t.NUM_ROWS,
t.AVG_LEAF_BLOCKS_PER_KEY,
t.AVG_DATA_BLOCKS_PER_KEY,
t.CLUSTERING_FACTOR,
t.GLOBAL_STATS,
t.USER_STATS,
t.SAMPLE_SIZE,
to_char(t.last_analyzed,'MM-DD-YYYY')
from
dba_ind_subpartitions t,
dba_indexes i
where
i.table_name = upper('&Table_name')
and i.table_owner = upper(nvl('&Owner',user))
and i.owner = t.index_owner
and i.index_name=t.index_name;
#正在運作的存儲過程
col name format a56
select name
from v$db_object_cache
where locks > 0 and pins > 0 and type='PROCEDURE';
select sid,name from v$access;
SELECT s.SID, s.OSUSER, s.USERNAME, s.MACHINE, s.PROCESS,a.object FROM V$SESSION s ,v$access a, v$db_object_cache d WHERE s.sid = a.sid and a.object=d.name and d.type='PROCEDURE';
# 查詢外鍵限制(查scott使用者emp表的所有父表)
set linesize 120
col owner for a8
col table_name for a12
col constraint_name for a20
col column_name for a20
select c.constraint_name,cc.column_name,rcc.owner,rcc.table_name,rcc.column_name
from dba_constraints c,dba_cons_columns cc,dba_cons_columns rcc
where c.owner='SCOTT'
and c.table_name='EMP' and c.constraint_type='R' and c.owner=cc.owner
and c.constraint_name=cc.constraint_name and c.r_owner=rcc.owner and c.r_constraint_name=rcc.constraint_name
and cc.position=rcc.position
order by c.constraint_name,cc.position;
# 查詢連接配接到某表的所有外鍵(查HR使用者下EMPLOYEES表的所有子表)
set linesize 120
col owner for a8
col pk_tab for a12
col fk_tab for a12
col pk for a15
col fk for a15
col pk_col for a12
col fk_col for a12
select rcc.owner,rcc.table_name pk_tab,rcc.constraint_name pk,rcc.column_name pk_col,c.table_name fk_tab,c.constraint_name fk,cc.column_name fk_col
from dba_constraints c,dba_cons_columns cc,dba_cons_columns rcc
where c.owner='HR' and rcc.table_name='EMPLOYEES'
and c.constraint_type='R'
and c.owner=cc.owner and c.constraint_name=cc.constraint_name
and c.r_constraint_name=rcc.constraint_name
order by c.constraint_name,cc.position;
# 查詢主鍵唯一鍵限制(HR使用者Employees表的主鍵唯一鍵限制)
set linesize 120
col constraint_type for a8
col constraint_name for a20
col column_name for a20
select c.constraint_name,c.constraint_type,cc.column_name
from dba_constraints c,dba_cons_columns cc
where c.owner='HR' and c.table_name='EMPLOYEES' and c.owner=cc.owner and
c.constraint_name=cc.constraint_name and c.constraint_type in ('P','U')
order by c.constraint_type,c.constraint_name,cc.position;
#監控使用并行的sql
set pages 0
column sql_test format a60
select p.server_name,
sql.sql_text
from v$px_process p, v$sql sql, v$session s
WHERE p.sid = s.sid
and p.serial# = s.serial#
and s.sql_address = sql.address
and s.sql_hash_value = sql.hash_value
/
#檢視并行程序的會話統計資訊,如實體讀
SELECT QCSID, SID, INST_ID "Inst", SERVER_GROUP "Group", SERVER_SET "Set",
NAME "Stat Name", VALUE
FROM GV$PX_SESSTAT A, V$STATNAME B
WHERE A.STATISTIC# = B.STATISTIC# AND NAME LIKE 'PHYSICAL READS'
AND VALUE > 0 ORDER BY QCSID, QCINST_ID, SERVER_GROUP, SERVER_SET;
#檢視并行程序的系統統計資訊
SELECT NAME, VALUE FROM GV$SYSSTAT
WHERE UPPER (NAME) LIKE '%PARALLEL OPERATIONS%'
OR UPPER (NAME) LIKE '%PARALLELIZED%' OR UPPER (NAME) LIKE '%PX%';
#檢視隐藏參數
col KSPPINM for a40
col KSPPDESC for a40
SELECT ksppinm, ksppstvl, ksppdesc
FROM x$ksppi x, x$ksppcv y
WHERE x.indx = y.indx AND ksppinm = '_db_block_hash_buckets'
#檢視本會話消耗的資源
select sn.statistic#,
sn.name,
m.value
from v$statname sn, v$mystat m
where sn.statistic# = m.statistic#;
#檢視LOB字段資訊
SELECT A.OWNER,
A.TABLE_NAME,
A.COLUMN_NAME,
B.SEGMENT_NAME,
B.SEGMENT_TYPE,
B.TABLESPACE_NAME,
B.BYTES/1024/1024/1024 gb,
B.BLOCKS,
B.EXTENTS
FROM dba_LOBS A, dba_SEGMENTS B
WHERE A.SEGMENT_NAME = B.SEGMENT_NAME
ORDER BY B.BYTES DESC;
#清空特定的執行計劃
select address,hash_value,executions,parse_calls from v$sql where sql_id='d88hn81x70bfd';
exec dbms_shared_pool.purge('00000011D8114B20,3714054405','C');
#db file sequential read事件參數資訊
select b.sid,
nvl(substr(a.object_name,1,30),
'P1='||b.p1||' P2='||b.p2||' P3='||b.p3) object_name,
a.subobject_name,
a.object_type
from dba_objects a, v$session_wait b, x$bh c
where c.obj = a.object_id(+)
and b.p1 = c.file#(+)
and b.p2 = c.dbablk(+)
and b.event = 'db file sequential read'
union
select b.sid,
nvl(substr(a.object_name,1,30),
'P1='||b.p1||' P2='||b.p2||' P3='||b.p3) object_name,
a.subobject_name,
a.object_type
from dba_objects a, v$session_wait b, x$bh c
where c.obj = a.data_object_id(+)
and b.p1 = c.file#(+)
and b.p2 = c.dbablk(+)
and b.event = 'db file sequential read'
order by 1;
#查詢某段時間的事務數
select instance_number,
metric_unit,
trunc(begin_time) time,
round(avg(average), 2) average
from DBA_HIST_SYSMETRIC_SUMMARY
where metric_unit = 'Transactions Per Second'
and begin_time >=
to_date('2013-01-21 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
and begin_time < to_date('2013-01-22 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
group by instance_number, metric_unit, trunc(begin_time)
order by instance_number;
#從DBA_HIST_ACTIVE_SESS_HISTORY查詢等待事件關系
set linesize 400
col WAIT_CHAIN for a80
col %This for a8
col SECONDS for 999999999
col AAS for 999999999
@/home/oracle/scripts/dash_wait_chains username||':'||event 1=1 sysdate-1 sysdate
#從V$ACTIVE_SESSION_HISTORY查詢等待事件關系
set linesize 400
col WAIT_CHAIN for a80
col %This for a8
col SECONDS for 999999999
col AAS for 999999999
@/home/oracle/scripts/ash_wait_chains username||':'||event 1=1 sysdate-1 sysdate
#對程序673每秒都進行跟蹤，共跟蹤10秒
@snapper out 1 10 673
#檢視latch命中率
set linesize 200
SELECT substr(ln.name, 1, 20), gets, misses, immediate_gets,
immediate_misses,
round((misses / decode(gets,0,gets+1,gets)),4) * 100 MISS_RATIO,
(immediate_misses)/
decode(immediate_gets+immediate_misses,0,
immediate_gets+immediate_misses+1,
round(immediate_gets+immediate_misses),4) * 100 IMM_MISS_RATIO
FROM v$latch l, v$latchname ln
WHERE ln.name like 'redo%'
AND ln.latch# = l.latch#
/
RESIZE表空間
select segment_type, segment_name, partition_name,
case segment_type
when 'TABLE' then 'alter table ' || owner || '.' || segment_name || ' move tablespace dbs_temp;',
when 'INDEX' then 'alter index ' || owner || '.' || segment_name || ' rebuild tablespace dbs_temp;',
when 'INDEX PARTITION' then 'alter index ' || owner || '.' || segment_name || ' rebuild tablespace dbs_temp;',
when 'TABLE PARTITION' then 'alter table ' || owner || '.' || segment_name || ' move partition ' || partition_name || 'tablespace dbs_temp;' sqltext
from dba_segments
where tablespace_name = 'FUND_TABLE'
and segment_type not like 'LOB%';
select a.file#,
a.name,
a.bytes / 1024 / 1024 CurrentMB,
ceil(HWM * a.block_size) / 1024 / 1024 ResizeTo,
(a.bytes - HWM * a.block_size) / 1024 / 1024 ReleaseMB,
'alter database datafile ''' || a.name || ''' resize ' ||
ceil(HWM * a.block_size / 1024 / 1024) || 'M;' ResizeCMD
from v$datafile a,
(select file_id, max(block_id + blocks - 1) HWM
from dba_extents
group by file_id) b
where a.file# = b.file_id(+)
and (a.bytes - HWM * block_size) > 0
order by 5
檢視復原的的使用情況
select start_time, --事務起始時間
username, --使用者名
s.MACHINE, --機器名稱
s.OSUSER, --登入名
r.name, --復原段名稱
ubafil, --Undo block address (UBA) filenum
ubablk, --UBA block number
t.status, --回話狀态
(used_ublk * p.value) / 1024 blk, --使用的復原段空間
used_urec, --使用的undo 記錄 ,
s1.SQL_ID, --sql_id
s1.SQL_TEXT --sql文本
from v$transaction t, v$rollname r, v$session s, v$parameter p,v$sql s1
where xidusn = usn
and s.saddr = t.ses_addr
and p.name = 'db_block_size'
and s.SADDR=s1.ADDRESS(+)
order by 1;
察看長時間執行的sql的進度
select * from (
select opname, target, sofar, totalwork, round(SOFAR/TOTALWORK, 5)*100 ||'%' sofar_percent,
units, elapsed_seconds, message
from v$session_longops
where 1=1
and TOTALWORK <>0
-- sid = <sid> and serial# = <serial#>
order by start_time desc)
where rownum <=1;
察看備庫是否和主庫同步
SQL> SELECT ARCHIVED_THREAD#, ARCHIVED_SEQ#, APPLIED_THREAD#,
APPLIED_SEQ# FROM V$ARCHIVE_DEST_STATUS;
select * from v$archive_gap;
注意：11g以後恢複歸檔日之後會自動注冊到備庫
察看備庫已經歸檔的redo
SQL> SELECT REGISTRAR, CREATOR, THREAD#, SEQUENCE#, FIRST_CHANGE#, NEXT_CHANGE# FROM V$ARCHIVED_LOG;
察看備庫已經應用的 redo
SQL> SELECT THREAD#, SEQUENCE#, FIRST_CHANGE#, NEXT_CHANGE#
FROM V$LOG_HISTORY;
察看備庫接收 , 應用redo資料過程 .
SQL> SELECT MESSAGE FROM V$DATAGUARD_STATUS;
檢視從庫上的日志接收情況
SQL> select status,target,archiver,error,process from v$archive_dest;
檢視從庫上的應用程序狀态 select process,status,client_process,sequence#,block# from v$managed_standby;
感謝$無為公子、蕭雨、惜分飛的幫助
參考至:
http://mlxia.iteye.com/blog/741227
http://www.dbtan.com/2010/05/latch-free.html
http://www.2cto.com/database/201107/96826.html
http://blog.csdn.net/robinson1988/article/details/4793962
http://www.laoxiong.net/dbms_stats_and_analyze_and_global_statistics.html
http://www.laoxiong.net/wp-content/uploads/2008/12/sosi.sql
http://oracledoug.com/px.pdf
http://www.linuxeden.com/html/database/20111127/117134.html
https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&id=1019722.6
http://docs.oracle.com/cd/E11882_01/server.112/e25523/parallel006.htm#VLDBG1513
![阿裡雲天池龍珠計劃SQL訓練營day1[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)