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 < 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 BDUMPaccess parameters (records delimited  by  newlinenobadfilenodiscardfilenologfile)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 ASTART  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$PGASTAV$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_timehash_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_timehash_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 ;