DB：10205
OS: AIX 5
症状：
SELECT
JUSTIN_PROJECT.PROJECT_NM,
Sum(LARGE_HOUSE.ACT_EFF_$)
FROM
JUSTIN_PROJECT,
LARGE_HOUSE,
(
SELECT DISTINCT EXTRACT_DT ,is_completed,IS_LATEST_EXTRACT_DT FROM ADMINISTRIOR
)ADMINISTRIOR,
HOUSE_TIME
WHERE
( ADMINISTRIOR.EXTRACT_DT=HOUSE_TIME.CALENDER_DT)
AND( LARGE_HOUSE.PROJECT_SK=JUSTIN_PROJECT.PROJECT_SK)
AND( HOUSE_TIME.CALENDER_DT=LARGE_HOUSE.AS_OF_DT)
AND
(
JUSTIN_PROJECT.PROJECT_NMIN( 'ABC')
AND
( ADMINISTRIOR.IS_LATEST_EXTRACT_DT=1)
)
GROUP BY
JUSTIN_PROJECT.PROJECT_NM
客户抱怨以上这条sql在pre-prod运行很快，但是在prod上则巨慢；
登陆数据库，首先查看执行计划，
--prod
--------------------------------------------------------------------------+-----------------------------------+-------------------------+---------------+
| Id| Operation
| Name
| Rows| Bytes | Cost| Time|TQ|IN-OUT|PQ Distrib | Pstart| Pstop |
--------------------------------------------------------------------------+-----------------------------------+-------------------------+---------------+
| 0 | SELECT STATEMENT
|
| | |5988 | |
