关闭

ORACLE DBA SCRIPTS

623人阅读 评论(0) 收藏 举报
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; 

--表空间褂寐?/font>

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),2SPACE,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),2SPACE,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),2SPACE,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),2SPACE,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*1024AS "SIZE(M)" FROM USER_SEGMENTS 
WHERE SEGMENT_NAME=UPPER('&TABLE_NAME');


--查看索引的大小
SELECT SUM(BYTES)/(1024*1024AS "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, 1NULL2'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 &lt; 15%, otherwise Add share_pool_size*/ 


**********************************************使用大量临时段的SQL***************************/
--用于查看哪些实例的哪些操作使用了大量的临时段 

SELECT to_number(decode(SID, 65535NULL, 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 DESCWHERE 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 DESCWHERE 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;


--全表扫描
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


/********************************************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.FILEAND 
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(1DESC


--当前会话所执行的语句
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, 
16) "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/10242) 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), 
01 ,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),
01,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 OWNER = 'TEST_BAK' 
AND BLOCKS > 128
ORDER BY 
10 DESC1 ASC2 ASC;

 
0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:216402次
    • 积分:3520
    • 等级:
    • 排名:第9823名
    • 原创:140篇
    • 转载:8篇
    • 译文:0篇
    • 评论:14条
    JAVA JSP