Oracle 大表的加入需要很多时间

ASKTOM 2018-12-20

271

问题描述

嗨，团队，

Background:
我们有40-45个应用程序将SQL server作为数据库 (假名: <>)。现在，此 <> 具有一些视图，这些视图通过链接的服务器连接指向Oracle的视图 (假名: <>)。
这个 <> 架构只有视图。这里没有表或其他DB对象。
在这里，此 <> 视图正在击中某些供应商Oracle数据库的表 (假名: <>)。“我们无法更改供应商的数据库 (索引除外)”。

在我们的观点中，我们使用一些供应商表，这些表很大 (每个表中的数据300-400万)，因为它们持有每日股票价格。
由于业务逻辑，我们必须将联接放在这些巨大的表上，然后获取整个数据，以便它们可以进一步用于将联接与SQL Server数据库 (<>) 表在SQL端。

Problem#1:
最初，当我们运行视图查询时，它会被挂起。并且不回应。所以，我们做了一些改变:

Changes :
1.重新编写查询以避免某些计算
2.创建了一些CTE。
3.创建的索引
4.解锁表统计/分析以计算统计信息/收集表的统计信息。
5.放入Oracle提示 (如US_NL，INDEX，LEADING等) 来制定自适应计划。
6.实现的并行度。

即使做了所有这些事情，我们也面临着这个问题。在某些情况下 (当我们没有where子句的情况下)，视图给出初始结果 (不是所有的数据)。
但是，当我们放一些WHERE子句时，它的行为很奇怪。

Problem#2:
当我们尝试从SQL端 (使用链接服务器连接) 访问这些视图时，在Oracle端运行良好的视图在SQL端运行缓慢。

如果您可以在此方面提供帮助，将很有帮助。我被困住了。

Below is the query of the view :


WITH ttr AS (
    SELECT /*+ INLINE */
        *
    FROM
        swpr_eod.transaction
),ttd_base AS (
    SELECT /*+ INLINE */
        *
    FROM
        swpr_eod.transaction_detail
) SELECT
-- ***** DO NOT REMOVE BELOW AREA. THESE ARE ORACLE HINTS PLACED TO IMPORVE THE PERFORMANCE *****   
--
    /*+      parallel(ttr,4) parallel(tins,4) parallel(tig,4) parallel(tte,4) parallel(ttc,4) parallel(ttd,4) parallel(ttdb,4)  parallel(ttds,4)
             no_merge(ttr) no_merge(ttd_base)
             LEADING(ttr tins tig ttt tte ttc ta ttrel ttd ttdb ttds tfi tce tii tfpr tfr vsac tpi ttrs1 vctp) 
             USE_NL(tins tig ttt tte ttc ta)
             INDEX_JOIN(ttc idx_transaction_id idx_tran_cost_instrument_id)
             INDEX_JOIN(ttr pk_transaction idx_instrument_id)
             INDEX(ta idx_account_account_number)
             INDEX(tfi IDX_FIRM_INSTRUMENT_INSTRUMENT_ID)
             PARALLEL_INDEX(tii IDX_INST_IDEN_GLOBAL_ID_SCHE_CD_ACT_FLG)
             PARALLEL_INDEX(ttd idx_tran_detail_transaction_id)
             PARALLEL_INDEX(ttdb idx_tran_detail_fee_type_name)
             PARALLEL_INDEX(ttds idx_tran_detail_fee_type_name)
             INDEX_JOIN(ttr idx_tran_third_ini_bus_proc_ref idx_tran_third_ini_bus_proc_type_cd)
             INDEX(tce PK_CA_EVENT)
             PARALLEL_INDEX(tte idx_tran_ext_fee_package_profile_id)
             INDEX(tfpr idx_fee_pkg_rule_fee_rule_profile_id)
             INDEX(tfr pk_fee_rule)
             INDEX(tpi idx_party_ident_entity_id)
             PARALLEL_INDEX(ttrs1 pk_transaction)
             PARALLEL_INDEX(ttr idx_tran_fourth_ini_bus_proc_type_cd)
             PARALLEL_INDEX(ttr idx_tran_second_ini_bus_proc_ref)
         */
    ta.account_number AS account_id,
    tig.instrument_type_cd AS asset_class_cd,
    tig.instrument_long_name AS asset_nm,
    ttr.transaction_net_amt AS cash_effect_amt,
    ttrs1.fourth_ini_bus_proc_ref AS clearing_broker_fins_id,
    ttt.transaction_type_desc AS corporate_action_tp,
    tii.instrument_external_id AS cusip_id,
    ttr.transaction_dt AS entry_dt,
    ttr.transaction_remarks_text AS explanation_tx,
    ttc.base_national_cost_amt AS federal_tax_cost_amt,
    tpi.tax_identifier_text AS for_interested_party_id,
    ttr.gross_amt,
    vctp.interested_party_id AS interested_party_id,
    tte.base_fair_market_val AS market_val,
    ttr.transaction_price AS per_unit_prc,
    ttr.portfolio_id AS portfolio_num,
    tte.submitter_team_id AS posting_location_id,
    ttr.instrument_id AS property_num,
    DECODE(ttr.reversed_transaction_flg,1,ttr.transaction_dt,NULL) AS reverse_dt,
    ttrel.related_transaction_id AS reverse_tran_num,
    ttr.reversed_transaction_flg AS reversed_transaction_fl,
    ttr.reversal_flg AS reversing_transaction_fl,
    ttr.settlement_dt AS settlement_dt,
    vsac.source_account_id AS source_account_id,
    ttr.trade_dt AS trade_dt,
    ttr.transaction_id AS transaction_num,
    tfi.instrument_template_id AS unique_asset_fl,
    ttr.transaction_unit_qty AS unit_qty,
    ttt.transaction_type_desc AS action_tx,
    ttdb.transaction_amt AS broker_amt,
    ttt.transaction_type_desc AS corporate_action_tx,
    DECODE(ttr.transaction_type_cd,7,ttr.txn_description_cd,0) AS disbursement_cd,
    tfr.fee_type_profile_id AS fee_tp,
    DECODE(ttr.transaction_type_cd,6,ttr.txn_description_cd,0) AS receipt_cd,
    ttr.custodian_account_entity_id AS registration_cd_1,
    tte.fee_package_profile_id AS schedule_cd,
    ttds.transaction_amt AS sec_fee_amt,
    ttr.transaction_type_cd AS tran_subtype_cd,
    ttr.transaction_type_cd AS transaction_type_cd,
    ttr.accrued_interest_net_amt AS accrued_interest_amt,
    vsac.pay_to_account_name AS additional_nm,
    'NULL' AS broker_cd,
    ttr.last_update_dt AS last_update_dt,
    vctp.name_address_tx_2 AS name_address_tx_2,
    vctp.name_address_tx_3 AS name_address_tx_3,
    vctp.name_address_tx_4 AS name_address_tx_4,
    'NULL' AS name_address_tx_5 
--* Added Below Fields for CORP_Audit *
   ,
    tte.submitted_by_userid AS poster_initials_tx,
    tte.book_val AS book_val,
    ttr.check_number AS check_num,
    tte.ca_terms_text AS per_unit_rt,
    tce.record_dt AS record_dt,
    DECODE(ttr.transaction_type_cd,8,1,0) AS transfer_fl,
    ttr.txn_description_cd
        --ttd.component_type_cd
  from
    ttr
    INNER JOIN swpr_eod.instrument tins ON tins.instrument_id = ttr.instrument_id
    INNER JOIN swpr_eod.instrument_global tig ON tig.instrument_global_id = tins.instrument_global_id
    INNER JOIN swpr_eod.transaction_type ttt ON ttt.transaction_type_cd = ttr.transaction_type_cd
    INNER JOIN swpr_eod.transaction_ext tte ON tte.transaction_id = ttr.transaction_id
    INNER JOIN swpr_eod.transaction_cost ttc ON ttc.transaction_id = ttr.transaction_id
                                                AND ttc.instrument_id = ttr.instrument_id
    INNER JOIN swpr_eod.account ta ON ta.account_id = ttr.account_id
    LEFT OUTER JOIN swpr_eod.transaction_relationship ttrel ON ttrel.transaction_id = ttr.transaction_id
    LEFT OUTER JOIN ttd_base ttd ON ttd.transaction_id = ttr.transaction_id
    LEFT OUTER JOIN ttd_base ttdb ON ttdb.transaction_id = ttr.transaction_id
                                     AND ttdb.fee_type_name IN (
        'Broker Commission',
        'Broker Dealer Fee Type'
    )
    LEFT OUTER JOIN ttd_base ttds ON ttds.transaction_id = ttr.transaction_id
                                     AND ttds.fee_type_name IN (
        'Sec Amount'
    )
    LEFT OUTER JOIN swpr_eod.firm_instrument tfi ON tfi.instrument_id = ttr.instrument_id
    LEFT OUTER JOIN swpr_eod.ca_event tce ON tce.ca_event_id = ttr.third_ini_bus_proc_ref
                                             AND ttr.third_ini_bus_proc_type_cd = 5
    LEFT OUTER JOIN swpr_eod.instrument_identification tii ON tii.instrument_global_id = tig.instrument_global_id
                                                              AND tii.instrument_scheme_cd = 5
                                                              AND tii.active_flg = 1
    LEFT OUTER JOIN swpr_eod.fee_package_rule tfpr ON tfpr.fee_package_profile_id = tte.fee_package_profile_id
    LEFT OUTER JOIN swpr_eod.fee_rule tfr ON tfr.fee_rule_profile_id = tfpr.fee_rule_profile_id
    LEFT OUTER JOIN bmo_ft_source_acccount_cash_transfers vsac ON vsac.transaction_id = ttr.transaction_id
    LEFT OUTER JOIN swpr_eod.party_identification tpi ON tpi.entity_id = vsac.pay_to_party_id
    LEFT OUTER JOIN ttr ttrs1 ON ttrs1.transaction_id = ttr.transaction_id
                                 AND ttr.fourth_ini_bus_proc_type_cd IN (
        14,
        13
    )
   LEFT OUTER JOIN bmo_ft_cash_transfer_parties vctp ON vctp.transacation_ref_no = ttr.second_ini_bus_proc_ref ;

问候，
Prashant Srivastava
prashants7945@gmail.com

专家解答

有几件事要尝试。

1) 在事物的 * Oracle * 方面运行跟踪，以便您可以 * 确切地 * 看到从SQL Server传递的查询。为了进行讨论，可以说我们发现它很简单: “从MY_VIEW中选择 *”

2) 让我们看看优化器是否使用了良好的信息。所以在你的Oracle服务器上采取上面的查询并做:

从我的视图中选择/* 收集表统计 */ *

运行它，然后运行:

从表中选择 * (dbms_xplan.display_cursor(null，null，'ALLSTATS LAST'))

这将为您提供计划每个部分的实际vs估计分析。寻找疯狂的差异或过度的开始。

3) 看看时间在哪里丢失。最好的方法是使用SQL监控。所以也添加一个监视器提示，然后运行它。

您可以在执行过程中反复运行以下内容，以查看计划的进展情况

set pagesize 0 echo off timing off linesize 1000 
set trimspool on trim on long 2000000 longchunksize 2000000 feedback off
set termout off
select DBMS_SQLTUNE.REPORT_SQL_MONITOR(
  sql_id=>'...',
  type=>'HTML',
  report_level=>'ALL')
from dual

spool sqlmon_output.htm
/
spool off
set termout on

希望这有所帮助

oracle asktom

文章转载自ASKTOM，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

Oracle 大表的加入需要很多时间

问题描述

专家解答

评论