Oracle sql 性能优化（三）

性能优化

【推荐】查询分页场景，建议如下分页格式，先查询过滤出一部分数据，再做下一层过滤查询

举例：

Demo

Avoided

SELECT *

FROM (SELECT A.*, ROWNUM RN

FROM (SELECT OWNER, CREATED, OBJECT_ID, OBJECT_TYPE

FROM T1

WHERE OWNER = 'SYS'

ORDER BY OBJECT_ID DESC) A)

WHERE RN > 10

and RN <= 20

Preferred

SELECT *

FROM (SELECT A.*, ROWNUM RN

FROM (SELECT OWNER, CREATED, OBJECT_ID, OBJECT_TYPE

FROM T1

WHERE OWNER = 'SYS'

ORDER BY OBJECT_ID DESC) A

WHERE ROWNUM <= 20)

WHERE RN > 10

\

2.20【参考】尽量避免 HINT 在代码中出现

说明：

通过 hint 的影响，使执行计划固化下来，有时会导致错误的结果，一般不建议使

用，特别应用场景情况下除外。

\

2.21【参考】并行度使用需谨慎

说明：

并行技术的使用需要考虑 CPU 核数，系统负载等情况，并行执行的 SQL 会对其它语

句的性能产生影响，一般常用在报表分析任务和 OLAP 系统中。

在使用 PRARLLEL 时，不要直接使用/*+ PRARLLEL */，而需要指明具体的并行度值

/*+ PRARLLEL(4) */，一般并行度取值不能太高，理论上并行度最大取值为物理 CPU

总数减 1。

举例：

Demo

Avoided

SELECT /*+ PRARLLEL */ SUBS_ID, CUST_ID,

ACCT_ID, PREFIX, ACC_NBR

FROM SUBS

WHERE ACC_NBR LIKE ‘87%’

Preferred

SELECT /*+ PRARLLEL(4) */ SUBS_ID, CUST_ID,

ACCT_ID, PREFIX, ACC_NBR

FROM SUBS

WHERE ACC_NBR LIKE ‘87%’

\

2.22【推荐】SQL 里面避免使用标量子查询，标量子查询全部使用外连接实现

说明：

举例中标量子查询将 A 表里把每条 ID 传到 B 表里执行，也就是说 A 表有多少条数

据，B 表就要执行多少次。当 A 表数据量很大时，就会存在问题。

建议修改成外连接方式，这样可以走 HASH JOIN，避免 FILTER，提升性能。

标量子查询用在如下情况较合适：

1.A 表的 count(distinct id)比较小

2.B 表的连接键，这里就是 ID 上有比较高效的选择性的索引

举例：

Demo

Avoided

SELECT A.OBJECT_ID,

(SELECT B.USERNAME

FROM T1 B

WHERE A.OWNER = B.USERNAME)

from T2 A;

Preferred

SELECT A.OBJECT_ID, B.USERNAME

FROM T2 A,

T1 B

WHERE T2.USERNAME = T1.OWNER(+);

\

最佳实践

3.1【推荐】不要将空的变量值直接与比较运算符比较

说明：

如果变量可能为空，应该使用 IS NULL 或 IS NOT NULL 或 NVL 函数来进行比较。

举例：

Demo

Avoided

IF vUserName == NULL THEN

vUserName = ‘UNKOWN’;

END IF;

Preferred

IF vUserName IS NULL THEN

vUserName = ‘UNKOWN’;

END IF;3.2【强制】多表关联和复杂的 SQL，需要进行执行计划分析

说明：

SQL 语句的执行效率对于系统非常重要，一个效率很低的 SQL 会降低系统吞吐

量，严重时可导致系统宕机。所以，对于 SQL 语句的执行计划分析显得相当重要，下图是使

用 PL/SQL Developer 分析 SQL 执行计划的示例。这里仅给出示例，不给出具体如何分析的方

法，相关技能请自学或参加培训。

【注】一旦发现对大数据量表的全表扫描 SQL，务必与相关同事进行讨论和分析。

Note：

对于有关联实例表的查询，原则上都需要进行执行计划分析。同时需要结合业务场景分析，

比如某些 job 是需要处理所有记录的，需要全表扫描，这种情况除外。