/ 目录： ###########查询类###########关于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数据库内部的session alter 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的session kill -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掉造成某一类等待事件的所有活动session select 'alter system kill session ' || '''' || sid || ',' || serial# || '''' || ';'from v$sessionwhere status = 'ACTIVE'and sid in (select sidfrom v$session_waitwhere event in ('&EVENT_NAME')); ******************************************************************************************************************查询session信息以及process信息********************************* --根据username查询sid和后台spid select 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查询sid select username,sid,serial#,STATUS,OSUSER,MACHINE,PROGRAM from v$session where paddr = (select addr from v$process where spid = &SPID);--根据sid查询后台spid select spid from v$process where addr = (select paddr from v$session where sid = &SID);--查询自己session的后台spid select 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_timefrom v$sesstat st,v$statname sn ,v$session swhere st.sid=s.sid AND st.statistic#=sn.statistic# and st.value>100000 and s.username is not nulland sn.name like '%physical read%' order by 2 desc)); ***********************************************************************************************************************查找产生redo log过快的进程********************************* col machine format a20 col osuser format a20 set lines 150 select 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_textfrom v$session se, v$sesstat st, v$sqlarea sawhere se.sid = st.sidand st.STATISTIC# =(select STATISTIC# from v$statname where NAME = 'redo size')--and se.username is not nulland st.value > 10 * 1024 * 1024and se.SQL_ADDRESS = sa.ADDRESSand se.SQL_HASH_VALUE = sa.HASH_VALUEorder by redosize; *****************************************************************************************************************************获取大批量数据操作测试的耗费时间和产生redo量********************************* set serveroutput on; DECLARE start_time NUMBER; end_time NUMBER; start_redo_size NUMBER; end_redo_size NUMBER; BEGIN start_time := dbms_utility.get_time; SELECT VALUE INTO start_redo_size FROM v$mystat m,v$statname s WHERE m.STATISTIC#=s.STATISTIC# AND s.NAME='redo size'; --transaction start 用户脚本 --transaction end end_time := dbms_utility.get_time; SELECT VALUE INTO end_redo_size FROM v$mystat m,v$statname s WHERE 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 end 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'; ***********************************************************************************************************************************************查询tmep表空间的使用情况********************************* --哪个进程占用临时表空间 select s.username, u.tablespace, u.contents, u.extents, u.blocks from v$session s, v$sort_usage u where s.saddr = u.session_addr and u.contents = 'TEMPORARY'--temp空间使用率 select (s.tot_used_blocks/f.total_blocks)*100 as pctused from (select sum(used_blocks) tot_used_blocksfrom v$sort_segmentwhere tablespace_name='TEMP') s,(select sum(blocks) total_blocksfrom dba_temp_fileswhere 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 s where su.session_addr=s.saddr and 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_USAGEV$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 drs WHERE s.saddr=t.ses_addr AND t.xidusn=r.usn and r.usn=drs.segment_id and ds.segment_name=drs.segment_name AND ((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 vWhere 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.machinefrom v$transaction t, v$session s where t.ses_addr=s.saddr and t.xidusn=&usn;--以下语句为查可能导致回滚段持续增长的会话 select s.sid, s.serial#, t.start_time, t.xidusn, s.username from v$session s, v$transaction t , v$rollstat r where s.saddr=t.ses_addr and t.xidusn=r.usn and ((r.curext=t.start_uext-1) or ((r.curext=r.extents-1) and t.start_uext=0)); **************************************************************************************************########################################关于sql和执行计划########################################*********************************查看某用户当前执行的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 bwhere a.username like upper('&1')and b.address = a.sql_addressand b.hash_value = a.sql_hash_valueorder by a.sid,a.serial#,b.piece; ******************************************************************************************************************************查看某用户之前执行的sql语句*********************************set pages 500set lines 120break on sid nodup on serial# nodup on machine nodupcol machine format a20select b.sql_text text, a.sid sid, a.serial# serial#, a.machine machinefrom v$session a, v$sqltext bwhere a.username = upper('&1')and b.address = a.prev_sql_addrand b.hash_value = a.prev_hash_valueorder 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 dual union all selectrpad('| '||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 sp where 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 where sa.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$sqlareaWHERE executions <5GROUP BY substr(sql_text, 1, 40) HAVING count(*) > 100ORDER BY 2;--执行了许多硬解析的当前会话 select c.sid||','||c.serial#,c.username,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.sidand a.statistic#=b.statistic#and a.value>0and 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_textfrom v$session se, v$sqlarea sawhere se.SQL_ADDRESS = sa.ADDRESSand se.SQL_HASH_VALUE = sa.HASH_VALUEand se.sid = '&SID';--根据spid查询执行的sql语句 select se.username,se.sid,se.serial#,se.osuser,se.machine,se.program,se.logon_time,sa.sql_textfrom v$session se, v$sqlarea sa, v$process prwhere se.SQL_ADDRESS = sa.ADDRESSand se.SQL_HASH_VALUE = sa.HASH_VALUEand se.PADDR=pr.ADDRand pr.spid = '&SPID'; *********************************************************************************************************************************查找当前硬解析过多的session********************************* select c.username,c.sid,c.serial#,b.name,a.value,round((sysdate - c.logon_time) * 24) hours_connectedfrom v$sesstat a, v$statname b, v$session cwhere c.sid = a.sidand a.statistic# = b.statistic#and a.value > 1000and b.name = 'parse count (hard)'order by a.value; ******************************************************************************************************************************查询各种等待事件对应的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, v$session_wait c where a.sid = c.sidand c.event = '&EVENT_NAME'and b.address = a.sql_addressand b.hash_value = a.sql_hash_value order 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,typefrom v$lockwhere (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_modefrom v$locked_object v, dba_objects dwhere v.object_id = d.object_idand object_name=upper('&1')order by object_name ;--查找所有被锁的对象：select oracle_username,owner,object_name,object_type,session_id,locked_mode,l.type,l.blockfrom v$locked_object v, dba_objects d,v$lock lwhere l.block>0 and v.session_id=l.sidand d.object_id=v.object_idorder 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_id2 from v$lock a,dba_objects cwhere (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$LOCKWHERE SID IN (SELECT SID SESSION_IDFROM V$LOCKWHERE BLOCK > 0MINUSSELECT W.SID SESSION_IDFROM V$SESSION_WAIT WWHERE 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.BLOCKFROM V$SESSION S, V$SESSION_WAIT W, V$LOCK LWHERE S.SID = W.SIDAND S.SID = L.SIDAND L.BLOCK > 0;--查出WAITER等待的记录行：--首先查出WAITER等待的资源：SELECT ROW_WAIT_OBJ# ,ROW_WAIT_FILE# ,ROW_WAIT_BLOCK# ,ROW_WAIT_ROW#FROM V$SESSIONWHERE 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确定哪个用户在调用这个package select b.sql_text text,a.sid sid ,a.serial# sria#,a.username username, c.type type,a.machine machine from v$session a ,v$sqltext b ,v$access c where c.object=upper('&OBJECT_NAME') and c.type in ('TABLE','PACKAGE','PROCEDURE','FUNCTION','PACKAGE BODY') and a.sid=c.sid and b.address = a.sql_address and b.hash_value = a.sql_hash_value order 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_TEXTfrom v$session s, v$session_wait sw --,v$sqlarea sawhere sw.sid = s.sid--and s.SQL_ADDRESS=sa.ADDRESS--and s.SQL_HASH_VALUE=sa.HASH_VALUEand s.username is not nulland 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 swhere p.kglpnuse = s.saddrand kglpnhdl = (select p1rawfrom v$session_waitwhere sid = &SID_IN_LIBRARY_CACHE_PIN);或者： select sid Holder ,KGLPNUSE Sesion , KGLPNMOD Held, KGLPNREQ Reqfrom x$kglpn , v$sessionwhere KGLPNHDL in (select p1raw from v$session_waitwhere wait_time=0 and event like 'library%')and KGLPNMOD <> 0and v$session.saddr=x$kglpn.kglpnuse ;或者：select sql_text from v$sqlareawhere (v$sqlarea.address,v$sqlarea.hash_value)in (select sql_address,sql_hash_value from v$session where sid in (select sidfrom x$kglpn , v$sessionwhere KGLPNHDL in (select p1raw from v$session_waitwhere wait_time=0 and event like 'library%')and KGLPNMOD <> 0and 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_timefrom v$session s, v$session_wait swwhere s.username is not nulland sw.sid = s.sidand 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 ewhere x.hladdr = '&P1RAW'and e.file_id = x.file#and x.hladdr = l.addrand x.dbablk between e.block_id and e.block_id + e.blocks - 1order by x.tch desc;column segment_name format a30 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) bwhere e.relative_fno=b.dbarfiland e.block_id<=b.dbablkand e.block_id+e.blocks>b.dbablk;--查找产生热块的sql： column segment_name format a35 select /*+ rule */ hash_value,sql_text from v$sqltext where (hash_value,address ) in (select a.hash_value,a.address from v$sqltext a ,(select 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) bwhere e.relative_fno=b.dbarfiland e.block_id<=b.dbablkand e.block_id+e.blocks>b.dbablk ) bwhere 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.namefrom v$session a, v$latchholder bwhere a.sid = b.sid; ************************************************************************************************************************************db file sequential read等待事件的分析********************************* --当等待事件为db file sequential read时，P1对应file_id，P2对应&block_id --通过下面这个语句可以查询到正在等待什么对象select owner,segment_name,segment_typefrom dba_extentswhere file_id = &file_idand &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) kbytesfrom v$sql_planwhere hash_value in(select a.sql_hash_value from v$session a,v$session_wait bwhere a.sid=b.sidand b.event='db file scattered read')order by hash_value,child_number,id; *******************************************************************************************************#############################################关于trace和event#############################################*********************************设置trace和event********************************* --设置autotrace set autotrace on SET AUTOT[RACE] {OFF | ON | TRACE[ONLY] } [EXP[LAIN]] [STAT[ISTICS] ]--设置sql trace alter session set sql_trace=true; --my session execute dbms_system.set_sql_trace_in_session(&SID,&SERIAL#,true); --other session--设置10046 event Alter session set events '10046 trace name context forever,level 12'; --my session alter session set events '10046 trace name context off'; --closeExec dbms_system.set_ev(&SID,&SERIAL#,10046,12,''); --open other session Exec 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_TIMEfrom dbq$session_detailwhere 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 SQLfrom v$session a, v$sqltext bwhere a.status = 'ACTIVE'and username not in ('SYS')and a.last_call_et / 60 >= 240and a.username <> 'CIF_AQ'and a.sql_address = b.addressand a.username is not nullorder 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_valueFROM V$session s, V$transaction t, V$rollstat r,v$sqlarea qWHERE s.saddr = t.ses_addrAND t.xidusn = r.usnAND s.sql_address=q.addressAND s.sql_hash_value=q.hash_valueAND ((((r.curext = t.start_uext - 1) OR((r.curext = r.extents - 1) AND t.start_uext = 0))and s.last_call_et /60 > 30and r.rssize>r.optsizeand 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_timefrom v$session s,v$session_wait swwheresw.sid =s.sidand s.username is not nulland 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$sqlareaWHERE executions =1GROUP BY substr(sql_text, 1, 50) HAVING count(*) > 5000ORDER 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_TEXT from v$sqlarea sq,v$session se,v$process pr where se.PADDR=pr.ADDR and ((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*1024 order 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_textfrom v$session se, v$sesstat st, v$sqlarea sa ,v$process prwhere se.sid = st.sidand st.STATISTIC# =(select STATISTIC# from v$statname where NAME = 'redo size')and se.username is not nulland st.value > 50 * 1024 * 1024and se.SQL_ADDRESS = sa.ADDRESSand se.SQL_HASH_VALUE = sa.HASH_VALUEand se.paddr=pr.addrorder 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_textfrom v$sort_usage su, v$session se ,v$sqlarea sa where su.session_addr=se.saddrand se.SQL_ADDRESS = sa.ADDRESSand se.SQL_HASH_VALUE = sa.HASH_VALUEand su.extents>10;select su.segtype, sum(su.extents) from v$sort_usage su group by su.segtype; ***************************************************************************************************************#################################关于数据库实例######################################*********************************了解当前ASMM 自动调整的内存参数情况********************************* column "Parameter" format a20 truncate column size_m format 9999 select a.ksppinm "Parameter", c.ksppstvl/1048576 size_m from sys.x$ksppi a, sys.x$ksppcv b, sys.x$ksppsv c where a.indx = b.indx and a.indx = c.indx and 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 log alter database drop logfile group 1;(3)添加新size的redo log alter 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 abort Startup mount; recover database parallel 8;(3)打开数据库 Alter database open; ******************************************************************************************************************************redo log header corrupt处理方法*********************************** (1)查询redo log的情况，确定损坏的log group select * from v$log; select * from v$logfile;(2)切换redo log，根据剩余可用的redo log，不要切换多次 alter system switch logfile;(3)清掉已经坏的redo log alter database clear unarchived logfile group <&损坏的log group>; --检查结果 select * from v$log;(4)重设log archive dest alter 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 5 alter 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)查出备份的最近一次统计信息收集的statid select 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:00 SICSDATA SICS_INSCARD_CLIENT_INFO 18-DEC-07 01.47.32.624219 PM +08:00 SICSDATA 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@luhz0 exec 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.cn cd $HOME/tmp exp dbmgr tables=EXP_STATS_TMP file=queue_stats_luhz0.dmp log=exp.log(3) logon to luzz0.db.paic.com.cn cd $HOME/tmp scp 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@luzz0 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.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.1 unzip rda.zip cd 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 text select sql_text ||to_char(length(sql_text)) sql_text from v$sqltext where hash_value=2092045661 order by piece; SQL_TEXT --------------------------------------------------------------------------------------------------------SELECT OB_RESULT_SEQ obResultSeq,rs.TASK_CODE taskCode, rs64 .SPECIAL_CASE_CODE specialCaseCode,rs.DATA_SEQ dataSeq, rs.Q64 UESTIONNAIRE_CODE questionnaireCode,to_char(rs.OB_DATE,'yyyy-mm-64 dd hh24:mi:ss') obDate , rs.OB_TSR obTsr,rs.OB_RESULT_STATE 64 obResultState, rs.REJECT_REASON rejectReason,rs.REMARK remar64 k,rs.RECORD_NO recordNo, rs.DATA_STATE dataState,rs.BEGIN_PR64 OCESS_TIME beginProcessTime, rs.END_PROCESS_TIME endProcessT64 ime,rs.BEGIN_CALL_TIME beginCallTime, rs.END_CALL_TIME endCa64 llTime,rs.OB_TEL obTel, rs.OB_TEL_TYPE obTelType,rs.OB_TYPE 64 obType,rs.OB_TIMES obTimes , po.PARTY_NO partyNo ,sp.S64 PECIAL_CASE_NAME specialCaseName, po.BATCH batch , po.TABLE64 _ID tableId , drs.state_name FROM c_ob_result rs , c_obd_commo64 n_info po , c_special_case_info sp ,c_ob_data_result_tbl64drs WHERE rs.data_seq = po.data_seq AND rs.special_64 case_code = sp.special_case_code AND rs.ob_result_state = drs.64 state_code and po.CLIENT_NO = :1 and64rs.ob_date > sysdate - :2 ORDER BY rs.ob_resu64 lt_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 forSELECT 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 onSELECT 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 10000 set pagesize 100 set linesize 120 select OL_NAME,TEXTLEN,SQL_TEXT from outln.ol$; 确认长度和文本是否正确（outline可能将SQL最前面的空格截掉，像这种情况可以忽略，长度为原长度减去被截去前面空格的大小） 例如上面sql 原长度为1102，创建outline后的长度为1100，前面两个空格被截去STG_OB_RESULT_SEQ 1100 SELECT OB_RESULT_SEQ obResultSeq,rs.TASK_CODE taskCode, rs.SPECIAL_CASE_CODEspecialCaseCode,rs.DATA_SEQ dataSeq, rs.QUESTIONNAIRE_CODE questionnaireCod e,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 beginPr ocessTime, rs.END_PROCESS_TIME endProcessTime,rs.BEGIN_CALL_TIME beginCallTi me, rs.END_CALL_TIME endCallTime,rs.OB_TEL obTel, rs.OB_TEL_TYPE obTelTy pe,rs.OB_TYPE obType,rs.OB_TIMES obTimes , po.PARTY_NO partyNo ,sp.SPE CIAL_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 A ND rs.special_case_code = sp.special_case_code AND rs.ob_result_state = drs.st ate_code and po.CLIENT_NO = :1 and rs.ob_date > sysdate - :2 ORDER BY rs.ob_result_seq desc8 在其它环境如测试库exp出outline exp 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 在生产库启用outline exec dbms_outln.update_signatures; alter system set use_stored_outlines=special;12 在生产库检查执行计划： explain plan for 上面整理后的语句。 explain plan forSELECT 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 1000 u: about CPU 5: 以秒为单位的测量周期 1000: 测量周期中重复测量的次数输出结果： 11:32:42 %usr %sys %wio %idle 11:32:47 4 3 0 93 11: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 1000 iostat输出结果： 11:41:44 device %busy avque r+w/s blks/s avwait avserv 11:41:49 md10 0 0.0 0 0 0.0 0.0md11 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 cpur b w swap free si so pi po fr de sr m1 m1 m1 m2 in sy cs us sy id0 0 0 65706952 19722504 0 0 729 272 271 0 0 1 1 1 0 992 29474 2978 1 1 980 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数据） *******************************************************************************
支付宝扫一扫
微信扫一扫
.png)
.png)
.png)
