Oracle SQL调优系列之定位生产性能问题方法

ゝ一世哀愁。 2021-09-09 01:28 253阅读 0赞

## Oracle SQL调优系列之定位生产性能问题方法 ##

### 1、AWR整体分析 ###

场景：最近遇到紧急生产问题，因为数据库锁表导致业务功能不能正常使用，对于这种紧急问题，首先要安稳心态，然后合理分析问题，可以先从整体出发，拿下[Oracle AWR报告][Oracle AWR]，进行整体分析，需要找出是因为cpu问题，还是具体哪里的程序导致的

### 2、JVM命令进行监控 ###

从整体不能定位到问题，还是需要配合JVM的调优命令进行排查问题，监控是否出现oom的情况？

// 监控进程信息
    [www@localhost ~]$ top
    // 查找具体的线程情况
    [www@localhost ~]$ top -H -p PID
    // 查看进程id
    [www@localhost ~]$ jps -l
    //根据jps拿到的PID获取堆信息，然后使用gcviewer等等工具进行分析
    [www@localhost ~]$ jmap -dump:format=b,file=heap.hprof PID
    // 直接查看程序堆信息
    [www@localhost ~]$ jmap -heap PID
    // 查看栈信息，看看是否有程序死锁的情况
    [www@localhost ~]$ jstack PID

gc监控工具：MAT、gcviewer，也可以通过在线网站进行分析https://gceasy.io/

### 3、拿到锁表sql ###

ok，程序排查没问题，从sql方面进行排查

*  查看是否有锁表

SELECT object_name, machine, s.sid, s.serial#
    FROM gv$locked_object l, dba_objects o, gv$session s
    WHERE l.object_id　= o.object_id
    AND l.session_id = s.sid;

*  释放数据表锁

// 释放SESSION SQL:
    alter system kill session 'sid, serial#';
    ALTER system kill session '23, 1647';

*  查看具体的锁表sql

select l.session_id sid, 
           s.serial#, 
           l.locked_mode, 
           l.oracle_username, 
           s.user#, 
           l.os_user_name, 
           s.machine, 
           s.terminal, 
           a.sql_text, 
           a.action 
      from v$sqlarea a, v$session s, v$locked_object l 
    where l.session_id = s.sid 
       and s.prev_sql_addr = a.address 
    order by sid, s.serial#;

### 4、修改数据库连接数 ###

ps：当然锁表也有可能是连接数不够

*  查看当前的数据库连接数

select count(*) from v$process ;

*  查看数据库允许的最大连接数

select value from v$parameter where name ='processes';

*  查看当前的session连接数

select count(*) from v$session

*  查看当前并发连接数

select count(*) from v$session where status='ACTIVE';

*  修改数据最大连接数

alter system set processes = 500 scope = spfile;

*  重启关闭数据库

--关闭数据库
    shutdown immediate; 
    --重启数据库
    startup;

### 5、定位慢sql ###

ok，锁表问题如果可以定位到，也要顺便排查一下哪些慢SQL，在拖系统性能

*  先查询哪些用户在使用

select osuser, a.username, cpu_time/executions/1000000||'s', b.sql_text, machine
    from v$session a, v$sqlarea b
    where a.sql_address =b.address
    order by cpu_time/executions desc;

拿出慢sql：

SELECT SQL_TEXT,
           SQL_FULLTEXT,
           ELAPSED_TIME,
           DISK_READS,
           BUFFER_GETS,
           EXECUTIONS,
           Round(ELAPSED_TIME / EXECUTIONS ,2),
           ROUND(DISK_READS / EXECUTIONS, 2),
           ROUND(BUFFER_GETS / EXECUTIONS , 2),
           ROUND((BUFFER_GETS - DISK_READS) / BUFFER_GETS, 2) 
      FROM V$SQLAREA 
     WHERE EXECUTIONS > 0
       AND BUFFER_GETS > 0
       AND (BUFFER_GETS - DISK_READS) / BUFFER_GETS < 0.8
       ORDER BY Round(ELAPSED_TIME / EXECUTIONS ,2) desc;

> 然后解释一下这些意义：  
> Round(ELAPSED\_TIME / EXECUTIONS ,2)：求每个游标执行SQL需要的时间  
> ROUND(DISK\_READS / EXECUTIONS, 2)：求每个游标执行SQL需要读磁盘的次数  
> ROUND(BUFFER\_GETS / EXECUTIONS , 2)：求每个游标执行SQL需要读内存的次数  
> ROUND((BUFFER\_GETS - DISK\_READS) / BUFFER\_GETS, 2) ：SQL命中率

然后和同事找到一个问题，发现一个业务逻辑里主键的生成是用数字加上事务控制生成的，在这种情况表就经常出现被锁表的情况

结合druid的监控，然后在阿里druid框架的官网找到如下的wiki[https://github.com/alibaba/druid/wiki/%E8%BF%9E%E6%8E%A5%E6%B3%84%E6%BC%8F%E7%9B%91%E6%B5%8B][https_github.com_alibaba_druid_wiki_E8_BF_9E_E6_8E_A5_E6_B3_84_E6_BC_8F_E7_9B_91_E6_B5_8B]

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTQ0MjczOTE_size_16_color_FFFFFF_t_70]  
发现公司性能这个监控被开起来了，所以一个是因为程序问题，加上框架的使用不当，导致的。ok，性能问题是一个很花时间的问题，本博客只进行一些简单的分享，仅供参考

[Oracle AWR]: https://smilenicky.blog.csdn.net/article/details/89414432
[https_github.com_alibaba_druid_wiki_E8_BF_9E_E6_8E_A5_E6_B3_84_E6_BC_8F_E7_9B_91_E6_B5_8B]: https://github.com/alibaba/druid/wiki/%E8%BF%9E%E6%8E%A5%E6%B3%84%E6%BC%8F%E7%9B%91%E6%B5%8B
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTQ0MjczOTE_size_16_color_FFFFFF_t_70]: /images/20210813/6acf560c82ab4eb28f909598fe0d7632.png