Oracle Fusion HCM薪资查询SQL性能优化求助

我使用以下SQL从Oracle Fusion HCM表获取员工薪资明细，但查询结果存在4-5分钟延迟，恳请帮助优化该查询性能：

SELECT DISTINCT papf.person_number,
            ppnf.full_name name,
            bal.balance_value,
            fi.instance_name,
            fabu.bu_name
        FROM pay_payroll_actions ppa,
            pay_all_payrolls_f pay,
            pay_payroll_rel_actions pra,
            pay_pay_relationships_dn pprd,
            per_all_people_f papf,
            per_all_assignments_m paam,
            per_person_names_f ppnf,
            fun_all_business_units_v fabu,
            pay_time_periods ptp,
            pay_person_pay_methods_f ppm,
            pay_action_classes pac,
            pay_balance_types_vl pbt,
            PAY_FLOW_INSTANCES fi,
            PAY_REQUESTS pr,
            TABLE(
                pay_balance_view_pkg.get_balance_dimensions (
                    p_balance_type_id => pbt.balance_type_id,
                    p_payroll_rel_action_id => pra.payroll_rel_action_id,
                    p_payroll_term_id => NULL,
                    p_payroll_assignment_id => NULL
                )
            ) bal,
            pay_dimension_usages_vl pdu,
            PAY_ORG_PAY_METHODS_F popf1
        WHERE ppa.action_type IN ('Q', 'R')
            AND ppa.effective_date BETWEEN to_date(
                :p_pay_period,
                'MON-YY',
                'nls_date_language=American'
            ) AND LAST_DAY(
                to_date(
                    :p_pay_period,
                    'MON-YY',
                    'nls_date_language=American'
                )
            )
            AND pay.payroll_id = ppa.payroll_id
            AND ppa.PAYROLL_ACTION_ID = pra.PAYROLL_ACTION_ID
            AND pr.PAY_REQUEST_ID = ppa.PAY_REQUEST_ID
            AND fi.FLOW_INSTANCE_ID = pr.FLOW_INSTANCE_ID
            AND pay.payroll_name = NVL(:p_payroll, pay.payroll_name)
            AND ppa.effective_date BETWEEN pay.effective_start_date AND pay.effective_end_date
            AND pra.payroll_action_id = ppa.payroll_action_id
            AND pra.retro_component_id IS NULL
            AND pra.action_status = 'C'
            AND pprd.payroll_relationship_id = pra.payroll_relationship_id
            AND ppa.effective_date BETWEEN pprd.start_date AND pprd.end_date
            AND papf.person_id = pprd.person_id
            AND ppa.effective_date BETWEEN papf.effective_start_date AND papf.effective_end_date
            AND paam.person_id = papf.person_id
            AND paam.assignment_type = 'E'
            AND paam.primary_flag = 'Y'
            AND paam.effective_latest_change = 'Y'
            AND ppa.effective_date BETWEEN paam.effective_start_date AND paam.effective_end_date
            AND ppnf.person_id = pprd.person_id
            AND ppnf.name_type = 'GLOBAL'
            AND ppa.effective_date BETWEEN ppnf.effective_start_date AND ppnf.effective_end_date
            AND fabu.bu_id = paam.business_unit_id
            AND ppa.effective_date BETWEEN fabu.date_from AND fabu.date_to
            AND (
                LEAST(:company_name) IS NULL
                OR (
                    DECODE(
                        :company_name,
                        'Company 1',
                        1,
                        0
                    ) = 1
                    AND fabu.bu_name IN (
                        'Company 2',
                        'Shared Service BU'
                    )
                )
                OR (
                    DECODE(
                        :company_name,
                        'Company 3',
                        1,
                        0
                    ) = 0
                    AND fabu.bu_name IN (:company_name)
                )
            )
            AND pay.payroll_id = ptp.payroll_id
            AND ptp.period_category IN ('E', 'C')
            AND TO_CHAR(
                ptp.start_date,
                'MON-YY',
                'NLS_DATE_LANGUAGE = american'
            ) IN (:p_pay_period)
            AND ppm.payroll_relationship_id(+) = pprd.payroll_relationship_id
            AND pac.action_type = ppa.action_type
            AND pac.classification_name = 'SEQUENCED'
            AND pbt.legislation_code = 'SA'
            AND pbt.balance_name IN ('Net Pay')
            AND pdu.database_item_suffix = '_REL_RUN'
            AND pdu.balance_dimension_id = bal.balance_dimension_id
            AND pdu.legislation_code = 'SA'
            AND bal.balance_value <> 0
            AND ppm.org_payment_method_id = popf1.org_payment_method_id(+)
            AND EXISTS (
                SELECT 1
                FROM pay_paymt_search_results_vl ppsrv,
                    pay_action_interlocks pai,
                    pay_payroll_rel_actions pre_rel_actions,
                    pay_payroll_actions pre_actions
                WHERE (
                        ppsrv.person_number = pprd.payroll_relationship_number
                        OR ppsrv.person_number || '-1' = pprd.payroll_relationship_number
                        OR ppsrv.person_number || '-2' = pprd.payroll_relationship_number
                        OR ppsrv.person_number || '-3' = pprd.payroll_relationship_number
                    )
                    AND pai.locked_action_id = pra.payroll_rel_action_id
                    AND pre_rel_actions.payroll_rel_action_id = pai.locking_action_id
                    AND pre_actions.payroll_action_id = pre_rel_actions.payroll_action_id
                    AND ppsrv.opm IN (:PAYMENT_METHOD)
                    AND ppsrv.payroll_rel_action_id = pre_rel_actions.payroll_rel_action_id
                    AND ppsrv.process_date BETWEEN ptp.start_date AND ptp.end_date
            )
        ORDER BY papf.person_number

优化建议 #

1. 替换旧式连接语法为ANSI标准JOIN #

将逗号分隔表列表的方式改为明确的INNER JOIN/LEFT JOIN语法，让连接逻辑更清晰，帮助Oracle优化器生成更高效的执行计划。示例：

FROM pay_payroll_actions ppa
INNER JOIN pay_all_payrolls_f pay ON pay.payroll_id = ppa.payroll_id AND ppa.effective_date BETWEEN pay.effective_start_date AND pay.effective_end_date
INNER JOIN pay_payroll_rel_actions pra ON pra.payroll_action_id = ppa.payroll_action_id AND pra.retro_component_id IS NULL AND pra.action_status = 'C'
-- 其他表按逻辑替换为对应JOIN类型
LEFT JOIN pay_person_pay_methods_f ppm ON ppm.payroll_relationship_id = pprd.payroll_relationship_id

2. 消除不必要的DISTINCT #

先排查DISTINCT的必要性：是否是多表连接产生了重复行？如果是，可通过调整连接条件（增加更严格的过滤）或提前聚合数据避免重复，去掉DISTINCT——该操作会触发额外排序，对性能影响较大。

3. 优化日期转换逻辑 #

多次重复调用to_date(:p_pay_period, ...)会增加计算开销，用CTE提前转换参数并存储结果：

WITH param_dates AS (
    SELECT 
        TO_DATE(:p_pay_period, 'MON-YY', 'nls_date_language=American') AS period_start,
        LAST_DAY(TO_DATE(:p_pay_period, 'MON-YY', 'nls_date_language=American')) AS period_end
    FROM DUAL
)
SELECT ...
FROM param_dates pd
JOIN pay_payroll_actions ppa ON ppa.effective_date BETWEEN pd.period_start AND pd.period_end
-- 其他日期条件统一引用pd的字段

同时，ptp表的TO_CHAR(ptp.start_date, ...) IN (:p_pay_period)会导致索引失效，改为：

AND ptp.start_date BETWEEN pd.period_start AND pd.period_end

4. 简化公司名称过滤逻辑 #

将嵌套的DECODE逻辑替换为直观的条件判断，减少函数调用开销：

AND (
    :company_name IS NULL
    OR (:company_name = 'Company 1' AND fabu.bu_name IN ('Company 2', 'Shared Service BU'))
    OR (:company_name != 'Company 3' AND fabu.bu_name = :company_name)
)

5. 优化EXISTS子查询 #

子查询中的多OR匹配可简化为字符串截取（假设payroll_relationship_number格式为人员编号-后缀）：

WHERE ppsrv.person_number = REGEXP_SUBSTR(pprd.payroll_relationship_number, '^[^-]+')

同时给子查询涉及的表添加组合索引：

• pay_paymt_search_results_vl: (person_number, payroll_rel_action_id, process_date, opm)
• pay_action_interlocks: (locked_action_id)
• pay_payroll_rel_actions: (payroll_rel_action_id)

6. 减少表值函数开销 #

pay_balance_view_pkg.get_balance_dimensions是表值函数，逐行调用会显著拖慢性能。可先过滤出需要的pbt.balance_type_id和pra.payroll_rel_action_id集合，再批量调用该函数；或检查Fusion HCM是否有现成视图可直接获取余额维度数据，避免函数调用。

7. 移除未使用的表 #

pay_person_pay_methods_f ppm和PAY_ORG_PAY_METHODS_F popf1未在SELECT或过滤条件中使用任何字段，仅做外连接，可直接移除，减少连接开销。

8. 验证索引有效性 #

确保以下核心字段有合适的组合索引（匹配查询过滤和连接顺序）：

• pay_payroll_actions: (action_type, effective_date, payroll_id, pay_request_id)
• pay_payroll_rel_actions: (payroll_action_id, payroll_relationship_id, action_status, retro_component_id)
• per_all_assignments_m: (person_id, primary_flag, assignment_type, effective_latest_change, business_unit_id)
• pay_time_periods: (payroll_id, period_category, start_date)
• pay_balance_types_vl: (legislation_code, balance_name)

9. 优化排序性能 #

若papf.person_number上有索引，可让优化器利用索引排序，避免额外排序操作。若DISTINCT和ORDER BY都基于该字段，可调整连接顺序，让优化器优先按person_number排序。

内容的提问来源于stack exchange，提问作者mu shaikh