Oracle日常维护脚本-白红宇

1.正常停库流程

                         
ps -ef|grep LOCAL=NO|cut -c 9-15|xargs kill -9 
    
shutdown immediate;

2.备份数据库

                         
backup database format 
'/home/orarch_ccmpdb1/backup2/full_%d_%s_%p_%u.%T'
; 
    
backup current controlfile format 
'/home/orarch_ccmpdb1/backup2/controlfile_%d_%s_%p_%u.%T'
;

3.建立连接

                         
ln -s /dev/ccmpvg01/rccmpv01l3111 /home/db/oracle/oradata/cmpdb/rtbs_data2_05_10g 
    
ln -s /dev/ccmpvg01/rccmpv01l3112 /home/db/oracle/oradata/cmpdb/rtbs_data2_06_10g

4.增加表空间的空间

                         
alter tablespace tbs_data2 add datafile  
    
'/home/db/oracle/oradata/cmpdb/rtbs_data2_05_10g'
size 10200m, 
    
'/home/db/oracle/oradata/cmpdb/rtbs_data2_06_10g'
size 10200m;

5.查看主分区表和子分区表

                         
col partition_keyname 
for
a20 
    
col sub_partition_keyname 
for
a20 
    
select a.*,b.COLUMN_NAME sub_partition_keyname from 
    
(select t1.OWNER, 
           
t1.TABLE_NAME, 
           
t1.PARTITIONING_TYPE, 
           
t1.SUBPARTITIONING_TYPE, 
           
t1.PARTITION_COUNT, 
           
t2.column_name partition_keyname 
    
from dba_part_tables t1,dba_part_key_columns t2  
    
where t1.owner = t2.owner 
          
and t1.table_name = t2.name 
          
and t1.OWNER = 
'BILL'
          
and t1.TABLE_NAME = 
'HA_CREDIT_CTRL'
    
) a,dba_subpart_key_columns b 
    
where a.owner = b.owner(+) 
          
and a.table_name = b.name(+);

6.查看子分区信息

                         
select TABLE_OWNER, 
           
TABLE_NAME, 
           
PARTITION_NAME, 
           
SUBPARTITION_NAME, 
           
SUBPARTITION_POSITION  
    
from DBA_TAB_SUBPARTITIONS 
    
where TABLE_OWNER = 
'CASHBILL_TEST'
          
and TABLE_NAME = 
'BALANCE_PAYOUT'
    
order by PARTITION_NAME,SUBPARTITION_POSITION; 
 
select dbms_metadata.get_ddl(
'TABLE'
,
'ACCT_ITEM_1080'
,
'CASH_BILL'
) from dual;

7.段级别统计信息

                         
select owner,object_name,statistic_name,value  
    
from v$segment_statistics  
    
where owner=
'SYS'
and object_name = 
'I_FILE2'

8.看最严重的前20个等待事件

                         
    
set lines 200 
    
set pages 200 
    
col sql_text 
for
a70 
    
col name 
for
a30 
    
col parameter1 
for
a30 
    
col parameter2 
for
a30 
    
col parameter3 
for
a30 
    
col value 
for
999999999999999999 
    
col 
gets
for
9999999999999999999 
    
col wait_time 
for
9999999999999999 
    
select * from (select event,wait_time,SECONDS_IN_WAIT from v$session_wait  
    
where wait_class not in (
'Idle'
,
'Network'
) order by SECONDS_IN_WAIT desc)  
    
where rownum <= 20;
        
      

9.看最严重的等待事件

                         
select event,count(*) from v$session  
    
where wait_class not in (
'Idle'
,
'Network'
)  
    
group by event  
    
order by 2 desc;

10.看当前连接会话信息

                         
select username,program,status,count(*) from v$session  
    
where username is not null group by username,program,status order by 3; 
    
select prev_sql_id,count(*) from v$session  
    
where program is null group by prev_sql_id order by 2

11.最严重的前10个latch

                         
select * from (select addr,name,
gets
,misses,sleeps,SPIN_GETS,WAIT_TIME  
    
from v$latch order by misses desc) where rownum <= 10 
    
--latch: row cache objects 
    
select distinct s.kqrstcln latch#, 
            
r.cache#, 
            
r.parameter name, 
            
r.type, 
            
r.subordinate#, 
            
r.
gets
    
from v$rowcache r,x$kqrst s 
    
where r.cache# = s.kqrstcid 
    
order by 1,4,5;

                         
    
select * from (select cache#,type,SUBORDINATE#,parameter,count, 
    
USAGE,GETS,GETMISSES from v$rowcache order by GETMISSES desc)  
    
where rownum <= 10; 
    
select addr,latch#,child#,level#,name,
gets
from v$latch_children  
    
where name = 
'row cache objects'
and 
gets
<>0 order by 
gets
;
        
      

--latch cbc

--个个查询

                         
    
select * from (select addr from v$latch_children  
    
where name = 
'cache buffers chains'
order by misses)  
    
where rownum <= 10; 
    
select 
/*+ rule */
owner,object_name from dba_objects  
    
where object_id in 
    
(select 
/*+ rule */
distinct obj from x$bh  
    
where hladdr = 
'C000000BC6813AB8'
);
        
      

12.查询前十个cbc latch最严重对应的对象

                         
    
select 
/*+ rule */
owner,object_name from dba_objects where object_id in  
    
( 
    
select 
/*+ rule */
distinct obj from x$bh where hladdr in  
     
(select 
/*+ rule */
* from (select 
/*+ rule */
addr  
      
from v$latch_children  
      
where name = 
'cache buffers chains'
      
order by 
gets
desc) where rownum <= 10) 
    
);
        
      

13.检查分区表的创建与否

                         
select TABLE_OWNER, 
           
TABLE_NAME, 
           
PARTITION_NAME, 
           
SUBPARTITION_NAME, 
           
SUBPARTITION_POSITION  
    
from DBA_TAB_SUBPARTITIONS 
    
where TABLE_OWNER = 
'CASHBILL_TEST'
          
and TABLE_NAME = 
'BALANCE_PAYOUT'
    
order by PARTITION_NAME,SUBPARTITION_POSITION;

14.表空间自动扩展性

                         
SELECT T.TABLESPACE_NAME,D.FILE_NAME,D.AUTOEXTENSIBLE,D.BYTES,D.MAXBYTES,D.STATUS  
    
FROM DBA_TABLESPACES T,DBA_DATA_FILES D  
    
WHERE T. TABLESPACE_NAME =D. TABLESPACE_NAME  
    
ORDER BY TABLESPACE_NAME,FILE_NAME;

15.全表扫描 ,注意修改 OBJECT_OWNER

                         
select distinct t.sql_text from v$sqlarea t, v$sql_plan p 
    
where t.hash_value=p.hash_value  
    
and t.SQL_ID=p.SQL_ID 
    
and t.PLAN_HASH_VALUE=p.PLAN_HASH_VALUE 
    
and p.operation=
'TABLE ACCESS'
    
and p.options=
'FULL'
    
and p.OBJECT_OWNER =
'TBMS'
;

16.全索引扫描, 注意修改 OBJECT_OWNER

                         
select distinct t.sql_text from v$sqlarea t, v$sql_plan p 
    
where t.hash_value=p.hash_value  
    
and t.SQL_ID=p.SQL_ID 
    
and t.PLAN_HASH_VALUE=p.PLAN_HASH_VALUE 
    
and p.operation=
'INDEX'
    
and p.options=
'FULL SCAN'
    
and p.OBJECT_OWNER =
'TBMS'
;

17.查看归档错误:

                         
select dest_id,error from v$archive_dest where dest_id=1;

18.数据字典命中率

                         
column parameter format a21 
    
column pct_succ_gets format 999.9 
    
column updates format 999,999,999 
    
SELECT parameter 
    
, sum(
gets
) 
    
, sum(getmisses) 
    
, 100*sum(
gets
- getmisses) / sum(
gets
) pct_succ_gets 
    
, sum(modifications) updates 
    
FROM V$ROWCACHE WHERE 
gets
> 0 
    
GROUP BY parameter;

19.查看归档错误

                         
select dest_id,error from v$archive_dest where dest_id=1;

20.是否有行迁移

--收集对象的统计信息:

                         
analyze table t compute statistics;

                         
select TABLE_NAME,BLOCKS,EMPTY_BLOCKS,NUM_ROWS,AVG_ROW_LEN,CHAIN_CNT  
    
from user_tables where table_name=
'T'
;

21.取出全表扫描的表

                         
select sql_text from v$sqltext where sql_id in  
    
(select sql_id from v$sql_plan   
    
where operation like 
'%TABLE ACCESS%'
    
and options like 
'%FULL%'
)  
    
where sql_text like 
'%EMP%'

22.全表扫描统计项

                         
select name,value from v$sysstat where name like 
'table scan%'
;

23.哪个用户哪个表作了全表扫描

                         
select OPERATION,object_owner,OPTIONS,OBJECT_NAME from  
    
v$sql_plan 
    
where options=
'FULL'
    
and OPERATION=
'TABLE ACCESS'
    
and object_owner=
'SCOTT'
;

24.哪个用户下有多少张表作了全表扫描

                         
    
select  ss.username||
'('
||se.sid||
') '
"User Process"
, 
    
sum(decode(name,
'table scans (short tables)'
,value)) 
"Short Scans"
, 
    
sum(decode(name,
'table scans (long tables)'
, value)) 
"Long Scans"
, 
    
sum(decode(name,
'table scan rows gotten'
,value)) 
"Rows Retreived"
    
from    v$session ss, 
    
v$sesstat se, 
    
v$statname sn 
    
where   se.statistic# = sn.statistic# 
    
and     (name  like 
'%table scans (short tables)%'
    
or   name  like 
'%table scans (long tables)%'
    
or   name  like 
'%table scan rows gotten%'
) 
    
and     se.sid = ss.sid 
    
and     ss.username is not null 
    
group   by ss.username||
'('
||se.sid||
') '
    
/
        
      

25.增加全表扫描时I/O的吞吐量

                         
db_file_multiblock_read_count=1~128

26.使用并行处理提高全表扫描效率:

                         
select 
/*+ parallel (ob ,16)*/
count(*) from OB;

27.条带化堆表数据:

                         
    
alter table ob allocate extent (size 5m datafile 
'/u01/app/oracle/oradata/madrid/users01.dbf'
); 
    
alter table ob allocate extent (size 5m datafile 
'/u01/app/oracle/oradata/madrid/users02.dbf'
); 
    
alter table ob allocate extent (size 5m datafile 
'/u01/app/oracle/oradata/madrid/users03.dbf'
); 
    
alter table ob allocate extent (size 5m datafile 
'/u01/app/oracle/oradata/madrid/users04.dbf'
);
        
      

28.发生cbc latch争用时，如可查是哪个sql语句造成的？

                         
select v.SQL_HASH_VALUE, v.SQL_ADDRESS, v.INST_ID, v.EVENT 
    
from gv$session v 
    
where v.EVENT = 
'cache buffer chains'
;

                         
select a.INST_ID, a.SQL_TEXT  from gv$sqltext a 
    
where a.HASH_VALUE = 
'&HASH_VALUE'
    
and a.ADDRESS = 
'&ADDRESS'
    
ORDER BY address, hash_value, piece;

29.数据字典命中率

                         
column parameter format a21 
    
column pct_succ_gets format 999.9 
    
column updates format 999,999,999 
    
SELECT parameter 
    
, sum(
gets
) 
    
, sum(getmisses) 
    
, 100*sum(
gets
- getmisses) / sum(
gets
) pct_succ_gets 
    
, sum(modifications) updates 
    
FROM V$ROWCACHE WHERE 
gets
> 0 
    
GROUP BY parameter;

30.估算 db_cache 放大或减小后对 I/o 的影响

                         
    
COLUMN size_for_estimate FORMAT 999,999,999,999 heading 
'Cache Size (MB)'
    
COLUMN buffers_for_estimate FORMAT 999,999,999 heading 
'Buffers'
    
COLUMN estd_physical_read_factor FORMAT 999.90 heading 
'Estd Phys|Read Factor'
    
COLUMN estd_physical_reads FORMAT 999,999,999 heading 
'Estd Phys| Reads'
    
SELECT       size_for_estimate,    buffers_for_estimate,    estd_physical_read_factor, 
    
estd_physical_reads 
    
FROM V$DB_CACHE_ADVICE 
    
WHERE name = 
'DEFAULT'
    
AND block_size = (SELECT value FROM V$PARAMETER WHERE name = 
'db_block_size'
) 
    
AND advice_status = 
'ON'
;
        
      

本文转自张冲andy 博客园博客，原文链接： http://www.cnblogs.com/andy6/p/5840657.html ，如需转载请自行联系原作者