运维积累

TOP
首先查看系统资源占用信息，TOP看一下
 
 
ps -mp 4318 -o THREAD,tid,time
再通过ps命令查看这个程序的线程信息,tid代码线程ID，time代表这个线程的已运行时间
 
由上面TOP可知进程ID为15669
 
printf “%x\n” 4329
10e9n
有了线程ID的16进制后，再在jstack中查看进程堆栈信息(之所有拿到TID信息，主要是为了查找方便
jstack 4318 |grep  10e9n
 
1.top  查到pid 28555
2.ps aux|grep 28555 确定到是tomcat的进程
3.显示线程列表 ps -mp 28555 -o THREAD,tid,time   查到tid 28802
4. printf "%x\n" 28802 将线程id，tid进行16进制转换
5.jstack pid |grep tid -A 30 显示堆栈信息 jstack 28555 |grep 28802 -A 30
 
 
iostat -dxm 3 查看io
 
iotop -oP 只显示有I/O行为的进程
 pidstat -d 1 展示I/O统计，每秒更新一次
 
1、查看什么sql占用高
select sql_text
  from v$process pr, v$session ss, v$sqlarea sl
 where pr.addr = ss.PADDR
   and ss.SQL_HASH_VALUE = sl.HASH_VALUE
   and pr.spid = 1606;
 
2、查看这些占用CPU资源很高的Oracle进程究竟是在做什么操作
select sql_text,spid,v$session.program,process from 
　　v$sqlarea,v$session,v$process
　　where v$sqlarea.address=v$session.sql_address
 
1.查询表空间使用率

自动扩展和非自动扩展均使用

select tbs_used_info.tablespace_name,

       tbs_used_info.alloc_mb,
       tbs_used_info.used_mb,
       tbs_used_info.max_mb,
       tbs_used_info.free_of_max_mb,
       tbs_used_info.used_of_max || '%' used_of_max_pct
  from (select a.tablespace_name,
               round(a.bytes_alloc / 1024 / 1024) alloc_mb,
               round((a.bytes_alloc - nvl(b.bytes_free, 0)) / 1024 / 1024) used_mb,
               round((a.bytes_alloc - nvl(b.bytes_free, 0)) * 100 /
                     a.maxbytes) used_of_max,
               round((a.maxbytes - a.bytes_alloc + nvl(b.bytes_free, 0)) /
                     1048576) free_of_max_mb,
               round(a.maxbytes / 1048576) max_mb
          from (select f.tablespace_name,
                       sum(f.bytes) bytes_alloc,
                       sum(decode(f.autoextensible,
                                  'YES',
                                  f.maxbytes,
                                  'NO',
                                  f.bytes)) maxbytes
                  from dba_data_files f
                 group by tablespace_name) a,
               (select f.tablespace_name, sum(f.bytes) bytes_free
                  from dba_free_space f
                 group by tablespace_name) b
         where a.tablespace_name = b.tablespace_name(+)) tbs_used_info
 order by tbs_used_info.used_of_max desc;

2.查看总消耗时间最多的前10条SQL语句
select * from (select v.sql_id,v.child_number,v.sql_text,v.elapsed_time,v.cpu_time,v.disk_reads,rank() over(order by v.elapsed_time desc) elapsed_rank from v$sql v) a where elapsed_rank <= 10; 
 
 
3.查看CPU消耗时间最多的前10条SQL语句
 
select * from (select v.sql_id,v.child_number,v.sql_text,v.elapsed_time,v.cpu_time,v.disk_reads,rank() over(order by v.cpu_time desc) elapsed_rank from v$sql v) a where elapsed_rank <= 10; 
 
4.查看消耗磁盘读取最多的前10条SQL语句
 
select *
from (select v.sql_id,
v.child_number,
v.sql_text,
v.elapsed_time,
v.cpu_time,
v.disk_reads,
rank() over(order by v.disk_reads desc) elapsed_rank
from v$sql v) a
where elapsed_rank <= 10;
 
 
 
5. IOps和IO吞吐量 （oracle 11g）
select sum(decode(name,'physical read IO requests',value,'physical write IO requests',value,0)) as iops,
sum(decode(name,'physical read bytes',value,'physical write bytes',value,0)) / 1024 / 1024 as mbps from v$sysstat
where name in ('physical read IO requests','physical write IO requests','physical read bytes','physical read total bytes',
'physical write bytes','physical write total bytes','physical read total IO requests','physical write total IO requests'); 
 
 
6.内存使用情况
 
SGA / PGA 使用情况
 
 
 
-- pctused: 使用率
 
 
 
select name,total,round(total-free,2) used, round(free,2) free,round((total-free)/total*100,2) pctused from
(select 'SGA' name,(select sum(value/1024/1024) from v$sga) total,
(select sum(bytes/1024/1024) from v$sgastat where name='free memory')free from dual)
union
select name,total,round(used,2)used,round(total-used,2)free,round(used/total*100,2)pctused from (
select 'PGA' name,(select value/1024/1024 total from v$pgastat where name='aggregate PGA target parameter')total,
(select value/1024/1024 used from v$pgastat where name='total PGA allocated')used from dual);
 
 
根据占用cpu高的进程号来查询者个进程执行的sql语句：
 
SELECT 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, prev_sql_addr, sql_address) 
FROM v$session b 
WHERE b.paddr = (SELECT addr FROM v$process c WHERE c.spid = '&pid')) 
ORDER BY piece ASC;
 
例：查询31968进程对应的sql语言：
 
SELECT 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, prev_sql_addr, sql_address) 
FROM v$session b 
WHERE b.paddr = (SELECT addr FROM v$process c WHERE c.spid = '31968')) 
ORDER BY piece ASC;
 
通过pid查看程序执行对应的sql:
 
select sql_text 
from v$process pr,v$session ss,v$sqlarea sl 
where pr.addr=ss.PADDR  
and ss.SQL_HASH_VALUE=sl.HASH_VALUE 
and pr.spid=10840;
 
查看当前正在执行的SQL：
 
select a.program, b.spid, c.sql_text,c.SQL_ID 
from v$session a, v$process b, v$sqlarea c 
where a.paddr = b.addr 
and a.sql_hash_value = c.hash_value 
and a.username is not null;
 
1，查看CPU占用高的进程号
 
2，根据进程号查看该进程在做什么
 
select sql_text,spid,v$session.program,process 
from v$sqlarea,v$session,v$process 
where v$sqlarea.address=v$session.sql_address 
and v$sqlarea.hash_value=v$session.sql_hash_value 
and v$session.paddr=v$process.addr 
and v$process.spid in(PID);
 
3，看看数据库的等待事件都有些什么
 
select sid,event,p1,p1text from v$session_wait;
 
看看等待事件由什么进程造成的
 
select spid from v$process where addr in(select paddr from v$session where sid in(84,102,101));
 
利用一下脚本可以由已知session的SID来获得SQL语句
 
select sql_text 
from v$sql_text a 
where a.hash_value=(
  select sql_hash_value 
  from v$session b 
  where b.SID=’&sid’) 
order by piect ASC;
查看当前会话sql_id
 
select sql_id ,username,status,event from v$session;
根据sql_id查看sql语句
 
select sql_text from v$sql where sql_id='cx7sxk891r782';
 
 
ORACLE查询当前执行效率低的sql
 
--CPU高的SQL
select sql_text from v$sql order by cpu_time desc where rownum <10
--逻辑读多的SQL：
select * from (select buffer_gets, sql_text from v$sqlarea where buffer_gets > 500000 order by buffer_gets desc) where rownum<=30;
--执行次数多的SQL ：
select sql_text,executions from
(select sql_text,executions from v$sqlarea order by executions desc)
where rownum<10;
--读硬盘多的SQL ：
select sql_text,disk_reads from (select sql_text,disk_reads from v$sqlarea order by disk_reads desc)
where rownum<21;
 
 
 
select * 
from (
  select sql_text,sql_id,cpu_time 
  from v$sql 
  order by cpu_time desc) 
where ownum<=10 
order by rownum asc;
 
select * 
from (
  select sql_text,sql_id,cpu_time 
  from v$sqlarea 
  order by cpu_time desc) 
where rownum<=10 
order by rownum asc;
 
--这2个语句效果基本一样，一个从v$sql视图查询一个从v$sqlarea视图查询。
 
 
--列出使用频率最高的5个查询：
select sql_text,executions
from (select sql_text,executions,
   rank() over
    (order by executions desc) exec_rank
   from v$sql)
where exec_rank <=5;
 
 
 
--消耗磁盘读取最多的sql top5：
select disk_reads,sql_text
from (select sql_text,disk_reads,
   dense_rank() over
     (order by disk_reads desc) disk_reads_rank
   from v$sql)
where disk_reads_rank <=5;
 
 
 
--找出需要大量缓冲读取（逻辑读）操作的查询：
select buffer_gets,sql_text
from (select sql_text,buffer_gets,
   dense_rank() over
     (order by buffer_gets desc) buffer_gets_rank
   from v$sql)
where buffer_gets_rank<=5;
 
 
 
select count(*) from v$process --当前的连接数
select value from v$parameter where name = 'processes'; --数据库允许的最大连接数
 
*修改最大连接数:
alter system set processes = 300 scope = spfile
shutdown immediate
startup
 
 
 
查看连接消耗情况
默认情况下，oracle记录了机器名（hostname），而没有记录ip。所以，只能知道每个机器消耗了多少连接（弊端：如果hostname重名时，视作一台机器）。
 
查询：客户端设备标识、客户端程序、oracle用户名、消耗的连接数量
select  b.MACHINE, b.PROGRAM, b.USERNAME, count(*) from v$process a, v$session b 
where a.ADDR = b.PADDR and  b.USERNAME is not null   
group by  b.MACHINE, b.PROGRAM, b.USERNAME 
order by count(*) desc 
 
 
 
 
 
 
oracle 表被锁解决
1.下面的语句用来查询哪些对象被锁：
 
 
select object_name,machine,s.sid,s.serial# 
from v$locked_object l,dba_objects o ,v$session s
where l.object_id　=　o.object_id and l.session_id=s.sid;
 
2.下面的语句用来杀死一个进程：
alter system kill session '24,111'; (其中24,111分别是上面查询出的sid,serial#)
 
【注】以上两步，可以通过Oracle的管理控制台来执行。