precompute_subquery hint的使用二

 与反解析操作不同的是(反解析操作被用于从分布式查询中生成远程SQL语句),这种方法仅仅拷贝SQL文本到一个递归的语句中并执行它(这能通过设置一些定制注释文本到子查询中,并跟踪其执行来进行验证,这些注释持续存在)

因此,考虑到所有这些”硬编码”的过滤条件需要被存储在子光标的内存结构中,如果T2 表包含上百万条记录怎么办?我们拥有成十上百兆空间的光标吗?

   首先,让我们增加更多的行数到T2 table里面（我特意使用一种容易区分的数字例如5555）。

SQL> delete t2;

9 rows deleted.

SQL> insert into t2 select rownum from dual connect by level <= 5555;

5555 rows created.

SQL> select a from t1 where a in (select /*+ PRECOMPUTE_SUBQUERY */b from t2);

         A

----------

         1

         2

         3

         4

         5

         6

         7

         8

         9

9 rows selected.

现在我使用一个简单的脚本名为 hash.sql，它将显示我事务中的最后一个执行的SQL的 hash value 和child_number (using v$session.prev_hash_value).

SQL> @hash

 

HASH_VALUE SQL_ID        CHILD_NUMBER

---------- ------------- ------------

4073976606 5phajvmtd7wsy            1

让我们检查一下，使用我的curheaps.sql 脚本，它将报告在一些光标的堆栈中（数据块）在，使用了哪些内存。这个脚本获取SQL hash value and child cursor number 作为参数：

SQL> @curheaps 4073976606 1

KGLNAHSH KGLHDPAR     CHILD# KGLHDADR KGLOBHD0    SIZE0    SIZE1    SIZE2    SIZE3    SIZE4    SIZE5 KGLOBHD6    SIZE6    SIZE7     STATUS

---------- -------- ---------- -------- -------- -------- -------- -------- -------- -------- -------- -------- -------- -------- ----------

4073976606 3175A618          1 2FD2D6DC 2FD945BC     3392        0        0        0        0        0 312C53F4  1412196        0          1

 

HEAP  CLASS    ALLOC_COMMENT         BYTES     CHUNKS

----- -------- ---------------- ---------- ----------

HEAP0 perm     permanent memor        2500          4

HEAP0 free     free memory             412          2

HEAP0 freeabl  kgltbtab                228          3

HEAP  CLASS    ALLOC_COMMENT         BYTES     CHUNKS

----- -------- ---------------- ---------- ----------

HEAP6 freeabl  qeeOpt: qeesCre      489048       5557

HEAP6 freeabl  optdef: qcopCre      400056       5555

HEAP6 freeabl  opn: qkexrInitO      334192       5558

HEAP6 freeabl  ub1[]: qkexrXfo       90604       5555

HEAP6 freeabl  strdef_buf : kk       90060       5556

HEAP6 free     free memory            3600          1

HEAP6 freeabl  kksol : kksnsg          920         23

HEAP6 freeabl  kctdef : qcdlgo         876          3

HEAP6 freeabl  ctxdef:kksLoadC         488          1

HEAP6 freeabl  kctdef : qcsfps         292          1

HEAP6 freeabl  qertbs:qertbIAl         228          1

[...snip...]

HEAP6 freeabl  kksoff : opitca          20          1

HEAP6 freeabl  opixfalo:froaty          16          1

HEAP6 freeabl  xplGenXpl:planL          16          1

HEAP6 freeabl  opixfalo:ctxkct          16          1

46 rows selected.

在输出文本的上方，我们看到SIZE6 = 1412196, 这显示出该子光标的HEAP6 占用了1.4MB 的内存!

从底部黑体数字部分(5555, 5556 等等)，我们看到在光标堆栈区（这些堆栈区域相当多）里面有很多内存分配，这与表T2的行数是相匹配的。这就是硬编码的过滤条件和数据存放的地方。

来自于V$SQL_PLAN的这些Predicate Information部分，现在其中罗列刻了全部的过滤条件（至多4000字节的varchar2 ）：

-------------------------------------------------------------------------------| Id  | Operation         | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers

-------------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |      |      1 |        |      9 |00:00:00.01 |       4

|*  1 |  TABLE ACCESS FULL| T1   |      1 |      9 |      9 |00:00:00.01 |       4

-------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - filter(("A"=1 OR "A"=2 OR "A"=3 OR "A"=4 OR "A"=5 OR "A"=6 OR "A"=7

              OR "A"=8 OR "A"=9 OR "A"=10 OR "A"=11 OR "A"=12 OR "A"=13 OR "A"=14 OR

              "A"=15 OR "A"=16 OR "A"=17 OR "A"=18 OR "A"=19 OR "A"=20 OR "A"=21 OR

              "A"=22 OR "A"=23 OR "A"=24 OR "A"=25 OR "A"=26 OR "A"=27 OR "A"=28 OR

              "A"=29 OR "A"=30 OR "A"=31 OR "A"=32 OR "A"=33 OR "A"=34 OR "A"=35 OR

              "A"=36 OR "A"=37 OR "A"=38 OR "A"=39 OR "A"=40 OR "A"=41 OR "A"=42 OR

              "A"=43 OR "A"=44 OR "A"=45 OR "A"=46 OR "A"=47 OR "A"=48 OR "A"=49 OR

              "A"=50 OR "A"=51 OR "A"=52 OR "A"=53 OR "A"=54 OR "A"=55 OR "A"=56 OR

              "A"=57 OR "A"=58 OR "A"=59 OR "A"=60 OR "A"=61 OR "A"=62 OR "A"=63 OR

              "A"=64 OR "A"=65 OR "A"=66 OR "A"=67 OR "A"=68 OR "A"=69 OR "A"=70 OR

              "A"=71 OR "A"=72 OR "A"=73 OR "A"=74 OR "A"=75 OR "A"=76 OR "A"=77 OR

              "A"=78 OR "A"=79 OR "A"=80 OR "A"=81 OR "A"=82 OR "A"=83 OR "A"=84 OR

              "A"=85 OR "A"=86 OR "A"=87 OR "A"=88 OR "A"=89 OR "A"=90 OR "A"=91 OR

              "A"=92 OR "A"=93 OR "A"=94 OR "A"=95 OR "A"=96 OR "A"=97 OR "A"=98 OR

              "A"=99 OR "A"=100 OR "A"=101 OR "A"=102 OR "A"=103 OR "A"=104 OR "A"=105 OR

              "A"=106 OR "A"=107 OR "A"=108 OR "A"=109 OR "A"=110 OR "A"=111 OR "A"=112

              OR "A"=113 OR "A"=114 OR "A"=115 OR "A"=116 OR "A"=117 OR "A"=118 OR

              "A"=119 OR "A"=120 OR "A"=121 OR "A"=122 OR "A"=123 OR "A"=124 OR "A"=125

              OR "A"=126 OR "A"=127 OR "A"=128 OR "A"=129 OR "A"=130 OR "A"=131 OR

              "A"=132 OR "A"=133 OR "A"=134 OR "A"=135 OR "A"=136 OR "A"=137 OR "A"=138

              [...snip..此处删减]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/7177735/viewspace-708012/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/7177735/viewspace-708012/