oracle查看执行最慢与查询次数最多的sql语句

　　在ORACLE数据库应用调优中，一个SQL的执行次数/频率也是常常需要关注的，因为某个SQL执行太频繁，要么是由于应用设计有缺陷，需要在业务逻辑上做出优化处理，要么是业务特殊性所导致。如果执行频繁的SQL，往往容易遭遇一些并发性的问题。下面来查看ORACLE数据库某个SQL的执行频率/次数完整的示例代码。

一、查询执行最慢的sql

SELECT 
    *
FROM 
    (   SELECT 
            sa.SQL_TEXT,
            sa.SQL_FULLTEXT,
            sa.EXECUTIONS "执行次数",
            ROUND(sa.ELAPSED_TIME / 1000000, 2) "总执行时间",
            ROUND(sa.ELAPSED_TIME / 1000000 / sa.EXECUTIONS, 2) "平均执行时间",
            sa.COMMAND_TYPE,
            sa.PARSING_USER_ID "用户ID",
            u.username "用户名",
            sa.HASH_VALUE
        FROM 
            v$sqlarea sa
        LEFT JOIN 
            all_users u
        ON 
            sa.PARSING_USER_ID = u.user_id
        WHERE 
            sa.EXECUTIONS > 0
        ORDER BY 
            (sa.ELAPSED_TIME / sa.EXECUTIONS) DESC)
WHERE 
    rownum <= 50;

 

二、查询次数最多的 sql

SELECT 
    *
FROM 
    (   SELECT 
            s.SQL_TEXT,
            s.EXECUTIONS "执行次数",
            s.PARSING_USER_ID "用户名",
            RANK() over(ORDER BY EXECUTIONS DESC) EXEC_RANK
        FROM 
            v$sql s
        LEFT JOIN 
            all_users u
        ON 
            u.USER_ID = s.PARSING_USER_ID) t
WHERE 
    exec_rank <= 100;

 

三、Oracle查询SQL语句执行的耗时

SELECT 
    a.sql_text SQL语句,
    b.etime 执行耗时,
    c.user_id 用户ID,
    c.SAMPLE_TIME 执行时间,
    c.INSTANCE_NUMBER 实例数,
    u.username 用户名, 
    a.sql_id SQL编号
FROM 
    dba_hist_sqltext a,
    (   SELECT 
            sql_id, 
            ELAPSED_TIME_DELTA / 1000000 AS etime
        FROM 
            dba_hist_sqlstat
        WHERE 
            ELAPSED_TIME_DELTA / 1000000 >= 1) b,
    dba_hist_active_sess_history               c,
    dba_users                                  u
WHERE 
    a.sql_id = b.sql_id
AND u.username = 'SYNC_PLUS_1_20190109'
AND c.user_id = u.user_id
AND b.sql_id = c.sql_id
    -- and a.sql_text like '%insert into GK_ZWVCH_HSC_NEW select %'
ORDER BY 
    SAMPLE_TIME DESC,
    b.etime DESC;

 

四、定位系统里面哪些SQL脚本存在TABLE ACCESS FULL行为

select * from v$sql_plan v where v.operation = 'TABLE ACCESS' and v.OPTIONS = 'FULL' and v.OBJECT_OWNER='SYNC_PLUS_1_20190109';

select s.SQL_TEXT from v$sqlarea s where s.SQL_ID = '4dpd97jh2gzsd' and s.HASH_VALUE = '1613233933' and s.PLAN_HASH_VALUE = '3592287464';
或者
select s.SQL_TEXT from v$sqlarea s where s.ADDRESS = '00000000A65D2318';

Oracle SQL执行缓慢的原因以及解决方案

　　对Oracle SQL执行缓慢的原因的分析，如果Oracle数据库中的某张表的相关数据已是2亿多时，同时此表也创建了相关的4个独立的相关索引。由于业务方面的需要，每天需分两次向此表中插入300万条记录。由于数据量大，每次插入耗时3个小时以上，严重影响效率。
　　因此，修改了系统的算法，将此表中只存储当天新增记录。将此表truncate后，第二天执行对此表的update操作时，非常耗时。表中有2亿多条数据的时候，此Oracle sql语句耗时59秒；表中有300万条数据的时候，此Oracle sql语句耗时几个小时。
　　咨询DBA后，得出结论，需重建索引。重建后，6秒完成此操作。但第三天问题依然出现。DBA正在查找原因。难道每次truncate表，都需要重建索引？对于这个问题，DBA也没有给出合理的解释，推测主要原因是Oracle复杂的查询优化算法。

最终，DBA给出的解决方案：

truncate table ....
drop index .....
insert data .....
create index ...

analyze table table_name compute statistics;

重新生成统计数据，调整后，整个操作耗时非常少。

 

PS==>本文来自：https://blog.csdn.net/weixin_42473904/article/details/116315044

 

如有帮助希望点个推荐；如果没帮助到或者内容有错误，可以下面留言，谢谢！