最近在分析一个财务系统(Oracle EBS)的SQL执行计划时，发现一个有意思的事情。SQL是这样的：
SELECT int.ROWID,
...
FROM GL_INTERFACE INT,
gl_code_combinations flex_cc,
gl_code_combinations ccid_cc
WHERE int.set_of_books_id = 1001
AND int.status != 'PROCESSED'
AND (int.user_je_source_name, GROUP_ID) IN ( ('精算', 119850))
AND flex_cc.SEGMENT1(+) = int.SEGMENT1
AND flex_cc.SEGMENT2(+) = int.SEGMENT2
AND flex_cc.SEGMENT3(+) = int.SEGMENT3
AND flex_cc.SEGMENT4(+) = int.SEGMENT4
AND flex_cc.SEGMENT5(+) = int.SEGMENT5
AND flex_cc.SEGMENT6(+) = int.SEGMENT6
AND flex_cc.SEGMENT7(+) = int.SEGMENT7
AND flex_cc.SEGMENT8(+) = int.SEGMENT8
AND flex_cc.chart_of_accounts_id(+) = 50268
AND flex_cc.template_id(+) IS NULL
AND ccid_cc.code_combination_id(+) = int.code_combination_id
AND ccid_cc.chart_of_accounts_id(+) = 50268
AND ccid_cc.template_id(+) IS NULL
ORDER BY ...;
原SQL比较长，所以省略掉了一些不重要的细节。这是一个帐目导入的SQL，表GL_INTERFACE和gl_code_combinations是GL下的两张表，而SQL是由APPS调用的，在APPS下建有该两张表的VIEW。正确的执行计划应该是这样的：
----------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | ----------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 33717 | 14M| | 27731 (1)| 00:05:33 | | 1 | SORT ORDER BY | | 33717 | 14M| 16M| 27731 (1)| 00:05:33 | | 2 | NESTED LOOPS OUTER | | 33717 | 14M| | 24454 (1)| 00:04:54 | |* 3 | HASH JOIN OUTER | | 33717 | 11M| 9688K| 24453 (1)| 00:04:54 | |* 4 | TABLE ACCESS FULL | GL_INTERFACE | 33717 | 9285K| | 1039 (1)| 00:00:13 | |* 5 | TABLE ACCESS FULL | GL_CODE_COMBINATIONS | 2758K| 231M| | 9873 (2)| 00:01:59 | |* 6 | TABLE ACCESS BY INDEX ROWID| GL_CODE_COMBINATIONS | 1 | 88 | | 1 (0)| 00:00:01 | |* 7 | INDEX UNIQUE SCAN | GL_CODE_COMBINATIONS_U1 | 1 | | | 1 (0)| 00:00:01 | -----------------------------------------------------------------------------------------------------------------但是APPS跑的执行计划就是不对：
----------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| ----------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 337 | 150K| 1721 (1)| | 1 | SORT ORDER BY | | 337 | 150K| 1721 (1)| | 2 | NESTED LOOPS OUTER | | 337 | 150K| 1720 (1)| | 3 | NESTED LOOPS OUTER | | 337 | 121K| 1046 (2)| |* 4 | TABLE ACCESS FULL | GL_INTERFACE | 337 | 95034 | 1045 (2)| |* 5 | TABLE ACCESS BY INDEX ROWID| GL_CODE_COMBINATIONS | 1 | 88 | 1 (0)| |* 6 | INDEX UNIQUE SCAN | GL_CODE_COMBINATIONS_U1 | 1 | | 1 (0)| |* 7 | TABLE ACCESS BY INDEX ROWID | GL_CODE_COMBINATIONS | 1 | 88 | 2 (0)| |* 8 | INDEX RANGE SCAN | GL_CODE_COMBINATIONS_N4 | 1134 | | 2 (0)| -----------------------------------------------------------------------------------------------一番研究后，有意思的事情发生了，我用DBA帐号执行该SQL，如果表之前加上GL，即：
FROM GL.GL_INTERFACE INT, GL.gl_code_combinations flex_cc, GL.gl_code_combinations ccid_cc执行计划就是正确的，而如果表之前使用APPS，即：
FROM APPS.GL_INTERFACE INT, APPS.gl_code_combinations flex_cc, APPS.gl_code_combinations ccid_cc就使用错误的执行计划。因APPS下在这两表上的VIEW就只是简单对这两张表的引用，所以把VIEW改成了SYNONYM，然后执行计划就正确了。其中的原因还在探究中。
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/13885898/viewspace-1651373/ ，如需转载，请注明出处，否则将追究法律责任。
请登录后发表评论
登录
全部评论
