1、主机动态情况检查
uname -a
top 或者topas
vmstat 1 10
ps -ef |grep ora|wc -l
ps -ef |grep ora|grep LOCAL|wc -l
2、性能视图备份
sqlplus " / as sysdba"
create user ljb identified by ljb;
grant dba to ljb;
connect ljb/ljb;
create table diag_session_&yyyymmdd_seq_area nologging as select * from gv$session;
..............
需要备份的数据库性能视图有:
gv$session:
gv_session_wait:
gv$process:
gv$sql:
gv$sqlarea:
gv$sql_plan:
gv$lock:
gv$locked_objects:
gv$access
gv$latch:
gv$latch_children:
gv_$Librarycache:
gv_$rowcache:
gv$sort_segment:
gv$sort_usage:
gv$log_history:
gv$log:
gv$logfile:
gv$transaction:
gv$parameter:gv$session_longops:
gv$bh:
gv$filestat:
gv$segstat:
gv$tempstat:
gv$datafile:
gv$tempfile:
gv$open_cusors:
3、当系统觉得有问题时,可以考虑立即取一个断点基线,作为AWR报表的一个断点:
sqlplus " / as sysdba"
exec dbms_workload_repository.create_snapshot();
4、观察临时表空间和回滚段表空间情况
--查谁占用了undo表空间
select r.name 回滚段名,
rssize / 1024 / 1024 / 1024 "rssize(g)",
s.sid,
s.serial#,
s.username 用户名,
s.status,
s.sql_hash_value,
s.sql_address,
s.machine,
s.module,
substr(s.program, 1, 78) 操作程序,
r.usn,
hwmsize / 1024 / 1024 / 1024,
shrinks,
xacts
from sys.v_$session s,
sys.v_$transaction t,
sys.v_$rollname r,
v$rollstat rs
where t.addr = s.taddr
and t.xidusn = r.usn
and r.usn = rs.usn
order by rssize desc;
--查谁占用了temp表空间
select t.blocks * 16 / 1024 / 1024 / 1024,
s.username,
s.schemaname,
t.tablespace,
t.segtype,
t.extents,
s.program,
s.osuser,
s.terminal,
s.sid,
s.serial#
from v$sort_usage t, v$session s
where t.session_addr = s.saddr;
--还可查到具体SQL
select r.name 回滚段名,
rssize / 1024 / 1024 / 1024 "rssize(g)",
s.sid,
s.serial#,
s.username 用户名,
s.status,
s.sql_hash_value,
s.sql_address,
s.machine,
s.module,
substr(s.program, 1, 78) 操作程序,
r.usn,
hwmsize / 1024 / 1024 / 1024,
shrinks,
xacts
from sys.v_$session s,
sys.v_$transaction t,
sys.v_$rollname r,
v$rollstat rs
where t.addr = s.taddr
and t.xidusn = r.usn
and r.usn = rs.usn
order by rssize desc;
- 通过主机进程PID查SQL(比较慢,有时无效)
select /*+ordered*/
sql_text
from v$sqltext a
where (a.hash_value, a.address) in
(select decode(sql_hash_value, 0, prev_hash_value, sql_hash_value),
decode(sql_hash_value, 0, pre_sql_addr, sqladdress)
from v$session b shere b.paddr = (select addr
from v$process c
where c.spid = '&pid'))
order by piect asc;
未避免性能问题,可采用如下三步骤来完成
1.关键获取addr地址
select spid,
addr,
t.pga_used_mem,
t.pga_alloc_mem,
t.pga_freeable_mem,
t.pga_max_mem
from v$process t
where spid = '1667438';
2、根据addr地址,关键得到sql_id
select t.sid,
t.program,
t.machine,
t.logon_time,
t.wait_class,
t.wait_time,
t.seconds_in_wait,
t.event,
t.sql_id,
t.prev_sql_id
from v$session t
where paddr = '070000018fbf9210';
3、根据sql_id知道是什么SQL
select t.sql_id,
t.sql_text,
t.executions,
t.first_load_time,
t.last_load_time,
t.buffer_gets,
t.rows_processed
from v$sql t
where sql_id in ('fq0w89mgsgzvs');
查看逻辑读最多
select *
from (select sql_id,
s.executions, --执行次数
s.LAST_LOAD_TIME,
s.first_load_time,
s.disk_reads, --物理读
s.buffer_gets, --逻辑读
s.parse_calls --解析次数
from v$sql s
where s.BUFFER_GETS > 300
order by buffer_gets desc)
where rownum <= 20;
静态整体问题的解决流程
df -h //查看磁盘使用情况
cat /proc/meminfo //主机内存使用情况
cat /proc/cpuinfo //主机CPU使用情况
检查数据库表和索引是否存在并行度设在其中的情况(很多时候有人用parallel建了表或索引,忘记alter table xxx noparallel关闭了)
select t.owner, t.table_name, degree
from dba_tables t
where t.degree > '1';
select t.owner, t.table_name, index_name, degree, status
from dba_indexes t
where owner in ('LJB')
and t.degree > '1';
--有问题就处理,比如索引有并行,就处理如下
select 'alter index' || t.owner || '.' || index_name || 'noparallel;'
from dba_indexes t
where owner in ('LJB')
and t.degree > '1';
--awr addm ash awrddrpt awrsqrpt 等方式观察数据库
exec dbms_workload_repository.create_snapshot();
sqlplus "/ as sysdba"
@?/rdbms/admin/awrrpt.sql
@?/rdbms/admin/addmrpt.sql
@?/rdbms/admin/ashrpt.sql
@?/rdbms/admin/awrddrpt.sql
@?/rdbms/admin/awrsqrpt.sql