数据库日常管理常用sql脚本

最新推荐文章于 2022-12-21 19:12:56 发布
wish503
最新推荐文章于 2022-12-21 19:12:56 发布
阅读量2.8k
点赞数 1
分类专栏： oracle
oracle 专栏收录该内容
36 篇文章 0 订阅
订阅专栏
/ 目录： ###########查询类###########

           关于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$session
 where status = 'ACTIVE'
   and sid in (select sid
                 from v$session_wait
                where 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_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 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_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;
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_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 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_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 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 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 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 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 dual
union all
select
    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 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$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_connected
from v$sesstat a,v$statname b,v$session c
where 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 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.sid
      and c.event = '&EVENT_NAME'
      and b.address = a.sql_address
      and 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,
       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_id2
from 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确定哪个用户在调用这个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_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 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) 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 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_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*********************************
--设置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';   --close

Exec dbms_system.set_ev(&SID,&SERIAL#,10046,12,'');  --open  other session
Exec dbms_system.set_ev(&SID,&SERIAL#,10046,0,'');   --close other session

ALTER 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_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_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 sa
where 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 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_tbl64
 drs          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                and64
     rs.ob_date > sysdate - :2               ORDER BY rs.ob_resu64
lt_seq desc   14

2 取到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 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_CODE
 specialCaseCode,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 desc


8 在其它环境如测试库exp出outline
exp outln/outln owner=outln file=ol.dmp log=ol_exp.log

9 在生产库将导出的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.log

11 在生产库启用outline
exec 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 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.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数据）
*******************************************************************************
wish503
关注
1
点赞
踩
4

收藏

觉得还不错? 一键收藏
0
评论
数据库日常管理常用sql脚本

/ 目录： ###########查询类########### 关于session: kill session和kill -9 查询session信息以及process信息查找产生大量物理读的进程查找产生redo log过快的进程
复制链接

扫一扫