ORACLE DBA SCRIPTS

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 ), 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 &lt; 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;


-- 全表扫描
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. 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  OWNER  =   ' TEST_BAK '  
AND  BLOCKS  >   128
ORDER   BY  
10   DESC ,  1   ASC ,  2   ASC ;