1.正常停库流程
|
1
2
|
ps -ef|grep LOCAL=NO|cut -c 9-15|xargs kill -9
shutdown immediate;
|
2.备份数据库
|
1
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.建立连接
|
1
2
|
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.增加表空间的空间
|
1
2
3
|
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.查看主分区表和子分区表
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
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.查看子分区信息
|
1
2
3
4
5
6
7
8
9
10
11
|
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.段级别统计信息
|
1
2
3
|
select owner,object_name,statistic_name,value
from v$segment_statistics
where owner=
'SYS'
and object_name =
'I_FILE2'
|
8.看最严重的前20个等待事件
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
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.看最严重的等待事件
|
1
2
3
4
|
select event,count(*) from v$session
where wait_class not in (
'Idle'
,
'Network'
)
group by event
order by 2 desc;
|
10.看当前连接会话信息
|
1
2
3
4
|
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
|
1
2
3
4
5
6
7
8
9
10
11
12
|
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;
|
|
1
2
3
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
--个个查询
|
1
2
3
4
5
6
7
|
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最严重对应的对象
|
1
2
3
4
5
6
7
8
|
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.检查分区表的创建与否
|
1
2
3
4
5
6
7
8
9
|
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.表空间自动扩展性
|
1
2
3
4
|
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
|
1
2
3
4
5
6
7
|
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
|
1
2
3
4
5
6
7
|
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.查看归档错误:
|
1
|
select dest_id,error from v$archive_dest where dest_id=1;
|
18.数据字典命中率
|
1
2
3
4
5
6
7
8
9
10
|
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.查看归档错误
|
1
|
select dest_id,error from v$archive_dest where dest_id=1;
|
20.是否有行迁移
--收集对象的统计信息:
|
1
|
analyze table t compute statistics;
|
|
1
2
|
select TABLE_NAME,BLOCKS,EMPTY_BLOCKS,NUM_ROWS,AVG_ROW_LEN,CHAIN_CNT
from user_tables where table_name=
'T'
;
|
21.取出全表扫描的表
|
1
2
3
4
5
|
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.全表扫描统计项
|
1
|
select name,value from v$sysstat where name like
'table scan%'
;
|
23.哪个用户哪个表作了全表扫描
|
1
2
3
4
5
|
select OPERATION,object_owner,OPTIONS,OBJECT_NAME from
v$sql_plan
where options=
'FULL'
and OPERATION=
'TABLE ACCESS'
and object_owner=
'SCOTT'
;
|
24.哪个用户下有多少张表作了全表扫描
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
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的吞吐量
|
1
|
db_file_multiblock_read_count=1~128
|
26.使用并行处理提高全表扫描效率:
|
1
|
select
/*+ parallel (ob ,16)*/
count(*) from OB;
|
27.条带化堆表数据:
|
1
2
3
4
|
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语句造成的 ？
|
1
2
3
|
select v.SQL_HASH_VALUE, v.SQL_ADDRESS, v.INST_ID, v.EVENT
from gv$session v
where v.EVENT =
'cache buffer chains'
;
|
|
1
2
3
4
|
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.数据字典命中率
|
1
2
3
4
5
6
7
8
9
10
|
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 的影响
|
1
2
3
4
5
6
7
8
9
10
|
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 ，如需转载请自行联系原作者
