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
f.tablespace_name,
a.total,
f.free,(a.total-f.free)/1024 "used SIZE(G)"
,round((f.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) f
WHERE a.tablespace_name = f.tablespace_name(+)
order by "% Free"
/
# 查詢臨時 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.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
# 檢視當前臨時表空間使用大小與正在佔用臨時表空間的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;
# 檢視資料檔案資訊 , 若檔案較多可以根據需要欄位進行排序 輸出 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%" ;
#精確計算表空間大小，消耗系統資源，慎用
SELECT F.TABLESPACE_NAME,
A.ALL_TOTAL "總空間" ,
A.ALL_USED "總使用空間" ,
A.ALL_TOTAL - A.ALL_USED "總剩餘空間" ,
(A.ALL_TOTAL - A.ALL_USED) / A.ALL_TOTAL * 100 "總剩餘比例" ,
A.TOTAL "當前大小" ,
U.USED "當前使用空間" ,
F. FREE "當前剩餘空間" ,
(U.USED / A.TOTAL) * 100 "當前使用比例" ,
(F. FREE / A.TOTAL) * 100 "當前剩餘比例"
FROM ( SELECT TABLESPACE_NAME,
SUM (BYTES / (1024 * 1024 * 1024)) TOTAL,
SUM (DECODE(AUTOEXTENSIBLE, 'YES' , MAXBYTES, BYTES) /
(1024 * 1024 * 1024)) ALL_TOTAL,
SUM (USER_BYTES) / (1024 * 1024 * 1024) ALL_USED
FROM DBA_DATA_FILES
GROUP BY TABLESPACE_NAME) A,
( SELECT TABLESPACE_NAME, SUM (BYTES / (1024 * 1024 * 1024)) USED
FROM DBA_EXTENTS
GROUP BY TABLESPACE_NAME) U,
( SELECT TABLESPACE_NAME, SUM (BYTES / (1024 * 1024 * 1024)) FREE
FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F
WHERE A.TABLESPACE_NAME = F.TABLESPACE_NAME(+)
AND A.TABLESPACE_NAME = U.TABLESPACE_NAME(+)
ORDER BY (A.ALL_TOTAL - A.ALL_USED) / A.ALL_TOTAL,F. FREE / A.TOTAL ASC ;
檢視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
0
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="" sharedpool="">
# 查詢還保留在 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 回滾段名,s.sid SID,s.serial# Serial,
s.username 使用者名稱,s.machine 機器名,
t.start_time 開始時間,t.status 狀態,
t.used_ublk 撤消塊,USED_UREC 撤消記錄,
t.cr_get 一致性取,t.cr_change 一致性變化,
t.log_io "邏輯I/O",t.phy_io "物理I/O",
t.noundo NoUndo,g.extents Extents,substr(s.program, 1, 50) 操作程式
FROM v$session s, v$transaction t, v$rollname r,v$rollstat g
WHERE t.addr = s.taddr
AND t.xidusn = r.usn
AND r.usn = g.usn
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的等待事件
col event for a30
包括空閒等待事件
select * from (select sid,event,p1,p2,p3,p1text,WAIT_TIME,SECONDS_IN_WAIT from v$session_wait where
event not like 'SQL%' and event not like 'rdbms%' order by wait_time desc) where rownum <=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;
# 檢視鎖住物件的會話資訊，作業系統程式資訊
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_hashvalue=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
TOP SQL
# 邏輯讀 TOP 10
select *
from (select sqt.logicr logical_Reads,
sqt.exec Executions,
decode(sqt.exec, 0, to_number(null), (sqt.logicr / sqt.exec)) Reads_per_Exec ,
(100 * sqt.logicr) /
(SELECT sum(e.VALUE) - sum(b.value)
FROM DBA_HIST_SYSSTAT b, DBA_HIST_SYSSTAT e
WHERE B.SNAP_ID =7634
AND E.SNAP_ID =7637
AND B.DBID = 3629726729
AND E.DBID = 3629726729
AND B.INSTANCE_NUMBER = 1
AND E.INSTANCE_NUMBER = 1
and e.STAT_NAME = 'session logical reads'
and b.stat_name = 'session logical reads') Total_rate,
nvl((sqt.cput / 1000000), to_number(null)) CPU_Time_s,
nvl((sqt.elap / 1000000), to_number(null)) Elapsed_Time_s,
sqt.sql_id,
decode(sqt.module, null, null, 'Module: ' || sqt.module) SQL_Module,
nvl(st.sql_text, to_clob('** SQL Text Not Available **')) SQL_Text
from (select sql_id,
max(module) module,
sum(buffer_gets_delta) logicr,
sum(executions_delta) exec,
sum(cpu_time_delta) cput,
sum(elapsed_time_delta) elap
from dba_hist_sqlstat
where dbid = 3629726729
and instance_number = 1
and 7634 < snap_id
and snap_id <= 7637
group by sql_id) sqt,
dba_hist_sqltext st
where st.sql_id(+) = sqt.sql_id
and st.dbid(+) = 3629726729
and (SELECT sum(e.VALUE) - sum(b.value)
FROM DBA_HIST_SYSSTAT b, DBA_HIST_SYSSTAT e
WHERE B.SNAP_ID =7634
AND E.SNAP_ID =7637
AND B.DBID = 3629726729
AND E.DBID = 3629726729
AND B.INSTANCE_NUMBER = 1
AND E.INSTANCE_NUMBER = 1
and e.STAT_NAME = 'session logical reads'
and b.stat_name = 'session logical reads') > 0
order by nvl(sqt.logicr, -1) desc, sqt.sql_id)
where rownum < 65and(rownum <= 10
or Total_rate > 1);
# 物理讀 TOP 10
select *
from (select sqt.dskr Physical_Reads,
sqt.exec Executions,
decode(sqt.exec, 0, to_number(null), (sqt.dskr / sqt.exec)) Reads_per_Exec ,
(100 * sqt.dskr) /
(SELECT sum(e.VALUE) - sum(b.value)
FROM DBA_HIST_SYSSTAT b, DBA_HIST_SYSSTAT e
WHERE B.SNAP_ID = $P{p_beg_snap}
AND E.SNAP_ID = $P{p_end_snap}
AND B.DBID = 1273705906
AND E.DBID = 1273705906
AND B.INSTANCE_NUMBER = 1
AND E.INSTANCE_NUMBER = 1
and e.STAT_NAME = 'physical reads'
and b.stat_name = 'physical reads') Total_rate,
nvl((sqt.cput / 1000000), to_number(null)) CPU_Time_s,
nvl((sqt.elap / 1000000), to_number(null)) Elapsed_Time_s,
sqt.sql_id,
decode(sqt.module, null, null, 'Module: ' || sqt.module) SQL_Module,
nvl(st.sql_text, to_clob('** SQL Text Not Available **')) SQL_Text
from (select sql_id,
max(module) module,
sum(disk_reads_delta) dskr,
sum(executions_delta) exec,
sum(cpu_time_delta) cput,
sum(elapsed_time_delta) elap
from dba_hist_sqlstat
where dbid = 1273705906
and instance_number = 1
and $P{p_beg_snap} < snap_id
and snap_id <= $P{p_end_snap}
group by sql_id) sqt,
dba_hist_sqltext st
where st.sql_id(+) = sqt.sql_id
and st.dbid(+) = 1273705906
and (SELECT sum(e.VALUE) - sum(b.value)
FROM DBA_HIST_SYSSTAT b, DBA_HIST_SYSSTAT e
WHERE B.SNAP_ID = $P{p_beg_snap}
AND E.SNAP_ID = $P{p_end_snap}
AND B.DBID = 1273705906
AND E.DBID = 1273705906
AND B.INSTANCE_NUMBER = 1
AND E.INSTANCE_NUMBER = 1
and e.STAT_NAME = 'physical reads'
and b.stat_name = 'physical reads') > 0
order by nvl(sqt.dskr, -1) desc, sqt.sql_id)
where rownum < 65and(rownum <= 10
or Total_rate > 1);
# 消耗CPU TOP 10
select *
from (select nvl((sqt.elap / 1000000), to_number(null)) Elapsed_Time_s,
nvl((sqt.cput / 1000000), to_number(null)) CPU_Time_s,
sqt.exec Executions,
decode(sqt.exec,
0,
to_number(null),
(sqt.elap / sqt.exec / 1000000)) Elap_per_Exec_s,
(100 *
(sqt.elap / (SELECT sum(e.VALUE) - sum(b.value)
FROM DBA_HIST_SYSSTAT b, DBA_HIST_SYSSTAT e
WHERE B.SNAP_ID = 7396
AND E.SNAP_ID = 7399
AND B.DBID = 1273705906
AND E.DBID = 1273705906
AND B.INSTANCE_NUMBER = 1
AND E.INSTANCE_NUMBER = 1
and e.STAT_NAME = 'DB time'
and b.stat_name = 'DB time')))/1000 Total_DB_Time_rate,
sqt.sql_id,
to_clob(decode(sqt.module,
null,
null,
'Module: ' || sqt.module)) SQL_Module,
nvl(st.sql_text, to_clob(' ** SQL Text Not Available ** ')) SQL_Text
from (select sql_id,
max(module) module,
sum(elapsed_time_delta) elap,
sum(cpu_time_delta) cput,
sum(executions_delta) exec
from dba_hist_sqlstat
where dbid = 65972167
and instance_number = 1
and 7396 < snap_id
and snap_id <= 7399
group by sql_id) sqt,
dba_hist_sqltext st
where st.sql_id(+) = sqt.sql_id
and st.dbid(+) = 1273705906
order by nvl(sqt.cput, -1) desc, sqt.sql_id)
where rownum < 65
and (rownum <= 10 or Total_DB_Time_rate > 1);
# 執行時間 TOP 10
select *
from (select nvl((sqt.elap / 1000000), to_number(null)) Elapsed_Time_s,
nvl((sqt.cput / 1000000), to_number(null)) CPU_Time_s,
sqt.exec Executions,
decode(sqt.exec,
0,
to_number(null),
(sqt.elap / sqt.exec / 1000000)) Elap_per_Exec_s,
(100 *
(sqt.elap / (SELECT sum(e.VALUE) - sum(b.value)
FROM DBA_HIST_SYSSTAT b, DBA_HIST_SYSSTAT e
WHERE B.SNAP_ID = $P{p_beg_snap}
AND E.SNAP_ID = $P{p_end_snap}
AND B.DBID = 1273705906
AND E.DBID = 1273705906
AND B.INSTANCE_NUMBER = 1
AND E.INSTANCE_NUMBER = 1
and e.STAT_NAME = 'DB time'
and b.stat_name = 'DB time')))/1000 Total_DB_Time_rate,
sqt.sql_id,
to_clob(decode(sqt.module,
null,
null,
'Module: ' || sqt.module)) SQL_Module,
nvl(st.sql_text, to_clob(' ** SQL Text Not Available ** ')) SQL_Text
from (select sql_id,
max(module) module,
sum(elapsed_time_delta) elap,
sum(cpu_time_delta) cput,
sum(executions_delta) exec
from dba_hist_sqlstat
where dbid = 1273705906
and instance_number = 1
and $P{p_beg_snap} < snap_id
and snap_id <= $P{p_end_snap}
group by sql_id) sqt,
dba_hist_sqltext st
where st.sql_id(+) = sqt.sql_id
and st.dbid(+) = 1273705906
order by nvl(sqt.elap, -1) desc, sqt.sql_id)
where rownum < 65
and (rownum <= 10 or Total_DB_Time_rate > 1);
查詢需要使用繫結變數的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;
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執行計劃
# 查詢你的session資訊
SELECT SID, OSUSER, USERNAME, MACHINE, PROCESS
FROM V$SESSION WHERE audsid = userenv('SESSIONID');
# 當machine已知的情況下查詢session
SELECT SID, OSUSER, USERNAME, MACHINE, TERMINALFROM 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)等
select a.sid,spid,status,substr(a.program,1,40) prog,a.terminal,osuser,c.value/60/100 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 desc
注意: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;
#檢視等待事件的詳細情況
create or replace procedure WaitHistogram(pFilter varchar2)
is
vTotalWaitCount integer;
cursor rec_cur is
select rpad(substr(event,1,40),42) event,
lpad(to_char(wait_time_MILLI,999999999.99),13) wtm,
lpad(to_char(wait_count,9999999999.99),13) wct,
100*(sum(wait_count) over(order by event,wait_time_milli)) pct_rt
from v$event_histogram where event=pFilter
order by 1,2;
c_event varchar2(100);
c_wtm varchar2(100);
c_wct varchar2(100);
c_pct_rt number(20,2);
begin
select sum(wait_count) into vTotalWaitCount from v$event_histogram where event=pFilter;
dbms_output.enable(800000);
dbms_output.put_line(rpad('event',45)||'Wait time Wait count Pct_rt');
open rec_cur;
fetch rec_cur into c_event,c_wtm,c_wct,c_pct_rt;
while rec_cur%found loop
dbms_output.put_line(c_event||' '||c_wtm||' '||c_wct||' '||to_char((c_pct_rt/vTotalWaitCount),0999.99));
fetch rec_cur into c_event,c_wtm,c_wct,c_pct_rt;
end loop;
close rec_cur;
end ;
/
exec WaitHistogram(pfilter=>'gc buffer busy');
# 檢視錶的統計資訊
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#