Oracle 调整以降低使用的 BUFFER
我正在尝试优化一个 SQL，下面有解释计划。
我实际上陷入了解释计划中的这一特定行
45 NESTED LOOPS Cost: 782 Bytes: 293,832 Cardinality: 212
，如果您注意到字节数为 293K，并且由于它是嵌套循环，则最终 SQL 使用的缓冲区数为数百万。 并且它被数据库配置文件阻止。
对于如何减少 ROW 45 以上的字节数有什么一般建议吗？
第 45 行注释（来自 TOAD）：
对于步骤 42 检索到的每一行，执行步骤 44 中的操作来查找匹配的行
非常感谢
SELECT STATEMENT HINT: ALL_ROWSCost: 800 Bytes: 7,844 Cardinality: 1
67 SORT ORDER BY Cost: 800 Bytes: 7,844 Cardinality: 1
66 VIEW VIEW SYS.VM_NWVW_2 Cost: 800 Bytes: 7,844 Cardinality: 1
65 HASH UNIQUE Cost: 800 Bytes: 1,672 Cardinality: 1
64 NESTED LOOPS OUTER Cost: 799 Bytes: 1,672 Cardinality: 1
61 NESTED LOOPS OUTER Cost: 795 Bytes: 1,627 Cardinality: 1
58 NESTED LOOPS OUTER Cost: 793 Bytes: 1,599 Cardinality: 1
55 NESTED LOOPS OUTER Cost: 791 Bytes: 1,571 Cardinality: 1
52 NESTED LOOPS OUTER Cost: 790 Bytes: 1,537 Cardinality: 1
49 NESTED LOOPS OUTER Cost: 788 Bytes: 1,491 Cardinality: 1
46 HASH JOIN Cost: 786 Bytes: 1,442 Cardinality: 1
2 TABLE ACCESS BY INDEX ROWID TABLE SIEBEL.S_POS_SKILL Cost: 4 Bytes: 56 Cardinality: 1
1 INDEX RANGE SCAN INDEX (UNIQUE) SIEBEL.S_POS_SKILL_U2 Cost: 3 Cardinality: 1
45 NESTED LOOPS Cost: 782 Bytes: 293,832 Cardinality: 212
42 NESTED LOOPS OUTER Cost: 722 Bytes: 4,056 Cardinality: 3
39 NESTED LOOPS OUTER Cost: 719 Bytes: 3,909 Cardinality: 3
36 HASH JOIN Cost: 716 Bytes: 3,795 Cardinality: 3
33 NESTED LOOPS Cost: 714 Bytes: 2,318 Cardinality: 2
30 NESTED LOOPS OUTER Cost: 710 Bytes: 1,662 Cardinality: 2
27 NESTED LOOPS OUTER Cost: 706 Bytes: 1,524 Cardinality: 2
24 NESTED LOOPS Cost: 704 Bytes: 1,316 Cardinality: 2
21 NESTED LOOPS Cost: 698 Bytes: 1,124 Cardinality: 2
18 NESTED LOOPS Cost: 696 Bytes: 984 Cardinality: 2
15 NESTED LOOPS Cost: 694 Bytes: 892 Cardinality: 2
12 NESTED LOOPS Cost: 692 Bytes: 660 Cardinality: 2
9 NESTED LOOPS Cost: 687 Bytes: 70 Cardinality: 1
6 NESTED LOOPS Cost: 5 Bytes: 49 Cardinality: 1
4 TABLE ACCESS BY INDEX ROWID TABLE SIEBEL.S_POSTN Cost: 4 Bytes: 37 Cardinality: 1
3 INDEX RANGE SCAN INDEX (UNIQUE) SIEBEL.S_POSTN_U1 Cost: 3 Cardinality: 1
5 INDEX UNIQUE SCAN INDEX (UNIQUE) SIEBEL.S_PARTY_P1 Cost: 1 Bytes: 12 Cardinality: 1
8 TABLE ACCESS BY INDEX ROWID TABLE SIEBEL.S_OPTY_POSTN Cost: 682 Bytes: 21 Cardinality: 1
7 INDEX RANGE SCAN INDEX SIEBEL.S_OPTY_POSTN_M1 Cost: 6 Cardinality: 821
11 TABLE ACCESS BY INDEX ROWID TABLE SIEBEL.S_REVN Cost: 5 Bytes: 260 Cardinality: 1
10 INDEX RANGE SCAN INDEX SIEBEL.S_REVN_F3 Cost: 2 Cardinality: 6
14 TABLE ACCESS BY INDEX ROWID TABLE SIEBEL.S_PROD_LN Cost: 1 Bytes: 116 Cardinality: 1
13 INDEX UNIQUE SCAN INDEX (UNIQUE) SIEBEL.S_PROD_LN_P1 Cost: 0 Cardinality: 1
17 TABLE ACCESS BY INDEX ROWID TABLE SIEBEL.S_USER Cost: 1 Bytes: 46 Cardinality: 1
16 INDEX UNIQUE SCAN INDEX (UNIQUE) SIEBEL.S_USER_U2 Cost: 0 Cardinality: 1
20 TABLE ACCESS BY INDEX ROWID TABLE SIEBEL.S_POSTN Cost: 1 Bytes: 70 Cardinality: 1
19 INDEX UNIQUE SCAN INDEX (UNIQUE) SIEBEL.S_POSTN_U2 Cost: 0 Cardinality: 1
23 TABLE ACCESS BY INDEX ROWID TABLE SIEBEL.S_REVN_X Cost: 3 Bytes: 96 Cardinality: 1
22 INDEX RANGE SCAN INDEX (UNIQUE) SIEBEL.S_REVN_X_U1 Cost: 2 Cardinality: 1
26 TABLE ACCESS BY INDEX ROWID TABLE SIEBEL.S_PROD_INT Cost: 2 Bytes: 104 Cardinality: 1
25 INDEX UNIQUE SCAN INDEX (UNIQUE) SIEBEL.S_PROD_INT_P1 Cost: 1 Cardinality: 1
29 TABLE ACCESS BY INDEX ROWID TABLE SIEBEL.S_PROD_INT Cost: 2 Bytes: 69 Cardinality: 1
28 INDEX UNIQUE SCAN INDEX (UNIQUE) SIEBEL.S_PROD_INT_P1 Cost: 1 Cardinality: 1
32 TABLE ACCESS BY GLOBAL INDEX ROWID TABLE SIEBEL.S_OPTY Cost: 2 Bytes: 328 Cardinality: 1 Partition #: 46 Partition access computed by row location
31 INDEX UNIQUE SCAN INDEX (UNIQUE) SIEBEL.S_OPTY_P1 Cost: 1 Cardinality: 1
35 TABLE ACCESS BY INDEX ROWID TABLE SIEBEL.S_SALES_METHOD Cost: 1 Bytes: 318 Cardinality: 3
34 INDEX RANGE SCAN INDEX (UNIQUE) SIEBEL.S_SALES_METHOD_U1 Cost: 2 Cardinality: 1
38 TABLE ACCESS BY INDEX ROWID TABLE SIEBEL.S_STG Cost: 1 Bytes: 38 Cardinality: 1
37 INDEX UNIQUE SCAN INDEX (UNIQUE) SIEBEL.S_STG_P1 Cost: 0 Cardinality: 1
41 TABLE ACCESS BY INDEX ROWID TABLE SIEBEL.S_POSTN Cost: 1 Bytes: 49 Cardinality: 1
40 INDEX UNIQUE SCAN INDEX (UNIQUE) SIEBEL.S_POSTN_U2 Cost: 0 Cardinality: 1
44 TABLE ACCESS BY INDEX ROWID TABLE SIEBEL.S_POS_SKILL_IT Cost: 20 Bytes: 2,618 Cardinality: 77
43 INDEX RANGE SCAN INDEX SIEBEL.S_POS_SKILL_IT_F2 Cost: 2 Cardinality: 20
48 TABLE ACCESS BY INDEX ROWID TABLE SIEBEL.S_ORG_EXT Cost: 2 Bytes: 49 Cardinality: 1
47 INDEX UNIQUE SCAN INDEX (UNIQUE) SIEBEL.S_ORG_EXT_P1 Cost: 1 Cardinality: 1
51 TABLE ACCESS BY INDEX ROWID TABLE SIEBEL.S_ORG_EXT Cost: 2 Bytes: 46 Cardinality: 1
50 INDEX UNIQUE SCAN INDEX (UNIQUE) SIEBEL.S_ORG_EXT_U3 Cost: 1 Cardinality: 1
54 TABLE ACCESS BY INDEX ROWID TABLE SIEBEL.S_BU Cost: 1 Bytes: 34 Cardinality: 1
53 INDEX UNIQUE SCAN INDEX (UNIQUE) SIEBEL.S_BU_P1 Cost: 0 Cardinality: 1
57 TABLE ACCESS BY INDEX ROWID TABLE SIEBEL.S_ORG_EXT Cost: 2 Bytes: 28 Cardinality: 1
56 INDEX UNIQUE SCAN INDEX (UNIQUE) SIEBEL.S_ORG_EXT_U3 Cost: 1 Cardinality: 1
60 TABLE ACCESS BY INDEX ROWID TABLE SIEBEL.S_ORG_EXT Cost: 2 Bytes: 28 Cardinality: 1
59 INDEX UNIQUE SCAN INDEX (UNIQUE) SIEBEL.S_ORG_EXT_U3 Cost: 1 Cardinality: 1
63 TABLE ACCESS BY INDEX ROWID TABLE SIEBEL.S_OPTY_POSTN Cost: 4 Bytes: 450 Cardinality: 10
62 INDEX RANGE SCAN INDEX (UNIQUE) SIEBEL.S_OPTY_POSTN_U1 Cost: 3 Cardinality: 1
I am trying to optimize one SQL that having below explain plan.
I am actually stuck at this particular line from the explain plan
45 NESTED LOOPS Cost: 782 Bytes: 293,832 Cardinality: 212
If you notice the number of Bytes is 293K, and due to it is nested loops the number of buffer used by final SQL is millions.
And it is blocked by db profile.
Any general suggestions how to reduce the number of bytes of ROW 45 above?
Note on row 45 (from TOAD):
For each row retrieved by step 42, the operation in step 44 was performed to find a matching row
Thanks a lot
SELECT STATEMENT HINT: ALL_ROWSCost: 800 Bytes: 7,844 Cardinality: 1
67 SORT ORDER BY Cost: 800 Bytes: 7,844 Cardinality: 1
66 VIEW VIEW SYS.VM_NWVW_2 Cost: 800 Bytes: 7,844 Cardinality: 1
65 HASH UNIQUE Cost: 800 Bytes: 1,672 Cardinality: 1
64 NESTED LOOPS OUTER Cost: 799 Bytes: 1,672 Cardinality: 1
61 NESTED LOOPS OUTER Cost: 795 Bytes: 1,627 Cardinality: 1
58 NESTED LOOPS OUTER Cost: 793 Bytes: 1,599 Cardinality: 1
55 NESTED LOOPS OUTER Cost: 791 Bytes: 1,571 Cardinality: 1
52 NESTED LOOPS OUTER Cost: 790 Bytes: 1,537 Cardinality: 1
49 NESTED LOOPS OUTER Cost: 788 Bytes: 1,491 Cardinality: 1
46 HASH JOIN Cost: 786 Bytes: 1,442 Cardinality: 1
2 TABLE ACCESS BY INDEX ROWID TABLE SIEBEL.S_POS_SKILL Cost: 4 Bytes: 56 Cardinality: 1
1 INDEX RANGE SCAN INDEX (UNIQUE) SIEBEL.S_POS_SKILL_U2 Cost: 3 Cardinality: 1
45 NESTED LOOPS Cost: 782 Bytes: 293,832 Cardinality: 212
42 NESTED LOOPS OUTER Cost: 722 Bytes: 4,056 Cardinality: 3
39 NESTED LOOPS OUTER Cost: 719 Bytes: 3,909 Cardinality: 3
36 HASH JOIN Cost: 716 Bytes: 3,795 Cardinality: 3
33 NESTED LOOPS Cost: 714 Bytes: 2,318 Cardinality: 2
30 NESTED LOOPS OUTER Cost: 710 Bytes: 1,662 Cardinality: 2
27 NESTED LOOPS OUTER Cost: 706 Bytes: 1,524 Cardinality: 2
24 NESTED LOOPS Cost: 704 Bytes: 1,316 Cardinality: 2
21 NESTED LOOPS Cost: 698 Bytes: 1,124 Cardinality: 2
18 NESTED LOOPS Cost: 696 Bytes: 984 Cardinality: 2
15 NESTED LOOPS Cost: 694 Bytes: 892 Cardinality: 2
12 NESTED LOOPS Cost: 692 Bytes: 660 Cardinality: 2
9 NESTED LOOPS Cost: 687 Bytes: 70 Cardinality: 1
6 NESTED LOOPS Cost: 5 Bytes: 49 Cardinality: 1
4 TABLE ACCESS BY INDEX ROWID TABLE SIEBEL.S_POSTN Cost: 4 Bytes: 37 Cardinality: 1
3 INDEX RANGE SCAN INDEX (UNIQUE) SIEBEL.S_POSTN_U1 Cost: 3 Cardinality: 1
5 INDEX UNIQUE SCAN INDEX (UNIQUE) SIEBEL.S_PARTY_P1 Cost: 1 Bytes: 12 Cardinality: 1
8 TABLE ACCESS BY INDEX ROWID TABLE SIEBEL.S_OPTY_POSTN Cost: 682 Bytes: 21 Cardinality: 1
7 INDEX RANGE SCAN INDEX SIEBEL.S_OPTY_POSTN_M1 Cost: 6 Cardinality: 821
11 TABLE ACCESS BY INDEX ROWID TABLE SIEBEL.S_REVN Cost: 5 Bytes: 260 Cardinality: 1
10 INDEX RANGE SCAN INDEX SIEBEL.S_REVN_F3 Cost: 2 Cardinality: 6
14 TABLE ACCESS BY INDEX ROWID TABLE SIEBEL.S_PROD_LN Cost: 1 Bytes: 116 Cardinality: 1
13 INDEX UNIQUE SCAN INDEX (UNIQUE) SIEBEL.S_PROD_LN_P1 Cost: 0 Cardinality: 1
17 TABLE ACCESS BY INDEX ROWID TABLE SIEBEL.S_USER Cost: 1 Bytes: 46 Cardinality: 1
16 INDEX UNIQUE SCAN INDEX (UNIQUE) SIEBEL.S_USER_U2 Cost: 0 Cardinality: 1
20 TABLE ACCESS BY INDEX ROWID TABLE SIEBEL.S_POSTN Cost: 1 Bytes: 70 Cardinality: 1
19 INDEX UNIQUE SCAN INDEX (UNIQUE) SIEBEL.S_POSTN_U2 Cost: 0 Cardinality: 1
23 TABLE ACCESS BY INDEX ROWID TABLE SIEBEL.S_REVN_X Cost: 3 Bytes: 96 Cardinality: 1
22 INDEX RANGE SCAN INDEX (UNIQUE) SIEBEL.S_REVN_X_U1 Cost: 2 Cardinality: 1
26 TABLE ACCESS BY INDEX ROWID TABLE SIEBEL.S_PROD_INT Cost: 2 Bytes: 104 Cardinality: 1
25 INDEX UNIQUE SCAN INDEX (UNIQUE) SIEBEL.S_PROD_INT_P1 Cost: 1 Cardinality: 1
29 TABLE ACCESS BY INDEX ROWID TABLE SIEBEL.S_PROD_INT Cost: 2 Bytes: 69 Cardinality: 1
28 INDEX UNIQUE SCAN INDEX (UNIQUE) SIEBEL.S_PROD_INT_P1 Cost: 1 Cardinality: 1
32 TABLE ACCESS BY GLOBAL INDEX ROWID TABLE SIEBEL.S_OPTY Cost: 2 Bytes: 328 Cardinality: 1 Partition #: 46 Partition access computed by row location
31 INDEX UNIQUE SCAN INDEX (UNIQUE) SIEBEL.S_OPTY_P1 Cost: 1 Cardinality: 1
35 TABLE ACCESS BY INDEX ROWID TABLE SIEBEL.S_SALES_METHOD Cost: 1 Bytes: 318 Cardinality: 3
34 INDEX RANGE SCAN INDEX (UNIQUE) SIEBEL.S_SALES_METHOD_U1 Cost: 2 Cardinality: 1
38 TABLE ACCESS BY INDEX ROWID TABLE SIEBEL.S_STG Cost: 1 Bytes: 38 Cardinality: 1
37 INDEX UNIQUE SCAN INDEX (UNIQUE) SIEBEL.S_STG_P1 Cost: 0 Cardinality: 1
41 TABLE ACCESS BY INDEX ROWID TABLE SIEBEL.S_POSTN Cost: 1 Bytes: 49 Cardinality: 1
40 INDEX UNIQUE SCAN INDEX (UNIQUE) SIEBEL.S_POSTN_U2 Cost: 0 Cardinality: 1
44 TABLE ACCESS BY INDEX ROWID TABLE SIEBEL.S_POS_SKILL_IT Cost: 20 Bytes: 2,618 Cardinality: 77
43 INDEX RANGE SCAN INDEX SIEBEL.S_POS_SKILL_IT_F2 Cost: 2 Cardinality: 20
48 TABLE ACCESS BY INDEX ROWID TABLE SIEBEL.S_ORG_EXT Cost: 2 Bytes: 49 Cardinality: 1
47 INDEX UNIQUE SCAN INDEX (UNIQUE) SIEBEL.S_ORG_EXT_P1 Cost: 1 Cardinality: 1
51 TABLE ACCESS BY INDEX ROWID TABLE SIEBEL.S_ORG_EXT Cost: 2 Bytes: 46 Cardinality: 1
50 INDEX UNIQUE SCAN INDEX (UNIQUE) SIEBEL.S_ORG_EXT_U3 Cost: 1 Cardinality: 1
54 TABLE ACCESS BY INDEX ROWID TABLE SIEBEL.S_BU Cost: 1 Bytes: 34 Cardinality: 1
53 INDEX UNIQUE SCAN INDEX (UNIQUE) SIEBEL.S_BU_P1 Cost: 0 Cardinality: 1
57 TABLE ACCESS BY INDEX ROWID TABLE SIEBEL.S_ORG_EXT Cost: 2 Bytes: 28 Cardinality: 1
56 INDEX UNIQUE SCAN INDEX (UNIQUE) SIEBEL.S_ORG_EXT_U3 Cost: 1 Cardinality: 1
60 TABLE ACCESS BY INDEX ROWID TABLE SIEBEL.S_ORG_EXT Cost: 2 Bytes: 28 Cardinality: 1
59 INDEX UNIQUE SCAN INDEX (UNIQUE) SIEBEL.S_ORG_EXT_U3 Cost: 1 Cardinality: 1
63 TABLE ACCESS BY INDEX ROWID TABLE SIEBEL.S_OPTY_POSTN Cost: 4 Bytes: 450 Cardinality: 10
62 INDEX RANGE SCAN INDEX (UNIQUE) SIEBEL.S_OPTY_POSTN_U1 Cost: 3 Cardinality: 1
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
真正的问题是以下几行：
8 TABLE ACCESS BY INDEX ROWID TABLE SIEBEL.S_OPTY_POSTN Cost: 682 Bytes: 21 Cardinality: 1 7 INDEX RANGE SCAN INDEX SIEBEL.S_OPTY_POSTN_M1 Cost: 6 Cardinality: 821
它负责大部分成本，假设表/索引统计信息是最新的。如果没有实际的 sql 语句，问题是为什么它更昂贵？
潜在问题：
The real problem are the following lines:
8 TABLE ACCESS BY INDEX ROWID TABLE SIEBEL.S_OPTY_POSTN Cost: 682 Bytes: 21 Cardinality: 1 7 INDEX RANGE SCAN INDEX SIEBEL.S_OPTY_POSTN_M1 Cost: 6 Cardinality: 821
It is responsible for most of the costs, assuming the table/index statistics are up-to-date. Without the actual sql statement the question is why is it more expensive?
Potential problems: