Oracle性能管理常用命令

最新推荐文章于 2023-02-01 09:57:12 发布

来罐百事可乐

最新推荐文章于 2023-02-01 09:57:12 发布

阅读量719

点赞数 2

分类专栏： oracle 文章标签： oracle Linux运维

本文链接：https://blog.csdn.net/IIOOHHHSSSS/article/details/121603654

版权

oracle 专栏收录该内容

10 篇文章 1 订阅

订阅专栏

这篇内容涉及了数据库SQL优化、事件监控、事务管理、游标管理和表空间数据迁移等多个方面。通过SQL查询展示了如何查看SQL执行计划、调整SQL性能、监控阻塞情况、管理会话以及查看和调整SGA组件大小。此外，还涵盖了索引监控、日志生成量分析以及无效对象的管理策略。

摘要由CSDN通过智能技术生成

SQL优化任务查看与参数设置

select * from DBA_ADVISOR_EXECUTIONS t where t.task_name like '%AUTO%' order by t.execution_start desc;

select dbms_sqltune.report_tuning_task(task_name => 'SYS_AUTO_SQL_TUNING_TASK',execution_name => 'EXEC_329376') from dual;
 
 begin
    
            dbms_sqltune.set_tuning_task_parameter('SYS_AUTO_SQL_TUNING_TASK','TIME_LIMIT',7200);
 end;

查看阻塞情况,从历史会话中查看阻塞情况

SELECT D.instance_number,D.sample_id,
       d.sample_time,
       d.session_id,
       d.session_serial#,
       d.sql_id,
       d.sql_opname,
       d.blocking_session,
       d.blocking_session_serial#,
       d.event,
       e.sql_text
  FROM DBA_HIST_ACTIVE_SESS_HISTORY D, dba_hist_sqltext e
 WHERE D.SAMPLE_TIME BETWEEN
       TO_DATE('2018-04-08 21:00:00', 'YYYY-MM-DD HH24:MI:SS') AND
       TO_DATE('2018-04-08 22:00:00', 'YYYY-MM-DD HH24:MI:SS')
   and d.sql_id = e.sql_id
   and d.blocking_session is not null
   and d.EVENT = 'enq: TX - row lock contention'
 order by d.sample_time asc;
 ---
 SELECT e.sql_text,d.* 
 FROM DBA_HIST_ACTIVE_SESS_HISTORY D left join  dba_hist_sqltext e on d.sql_id=e.sql_id
 WHERE d.session_id =534  and d.session_serial# =8467
 order by d.sample_time asc;

select *
  from dba_hist_active_sess_history t
 where t.SESSION_ID = 402
   and t.SESSION_SERIAL# = 47229;
 

/*drop table TEST_T;*/
 /*create table TEST_T as
  select *
          from dba_hist_active_sess_history
         where sample_time between
               to_date('20171229 07:20', 'yyyymmdd hh24:mi') and
               to_date('20171229 08:10', 'yyyymmdd hh24:mi');*/

从历史会话中查看阻塞情况

select  
       t.sample_time,      
       t.session_id,
       T.SESSION_SERIAL#,
       t.BLOCKING_INST_ID,
       t.blocking_session,
       t.blocking_session_serial#,
       t.sql_id,
       t.event,
       t.session_state,
       level,
      w.sql_text,t.current_obj#,x.OBJECT_NAME,t.MACHINE,CHR(BITAND(P1, -16777216) / 16777215) ||  CHR(BITAND(P1, 16711680) / 65535) "Lock",
          BITAND(P1, 65535) "Mode",w.SQL_TEXT,t.*
   from v$active_session_history t left join v$sql w on t.sql_id=w.sql_id left join dba_objects x on t.CURRENT_OBJ#=x.OBJECT_ID 
 where sample_time > SYSDATE - 10/24/60             
 start with blocking_session is not null
       -- and event like 'enq: TX - row lock contention%'
connect by nocycle sample_time = prior sample_time
       and session_id = prior blocking_session
       and session_serial# = prior blocking_session_serial#
 

select *
  from v$active_session_history t
 where t.SESSION_ID = 776
   and t.SESSION_SERIAL# = 8869 
   order by t.SAMPLE_TIME asc;

查看会话信息

 select t.SAMPLE_ID,
         t.sample_time,
         t.INST_ID,
         t.session_id,
         T.SESSION_SERIAL#,
         t.BLOCKING_INST_ID,
         t.blocking_session,
         t.blocking_session_serial#,
         t.sql_id,
         t.event,
         t.session_state,w.sql_text,t.current_obj#,x.OBJECT_NAME
  from gv$active_session_history t left join gv$sql w on t.sql_id=w.sql_id left join dba_objects x on t.CURRENT_OBJ#=x.OBJECT_ID
 where t.SESSION_ID = 1052
   and t.SESSION_SERIAL# = 44721;

从历史会话中查看阻塞情况—>>>

select  
       t.sample_time,      
       t.session_id,
       T.SESSION_SERIAL#,
       t.BLOCKING_INST_ID,
       t.blocking_session,
       t.blocking_session_serial#,
       t.sql_id,
       t.event,
       t.session_state,
       level,
       connect_by_isleaf,
       connect_by_iscycle
  from v$active_session_history t
 where SAMPLE_TIME BETWEEN
       TO_DATE('2018-04-08 21:00:00', 'YYYY-MM-DD HH24:MI:SS') AND
       TO_DATE('2018-04-08 22:00:00', 'YYYY-MM-DD HH24:MI:SS')           
     and event like 'enq: TX - row lock contention%'  ;
   ---
 SELECT e.sql_text,d.* 
 FROM v$active_session_history D left join  v$sql e on d.sql_id=e.sql_id
 WHERE d.session_id =534  and d.session_serial# =8467
 order by d.sample_time asc;

查看事件的会话情况

select t.SAMPLE_ID,
         t.sample_time,
       --  t.INST_ID,
         t.session_id,
         T.SESSION_SERIAL#,
         t.BLOCKING_INST_ID,
         t.blocking_session,
         t.blocking_session_serial#,
         t.sql_id,
         t.event,
         t.session_state,w.sql_text,t.current_obj#,x.OBJECT_NAME,t.MACHINE,CHR(BITAND(P1, -16777216) / 16777215) ||  CHR(BITAND(P1, 16711680) / 65535) "Lock",   BITAND(P1, 65535) "Mode"

from v$active_session_history t left join dba_hist_sqltext w on t.sql_id=w.sql_id left join dba_objects x on t.CURRENT_OBJ#=x.OBJECT_ID
   where 
      sample_time between to_date('2018-01-03 07:30', 'yyyy-mm-dd hh24:mi') and   to_date('2018-01-03 08:30', 'yyyy-mm-dd hh24:mi')
    -- and event like 'SQL*Net more data to client'
     and  t.event ='enq: TX - row lock contention'
     order by t.sample_time asc;
    -- order by t.SESSION_ID,t.SESSION_SERIAL#,t.sample_time asc;

查看SQL ID的锁的情况

SELECT D.SQL_ID,CHR(BITAND(P1, -16777216) / 16777215) ||  CHR(BITAND(P1, 16711680) / 65535) "Lock",BITAND(P1, 65535) "Mode", COUNT(1),COUNT(DISTINCT d.session_id )
 FROM DBA_HIST_ACTIVE_SESS_HISTORY D
 WHERE D.SAMPLE_TIME BETWEEN TO_DATE('2018-01-03 07:30:00', 'YYYY-MM-DD HH24:MI:SS') AND
       TO_DATE('2018-01-03 08:30:00', 'YYYY-MM-DD HH24:MI:SS')
   AND D.EVENT = 'enq: TX - row lock contention'
 GROUP BY D.SQL_ID,(CHR(BITAND(P1, -16777216) / 16777215) ||CHR(BITAND(P1, 16711680) / 65535)),(BITAND(P1, 65535));

阻塞会话
查询阻塞SQL及被阻塞SQL

select g.sid,
       g.serial#,
       g.event,
       g.username,
       g.SQL_ID,
       s.SQL_TEXT,
       g.MACHINE,
       g.BLOCKING_SESSION,
        'alter system kill session ''' || g.sid || ',' ||g.serial# ||  ''' immediate;',
       t.PREV_SQL_ID,
       u.SQL_TEXT,t.MACHINE
  from v$session g, v$sql s, v$session t,v$sql u
 where g.sql_hash_value = s.HASH_VALUE
    -- and g.Wait_class <> 'Idle'
   --and g.EVENT = 'enq: TX - row lock contention'
   and g.BLOCKING_SESSION=t.SID(+)
   and t.PREV_SQL_ID=u.SQL_ID(+)
;

绑定变量
得到绑定变量

select sql_id,
        name,
        datatype_string,
        case datatype
          when 180 then --TIMESTAMP
           to_char(ANYDATA.accesstimestamp(t.value_anydata),
                   'YYYY/MM/DD HH24:MI:SS')
          else
           t.value_string
        end as bind_value,
        last_captured
   from gv$sql_bind_capture t
  where sql_id = '1u64vwv30yd3d';

查看执行计划

SELECT * FROM TABLE(dbms_xplan.display_cursor('70591ynqcnfw1',null,'ADVANCED'));

性能查看

SELECT sql_id,   
    count(*),   
    round(count(*) / sum(count(*)) over(), 2) pctload  
FROM V$ACTIVE_SESSION_HISTORY  
WHERE sample_time > sysdate - 1 / (24 * 60)  
    AND session_type <> 'BACKGROUND'  
    AND session_state = 'ON CPU'  
GROUP BY sql_id  
ORDER BY count(*) desc;  

SELECT ash.sql_id,  
    count(*)  
FROM V$ACTIVE_SESSION_HISTORY ASH,V$EVENT_NAME EVT  
WHERE ash.sample_time > sysdate -1/(24*60)  
    AND ash.session_state = 'WAITING'  
    AND ash.event_id = evt.event_id  
    AND evt.wait_class = 'USER I/O'  
GROUP BY ash.sql_id  
ORDER BY count(*) desc;  


SELECT session_id,  
    count(*)  
    FROM V$ACTIVE_SESSION_HISTORY  
WHERE session_state = 'ON CPU'  
    AND sample_time > sysdate -1/(24*60)  
GROUP BY session_id  
ORDER BY count(*) desc; 


SELECT ash.sql_id,  
    sum(decode(ash.session_state,'ON CPU',1,0)) "CPU",  
    sum(decode(ash.session_state,'WAITING',1,0)) -  
    sum(decode(ash.session_state,'WAITING',decode(en.wait_class,'USER I/O',1,0),0)) "WAIT",  
    sum(decode(ash.session_state,'WAITING',decode(en.wait_class,'USER I/O',1,0),0)) "IO",  
    sum(decode(ash.session_state,'ON CPU',1,1)) "TOTAL"  

FROM V$ACTIVE_SESSION_HISTORY ASH,V$EVENT_NAME EN  
WHERE SQL_ID is not null and en.event#=ash.event# and ash.sample_time > sysdate -1/(24*60)  
GROUP BY ash.sql_id  
ORDER BY sum(decode(ash.session_state,'ON CPU',1,1)) desc;  


SELECT
h.event "Wait Event",
SUM(h.wait_time + h.time_waited) "Total Wait Time"
FROM
v$active_session_history h,
v$event_name e
WHERE
h.sample_time BETWEEN sysdate - 4/24 AND sysdate - 1/24 
AND h.event_id = e.event_id
AND e.wait_class <> 'Idle'
GROUP BY h.event
ORDER BY 2 DESC   
   
select * from dba_lobs t where t.SEGMENT_NAME='SYS_LOB0000194950C00002$$';


   
 select * from v$mystat

实例内存

--查询sga的空余内存。
select b.* ,round(b.free / b.total,2) free_pct
from (
select a.pool,a.NAME,
  round(a.BYTES /1024/1024 , 0) free ,
 round((select  sum(a.BYTES) /1024/1024  from v$sgastat a where a.pool = 'shared pool' ),0) total
from v$sgastat a where a.pool = 'shared pool' and a.NAME = 'free memory'
) b;

select * from v$instance;
/*
select   t.USERNAME,count(*) from v$session t group by t.username;*/

-- SGA 变化查询
SELECT start_time, 
       component, 
       oper_type, 
       oper_mode, 
       initial_size / 1024 / 1024 "INITIAL", 
       final_size / 1024 / 1024   "FINAL", 
       end_time 
FROM   v$sga_resize_ops 
WHERE  component IN ( 'DEFAULT buffer cache', 'shared pool' ) 
       AND status = 'COMPLETE' 
ORDER  BY start_time, 
          component;

事务管理

--查询活动事务----
 with ltr as ( 
select to_char(sysdate,'YYYYMMDDHH24MISS') TM, 
       s.sid, 
       s.SERIAL#,
       s.sql_id, 
       s.sql_child_number, 
       s.prev_sql_id, 
       xid, 
       to_char(t.start_date,'YYYYMMDDHH24MISS') start_time, 
       e.TYPE,e.block, 
       e.ctime, 
       decode(e.CTIME, 0, (sysdate - t.start_date) * 3600*24, e.ctime) el_second ,
       s.CLIENT_INFO,
       s.MODULE,
       s.MACHINE , 
       t.STATUS    
     --  q.sql_text 
  from v$transaction t, v$session s,v$transaction_enqueue e
 where   t.addr = s.taddr 
   --and s.sql_child_number = q.CHILD_NUMBER(+) 
   --and s.sql_id = q.sql_id(+) and s.prev_sql_id = q.sql_id(+) 
   and t.addr = e.addr(+) )  
  select ltr.* , (select q1.sql_text from v$sql q1 where ltr.prev_sql_id = q1.sql_id(+)
   and rownum = 1) prev_sql_text , 
  (select q1.sql_text from v$sql q1 where ltr.sql_id = q1.sql_id(+) 
   and ltr.sql_child_number = q1.CHILD_NUMBER(+)) sql_text 
   from ltr ltr;
   
   
 ---查询事务的活动时间
 
 with transaction_details as  
( select   ses_addr  
  , sysdate - start_date as diff  
  from  v$transaction  
)  
select s.username  
, to_char(trunc(t.diff))  
             || ' days, '  
             || to_char(trunc(mod(t.diff * 24,24)))  
             || ' hours, '  
             || to_char(trunc(mod(t.diff * 24 * 60,24)))  
             || ' minutes, '  
             || to_char(trunc(mod(t.diff * 24 * 60 * 60,60)))  
             || ' seconds' as transaction_duration  
, s.program  
, s.terminal  
, s.status  
, s.sid  
, s.serial#  
from  v$session s  
, transaction_details t  
where s.saddr = t.ses_addr  
order by t.diff desc ;

查询会话

 --查询被锁定的对象，且会话不活动 
   SELECT A.SID,
       A.SERIAL#,
       A.USERNAME,
       A.EVENT,
       A.WAIT_CLASS,
       A.SECONDS_IN_WAIT,
       A.PREV_EXEC_START,
       b.LOCKED_MODE,
       C.OWNER,
       C.OBJECT_NAME,
       C.OBJECT_TYPE,
       A.STATUS,
       A.STATE
  FROM V$SESSION A
 INNER JOIN V$LOCKED_OBJECT B
    ON A.SID = b.SESSION_ID
 INNER JOIN DBA_OBJECTS C
    ON B.OBJECT_ID = c.OBJECT_ID
 WHERE A.WAIT_CLASS = 'Idle'
   AND A.SECONDS_IN_WAIT > 10;
   
  
--正在运行，且超长的会话
select 'ALTER system kill session '''||s.sid||','||s.serial# ||''';',s.machine,s.username,s.program,s.sid,s.serial#,a.sql_id,s.status,'OS id: '||p.spid "OS ID",s.event,'call_et: '||s.last_call_et,'wait time: '|| wait_time
 "Wait_time",a.sql_text,s.sql_hash_value,s.state
from v$session s,v$process p,v$sqlarea a
 where s.paddr = p.addr
and s.sql_hash_value = a.hash_value(+)
and s.type = 'USER'
and s.status = 'ACTIVE'
and s.last_call_et > 10 ;


--查运行时间长且未结束的会话
select 'alter system kill session ''' || t.sid || ',' || t.serial# ||
''' immediate;'
from v$session_longops t
where t.SOFAR < t.TOTALWORK and t.ELAPSED_SECONDS >30
order by start_time;


-- 查询处于等待的会话
select t.CLIENT_INFO,t.WAIT_TIME_MICRO ,t.TIME_REMAINING_MICRO,t.STATE,'alter system kill session ''' || t.sid || ',' || t.serial# ||
',@' || t.inst_id || 
''' immediate;' ,t.event,t.wait_class,t.module,
       (select    S.SQL_FULLTEXT  from v$sql s where t.sql_address = s.address and t.sql_hash_value = s.hash_value and rownum=1),
       t.*
  from gv$session t
where state in ('WAITING')
   and wait_class != 'Idle'
   order by t.wait_class;

游标管理

--查看打开的游标和会话缓存游标
   SELECT 'session_cached_cursors' PARAMETER,
       LPAD (VALUE, 5) VALUE,
       DECODE (VALUE, 0, ' n/a', TO_CHAR (100 * USED / VALUE, '990') || '%')
          USAGE
  FROM (SELECT MAX (S.VALUE) USED
          FROM V$STATNAME N, V$SESSTAT S
         WHERE     N.NAME = 'session cursor cache count'
               AND S.STATISTIC# = N.STATISTIC#),
       (SELECT VALUE
          FROM V$PARAMETER
         WHERE NAME = 'session_cached_cursors')
UNION ALL
SELECT 'open_cursors',
       LPAD (VALUE, 5),
       TO_CHAR (100 * USED / VALUE, '990') || '%'
  FROM (  SELECT MAX (SUM (S.VALUE)) USED
            FROM V$STATNAME N, V$SESSTAT S
           WHERE     N.NAME IN ('opened cursors current',
                                'session cursor cache count')
                 AND S.STATISTIC# = N.STATISTIC#
        GROUP BY S.SID),
       (SELECT VALUE
          FROM V$PARAMETER
         WHERE NAME = 'open_cursors');

Keep缓冲区管理

-对小表keep缓冲中，避免物理IO----------开始-----------------------------------------------------

--查看当前实际的__db_cache_size
SELECT x.ksppinm NAME, y.ksppstvl / 1024 / 1024 VALUE, x.ksppdesc describ
  FROM SYS.x$ksppi x, SYS.x$ksppcv y
 WHERE x.indx = y.indx
   AND x.ksppinm LIKE '%__db_cache_size%';
   
--查看shared pool空闲
select POOL,
       round(BYTES / 1024 / 1024,0) FREE_MB,
       round( BYTES / 1024 / 1024 /
       (select t.CURRENT_SIZE / 1024 / 1024
          from v$sga_dynamic_components t
         where t.COMPONENT = 'shared pool'),2) free_percent
  from v$sgastat
 WHERE NAME = 'free memory'
   and pool = 'shared pool';

select t.COMPONENT,t.CURRENT_SIZE /1024/1024 from v$sga_dynamic_components t where t.COMPONENT='shared pool';


--查看哪些表已经keep了
select a.owner,a.segment_name from dba_segments a where BUFFER_POOL = 'KEEP';

select * from dba_segments a where a.segment_name='HRM_EMPLOYEE' and a.owner='sy';
 
 --把表keep在缓冲中
 alter table sy.DAP_DRUG_PRICE storage(buffer_pool keep);
 
 
--1.查找哪些表进行了全表扫描
drop table TEST_FULL_SCAN;
create table TEST_FULL_SCAN
as
select distinct v.OBJECT_OWNER,v.OBJECT_NAME,v.CARDINALITY,v.OBJECT_TYPE,v.SQL_ID
  from v$sql_plan v
 where v.operation = 'TABLE ACCESS'
   and v.OPTIONS = 'FULL'
   and v.OBJECT_OWNER='sy';
   
select * from v$sql t where t.SQL_ID='0psd375vrnb2y';

--2. 根据全表扫描的表，统计其占用的空间M
drop table TEST_FULL_SCAN_TALBE_INFO;
create table TEST_FULL_SCAN_TALBE_INFO
as
select b.owner,b.bytes /1024/1024 sizez, 'M' UNIT, b.segment_type,b.segment_name,b.buffer_pool
from  DBA_SEGMENTS b
where (b.owner,b.segment_name) in (select c.object_owner,c.object_name from  TEST_FULL_SCAN c);

select * from TEST_FULL_SCAN_TALBE_INFO a order by a.sizez asc 
   

 --3.生成keep表到内存的语句
 
--对表数据大小小于1M的表生成带缓存的SQL
select A.*,
       'alter table sy.' || A.SEGMENT_NAME ||
       ' storage(buffer_pool KEEP);'
  from TEST_FULL_SCAN_TALBE_INFO a
 WHERE A.SIZEE < 1
   AND A.OWNER = 'sy'
 order by 1 ASC;
   
 --keep buffer使用率
     select p.name,a.cnum_repl "total buffers",a.anum_repl "free buffers" 
        from x$kcbwds a, v$buffer_pool p
       where a.set_id=p.LO_SETID and p.name='KEEP';  
 
 --查看keep buffer的大小
   select component,current_size /1024/1024 from v$sga_dynamic_components
  where component='KEEP buffer cache';
  
  select '--',a.*,B.*  from V$BH a, DBA_objects b  
where a.objd=b.object_id and object_name='HRM_EMPLOYEE' AND B.owner='sy' group by object_name, a.status;
  
  --查看keep到内存的所有表的总容量
  select s.owner,
       s.segment_name,
       s.partition_name,
       s.bytes / 1024 / 1024 as "size(m)"
  from dba_segments s
 where owner = 'sy'
   and segment_name in
       (select table_name from dba_tables where owner = 'sy' and buffer_pool='KEEP')
 order by 4 desc;
 
 
   select sum(s.bytes / 1024 / 1024) as "size(m)"
  from dba_segments s
 where owner = 'sy'
   and segment_name in
       (select table_name from dba_tables where owner = 'sy' and buffer_pool='KEEP')
 ;
  
 --统计缓存中的块
 select count(*)
      from v$bh   
     where objd = (select data_object_id   
                     from dba_objects   
                    where owner = 'sy'  
                      and object_name = 'HRM_EMPLOYEE')   
       and status != 'free'; 
  
 select t.blocks from DBA_SEGMENTS t where t.owner='sy' AND T.segment_name='HRM_EMPLOYEE';
 
       
 --把表keep在内存中
   ALTER TABLE sy.HRM_EMPLOYEE STORAGE (BUFFER_POOL KEEP);
   
--把索引keep在内存中
    ALTER INDEX sy.HRM_EMPLOYEE_IDX$$_44010001 STORAGE (BUFFER_POOL KEEP);
    
     
    
--查看SGA各组件的大小
    SELECT component, current_size /1024 /1024  , min_size FROM v$sga_dynamic_components;
   
--创建过程显示 表的块的情况
create or replace procedure show_space(p_segname   in varchar2,
                                           p_owner     in varchar2 default user,
                                           p_type      in varchar2 default 'TABLE',
                                           p_partition in varchar2 default '') as

      l_total_blocks       number;
      l_total_bytes        number;
      l_unused_blocks      number;
      l_unused_bytes       number;
      l_LastUsedExtFileId  number;
      l_LastUsedExtBlockId number;
      l_last_used_block    number;
      procedure p(p_label in varchar2, p_num in number) is
      begin
        dbms_output.put_line(rpad(p_label, 40, '.') || p_num);

      end;

    begin
      dbms_space.unused_space(segment_owner             => p_owner,
                              segment_name              => p_segname,
                              segment_type              => p_type,
                              partition_name            => p_partition,
                              total_blocks              => l_total_blocks,
                              total_bytes               => l_total_bytes,
                              unused_blocks             => l_unused_blocks,
                              unused_bytes              => l_unused_bytes,
                              last_used_extent_file_id  => l_LastUsedExtFileId,
                              last_used_extent_block_id => l_LastUsedExtBlockId,
                              last_used_block           => l_last_used_block);
      p('Total Blocks', l_total_blocks);
      p('Total Bytes', l_total_bytes);
      p('Unused Blocks', l_unused_blocks);
      p('Unused Bytes', l_unused_bytes);
      p('Last Used Ext FileId', l_LastUsedExtFileId);
      p('Last Used Ext BlockId', l_LastUsedExtBlockId);
      p('Last Used Block', l_last_used_block);
    end;
    
    
 --调用显示表 的块的使用情况。
   begin
     sys.show_space('HRM_EMPLOYEE','sy','TABLE');
   end;

索引监控

-----对索引进行监控 ，  开始----------------------------------------------- 

--查找未进行监控的索引
   
select 'alter index "' || index_name || '" monitoring usage;',t.table_name,t.table_owner
  from user_indexes t
 where t.index_name not in
       (      
        select index_name from v$object_usage t )
   and t.index_type <> 'LOB';


select * from v$object_usage t ;
--取消监控
alter index "PK_OPR_SEQ_NO" nomonitoring usage;
---------------------------------------------对索引进行监控 ，  结束-----------------------------------------------------

监控数据库日志生成量与生成速度

--按每天，每小时监控日志生成个数
    SELECT SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'),1,5) Day,
           SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'00',1,0)) H00,
           SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'01',1,0)) H01,
           SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'02',1,0)) H02,
           SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'03',1,0)) H03,
           SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'04',1,0)) H04,
           SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'05',1,0)) H05,
           SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'06',1,0)) H06,
           SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'07',1,0)) H07,
           SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'08',1,0)) H08,
           SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'09',1,0)) H09,
           SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'10',1,0)) H10,
           SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'11',1,0)) H11,
           SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'12',1,0)) H12,
           SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'13',1,0)) H13,
           SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'14',1,0)) H14,
           SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'15',1,0)) H15,
           SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'16',1,0)) H16,
           SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'17',1,0)) H17,
           SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'18',1,0)) H18,
           SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'19',1,0)) H19,
           SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'20',1,0)) H20,
           SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'21',1,0)) H21,
           SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'22',1,0)) H22,
           SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'23',1,0)) H23,
           COUNT(*) TOTAL
    FROM v$log_history a
    WHERE first_time>=to_char(sysdate-10)
    GROUP BY SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'),1,5)
    ORDER BY SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'),1,5) DESC;
    
    --查最近一周每天的各个实例的归档日志生成量
   select THREAD#,  
           logtime,  
           count(*),  
           round(sum(blocks * block_size) / 1024 / 1024) mbsize  
      from (select THREAD#,  
                   trunc(first_time, 'dd') as logtime,  
                   a.BLOCKS,  
                   a.BLOCK_SIZE   
              from v$archived_log a  
             where a.FIRST_TIME > trunc(sysdate -7  ) and a.CREATOR='ARCH' )  
     group by THREAD#, logtime  
     order by THREAD#, logtime desc;      

--查最近一周每天的所有实例的归档日志生成量
select  
           logtime,  
           count(*),  
           round(sum(blocks * block_size) / 1024 / 1024) mbsize  
      from (select THREAD#,  
                   trunc(first_time, 'dd') as logtime,  
                   a.BLOCKS,  
                   a.BLOCK_SIZE   
              from v$archived_log a  
             where a.FIRST_TIME > trunc(sysdate -7 ) and a.CREATOR='ARCH' )  
     group by   logtime  
     order by  logtime desc;

--按每天，每小时监控日志生成个数，和 大小
    select logtime,  
           count(*),  
           round(sum(blocks * block_size) / 1024 / 1024) mbsize  
      from (select trunc(first_time, 'dd') as logtime, a.BLOCKS, a.BLOCK_SIZE  
              from v$archived_log a  
             where a.DEST_ID = 1  
               and a.FIRST_TIME > trunc(sysdate - 7))  
     group by logtime  
     order by logtime desc;  
     --查当天每小时的各个实例的归档日志生成量
         select THREAD#,  
           logtime,  
           count(*),  
           round(sum(blocks * block_size) / 1024 / 1024) mbsize  
      from (select a.THREAD#,  
                   trunc(first_time, 'hh') as logtime,  
                   a.BLOCKS,  
                   a.BLOCK_SIZE  
              from v$archived_log a  
             where a.DEST_ID = 1  
               and a.FIRST_TIME > trunc(sysdate))  
     group by THREAD#, logtime  
     order by THREAD#, logtime desc;  
 
    --按每小时监控日志生成个数，和 大小
    select logtime,  
           count(*),  
           round(sum(blocks * block_size) / 1024 / 1024) mbsize  
      from (select trunc(first_time, 'hh') as logtime, a.BLOCKS, a.BLOCK_SIZE  
              from v$archived_log a  
             where a.DEST_ID = 1  
               and a.FIRST_TIME > trunc(sysdate))  
     group by logtime  
     order by logtime desc;   
     
     -- 查询日志文件大小 M.
     select max(t.bytes / 1024 / 1024) || 'M',min(t.bytes / 1024 / 1024 ) ||  'M'  from v$log t;

根据操作系统进程查询会话或执行的SQL

--  先用ps -aux查询，发现有个进程占用大量CPU，记录下PID


-- 查看所有会话对应的sql
SELECT c.spid,b.sql_text, a.sid, a.serial#, osuser, machine  
    FROM v$session a, v$sqlarea b ,v$process c
    WHERE a.sql_address = b.address and a.paddr=c.addr; 

-- 查看某个会话对应的sql内容
SELECT c.spid,b.sql_text, a.sid, a.serial#, osuser, machine  
    FROM v$session a, v$sqlarea b ,v$process c
    WHERE a.sql_address = b.address and a.paddr=c.addr and spid=&pid; 
 
-- 用语句 就能查出等待事件是什么。
 select * from v$session_wait where sid=  --就能查出等待事件是什么。

表空间数据迁移

表空间有三类数据：TABLE,INDEX,LOB,都需要迁移，以腾空表空间。
select segment_type, partition_name, segment_name from dba_segments;

--查看表空间的内容
select t.file_name,
       t1.owner,
 
       t1.segment_name,
       t1.segment_type,
       t1.tablespace_name  --,'alter index ' || t1.OWNER || '.' || t1.segment_name || ' rebuild tablespace USRDATA;' 
  from dba_data_files t, dba_extents t1
 where t.file_id = t1.file_id
   and t.tablespace_name='USERS'  --and t1.segment_type='TABLE'
   --and file_name = '你要查询的数据文件路径';
   ;
   
   
  --将数据文件离线
  select ' alter database datafile ''' || T.file_name || ''' offline;',T.online_status
  from dba_data_files t
  where t.tablespace_name='USERS';
  
  --压缩数据文件大小
  select 'alter database datafile  ' || t.FILE# || '  resize ' || 11     || 'm;', to_number(w.used),
       t.name,
       t.BYTES / 1024 / 1024 / 1024,
       t.BLOCKS * 8 / 1024 / 1024
  from v$datafile t, v_data_files w
  where t.FILE#=w.file_id and  w.tablespace_name='USERS';
  
 --0.移动LOB到新空间
 select 'alter table ' || T.OWNER || '.' || T.TABLE_NAME || '  move tablespace users lob(' || T.COLUMN_NAME || ') store as (tablespace usrdata);', T.*
  from dba_lobs t
  where t.tablespace_name='USERS';
  
  
 --1.迁移表到新的表空间
 SELECT OWNER    
      ,TABLE_NAME ,  'alter TABLE  ' || t.owner || '."' || t.TABLE_NAME || '" MOVE tablespace USRDATA;' ,T.*
FROM DBA_TABLES T
WHERE TABLESPACE_NAME='USERS';


 --2.迁移索引到新的表空间
 SELECT 'alter index ' || t.owner || '."' || t.index_name || '" rebuild tablespace USRDATA;' ,T.*
  FROM dba_indexes t
 WHERE -- index_type = 'NORMAL' ANDAND
    t.tablespace_name='USERS'  
 --  AND dropped = 'NO'; 
 
 --3.重建索引
 SELECT 'alter index ' || t.owner || '."' || t.index_name || '" rebuild;' ,T.*
  FROM dba_indexes t
 WHERE   t.tablespace_name='USERS';
  -- AND t.owner='QHSWST_XTPT'

失效对象管理

select d.EXTENTON3,d.ORDER_ID from RMC_ORDERS_DETAIL d  where d.ORDER_ID=:1 


SELECT * FROM QHSWST_XTPT.RMC_ORDERS_DETAIL


create index QHSWST_XTPT.idx_RMC_ORDERS_DETAIL on QHSWST_XTPT.RMC_ORDERS_DETAIL(ORDER_ID) parallel 4;


 declare
       --类型定义
       cursor c_job
       is
                SELECT CASE T.object_type  
                WHEN 'VIEW' THEN 'Alter view ' || t.owner|| '.' || t.object_name || ' compile '  
                WHEN 'PROCEDURE' THEN 'Alter PROCEDURE ' || t.owner|| '.' || t.object_name || ' compile; '
                WHEN 'FUNCTION' THEN 'Alter function ' || t.owner|| '.' || t.object_name || ' compile; '  
                WHEN 'TRIGGER' THEN 'Alter TRIGGER ' || t.owner|| '.' || t.object_name || ' compile; '     
                WHEN 'SYNONYM' THEN 'Alter SYNONYM ' || t.owner|| '.' || t.object_name || ' compile; '
                WHEN 'TABLE' THEN 'Alter TABLE ' || t.owner|| '.' || t.object_name || ' compile; '
                WHEN 'TYPE' THEN 'Alter TYPE ' || t.owner|| '.' || t.object_name || ' compile; '
                WHEN 'PACKAGE' THEN 'Alter PACKAGE ' || t.owner|| '.' || t.object_name || ' compile; '
                WHEN 'JAVA CLASS' THEN 'Alter JAVA CLASS ' || t.owner|| '.' || t.object_name || ' compile; '
                WHEN 'TYPE BODY' THEN 'Alter TYPE  ' || t.owner|| '.' || t.object_name || ' compile BODY; '   
                WHEN 'MATERIALIZED VIEW' THEN 'Alter MATERIALIZED VIEW ' || t.owner|| '.' || t.object_name || ' compile; '
                WHEN 'PACKAGE BODY' THEN 'Alter package ' || t.owner|| '.' || t.object_name || ' compile body; '
                ELSE '其他' END  altersql
                ,OBJECT_NAME, STATUS,T.object_type,T.owner FROM DBA_OBJECTS t where t.status='INVALID'   
                order by t.owner desc, t.object_type;
       --定义一个游标变量v_cinfo c_emp%ROWTYPE ，该类型为游标c_emp中的一行数据类型
       c_row c_job%rowtype;
       v_sql varchar2(1000);   --  execute immediate 'Alter view HOSPITAL_YIB.BI_SCBCJL compile'  
begin 
       dbms_output.put_line('........error during compiling.....'  );
       for c_row in c_job loop
           v_sql :=   c_row.altersql ;
           v_sql :=replace(v_sql,';','');
           
           begin
               execute immediate v_sql; 
           exception
                when others then
                  begin
                   -- dbms_output.put_line('........error during compiling.....'  );
                    dbms_output.put_line(c_row.object_type || '---' ||c_row.owner || '.' || c_row.OBJECT_NAME  );
                   -- dbms_output.put_line(',编译错误:' || sqlerrm);
                  end;
           end;          
       end loop;
end;



SELECT CASE T.object_type  
WHEN 'VIEW' THEN 'Alter view ' || t.owner|| '.' || t.object_name || ' compile; '  
WHEN 'PROCEDURE' THEN 'Alter PROCEDURE ' || t.owner|| '.' || t.object_name || ' compile; '
WHEN 'FUNCTION' THEN 'Alter function ' || t.owner|| '.' || t.object_name || ' compile; '  
WHEN 'TRIGGER' THEN 'Alter TRIGGER ' || t.owner|| '.' || t.object_name || ' compile; '     
WHEN 'SYNONYM' THEN 'Alter SYNONYM ' || t.owner|| '.' || t.object_name || ' compile; '
WHEN 'TABLE' THEN 'Alter TABLE ' || t.owner|| '.' || t.object_name || ' compile; '
WHEN 'TYPE' THEN 'Alter TYPE ' || t.owner|| '.' || t.object_name || ' compile; '
WHEN 'PACKAGE' THEN 'Alter PACKAGE ' || t.owner|| '.' || t.object_name || ' compile; '
WHEN 'JAVA CLASS' THEN 'Alter JAVA CLASS ' || t.owner|| '.' || t.object_name || ' compile; '
WHEN 'TYPE BODY' THEN 'Alter TYPE  ' || t.owner|| '.' || t.object_name || ' compile BODY; '   
WHEN 'MATERIALIZED VIEW' THEN 'Alter MATERIALIZED VIEW ' || t.owner|| '.' || t.object_name || ' compile; '
WHEN 'PACKAGE BODY' THEN 'Alter package ' || t.owner|| '.' || t.object_name || ' compile body; '
ELSE '其他' END  
,OBJECT_NAME, STATUS,T.object_type,T.owner FROM DBA_OBJECTS t where t.status='INVALID'   
order by t.object_type desc, 1;

查询正在进行的rman任务

SELECT  'kill -9 '||spid      FROM v$process p, v$session s      WHERE p.addr = s.paddr     AND client_info LIKE '%rman%';

来罐百事可乐

关注

2
点赞
踩
2

收藏

觉得还不错? 一键收藏
0
评论
Oracle性能管理常用命令

1.1.1.SQL优化任务查看与参数设置select * from DBA_ADVISOR_EXECUTIONS t where t.task_name like ‘%AUTO%’ order by t.execution_start desc;select dbms_sqltune.report_tuning_task(task_name => ‘SYS_AUTO_SQL_TUNING_TASK’,execution_name => ‘EXEC_329376’) from dual;be
复制链接

扫一扫

专栏目录