数据库日常管理常用sql脚本
来源：互联网 发布：芒果tv php视频解析 编辑：程序博客网 时间：2024/05/22 11:38
/ 目录： ###########查询类########### 关于session: kill session和kill -9 查询session信息以及process信息 查找产生大量物理读的进程 查找产生redo log过快的进程 获取大批量数据操作测试的耗费时间和产生redo量 查询tmep表空间的使用情况 查询哪些回滚段被大量占用 for 8i 关于sql和执行计划: 查看某用户当前执行的sql语句 查看某用户之前执行的sql语句 根据sid或spid查询执行的sql语句 查询执行计划并格式化输出 查找未使用绑定变量产生大量硬解析的语句 查找当前硬解析过多的session 查询各种等待事件对应的sql语句 关于锁: 查询锁 有session调用package导致无法编译 分布式事务锁的处理 关于等待事件 查询等待事件 library cache pin等待事件的处理 cache buffers chains等待事件的处理 db file sequential read等待事件的分析 db file scattered read等待事件的分析 关于trace和event: 设置trace和event 查询audit审计记录 关于监控巡检语句： mon_long_session 长连接的session监控 mon_rbs 占用大量回滚段的监控 mon_ckpt active redo log及其相关session和sql的监控 mon_xatrans 分布式事务锁的监控 mon_swait 等待事件的监控 mon_sqlarea 未使用绑定变量的sql监控 mon_sharepool 占用大量内存的sql监控 mon_redo 产生大量redo log的session和sql监控 mon_temp 占用大量temp表空间的session和sql监控 关于数据库实例： 了解当前ASMM 自动调整的内存参数情况 ###########操作类########### 关于redo log、archive log、checkpoint 加大（或减少）redo log尺寸的操作步骤 关于备份和恢复: 加快实例crash后的恢复速度 redo log header corrupt处理方法 recover命令：完全恢复 recover命令：不完全恢复 recover命令：恢复数据文件或表空间 关于cbo统计信息 快速恢复旧的统计信息 for 9i 快速恢复旧的统计信息 for 10g 搜集统计信息 导入统计信息 关于保留现场信息或oradebug 使用oradebug搜集现场信息 使用rda搜集当前系统信息供GCS工程师分析问题 采用OUTLN技术将正确的执行计划从其它环境导入到生产库 ###########操作系统类########### 关于主机资源消耗 CPU使用情况 设备使用情况 虚拟内存使用情况********************************************/######################################查询类########################################################################关于session#################################*********************************kill session和kill -9***********************************--kill数据库内部的sessionalter system kill session '&SID,&SERIAL#';--生成kill所有active session的脚本select 'alter system kill session ' || '''' || sid || ',' || serial# || '''' || ';'from v$session where status='ACTIVE' and username is not null;--生成按用户名kill所有session的脚本select 'alter system kill session ' || '''' || sid || ',' || serial# || '''' || ';'from v$session where username=upper('&USERNAME';--kill后台所有LOCAL=NO的sessionkill -9 `ps -ef|grep oplpms|grep LOCAL=NO|awk '{print $2}'`--从数据库生成kill所有活动的后台进程的脚本select ' kill -9 ' || spid from (select spid from v$process where addr in(select paddr from v$session where status='ACTIVE' and username is not null));--kill掉造成某一类等待事件的所有活动sessionselect 'alter system kill session ' || '''' || sid || ',' || serial# || '''' || ';' from v$session where status = 'ACTIVE' and sid in (select sid from v$session_wait where event in ('&EVENT_NAME'));******************************************************************************************************************查询session信息以及process信息*********************************--根据username查询sid和后台spidselect a.username,a.sid,a.serial#,b.spid,a.STATUS,a.OSUSER,a.MACHINE,a.PROGRAM from v$session a,v$process b where a.PADDR=b.ADDR and a.username=&USERNAME;--根据后台spid查询sidselect username,sid,serial#,STATUS,OSUSER,MACHINE,PROGRAM from v$session where paddr = (select addr from v$process where spid = &SPID);--根据sid查询后台spidselect spid from v$process where addr = (select paddr from v$session where sid = &SID);--查询自己session的后台spidselect a.username,a.sid,a.serial#,b.spid,a.STATUS,a.OSUSER,a.MACHINE,a.PROGRAM from v$session a,v$process b where a.PADDR=b.ADDR and a.sid=(select distinct sid from v$mystat);*********************************************************************************************************************************查找产生大量物理读的进程********************************* select * from (select * from (select st.sid,st.value,sn.name,s.username,s.logon_time from v$sesstat st,v$statname sn ,v$session s where st.sid=s.sid AND st.statistic#=sn.statistic# and st.value>100000 and s.username is not null and sn.name like '%physical read%' order by 2 desc));***********************************************************************************************************************查找产生redo log过快的进程*********************************col machine format a20col osuser format a20set lines 150select sysdate, se.username, se.sid, se.serial#, se.SQL_HASH_VALUE, se.status, se.machine, se.osuser, round(st.value / 1024 / 1024) redosize, sa.sql_text from v$session se, v$sesstat st, v$sqlarea sa where se.sid = st.sid and st.STATISTIC# = (select STATISTIC# from v$statname where NAME = 'redo size') --and se.username is not null and st.value > 10 * 1024 * 1024 and se.SQL_ADDRESS = sa.ADDRESS and se.SQL_HASH_VALUE = sa.HASH_VALUE order by redosize;*****************************************************************************************************************************获取大批量数据操作测试的耗费时间和产生redo量*********************************set serveroutput on;DECLAREstart_time NUMBER;end_time NUMBER;start_redo_size NUMBER;end_redo_size NUMBER;BEGINstart_time := dbms_utility.get_time;SELECT VALUE INTO start_redo_size FROM v$mystat m,v$statname sWHERE m.STATISTIC#=s.STATISTIC#AND s.NAME='redo size';--transaction start用户脚本--transaction endend_time := dbms_utility.get_time;SELECT VALUE INTO end_redo_size FROM v$mystat m,v$statname sWHERE m.STATISTIC#=s.STATISTIC#AND s.NAME='redo size';dbms_output.put_line('Escape Time:'||to_char(end_time-start_time)||' centiseconds');dbms_output.put_line('Redo Size:'||to_char(end_redo_size-start_redo_size)||' bytes');END;/--如果用户的脚本无法嵌入到如上的匿名块中，则使用如下脚本获取时间点和redo值前后相减即可：SELECT to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),VALUE||' bytes' FROM v$mystat m,v$statname s WHERE m.STATISTIC#=s.STATISTIC# AND s.NAME='redo size';--transaction start执行用户脚本--transaction endSELECT to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),VALUE||' bytes' FROM v$mystat m,v$statname s WHERE m.STATISTIC#=s.STATISTIC# AND s.NAME='redo size';***********************************************************************************************************************************************查询tmep表空间的使用情况*********************************--哪个进程占用临时表空间select s.username, u.tablespace, u.contents, u.extents, u.blocksfrom v$session s, v$sort_usage uwhere s.saddr = u.session_addrand u.contents = 'TEMPORARY'--temp空间使用率select (s.tot_used_blocks/f.total_blocks)*100 as pctusedfrom (select sum(used_blocks) tot_used_blocks from v$sort_segment where tablespace_name='TEMP') s, (select sum(blocks) total_blocks from dba_temp_files where tablespace_name='TEMP') f;--监控temp表空间的增长情况select su.extents, su.segtype, su.sqlhash, s.sid, s.serial#, s.last_call_et, s.username, s.machine, from v$sort_usage su, v$session swhere su.session_addr=s.saddrand su.extents>10;select su.segtype, sum(su.extents) from v$sort_usage su group by su.segtype;9i: V$TEMPSEG_USAGE This view describes temporary segment usage.从9i R2开始v$sort_usage就改名为V$TEMPSEG_USAGE V$TEMPSTAT This view contains information about file read/write statistics.****************************************************************************************************************************查询哪些回滚段被大量占用 for 8i*********************************--查找使用回滚段的进程SELECT s.sid "sid", s.serial# "serial#",s.username "username",s.status, t.start_time, t.xidusn "xidusn", drs.segment_name "segment_name",ds. header_file "file_h", ds.header_block "blk_h"FROM V$session s, V$transaction t, V$rollstat r ,dba_segments ds,dba_rollback_segs drsWHERE s.saddr=t.ses_addrAND t.xidusn=r.usnand r.usn=drs.segment_idand ds.segment_name=drs.segment_nameAND ((r.curext=t.start_uext-1) OR((r.curext=r.extents-1) AND t.start_uext=0))order by r.usn;--查询哪些回滚段被大量占用 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;--查出来结果以后看该rbs是否确实太小，用如下语句调整大小： alter rollback segment r13 storage(maxextents 32765);--查出有 100 个以上 extent 的回滚段Select usn, extents, curext from v$rollstat where extents>100;--对其中返回的每个 usn 检查其中的事务情况：查某个回滚段中的事务情况select t.start_uext, t.used_ublk, t.start_time, s.sid, s.serial#, s.last_call_et, s.username, s.machine from v$transaction t, v$session swhere t.ses_addr=s.saddrand t.xidusn=&usn;--以下语句为查可能导致回滚段持续增长的会话select s.sid, s.serial#, t.start_time, t.xidusn, s.usernamefrom v$session s, v$transaction t , v$rollstat rwhere s.saddr=t.ses_addrand t.xidusn=r.usnand ((r.curext=t.start_uext-1) or((r.curext=r.extents-1) and t.start_uext=0));**************************************************************************************************########################################关于sql和执行计划########################################*********************************查看某用户当前执行的sql语句********************************* set pages 500 set lines 160 break on sid nodup on serial# nodup on user nodup on machine nodup on logontime nodup col machine format a20 col text format a64 col user format a10 col logontime format a10 col sid format 99999 col serial# format 99999 select b.sql_text text,a.sid sid,a.serial# serial#,a.username "user",a.machine machine,to_char(logon_time,'dd/hh24:mi') "logontime" from v$session a,v$sqltext b where a.username like upper('&1') and b.address = a.sql_address and b.hash_value = a.sql_hash_value order by a.sid,a.serial#,b.piece;******************************************************************************************************************************查看某用户之前执行的sql语句********************************* set pages 500 set lines 120 break on sid nodup on serial# nodup on machine nodup col machine format a20 select b.sql_text text, a.sid sid, a.serial# serial#, a.machine machine from v$session a, v$sqltext b where a.username = upper('&1') and b.address = a.prev_sql_addr and b.hash_value = a.prev_hash_value order by a.sid, a.serial#, b.piece;******************************************************************************************************************************查询执行计划并格式化输出*********************************--直接查询library cache中的sql真实的执行计划（9i以上），sql_hash_value 从 v$session 中查到：select '| Operation | PHV/Object Name | Rows | Bytes| Cost |'as "Optimizer Plan:" from dualunion allselect rpad('| '||substr(lpad(' ',1*(depth-1))||operation|| decode(options, null,'',' '||options), 1, 35), 36, ' ')||'|'|| rpad(decode(id, 0, '------------- ' , substr(decode(substr(object_name, 1, 7), 'SYS_LE_', null, object_name) ||' ',1, 30)), 31, ' ')||'|'|| lpad(decode(cardinality,null,' ', decode(sign(cardinality-1000), -1, cardinality||' ', decode(sign(cardinality-1000000), -1, trunc(cardinality/1000)||'K', decode(sign(cardinality-1000000000), -1, trunc(cardinality/1000000)||'M', trunc(cardinality/1000000000)||'G')))), 7, ' ') || '|' || lpad(decode(bytes,null,' ', decode(sign(bytes-1024), -1, bytes||' ', decode(sign(bytes-1048576), -1, trunc(bytes/1024)||'K', decode(sign(bytes-1073741824), -1, trunc(bytes/1048576)||'M', trunc(bytes/1073741824)||'G')))), 6, ' ') || '|' || lpad(decode(cost,null,' ', decode(sign(cost-10000000), -1, cost||' ', decode(sign(cost-1000000000), -1, trunc(cost/1000000)||'M', trunc(cost/1000000000)||'G'))), 8, ' ') || '|' as "Explain plan"from v$sql_plan spwhere sp.hash_value=&SQL_HASH_VALUE;--或者预生成执行计划：EXPLAIN PLAN set statement_id='MYSQL1' FOR--(表示为以下sql语句生成执行计划,不会执行该语句)&SQL语句--格式化输出：select plan_table_output from table(dbms_xplan.display('plan_table',null,'serial'));--查找执行计划版本超过10个的sql语句select sa.sql_text,sa.version_count ,ss.*from v$sqlarea sa,v$sql_shared_cursor ss wheresa.address=ss.KGLHDPAR and sa.version_count > 10 order by sa.version_count ;***************************************************************************************************************************查找未使用绑定变量产生大量硬解析的语句*********************************SELECT substr(sql_text, 1, 40) "SQL", count(*), sum(executions) "TotExecs" FROM v$sqlarea WHERE executions <5 GROUP BY substr(sql_text, 1, 40)HAVING count(*) > 100 ORDER BY 2;--执行了许多硬解析的当前会话select c.sid||','||c.serial#,c.username,b.name,a.value, round((sysdate-c.logon_time)*24) hours_connectedfrom v$sesstat a,v$statname b,v$session cwhere c.sid=a.sid and a.statistic#=b.statistic# and a.value>0 and b.name='parse count (hard)'order by a.value desc;*****************************************************************************************************************************************根据sid或spid查询执行的sql语句*********************************--根据sid查询执行的sql语句select se.username, se.sid, se.serial#, se.osuser, se.machine, se.program, se.logon_time, sa.sql_text from v$session se, v$sqlarea sa where se.SQL_ADDRESS = sa.ADDRESS and se.SQL_HASH_VALUE = sa.HASH_VALUE and se.sid = '&SID';--根据spid查询执行的sql语句select se.username, se.sid, se.serial#, se.osuser, se.machine, se.program, se.logon_time, sa.sql_text from v$session se, v$sqlarea sa, v$process pr where se.SQL_ADDRESS = sa.ADDRESS and se.SQL_HASH_VALUE = sa.HASH_VALUE and se.PADDR=pr.ADDR and pr.spid = '&SPID';*********************************************************************************************************************************查找当前硬解析过多的session*********************************select c.username, c.sid, c.serial#, b.name, a.value, round((sysdate - c.logon_time) * 24) hours_connected from v$sesstat a, v$statname b, v$session c where c.sid = a.sid and a.statistic# = b.statistic# and a.value > 1000 and b.name = 'parse count (hard)' order by a.value;******************************************************************************************************************************查询各种等待事件对应的sql语句*********************************set pages 500set lines 160break on sid nodup on serial# nodup on user nodup on machine nodup on logontime nodupcol machine format a20col text format a64col user format a10col logontime format a10col sid format 99999col serial# format 99999select b.sql_text text, a.sid sid, a.serial# serial#, a.username "user", a.machine machine, to_char(logon_time,'dd/hh24:mi') "logontime"from v$session a,v$sqltext b, v$session_wait cwhere a.sid = c.sid and c.event = '&EVENT_NAME' and b.address = a.sql_address and b.hash_value = a.sql_hash_valueorder by a.sid,a.serial#,b.piece;***********************************************************************************************#######################################关于锁#######################################补充：latch 到对象*******************************************查询锁****************************************--查看整个instance的锁情况select * from dba_locks;--查看整个instance的dml锁情况select * from v$lock where type in ('TX','TM');select * from v$lock where type in ('TX','TM') and sid='&SID';--查看session锁定的对象select * from v$locked_object;--查询锁的holder和waiter：select decode(request, 0, 'Holder:', 'Waiter:') || sid, id1, id2, lmode, request, type from v$lock where (id1, id2, type) in (select id1, id2, type from v$lock where request > 0) order by id1, request;--查询表是否有锁： select oracle_username,owner,object_name,object_type,session_id,locked_mode from v$locked_object v, dba_objects d where v.object_id = d.object_id and object_name=upper('&1') order by object_name ;--查找所有被锁的对象： select oracle_username,owner,object_name,object_type,session_id,locked_mode,l.type,l.block from v$locked_object v, dba_objects d,v$lock l where l.block>0 and v.session_id=l.sid and d.object_id=v.object_id order by object_name,l.block ;--查看DML LOCK情况和锁定的对象情况：select a.sid, decode(a.type, 'MR', 'Media Recovery', 'RT', 'Redo Thread', 'UN', 'User Name', 'TX', 'Transaction', 'TM', 'DML', 'UL', 'PL/SQL User Lock', 'DX', 'Distributed Xaction', 'CF', 'Control File', 'IS', 'Instance State', 'FS', 'File Set', 'IR', 'Instance Recovery', 'ST', 'Disk Space Transaction', 'IR', 'Instance Recovery', 'ST', 'Disk Space Transaction', 'TS', 'Temp Segment', 'IV', 'Library Cache Invalidation', 'LS', 'Log Start or Switch', 'RW', 'Row Wait', 'SQ', 'Sequence Number', 'TE', 'Extend Table', 'TT', 'Temp Table', a.type) lock_type, decode(a.lmode, 0, 'None', /* Mon Lock equivalent */ 1, 'Null', /* N */ 2, 'Row-S (SS)', /* L */ 3, 'Row-X (SX)', /* R */ 4, 'Share', /* S */ 5, 'S/Row-X (SSX)', /* C */ 6, 'Exclusive', /* X */ to_char(a.lmode)) mode_held, decode(a.request, 0, 'None', /* Mon Lock equivalent */ 1, 'Null', /* N */ 2, 'Row-S (SS)', /* L */ 3, 'Row-X (SX)', /* R */ 4, 'Share', /* S */ 5, 'S/Row-X (SSX)', /* C */ 6, 'Exclusive', /* X */ to_char(a.request)) mode_requested, a.ctime lock_time, to_char(a.id1) lock_id1, c.object_name lock_object_name, c.object_type lock_object_type, to_char(a.id2) lock_id2from v$lock a,dba_objects c where (id1,id2) in (select b.id1, b.id2 from v$lock b where b.id1=a.id1 and b.id2=a.id2 ) and a.type in ('TX','TM') and a.id1=c.object_id(+);--存在多个BLOCKER时，查出源头的BLOCKER：SELECT * FROM V$LOCK WHERE SID IN (SELECT SID SESSION_ID FROM V$LOCK WHERE BLOCK > 0 MINUS SELECT W.SID SESSION_ID FROM V$SESSION_WAIT W WHERE W.EVENT = 'enqueue');--查看BLOCKER对应的SESSION的状态和等待事件：SELECT S.SID, S.USERNAME, S.STATUS, W.EVENT, L.TYPE, L.ID1, L.ID2, L.LMODE, L.CTIME, L.BLOCK FROM V$SESSION S, V$SESSION_WAIT W, V$LOCK L WHERE S.SID = W.SID AND S.SID = L.SID AND L.BLOCK > 0;--查出WAITER等待的记录行： --首先查出WAITER等待的资源： SELECT ROW_WAIT_OBJ# , ROW_WAIT_FILE# , ROW_WAIT_BLOCK# , ROW_WAIT_ROW# FROM V$SESSION WHERE SID IN (SELECT DISTINCT SID FROM V$LOCK WHERE REQUEST > 0 ) AND ROW_WAIT_OBJ# <> -1;--再根据OBJECT_ID得出具体的对象属主和名称：SELECT OWNER,OBJECT_NAME,OBJECT_TYPE FROM DBA_OBJECTS WHERE OBJECT_ID=< ROW_WAIT_OBJ#>--根据以上得到的OBJECT_ID,FILE_ID,BLOCK_ID,ROW#，就构成标准的ROWID，查出记录行： SELECT * FROM < OWNER > . < OBJECT_NAME > WHERE ROWID = DBMS_ROWID.ROWID_CREATE(1, ROW_WAIT_OBJ#, ROW_WAIT_FILE#, ROW_WAIT_BLOCK#, ROW_WAIT_ROW#);***************************************************************************************************************************有session调用package导致无法编译*********************************--编译package被锁,可以查询v$access和v$session确定哪个用户在调用这个packageselect b.sql_text text,a.sid sid ,a.serial# sria#,a.username username, c.type type,a.machine machinefrom v$session a ,v$sqltext b ,v$access cwhere c.object=upper('&OBJECT_NAME')and c.type in ('TABLE','PACKAGE','PROCEDURE','FUNCTION','PACKAGE BODY')and a.sid=c.sidand b.address = a.sql_addressand b.hash_value = a.sql_hash_valueorder by a.sid,a.serial#,b.piece;--直接生成kill session脚本select username,'alter system kill session '''||sid||','||serial#||''';' from v$session where sid in(select distinct sid from v$access where object in('&OBJECT_NAME'))************************************************************************************************************************************分布式事务锁的处理*********************************select a.local_tran_id,statu from dba_2pc_pending a where state='prepared';处理：rollback force '&LOCAL_TRAN_ID';commit;execute DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('&LOCAL_TRAN_ID');commit;************************************************************************************************#######################################关于等待事件#######################################*******************************************查询等待事件****************************************select sw.seq#, sw.sid || ',' || s.serial# sids, s.username, sw.event, sw.P1, sw.p2, sw.p3, sw.wait_time "WAIT", sw.state, sw.seconds_in_wait sec, s.status, to_char(s.logon_time, 'dd/hh24:mi:ss') logon_time, s.MACHINE, s.TERMINAL, s.PROGRAM --,sa.SQL_TEXT from v$session s, v$session_wait sw--,v$sqlarea sa where sw.sid = s.sid --and s.SQL_ADDRESS=sa.ADDRESS --and s.SQL_HASH_VALUE=sa.HASH_VALUE and s.username is not null and sw.event not like '%SQL*Net%' and sw.event not like 'PX Deq%' and sw.event not like 'rdbms ipc message' order by sw.event, s.username;**********************************************************************************************************************************library cache pin等待事件的处理*********************************在后台sys用户下执行：select s.sid || ',' || s.serial# sid_serial, kglpnmod "mode held", kglpnreq "request" from sys.x$kglpn p, v$session s where p.kglpnuse = s.saddr and kglpnhdl = (select p1raw from v$session_wait where sid = &SID_IN_LIBRARY_CACHE_PIN);或者：select sid Holder ,KGLPNUSE Sesion , KGLPNMOD Held, KGLPNREQ Req from x$kglpn , v$session where KGLPNHDL in (select p1raw from v$session_wait where wait_time=0 and event like 'library%') and KGLPNMOD <> 0 and v$session.saddr=x$kglpn.kglpnuse ;或者： select sql_text from v$sqlarea where (v$sqlarea.address,v$sqlarea.hash_value) in (select sql_address,sql_hash_value from v$session where sid in ( select sid from x$kglpn , v$session where KGLPNHDL in (select p1raw from v$session_wait where wait_time=0 and event like 'library%') and KGLPNMOD <> 0 and v$session.saddr=x$kglpn.kglpnuse );查到held>0 的sid，如果local=no ，请沟通是否可以kill掉这个进程**********************************************************************************************************************************cache buffers chains等待事件的处理*********************************查询等待事件的类型是否是latch free：select sw.sid || ',' || s.serial# sids, s.username, sw.event, sw.P1, sw.p2, sw.p3, sw.p1raw, sw.wait_time "WAIT", sw.state, sw.seconds_in_wait sec, s.status, to_char(s.logon_time, 'dd/hh24:mi:ss') log_time from v$session s, v$session_wait sw where s.username is not null and sw.sid = s.sid and sw.event not like '%SQL*Net%' and sw.event not like 'PX Deq%' order by sw.event;如果是latch free，则其中p2字段的值表示latch number，据此可以查出是什么原因引起的latch free：select * from v$latchname where latch#=&P2;如果等待的latch是cache buffers chains，则需要根据p1raw查出被争用的hot block和segment名称：--在后台sys用户下执行，查找热块select /*+ RULE */ 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 x.hladdr = '&P1RAW' and e.file_id = x.file# and x.hladdr = l.addr and x.dbablk between e.block_id and e.block_id + e.blocks - 1 order by x.tch desc;column segment_name format a30select distinct e.owner,e.segment_name,e.segment_typefrom dba_extents e, (select * from (select addr,ts#,file#,dbarfil,dbablk,tch from x$bh order by tch desc )where rownum<11) b where e.relative_fno=b.dbarfil and e.block_id<=b.dbablk and e.block_id+e.blocks>b.dbablk;--查找产生热块的sql：column segment_name format a35select /*+ rule */ hash_value,sql_text from v$sqltextwhere (hash_value,address ) in ( select a.hash_value,a.address from v$sqltext a , (select distinct e.owner,e.segment_name,e.segment_type from dba_extents e, (select * from (select addr,ts#,file#,dbarfil,dbablk,tch from x$bh order by tch desc )where rownum<11) b where e.relative_fno=b.dbarfil and e.block_id<=b.dbablk and e.block_id+e.blocks>b.dbablk ) b where a.sql_text like '%'||b.segment_name||'%' and b.segment_type='TABLE') order by hash_value,address,piece;找到latch holder所在session的sid和serial#，考虑是否可以kill掉，缓解数据库的压力：--这个latchhold变化得非常快，每刷新一次都会变化select a.username, a.sid, a.serial#, a.status, b.pid, b.laddr, b.name from v$session a, v$latchholder b where a.sid = b.sid;************************************************************************************************************************************db file sequential read等待事件的分析*********************************--当等待事件为db file sequential read时，P1对应file_id，P2对应&block_id--通过下面这个语句可以查询到正在等待什么对象 select owner,segment_name,segment_type from dba_extents where file_id = &file_id and &block_id between block_id and block_id+blocks-1;****************************************************************************************************************************************db file scattered read等待事件的分析*********************************--当等待事件是db file scattered read时，用以下语句检查执行计划： select hash_value,child_number, lpad(' ',2*depth)||operation||' '||options||decode(id,0,substr(optimizer,1,6)||' Cost='||to_char(cost)) operation, object_name object,cost,cardinality,round(bytes/1024) kbytes from v$sql_plan where hash_value in (select a.sql_hash_value from v$session a,v$session_wait b where a.sid=b.sid and b.event='db file scattered read') order by hash_value,child_number,id;*******************************************************************************************************#############################################关于trace和event#############################################*********************************设置trace和event*********************************--设置autotraceset autotrace onSET AUTOT[RACE] {OFF | ON | TRACE[ONLY] } [EXP[LAIN]] [STAT[ISTICS] ]--设置sql tracealter session set sql_trace=true; --my sessionexecute dbms_system.set_sql_trace_in_session(&SID,&SERIAL#,true); --other session--设置10046 eventAlter session set events '10046 trace name context forever,level 12'; --my sessionalter session set events '10046 trace name context off'; --closeExec dbms_system.set_ev(&SID,&SERIAL#,10046,12,''); --open other sessionExec dbms_system.set_ev(&SID,&SERIAL#,10046,0,''); --close other sessionALTER SYSTEM SET EVENTS='10046 trace name context forever, level 4' SCOPE=spfile; --system level*******************************************************************************************************************查询audit审计记录*********************************查看数据表修改的审计记录Select * from dba_audit_object where obj_name='&OBJ_NAME';查看用户登陆的审计记录Select * from dba_audit_session where username='&USERNAME';查看审计策略SELECT * FROM DBA_STMT_AUDIT_OPTS;查询某段时间某用户的登陆记录--conn dbqua@cmmrep--如果是两周以前的数据，要到isw中心库取(DBQDATA.DBQC$SESSION_COLLECT)Select SNAP_ID, SNAP_TIME, SID, SERIAL#, USERNAME, STATUS, MACHINE, PROGRAM, LOGON_TIME from dbq$session_detail where snap_time >= to_date('20071122', 'yyyymmdd') and snap_time < to_date('20071123', 'yyyymmdd') and username = 'EPRPNET' ORDER BY SNAP_ID;***********************************************************************************#####################################################关于监控巡检语句#####################################################*********************************mon_long_session 长连接的session监控*********************************select a.sid, a.serial#, a.machine, a.osuser, a.username, trunc(a.last_call_et / 60) last_call_et, to_char(a.LOGON_TIME, 'yyyymmdd hh24:mi:ss') logon_time, trunc((sysdate - a.logon_time) * 24 * 60) remain_time, b.sql_text SQL from v$session a, v$sqltext b where a.status = 'ACTIVE' and username not in ('SYS') and a.last_call_et / 60 >= 240 and a.username <> 'CIF_AQ' and a.sql_address = b.address and a.username is not null order by a.username, a.last_call_et desc, a.sid, b.address, b.piece;***************************************************************************************************************************************mon_rbs 占用大量回滚段的监控*********************************select s.sid, s.serial#, s.machine, s.OSUSER, s.username, s.status, round(s.last_call_et / 60) "IDLE_Min", round((sysdate - to_date(t.start_time, 'mm/dd/yy hh24:mi:ss')) * 24 * 60) "Trans_Min", r.usn, round(r.RSSIZE / 1024 / 1024) rbssize_M, round(r.OPTSIZE / 1024 / 1024) optsize_M, s.logon_time, s.program, q.sql_text, q.hash_value FROM V$session s, V$transaction t, V$rollstat r,v$sqlarea q WHERE s.saddr = t.ses_addr AND t.xidusn = r.usn AND s.sql_address=q.address AND s.sql_hash_value=q.hash_value AND ((((r.curext = t.start_uext - 1) OR ((r.curext = r.extents - 1) AND t.start_uext = 0)) and s.last_call_et /60 > 30 and r.rssize>r.optsize and r.rssize > 50*1024*1024) or r.rssize >100*1024*1024) order by last_call_et desc;*******************************************************************************************************************************mon_ckpt active redo log及其相关session和sql的监控*********************************select status,count(*) from v$log where status in ('ACTIVE','CURRENT') group by status;*****************************************************************************************************************************************************mon_xatrans 分布式事务锁的监控*********************************select a.local_tran_id,statu from dba_2pc_pending a where state='prepared';处理：rollback force '&LOCAL_TRAN_ID';commit;execute DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('&LOCAL_TRAN_ID');commit;*********************************************************************************************************************************mon_swait 等待事件的监控*********************************select sw.seq#,sw.sid||','||s.serial# sids,s.username,sw.event,sw.P1,sw.p2,sw.p3,sw.wait_time "WAIT", sw.state,sw.seconds_in_wait sec,s.status,to_char(s.logon_time,'dd/hh24:mi:ss') logon_time from v$session s,v$session_wait sw where sw.sid =s.sid and s.username is not null and sw.event not like '%SQL*Net%' and sw.event not like 'PX Deq%' and sw.event not like 'rdbms ipc message' and sw.event not like 'queue messages' order by sw.event,s.username ;***************************************************************************************************************************mon_sqlarea 未使用绑定变量的sql监控*********************************select substr(sql_text, 1, 50) "SQL", count(*) cnt, sum(sharable_mem) "TotExecs" FROM v$sqlarea WHERE executions =1 GROUP BY substr(sql_text, 1, 50)HAVING count(*) > 5000 ORDER BY 2;**************************************************************************************************************************************mon_sharepool 占用大量内存的sql监控*********************************select se.sid,se.SERIAL#,pr.SPID,se.osuser,se.MACHINE,sq.SHARABLE_MEM/1024/1024 ,se.PROGRAM,sq.SQL_TEXTfrom v$sqlarea sq,v$session se,v$process prwhere se.PADDR=pr.ADDRand ((se.SQL_ADDRESS=sq.ADDRESS and se.SQL_HASH_VALUE=sq.HASH_VALUE) or (se.PREV_SQL_ADDR=sq.ADDRESS and se.PREV_HASH_VALUE=sq.HASH_VALUE))and sq.SHARABLE_MEM>20*1024*1024order by sq.SHARABLE_MEM/1024/1024;**************************************************************************************************************************************mon_redo 产生大量redo log的session和sql监控*********************************select se.username, se.sid, se.serial#, pr.spid, se.status, se.machine, se.osuser, round(st.value / 1024 / 1024) redosize, sa.sql_text from v$session se, v$sesstat st, v$sqlarea sa ,v$process pr where se.sid = st.sid and st.STATISTIC# = (select STATISTIC# from v$statname where NAME = 'redo size') and se.username is not null and st.value > 50 * 1024 * 1024 and se.SQL_ADDRESS = sa.ADDRESS and se.SQL_HASH_VALUE = sa.HASH_VALUE and se.paddr=pr.addr order by redosize;**********************************************************************************************************************************************mon_temp 占用大量temp表空间的session和sql监控*********************************select su.extents, su.segtype, su.sqlhash, se.sid, se.serial#, se.last_call_et, se.username, se.machine ,sa.sql_text from v$sort_usage su, v$session se ,v$sqlarea sawhere su.session_addr=se.saddr and se.SQL_ADDRESS = sa.ADDRESS and se.SQL_HASH_VALUE = sa.HASH_VALUE and su.extents>10;select su.segtype, sum(su.extents) from v$sort_usage su group by su.segtype;***************************************************************************************************************#################################关于数据库实例######################################*********************************了解当前ASMM 自动调整的内存参数情况*********************************column "Parameter" format a20 truncatecolumn size_m format 9999select a.ksppinm "Parameter",c.ksppstvl/1048576 size_mfrom sys.x$ksppi a, sys.x$ksppcv b, sys.x$ksppsv cwhere a.indx = b.indx and a.indx = c.indxand a.ksppinm in ('__shared_pool_size','shared_pool_size','__large_pool_size','large_pool_size','__db_cache_size','db_cache_size','__streams_pool_size','streams_pool_size','__java_pool_size','java_pool_size','_kghdsidx_count','sga_target','sga_max')/*****************************************************************************************************######################################操作类########################################################################关于redo log、archive log、checkpoint######################################*********************************加大（或减少）redo log尺寸的操作步骤*********************************(1)查询哪个redo log可以drop仅 ARCHIVED='YES' AND STATUS='INACTIVE' 的可以drop，这表示该redo log已经被归档且当前未使用。未完成归档的，或者状态为ACTIVE和CURRENT的都不允许drop。select * from v$log;(2)删除旧的redo logalter database drop logfile group 1;(3)添加新size的redo logalter database add logfile <'/paic/sx/ims/data/oradata/ims/redo07.log'> size 400m;(4)检查v$log确认操作效果select * from v$log;******************************************************************************************************#################################关于备份和恢复######################################*********************************加快实例crash后的恢复速度***********************************(1)由当前spfile文件生成新的参数文件，并在其中设置数据库参数： Parallel_execution_message_size=16384 _parallel_min_message_pool=8192000(2)关闭并重启数据库至 mount 状态Shutdown abortStartup mount;recover database parallel 8;(3)打开数据库Alter database open;******************************************************************************************************************************redo log header corrupt处理方法***********************************(1)查询redo log的情况，确定损坏的log groupselect * from v$log;select * from v$logfile;(2)切换redo log，根据剩余可用的redo log，不要切换多次alter system switch logfile;(3)清掉已经坏的redo logalter database clear unarchived logfile group <&损坏的log group>;--检查结果select * from v$log;(4)重设log archive destalter system archive log all;alter system set log_archive_dest_1='LOCATION=/paic/hq/gccsu/log/gccsu';--检查select * from v$log;(5)手工归档alter system archive log all;(6)重建损坏的logfile group 5alter database drop logfile group <5需要修改>;select * from v$logfile;alter database add logfile '/paic/hq/gccsu/data/oradata/gccsu/redo11.log' size 100m;***********************************************************************************************************************************recover命令：完全恢复***********************************(1)确定需要恢复的数据文件select file#,error,change# from v$recover_file;(2)恢复还原的数据文件Recover database;**************************************************************************************************************************recover命令：不完全恢复***********************************--基于时间的恢复Alter database recover until time '2002-01-04:09:07:10';--基于SCN的恢复Select * from v$log_history; --根据sequence#和first_time确定change#Alter database recover until change 8175667922546 using backup controlfile;--基于取消的恢复Alter database recover until cancel using backup controlfile;****************************************************************************************************************************recover命令：恢复数据文件或表空间***********************************Alter database recover [from /path] datafile 5;Alter database recover tablespace tools;*****************************************************************************************************#####################################################关于cbo统计信息#####################################################*********************************快速恢复旧的统计信息 for 9i*********************************(1)创建统计信息备份表（如果已有，则无需创建）execute dbms_stats.create_stat_table(ownname => 'dbmgr',stattab => 'stat_bak_all');(2)按备份现有的统计信息execute dbms_stats.export_table_stats(ownname => 'PA18CLM',tabname => 'T_DISPATCH',stattab => 'stat_bak_all',statown => 'dbmgr',cascade => true);(3)查出备份的最近一次统计信息收集的statidselect distinct statid from dbstats.stab__pa18clm where c1='T_DISPATCH';(4)按statid恢复表的统计信息，同时失效现有的执行计划execute dbms_stats.import_table_stats(ownname => 'pa18clm',tabname => 'T_DISPATCH',stattab => 'stab__pa18clm',statown => 'dbstats',cascade => true,no_invalidate => false,statid => '640--07-12-22 01:31');(5)如果执行计划没有恢复，则按以上方法备份和恢复其它表的统计信息execute dbms_stats.import_table_stats(ownname => 'pa18clm',tabname => 'T_DISPATCH_DETAIL',stattab => 'stab__pa18clm',statown => 'dbstats',cascade => true,no_invalidate => false,statid => '640--07-12-22 01:31');******************************************************************************************************************************快速恢复旧的统计信息 for 10g*********************************(1)检查该table最近几次统计信息收集的信息select owner,table_name,stats_update_time from dba_tab_stats_history where table_name ='SICS_INSCARD_CLIENT_INFO';OWNER TABLE_NAME STATS_UPDATE_TIME---------- ------------------------- -------------------------------SICSDATA SICS_INSCARD_CLIENT_INFO 17-DEC-07 10.16.50.435300 PM +08:00SICSDATA SICS_INSCARD_CLIENT_INFO 18-DEC-07 01.47.32.624219 PM +08:00SICSDATA SICS_INSCARD_CLIENT_INFO 18-DEC-07 01.56.19.437726 PM +08:00--其中STATS_UPDATE_TIME表示的是收集的时间和收集的ID(stat_id)(2)要将该表的统计信息恢复到17号的状态dbms_stats.restore_table_stats(ownname => 'SICSDATA', tabname => 'SICS_INSCARD_CLIENT_INFO', as_of_timestamp => '17-DEC-07 10.16.50.435300 PM +08:00', no_invalidate => FALSE);******************************************************************************************************************************搜集统计信息 for 10g*********************************--设置默认收集参数为不收集直方图：execute dbms_stats.set_param('METHOD_OPT','FOR ALL COLUMNS SIZE 1');select DBMS_STATS.GET_PARAM('METHOD_OPT') from dual;--第一次使用手工收集统计信息：execute dbms_stats.gather_database_stats(method_opt=>'FOR ALL COLUMNS SIZE 1');--搜集schema的统计信息exec dbms_stats.gather_schema_stats(ownname => '&USERNAME', method_opt => 'FOR ALL COLUMNS SIZE 1', degree => 8, cascade => TRUE );--收集用户表（包括索引）的统计信息，同时设置为不收集直方图exec DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'&OWNER',TABNAME=>'&TABLE_NAME',METHOD_OPT=>'FOR ALL COLUMNS SIZE 1',NO_INVALIDATE=>FALSE,CASCADE=>TRUE);--生成批次搜集的脚本select 'exec dbms_stats.gather_table_stats(''' || owner || ''', ''' || table_name || ''', method_opt=>''FOR ALL COLUMNS SIZE 1'',NO_INVALIDATE=>FALSE,CASCADE=>TRUE);'from dba_tables where owner not in ('SYS','SYSTEM');***********************************************************************************************************************导入统计信息***************************************已将luhz0的imsqueue.ims_output_queue_table的统计信息导入luzz0为例：(1) conn dbmgr@luhz0exec dbms_stats.create_stat_table('DBMGR','EXP_STATS_TMP');EXEC dbms_stats.export_table_stats(ownname => 'IMSQUEUE',tabname => 'IMS_OUTPUT_QUEUE_TABLE',stattab => 'EXP_STATS_TMP',cascade => TRUE,statown => 'DBMGR');(2) logon to luhz0.db.paic.com.cncd $HOME/tmpexp dbmgr tables=EXP_STATS_TMP file=queue_stats_luhz0.dmp log=exp.log(3) logon to luzz0.db.paic.com.cncd $HOME/tmpscp user@luhz0.db.paic.com.cn:/paic/hz/lbs/data/opluhz0/tmp/queue_stats_luhz0.dmp ./imp dbmgr file=queue_stats_luhz0.dmp full=y log=imp.log(4) conn dbmgr@luzz0select to_char(last_analyzed,'yyyy-mm-dd hh24:mi:ss') from dba_tab_statistics where owner='IMSQUEUE' and table_name='IMS_OUTPUT_QUEUE_TABLE';exec dbms_stats.import_table_stats(ownname => 'IMSQUEUE',tabname => 'IMS_OUTPUT_QUEUE_TABLE',stattab => 'EXP_STATS_TMP',statown => 'DBMGR',cascade => TRUE,no_invalidate => FALSE,force => TRUE);select to_char(last_analyzed,'yyyy-mm-dd hh24:mi:ss') from dba_tab_statistics where owner='IMSQUEUE' and table_name='IMS_OUTPUT_QUEUE_TABLE';--是否锁定视情况而定：exec dbms_stats.lock_table_stats('IMSQUEUE','IMS_OUTPUT_QUEUE_TABLE');**************************************************************************************#################################################关于保留现场信息或oradebug#################################################*********************************使用oradebug搜集现场信息***********************************(1).Systemstate dump-login sqlplus internal-oradebug setmypid-oradebug unlimit-oradebug dump systemstate 10-repeat 3 times with 5 minutes interval(2).Hang analyze- login sqlplus internal- oradebug setmypid- oradebug unlimit-oradebug dump hanganalyze 10(3).errorstack dump- login sqlplus internal- oradebug setospid <suspected process>- oradebug unlimit-oradebug dump errorstack 3(4).processstate dump- login sqlplus internal- oradebug setospid <suspected process>- oradebug unlimit-oradebug dump processstate 10*******************************************************************************************************************************使用rda搜集当前系统信息供GCS工程师分析问题***********************************(1)获取rda工具并解压，可以从metalink下载，Doc ID: Note:314422.1unzip rda.zipcd rda(2)修改权限chmod +x rda.sh(3)初始化，需要提供dba权限的用户（能够用connect user_name AS SYSDBA连接数据库）./rda.sh -S(4)运行脚本，得到html的输出结果./rda.sh -v*****************************************************************************************************************************采用OUTLN技术将正确的执行计划从其它环境如测试库导入到生产库************************************************************************************************************以下操作务必在sqlplus工具中进行，不能在其它工具中进行。1在生产库获得sql hash value，并且获取当前执行计划2 找到有好的执行计划其它环境如测试库3 如果没有好的执行计划需要想办法在其它环境制造出好的执行计划1 在生产库获得该top sql的sql textselect sql_text ||to_char(length(sql_text)) sql_text from v$sqltext where hash_value=2092045661order by piece;SQL_TEXT-------------------------------------------------------------------------------------------------------- SELECT OB_RESULT_SEQ obResultSeq,rs.TASK_CODE taskCode, rs64.SPECIAL_CASE_CODE specialCaseCode,rs.DATA_SEQ dataSeq, rs.Q64UESTIONNAIRE_CODE questionnaireCode,to_char(rs.OB_DATE,'yyyy-mm-64dd hh24:mi:ss') obDate , rs.OB_TSR obTsr,rs.OB_RESULT_STATE 64obResultState, rs.REJECT_REASON rejectReason,rs.REMARK remar64k,rs.RECORD_NO recordNo, rs.DATA_STATE dataState,rs.BEGIN_PR64OCESS_TIME beginProcessTime, rs.END_PROCESS_TIME endProcessT64ime,rs.BEGIN_CALL_TIME beginCallTime, rs.END_CALL_TIME endCa64llTime,rs.OB_TEL obTel, rs.OB_TEL_TYPE obTelType,rs.OB_TYPE 64obType,rs.OB_TIMES obTimes , po.PARTY_NO partyNo ,sp.S64PECIAL_CASE_NAME specialCaseName, po.BATCH batch , po.TABLE64_ID tableId , drs.state_name FROM c_ob_result rs , c_obd_commo64n_info po , c_special_case_info sp ,c_ob_data_result_tbl64 drs WHERE rs.data_seq = po.data_seq AND rs.special_64case_code = sp.special_case_code AND rs.ob_result_state = drs.64state_code and po.CLIENT_NO = :1 and64 rs.ob_date > sysdate - :2 ORDER BY rs.ob_resu64lt_seq desc 142 取到SQL后在UltraEdit中整理sql ：去掉每行尾部长度的数字，从第2行开始复制每一行粘贴到第一行的末尾，把整个sql粘贴成一行，注意首尾空格绝对不能丢失，在sql尾部增加一个;分号。 SELECT OB_RESULT_SEQ obResultSeq,rs.TASK_CODE taskCode, rs.SPECIAL_CASE_CODE specialCaseCode,rs.DATA_SEQ dataSeq, rs.QUESTIONNAIRE_CODE questionnaireCode,to_char(rs.OB_DATE,'yyyy-mm-dd hh24:mi:ss') obDate , rs.OB_TSR obTsr,rs.OB_RESULT_STATE obResultState, rs.REJECT_REASON rejectReason,rs.REMARK remark,rs.RECORD_NO recordNo, rs.DATA_STATE dataState,rs.BEGIN_PROCESS_TIME beginProcessTime, rs.END_PROCESS_TIME endProcessTime,rs.BEGIN_CALL_TIME beginCallTime, rs.END_CALL_TIME endCallTime,rs.OB_TEL obTel, rs.OB_TEL_TYPE obTelType,rs.OB_TYPE obType,rs.OB_TIMES obTimes , po.PARTY_NO partyNo ,sp.SPECIAL_CASE_NAME specialCaseName, po.BATCH batch , po.TABLE_ID tableId , drs.state_name FROM c_ob_result rs , c_obd_common_info po , c_special_case_info sp ,c_ob_data_result_tbl drs WHERE rs.data_seq = po.data_seq AND rs.special_case_code = sp.special_case_code AND rs.ob_result_state = drs.state_code and po.CLIENT_NO = :1 and rs.ob_date > sysdate - :2 ORDER BY rs.ob_result_seq desc ;3 在其它环境如测试库中解锁outln用户并修改密码及授权alter user outln identified by outln account unlock;grant create any outline to outln;grant drop any outline to outln;grant all on plan_table to outln;--如果没有plan_table，则以 sys 用户执行以下操作：--@?/rdbms/admin/utlxplan.sql;--create public synonym plan_table for plan_table;--grant select ,insert, update, delete on plan_table to public;4 在其它环境如测试库使用DBA角色用户dbmgr,dba实名,sys,system或表属主用户授权该SQL涉及到的所有表及视图的select权限给outln用户，例如：grant select on icssobdata.c_ob_result to outln;grant select on icssobdata.c_obd_common_info to outln;grant select on icssobdata.c_special_case_info to outln;grant select on icssobdata.c_ob_data_result_tbl to outln;3 将整理出的SQL在其它环境如测试库确认执行计划是否是好的执行计划：explain plan for 上面整理后的语句,举例：explain plan for SELECT OB_RESULT_SEQ obResultSeq,rs.TASK_CODE taskCode, rs.SPECIAL_CASE_CODE specialCaseCode,rs.DATA_SEQ dataSeq, rs.QUESTIONNAIRE_CODE questionnaireCode,to_char(rs.OB_DATE,'yyyy-mm-dd hh24:mi:ss') obDate , rs.OB_TSR obTsr,rs.OB_RESULT_STATE obResultState, rs.REJECT_REASON rejectReason,rs.REMARK remark,rs.RECORD_NO recordNo, rs.DATA_STATE dataState,rs.BEGIN_PROCESS_TIME beginProcessTime, rs.END_PROCESS_TIME endProcessTime,rs.BEGIN_CALL_TIME beginCallTime, rs.END_CALL_TIME endCallTime,rs.OB_TEL obTel, rs.OB_TEL_TYPE obTelType,rs.OB_TYPE obType,rs.OB_TIMES obTimes , po.PARTY_NO partyNo ,sp.SPECIAL_CASE_NAME specialCaseName, po.BATCH batch , po.TABLE_ID tableId , drs.state_name FROM c_ob_result rs , c_obd_common_info po , c_special_case_info sp ,c_ob_data_result_tbl drs WHERE rs.data_seq = po.data_seq AND rs.special_case_code = sp.special_case_code AND rs.ob_result_state = drs.state_code and po.CLIENT_NO = :1 and rs.ob_date > sysdate - :2 ORDER BY rs.ob_result_seq desc ;select * from table(dbms_xplan.display);6 在有好的执行计划的其它环境如测试库创建outline ,如：Create outline <outline名字stg_OB_RESULT_SEQ> for category special on SELECT OB_RESULT_SEQ obResultSeq,rs.TASK_CODE taskCode, rs.SPECIAL_CASE_CODE specialCaseCode,rs.DATA_SEQ dataSeq, rs.QUESTIONNAIRE_CODE questionnaireCode,to_char(rs.OB_DATE,'yyyy-mm-dd hh24:mi:ss') obDate , rs.OB_TSR obTsr,rs.OB_RESULT_STATE obResultState, rs.REJECT_REASON rejectReason,rs.REMARK remark,rs.RECORD_NO recordNo, rs.DATA_STATE dataState,rs.BEGIN_PROCESS_TIME beginProcessTime, rs.END_PROCESS_TIME endProcessTime,rs.BEGIN_CALL_TIME beginCallTime, rs.END_CALL_TIME endCallTime,rs.OB_TEL obTel, rs.OB_TEL_TYPE obTelType,rs.OB_TYPE obType,rs.OB_TIMES obTimes , po.PARTY_NO partyNo ,sp.SPECIAL_CASE_NAME specialCaseName, po.BATCH batch , po.TABLE_ID tableId , drs.state_name FROM c_ob_result rs , c_obd_common_info po , c_special_case_info sp ,c_ob_data_result_tbl drs WHERE rs.data_seq = po.data_seq AND rs.special_case_code = sp.special_case_code AND rs.ob_result_state = drs.state_code and po.CLIENT_NO = :1 and rs.ob_date > sysdate - :2 ORDER BY rs.ob_result_seq desc ;7 在有好的执行计划的其它环境如测试库检查outline的sql 长度和sql文本set long 10000set pagesize 100set linesize 120select OL_NAME,TEXTLEN,SQL_TEXT from outln.ol$;确认长度和文本是否正确（outline可能将SQL最前面的空格截掉，像这种情况可以忽略，长度为原长度减去被截去前面空格的大小）例如上面sql 原长度为1102，创建outline后的长度为1100，前面两个空格被截去STG_OB_RESULT_SEQ 1100SELECT OB_RESULT_SEQ obResultSeq,rs.TASK_CODE taskCode, rs.SPECIAL_CASE_CODE specialCaseCode,rs.DATA_SEQ dataSeq, rs.QUESTIONNAIRE_CODE questionnaireCode,to_char(rs.OB_DATE,'yyyy-mm-dd hh24:mi:ss') obDate , rs.OB_TSR obTsr,rs.OB_RESULT_STATE obResultState, rs.REJECT_REASON rejectReason,rs.REMARK remark,rs.RECORD_NO recordNo, rs.DATA_STATE dataState,rs.BEGIN_PROCESS_TIME beginProcessTime, rs.END_PROCESS_TIME endProcessTime,rs.BEGIN_CALL_TIME beginCallTime, rs.END_CALL_TIME endCallTime,rs.OB_TEL obTel, rs.OB_TEL_TYPE obTelType,rs.OB_TYPE obType,rs.OB_TIMES obTimes , po.PARTY_NO partyNo ,sp.SPECIAL_CASE_NAME specialCaseName, po.BATCH batch , po.TABLE_ID tableId , drs.state_name FROM c_ob_result rs , c_obd_common_info po , c_special_case_info sp ,c_ob_data_result_tbl drs WHERE rs.data_seq = po.data_seq AND rs.special_case_code = sp.special_case_code AND rs.ob_result_state = drs.state_code and po.CLIENT_NO = :1 and rs.ob_date >sysdate - :2 ORDER BY rs.ob_result_seq desc8 在其它环境如测试库exp出outlineexp outln/outln owner=outln file=ol.dmp log=ol_exp.log9 在生产库将导出的dmp文件scp从测试库取到生产库主机scp cuichenglei001@3 在生产库解锁outln用户并修改密码alter user outln identified by outln account unlock;10 在生产库imp outline (8i是两个表ol$ ol$hints，9i是三个表)imp outln/outln file=ol.dmp full=y ignore=y log=ol_imp.log11 在生产库启用outlineexec dbms_outln.update_signatures;alter system set use_stored_outlines=special;12 在生产库检查执行计划：explain plan for 上面整理后的语句。explain plan for SELECT OB_RESULT_SEQ obResultSeq,rs.TASK_CODE taskCode, rs.SPECIAL_CASE_CODE specialCaseCode,rs.DATA_SEQ dataSeq, rs.QUESTIONNAIRE_CODE questionnaireCode,to_char(rs.OB_DATE,'yyyy-mm-dd hh24:mi:ss') obDate , rs.OB_TSR obTsr,rs.OB_RESULT_STATE obResultState, rs.REJECT_REASON rejectReason,rs.REMARK remark,rs.RECORD_NO recordNo, rs.DATA_STATE dataState,rs.BEGIN_PROCESS_TIME beginProcessTime, rs.END_PROCESS_TIME endProcessTime,rs.BEGIN_CALL_TIME beginCallTime, rs.END_CALL_TIME endCallTime,rs.OB_TEL obTel, rs.OB_TEL_TYPE obTelType,rs.OB_TYPE obType,rs.OB_TIMES obTimes , po.PARTY_NO partyNo ,sp.SPECIAL_CASE_NAME specialCaseName, po.BATCH batch , po.TABLE_ID tableId , drs.state_name FROM c_ob_result rs , c_obd_common_info po , c_special_case_info sp ,c_ob_data_result_tbl drs WHERE rs.data_seq = po.data_seq AND rs.special_case_code = sp.special_case_code AND rs.ob_result_state = drs.state_code and po.CLIENT_NO = :1 and rs.ob_date > sysdate - :2 ORDER BY rs.ob_result_seq desc ;select * from table(dbms_xplan.display);执行计划应该正常13 在生产库kill还在使用原执行计划的session，以便使用新计划14 在生产库和测试库修改outln密码成复杂密码并锁定outln用户alter user outln identified by <密码> account lock;######################################操作系统类########################################################################关于主机资源消耗#################################*********************************CPU使用情况***********************************top或glance或sar -u 5 1000u: about CPU5: 以秒为单位的测量周期1000: 测量周期中重复测量的次数输出结果：11:32:42 %usr %sys %wio %idle11:32:47 4 3 0 9311:32:53 5 5 0 91解释：%usr: 指用户进程使用的CPU比例，包括oracle的用户%sys: 指操作系统完成自己的工作（切换、中断等）使用的CPU比例%wio: 为特定进程的度量，这些进程当前正使用CPU却在等待IO请求服务%idle: CPU空闲率%sys和%wio应该小于10%到15%是否CPU空闲率为0%的系统存在CPU瓶颈？要看有多少进程在等待CPU，只要CPU的平均可执行队列小于2*CPU数目，则CPU空闲率0%是可以接受的。可以使用sar -q 5 1000测定系统上的可执行队列**************************************************************************************************************设备使用情况************************************top或glance或sar -d 5 1000iostat输出结果：11:41:44 device %busy avque r+w/s blks/s avwait avserv11:41:49 md10 0 0.0 0 0 0.0 0.0 md11 0 0.0 0 0 0.0 0.0解释：device: 设备名%busy： 设备繁忙程度，最好低于60%avque： 设备队列的平均长度r+w/s: 每秒读出+写入数blks/s: 每秒传输的块数(以512b的块计量)avwait： 五秒周期内每个I/O操作的平均等待时间，以毫秒为单位avserv: 服务I/O操作所用的平均时间**************************************************************************************************************虚拟内存使用情况**********************************vmstat -S 5 1000输出结果： kthr memory page disk faults cpu r b w swap free si so pi po fr de sr m1 m1 m1 m2 in sy cs us sy id 0 0 0 65706952 19722504 0 0 729 272 271 0 0 1 1 1 0 992 29474 2978 1 1 98 0 0 0 60526056 12733720 0 0 35324 0 0 0 0 0 0 0 0 1591 6047 3225 3 2 96解释：kthr(r b w): r指出执行队列中的进程（等待使用CPU执行） b指出被诸如I/O、分页等资源阻塞的进程 w指出可执行单当前正交换（可能处于内存极为缺乏）的进程memory(swap free): swap以K字节指出当前可用的交换空间量 free以K字节指出内存自由表的大小page(si so pi po fr de sr): si和so指出换入和换出内存K字节数 pi和po指出调入页和调出页的内存K字节数 fr指出空闲的K字节数 de以K字节指出预期的短期内存不足 sr指出以时钟算法扫描的页面（以页面尺寸设置大小）数disk(m1 m1 m1 m2): 最多提供四个值得注意的设备信息。这些数指出每秒I/O操作的数据。不是很有用，可从sar -d得到更好的信息faults(in sy cs): in指出设备终端的数目 sy指出系统调用的数目 cs指出CPU环境切换的数目cpu(us sy id): us指出用户进程使用时间的百分比 sy指出系统进程使用时间的百分比 id指出非当前使用时间的百分比（包括所有等待I/O数据）******************************************************************************* 0 0
- 数据库日常管理常用sql脚本
- sql数据库日常维护脚本
- sql server 常用的日常监控脚本
- db2数据库常用sql脚本
- 数据库日常维护常用的脚本部分收录
- 数据库日常维护常用的脚本部分收录
- SQL 日常检查脚本
- SQL 日常检查脚本
- Oracle数据库管理常用SQL
- 数据库管理常用sql语句
- 数据库管理常用sql语句
- 数据库管理常用sql命令
- DB2 数据库日常管理
- 转载】Oracle的日常监控脚本 Oracle管理及常用基础脚本
- 日常维护SQL以及脚本
- Informix数据库日常维护脚本
- SQL Server 2008 数据库还原常用脚本
- Sql Server 2008 数据库优化常用脚本
- c++丑数判断
- 八皇后（原创）
- 集合
- Scrapy源码分析（三）：信号管理器SignalManager
- HDU2049_不容易系列之(4)——考新郎
- 数据库日常管理常用sql脚本
- Android基础
- （十五）建造者模式详解（都市异能版）
- 归并排序-分治算法
- 堆
- Android动画----Scale
- 如何检测棋盘格
- Angular2 表单form
- js案例 移动端左右滑动touch+transform
