sql性能优化精要分析

最新推荐文章于 2024-07-24 04:12:21 发布

baomw

最新推荐文章于 2024-07-24 04:12:21 发布

阅读量278

点赞数 1

分类专栏： oracle 文章标签： sql调优 oracle sql优化性能优化解决

本文链接：https://blog.youkuaiyun.com/baomw/article/details/84403106

版权

oracle 专栏收录该内容

6 篇文章

订阅专栏

本文分享了作者两年开发过程中总结的Oracle SQL调优经验，包括全表缓存、动态变量使用、执行计划分析、索引优化及DML操作优化等，为数据库设计与SQL性能提升提供实用指导。

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

sql优化应该是任何一个开发，运维人员耳熟能详的问题，为什么有的人开发的系统测试时各种没问题，一到生产实际使用，数据量一大各种跑不动，崩盘挂死，很多时候和我们不合理的数据库设计以及sql有关，这里我将我这两年开发过程中总结的基于oracle相关sql调优经验总结一下，希望对大家有所启发。

1，对于数据量少的表，如果经常全表扫描，可以对全表缓存

alter table t1 cache;
	alter table t1 buffer_pool keep;--放在keep池中。

2，当有大量相同语句是只是参数不同–建议使用动态变量

SET AUTOTRACE OFF ---------------- 不生成AUTOTRACE 报告，这是缺省模式
SET AUTOTRACE ON EXPLAIN ------ AUTOTRACE只显示优化器执行路径报告
SET AUTOTRACE ON STATISTICS -- 只显示执行统计信息
SET AUTOTRACE ON ----------------- 包含执行计划和统计信息
SET AUTOTRACE TRACEONLY EXPLAIN------ 同set autotrace on，但是不显示查询输出

3.关联语句中表的顺序（有兴趣的可以百度下oracle执行计划过程中表之间的关联关系，百度上一坨说明，写的比我好）
在RBO优化器模式下，表应按结果记录数从大到小的顺序从左到右来排列，因为表间连接时，最右边的表会被放到嵌套循环的最外层。最外层的循环次数越少，效率越高。

3，执行计划

a）统计信息中逻辑读取平均每行小于5 时一般是不需要优化的也就是consistent gets/查询总行数<5
b）执行计划中预测行 rows 和统计信息的 rows precessed 或者查询结果行数不一样的时候说明可以优化。
c）predicate information 中 如果出现类型转换 to_number 等肯定需要优化 acess 表示使用索引

1）查看执行计划
a）执行计划是从缩进最多的开始的，如果同时又多个缩进一样，按照从上往下进行（缩进一样是有可能并行处理）
b）对于语句硬解析后，执行计划也会保存在缓存中。
Statistics
---------------------------------------------------------------------------------------------------------------------------------------
　　　　　0　recursive calls（归调用次数）
　　　　　8　db block gets(从磁盘上读取的块数,即通过update/delete/select for update读的次数)
　　　　　6　consistent gets(从内存里读取的块数,即通过不带for update的select 读的次数)
　　　　　0　physical reads(物理读—从磁盘读到数据块数量,一般来说是’consistent gets’ + ‘db block gets’)
　　　　　0　redo size (重做数——执行SQL的过程中，产生的重做日志的大小)
　　　　551　bytes sent via SQLNet to client
　　　　430　bytes received via SQLNet from client
　　　　　2　SQL*Net roundtrips to/from client
　　　　　2　sorts (memory) (在内存中发生的排序)
　　　　　0　sorts (disk) (在硬盘中发生的排序)
　　　　　6　rows processed
　　　　　
1）Sort unique(sort的目的是取出所有的唯一值)（在distinct操作或者某个下一步需要unique值的操作）
2）Sort aggregate(sort的目的是为了聚合)//聚合函数如count（）、sum（）等就是聚合操作(只是在计算整个表，所有行的总计时使用不分组)
select sum(a.p_state_id) from t_policy_state a ?
3）Sort group by(sort的目的是为了分组)
select a.state,sum(a.p_state_id) from t_policy_state a group by a.state;–这种合计使用的是group by 不是Sort aggregate
4）Sort join(sort的目的是为了merge)
5）Sort order by(sort的目的是为了有序输出)
全表扫描需要扫描高水位线以下的所有块。
2）表连连接方式
a）sort --排序，很消耗资源（除非有特殊要求，尽量减少排序操作，可以先查出少量结果集后做排序）

order by clauses
group by
sort merge join

–-这三个会产生排序运算
b）filter --过滤，如not in、min函数等容易产生
c）索引
索引范围扫描：like ‘%abc’ 不会走索引
索引跳跃扫描：索引跳跃式扫描只有前导列 a 是低基列时才会引发跳跃式索引扫描，也就是第一列索引的不通知DISTINCT少的时候才会跳跃扫描（测试的数据是有37个不同值的时候就不会跳跃，低于37的才会跳跃）
索引快速全扫描：比较依赖非空约束

比如： select deptno from t3 ；–deptno上有索引如果不是not null 约束，不走索引，因为怕有空数据，有 not null约束就会走索引
所以一般索引建立原则是尽量建在有非空约束的字段上

4，数据库硬件解析
1）共享池是缓存数据地方，SQL PLSQL 都会缓存在这里，即时其他用户执行相同的语句，也会从共享池中查看缓存，可以共享给所有用户。系统参数也在共享池中，共享池使用最近最少使用方法，只保留最频繁使用以及最近使用的。
2）查看高速缓存里面的sql语句

   select * from emp;
	select * from EMP;
	select sql_text from v$sql where upper(sql_text) like'%EMP%';    --会缓存两句，认为是不一样de
	select * from emp where deptno=:no;      --使用动态变量会只解析一次（常用于存储过程中）

3）查询转换
SELECT * FROM EMP,(SELECT DEPTNO FROM DEPT) D WHERE EMP.DEPTNO=D.DEPTNO;
会变查询转换 select * from emp ,(select deptno from dept) d where e.deptno=d.deptno;
强制不进行转换select * from emp where deptno in (select/+NO_UNNEST/ deptno from dept)
–执行计划采取filter 不会使用nested loop 速度慢

是否使用视图合并
SELECT * FROM EMP,(SELECT DEPTNO FROM DEPT) D WHERE EMP.DEPTNO=D.DEPTNO;
--系统默认是走视图合并的 nested loops就是视图合并
SELECT * FROM EMP,(SELECT /*+NO_MERGE*/DEPTNO FROM DEPT) D WHERE EMP.DEPTNO=D.DEPTNO;
--强制不走视图合并 会慢很多 view 就是没有走视图合并

5，DML大数据时一些处理措施
1）DML操作都是可以使用错误日志。
a）执行创建错误表 execute dbms_errlog.create_error_log('t1','t1_error');
b）插入式带上错误提示

  insert into t1 values('a','b','c') log errors into t1_error reject limit unlimited;有错误不回滚，继续插入，
     insert into t12 select * from dba_objects log errors into t12_error reject limit unlimited;--即使有很多错误也会继续插入，错误保存在错误表中。
    rollback；--如果这时手动回滚，只会回滚基表数据，并不影响错误表，已经插入错误表的数据仍然存在，是自治事务。
    insert into t1 values('a','b','c') log errors into t1_error reject limit 2;两个错误就回滚，回滚本条数据；

2）update 大量数据时，不如重新创建新表（最重要的缺点是虽然速度上来了，但是必须让原表保持离线或者不被修改，要不然新创建的表是被修改后的表）。

  SQL> update t2 set a=a+1;
 2200006 rows updated
 Executed in 449.455 seconds
 
 --重建速度会提高，但是必须保证对象重建期间不被修改


  SQL> create table t2_temp as select a+1 as a,b,c from t1 where 1=1;
     Table created
     Executed in 151.461 seconds--速度快很多。
     SQL> alter table t2 rename to t2_old;
    SQL> alter table t2_temp rename to t2;

或者使用直接路径插入
create table t2_temp
insert /+append/ into t2_temp select * from emp;

3）delete 大量数据时和update一样
a）创建一张临时表
b）将不需要的记录放到临时表中
c）重建相关对象（索引，约束，授权以及触发器）
d）表重命名
e）当然如果你足够自信，直接truncate或者drop重建表也是可以的

以上就是oracle数据优化一些经验，不过具体的DML/DQL操作速度快慢还取决于磁盘IO瓶颈，表结构设计是否合理，关联关系是否合理，针对于大批量数据业务表是否做了分区管理，以及db中是否有大量触发器，job等增加db负担，所以上述只是sql调优的相关指导，具体的实际效果还是需要具体情况具体分析的。

好了关于sql调优相关内容就介绍到这里了，大家有什么问题或者疑惑也欢迎给我留言！

写的不合理的地方望大家指正，谢谢