oracle 中sql优化的几种方法

king nginx 2023-02-27 514浏览 0

最基本最简单的方式是减少访问数据库的次数。oracle在内部执行了许多工作，比如解析SQL语句, 估算索引的利用率, 读数据块等等，都将大量耗费oracle数据库的运行。
选择最有效率的表名顺序，也将明显提升oracle的性能。 oracle解析器是按照从右到左的顺序处理FROM子句中的表名。
在执行SELECT子句时尽量避免使用 “*”，因为oracle在解析的过程中，会将“*” 依次转换成列名, 这是通过查询数据字典完成的, 这将耗费更长的时间。
可以好好利用DECODE函数，该函数可以避免重复扫描相同记录，或者重复连接相同的表。
可以在SQL*Plus和SQL*Forms以及Pro*C中重新设置ARRAYSIZE参数。如此可以明显增加每次数据库访问时的检索数据量。这里的建议值为200。
尽量用TRUNCATE语句替代DELETE语句。当执行TRUNCATE命令时, 回滚段不会存放任何可被恢复的信息，所有数据不能被恢复。因此很少资源被调用，整个执行时间就会很短。
在使用oracle时，尽量多使用COMMIT命令。该命令可以明显释放运行资源，因此程序的性能得到明显提高。
可以通过用索引提高效率。oracle使用了树形的数据结构。通过合理的索引，数据查询时明显快于全表扫描，因此可以显著提高检索数据的效率。

根据执行计划进行优化的一般步骤

将瓶颈sql块单独取出，查看其执行计划，

首先检查其中使用了全表扫描的对象，判断其是否合适。引发错误使用的情形通常是：

1）  没有建立合适的索引列导致全表扫描

2）  非函数索引列使用了函数引发全表扫描

3）   对象统计信息过旧或未收集导致全表扫描、

4）  对于位图索引，直方图信息的缺失有时也会导致错误的全表扫描

然后检查其中无用的表，确认无用的表可以直接去掉减少访问步骤减小系统开销。

接着检查其中是否有重复访问的表，查看是否可以减少访问次数一般可以通过使用with或者建立中间表来优化。

优化访问路径，数据库优化器也有不那么聪明的时候，有时候它生成的访问路径可能并不是最优的，可尝试使用hint来改变访问路径进行优化

常用hint：

/*+full( )*/表明对表选择全局扫描的方法.

/*+use_nl( )*/在多表关联查询中，指定使用nest loops方式进行多表关联。

/*+use_hast( )*/在多表关联查询中，指定使用hash join方式进行多表关联。

/*+index( )*/ 使用指定表的指定索引

/*+ append */ 以直接加载的方式将数据加载入库

/*+leading( )*/在多表关联查询中，指定哪个表作为驱动表，即告诉优化器首先要访问哪个表上的数据。

/*+ parallel() */ 在sql中指定执行的并行度，这个值将会覆盖自身的并行度

看Description的基数（执行结果返回的数据的行数）和自己预估的基数是否大致一直，不一致则考虑其它影响效率的情况，如统计信息比较旧等。