日常工作使用work.txt内容

原创

mb646da62f0d7e4 2023-05-24 14:13:45 ©著作权

文章标签 工作 session 数据库 file cache 文章分类 JavaScript 前端开发

©著作权归作者所有：来自51CTO博客作者mb646da62f0d7e4的原创作品，请联系作者获取转载授权，否则将追究法律责任

1.数据库版本
select * from v$instance
2.库物理logic结构
(1).重做日志
select * from v$log;
所在位置：
select * from v$logfile
select a.group#,b.member,a.bytes/1024/1024 as "size(MB)"
from v$log a,v$logfile b
where b.group#=a.group#
order by a.group#
(2).物理文件存放位置
select * from dba_data_files
(3).逻辑表空间查看
select * from dba_tablespaces //bigfile/segment man/type/status
(4)段和表空间关系
select * from dba_extents /partion/
select * from dba_segments
(5)查看临时表空间
select * from dba_temp_files
select * from v$tempfile
(6)undo表空间
select * from dba_undo_extents
3.查看用户默认平空间和临时平空间
select * from dba_users
4.数据库每月增长量
select to_char(creation_time,'RRRR MONTH')"MONTH",
SUM(BYTES)/1024/1024/1024 "GROWTH SIZE"
FROM SYS.V_$DATAFILE
WHERE CREATION_TIME>SYSDATE-365
GROUP BY to_char(creation_time,'RRRR MONTH')
5.数据文件每月成长量
select a.ts# as "tablespace number",
b.name as "tablspace name",
to_char(a.creation_time,'RRRR MONTH') "MONTH",
SUM(A.BYTES)/1024/1024/1024 "GROWTH SIZE"
FROM SYS.V_$DATAFILE A,SYS.V_$TABLESPACE B
WHERE A.CREATION_TIME>SYSDATE-365
AND A.TS#=B.TS#
GROUP BY A.TS#,B.NAME,TO_CHAR(A.CREATION_TIME,'RRRR MONTH')
6.查看警告日志文件
show parameter dump
检查近段时间告警日志有什么异常
ORA-00600或ORA-07445(留意)
上下班都检查次
7.了解重启过程，重启计划，错误
8.系统资源检查
CPU 内存 I/O
资源消耗规律（eg.月底，月中)
系统繁忙时间和繁忙度
CPU使用率规律
第三方监控软件
9.查看数据库当前等待事件
（空闲与非空闲等待事件）
锁，索引,DDL
10.处理方法
1、看系统资源，资源是否耗尽
CPU使用100%，内存快用完了，用了大量的swap，是否有异常进程，这些异常进程是oracle还是其他应用程序
2、看等待事件，看系统正处于什么状况
3、看告警日志
11.backup/restore
当前库是不是有备份
采用什么方法备份
用oracle的rman？exp/imp？冷备?
还是用第三方工具？netbackup？
还是直接用存储镜像备份？
备份的周期？多长备份一次？备份一次需要多长时间？备份到磁带库还是硬盘上？

表空间的剩余用量: 

 SET LINESIZE 120 

 select a.tablespace_name, 

 sum((a.tots)/1024/1024) "total size", 

 sum((a.sumb)/1024/1024) "total free size", 

 sum(a.sumb)*100/sum(a.tots) "pct_free", 

 sum(a.largest) "max free size", 

 sum(a.chunks) "free extent number" 

 from (select tablespace_name, 

 0 tots, 

 sum(bytes) sumb, 

 max(bytes) largest, 

 count(*) chunks 

 from dba_free_space a 

 group by tablespace_name 

 union 

 select tablespace_name,sum(bytes) tots,0,0,0 

 from dba_data_files 

 group by tablespace_name)a 

 group by a.tablespace_name 

 ORDER BY 4; 


 确认数据文件的使用量: 

 select 

 d.tablespace_name tablespace, 

 d.file_name filename, 

 d.bytes "total file size", 

 nvl((d.bytes-s.bytes),d.bytes) "used size", 

 trunc(((nvl((d.bytes-s.bytes),d.bytes))/d.bytes)*100) PCT_USED 

 FROM sys.dba_data_files d, 

 v$datafile v, 

 (select file_id,sum(bytes) bytes 

 from sys.dba_free_space 

 group by file_id)s 

 where (s.file_id(+)=d.file_id) 

 and (d.file_name=v.name) 

 union 

 select d.tablespace_name tablespace, 

 d.file_name filename, 

 d.bytes "total file size", 

 nvl(t.bytes_cached,0) "used size", 

 trunc((t.bytes_cached/d.bytes)*100) pct_used 

 from sys.dba_temp_files d,v$temp_extent_pool t,v$tempfile v 

 where (t.file_id(+)=d.file_id) 

 and (d.file_id=v.file#); 


 查看数据库缓冲快区的HIT RATIO 

 select 1-(phy.value/(cur.value+con.value)) "cache hit ratio", 

 round((1-(phy.value/(cur.value+con.value)))*100,2) "ratio" 

 from v$sysstat cur,v$sysstat con,v$sysstat phy 

 where cur.name='DB BLOCK GETS' 

 AND CON.NAME='CONSISTENT GETS' 

 AND PHY.NAME='PHYSICAL READS' 


 确认使用者使用PGA的大小 

 select username,name,value 

 from v$statname n,v$session s,v$sesstat t 

 where s.sid=t.sid 

 and n.statistic#=t.statistic# 

 and s.type='USER' 

 AND S.USERNAME IS NOT NULL 

 AND N.NAME='SESSION PGA MEMORY' 

 AND USERNAME NOT LIKE 'SYSTEM' 

 AND T.VALUE>30000; 


 查看前10名使用CPU用量的联机 

 select * 

 from (select 

 'p', 

 s.sid, 

 s.status, 

 t.value/100/60 "cpu time", 

 floor(last_call_et/60) "last call et", 

 to_char(s.logon_time,'mm/dd hh24:mi') "logon time", 

 s.username, 

 s.process, 

 p.spid, 

 s.module, 

 s.machine, 

 s.sql_hash_value 

 from v$sesstat t,v$session s,v$process p 

 where t.statistic#=12 

 and s.sid=t.sid 

 and s.paddr=p.addr 

 and s.type='USER' 

 and s.sql_hash_value!=1425819161 

 union 

 select 

 'N', 

 s.sid, 

 s.status, 

 t.value*-1/100/60 "cpu time", 

 floor(last_call_et/60) "last call et", 

 to_char(s.logon_time,'mm/dd hh24:mi') "logon time", 

 s.username, 

 s.process, 

 p.spid, 

 s.module, 

 s.machine, 

 s.sql_hash_value 

 from v$sesstat t,v$session s,v$process p 

 where t.statistic#=12 

 and s.sid=t.sid 

 and s.paddr=p.addr 

 and s.type='USER' 

 and s.sql_hash_value!=1425819161 

 and t.value<0 

 order by 4 desc) 

 where rownum<11; 


 查询前10名排序量最大使用者联机 

 select * 

 from (select s.sid, 

 s.status, 

 b.spid, 

 s.sql_hash_value sesshash, 

 u.sqlhash sorthash, 

 s.username, 

 u.tablespace, 

 sum(u.blocks*p.value/1024/1024)mbused, 

 sum(u.extents) noexts, 

 u.segtype, 

 s.module||' - '||s.program proginfo 

 from v$sort_usage u,v$session s,v$parameter p, 

 v$process b 

 where u.session_addr=s.saddr 

 and p.name='DB_BLOCK_SIZE' 

 and b.addr=s.paddr 

 group by s.sid, 

 s.status, 

 b.spid, 

 s.sql_hash_value, 

 u.sqlhash, 

 s.username, 

 u.tablespace, 

 u.segtype, 

 s.module||' - '||s.program 

 order by 8 desc,4) 

 where rownum<11; 


 查询前10名数据库等待事件 

 select * 

 from(select event,total_waits,time_waited,average_wait 

 from v$system_event 

 where event not like '%IPC%' 

 AND EVENT NOT LIKE 'SQL*NET%' 

 AND EVENT NOT LIKE 'PIPE%' 

 AND EVENT NOT LIKE 'NULL%' 

 AND EVENT NOT LIKE 'WAKEUP%' 

 AND EVENT NOT LIKE 'PX%' 

 AND EVENT NOT LIKE '%TIMER%' 

 ORDER BY 3 DESC) 

 WHERE ROWNUM<11; 


 查询前10名使用CURSOR最多的使用者联机 

 select * from ( 

 select s.sid, 

 v0.value cum_open_cursors, 

 v1.value cur_open_cursors, 

 v2.value cache_hits, 

 v3.value cache_count, 

 v4.value hard_parse, 

 v5.value-v4.value soft_parse, 

 s.osuser,s.machine, 

 nvl(s.module,s.program) proginfo 

 from v$sesstat v0,v$sesstat v1,v$sesstat v2,v$sesstat v3, 

 v$sesstat v4,v$sesstat v5, 

 v$session s 

 where v0.statistic#=2 

 and v0.sid=s.sid 

 and v1.statistic#=3 

 and v1.sid=s.sid 

 and v2.statistic#=191 

 and v2.sid=s.sid 

 and v3.statistic#=192 

 and v3.sid=s.sid 

 and v4.statistic#=180 

 and v4.sid=s.sid 

 and v5.statistic#=179 

 and v5.sid=s.sid 

 order by 3 desc,7 desc) 

 where rownum<11;