oracle 为什么按两个集合进行筛选时，执行时间会显著增加，查询需要很长时间？

qf9go6mv  于 2023-03-22  发布在  Oracle
关注(0)|答案(2)|浏览(175)

**给定：**一个有600万条记录的表。我们需要收集该季度的一些统计数据。65万行落入该季度。

数据过滤参数进入过程。其中一些参数作为“数组”传输。
例如，在产品和类别字段中，只有30个唯一值（实际上是27和22）。
有一个数据类型（集合）：

create or replace type strings is table of varchar2(256);

程序：

PROCEDURE getData(
  Filter1 IN VARCHAR2, 
  Filter2 IN VARCHAR2, 
  Filter3 IN strings,
  Filter4 IN strings,
  RES OUT SYS_REFCURSOR
  ) IS
 tSQL: CLOB;
  b1 strings;
  b2 strings;
  bindNum number := 0;
  BEGIN
    tSQL := 'SELECT count(*) FROM MyTable t WHERE to_char(t.dateInsert, 'Q.YYYY') = to_char(sysdate, 'Q.YYYY') ';
    
    IF (Filter3 IS NOT NULL) THEN
      tSQl := tSQL || ' AND nvl(products, 'notProduct') IN (SELECT column_value FROM TABLE(:b1)) ';
      b1 := Filter3;
      bindNum := bindNum + 1;
    END IF;

    IF (Filter4 IS NOT NULL) THEN
      tSQl := tSQL || ' AND nvl(categories, 'notCateg') IN (SELECT column_value FROM TABLE(:b2)) ';
      b2 := Filter4;
      bindNum := bindNum + 1;
    END IF;
    
    IF (bindNum = 1) then
      OPEN res FOR tSQL USING b1;
    elsif (bindNum = 2) then
      OPEN res FOR tSQL USING b1, b2;
    ELSE
      OPEN res FOR tSQL;
    END IF;
    
  END;

如果您在没有数据的情况下执行该过程，即使用当前季度的过滤器，则请求将在2-3秒内执行。
让我们将filter 3添加到参数中，集合中的元素数为28（整个表中唯一的元素数为30）查询在2-3秒内完成
让我们删除filter 3并将filter 4添加到参数中，集合中的元素数量为28（整个表中唯一的30个元素中的元素）查询在2-3秒内完成
让我们使用传递的filter 3和filter 4执行请求，每个集合中填充28个元素，请求在3-5分钟内完成！
好吧，也许是表中的数据和统计信息，我们手动编写一个查询，并将集合中的选择更改为传入值的枚举，并将其转换为`IN

SELECT count(*) 
FROM MyTable
WHERE to_char(dateInsert, 'Q.YYYY') = to_char(sysdate, 'Q.YYYY')
AND nvl(products, 'notProduct') IN ('prod1', 'prod2', ...) 
AND nvl(categories, 'notCateg') IN ('categ1', 'categ2', ...)

请求将在2-3秒内处理完毕...

这是怎么回事

为什么按日期和按集合的过滤器之一的选择执行得很快，而按两个集合的选择执行得要长几个数量级？为什么如果在值的显式枚举上替换SELECT COLUMN_VALUE from table(collection)，这种情况不会发生？
Oracle OEM监视显示CPU消耗量很高。

  • 我用我的头脑理解问题在于表面上的某个地方，并且显然与查询内部的集合工作有关，但我无法理解......似乎有两个集合，对于对象发生某种数据锁定，或者它以某种方式开始为每行多次重新读取集合中的值...... *

执行计划

20  | Id  | Operation                                    | Name                  | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |  OMem |  1Mem | Used-Mem |
21  --------------------------------------------------------------------------------------------------------------------------------------------------
22  |   0 | SELECT STATEMENT                             |                       |        |       | 12808 (100)|          |       |       |          |
23  |   1 |  HASH GROUP BY                               |                       |      2 |  3246 | 12808   (1)| 00:00:01 |   686K|   686K|          |
24  |*  2 |   HASH JOIN OUTER                            |                       |      2 |  3246 | 12807   (1)| 00:00:01 |   995K|   995K|  749K (0)|
25  |*  3 |    HASH JOIN OUTER                           |                       |      2 |  2214 | 12793   (1)| 00:00:01 |  1020K|  1020K|  742K (0)|
26  |   4 |     VIEW                                     |                       |      2 |  2154 | 12786   (1)| 00:00:01 |       |       |          |
27  |   5 |      HASH GROUP BY                           |                       |      2 |  2128 | 12786   (1)| 00:00:01 |   867K|   867K|          |
28  |   6 |       VIEW                                   |                       |      2 |  2128 | 12785   (1)| 00:00:01 |       |       |          |
29  |   7 |        UNION-ALL                             |                       |        |       |            |          |       |       |          |
30  |   8 |         NESTED LOOPS                         |                       |      1 |    66 |  1113   (1)| 00:00:01 |       |       |          |
31  |   9 |          MERGE JOIN CARTESIAN                |                       |      1 |     4 |    59   (2)| 00:00:01 |       |       |          |
32  |  10 |           SORT UNIQUE                        |                       |      1 |     2 |    29   (0)| 00:00:01 |  2048 |  2048 |          |
33  |* 11 |            COLLECTION ITERATOR PICKLER FETCH |                       |      1 |     2 |    29   (0)| 00:00:01 |       |       |          |
34  |  12 |           BUFFER SORT                        |                       |      1 |     2 |    30   (4)| 00:00:01 |  2048 |  2048 |          |
35  |  13 |            SORT UNIQUE                       |                       |      1 |     2 |    29   (0)| 00:00:01 |  2048 |  2048 |          |
36  |* 14 |             COLLECTION ITERATOR PICKLER FETCH|                       |      1 |     2 |    29   (0)| 00:00:01 |       |       |          |
37  |* 15 |          TABLE ACCESS BY INDEX ROWID BATCHED | CLOSED_DEALS          |      1 |    62 |  1054   (1)| 00:00:01 |       |       |          |
38  |* 16 |           INDEX RANGE SCAN                   | IDX_DC_DEALDATEQ      |  12687 |       |    41   (0)| 00:00:01 |       |       |          |
39  |  17 |         NESTED LOOPS                         |                       |      1 |    48 | 11672   (1)| 00:00:01 |       |       |          |
40  |  18 |          MERGE JOIN CARTESIAN                |                       |      1 |     4 |    59   (2)| 00:00:01 |       |       |          |
41  |  19 |           SORT UNIQUE                        |                       |      1 |     2 |    29   (0)| 00:00:01 |  2048 |  2048 |          |
42  |* 20 |            COLLECTION ITERATOR PICKLER FETCH |                       |      1 |     2 |    29   (0)| 00:00:01 |       |       |          |
43  |  21 |           BUFFER SORT                        |                       |      1 |     2 |    30   (4)| 00:00:01 |  2048 |  2048 |          |
44  |  22 |            SORT UNIQUE                       |                       |      1 |     2 |    29   (0)| 00:00:01 |  2048 |  2048 |          |
45  |* 23 |             COLLECTION ITERATOR PICKLER FETCH|                       |      1 |     2 |    29   (0)| 00:00:01 |       |       |          |
46  |* 24 |          TABLE ACCESS BY INDEX ROWID BATCHED | DEALS                 |      3 |   132 | 11613   (1)| 00:00:01 |       |       |          |
47  |* 25 |           INDEX RANGE SCAN                   | IDX_D_CREATDATEQ      |  52206 |       |   285   (0)| 00:00:01 |       |       |          |
48  |  26 |     VIEW                                     |                       |     32 |   960 |     7  (15)| 00:00:01 |       |       |          |
49  |  27 |      UNION-ALL                               |                       |        |       |            |          |       |       |          |
50  |  28 |       TABLE ACCESS FULL                      | SPR_PRODUCTS          |     19 |   513 |     3   (0)| 00:00:01 |       |       |          |
51  |  29 |       HASH UNIQUE                            |                       |     13 |   403 |     4  (25)| 00:00:01 |  1034K|  1034K|          |
52  |  30 |        TABLE ACCESS FULL                     | SPR_PRODUCTS_DECODE   |     13 |   403 |     3   (0)| 00:00:01 |       |       |          |
53  |  31 |    VIEW                                      |                       |     48 | 24768 |    14  (22)| 00:00:01 |       |       |          |
54  |  32 |     SORT UNIQUE                              |                       |     48 |  3823 |    14  (22)| 00:00:01 |  4096 |  4096 |          |
55  |  33 |      UNION-ALL                               |                       |        |       |            |          |       |       |          |
56  |* 34 |       HASH JOIN ANTI NA                      |                       |     32 |  2848 |     6   (0)| 00:00:01 |   958K|   958K| 1294K (0)|
57  |  35 |        TABLE ACCESS FULL                     | SPR_TECHNOLOGY        |     32 |  2176 |     3   (0)| 00:00:01 |       |       |          |
58  |  36 |        TABLE ACCESS FULL                     | SPR_TECHNOLOGY_DECODE |     15 |   315 |     3   (0)| 00:00:01 |       |       |          |
59  |  37 |       TABLE ACCESS FULL                      | SPR_TECHNOLOGY_DECODE |     15 |   975 |     3   (0)| 00:00:01 |       |       |          |
60  |  38 |       FAST DUAL                              |                       |      1 |       |     2   (0)| 00:00:01 |       |       |          |
61  --------------------------------------------------------------------------------------------------------------------------------------------------
62   
63  Query Block Name / Object Alias (identified by operation id):
64  -------------------------------------------------------------
65   
66     1 - SEL$5A8733C9
67     4 - SEL$3        / VX@SEL$2
68     5 - SEL$3       
69     6 - SET$1        / GX@SEL$3
70     7 - SET$1       
71     8 - SEL$574ED37E
72    11 - SEL$574ED37E / KOKBF$0@SEL$6
73    14 - SEL$574ED37E / KOKBF$1@SEL$8
74    15 - SEL$574ED37E / CD@SEL$4
75    16 - SEL$574ED37E / CD@SEL$4
76    17 - SEL$72CD2FF1
77    20 - SEL$72CD2FF1 / KOKBF$2@SEL$11
78    23 - SEL$72CD2FF1 / KOKBF$3@SEL$13
79    24 - SEL$72CD2FF1 / CD@SEL$9
80    25 - SEL$72CD2FF1 / CD@SEL$9
81    26 - SET$3        / P@SEL$18
82    27 - SET$3       
83    28 - SEL$19       / PX@SEL$19
84    29 - SEL$20      
85    30 - SEL$20       / D@SEL$20
86    31 - SET$2        / T@SEL$2
87    32 - SET$2       
88    34 - SEL$AC77216A
89    35 - SEL$AC77216A / T@SEL$14
90    36 - SEL$AC77216A / SPR_TECHNOLOGY_DECODE@SEL$15
91    37 - SEL$16       / TD@SEL$16
92    38 - SEL$17       / DUAL@SEL$17
93   
94  Outline Data
95  -------------
96   
97    /*+
98        BEGIN_OUTLINE_DATA
99        IGNORE_OPTIM_EMBEDDED_HINTS
100       OPTIMIZER_FEATURES_ENABLE('19.1.0')
101       DB_VERSION('19.1.0')
102       ALL_ROWS
103       OUTLINE_LEAF(@"SEL$574ED37E")
104       UNNEST(@"SEL$8A3193DA")
105       UNNEST(@"SEL$ABDE6DFF")
106       OUTLINE_LEAF(@"SEL$72CD2FF1")
107       UNNEST(@"SEL$61262C81")
108       UNNEST(@"SEL$285A8194")
109       OUTLINE_LEAF(@"SET$1")
110       OUTLINE_LEAF(@"SEL$3")
111       OUTLINE_LEAF(@"SEL$AC77216A")
112       UNNEST(@"SEL$15")
113       OUTLINE_LEAF(@"SEL$16")
114       OUTLINE_LEAF(@"SEL$17")
115       OUTLINE_LEAF(@"SET$2")
116       OUTLINE_LEAF(@"SEL$19")
117       OUTLINE_LEAF(@"SEL$20")
118       OUTLINE_LEAF(@"SET$3")
119       OUTLINE_LEAF(@"SEL$5A8733C9")
120       MERGE(@"SEL$E409AC11" >"SEL$1")
121       OUTLINE(@"SEL$4")
122       OUTLINE(@"SEL$8A3193DA")
123       MERGE(@"SEL$8" >"SEL$7")
124       OUTLINE(@"SEL$ABDE6DFF")
125       MERGE(@"SEL$6" >"SEL$5")
126       OUTLINE(@"SEL$9")
127       OUTLINE(@"SEL$61262C81")
128       MERGE(@"SEL$13" >"SEL$12")
129       OUTLINE(@"SEL$285A8194")
130       MERGE(@"SEL$11" >"SEL$10")
131       OUTLINE(@"SEL$14")
132       OUTLINE(@"SEL$15")
133       OUTLINE(@"SEL$1")
134       OUTLINE(@"SEL$E409AC11")
135       MERGE(@"SEL$3B245A68" >"SEL$45368B57")
136       OUTLINE(@"SEL$7")
137       OUTLINE(@"SEL$8")
138       OUTLINE(@"SEL$5")
139       OUTLINE(@"SEL$6")
140       OUTLINE(@"SEL$12")
141       OUTLINE(@"SEL$13")
142       OUTLINE(@"SEL$10")
143       OUTLINE(@"SEL$11")
144       OUTLINE(@"SEL$45368B57")
145       ANSI_REARCH(@"SEL$21")
146       OUTLINE(@"SEL$3B245A68")
147       MERGE(@"SEL$1A566D0B" >"SEL$8799457D")
148       OUTLINE(@"SEL$21")
149       OUTLINE(@"SEL$8799457D")
150       ANSI_REARCH(@"SEL$DC20D792")
151       OUTLINE(@"SEL$1A566D0B")
152       ANSI_REARCH(@"SEL$2")
153       OUTLINE(@"SEL$DC20D792")
154       ANSI_REARCH(@"SEL$18")
155       OUTLINE(@"SEL$2")
156       OUTLINE(@"SEL$18")
157       NO_ACCESS(@"SEL$5A8733C9" "VX"@"SEL$2")
158       NO_ACCESS(@"SEL$5A8733C9" "P"@"SEL$18")
159       NO_ACCESS(@"SEL$5A8733C9" "T"@"SEL$2")
160       LEADING(@"SEL$5A8733C9" "VX"@"SEL$2" "P"@"SEL$18" "T"@"SEL$2")
161       USE_HASH(@"SEL$5A8733C9" "P"@"SEL$18")
162       USE_HASH(@"SEL$5A8733C9" "T"@"SEL$2")
163       USE_HASH_AGGREGATION(@"SEL$5A8733C9")
164       NO_ACCESS(@"SEL$3" "GX"@"SEL$3")
165       USE_HASH_AGGREGATION(@"SEL$3")
166       FULL(@"SEL$20" "D"@"SEL$20")
167       USE_HASH_AGGREGATION(@"SEL$20")
168       FULL(@"SEL$19" "PX"@"SEL$19")
169       FULL(@"SEL$16" "TD"@"SEL$16")
170       FULL(@"SEL$AC77216A" "T"@"SEL$14")
171       FULL(@"SEL$AC77216A" "SPR_TECHNOLOGY_DECODE"@"SEL$15")
172       LEADING(@"SEL$AC77216A" "T"@"SEL$14" "SPR_TECHNOLOGY_DECODE"@"SEL$15")
173       USE_HASH(@"SEL$AC77216A" "SPR_TECHNOLOGY_DECODE"@"SEL$15")
174       FULL(@"SEL$72CD2FF1" "KOKBF$2"@"SEL$11")
175       FULL(@"SEL$72CD2FF1" "KOKBF$3"@"SEL$13")
176       INDEX_RS_ASC(@"SEL$72CD2FF1" "CD"@"SEL$9" "IDX_D_CREATDATEQ")
177       BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$72CD2FF1" "CD"@"SEL$9")
178       LEADING(@"SEL$72CD2FF1" "KOKBF$2"@"SEL$11" "KOKBF$3"@"SEL$13" "CD"@"SEL$9")
179       USE_MERGE(@"SEL$72CD2FF1" "KOKBF$3"@"SEL$13")
180       USE_NL(@"SEL$72CD2FF1" "CD"@"SEL$9")
181       SEMI_TO_INNER(@"SEL$72CD2FF1" "KOKBF$2"@"SEL$11")
182       SEMI_TO_INNER(@"SEL$72CD2FF1" "KOKBF$3"@"SEL$13")
183       FULL(@"SEL$574ED37E" "KOKBF$0"@"SEL$6")
184       FULL(@"SEL$574ED37E" "KOKBF$1"@"SEL$8")
185       INDEX_RS_ASC(@"SEL$574ED37E" "CD"@"SEL$4" "IDX_DC_DEALDATEQ")
186       BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$574ED37E" "CD"@"SEL$4")
187       LEADING(@"SEL$574ED37E" "KOKBF$0"@"SEL$6" "KOKBF$1"@"SEL$8" "CD"@"SEL$4")
188       USE_MERGE(@"SEL$574ED37E" "KOKBF$1"@"SEL$8")
189       USE_NL(@"SEL$574ED37E" "CD"@"SEL$4")
190       SEMI_TO_INNER(@"SEL$574ED37E" "KOKBF$0"@"SEL$6")
191       SEMI_TO_INNER(@"SEL$574ED37E" "KOKBF$1"@"SEL$8")
192       END_OUTLINE_DATA
193   */
194  
195 Predicate Information (identified by operation id):
196 ---------------------------------------------------
197  
198    2 - access("T"."TKEY"="VX"."TEHNOLOGY")
199    3 - access("P"."PKEY"="VX"."PRODUCT")
200   11 - filter((VALUE(KOKBF$)<>'CreditPotential' AND VALUE(KOKBF$)<>'Restructuring' AND VALUE(KOKBF$)<>'nullProduct'))
201   14 - filter(VALUE(KOKBF$)<>'ChangeLoan')
202   15 - filter(("SROK">1 AND NVL("TEHNOLOGY",'nullTechnology')=VALUE(KOKBF$) AND NVL("PRODUCT",'nullProduct')=VALUE(KOKBF$) AND CASE  WHEN 
203               ("PRODUCT"='Oborot' AND NVL("TEHNOLOGY",'NotTechnology')='NotTechnology' AND NVL("KISTAGE",(-1))<>(-1)) THEN 0 ELSE 1 END =1 AND 
204               "AMOUNT">1 AND NVL("PRODUCT",'nullProduct')<>'Restructuring' AND NVL("PRODUCT",'nullProduct')<>'nullProduct' AND 
205               NVL("PRODUCT",'nullProduct')<>'CreditPotential' AND NVL("TEHNOLOGY",'nullTechnology')<>'ChangeLoan'))
206   16 - access("CD"."SYS_NC00074$"='1.2023')
207   20 - filter((VALUE(KOKBF$)<>'CreditPotential' AND VALUE(KOKBF$)<>'Restructuring' AND VALUE(KOKBF$)<>'nullProduct'))
208   23 - filter(VALUE(KOKBF$)<>'ChangeLoan')
209   24 - filter((NVL("TEHNOLOGY",'nullTechnology')=VALUE(KOKBF$) AND NVL("PRODUCT",'nullProduct')=VALUE(KOKBF$) AND CASE  WHEN 
210               ("PRODUCT"='Oborot' AND NVL("TEHNOLOGY",'NotTechnology')='NotTechnology' AND NVL("KISTAGE",(-1))<>(-1)) THEN 0 ELSE 1 END =1 AND "SROK">1 
211               AND "AMOUNT">1 AND NVL("PRODUCT",'nullProduct')<>'Restructuring' AND NVL("TEHNOLOGY",'nullTechnology')<>'ChangeLoan' AND 
212               NVL("PRODUCT",'nullProduct')<>'CreditPotential' AND NVL("PRODUCT",'nullProduct')<>'nullProduct'))
213   25 - access("CD"."SYS_NC00076$"='1.2023')
214   34 - access("T"."TKEY"="TOLDKEY")
215  
216 Column Projection Information (identified by operation id):
217 -----------------------------------------------------------
218  
219    1 - "VX"."ISCLIENT"[NUMBER,22], "VX"."ISCREDIT"[NUMBER,22], "VX"."DEALSOURCE"[VARCHAR2,1024], NVL("P"."PGROUP",'Нет продукта')[512], 
220        NVL("T"."TGROUP",'Нет технологии')[512], "VX"."STATUS"[VARCHAR2,1024], "VX"."ISAPPROVED"[NUMBER,22], SUM("VX"."AMSUM")[22], 
221        SUM("VX"."CNT")[22]
222    2 - (#keys=1) "VX"."ISCLIENT"[NUMBER,22], "VX"."ISCREDIT"[NUMBER,22], "VX"."DEALSOURCE"[VARCHAR2,1024], "VX"."AMSUM"[NUMBER,22], 
223        "P"."PGROUP"[VARCHAR2,512], "VX"."STATUS"[VARCHAR2,1024], "VX"."ISAPPROVED"[NUMBER,22], "VX"."CNT"[NUMBER,22], "T"."TGROUP"[VARCHAR2,512]
224    3 - (#keys=1) "VX"."ISCLIENT"[NUMBER,22], "VX"."ISCREDIT"[NUMBER,22], "VX"."DEALSOURCE"[VARCHAR2,1024], "VX"."AMSUM"[NUMBER,22], 
225        "VX"."TEHNOLOGY"[VARCHAR2,1024], "VX"."STATUS"[VARCHAR2,1024], "VX"."ISAPPROVED"[NUMBER,22], "VX"."CNT"[NUMBER,22], 
226        "P"."PGROUP"[VARCHAR2,512]
227    4 - "VX"."ISCLIENT"[NUMBER,22], "VX"."ISCREDIT"[NUMBER,22], "VX"."DEALSOURCE"[VARCHAR2,1024], "VX"."PRODUCT"[VARCHAR2,1024], 
228        "VX"."TEHNOLOGY"[VARCHAR2,1024], "VX"."STATUS"[VARCHAR2,1024], "VX"."ISAPPROVED"[NUMBER,22], "VX"."CNT"[NUMBER,22], 
229        "VX"."AMSUM"[NUMBER,22]
230    5 - "GX"."ISCLIENT"[NUMBER,22], "GX"."ISCREDIT"[NUMBER,22], "GX"."DEALSOURCE"[VARCHAR2,1024], "GX"."PRODUCT"[VARCHAR2,1024], 
231        "GX"."TEHNOLOGY"[VARCHAR2,1024], "GX"."STATUS"[VARCHAR2,1024], "GX"."ISAPPROVED"[NUMBER,22], COUNT(*)[22], SUM("GX"."AMOUNT")[22]
232    6 - "GX"."ISCLIENT"[NUMBER,22], "GX"."ISCREDIT"[NUMBER,22], "GX"."DEALSOURCE"[VARCHAR2,1024], "GX"."PRODUCT"[VARCHAR2,1024], 
233        "GX"."TEHNOLOGY"[VARCHAR2,1024], "GX"."STATUS"[VARCHAR2,1024], "GX"."ISAPPROVED"[NUMBER,22], "GX"."AMOUNT"[NUMBER,22]
234    7 - STRDEF[22], STRDEF[22], STRDEF[1024], STRDEF[1024], STRDEF[1024], STRDEF[1024], STRDEF[22], STRDEF[22]
235    8 - "CD"."DEALSOURCE"[VARCHAR2,1024], "CD"."STATUS"[VARCHAR2,1024], "PRODUCT"[VARCHAR2,1024], "TEHNOLOGY"[VARCHAR2,1024], 
236        "AMOUNT"[NUMBER,22], "CD"."ISAPPROVED"[NUMBER,22], "CD"."ISCLIENT"[NUMBER,22], "CD"."ISCREDIT"[NUMBER,22]
237    9 - VALUE(KOKBF$)[256], VALUE(KOKBF$)[256]
238   10 - (#keys=1) VALUE(KOKBF$)[256]
239   11 - VALUE(A0)[256]
240   12 - (#keys=0) VALUE(KOKBF$)[256]
241   13 - (#keys=1) VALUE(KOKBF$)[256]
242   14 - VALUE(A0)[256]
243   15 - "CD"."DEALSOURCE"[VARCHAR2,1024], "CD"."STATUS"[VARCHAR2,1024], "PRODUCT"[VARCHAR2,1024], "TEHNOLOGY"[VARCHAR2,1024], 
244        "AMOUNT"[NUMBER,22], "CD"."ISAPPROVED"[NUMBER,22], "CD"."ISCLIENT"[NUMBER,22], "CD"."ISCREDIT"[NUMBER,22]
245   16 - "CD".ROWID[ROWID,10], "CD"."SYS_NC00074$"[VARCHAR2,6]
246   17 - "CD"."DEALSOURCE"[VARCHAR2,1024], "PRODUCT"[VARCHAR2,1024], "TEHNOLOGY"[VARCHAR2,1024], "AMOUNT"[NUMBER,22], 
247        "CD"."ISAPPROVED"[NUMBER,22], "CD"."ISCLIENT"[NUMBER,22], "CD"."ISCREDIT"[NUMBER,22]
248   18 - VALUE(KOKBF$)[256], VALUE(KOKBF$)[256]
249   19 - (#keys=1) VALUE(KOKBF$)[256]
250   20 - VALUE(A0)[256]
251   21 - (#keys=0) VALUE(KOKBF$)[256]
252   22 - (#keys=1) VALUE(KOKBF$)[256]
253   23 - VALUE(A0)[256]
254   24 - "CD"."DEALSOURCE"[VARCHAR2,1024], "PRODUCT"[VARCHAR2,1024], "TEHNOLOGY"[VARCHAR2,1024], "AMOUNT"[NUMBER,22], 
255        "CD"."ISAPPROVED"[NUMBER,22], "CD"."ISCLIENT"[NUMBER,22], "CD"."ISCREDIT"[NUMBER,22]
256   25 - "CD".ROWID[ROWID,10], "CD"."SYS_NC00076$"[VARCHAR2,6]
257   26 - "P"."PKEY"[VARCHAR2,512], "P"."PGROUP"[VARCHAR2,512]
258   27 - STRDEF[512], STRDEF[512]
259   28 - "PX"."PKEY"[VARCHAR2,512], "PX"."PGROUP"[VARCHAR2,512]
260   29 - "D"."PKEY"[VARCHAR2,512], "D"."PGROUP"[VARCHAR2,512]
261   30 - (rowset=119) "D"."PKEY"[VARCHAR2,512], "D"."PGROUP"[VARCHAR2,512]
262   31 - "T"."TKEY"[VARCHAR2,512], "T"."TGROUP"[VARCHAR2,512]
263   32 - (#keys=3) STRDEF[512], STRDEF[1024], STRDEF[512]
264   33 - STRDEF[512], STRDEF[1024], STRDEF[512]
265   34 - (#keys=1) "T"."TKEY"[VARCHAR2,512], "T"."TGROUP"[VARCHAR2,512], "T"."TVALUE"[VARCHAR2,1024]
266   35 - "T"."TKEY"[VARCHAR2,512], "T"."TVALUE"[VARCHAR2,1024], "T"."TGROUP"[VARCHAR2,512]
267   36 - "TOLDKEY"[VARCHAR2,512]
268   37 - "TD"."TKEY"[VARCHAR2,512], "TD"."TVALUE"[VARCHAR2,1024], "TD"."TGROUP"[VARCHAR2,512]
269  
270 Note
271 -----
272    - Warning: basic plan statistics not available. These are only collected when:
273        * hint 'gather_plan_statistics' is used for the statement or
274        * parameter 'statistics_level' is set to 'ALL', at session or system level
275
oracle

2条答案

按热度按时间
6kkfgxo0

6kkfgxo01#

对于注解来说太长，但您的查询可以写成：

SELECT count(*) 
FROM   MyTable
WHERE  dateInsert >= TRUNC(sysdate, 'Q')
AND    dateinsert <  ADD_MONTHS(TRUNC(sysdate, 'Q'), 3)
AND    (  products   MEMBER OF filter3 
       OR ( products IS NULL AND 'notProduct' MEMBER OF filter3 )
       )
AND    (  categories MEMBER OF filter4
       OR ( categories IS NULL AND 'notCateg' MEMBER OF filter4 )
       )

如果您在to_char(t.dateInsert, 'Q.YYYY')或nvl(products, 'notProduct')上进行比较，则Oracle将不会在dateInsert、products或categories上使用索引，并且需要在您调用的确切函数上使用单独的基于函数的索引。
通过直接在列上进行比较，Oracle可能能够使用可以提高性能的索引。
当您这样做时：

AND    nvl(products, 'notProduct') IN ('prod1', 'prod2', ...)

SQL引擎可能足够聪明，可以识别'notProduct'是否在比较的右侧，如果不是，则可以将查询简化为：

AND    products IN ('prod1', 'prod2', ...)

可以在索引中查找。
如果您这样做：

AND nvl(products, 'notProduct') IN (SELECT column_value FROM TABLE(:b1))

那么SQL引擎就不能对比较结果的右边做任何假设，也不能使用products上的索引，很可能会求助于全表扫描。您需要检查EXPLAIN PLAN的查询。

赞(0）分享  回复(0）举报  2023-03-22
cygmwpex

cygmwpex2#

很难确切地说查询的执行计划出了什么问题。正如BobC指出的，这个问题可能与统计数据有关。至少可以做三件事来改进查询的优化器统计数据-“简化”quarter表达式，对集合进行采样，以及为数据关系创建扩展统计数据。
如果这些建议没有帮助，那么您需要开始收集不同查询的执行计划。

示例Schema

下面的代码创建了一个与您描述的表非常相似的表。

--drop table MyTable;

create table MyTable
(
    id         number primary key,
    dateInsert date,          -- 6 millions rows, 650K rows per quarter
    products   varchar2(100), -- 22
    categories varchar2(100)  -- 27
);

--Create 6M rows with a data skew pretty close to your tables.
--(Have to add in chunks instead of all at once to avoid "ORA-30009: Not enough memory for CONNECT BY operation".)
begin
    for i in 0 .. 9 loop
        insert into MyTable
        select
            --Create quarters with 600K rows per quarter.
            rownum + (i * 600000) id,
            --Beginning of this month
            trunc(sysdate, 'MM')
            --Plus i months.
            + (i * (365/4))
            --Plus 1/600,000th of a quarter
            + (level * 365/4 /*days per quarter*/ / 600000) dateInsert,
            'product'  || mod(level, 27) products,
            'category' || mod(level, 27) categories
        from dual
        connect by level <= 600000;
    end loop;
end;
/

commit;

begin
    dbms_stats.gather_table_stats(user, 'MYTABLE');
end;
/

季度表达式

正如MTO所指出的，您的日期表达式可能会导致一些问题。尽管您的日期表达式 * 看起来 * 简单，但转换列值会使Oracle很难估计行数。
下面的示例显示了现有表达式的60 K“行”的估计值，真实的平均值接近600 K，并且不同的季度具有不同的值。（作为经验法则，尽量避免在表达式中将日期转换为字符串。几乎总是可以使用本地日期表达式。）

explain plan for
SELECT * FROM MyTable t WHERE to_char(t.dateInsert, 'Q.YYYY') = to_char(sysdate, 'Q.YYYY');

select * from table(dbms_xplan.display);

Plan hash value: 1015944200
 
-----------------------------------------------------------------------------
| Id  | Operation         | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |         | 60000 |  2050K|  9268   (5)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| MYTABLE | 60000 |  2050K|  9268   (5)| 00:00:01 |
-----------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter(TO_CHAR(INTERNAL_FUNCTION("T"."DATEINSERT"),'Q.YYYY')=TO_C
              HAR(SYSDATE@!,'Q.YYYY'))

通过转换为MTO的表达式（该表达式更复杂，但不转换列），优化器对203 K行进行了几乎完美的估计，而实际值为203835行。

EXPLAIN PLAN FOR
SELECT count(*)
FROM   MyTable
WHERE  dateInsert >= TRUNC(sysdate, 'Q')
AND    dateinsert <  ADD_MONTHS(TRUNC(sysdate, 'Q'), 3);

select * from table(dbms_xplan.display);

Plan hash value: 3441311250
 
-------------------------------------------------------------------------------
| Id  | Operation           | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |         |     1 |     8 |  9186   (5)| 00:00:01 |
|   1 |  SORT AGGREGATE     |         |     1 |     8 |            |          |
|*  2 |   FILTER            |         |       |       |            |          |
|*  3 |    TABLE ACCESS FULL| MYTABLE |   203K|  1592K|  9186   (5)| 00:00:01 |
-------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter(ADD_MONTHS(TRUNC(SYSDATE@!,'fmq'),3)>TRUNC(SYSDATE@!,'fmq'
              ))
   3 - filter("DATEINSERT"<ADD_MONTHS(TRUNC(SYSDATE@!,'fmq'),3) AND 
              "DATEINSERT">=TRUNC(SYSDATE@!,'fmq'))

收藏

虽然收集相对简单，但收集可能是非常复杂的PL/SQL代码片段。优化程序没有简单的方法来估计收集的行数，因此它只估计8168行，如下面的解释计划所示：

create or replace type strings is table of varchar2(256);

explain plan for
select * from table(strings('a','b','c'));

select * from table(dbms_xplan.display);

Plan hash value: 1748000095
 
----------------------------------------------------------------------------------------------
| Id  | Operation                             | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |      |  8168 | 16336 |    29   (0)| 00:00:01 |
|   1 |  COLLECTION ITERATOR CONSTRUCTOR FETCH|      |  8168 | 16336 |    29   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

要解决这个问题，请使用DYNAMIC_SAMPLING提示。这是一个“好”提示，因为它为优化器提供了额外的信息。通常情况下，Oracle从每个SQL语句中的每个表中采样行以估计总行数的成本非常高。但是在这种情况下，由于语句已经运行了至少2秒，并且所涉及的表只是内存结构，额外的采样时间是值得的。有了这个提示，“行”估计是一个完美的猜测- 3。

explain plan for
select /*+ dynamic_sampling(3) */ * from table(strings('a','b','c'));

select * from table(dbms_xplan.display);

Plan hash value: 1748000095
 
----------------------------------------------------------------------------------------------
| Id  | Operation                             | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |      |     3 |     6 |    11   (0)| 00:00:01 |
|   1 |  COLLECTION ITERATOR CONSTRUCTOR FETCH|      |     3 |     6 |    11   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
 
Note
-----
   - dynamic statistics used: dynamic sampling (level=3)

数据关系

如果您的产品和类别之间存在不寻常的关系，则可能需要同时收集这两列的统计信息。
Oracle通常会独立估计每个表达式。例如，如果查询有PRODUCTS = 'P1' AND CATEGORIES = 'C1'，Oracle会估计两个基数并将它们相乘。因此，如果每个基数有10%的正确概率，Oracle会将它们相乘，并假设表达式只返回1%的行。
但如果C1和P1总是一起出现，则真实值应为10%而不是1%。要向Oracle提供此信息，请在列PRODUCTS AND CATEGORIES上创建extended statistics。

select dbms_stats.create_extended_stats(user, 'MYTABLE', '(PRODUCTS, CATEGORIES)') from dual;

begin
    dbms_stats.gather_table_stats(user, 'MYTABLE');
end;
/
赞(0）分享  回复(0）举报  2023-03-22
我来回答

相关问题

    查看更多

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新问答

    更多
    • 蜀ICP备2022004421号-2 NULL123 https://www.null123.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com