DBA日常维护SQL整理(原创)

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
f.tablespace_name,
a.total,
f.free,(a.total-f.free)/1024 "used SIZE(G)"
,round((f.free/a.total)*100) "% Free"
from
(select tablespace_name, sum(bytes/(1024*1024)) total from dba_data_files group by
tablespace_name) a,
(select tablespace_name, round(sum(bytes/(1024*1024))) free from dba_free_space
group by tablespace_name) f
WHERE a.tablespace_name = f.tablespace_name(+)
order by "% Free"
/
# 查询临时 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.username,
         se.sid,
         su.extents,
         su.blocks * to_number(rtrim(p.value)) as Space,
         tablespace,
         segtype,
         sql_text
    from v$sort_usage su, v$parameter p, v$session se, v$sql s
   where p.name = ‘db_block_size‘
     and su.session_addr = se.saddr
     and s.hash_value = su.sqlhash
     and s.address = su.sqladdr
   order by se.username, se.sid

# 查看当前临时表空间使用大小与正在占用临时表空间的sql语句
select sess.SID, segtype, blocks * 8 / 1000 "MB", sql_text
    from v$sort_usage sort, v$session sess, v$sql sql
   where sort.SESSION_ADDR = sess.SADDR
     and sql.ADDRESS = sess.SQL_ADDRESS
   order by blocks desc;
# 查看数据文件信息 , 若文件较多可以根据需要字段进行排序 输出 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 (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 回滚段名,s.sid SID,s.serial# Serial,
s.username 用户名,s.machine 机器名,
t.start_time 开始时间,t.status 状态,
t.used_ublk 撤消块,USED_UREC 撤消记录,
t.cr_get 一致性取,t.cr_change 一致性变化,
t.log_io "逻辑I/O",t.phy_io "物理I/O",
t.noundo NoUndo,g.extents Extents,substr(s.program, 1, 50) 操作程序
FROM v$session s, v$transaction t, v$rollname r,v$rollstat g
WHERE t.addr = s.taddr
AND t.xidusn = r.usn
AND r.usn = g.usn
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的等待事件
col event for a30
包括空闲等待事件
select * from (select sid,event,p1,p2,p3,p1text,WAIT_TIME,SECONDS_IN_WAIT from v$session_wait where
event not like ‘SQL%‘ and event not like ‘rdbms%‘ order by wait_time desc) where rownum <=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;

# 查看锁住对象的会话信息，操作系统进程信息
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_hashvalue=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
TOP SQL
# 逻辑读 TOP 10
select *
  from (select sqt.logicr logical_Reads,
               sqt.exec Executions,
               decode(sqt.exec, 0, to_number(null), (sqt.logicr / sqt.exec)) Reads_per_Exec ,
               (100 * sqt.logicr) /
               (SELECT sum(e.VALUE) - sum(b.value)
                  FROM DBA_HIST_SYSSTAT b, DBA_HIST_SYSSTAT e
                 WHERE B.SNAP_ID =7634
                   AND E.SNAP_ID =7637
                   AND B.DBID = 3629726729
                   AND E.DBID = 3629726729
                   AND B.INSTANCE_NUMBER = 1
                   AND E.INSTANCE_NUMBER = 1
                   and e.STAT_NAME = ‘session logical reads‘
                   and b.stat_name = ‘session logical reads‘) Total_rate,
               nvl((sqt.cput / 1000000), to_number(null)) CPU_Time_s,
               nvl((sqt.elap / 1000000), to_number(null)) Elapsed_Time_s,
               sqt.sql_id,
               decode(sqt.module, null, null, ‘Module: ‘ || sqt.module) SQL_Module,
               nvl(st.sql_text, to_clob(‘** SQL Text Not Available **‘)) SQL_Text
          from (select sql_id,
                       max(module) module,
                       sum(buffer_gets_delta) logicr,
                       sum(executions_delta) exec,
                       sum(cpu_time_delta) cput,
                       sum(elapsed_time_delta) elap
                  from dba_hist_sqlstat
                 where dbid = 3629726729
                   and instance_number = 1
                   and 7634 < snap_id
                   and snap_id <= 7637
                 group by sql_id) sqt,
               dba_hist_sqltext st
         where st.sql_id(+) = sqt.sql_id
           and st.dbid(+) = 3629726729
           and (SELECT sum(e.VALUE) - sum(b.value)
                  FROM DBA_HIST_SYSSTAT b, DBA_HIST_SYSSTAT e
                 WHERE B.SNAP_ID =7634
                   AND E.SNAP_ID =7637
                   AND B.DBID = 3629726729
                   AND E.DBID = 3629726729
                   AND B.INSTANCE_NUMBER = 1
                   AND E.INSTANCE_NUMBER = 1
                   and e.STAT_NAME = ‘session logical reads‘
                   and b.stat_name = ‘session logical reads‘) > 0
         order by nvl(sqt.logicr, -1) desc, sqt.sql_id)
 where rownum < 65and(rownum <= 10
                   or Total_rate > 1);
# 物理读 TOP 10

select *
  from (select sqt.dskr Physical_Reads,
               sqt.exec Executions,
               decode(sqt.exec, 0, to_number(null), (sqt.dskr / sqt.exec)) Reads_per_Exec ,
               (100 * sqt.dskr) /
               (SELECT sum(e.VALUE) - sum(b.value)
                  FROM DBA_HIST_SYSSTAT b, DBA_HIST_SYSSTAT e
                 WHERE B.SNAP_ID = $P{p_beg_snap}
                   AND E.SNAP_ID = $P{p_end_snap}
                   AND B.DBID = 1273705906
                   AND E.DBID = 1273705906
                   AND B.INSTANCE_NUMBER = 1
                   AND E.INSTANCE_NUMBER = 1
                   and e.STAT_NAME = ‘physical reads‘
                   and b.stat_name = ‘physical reads‘) Total_rate,
               nvl((sqt.cput / 1000000), to_number(null)) CPU_Time_s,
               nvl((sqt.elap / 1000000), to_number(null)) Elapsed_Time_s,
               sqt.sql_id,
               decode(sqt.module, null, null, ‘Module: ‘ || sqt.module) SQL_Module,
               nvl(st.sql_text, to_clob(‘** SQL Text Not Available **‘)) SQL_Text
          from (select sql_id,
                       max(module) module,
                       sum(disk_reads_delta) dskr,
                       sum(executions_delta) exec,
                       sum(cpu_time_delta) cput,
                       sum(elapsed_time_delta) elap
                  from dba_hist_sqlstat
                 where dbid = 1273705906
                   and instance_number = 1
                   and $P{p_beg_snap} < snap_id
                   and snap_id <= $P{p_end_snap}
                 group by sql_id) sqt,
               dba_hist_sqltext st
         where st.sql_id(+) = sqt.sql_id
           and st.dbid(+) = 1273705906
           and (SELECT sum(e.VALUE) - sum(b.value)
                  FROM DBA_HIST_SYSSTAT b, DBA_HIST_SYSSTAT e
                 WHERE B.SNAP_ID = $P{p_beg_snap}
                   AND E.SNAP_ID = $P{p_end_snap}
                   AND B.DBID = 1273705906
                   AND E.DBID = 1273705906
                   AND B.INSTANCE_NUMBER = 1
                   AND E.INSTANCE_NUMBER = 1
                   and e.STAT_NAME = ‘physical reads‘
                   and b.stat_name = ‘physical reads‘) > 0
         order by nvl(sqt.dskr, -1) desc, sqt.sql_id)
 where rownum < 65and(rownum <= 10
                   or Total_rate > 1);
# 消耗CPU TOP 10

select *
  from (select nvl((sqt.elap / 1000000), to_number(null)) Elapsed_Time_s,
               nvl((sqt.cput / 1000000), to_number(null)) CPU_Time_s,
               sqt.exec Executions,
               decode(sqt.exec,
                      0,
                      to_number(null),
                      (sqt.elap / sqt.exec / 1000000)) Elap_per_Exec_s,
               (100 *
               (sqt.elap / (SELECT sum(e.VALUE) - sum(b.value)
                               FROM DBA_HIST_SYSSTAT b, DBA_HIST_SYSSTAT e
                              WHERE B.SNAP_ID = 7396
                                AND E.SNAP_ID = 7399
                                AND B.DBID = 1273705906
                                AND E.DBID = 1273705906
                                AND B.INSTANCE_NUMBER = 1
                                AND E.INSTANCE_NUMBER = 1
                                and e.STAT_NAME = ‘DB time‘
                                and b.stat_name = ‘DB time‘)))/1000 Total_DB_Time_rate,
               sqt.sql_id,
               to_clob(decode(sqt.module,
                              null,
                              null,
                              ‘Module: ‘ || sqt.module)) SQL_Module,
               nvl(st.sql_text, to_clob(‘ ** SQL Text Not Available ** ‘)) SQL_Text
          from (select sql_id,
                       max(module) module,
                       sum(elapsed_time_delta) elap,
                       sum(cpu_time_delta) cput,
                       sum(executions_delta) exec
                  from dba_hist_sqlstat
                 where dbid = 65972167
                   and instance_number = 1
                   and 7396 < snap_id
                   and snap_id <= 7399
                 group by sql_id) sqt,
               dba_hist_sqltext st
         where st.sql_id(+) = sqt.sql_id
           and st.dbid(+) = 1273705906
order by nvl(sqt.cput, -1) desc, sqt.sql_id)
 where rownum < 65
   and (rownum <= 10 or Total_DB_Time_rate > 1);
# 执行时间 TOP 10
select *
  from (select nvl((sqt.elap / 1000000), to_number(null)) Elapsed_Time_s,
               nvl((sqt.cput / 1000000), to_number(null)) CPU_Time_s,
               sqt.exec Executions,
               decode(sqt.exec,
                      0,
                      to_number(null),
                      (sqt.elap / sqt.exec / 1000000)) Elap_per_Exec_s,
               (100 *
               (sqt.elap / (SELECT sum(e.VALUE) - sum(b.value)
                               FROM DBA_HIST_SYSSTAT b, DBA_HIST_SYSSTAT e
                              WHERE B.SNAP_ID = $P{p_beg_snap}
                                AND E.SNAP_ID = $P{p_end_snap}
                                AND B.DBID = 1273705906
                                AND E.DBID = 1273705906
                                AND B.INSTANCE_NUMBER = 1
                                AND E.INSTANCE_NUMBER = 1
                                and e.STAT_NAME = ‘DB time‘
                                and b.stat_name = ‘DB time‘)))/1000 Total_DB_Time_rate,
               sqt.sql_id,
               to_clob(decode(sqt.module,
                              null,
                              null,
                              ‘Module: ‘ || sqt.module)) SQL_Module,
               nvl(st.sql_text, to_clob(‘ ** SQL Text Not Available ** ‘)) SQL_Text
          from (select sql_id,
                       max(module) module,
                       sum(elapsed_time_delta) elap,
                       sum(cpu_time_delta) cput,
                       sum(executions_delta) exec
                  from dba_hist_sqlstat
                 where dbid = 1273705906
                   and instance_number = 1
                   and $P{p_beg_snap} < snap_id
                   and snap_id <= $P{p_end_snap}
                 group by sql_id) sqt,
               dba_hist_sqltext st
         where st.sql_id(+) = sqt.sql_id
           and st.dbid(+) = 1273705906
         order by nvl(sqt.elap, -1) desc, sqt.sql_id)
 where rownum < 65
   and (rownum <= 10 or Total_DB_Time_rate > 1);

查找需要使用绑定变量的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(‘201

• < (转)数据库SQL优化大总结之 百万级数据库优化方案

• mysql 主从及配置 >