分享程序员开发的那些事...
更新时间：2022-09-13 17:12:17
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 ，如需转载请自行联系原作者