Oracle DBA Scripts

/****************************表空間*****************************************/
--表空間狀態

SELECT TABLESPACE_NAME,INITIAL_EXTENT,NEXT_EXTENT,MIN_EXTENTS,
MAX_EXTENTS,PCT_INCREASE,MIN_EXTLEN,STATUS,
CONTENTS,LOGGING,
EXTENT_MANAGEMENT, -- Columns not available in v8.0.x
ALLOCATION_TYPE, -- Remove these columns if running
PLUGGED_IN, -- against a v8.0.x database
SEGMENT_SPACE_MANAGEMENT --use only in v9.2.x or later
FROM DBA_TABLESPACES
ORDER BY TABLESPACE_NAME;

--表空間空閒比率

SELECT D.TABLESPACE_NAME,SPACE "SUM_SPACE(M)",BLOCKS SUM_BLOCKS,SPACE-NVL(FREE_SPACE,0) "USED_SPACE(M)",
ROUND((1-NVL(FREE_SPACE,0)/SPACE)*100,2) "USED_RATE(%)",FREE_SPACE "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 --if have tempfile
SELECT D.TABLESPACE_NAME,SPACE "SUM_SPACE(M)",BLOCKS SUM_BLOCKS,
USED_SPACE "USED_SPACE(M)",ROUND(NVL(USED_SPACE,0)/SPACE*100,2) "USED_RATE(%)",
NVL(FREE_SPACE,0) "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(+)

--表空間使用率(包含檔案自動擴充套件屬性)

SELECT D.TABLESPACE_NAME,FILE_NAME "FILE_NAME",SPACE "SUM_SPACE(M)",BLOCKS SUM_BLOCKS,SPACE-NVL(FREE_SPACE,0) "USED_SPACE(M)",
ROUND((1-NVL(FREE_SPACE,0)/SPACE)*100,2) "USED_RATE(%)",FREE_SPACE "FREE_SPACE(M)",AUTOEXTENSIBLE
FROM
(SELECT FILE_ID,FILE_NAME,TABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024),2) SPACE,SUM(BLOCKS) BLOCKS
FROM DBA_DATA_FILES
GROUP BY TABLESPACE_NAME,FILE_ID,FILE_NAME) D,
(SELECT FILE_ID,TABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024),2) FREE_SPACE
FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME,FILE_ID) E,
(SELECT FILE_ID,AUTOEXTENSIBLE FROM DBA_DATA_FILES) F
WHERE D.TABLESPACE_NAME = E.TABLESPACE_NAME(+) AND D.FILE_ID = E.FILE_ID(+) AND D.FILE_ID = F.FILE_ID(+)
UNION ALL --if have tempfile
SELECT D.TABLESPACE_NAME,FILE_NAME "FILE_NAME",SPACE "SUM_SPACE(M)",BLOCKS SUM_BLOCKS,
USED_SPACE "USED_SPACE(M)",ROUND(NVL(USED_SPACE,0)/SPACE*100,2) "USED_RATE(%)",
NVL(FREE_SPACE,0) "FREE_SPACE(M)",AUTOEXTENSIBLE
FROM
(SELECT FILE_ID,FILE_NAME,TABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024),2) SPACE,SUM(BLOCKS) BLOCKS
FROM DBA_TEMP_FILES
GROUP BY TABLESPACE_NAME,FILE_ID,FILE_NAME) D,
(SELECT FILE_ID,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,FILE_ID) E,
(SELECT FILE_ID,AUTOEXTENSIBLE FROM DBA_TEMP_FILES) F
WHERE D.TABLESPACE_NAME = E.TABLESPACE_NAME(+) AND D.FILE_ID = E.FILE_ID(+) AND D.FILE_ID = F.FILE_ID(+)
ORDER BY TABLESPACE_NAME,FILE_NAME

--單獨檢視資料檔案自動擴充套件狀態

select file_id,file_name,tablespace_name,autoextensible from dba_data_files
union all
select file_id,file_name,tablespace_name,autoextensible from dba_temp_files
order by file_id

 

 

/**************************回滾段*******************************************/
--檢視回滾段名稱及大小

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 ;

SELECT * FROM V$SESSTAT S,V$STATNAME N WHERE S.STATISTIC#=N.STATISTIC# AND N.NAME LIKE '%undo%';

/**************************表、索引SIZE***************************************/
--檢視某表的大小

SELECT SUM(BYTES)/(1024*1024) AS "SIZE(M)" FROM USER_SEGMENTS
WHERE SEGMENT_NAME=UPPER('&TABLE_NAME');

--檢視索引的大小
SELECT SUM(BYTES)/(1024*1024) AS "SIZE(M)" FROM USER_SEGMENTS
WHERE SEGMENT_NAME=UPPER('&INDEX_NAME');

/*****************************鎖定物件、鎖等待*********************************/
--檢視session訪問物件

SQL> SELECT SID FROM V$MYSTAT WHERE ROWNUM=1;

SID
----------
154

SQL> select * from v$access where sid=154;

方法一:
SELECT A.OWNER,
A.OBJECT_NAME,
B.XIDUSN,
B.XIDSLOT,
B.XIDSQN,
B.ORACLE_USERNAME,
B.OS_USER_NAME,
B.PROCESS,
B.LOCKED_MODE,
C.MACHINE,
C.STATUS,
C.SERVER,
C.SID,
C.SERIAL#,
C.PROGRAM
FROM ALL_OBJECTS A,
V$LOCKED_OBJECT B,
V$SESSION C
WHERE ( A.OBJECT_ID = B.OBJECT_ID )
AND (B.SESSION_ID = C.SID )
ORDER BY 1,2;

方法二:
SELECT /*+ RULE */ LS.OSUSER OS_USER_NAME, LS.USERNAME USER_NAME,
DECODE(LS.TYPE, 'RW', 'ROW WAIT ENQUEUE LOCK', 'TM', 'DML ENQUEUE LOCK', 'TX',
'TRANSACTION ENQUEUE LOCK', 'UL', 'USER SUPPLIED LOCK') LOCK_TYPE,
O.OBJECT_NAME OBJECT, DECODE(LS.LMODE, 1, NULL, 2, 'ROW SHARE', 3,
'ROW EXCLUSIVE', 4, 'SHARE', 5, 'SHARE ROW EXCLUSIVE', 6, 'EXCLUSIVE', NULL)
LOCK_MODE, O.OWNER, LS.SID, LS.SERIAL# SERIAL_NUM, LS.ID1, LS.ID2
FROM SYS.DBA_OBJECTS O, ( SELECT S.OSUSER, S.USERNAME, L.TYPE,
L.LMODE, S.SID, S.SERIAL#, L.ID1, L.ID2 FROM V$SESSION S,
V$LOCK L WHERE S.SID = L.SID ) LS WHERE O.OBJECT_ID = LS.ID1 AND O.OWNER
<> 'SYS' ORDER BY O.OWNER, O.OBJECT_NAME

--鎖與等待

SELECT /*+ rule */ 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

--鎖定事務
SELECT S.SID, S.SERIAL#, P.SPID, S.USERNAME, S.PROGRAM,
T.XIDUSN, T.USED_UBLK, T.USED_UREC, SA.SQL_TEXT FROM
V$PROCESS P,V$SESSION S, V$SQLAREA SA, V$TRANSACTION T
WHERE S.PADDR=P.ADDR
AND S.TADDR=T.ADDR
AND S.SQL_ADDRESS=SA.ADDRESS(+)
AND S.SQL_HASH_VALUE=SA.HASH_VALUE(+)
ORDER BY S.SID

/****************************************命中率*******************************/
--DataBuffer

SELECT A.VALUE + B.VALUE LOGICAL_READS,
C.VALUE PHYS_READS,
ROUND(100*(1-C.VALUE/(A.VALUE+B.VALUE)),4) HIT_RATIO
FROM V$SYSSTAT A,V$SYSSTAT B,V$SYSSTAT C
WHERE A.NAME='db block gets'
AND B.NAME='consistent gets'
AND C.NAME='physical reads'

--庫緩衝

SELECT SUM(PINS) TOTAL_PINS,SUM(RELOADS) TOTAL_RELOADS,
SUM(RELOADS)/SUM(PINS)*100 LIBCACHE_RELOAD_RATIO
FROM V$LIBRARYCACHE

--資料字典

SELECT SUM(GETMISSES)/SUM(GETS) FROM V$ROWCACHE;
/*It should be < 15%, otherwise Add share_pool_size*/

********************************使用大量臨時段的SQL***************************/
--用於檢視哪些例項的哪些操作使用了大量的臨時段

SELECT to_number(decode(SID, 65535, NULL, SID)) sid,
operation_type OPERATION,trunc(EXPECTED_SIZE/1024) ESIZE,
trunc(ACTUAL_MEM_USED/1024) MEM, trunc(MAX_MEM_USED/1024) "MAX MEM",
NUMBER_PASSES PASS, trunc(TEMPSEG_SIZE/1024) TSIZE
FROM V$SQL_WORKAREA_ACTIVE
ORDER BY 1,2;

*****************************使用大量記憶體分配的物件***************************/
--共享池中哪個物件引起了大的記憶體分配

SELECT * FROM X$KSMLRU WHERE KSMLRSIZ > 0;

/*********************SQL***********************************************/
--查詢當前執行SQL

SELECT SQL_TEXT FROM V$SQLTEXT
WHERE HASH_VALUE =
( SELECT SQL_HASH_VALUE FROM V$SESSION
WHERE SID = &SID)
ORDER BY PIECE

--檢視低效率的SQL語句

SELECT EXECUTIONS , DISK_READS, BUFFER_GETS,
ROUND((BUFFER_GETS-DISK_READS)/BUFFER_GETS,2) Hit_radio,
ROUND(DISK_READS/EXECUTIONS,2) Reads_per_run,
SQL_TEXT
FROM V$SQLAREA
WHERE EXECUTIONS>0
AND BUFFER_GETS > 0
AND (BUFFER_GETS-DISK_READS)/BUFFER_GETS < 0.8
ORDER BY 4 DESC;

--SQL(BUFFER_GETS)
SELECT * FROM (SELECT BUFFER_GETS, SQL_TEXT
FROM V$SQLAREA
WHERE BUFFER_GETS > 500000
ORDER BY BUFFER_GETS DESC) WHERE ROWNUM<=30;


--SQL(DISK_READS)
SELECT SQL_TEXT,DISK_READS FROM
(SELECT SQL_TEXT,DISK_READS FROM V$SQLAREA ORDER BY DISK_READS DESC)
WHERE ROWNUM<21;


--SQL(MULTI_VERSION)
SELECT SUBSTR(SQL_TEXT,1,80) "SQL", COUNT(*), SUM(EXECUTIONS) "TOTEXECS"
FROM V$SQLAREA
WHERE EXECUTIONS < 5
GROUP BY SUBSTR(SQL_TEXT,1,80)
HAVING COUNT(*) > 30
ORDER BY 2;

---查詢有熱塊查詢的SQL語句

SELECT HASH_VALUE
FROM V$SQLTEXT A,
(SELECT DISTINCT A.OWNER,A.SEGMENT_NAME,A.SEGMENT_TYPE FROM
DBA_EXTENTS A,
(SELECT DBARFIL,DBABLK
FROM (SELECT DBARFIL,DBABLK
FROM X$BH ORDER BY TCH DESC) WHERE ROWNUM < 11) B
WHERE A.RELATIVE_FNO = B.DBARFIL
AND A.BLOCK_ID <= B.DBABLK AND A.BLOCK_ID + A.BLOCKS > B.DBABLK) B
WHERE A.SQL_TEXT LIKE '%'||B.SEGMENT_NAME||'%' AND B.SEGMENT_TYPE = 'TABLE'
ORDER BY A.HASH_VALUE,A.ADDRESS,A.PIECE;

--可以根據v$latch_child.addr關聯到對應的x$bh.hladdr（這是buffer header中記錄的當前buffer所處的latch地址）
--透過x$bh可以獲得塊的檔案編號和block編號
select bh.DBARFIL,bh.DBABLK
from x$bh bh
where bh.HLADDR in
(select addr from
(select addr
from v$latch_children
order by sleeps desc)
where rownum <11);
/

--也可以檢視v$latch_children的sleeps欄位，如果sleeps值很高，
--則說明有hot block存在，下面的sql可以獲得latch的ADDR
select CHILD# "cCHILD"
, ADDR "sADDR"
, GETS "sGETS"
, MISSES "sMISSES"
, SLEEPS "sSLEEPS"
from v$latch_children
where name = 'cache buffers chains'
order by 5, 1, 2, 3;

--透過關聯v$latch_children/x$bh/dba_extents三張檢視，定位hot block所在的物件
column segment_name format a35
select /*+ RULE */
e.owner ||'.'|| e.segment_name segment_name,
e.extent_id extent#,
x.dbablk - e.block_id + 1 block#,
x.tch,
l.child#
from
sys.v$latch_children l,
sys.x$bh x,
sys.dba_extents e
where
x.hladdr = '&ADDR' and --latch address
e.file_id = x.file# and
x.hladdr = l.addr and
x.dbablk between e.block_id and e.block_id + e.blocks -1
order by x.tch desc ;

--全表掃描
SELECT OPNAME,TARGET,B.NUM_ROWS,B.TABLESPACE_NAME,COUNT(TARGET) FROM V$SESSION_LONGOPS A,ALL_ALL_TABLES B
WHERE A.TARGET=B.OWNER||'.'||B.TABLE_NAME
HAVING COUNT(TARGET)>10 GROUP BY OPNAME,TARGET,B.NUM_ROWS,B.TABLESPACE_NAME

--長時間執行的SQL

SELECT sql_text "SQL", executions "執行次數", buffer_gets / decode(executions, 0, 1, executions) / 4000 "響應時間"
FROM v$sql
WHERE buffer_gets / decode(executions, 0,1, executions) / 4000 > 10
AND executions > 0;

/******************************TRACE檔案**************************************/
--查詢TRACE檔案

SELECT P1.VALUE||''||P2.VALUE||'_ORA_'||P.SPID FILENAME
FROM
V$PROCESS P,
V$SESSION S,
V$PARAMETER P1,
V$PARAMETER P2
WHERE P1.NAME = 'user_dump_dest'
AND P2.NAME = 'db_name'
AND P.ADDR = S.PADDR
AND S.AUDSID = USERENV ('SESSIONID');

/******************ORACLE SID、作業系統程式*************************************/
--根據ORACLE SID查詢作業系統程式

SELECT SPID FROM V$PROCESS
WHERE ADDR IN ( SELECT PADDR FROM V$SESSION WHERE SID=&SID

--根據作業系統程式查詢ORACLE SID

SELECT SID FROM V$SESSION
WHERE PADDR IN ( SELECT ADDR FROM V$PROCESS WHERE SPID=&PID)

/*************************查詢等待********************************************/
--查詢等待SESSION

SELECT * FROM V$SESSION_WAIT
WHERE EVENT NOT LIKE 'RDBMS%'
AND EVENT NOT LIKE 'SQL*N%'
AND EVENT NOT LIKE '%TIMER';

--找出引起等待事件的SQL語句

SELECT SQL_TEXT
FROM V$SQLAREA A,V$SESSION B,V$SESSION_WAIT C
WHERE A.ADDRESS=B.SQL_ADDRESS AND B.SID=C.SID AND C.EVENT=$EVENT;

--找出每個檔案上的等待事件

SELECT DF.NAME,KF.COUNT FROM V$DATAFILE DF,X$KCBFWAIT KF WHERE (KF.INDX+1)=DF.FILE#;

--查詢HOT BLOCK

SELECT /*+ ORDERED */
E.OWNER ||'.'|| E.SEGMENT_NAME SEGMENT_NAME,
E.EXTENT_ID EXTENT#,
X.DBABLK - E.BLOCK_ID + 1 BLOCK#,
X.TCH,
L.CHILD#
FROM
SYS.V$LATCH_CHILDREN L,
SYS.X$BH X,
SYS.DBA_EXTENTS E
WHERE
L.NAME = 'cache buffers chains' AND
L.SLEEPS > &SLEEP_COUNT AND
X.HLADDR = L.ADDR AND
E.FILE_ID = X.FILE# AND
X.DBABLK BETWEEN E.BLOCK_ID AND E.BLOCK_ID + E.BLOCKS - 1;

--檢視LATCH FREE等待的LATCH名稱
SELECT V$SESSION.SID,NAME LATCH_NAME FROM
V$SESSION,V$LATCH,V$SESSION_WAIT S WHERE V$SESSION.SID=S.SID AND S.EVENT=LATCH FREE AND S.P2=V$LATCH.LATCH#;

--檢視alert的SQL

/*注意：該方法需要使用者具有create any directory許可權. 而create any directory是一個具有極大潛在安全隱患的許可權, 請小心使用.*/

column dir format a50 new_value dir
column fname for a20 new_value fname

select a.value dir , 'alert_' || b.instance_name || '.log' fname
from v$parameter a, v$instance b
where a.name = 'background_dump_dest';

create or replace directory bdump as '&dir';

create table alert_log ( text varchar2(400) )
organization external (
type oracle_loader
default directory BDUMP
access parameters (
records delimited by newline
nobadfile
nodiscardfile
nologfile)
location('&fname')
)
reject limit unlimited
/

SQL> select * from alert_log where text like 'ORA-%';

--檢視日誌切換間隔

SELECT B.RECID,B.FIRST_TIME,A.FIRST_TIME,ROUND((A.FIRST_TIME-B.FIRST_TIME)*24*60,2) MINUTES
FROM V$LOG_HISTORY A,V$LOG_HISTORY B
WHERE A.RECID=B.RECID +1
ORDER BY A.FIRST_TIME DESC

--SHARED POOL空閒率
SELECT TO_NUMBER(V$PARAMETER.VALUE) "TOTAL SHARED POOL", V$SGASTAT.BYTES "FREE",
ROUND((V$SGASTAT.BYTES/V$PARAMETER.VALUE)*100,2)||'%' "PERCENT FREE"
FROM V$SGASTAT, V$PARAMETER
WHERE V$SGASTAT.NAME= 'free memory'
AND V$PARAMETER.NAME = 'shared_pool_size'
AND V$SGASTAT.POOL='shared pool'

--SGA空閒率
SELECT TOTAL "TOTAL SGA",FREE "FREE",ROUND(FREE/TOTAL*100,2)||'%' "PERCENT FREE" FROM
(SELECT SUM(BYTES) FREE FROM V$SGASTAT WHERE V$SGASTAT.NAME= 'free memory') A,
(SELECT SUM(VALUE) TOTAL FROM V$SGA) B

--BUFFER命中率
SELECT SUM(DECODE(NAME, 'db block gets', VALUE, 0)) + SUM(DECODE(NAME, 'consistent gets', VALUE, 0)) "LOGIC READS",
SUM(DECODE(NAME, 'physical reads', VALUE, 0)) "PHISICAL READS",
1 - SUM(DECODE(NAME, 'physical reads', VALUE, 0))/
(SUM(DECODE(NAME, 'db block gets', VALUE, 0)) + SUM(DECODE(NAME, 'consistent gets', VALUE, 0))) "BUFFER HIT RATIO"
FROM V$SYSSTAT

--BUFFER命中率
SELECT CONSISTENT_GETS+DB_BLOCK_GETS "LOGIC READS",PHYSICAL_READS "PHISICAL READS",
1 - PHYSICAL_READS/(CONSISTENT_GETS+DB_BLOCK_GETS) "BUFFER HIT RATIO"
FROM V$BUFFER_POOL_STATISTICS;

--FLUSH BUFFER_CACHE
ALTER SYSTEM SET EVENTS = 'IMMEDIATE TRACE NAME FLUSH_CACHE'; --9I/10G
ALTER SYSTEM FLUSH BUFFER_CACHE; -- 10G

--V$BH
SELECT OWNER,OBJECT_NAME,COUNT(1),(COUNT(1)/(SELECT COUNT(*) FROM V$BH)) *100
FROM DBA_OBJECTS O,V$BH BH
WHERE O.OBJECT_ID = BH.OBJD
AND O.OWNER NOT IN ('SYS','SYSTEM')
GROUP BY OWNER,OBJECT_NAME
ORDER BY COUNT(1) DESC

--當前會話所執行的語句
SELECT A.SID||'.'||A.SERIAL#, A.USERNAME, A.TERMINAL, A.PROGRAM, S.SQL_TEXT
FROM V$SESSION A, V$SQLAREA S
WHERE A.SQL_ADDRESS = S.ADDRESS(+)
AND A.SQL_HASH_VALUE = S.HASH_VALUE(+)
ORDER BY A.USERNAME, A.SID

--根據SQL地址查詢執行計劃
SELECT LPAD(' ', 2*(LEVEL-1))||OPERATION "OPERATION",OPTIONS "OPTIONS",
DECODE(TO_CHAR(ID), '0', 'COST='||NVL(TO_CHAR(POSITION), 'N/A'), OBJECT_NAME) "OBJECT NAME",
SUBSTR(OPTIMIZER, 1, 6) "OPTIMIZER"
FROM V$SQL_PLAN A
START WITH ADDRESS = 'XXXXXXXXXX'
AND ID = 0
CONNECT BY PRIOR ID = A.PARENT_ID
AND PRIOR A.ADDRESS = A.ADDRESS
AND PRIOR A.HASH_VALUE = A.HASH_VALUE

--庫快取命中率
SELECT SUM(PINS) "HITS",
SUM(RELOADS) "MISSES",
SUM(PINS)/(SUM(PINS)+SUM(RELOADS)) "HITS RATIO"
FROM V$LIBRARYCACHE

--庫快取記憶體分配
SELECT * FROM V$LIBRARY_CACHE_MEMORY

--PGA狀態
SELECT * FROM V$PGASTA
V$PGA_TARGET_ADVICE

--PGA工作區的使用情況
SELECT * FROM V$SQL_WORKAREA

--PGA工作區的排序情況
SELECT * FROM V$SYSSTAT
WHERE NAME LIKE '%sort%'

--SHARED_POOL SPARE FREE MEMORY
SELECT
AVG(V.VALUE) SHARED_POOL_SIZE,
GREATEST(AVG(S.KSMSSLEN) - SUM(P.KSMCHSIZ), 0) SPARE_FREE,
TO_CHAR(
100 * GREATEST(AVG(S.KSMSSLEN) - SUM(P.KSMCHSIZ), 0) / AVG(V.VALUE),
'99999'
) || '%' WASTAGE
FROM
SYS.X$KSMSS S,
SYS.X$KSMSP P,
SYS.V$PARAMETER V
WHERE
S.INST_ID = USERENV('INSTANCE') AND
P.INST_ID = USERENV('INSTANCE') AND
P.KSMCHCOM = 'free memory' AND
S.KSMSSNAM = 'free memory' AND
V.NAME = 'shared_pool_size'

--SHARED_POOL TRUNK
SELECT
KGHLURCR "RECURRENT_CHUNKS", --1
KGHLUTRN "TRANSIENT_CHUNKS", --3
KGHLUFSH "FLUSHED_CHUNKS", --1
KGHLUOPS "PINS AND_RELEASES", --20
KGHLUNFU "ORA-4031_ERRORS",
KGHLUNFS "LAST ERROR_SIZE"
FROM
SYS.X$KGHLU
WHERE
INST_ID = USERENV('INSTANCE')

--表空間的 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;

--檔案系統的 I/O 比例
SELECT SUBSTR(A.FILE#,1,2) "#", SUBSTR(A.NAME,1,30) "NAME",
A.STATUS, A.BYTES, B.PHYRDS, B.PHYBLKRD PBR, B.PHYWRTS, B.PHYBLKWRT PBW
FROM V$DATAFILE A, V$FILESTAT B
WHERE A.FILE# = B.FILE#;

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

--MAX USAGE OF UGA

SELECT sum(value) "Max MTS Memory Allocated"
FROM v$sesstat　ss, v$statname st
WHERE name = 'session uga memory max'
AND ss.statistic#=st.statistic#

 

一:
Top 10 most expensive SQL(Elapsed Time)...
----------------------------------------------------------
select rownum as rank, a.*
from (
select elapsed_Time,
executions,
buffer_gets,
disk_reads,
cpu_time
hash_value,
sql_text
from v$sqlarea
where elapsed_time > 20000
order by elapsed_time desc) a
where rownum < 11
二:
Top 10 most expensive SQL (CPU Time)...
------------------------------------------------------------
select rownum as rank, a.*
from (
select elapsed_Time,
executions,
buffer_gets,
disk_reads,
cpu_time
hash_value,
sql_text
from v$sqlarea
where cpu_time > 20000
order by cpu_time desc) a
where rownum < 11
三:
Top 10 most expensive SQL (Buffer Gets by Executions)...
--------------------------------------------------------
select rownum as rank, a.*
from (
select buffer_gets,
executions,
buffer_gets/ decode(executions,0,1, executions) gets_per_exec,
hash_value,
sql_text
from v$sqlarea
where buffer_gets > 50000
order by buffer_gets desc) a
where rownum < 11

四:
Top 10 most expensive SQL (Physical Reads by Executions)...
--------------------------------------------------------------------------
select rownum as rank, a.*
from (
select disk_reads,
executions,
disk_reads / decode(executions,0,1, executions) reads_per_exec,
hash_value,
sql_text
from v$sqlarea
where disk_reads > 10000
order by disk_reads desc) a
where rownum < 11
五:
Top 10 most expensive SQL (Rows Processed by Executions)...
---------------------------------------------------------
select rownum as rank, a.*
from (
select rows_processed,
executions,
rows_processed / decode(executions,0,1, executions) rows_per_exec,
hash_value,
sql_text
from v$sqlarea
where rows_processed > 10000
order by rows_processed desc) a
where rownum < 11
六:
Top 10 most expensive SQL (Buffer Gets vs Rows Processed)...
----------------------------------------------------------------
select rownum as rank, a.*
from (
select buffer_gets, lpad(rows_processed ||
decode(users_opening + users_executing, 0, ' ','*'),20) "rows_processed",
executions, loads,
(decode(rows_processed,0,1,1)) *
buffer_gets/ decode(rows_processed,0,1,
rows_processed) avg_cost,
sql_text
from v$sqlarea
where decode(rows_processed,0,1,1) * buffer_gets/ decode(rows_processed,0,1,rows_processed) > 10000
order by 5 desc) a
where rownum < 11

--查詢浪費空間的表

SELECT
OWNER,
SEGMENT_NAME TABLE_NAME,
SEGMENT_TYPE,
GREATEST(ROUND(100 * (NVL(HWM - AVG_USED_BLOCKS,0)/GREATEST(NVL(HWM,1),1) ), 2), 0) WASTE_PER,
ROUND(BYTES/1024, 2) TABLE_KB,
NUM_ROWS,
BLOCKS,
EMPTY_BLOCKS,
HWM HIGHWATER_MARK,
AVG_USED_BLOCKS,
CHAIN_PER,
EXTENTS,
MAX_EXTENTS,
ALLO_EXTENT_PER,
DECODE(GREATEST(MAX_FREE_SPACE - NEXT_EXTENT, 0), 0,'N','Y') CAN_EXTEND_SPACE,
NEXT_EXTENT, MAX_FREE_SPACE,
O_TABLESPACE_NAME TABLESPACE_NAME

FROM

(
SELECT
A.OWNER OWNER,
A.SEGMENT_NAME,
A.SEGMENT_TYPE,
A.BYTES,
B.NUM_ROWS,
A.BLOCKS BLOCKS,
B.EMPTY_BLOCKS EMPTY_BLOCKS,
A.BLOCKS - B.EMPTY_BLOCKS - 1 HWM,
DECODE( ROUND((B.AVG_ROW_LEN * NUM_ROWS * (1 + (PCT_FREE/100)))/C.BLOCKSIZE, 0),
0, 1 ,ROUND((B.AVG_ROW_LEN * NUM_ROWS * (1 + (PCT_FREE/100)))/C.BLOCKSIZE, 0)) + 2 AVG_USED_BLOCKS,
ROUND(100 * (NVL(B.CHAIN_CNT, 0)/GREATEST(NVL(B.NUM_ROWS, 1), 1)), 2) CHAIN_PER,
ROUND(100 * (A.EXTENTS/A.MAX_EXTENTS), 2) ALLO_EXTENT_PER,
A.EXTENTS EXTENTS,
A.MAX_EXTENTS MAX_EXTENTS,
B.NEXT_EXTENT NEXT_EXTENT,
B.TABLESPACE_NAME O_TABLESPACE_NAME
FROM
SYS.DBA_SEGMENTS A,
SYS.DBA_TABLES B,
SYS.TS$ C
WHERE A.OWNER =B.OWNER
AND SEGMENT_NAME = TABLE_NAME
AND SEGMENT_TYPE = 'TABLE'
AND B.TABLESPACE_NAME = C.NAME

UNION ALL

SELECT
A.OWNER OWNER,
SEGMENT_NAME || '.' || B.PARTITION_NAME,
SEGMENT_TYPE,
BYTES,
B.NUM_ROWS,
A.BLOCKS BLOCKS,
B.EMPTY_BLOCKS EMPTY_BLOCKS,
A.BLOCKS - B.EMPTY_BLOCKS - 1 HWM,
DECODE( ROUND((B.AVG_ROW_LEN * B.NUM_ROWS * (1 + (B.PCT_FREE/100)))/C.BLOCKSIZE, 0),
0, 1,ROUND((B.AVG_ROW_LEN * B.NUM_ROWS * (1 + (B.PCT_FREE/100)))/C.BLOCKSIZE, 0)) + 2 AVG_USED_BLOCKS,
ROUND(100 * (NVL(B.CHAIN_CNT,0)/GREATEST(NVL(B.NUM_ROWS, 1), 1)), 2) CHAIN_PER,
ROUND(100 * (A.EXTENTS/A.MAX_EXTENTS), 2) ALLO_EXTENT_PER,
A.EXTENTS EXTENTS,
A.MAX_EXTENTS MAX_EXTENTS,
B.NEXT_EXTENT,
B.TABLESPACE_NAME O_TABLESPACE_NAME
FROM
SYS.DBA_SEGMENTS A,
SYS.DBA_TAB_PARTITIONS B,
SYS.TS$ C,
SYS.DBA_TABLES D
WHERE
A.OWNER = B.TABLE_OWNER
AND SEGMENT_NAME = B.TABLE_NAME
AND SEGMENT_TYPE = 'TABLE PARTITION'
AND B.TABLESPACE_NAME = C.NAME
AND D.OWNER = B.TABLE_OWNER
AND D.TABLE_NAME = B.TABLE_NAME
AND A.PARTITION_NAME = B.PARTITION_NAME
),

(
SELECT
TABLESPACE_NAME F_TABLESPACE_NAME,
MAX(BYTES) MAX_FREE_SPACE
FROM
SYS.DBA_FREE_SPACE
GROUP BY
TABLESPACE_NAME
)

WHERE
F_TABLESPACE_NAME = O_TABLESPACE_NAME
AND GREATEST(ROUND(100 * (NVL(HWM - AVG_USED_BLOCKS, 0)/GREATEST(NVL(HWM, 1), 1) ), 2), 0) > 25
AND WNER = 'TEST_BAK'
AND BLOCKS > 128
ORDER BY
10 DESC, 1 ASC, 2 ASC;

--調節索引掃描/全表掃描 比例

optimizer_index_cost_adj:

select

a.average_wait c1,

b.average_wait c2,

a.total_waits /(a.total_waits + b.total_waits) c3,

b.total_waits /(a.total_waits + b.total_waits) c4,

(b.average_wait / a.average_wait)*100 c5

from

v$system_event a,

v$system_event b

where

a.event = 'db file scattered read'

and

b.event = 'db file sequential read';

select a.snap_id "Snap",
sum(a.time_waited_micro)/sum(a.total_waits)/10000 c1,
sum(b.time_waited_micro)/sum(b.total_waits)/10000 c2,
(sum(a.total_waits) / sum(a.total_waits + b.total_waits)) * 100 c3,
(sum(b.total_waits) / sum(a.total_waits + b.total_waits)) * 100 c4,
(sum(b.time_waited_micro)/sum(b.total_waits)) / (sum(a.time_waited_micro)/sum(a.total_waits)) * 100 c5
from
dba_hist_system_event a,
dba_hist_system_event b
where a.snap_id = b.snap_id
and a.event_name = 'db file scattered read'
and b.event_name = 'db file sequential read'
group by a.snap_id
order by 1
/

--execution plan

select *
from (select
rpad('|'||substr(lpad(' ',1*(depth-1))||operation||
decode(options, null,'',' '||options), 1, 32), 33, ' ')||'|'||
rpad(decode(id, 0, '----- '||to_char(hash_value)||' -----'
, substr(decode(substr(object_name, 1, 7), 'SYS_LE_', null, object_name)
||' ',1, 20)), 21, ' ')||'|'||
lpad(decode(cardinality,null,' ',
decode(sign(cardinality-1000), -1, cardinality||' ',
decode(sign(cardinality-1000000), -1, trunc(cardinality/1000)||'K',
decode(sign(cardinality-1000000000), -1, trunc(cardinality/1000000)||'M',
trunc(cardinality/1000000000)||'G')))), 7, ' ') || '|' ||
lpad(decode(bytes,null,' ',
decode(sign(bytes-1024), -1, bytes||' ',
decode(sign(bytes-1048576), -1, trunc(bytes/1024)||'K',
decode(sign(bytes-1073741824), -1, trunc(bytes/1048576)||'M',
trunc(bytes/1073741824)||'G')))), 6, ' ') || '|' ||
lpad(decode(cost,null,' ',
decode(sign(cost-10000000), -1, cost||' ',
decode(sign(cost-1000000000), -1, trunc(cost/1000000)||'M',
trunc(cost/1000000000)||'G'))), 8, ' ') || '|' as "Explain plan"
from v$sql_plan
where sql_id='bc2zk34bz0cmr')

--connotative parameters

select x.indx+1 num,ksppinm NAME,ksppity TYPE,ksppstvl VALUE,ksppstdf isdefault,
decode(bitand(ksppiflg/256,1),1,'TRUE','FALSE') isses_modifiable,
decode(bitand(ksppiflg/65536,3),1,'IMMEDIATE',2,'DEFERRED',3,'IMMEDIATE','FALSE') issys_modifiable,,
decode(bitand(ksppstvf,7),1,'MODIFIED',4,'SYSTEM_MOD','FALSE') ismodified,,
decode(bitand(ksppstvf,2),2,'TRUE','FALSE') isadjusted,,
ksppdesc description,, ksppstcmnt update_comment
from x$ksppi x, x$ksppcv y where (x.indx = y.indx)
/

select hash_value,
child_number,
lpad(' ', 2 * depth) || operation || ' ' || options ||
decode(id, 0, substr(optimizer, 1, 6) || ' Cost =' || to_char(cost)) ||
operation,
object_name object,
cost,
cardinality,
round(bytes / 1024) Kbytes
from v$sql_plan
where hash_value in
(select a.sql_hash_value
from v$session a, v$session_wait b
where a.sid = b.sid)
order by hash_value, child_number, id;

retrieve the current running query in the db.

declare
x number;
c binary_integer:=0;
begin
for x in
( select username||'('||sid||','||serial#||
') spid = ' || process ||
' program = ' || program username,
to_char(LOGON_TIME,' MON-DD HH24:MI') logon_time,
to_char(sysdate,' MON-DD HH24:MI') current_time,
sql_address, LAST_CALL_ET
from v$session
where status = 'ACTIVE'
and rawtohex(sql_address) <> '00'
and username is not null
and sid != (select distinct sid from v$mystat)
order by last_call_et )
loop
for y in ( select max(decode(piece,0,sql_text,null)) ||
max(decode(piece,1,sql_text,null)) ||
max(decode(piece,2,sql_text,null)) ||
max(decode(piece,3,sql_text,null))
sql_text
from v$sqltext_with_newlines
where address = x.sql_address
and piece < 4)
loop
if ( y.sql_text not like '%listener.get_cmd%' and
y.sql_text not like '%RAWTOHEX(SQL_ADDRESS)%')

then
if c=0
then
dbms_output.put_line(
'**************************************' );
dbms_output.put_line( x.username );
dbms_output.put_line( x.logon_time || ' ' ||
x.current_time||
' last et = ' ||
x.LAST_CALL_ET);
dbms_output.put_line(substr( y.sql_text, 1, 250 ) );
dbms_output.put_line(substr( y.sql_text, 251, 250 ) );
dbms_output.put_line(substr( y.sql_text, 501, 250 ) );
dbms_output.put_line(substr( y.sql_text, 751, 250 ) );
-- dbms_output.put_line(substr( y.sql_text, 1051, 250 ) );
else
dbms_output.put_line(
'------------------------------------------------' );
dbms_output.put_line( x.username );
dbms_output.put_line( x.logon_time || ' ' ||
x.current_time||
' last et = ' ||
x.LAST_CALL_ET);
dbms_output.put_line(substr( y.sql_text, 1, 250 ) );
dbms_output.put_line(substr( y.sql_text, 251, 250 ) );
dbms_output.put_line(substr( y.sql_text, 501, 250 ) );
dbms_output.put_line(substr( y.sql_text, 751, 250 ) );
-- dbms_output.put_line(substr( y.sql_text, 1051, 250 ) );
end if;
end if;
c:=c+1;
end loop;
end loop;
end;
/

--查詢熱塊所在物件
--可以根據v$latch_child.addr關聯到對應的x$bh.hladdr（這是buffer header中記錄的當前buffer所處的latch地址）
--透過x$bh可以獲得塊的檔案編號和block編號
select bh.DBARFIL,bh.DBABLK
from x$bh bh
where bh.HLADDR in
(select addr from
(select addr
from v$latch_children
order by sleeps desc)
where rownum

 

can get sid with this PL/SQL
-Check lock waiting
--Gerry Lin
set serveroutput on
declare
mes varchar2(2000);
tmp_id1 number(22);
tmp_id2 number(22);
lk_sid number(22);
tmp_mac varchar2(64);
fl boolean;
begin
fl:=false;
for c1 in (select sid,serial#,machine,lockwait
from v$session
where lockwait is not null) loop
fl:=true;
mes:='sid:'||to_char(c1.sid)||','||c1.machine||'is be locked by sid ';
select id1,id2 into tmp_id1,tmp_id2
from v$lock where sid=c1.sid and kaddr=c1.lockwait;
for c2 in (select sid lk_sid from v$lock where sid!=c1.sid and id1=tmp_id1 and id2=tmp_id2 and block='1') loop
select machine into tmp_mac from v$session where sid=c2.lk_sid;
dbms_output.put_line(mes||to_char(c2.lk_sid)||' '||tmp_mac);
end loop;
end loop;
if not fl then
dbms_output.put_line('There is not lock waiting');
end if;
end;
/

 

select sql_text from v$process p,v$session se,v$sql sq
where p.addr=se.paddr and se.sql_id=sq.sql_id and p.spid in(8878,4118)

SELECT * FROM V$SQLTEXT WHERE HASH_VALUE in
(SELECT SQL_HASH_VALUE FROM V$SESSION WHERE SID in (1249) )
ORDER BY HASH_VALUE,PIECE

 

select (select username from v$session where sid = a.sid) blocker
, a.sid ,'is block'
, (select username from v$session where sid = b.sid) blockee
, b.sid
from v$lock a , v$lock b
where a.block = 1 and b.request > 0 and a.id1 = b.id1 and a.id2 = b.id2

 

--Active transaction

Select V.hash_value,V.Sql_Text,s.sid,s.status
From V$transaction T, V$session S,V$sqlarea V
Where T.addr=S.taddr And T.ses_addr=s.saddr
And S.PREV_SQL_ADDR=V.address
And S.PREV_HASH_VALUE =V.hash_value;

Select V.hash_value,V.Sql_Text,s.sid,s.serial#
From V$transaction T, V$session S,V$sqlarea V
Where T.addr=S.taddr And T.ses_Addr=S.saddr
And S.SQL_ADDRESS=V.address
And S.SQL_HASH_VALUE =V.hash_value;

 

select rownum as rank, a.*
from (
select round((elapsed_Time+cpu_time)/executions,0),
executions,
buffer_gets,
disk_reads,
cpu_time,
hash_value,
sql_text
from v$sqlarea
where elapsed_time > 20000 and executions>0
order by (elapsed_Time+cpu_time)/decode(executions,0,1,executions) desc) a
where rownum <11>

 

SELECT * FROM V$SESSION_WAIT
WHERE EVENT NOT LIKE 'rdbms%'
AND EVENT NOT LIKE 'SQL*N%'
AND EVENT NOT LIKE '%timer'
order by event;

 

SQL> select spid from v$process where addr in (select paddr from v$session where
sid=(select sid from v$mystat where rownum=1));

SPID
------------------------
3452