• Oracle 中定位重要(消耗资源多)的SQL


    1. 查看消耗内存最多的sql(v$sqlarea)

    1 select b.username ,
    2        a.buffer_gets , --所有子游标运行这条语句导致的读内存次数
    3        a.executions,   --所有子游标的执行这条语句次数
    4        a.buffer_gets/decode(a.executions,0,1,a.executions),--这条语句执行一次读取内存次数
    5        a.sql_text SQL
    6 from v$sqlarea a,dba_users b
    7 where a.parsing_user_id = b.user_id and a.buffer_gets >10000
    8 order by buffer_gets desc;

    2.查看消耗磁盘多的sql(v$sqlarea)

    1 select b.username ,
    2        a.disk_reads ,  --所有子游标运行这条语句导致的读磁盘次数
    3        a.executions,   --所有子游标的执行这条语句次数
    4        a.disk_reads/decode(a.executions,0,1,a.executions),----这条语句执行一次读取磁盘次数
    5        a.sql_text SQL
    6 from v$sqlarea a,dba_users b
    7 where a.parsing_user_id = b.user_id and a.DISK_READS >10000
    8 order by disk_reads desc;

    3.查看执行次数多的SQL(v$sqlarea)

    1 select sql_text, executions 
    2 from v$sqlarea 
    3 where rownum<81
    4 order by executions desc

    4.查看排序多的SQL(v$sqlarea)

    1 select sql_text, sorts 
    2 from v$sqlarea 
    3 order by sorts desc
    4 where rownum<21;

    5.分析的次数太多,执行的次数太少,要用绑变量的方法来写sql(v$sqlarea)

    1 select substr(sql_text,1,80) "sql",count(*),sum(executions) "totexecs"
    2 from v$sqlarea
    3 where executions<5  --sql_text 执行次数小于5
    4 group by substr(sql_text,1,80)
    5 having count(*)>30  --sql_text 分析次数大于30
    6 order by 2;

    6.前5位用户I/O等待最高的SQL语句 (v$sqlarea)

    1 select sql_text, sql_id, elapsed_time, cpu_time, user_io_wait_time 
    2 from sys.v$sqlarea
    3 where rownum < 6 
    4 order by 5 desc

    7.查看当前用户&username执行的SQL(v$sqltext_with_newlines,v$session)

    1 select sql_text
    2 from v$sqltext_with_newlines
    3 where(hash_value, address) in
    4      (select sql_hash_value, sql_address
    5       from v$session
    6       where username='&username')
    7 order by address, piece;
  • 相关阅读:
    elasticsearch-head插件基本使用
    Windows包管理工具-Chocolatey
    php-fpm常用操作
    nginx之日志处理
    进程管理工具之supervisor(完整版)
    Elasticsearch之常见问题
    支付宝支付功能接入(PC)
    UnityWebRequest 高级API常用的操作
    UnityWebRequest
    logback中使用日期做为文件目录
  • 原文地址:https://www.cnblogs.com/polestar/p/2946101.html
Copyright © 2020-2023  润新知