避免硬解析的 SQL 执行过程

来源：互联网发布：开淘宝店如何做好推广编辑：程序博客网时间：2024/05/17 16:57

SQL> select x from t where x = 4523423;   --第一次执行 已用时间:  00: 00: 00.15执行计划----------------------------------------------------------Plan hash value: 1572208108----------------------------------------------------------------------------| Id  | Operation        | Name    | Rows  | Bytes | Cost (%CPU)| Time     |----------------------------------------------------------------------------|   0 | SELECT STATEMENT |         |     1 |     5 |     3   (0)| 00:00:01 ||*  1 |  INDEX RANGE SCAN| IND_T_X |     1 |     5 |     3   (0)| 00:00:01 |----------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   1 - access("X"=4523423)统计信息----------------------------------------------------------        279  recursive calls          0  db block gets         44  consistent gets          9  physical reads          0  redo size        404  bytes sent via SQL*Net to client        385  bytes received via SQL*Net from client          2  SQL*Net roundtrips to/from client          5  sorts (memory)          0  sorts (disk)          1  rows processed

SQL> select x from t where x = 4523423;   --第二次执行 已用时间:  00: 00: 00.00执行计划----------------------------------------------------------Plan hash value: 1572208108----------------------------------------------------------------------------| Id  | Operation        | Name    | Rows  | Bytes | Cost (%CPU)| Time     |----------------------------------------------------------------------------|   0 | SELECT STATEMENT |         |     1 |     5 |     3   (0)| 00:00:01 ||*  1 |  INDEX RANGE SCAN| IND_T_X |     1 |     5 |     3   (0)| 00:00:01 |----------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   1 - access("X"=4523423)统计信息----------------------------------------------------------          0  recursive calls   --递归调用           0  db block gets          4  consistent gets   --逻辑读           0  physical reads    --物理读           0  redo size        404  bytes sent via SQL*Net to client        385  bytes received via SQL*Net from client          2  SQL*Net roundtrips to/from client          0  sorts (memory)          0  sorts (disk)          1  rows processedSQL>

执行过程：

1.首次执行SQL，从磁盘中读取指令，再次执行，由于session未断开，指令直接在PGA中获取，避免了物理读。

2.首次执行结束后，SGA共享池保存了该SQL唯一的HASH值，当再次执行时，该SQL指令的HASH值和共享池里保存的相匹配，无须进行硬解析。

3.首次执行SQL，数据只能从磁盘中获取，因此产生了物理读和递归调用。再次执行，从缓存中获取，避免了递读和物理读，性能得到提升。

常用避免硬解析的方法是绑定变量。

*****************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************

并不是执行一样的SQL就避免了硬解析。例如加上HINT语法的SQL语句:

SQL> select /*+full(t) */x from t where x = 4523423;已用时间:  00: 00: 05.14执行计划----------------------------------------------------------Plan hash value: 3050126167--------------------------------------------------------------------------------------------------------------| Id  | Operation            | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |--------------------------------------------------------------------------------------------------------------|   0 | SELECT STATEMENT     |          |     1 |     5 | -- 65  (10)| 00:00:01 |        |      |         ||   1 |  PX COORDINATOR      |          |       |       |            |          |        |      |         ||   2 |   PX SEND QC (RANDOM)| :TQ10000 |     1 |     5 |    65  (10)| 00:00:01 |  Q1,00 | P->S | QC (RAND)  ||   3 |    PX BLOCK ITERATOR |          |     1 |     5 |    65  (10)| 00:00:01 |  Q1,00 | PCWC |         ||*  4 |     TABLE ACCESS FULL| T        |     1 |     5 |    65  (10)| 00:00:01 |  Q1,00 | PCWP |         |--------------------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   4 - filter("X"=4523423)统计信息----------------------------------------------------------        278  recursive calls          3  db block gets      15535  consistent gets   -- 15237  physical reads        624  redo size        404  bytes sent via SQL*Net to client        385  bytes received via SQL*Net from client          2  SQL*Net roundtrips to/from client          6  sorts (memory)          0  sorts (disk)          1  rows processedSQL> select /*+full(t) */x from t where x = 4523423;已用时间:  00: 00: 04.89执行计划----------------------------------------------------------Plan hash value: 3050126167--------------------------------------------------------------------------------------------------------------| Id  | Operation            | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |--------------------------------------------------------------------------------------------------------------|   0 | SELECT STATEMENT     |          |     1 |     5 | -- 65  (10)| 00:00:01 |        |      |         ||   1 |  PX COORDINATOR      |          |       |       |            |          |        |      |         ||   2 |   PX SEND QC (RANDOM)| :TQ10000 |     1 |     5 |    65  (10)| 00:00:01 |  Q1,00 | P->S | QC (RAND)  ||   3 |    PX BLOCK ITERATOR |          |     1 |     5 |    65  (10)| 00:00:01 |  Q1,00 | PCWC |         ||*  4 |     TABLE ACCESS FULL| T        |     1 |     5 |    65  (10)| 00:00:01 |  Q1,00 | PCWP |         |--------------------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   4 - filter("X"=4523423)统计信息----------------------------------------------------------         12  recursive calls          0  db block gets      15499  consistent gets    --15236  physical reads          0  redo size        404  bytes sent via SQL*Net to client        385  bytes received via SQL*Net from client          2  SQL*Net roundtrips to/from client          1  sorts (memory)          0  sorts (disk)          1  rows processedSQL>

0 0

避免硬解析 的 SQL 执行过程

避免硬解析的 SQL 执行过程