oracle的sql备用

最新推荐文章于 2022-02-07 01:37:02 发布

财运通

最新推荐文章于 2022-02-07 01:37:02 发布

阅读量831

点赞数

CC 4.0 BY-SA版权

分类专栏：开发技术文章标签：数据库 sql oracle select

本文链接：https://blog.youkuaiyun.com/wzjin/article/details/37652609

开发技术专栏收录该内容

29 篇文章

订阅专栏

本文提供了一系列针对 Oracle 数据库 SQL 查询性能调优的方法，包括统计数据库执行情况、查看 SQL 执行计划、检查全表扫描操作等。适用于数据库管理员及开发人员进行 SQL 语句的性能分析和优化。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

下面语句有些需要有一定的权限，或者执行时需要做针对性的修改。

1、统计数据库的执行情况：

select s.parsing_schema_name 执行账号,
       decode(sq.command_type, '3', 'select', '2', 'insert', '6', 'UPDATE', '7', 'DELETE', '26', 'lock', '50', 'explain plan',
              '189', 'merge', '170', 'call', '1', 'CREATE table', '9', 'CREATE UNIQUE INDEX', '15', 'alter table', '62',
              ' ANALYZE TABLE', '25', 'ALTER PROCEDURE', '98', 'ALTER PACKAGE', sq.command_type) sql_type,s.sql_id, s.plan_hash_value,
       round(avg(s.elapsed_time_delta / 1000 / 1000), 2) elapsed_time_delta_all,
       round(avg(s.elapsed_time_delta / s.executions_delta / 1000 / 1000), 2) elapsed_time_delta_one,
       round(avg(s.elapsed_time_total / s.executions_delta / 1000 / 1000),2) elaped_time,
       round(avg(s.cpu_time_delta / s.executions_delta / 1000 / 1000), -2) cpu_time_delta,
       round(avg(s.iowait_delta / s.executions_delta / 1000 / 1000), -2) iowait_delta,
       round(avg(s.clwait_delta / s.executions_delta / 1000 / 1000), -2) clwait_delta, sum(executions_delta) 执行次数
  from dba_hist_sqlstat s, dba_hist_sqltext sq
 where s.elapsed_time_delta > 107975155
   and s.snap_id > 438 and s.executions_delta > 0 and s.sql_id = sq.sql_id and sq.command_type not in (47, 170)
   and s.parsing_schema_name not in ('SYS')
 group by decode(sq.command_type, '3', 'select', '2', 'insert', '6', 'UPDATE', '7', 'DELETE', '26', 'lock', '50', 'explain plan',
                 '189', 'merge', '170','call', '1', 'CREATE table','9', 'CREATE UNIQUE INDEX','15', 'alter table','62',
                 ' ANALYZE TABLE', '25', 'ALTER PROCEDURE', '98', 'ALTER PACKAGE', sq.command_type),
          s.parsing_schema_name,s.plan_hash_value, s.sql_id
 order by 6 desc

2、查看sql执行计划语句：

select * from table(dbms_xplan.display_awr('63gmans2kjk6q'));-- 查看已经运行结束的sql执行计划
select * from table(dbms_xplan.display_cursor('63gmans2kjk6q'));-- 查看正在运行的sql执行计划
--说明：如果sql text很长，上面的结果不能显示完整的sql内容，可以从下面的sql查看完整的sql text

3、查看sql text：

select * from dba_hist_sqltext t where t.sql_id ='d7dufuwwxns21';-- 看已经运行结束的sql
select * from gv$sqlarea t where t.sql_id ='63gmans2kjk6q';--查看正在运行的sql

4、查看sql中超过50万行的表，执行计划中全表扫描：

    select distinct p.OBJECT_OWNER, t.SQL_ID, p.OBJECT_NAME, p.OBJECT_OWNER, a.num_rows, a.blocks
      from gv$sqltext t, gv$sql_plan p, sys.all_tables a
     where t.hash_value = p.hash_value and p.OBJECT_NAME = a.table_name  and a.num_rows > 500000 -- 小表
       and p.operation = 'TABLE ACCESS'  and p.options = 'FULL'
          -- and p.OBJECT_OWNER in (/*'SAD',*/'RCR')
       and t.SQL_TEXT like '%:%' -- : 绑定变量，只考虑程序中的sql
     order by 6 desc;

5、查看历史sql中超过50万行的表，执行计划中全表扫描

  select distinct p.OBJECT_OWNER, p.SQL_ID, p.OBJECT_NAME, p.OBJECT_OWNER, a.num_rows, a.blocks
    from dba_hist_sqltext t, dba_hist_sql_plan p, sys.all_tables a
   where t.sql_id = p.sql_id and p.OBJECT_NAME = a.table_name
     and a.num_rows > 500000 -- 小表
     and p.operation = 'TABLE ACCESS' and p.options = 'FULL'
        -- and p.OBJECT_OWNER in (/*'SAD',*/'RCR') --输入具体的schema
     and t.SQL_TEXT like '%:%' -- : 绑定变量，只考虑程序中的sql
   order by 6 desc;

6、部分sequence当前最大值接近定义的最大值，未来有风险

 select a.max_value - a.last_number, a.last_number / a.max_value, a.last_number, a.max_value, a.*  from dba_sequences a
  where a.last_number / a.max_value > 0.8   and a.increment_by > 0
    --and a.max_value > 0