DBA日常维护SQL整理

转载至http://czmmiao.iteye.com/blog/1292412

database 概况信息检查 
# 检查 database 基本信息 
select * from v$version; 
select name ,open_mode,log_mode from v$database; 
select instance_number,instance_name ,status from gv$instance; 
show parameter cpu_count 
show parameter block_size 
select group#,thread#,members,bytes/1024/1024 from gv$log; 
show sga 
select count(*) from v$controlfile 
select count(*) from v$tempfile; 
select count(*) from v$datafile;

查看数据文件信息 
# 检查临时表空间数据文件信息 
col tablespace_name for a30 
select tablespace_name , sum(bytes)/1024/1024 from dba_temp_files group by tablespace_name;
# 检查表空间 
SELECT 
TABLESPACE_NAME,EXTENT_MANAGEMENT,ALLOCATION_TYPE,SEGMENT_SPACE 
_MANAGEMENT FROM DBA_TABLESPACES; 
# 检查数据文件状态 
select count(*),status from v$datafile group by status;
# 检查表空间使用情况 
select total.tablespace_name,
       round(total.MB, 2) as Total_MB,
       round(total.MB - free.MB, 2) as Used_MB,
       round((1 - free.MB / total.MB) * 100, 2) || '%' as Used_Pct from
       (select tablespace_name,sum(bytes) / 1024 / 1024 as MB from dba_free_space group by tablespace_name) free,
       (select tablespace_name,sum(bytes) / 1024 / 1024 as MB from dba_data_files group by tablespace_name) total
       where free.tablespace_name = total.tablespace_name order by used_pct desc; 
# 查询临时 segment 使用情况 
COL username FORMAT a10; 
COL segtype FORMAT a10; 
SELECT username, segtype, extents "Extents Allocated" 
,blocks "Blocks Allocated" 
FROM v$tempseg_usage; 
# 查看临时表空间大小
select tablespace_name,file_name,bytes/1024/1024 "file_size(M)",autoextensible from dba_temp_files;
select status,enabled, name, bytes/1024/1024 file_size from v$tempfile;
# 查看临时表空间的使用情况 
SELECT temp_used.tablespace_name,
           total - used as "Free",
           total as "Total",
           round(nvl(total - used, 0) * 100 / total, 3) "Free percent"
      FROM (SELECT tablespace_name, SUM(bytes_used) / 1024 / 1024 used
              FROM GV_$TEMP_SPACE_HEADER
             GROUP BY tablespace_name) temp_used,
           (SELECT tablespace_name, SUM(bytes) / 1024 / 1024 total
              FROM dba_temp_files
             GROUP BY tablespace_name) temp_total
     WHERE temp_used.tablespace_name = temp_total.tablespace_name
# 查看当前临时表空间使用大小与正在占用临时表空间的sql语句 
SELECT
       se.sid    
     , se.serial#
     , su.segtype
     , su.contents
     , su.blocks * 8 / 1024 "MB"
     , s.sql_id  
     , s.child_number
     , s.sql_text
FROM   v$sort_usage su
     , v$session se
     , v$sql s
WHERE  su.session_addr = se.saddr
AND    s.address       = se.sql_address
ORDER BY; 
# 查看数据文件信息 , 若文件较多可以根据需要字段进行排序 输出 top 10

col datafile for a60 
SELECT fs.phyrds "Reads", fs.phywrts "Writes" 
,fs.avgiotim "Average I/O Time", df.name "Datafile" 
FROM v$datafile df, v$filestat fs WHERE df.file# = fs.file#; 
# 查看所有数据文件 i/ o 情况 , 若文件太多 , 可以改写 为 top 10 select *( order by xx desc) where 
rownum<=10。其中phyrds为物理读的次数极为Reads，phywrts为物理写的次数极为Writes，phyblkrd为物理块读的次数即为br，phyblkwrt为物理写的次数即为bw。readtime为耗费在物理读上的总时间极为RTime，writetim为耗费在物理写上的总时间即为WTime。这两个值只有在参数timed_statistics参数为true时才有效。
COL ts FORMAT a10 HEADING "Tablespace"; 
COL reads FORMAT 999990; 
COL writes FORMAT 999990; 
COL br FORMAT 999990 HEADING "BlksRead"; 
COL bw FORMAT 999990 HEADING "BlksWrite"; 
COL rtime FORMAT 999990; 
COL wtime FORMAT 999990; 
SELECT ts.name AS ts, fs.phyrds "Reads", fs.phywrts "Writes" 
,fs.phyblkrd AS br, fs.phyblkwrt AS bw 
,fs.readtim "RTime", fs.writetim "WTime" 
FROM v$tablespace ts, v$datafile df, v$filestat fs 
WHERE ts.ts# = df.ts# AND df.file# = fs.file# 
UNION 
SELECT ts.name AS ts, ts.phyrds "Reads", ts.phywrts "Writes",ts.phyblkrd AS br, ts.phyblkwrt AS bw 
,ts.readtim "RTime", ts.writetim "WTime" 
FROM v$tablespace ts, v$tempfile tf, v$tempstat ts 
WHERE ts.ts# = tf.ts# AND tf.file# = ts.file# ORDER BY 1; 
# 获取 top 10 热 segment

set linesize 180
col object_name for a40 
select * from 
(select 
ob.owner, ob.object_name, sum(b.tch) Touchs 
from x$bh b , dba_objects ob 
where b.obj = ob.data_object_id 
and b.ts# > 0 
group by ob.owner, ob.object_name 
order by sum(tch) desc) 
where rownum <=10 
# 判断物理读最多的 object

select * from (select owner,object_name,value from v$segment_statistics where 
statistic_name='physical reads' order by value desc) where rownum<=10 
# 查看热点数据文件 ( 从单块读取时间判断 )

col FILE_NAME for a60
set linesize 180
SELECT t.file_name, 
t.tablespace_name, 
round(s.singleblkrdtim/s.singleblkrds, 2) AS CS, 
s.READTIM, 
s.WRITETIM 
FROM v$filestat s, dba_data_files t 
WHERE s.file# = t.file_id and s.singleblkrds <>0 and rownum<=10 order by cs desc 
#估算表空间大小

select a.tablespace_name,

        round(a.s,2) "CURRENT_TOTAL(MB)" ,

        round((a.s - f.s),2) "USED(MB)" ,

        f.s "FREE(MB)" ,

        round(f.s / a.s * 100, 2) "FREE%" ,

        g.autoextensible,

        round(a.ms,2) "MAX_TOTAL(MB)"

   from ( select d.tablespace_name,

                sum (bytes / 1024 / 1024) s,

                sum (decode(maxbytes, 0, bytes, maxbytes) / 1024 / 1024) ms

           from dba_data_files d

          group by d.tablespace_name) a,

        ( select f.tablespace_name, sum (f.bytes / 1024 / 1024) s

           from dba_free_space f

          group by f.tablespace_name) f,

        ( select distinct tablespace_name, autoextensible

           from DBA_DATA_FILES

          where autoextensible = 'YES'

         union

         select distinct tablespace_name, autoextensible

           from DBA_DATA_FILES

          where autoextensible = 'NO'

            and tablespace_name not in

                ( select distinct tablespace_name

                   from DBA_DATA_FILES

                  where autoextensible = 'YES' )) g

  where a.tablespace_name = f.tablespace_name

    and g.tablespace_name = f.tablespace_name order by "FREE%" ;

查看redo

# 检查日志切换频率 
select sequence#,to_char(first_time,'yyyymmdd_hh24:mi:ss') 
firsttime,round((first_time-lag(first_time) over(order by first_time))*24*60,2) minutes from 
v$log_history where first_time > sysdate - 1 order by first_time ,minutes; 
# 检查 lgwr i/o 性能 (time_waited/total_waits:表示平均lgwr写入完成时间若>1表示写入过慢 ) 
select total_waits,time_waited,average_wait,time_waited/total_waits as avg from 
v$system_event where event = 'log file parallel write'; 
# 查询 redo block size

select max(lebsz) from x$kccle;  
# 查看 user commit 次数 
select to_number(value,99999999999) from v$sysstat where name='user commits'; 
# 查看系统运行时间 
select (sysdate - startup_time)*24*60*60 as seconds from v$instance 
# 计算出每秒用户提交次数 
select user_commit 次数 / 系统运行时间 from dual; 
# 计算出每个事务平均处理多少个 redo block 
select value from v$sysstat where name = 'redo blocks written'; 
select a.redoblocks/b.trancount from (select value redoblocks from v$sysstat where name='redo 
blocks written') a ,(select value trancount from v$sysstat where name='user commits') b 
# 计算每天产生了多少日志
SELECT TO_CHAR (TRUNC (COMPLETION_TIME), 'yyyy-mm-dd') "日期",
         SUM (blocks * BLOCK_SIZE) / 1024 / 1024 / 1024 "日志量(G)"
    FROM V$ARCHIVED_LOG
   WHERE dest_id = 1
GROUP BY TRUNC (COMPLETION_TIME)
ORDER BY TRUNC (COMPLETION_TIME) DESC; 
sga,pga, 命中率 
# sga,pga, 命中率 
# 检查 sga 
show sga 
select * from v$sga; 
# 查看buffer cache设置建议 
select size_for_estimate, estd_physical_read_factor, 
to_char(estd_physical_reads,99999999999999999999999) as"estd_physical_reads" from 
v$db_cache_advice where name = 'DEFAULT'; 
COL pool FORMAT a10; 
SELECT (SELECT ROUND(value/1024/1024,0) FROM v$parameter 
WHERE name = 'db_cache_size') "Current Cache(Mb)" 
,name "Pool", size_for_estimate "Projected Cache(Mb)" 
,ROUND(100-estd_physical_read_factor,0) "Cache Hit Ratio%" 
FROM v$db_cache_advice 
WHERE block_size = (SELECT value FROM v$parameter 
WHERE name = 'db_block_size') 
ORDER BY 3; 
# 查看 cache 池 
show parameter cache 
# 查看 buffer cache 中 defalut pool 命中率 
select name,1-(physical_reads)/(consistent_gets+db_block_gets) 
from v$buffer_pool_statistics; 
# 检查 shared pool 
show parameter shared 
# 检查 shared pool 中 library cache
select namespace,pinhitratio from v$librarycache; 
# 检查整体命中率 (library cache) 
select sum(pinhits)/sum(pins) from v$librarycache; 
select sum(pins) "hits", 
sum(reloads) "misses", 
sum(pins)/(sum(pins)+sum(reloads)) "Hits Ratio" 
from v$librarycache; 
# 检查 shared pool free space 
SELECT * FROM V$SGASTAT 
WHERE NAME = 'free memory' 
AND POOL = 'shared pool'; 
# 每个子shared pool 由单独的 shared pool latch保护，查看他们的命中率 shared pool latch，用于shared pool空间回收分配使用的latch 
col name format a15 
select addr,name,gets,misses,1-misses/gets from v$latch_children where name='shared pool'; 
# 使用 v$shared_pool_advice 计算不同 shared pool 大小情况下，响应时间， S 单位 
SELECT 'Shared Pool' component, 
shared_pool_size_for_estimate estd_sp_size, 
estd_lc_time_saved_factor parse_time_factor, 
CASE 
WHEN current_parse_time_elapsed_s + adjustment_s < 0 THEN 
0 
ELSE 
current_parse_time_elapsed_s + adjustment_s 
END response_time 
FROM (SELECT shared_pool_size_for_estimate, 
shared_pool_size_factor, 
estd_lc_time_saved_factor, 
a.estd_lc_time_saved, 
e.VALUE / 100 current_parse_time_elapsed_s, 
c.estd_lc_time_saved - a.estd_lc_time_saved adjustment_s FROM v$shared_pool_advice a, 
(SELECT * FROM v$sysstat WHERE NAME = 'parse time elapsed') e, 
(SELECT estd_lc_time_saved FROM v$shared_pool_advice 
WHERE shared_pool_size_factor = 1) c) 
/ 
# 查看 shared pool 中 各种类型的 chunk 的大小数量 
SELECT KSMCHCLS CLASS, COUNT(KSMCHCLS) NUM, SUM(KSMCHSIZ) SIZ, 
To_char( ((SUM(KSMCHSIZ)/COUNT(KSMCHCLS)/1024)),'999,999.00')||'k' "AVG 
SIzE" 
FROM X$KSMSP GROUP BY KSMCHCLS; 
# 查看是否有库缓冲有关的等待事件 
select sid,seq#,event,p1,p1raw,p2,p2raw,p3,p3raw,state from v$session_wait where event like 
'library%'; 
# 查询 sga 中各个 pool 情况 
COL name FORMAT a32; 
SELECT pool, name, bytes FROM v$sgastat 
WHERE pool IS NULL 
OR pool != 'shared pool' OR (pool = 'shared pool' 
AND (name IN('dictionary cache','enqueue','library 
cache','parameters', 
'processes','sessions','free memory'))) 
ORDER BY pool DESC NULLS FIRST, name; 
SELECT * FROM V$SGAINFO; 
# 查看使用 shard_pool 保留池情况 
SELECT request_misses, request_failures, free_space 
FROM v$shared_pool_reserved; 
Oracle 专门从共享池内置出一块区域来来分配内存保持这些大块。这个保留共享池的默认大小是共享池的5%(_shared_pool_reserved_pct 5 控制 ) oracle 建设置为 10% 。大小通过参数 SHARED_POOL_RESERVED_SIZE 改。它是从共享池中分配，不是直接从 SGA 中分配的，它是共享池的保留部分，专门用于存储大块段#shared pool 中内存大于 _SHARED_POOL_RESERVED_MIN_ALLOC 将放入 shared pool 保留池 , 保留池维护一个单独的 freelist,lru ，并且不会在 lru 列表存recreatable 类型 chunks ，普通 shared pool 的释放与 shared pool 保留池无关。 
# 关于设置 SHARED_POOL_RESERVED_SIZE 
#1.如果系统出现ora-04031, 发现请求内存都是大于 _SHARED_POOL_RESERVED_MIN_ALLOC (default 10GR2 4400) , 且v$shared_pool_reserved 中有大量 REQUEST_MISSES( 并且可以看下LAST_MISS_SIZE )表示 SHARED_POOL_RESERVED_SIZE 太小了需要大的内存的请求失败 , 那么需要加大SHARED_POOL_RESERVED_SIZE 
#2. 如果 ora-04031 请求内存出现在 4100-4400 并造成 shared pool lru 合并 , 老化换出内存 , 可以调小 _SHARED_POOL_RESERVED_MIN_ALLOC 让此部分内存进入shared reserved pool, 相应的加大SHARED_POOL_RESERVED_SIZE 
#3. 从 v$shared_pool_reserved 来判断 , 如果 REQUEST_FAILURES>0( 出现过 ora-04031) 且LAST_FAILURE_SIZE( 最后请求内存大小 )>_SHARED_POOL_RESERVED_MIN_ALLOC表示 shared reserved pool 缺少连续内存 , 可以加大 SHARED_POOL_RESERVED_SIZE, 减少 _SHARED_POOL_RESERVED_MIN_ALLOC 少放对象 , 并相对加大 shared_pool_size 
# 要是反过来 REQUEST_FAILURES>0( 出现过 ora-04031) 且 LAST_FAILURE_SIZE( 最后请求内存大小)<_SHARED_POOL_RESERVED_MIN_ALLOC, 表示 在 shared pool 中缺少连续内存 , 可以加减少_SHARED_POOL_RESERVED_MIN_ALLOC 多放入一些对象 , 减少 sharedpool 压力 , 适当加大shared_pool_size,SHARED_POOL_RESERVED_SIZE 
# 查询还保留在 library cache 中，解析次数和执行次数最多的 sql( 解析 * 执行 ) 
COL sql_text FORMAT A38; 
SELECT * FROM( 
SELECT parse_calls*executions "Product", parse_calls 
"Parses" 
,executions "Execs", sql_text FROM v$sqlarea ORDER BY 1 DESC) 
WHERE ROWNUM <= 10; 
# 查看 pga 
show parameters area_size 
SELECT * FROM v$pgastat; 
# 查看pga建议

SELECT (SELECT ROUND(value/1024/1024,0) FROM v$parameter
WHERE name = 'pga_aggregate_target') "Current Mb"
, ROUND(pga_target_for_estimate/1024/1024,0) "Projected Mb"
, ROUND(estd_pga_cache_hit_percentage) "%"
FROM v$pga_target_advice
ORDER BY 2; 
# 查看数据库 cache 或 keep 了哪些 object
COL table_name FORMAT A16 
COL index_name FORMAT A16 
SELECT table_name AS "Table", NULL, buffer_pool, cache FROM 
user_tables 
WHERE buffer_pool != 'DEFAULT' OR TRIM(cache)='Y' 
UNION 
SELECT table_name, index_name, NULL, buffer_pool FROM 
user_indexes 
WHERE buffer_pool != 'DEFAULT' 
ORDER BY 1, 2 NULLS FIRST; 
# 取消 cache 或 keep(keep pool) 
ALTER TABLE XX NOCACHE; 
SELECT 'ALTER INDEX '||index_name||' STORAGE(BUFFER_POOL DEFAULT);' 
FROM USER_INDEXES WHERE BUFFER_POOL!='DEFAULT'; 
检查undo 
show parameter undo_ 
# 检查 undo rollback segment 使用情况 
select name ,rssize,extents,latch,xacts,writes,gets,waits from v$rollstat a,v$rollname b where 
a.usn=b.usn order by waits desc; 
select a.redoblocks/b.trancount from (select value redoblocks from v$sysstat where name='redo 
blocks written') a ,(select value trancount from v$sysstat where name='user commits') b; 
# 计算每秒钟产生的 undoblk 数量 
select sum(undoblks)/sum((end_time-begin_time)*24*60*60) from v$undostat; 
#Undospace=UR*UPS*blocksize + overload(10%), 计算 undo tablespace 大小 
show parameter block_size 
show parameter undo_retention 
# 计算undo表空间大小 
#select undo_retention* 每 秒 产 生 undoblk 数 量 *block_size/1024/1024/1024+ 
(1+1undo_retention* 每秒产生 undoblk 数量 *block_size/1024/1024/1024*0.1) from dual; 
# 查询 undo 具体信息 
COL undob FORMAT 99990; 
COL trans FORMAT 99990; 
COL snapshot2old FORMAT 9999999990; 
SELECT undoblks "UndoB", txncount "Trans" 
,maxquerylen "LongestQuery", maxconcurrency "MaxConcurrency" 
,ssolderrcnt "Snapshot2Old", nospaceerrcnt "FreeSpaceWait" 
FROM v$undostat; 
# 在内存中排序比率 ( 最优排序 ) 
SELECT 'Sorts in Memory ' "Ratio" 
, ROUND( 
(SELECT SUM(value) FROM V$SYSSTAT WHERE name = 'sorts (memory)') 
/ (SELECT SUM(value) FROM V$SYSSTAT 
WHERE name IN ('sorts (memory)', 'sorts (disk)')) * 100, 5) 
||'%' "Percentage" 
FROM DUAL; 
# 查看当前系统undo使用情况

SELECT DISTINCT STATUS "状态",
                COUNT(*) "EXTENT数量",
                SUM(BYTES) / 1024 / 1024 / 1024 "UNDO大小"
  FROM DBA_UNDO_EXTENTS
 GROUP BY STATUS;

# 查看当前系统和undo相关的会话

SELECT
       r.name undo_seg_name
     , se.sid sid
     , se.serial# 
     , se.username
     , se.machine
     , t.start_time
     , t.status
     , t.used_ublk
     , t.used_urec    
     , t.cr_get
     , t.cr_change     
     , t.log_io
     , t.phy_io             
     , t.noundo  
     , g.extents      
     , se.program 
     , s.sql_id
     , s.child_number
     , s.sql_text
FROM
       v$session se 
     , v$transaction t
     , v$rollname r
     , v$rollstat g
     , v$sql s
WHERE  t.addr    = se.taddr
AND    t.xidusn  = r.usn
AND    r.usn     = g.usn
AND    s.address = se.sql_address
ORDER BY t.used_ublk DESC;  
查看对象 
# 检查数据库中无效对象 
SELECT owner, object_type,count(object_name) FROM dba_objects WHERE status= 'INVALID'group by owner,object_type; 
# 检查是否有禁用约束 
SELECT owner, constraint_name, table_name, constraint_type, status 
FROM dba_constraints 
WHERE status ='DISABLE' and constraint_type='P' 
# 检查是否有禁用 trigger 
col owner for a10 
col taigger_name for a10 
cok table_name for a30 
col table_name for a30 
SELECT owner, trigger_name, table_name, status FROM dba_triggers WHERE status = 
'DISABLED'; 

# 在某个表下找的索引情况

col column_name for a12
set linesize 180
select user_indexes.table_name, user_indexes.index_name,uniqueness, column_name
from user_ind_columns, user_indexes
where user_ind_columns.index_name = user_indexes.index_name
and user_ind_columns.table_name = user_indexes.table_name

AND user_indexes.table_name='&tb_name'
order by user_indexes.table_type, user_indexes.table_name,
user_indexes.index_name, column_position;
# 检查与索引相关的字段 
select * from user_ind_columns where index_name=upper('&index_name');

# 检查索引的唯一性的 
col uniq    format a10 heading 'Uniqueness'  justify c trunc 
col indname format a40 heading 'Index Name'  justify c trunc 
col colname format a25 heading 'Column Name' justify c trunc 
break - 
  on indname skip 1 - 
  on uniq 
select 
  ind.uniqueness                  uniq, 
  ind.owner||'.'||col.index_name  indname, 
  col.column_name                 colname 
from 
  dba_ind_columns  col, 
  dba_indexes      ind 
where 
  ind.owner = upper('&ixowner') 
    and 
  ind.table_name = upper('&tabname') 
    and 
  col.index_owner = ind.owner  
    and 
  col.index_name = ind.index_name 
order by 
  col.index_name, 
  col.column_position 

查看当前系统状态
# 检查系统中排行前10的等待事件，不包括空闲等待事件 

select * from (select sid,event,p1,p2,p3,p1text,WAIT_TIME,SECONDS_IN_WAIT,state from v$session_wait where wait_class# <> 6

order by wait_time desc) where rownum <=10;

# 查看经常被使用而没有pin在内存中的对象
# 形成生成pin住共享池中当前没有被pin住的对象的sql语句。在执行exec sys.DBMS_SHARED_POOL.keep('JXXXT.IN_GZ_LOGS','P');可能会报出未定义的错误，需要在sqlplus下执行脚本$ORACLE_HOME/rdbms/admin/dbmspool.sql

select 'exec sys.DBMS_SHARED_POOL.keep('||chr(39)||owner||'.'||NAME||chr(39)||','||chr(39)||'P'||chr(39)||');' as sql_to_run
from  V$DB_OBJECT_CACHE where TYPE in ('PACKAGE','FUNCTION','PROCEDURE') and loads > 50 and kept='NO' and executions > 50; 
# 查看使用了超过10MB内存 而没有pin的对象 
SELECT owner,name,sharable_mem,kept FROM V$DB_OBJECT_CACHE
WHERE sharable_mem > 102400 AND kept = 'NO' ORDER BY sharable_mem DESC;

# 查看大的没有被pin住的对象.
set linesize 150
col sz for a10
col name for a100
col keeped for a6
select to_char(sharable_mem / 1024,'999999') sz_in_K, decode(kept, 'yes','yes  ','') keeped,
owner||','||name||lpad(' ',29 - (length(owner) + length(name))) || '(' ||type||')'name,
null extra, 0 iscur from v$db_object_cache v where sharable_mem > 1024*1000;

# 查看大的没有被pin住的过程，包和函数 
col type for a25
col name for a40   
col owner for a25
select owner,name,type,round(sum(sharable_mem/1024),1) sharable_mem_K from v$db_object_cache  where kept = 'NO'
and (type = 'PACKAGE' or type = 'FUNCTION' or type = 'PROCEDURE')
group by owner,name,type order by 4; 
需要被pin入内存中的对象主要有：常用的较大的存储对象，如standard、diutil包；编译的常用的triggers；sequences。
最好在开机时就将其pin入内存中。这样，既是使用命令alter system flush shared_pool时，也不会讲这些object flush掉。具体pin对象到内存的方法使用DBMS_SHARED_POOL.keep存储过程。可以用unkeep方法解除其pin状态。
db_object_cache和碎片化
碎片化造成在共享池中虽然有许多小的碎片可以使用，但没有足够大的连续空间，这在共享池中是普遍的现象。消除共享池错误的关键就是即将加载对象的大小是否可能会产生问题。一旦知道了这个存在问题的PL/SQL，那么就可以在数据库启动时(这时共享池是完全连续的)就将这个代码固定。这将确保在调用大型包时，它已经在共享池里，而不是在共享池中搜索连续的碎片(在使用系统时，这些碎片可能就不复存在)。可以查询V$DB_OBJECT_CACHE视图来判断PL/SQL是否很大并且还没有被标识为"kept"的标记。今后需要加载这些对象时，可能会产生问题(因为它们的大小和需要占用大量连续的内存)。通过查询V$DB_OBJECT_CACHE表，可以发现那些没有固定，但由于所需空间太大而很有可能导致潜在问题的对象。

# 查询一下回滚段的使用情况，其中USED_UREC为undo记录的使用条目数，USED_UBLK为undo块的使用数目
set linesize 180 

SELECT a.sid, a.username, b.xidusn, b.used_urec, b.used_ublk 
from v$session a, v$transaction b
WHERE a.saddr = b.ses_addr;

# 查看当前活动的会话

SELECT
       p.username                       AS process_username
     , se.username                      AS schema_name
     , se.status                        AS status
     , se.sid                           AS ssid
     , se.serial#                       AS serial#
     , p.spid                           AS spid
     , s.sql_text                       AS sql_txt
FROM
       v$process p
     , v$session se
     , v$sqlarea s
WHERE  p.addr            = se.paddr
AND    se.sql_address    = s.address(+)
AND    se.sql_hash_value = s.hash_value(+)
AND    se.status         = 'ACTIVE'
AND    se.username IS NOT NULL;

# sql的绑定变量

SELECT
       s.sid     
     , s.username
     , sq.sql_text
     , s.sql_hash_value
     , s.sql_id  
     , s.sql_child_number
     , spc.name  
     , spc.value_string
     , last_captured
FROM
       v$sql_bind_capture spc
     , v$session s
     , v$sql sq
WHERE  s.sql_hash_value  = spc.hash_value
AND    s.sql_address     = spc.address
AND    sq.sql_id         = s.sql_id
AND    spc.was_captured  = 'YES'
AND    s.TYPE           <> 'BACKGROUND'
--AND    s.sql_id        = '&sql_id'
--AND    s.status        = 'ACTIVE'
;
# 查看sql内存的使用情况
SELECT
       b.sid
     , name
     , value/(1024 * 1024) "MB"
     , s.serial#
     , s.sid ssid
     , s.status
     , s.username
     , s.schemaname
     , s.osuser
     , s.machine
     , s.terminal
     , s.program
     , s.module
     , state
     , logon_time
     , p.spid
     , sa.sql_text sql_txt
FROM
       v$statname a
     , v$sesstat b
     , v$session s
     , v$process p
     , v$sqlarea sa
WHERE  a.statistic#     = b.statistic#
AND    b.sid            = s.sid
AND    osuser!          = 'oracle'
AND    p.addr           = s.paddr
AND    s.sql_address    = sa.address(+)
AND    s.sql_hash_value = sa.hash_value(+)
AND    NAME LIKE '%a memory'
ORDER BY status,mb DESC,sid, name;

# 查看锁住对象的会话信息，操作系统进程信息
set linesize 180
select object_name,machine,s.sid,s.serial#,p.spid
from v$locked_object l,dba_objects o ,v$session s,v$process p
where l.object_id=o.object_id and l.session_id=s.sid
and  s.paddr=p.addr

# 根据进程查看sql

select sql_text
from v$sqltext_with_newlines
where (hash_value,address) in (select sql_hash_value,sql_address from v$session where sid=(select ses.sid from v$session ses,v$process pro
where pro.spid=&spid
and ses.paddr=pro.addr)) order by address,piece;

# 查看被锁的表的被锁时间 
set linesize 180
select b.username,b.sid,b.serial#,logon_time
from v$locked_object a,v$session b
where a.session_id = b.sid order by b.logon_time;

# 查看被锁的对象和引起锁的sql

select a.sid,a.username,d.object_name, b.sql_text
from v$session a,v$sql b, v$locked_object c,dba_objects d
where a.sql_hash_value=b.hash_value 
and a.sid = c.session_id
and d.object_id = c.object_id;

# 查看锁定的会话信息 
select b.username,b.sid,b.serial#,logon_time
from v$locked_object a,v$session b
where a.session_id = b.sid order by b.logon_time

# 杀死相关会话

alter system kill session 'sid,serial#';

# 如果出现ora-00031错误，则

alter system kill session 'sid,serial#' immediate；

# 亦可先查询该会话相对应的操作系统进程，在操作系统上进行kill

# 查找需要使用绑定变量的sql
select substr(sql_text,1,40), count(*)
from v$sqlarea
group by substr(sql_text,1,40) having count(*) > 50; 
再 select sql_text from v$sqlarea where sql_text like 'insert into test %'; 找出具体的sql代码
检查Latch的相关SQL 
# 查询当前数据库最繁忙的Buffer，TCH(Touch)越大表示访问次数越高
SELECT *
  FROM (  SELECT addr,
                 ts#,
                 file#,
                 dbarfil,
                 dbablk,
                 tch
            FROM x$bh
        ORDER BY tch DESC)
 WHERE ROWNUM < 11; 

# 查看latch的命中率

SQL>SELECT name, gets, misses, sleeps, 
      immediate_gets, immediate_misses
     FROM v$latch
   WHERE name = 'cache buffers chains';

#查找数据块中的热点块 

SELECT *

  FROM (SELECT O.OWNER, O.OBJECT_NAME, O.OBJECT_TYPE, SUM(TCH) TOUCHTIME

          FROM X$BH B, DBA_OBJECTS O

         WHERE B.OBJ = O.DATA_OBJECT_ID

           AND B.TS# > 0

         GROUP BY O.OWNER, O.OBJECT_NAME, O.OBJECT_TYPE

         ORDER BY SUM(TCH) DESC)

 WHERE ROWNUM <= 10;

#根据文件号和块号查找数据库对象

select owner, segment_name, partition_name, tablespace_name 
from dba_extents
where relative_fno = &v_dba_rfile and &v_dba_block between block_id and block_id + blocks - 1;
# 如果在Top 5中发现latch free热点块事件时，可以从V$latch_children中查询具体的子Latch信息
SELECT *
  FROM (SELECT   addr, child#, gets, misses, sleeps, immediate_gets igets,
                immediate_misses imiss, spin_gets sgets
            FROM v$latch_children
           WHERE NAME = 'cache buffers chains'
        ORDER BY sleeps DESC)
 WHERE ROWNUM < 11;

# 查看引起latch: cache buffers chains的sql
SQL> select * from (select 
    count(*), 
    sql_id, 
    nvl(o.object_name,ash.current_obj#) objn,
    substr(o.object_type,0,10) otype,
     3    4    5    6        CURRENT_FILE# fn,
         CURRENT_BLOCK# blockn
   from  v$active_session_history ash
       , all_objects o
   where event like 'latch: cache buffers chains'
     and o.object_id (+)= ash.CURRENT_OBJ#
   group by sql_id, current_obj#, current_file#,
                  current_block#, o.object_name,o.object_type
   order by  count(*) desc )where rownum <=10; 
日常管理
#查看当前用户的trace文件
SELECT      u_dump.VALUE
         || '/'
         || db_name.VALUE
         || '_ora_'
         || v$process.spid
         || NVL2 (v$process.traceid, '_' || v$process.traceid, NULL)
         || '.trc'
            "Trace File"
  FROM            v$parameter u_dump
               CROSS JOIN
                  v$parameter db_name
            CROSS JOIN
               v$process
         JOIN
            v$session
         ON v$process.addr = v$session.paddr
 WHERE       u_dump.name = 'user_dump_dest'
         AND db_name.name = 'db_name'
         AND v$session.audsid = SYS_CONTEXT ('userenv', 'sessionid')
#查询某段时间内执行过的sql
select a.sql_id,dbms_lob.substr(b.sql_text,4000,1) from dba_hist_active_sess_history a, dba_hist_sqltext b
where sample_time between to_date('20100930:09:00','yyyymmdd:hh24:mi')
and to_date('20100930:09:01','yyyymmdd:hh24:mi') and b.sql_id=a.sql_id
union all
select a.sql_id ,dbms_lob.substr(b.sql_text,4000,1)from v$active_session_history a ,v$sqlarea b
where sample_time between to_date('20100930:09:00','yyyymmdd:hh24:mi') and
to_date('20100930:09:01','yyyymmdd:hh24:mi') and b.sql_id=a.sql_id
由于v$active_session_history和dba_hist_active_sess_history的数据来源于awr和ash采样，记录并不完全，故查询结果并不准确。
#查看sql的实际执行计划
SELECT sql_id, address, hash_value FROM v$sql 
 WHERE sql_text like ¨%TAG%¨; 
SQL_ID  ADDRESS HASH_VALUE 
-------- -------- ---------- 
40qhh45kcnfbv  82157784 1224822469 
#通过sqlid查询库缓冲区中的sql执行计划

SELECT * FROM TABLE(dbms_xplan.display_cursor('40qhh45kcnfbv',0));
#查看全面的执行计划

select /*+ gather_plan_statistics */ count(*) from scott.emp; select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

# 查找你的session信息

SELECT SID, OSUSER, USERNAME, MACHINE, PROCESS

FROM V$SESSION WHERE audsid = userenv('SESSIONID');

# 当machine已知的情况下查找session

SELECT SID, OSUSER, USERNAME, MACHINE, TERMINAL

FROM V$SESSION

WHERE terminal = 'pts/tl' AND machine = 'rgmdbs1';

# 查找当前被某个指定session正在运行的sql语句。寻找被指定session执行的SQL语句是一个公共需求，如果session是瓶颈的主要原因，那根据其当前在执行的语句可以查看session在做些什么。 假设sessionID为100

select b.sql_text

from v$session a,v$sqlarea b

where a.sql_hashvalue=b.hash_value and a.sid=100

#查看sql执行状态

select status,last_call_et,event from v$session where sid=&id;

#查看客户端和应用信息

select osuser,machine,terminal,process,program,client_info,action,module from v$session
where sid=&id and seq#=&seq

#查看会话消耗资源的情况，以CPU资源为例，不同的资源可以根据v$statname和v$sesstat关联进行查询，常用的有session logical reads, CPU used by this session, db block changes, redo size, 
physical writes, parse count (hard), parse count (total), sorts (memory), and sorts (disk)等
select a.sid,spid,status,substr(a.program,1,40) prog,a.terminal,osuser,c.value/60/100 value,d.name
from v$session a,v$process b,v$sesstat c,v$statname d
where d.name='CPU used by this session'

   and c.sid=a.sid and a.paddr=b.addr and d.statistic#=c.statistic#
order by c.value desc

注意:v$sysstat 和v$sesstat差别如下： 
v$sesstat只保存session数据，而v$sysstat则保存所有sessions的累积值。
v$sesstat只是暂存数据，session退出后数据即清空。v$sysstat则是累积的，只有当实例被shutdown才会清空。
v$sesstat不包括统计项名称，如果要获得统计项名称则必须与v$sysstat或v$statname连接查询获得。 
#通过sqlid查询库AWR中的sql执行计划

SELECT * FROM table (DBMS_XPLAN.DISPLAY_AWR ('40qhh45kcnfbv'));

#查看某用户的PID和SPID

select pid,spid from v$process

where addr in (select paddr from v$session where username='SYSTEM');

#查看隐含参数

select x.ksppinm name, y.ksppstvl value, x.ksppdesc describ

from sys.x$ksppi x , sys.x$ksppcv y

where x.indx = y.indx

and x.ksppinm like '%&par%';

#查看对象大小，对象大小以已分配的extent统计

select segment_name, bytes/1024/1024 MB

from user_segments

where segment_type = 'TABLE';

或者

Select SEGMENT_NAME,Sum(bytes)/1024/1024 From User_Extents where segment_name='BIG' group by segment_name;

# 查看表的统计信息

select table_name,num_rows,blocks,empty_blocks,avg_space,chain_cnt,avg_row_len,global_stats,user_stats,sample_size,to_char(last_analyzed,'yyyy-mm-dd hh24:mi:ss') 
from dba_tables where owner = upper(nvl('&Owner',user)) and table_name=upper('&table_name');
# 查看分区的统计信息

select table_name,PARTITION_NAME ,composite,SUBPARTITION_COUNT,num_rows,blocks,empty_blocks,avg_space,chain_cnt,avg_row_len,global_stats,user_stats,sample_size,to_char(last_analyzed,'yyyy-mm-dd hh24:mi:ss') 
from dba_tab_partitions where and table_name=upper('&table_name');

# 查看子分区的统计信息

select table_name,PARTITION_NAME ,subpartition_name,subpartition_position,num_rows,blocks,empty_blocks,avg_space,chain_cnt,avg_row_len,global_stats,user_stats,sample_size,to_char(last_analyzed,'yyyy-mm-dd hh24:mi:ss') 
from dba_TAB_SUBPARTITIONS where table_name=upper('&table_name');

#查看列上的统计信息

select
    COLUMN_NAME,
    decode(t.DATA_TYPE,
           'NUMBER',t.DATA_TYPE||'('||
           decode(t.DATA_PRECISION,
                  null,t.DATA_LENGTH||')',
                  t.DATA_PRECISION||','||t.DATA_SCALE||')'),
                  'DATE',t.DATA_TYPE,
                  'LONG',t.DATA_TYPE,
                  'LONG RAW',t.DATA_TYPE,
                  'ROWID',t.DATA_TYPE,
                  'MLSLABEL',t.DATA_TYPE,
                  t.DATA_TYPE||'('||t.DATA_LENGTH||')') ||' '||
    decode(t.nullable,
              'N','NOT NULL',
              'n','NOT NULL',
              NULL) col,
    NUM_DISTINCT,
    DENSITY,
    NUM_BUCKETS,
    NUM_NULLS,
    GLOBAL_STATS,
    USER_STATS,
    SAMPLE_SIZE,
    HISTOGRAM,
    AVG_COL_LEN,
    to_char(t.last_analyzed,'yyyy-mm-dd hh24:mi:ss')
from dba_tab_columns t
where 
    table_name = upper('&Table_name')
and owner = upper(nvl('&Owner',user));

# 查看分区表列上的统计信息

select

    TABLE_NAME,
    PARTITION_NAME,
    COLUMN_NAME,
    NUM_DISTINCT,
    DENSITY,
    NUM_BUCKETS,
    NUM_NULLS,
    GLOBAL_STATS,
    USER_STATS,
    SAMPLE_SIZE,
    to_char(t.last_analyzed,'YYYY-MM-DD HH24:MI:SS')
from 
    dba_PART_COL_STATISTICS t
where 
    table_name = upper('&Table_name')
and owner = upper(nvl('&Owner',user))
/
# 查看子分区上列的统计信息

select 
    p.PARTITION_NAME,
    t.SUBPARTITION_NAME,
    t.COLUMN_NAME,
    t.NUM_DISTINCT,
    t.DENSITY,
    t.NUM_BUCKETS,
    t.NUM_NULLS,
    t.GLOBAL_STATS,
    t.USER_STATS,
    t.SAMPLE_SIZE,
    to_char(t.last_analyzed,'YYYY-MM-DD HH24:MI:SS')
from 
    dba_SUBPART_COL_STATISTICS t, 
    dba_tab_subpartitions p
where 
    t.table_name = upper('&Table_name')
and t.owner = upper(nvl('&Owner',user))
and t.subpartition_name = p.subpartition_name
and t.owner = p.table_owner
and t.table_name=p.table_name;
#索引的统计信息

select 
    INDEX_NAME,
    UNIQUENESS,
    BLEVEL BLev,
    LEAF_BLOCKS,
    DISTINCT_KEYS,
    NUM_ROWS,
    AVG_LEAF_BLOCKS_PER_KEY,
    AVG_DATA_BLOCKS_PER_KEY,
    CLUSTERING_FACTOR,
    GLOBAL_STATS,
    USER_STATS,
    SAMPLE_SIZE,
    to_char(t.last_analyzed,'YYYY-MM-DD HH24:MI:SS')
from 
    dba_indexes t
where 
    table_name = upper('&Table_name')
and table_owner = upper(nvl('&Owner',user));

#查看分区索引的统计信息

select
    i.INDEX_NAME,
    i.COLUMN_NAME,
    i.COLUMN_POSITION,
    decode(t.DATA_TYPE,
           'NUMBER',t.DATA_TYPE||'('||
           decode(t.DATA_PRECISION,
                  null,t.DATA_LENGTH||')',
                  t.DATA_PRECISION||','||t.DATA_SCALE||')'),
                  'DATE',t.DATA_TYPE,
                  'LONG',t.DATA_TYPE,
                  'LONG RAW',t.DATA_TYPE,
                  'ROWID',t.DATA_TYPE,
                  'MLSLABEL',t.DATA_TYPE,
                  t.DATA_TYPE||'('||t.DATA_LENGTH||')') ||' '||
           decode(t.nullable,
                  'N','NOT NULL',
                  'n','NOT NULL',
                  NULL) col
from 
    dba_ind_columns i,
    dba_tab_columns t
where 
    i.table_name = upper('&Table_name')
and owner = upper(nvl('&Owner',user))
and i.table_name = t.table_name
and i.column_name = t.column_name
order by index_name,column_position;

# 查看子分区索引的统计信息

select 
    t.INDEX_NAME,
    t.PARTITION_NAME,
    t.SUBPARTITION_NAME,
    t.BLEVEL BLev,
    t.LEAF_BLOCKS,
    t.DISTINCT_KEYS,
    t.NUM_ROWS,
    t.AVG_LEAF_BLOCKS_PER_KEY,
    t.AVG_DATA_BLOCKS_PER_KEY,
    t.CLUSTERING_FACTOR,
    t.GLOBAL_STATS,
    t.USER_STATS,
    t.SAMPLE_SIZE,
    to_char(t.last_analyzed,'MM-DD-YYYY')
from 
    dba_ind_subpartitions t, 
    dba_indexes i
where 
    i.table_name = upper('&Table_name')
and i.table_owner = upper(nvl('&Owner',user))
and i.owner = t.index_owner
and i.index_name=t.index_name;

#正在运行的存储过程

col name format a56 
select name                                        
from v$db_object_cache                             
where locks > 0 and pins > 0 and type='PROCEDURE';

select sid,name from v$access;
SELECT s.SID, s.OSUSER, s.USERNAME, s.MACHINE, s.PROCESS,a.object FROM V$SESSION s ,v$access a, v$db_object_cache  d WHERE s.sid = a.sid and a.object=d.name and d.type='PROCEDURE';

# 查询外键约束(查scott用户emp表的所有父表)  
set linesize 120
col owner for a8
col table_name for a12
col constraint_name for a20
col column_name for a20
select c.constraint_name,cc.column_name,rcc.owner,rcc.table_name,rcc.column_name 
from dba_constraints c,dba_cons_columns cc,dba_cons_columns rcc  
where c.owner='SCOTT' 
and c.table_name='EMP' and c.constraint_type='R' and c.owner=cc.owner 
and c.constraint_name=cc.constraint_name and c.r_owner=rcc.owner and c.r_constraint_name=rcc.constraint_name 
and cc.position=rcc.position 
order by c.constraint_name,cc.position;

# 查询连接到某表的所有外键(查HR用户下EMPLOYEES表的所有子表) 
set linesize 120
col owner for a8
col pk_tab for a12
col fk_tab for a12
col pk for a15
col fk for a15
col pk_col for a12
col fk_col for a12
select rcc.owner,rcc.table_name pk_tab,rcc.constraint_name pk,rcc.column_name pk_col,c.table_name fk_tab,c.constraint_name fk,cc.column_name fk_col
from dba_constraints c,dba_cons_columns cc,dba_cons_columns rcc
where c.owner='HR' and rcc.table_name='EMPLOYEES'
and c.constraint_type='R'
and c.owner=cc.owner and c.constraint_name=cc.constraint_name 
and c.r_constraint_name=rcc.constraint_name
order by c.constraint_name,cc.position;

# 查询主键唯一键约束(HR用户Employees表的主键唯一键约束) 
set linesize 120
col constraint_type for a8
col constraint_name for a20
col column_name for a20
select c.constraint_name,c.constraint_type,cc.column_name 
from dba_constraints c,dba_cons_columns cc 
where c.owner='HR' and c.table_name='EMPLOYEES' and c.owner=cc.owner and 
c.constraint_name=cc.constraint_name and c.constraint_type in ('P','U') 
order by c.constraint_type,c.constraint_name,cc.position; 

#监控使用并行的sql

set pages 0
column sql_test format a60
select p.server_name,
sql.sql_text
from v$px_process p, v$sql sql, v$session s 
WHERE p.sid = s.sid
and p.serial# = s.serial#
and s.sql_address = sql.address
and s.sql_hash_value = sql.hash_value
/

#查看并行进程的会话统计信息,如物理读

SELECT QCSID, SID, INST_ID "Inst", SERVER_GROUP "Group", SERVER_SET "Set",
  NAME "Stat Name", VALUE
FROM GV$PX_SESSTAT A, V$STATNAME B
WHERE A.STATISTIC# = B.STATISTIC# AND NAME LIKE 'PHYSICAL READS'
  AND VALUE > 0 ORDER BY QCSID, QCINST_ID, SERVER_GROUP, SERVER_SET;

#查看并行进程的系统统计信息

SELECT NAME, VALUE FROM GV$SYSSTAT
WHERE UPPER (NAME) LIKE '%PARALLEL OPERATIONS%'
OR UPPER (NAME) LIKE '%PARALLELIZED%' OR UPPER (NAME) LIKE '%PX%';

#查看隐藏参数

col KSPPINM for a40

col KSPPDESC for a40
SELECT   ksppinm, ksppstvl, ksppdesc
      FROM   x$ksppi x, x$ksppcv y
     WHERE   x.indx = y.indx AND  ksppinm = '_db_block_hash_buckets'

#查看本会话消耗的资源

select sn.statistic#,
       sn.name,
       m.value
  from v$statname sn, v$mystat m
 where sn.statistic# = m.statistic#;

#查看LOB字段信息

SELECT A.OWNER,
       A.TABLE_NAME,
       A.COLUMN_NAME,
       B.SEGMENT_NAME,
       B.SEGMENT_TYPE,
       B.TABLESPACE_NAME,
       B.BYTES/1024/1024/1024 gb,
       B.BLOCKS,
       B.EXTENTS
  FROM dba_LOBS A, dba_SEGMENTS B
WHERE A.SEGMENT_NAME = B.SEGMENT_NAME
ORDER BY B.BYTES DESC;

#清空特定的执行计划

select address,hash_value,executions,parse_calls from v$sql where sql_id='d88hn81x70bfd';
exec dbms_shared_pool.purge('00000011D8114B20,3714054405','C');

#db file sequential read事件参数信息

select b.sid,
       nvl(substr(a.object_name,1,30),
                  'P1='||b.p1||' P2='||b.p2||' P3='||b.p3) object_name,
       a.subobject_name,
       a.object_type
from   dba_objects a, v$session_wait b, x$bh c
where  c.obj = a.object_id(+)
and    b.p1 = c.file#(+)
and    b.p2 = c.dbablk(+)
and    b.event = 'db file sequential read'
union
select b.sid,
       nvl(substr(a.object_name,1,30),
                  'P1='||b.p1||' P2='||b.p2||' P3='||b.p3) object_name,
       a.subobject_name,
       a.object_type
from   dba_objects a, v$session_wait b, x$bh c
where  c.obj = a.data_object_id(+)
and    b.p1 = c.file#(+)
and    b.p2 = c.dbablk(+)
and    b.event = 'db file sequential read'
order  by 1;

 

参考至:http://mlxia.iteye.com/blog/741227

            http://blog.csdn.net/soulcq/article/details/5418085
            http://www.dbtan.com/2010/05/latch-free.html

            http://www.2cto.com/database/201107/96826.html
            http://blog.csdn.net/robinson1988/article/details/4793962
            http://blog.csdn.net/tianlesoftware/article/details/5263238
            http://www.laoxiong.net/dbms_stats_and_analyze_and_global_statistics.html
            http://www.laoxiong.net/wp-content/uploads/2008/12/sosi.sql
            http://www.cnblogs.com/caizhimin816/archive/2012/12/21/2827375.html
            http://oracledoug.com/px.pdf

            http://www.linuxeden.com/html/database/20111127/117134.html
            https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&id=1019722.6 
            http://docs.oracle.com/cd/E11882_01/server.112/e25523/parallel006.htm#VLDBG1513