Skip to Main Content
  • Questions

  • Join of huge tables taking to much of time

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Prashant.

Asked: December 20, 2018 - 10:30 pm UTC

Last updated: December 24, 2018 - 1:54 am UTC

Version: 12c

Viewed 10K+ times! This question is

You Asked


Hi Team,

Background:
We have 40-45 applications who have SQL server as a database (fake name : <<SQLAppDB>>). Now, this <<SQLAppDB>> is having some views which point to Oracle's Views via linked server connection (fake name : <<OraViewsDB>>).
This <<OraViewsDB>> schema has only views. There are no tables or other DB Objects in this.
Here, this <<OraViewsDB>> views are hitting the tables of some Vendor Oracle database (fake name : <<VendorDB>>). "We can't make changes to Vendor's DB (except the indexes)".

In our views we are using some Vendor tables which are huge (3-4 million of data in each table) because they hold daily stock price.
Due to business logic we have to put joins on these huge tables and then get the whole data so that they can further be used to put join with SQL Server database (<<SQLAppDB>>) tables at SQL side.

Problem#1:
Initially, When we ran the views queries it use to get hang. And do not respond. So, we made some changes :

Changes :
1. Re-write the query to avoid some computations.
2. Created some CTE's.
3. Created Indexes.
4. unlocked the table stats/ Analyzed to Compute statistics/ Gather the stats of tables.
5. Putted Oracle hints (such as US_NL , INDEX, LEADING etc.) to make adaptive plan.
6. Implemented Degree of Parallelism.

Even after doing all of these things we are facing the issue. In some cases (case when we do not have where clause), view gives initial result (not all the data).
But, when we put some WHERE clause then it acts weird.

Problem#2:
When we try to access these views from SQL side (using Linked server connection) then the view which are running fine in Oracle side are running way to slow in SQL side.

It would be helpful if you can please help on this. I am stuck.

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 ;



Regards,
Prashant Srivastava
prashants7945@gmail.com

and Connor said...

A couple of things to try.

1) Run a trace on the *Oracle* side of things so you can see *exactly* what query is being passed from SQL Server. For the sake of this discussion, lets say we find out it was a simple: "select * from MY_VIEW"

2) Let's see if the optimizer is using good info. So on your Oracle server take the query above and do:

select /*+ gather_table_statistics */ * from MY_VIEW

Run that, and then run:

select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'))

That will give you a Actual vs Estimate analysis for each part of the plan. Look for wild discrepancies or excessive Starts.

3) See where in the the time is being lost. The best way of doing that is with SQL Monitoring. So add a MONITOR hint as well, and then run it.

Something like the below you can run repeatedly during execution to see how the plan is progressing

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


Hope this helps

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Followup

Prashant Srivastava, January 09, 2019 - 9:19 pm UTC

Thanks for the reply. I really appreciate your support. I further did some analysis but, even after doing that, select (of view) is taking time at Oracle side itself and give 200 records in 4 minutes.

Further Problem Analysis:
In the query there certain LEFT OUTER JOINS on tables such as BMO_FT_CASH_TRANSFER_PARTIES (this is another view) , ttd_base ( this is a CTE for transaction_detail table having 3098637 data). And, is used 3 times in the SELECT.

<b>Table,View Names USED in SELECT   Type No. Of records</b>
ACCOUNT         Table 16970
BMO_FT_CASH_TRANSFER_PARTIES   View 23648
BMO_FT_SOURCE_ACCCOUNT_CASH_TRANSFERS View 472
CA_EVENT        Table 11707
currency_distribution     Table 52316
FEE_PACKAGE_RULE      Table 329
FEE_RULE        Table 331
FIRM_INSTRUMENT       Table 55362
INSTRUMENT        Table 211729
INSTRUMENT_GLOBAL      Table 208990
INSTRUMENT_IDENTIFICATION    Table 686705
PARTY_IDENTIFICATION     Table 34015
TRANSACTION        Table 3029723
TRANSACTION        Table 3029723
TRANSACTION_COST      Table 3054173
TRANSACTION_DETAIL      Table 3098637
TRANSACTION_EXT       Table 3029723
TRANSACTION_RELATIONSHIP    Table 8018
TRANSACTION_TYPE      Table 123


Query:
WITH ttr AS (SELECT /*+ INLINE */  * FROM swpr_eod.transaction),
tte AS (SELECT /*+ INLINE */ * FROM swpr_eod.transaction_ext),
ttc AS (SELECT /*+ INLINE */ * FROM swpr_eod.transaction_cost),
ttd_base AS (SELECT /*+ INLINE */ * FROM swpr_eod.transaction_detail) 
select /*+ 
             parallel(ttr,4)  parallel(tte,4) parallel(ttc,4) parallel(ttd,4) parallel(ttdb,4)  parallel(ttds,4)
             no_merge(ttr) no_merge(tte) no_merge(ttc) no_merge(ttd_base)
             LEADING(ttr tins tig ttt tte ttc ta ttrel tfi tce tfpr tfr tii vsac tpi ttrs1 vctp ttd ttdb ttds)
             USE_NL(tins tig ttt tte ttc ta)
             INDEX_JOIN(ttc idx_transaction_id idx_tran_cost_instrument_id)
             INDEX(ttrel IDX_TRAN_REL_TRANSACTION_ID)
             INDEX(tfi IDX_FIRM_INSTRUMENT_INSTRUMENT_ID)
             INDEX_JOIN(ttr idx_tran_third_ini_bus_proc_ref idx_tran_third_ini_bus_proc_type_cd)
             INDEX(tfpr idx_fee_pkg_rule_fee_rule_profile_id)
             INDEX(tfr pk_fee_rule)
             PARALLEL_INDEX(tii IDX_INST_IDEN_GLOBAL_ID_SCHE_CD_ACT_FLG)
             INDEX(tpi idx_party_ident_entity_id)
             PARALLEL_INDEX(ttr idx_tran_fourth_ini_bus_proc_type_cd)
             PARALLEL_INDEX(ttr idx_tran_second_ini_bus_proc_ref)
             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)
             ALL_ROWS
              */
    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,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
  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 tte ON tte.transaction_id = ttr.transaction_id
    INNER JOIN 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 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.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 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 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
    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');

Execution Plan:
| Id  | Operation                                                  | Name                                  | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |
|   0 | SELECT STATEMENT                                           |                                       |  3902K|    14G|       |  7691K  (1)| 00:05:01 |       |       |        |      |            |
|   1 |  PX COORDINATOR                                            |                                       |       |       |       |            |          |       |       |        |      |            |
|   2 |   PX SEND QC (RANDOM)                                      | :TQ10015                              |  3902K|    14G|       |  7691K  (1)| 00:05:01 |       |       |  Q1,15 | P->S | QC (RAND)  |
|   3 |    BUFFER SORT                                             |                                       |  3902K|    14G|       |            |          |       |       |  Q1,15 | PCWP |            |
|   4 |     NESTED LOOPS OUTER                                     |                                       |  3902K|    14G|       |  7691K  (1)| 00:05:01 |       |       |  Q1,15 | PCWP |            |
|*  5 |      HASH JOIN RIGHT OUTER                                 |                                       |  3902K|    14G|       |  6716K  (1)| 00:04:23 |       |       |  Q1,15 | PCWP |            |
|   6 |       PX RECEIVE                                           |                                       | 34016 |   597K|       | 33949   (1)| 00:00:02 |       |       |  Q1,15 | PCWP |            |
|   7 |        PX SEND BROADCAST                                   | :TQ10010                              | 34016 |   597K|       | 33949   (1)| 00:00:02 |       |       |  Q1,10 | S->P | BROADCAST  |
|   8 |         PX SELECTOR                                        |                                       |       |       |       |            |          |       |       |  Q1,10 | SCWC |            |
|   9 |          TABLE ACCESS BY INDEX ROWID BATCHED               | PARTY_IDENTIFICATION                  | 34016 |   597K|       | 33949   (1)| 00:00:02 |       |       |  Q1,10 | SCWC |            |
|  10 |           INDEX FULL SCAN                                  | IDX_PARTY_IDENT_ENTITY_ID             | 34016 |       |       |    74   (0)| 00:00:01 |       |       |  Q1,10 | SCWP |            |
|* 11 |       HASH JOIN RIGHT OUTER                                |                                       |  3902K|    14G|       |  6682K  (1)| 00:04:22 |       |       |  Q1,15 | PCWP |            |
|  12 |        PX RECEIVE                                          |                                       |   331 |  3310 |       |    19   (0)| 00:00:01 |       |       |  Q1,15 | PCWP |            |
|  13 |         PX SEND BROADCAST                                  | :TQ10011                              |   331 |  3310 |       |    19   (0)| 00:00:01 |       |       |  Q1,11 | S->P | BROADCAST  |
|  14 |          PX SELECTOR                                       |                                       |       |       |       |            |          |       |       |  Q1,11 | SCWC |            |
|  15 |           TABLE ACCESS BY INDEX ROWID BATCHED              | FEE_RULE                              |   331 |  3310 |       |    19   (0)| 00:00:01 |       |       |  Q1,11 | SCWC |            |
|  16 |            INDEX FULL SCAN                                 | PK_FEE_RULE                           |   331 |       |       |     1   (0)| 00:00:01 |       |       |  Q1,11 | SCWP |            |
|* 17 |        HASH JOIN RIGHT OUTER                               |                                       |  3902K|    14G|       |  6682K  (1)| 00:04:22 |       |       |  Q1,15 | PCWP |            |
|  18 |         PX RECEIVE                                         |                                       |   329 |  3948 |       |    18   (0)| 00:00:01 |       |       |  Q1,15 | PCWP |            |
|  19 |          PX SEND BROADCAST                                 | :TQ10012                              |   329 |  3948 |       |    18   (0)| 00:00:01 |       |       |  Q1,12 | S->P | BROADCAST  |
|  20 |           PX SELECTOR                                      |                                       |       |       |       |            |          |       |       |  Q1,12 | SCWC |            |
|  21 |            TABLE ACCESS BY INDEX ROWID BATCHED             | FEE_PACKAGE_RULE                      |   329 |  3948 |       |    18   (0)| 00:00:01 |       |       |  Q1,12 | SCWC |            |
|  22 |             INDEX FULL SCAN                                | IDX_FEE_PKG_RULE_FEE_RULE_PROFILE_ID  |   329 |       |       |     1   (0)| 00:00:01 |       |       |  Q1,12 | SCWP |            |
|* 23 |         HASH JOIN RIGHT OUTER                              |                                       |  3897K|    14G|       |  6682K  (1)| 00:04:22 |       |       |  Q1,15 | PCWP |            |
|  24 |          PX RECEIVE                                        |                                       |  1182 |   638K|       |   164   (1)| 00:00:01 |       |       |  Q1,15 | PCWP |            |
|  25 |           PX SEND BROADCAST                                | :TQ10013                              |  1182 |   638K|       |   164   (1)| 00:00:01 |       |       |  Q1,13 | S->P | BROADCAST  |
|  26 |            PX SELECTOR                                     |                                       |       |       |       |            |          |       |       |  Q1,13 | SCWC |            |
|  27 |             VIEW                                           | BMO_FT_SOURCE_ACCCOUNT_CASH_TRANSFERS |  1182 |   638K|       |   164   (1)| 00:00:01 |       |       |  Q1,13 | SCWC |            |
|  28 |              VIEW                                          |                                       |  1182 |   685K|       |   164   (1)| 00:00:01 |       |       |  Q1,13 | SCWC |            |
|  29 |               WINDOW SORT                                  |                                       |  1182 | 57918 |       |   164   (1)| 00:00:01 |       |       |  Q1,13 | SCWC |            |
|  30 |                NESTED LOOPS                                |                                       |  1182 | 57918 |       |   163   (0)| 00:00:01 |       |       |  Q1,13 | SCWC |            |
|  31 |                 NESTED LOOPS                               |                                       |  2098 | 57918 |       |   163   (0)| 00:00:01 |       |       |  Q1,13 | SCWC |            |
|  32 |                  TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED| TRANSACTION                           |  2098 | 50352 |       |    91   (0)| 00:00:01 | ROWID | ROWID |  Q1,13 | SCWC |            |
|* 33 |                   INDEX RANGE SCAN                         | IDX_TRANSACTION_TYPE_CD               |  2098 |       |       |     9   (0)| 00:00:01 |       |       |  Q1,13 | SCWP |            |
|* 34 |                  INDEX UNIQUE SCAN                         | PK_CURRENCY_DISTRIBUTION              |     1 |       |       |     0   (0)| 00:00:01 |       |       |  Q1,13 | SCWP |            |
|  35 |                 TABLE ACCESS BY INDEX ROWID                | CURRENCY_DISTRIBUTION                 |     1 |    25 |       |     1   (0)| 00:00:01 |       |       |  Q1,13 | SCWP |            |
|  36 |          NESTED LOOPS                                      |                                       |  3897K|    12G|       |  6681K  (1)| 00:04:22 |       |       |  Q1,15 | PCWP |            |
|* 37 |           HASH JOIN RIGHT OUTER                            |                                       |  3897K|    12G|       |  3432K  (1)| 00:02:15 |       |       |  Q1,15 | PCWP |            |
|  38 |            BUFFER SORT                                     |                                       |       |       |       |            |          |       |       |  Q1,15 | PCWC |            |
|  39 |             PX RECEIVE                                     |                                       | 41718 |    64M|       |  7904   (1)| 00:00:01 |       |       |  Q1,15 | PCWP |            |
|  40 |              PX SEND HYBRID HASH                           | :TQ10000                              | 41718 |    64M|       |  7904   (1)| 00:00:01 |       |       |        | S->P | HYBRID HASH|
|  41 |               STATISTICS COLLECTOR                         |                                       |       |       |       |            |          |       |       |        |      |            |
|  42 |                VIEW                                        | BMO_FT_CASH_TRANSFER_PARTIES          | 41718 |    64M|       |  7904   (1)| 00:00:01 |       |       |        |      |            |
|  43 |                 SORT UNIQUE                                |                                       | 41718 |    27M|    28M|  7904   (1)| 00:00:01 |       |       |        |      |            |
|  44 |                  UNION-ALL                                 |                                       |       |       |       |            |          |       |       |        |      |            |
|* 45 |                   HASH JOIN                                |                                       | 40617 |    26M|       |  1694   (1)| 00:00:01 |       |       |        |      |            |
|  46 |                    TABLE ACCESS FULL                       | PARTY_IDENTIFICATION                  | 34016 |   597K|       |   103   (0)| 00:00:01 |       |       |        |      |            |
|* 47 |                    TABLE ACCESS FULL                       | CURRENCY_DISTRIBUTION                 | 41183 |    26M|       |  1590   (1)| 00:00:01 |       |       |        |      |            |
|* 48 |                   HASH JOIN                                |                                       |  1101 |   266K|       |   261   (1)| 00:00:01 |       |       |        |      |            |
|* 49 |                    TABLE ACCESS FULL                       | CURRENCY_RECEIPT                      |  1101 |   247K|       |   157   (0)| 00:00:01 |       |       |        |      |            |
|  50 |                    TABLE ACCESS FULL                       | PARTY_IDENTIFICATION                  | 34016 |   597K|       |   103   (0)| 00:00:01 |       |       |        |      |            |
|  51 |            PX RECEIVE                                      |                                       |  3897K|  6293M|       |  3424K  (1)| 00:02:14 |       |       |  Q1,15 | PCWP |            |
|  52 |             PX SEND HYBRID HASH                            | :TQ10014                              |  3897K|  6293M|       |  3424K  (1)| 00:02:14 |       |       |  Q1,14 | P->P | HYBRID HASH|
|* 53 |              HASH JOIN RIGHT OUTER BUFFERED                |                                       |  3897K|  6293M|       |  3424K  (1)| 00:02:14 |       |       |  Q1,14 | PCWP |            |
|  54 |               PX RECEIVE                                   |                                       |  8058 |   110K|       |  1357   (0)| 00:00:01 |       |       |  Q1,14 | PCWP |            |
|  55 |                PX SEND BROADCAST                           | :TQ10001                              |  8058 |   110K|       |  1357   (0)| 00:00:01 |       |       |  Q1,01 | S->P | BROADCAST  |
|  56 |                 PX SELECTOR                                |                                       |       |       |       |            |          |       |       |  Q1,01 | SCWC |            |
|  57 |                  TABLE ACCESS BY INDEX ROWID BATCHED       | TRANSACTION_RELATIONSHIP              |  8058 |   110K|       |  1357   (0)| 00:00:01 |       |       |  Q1,01 | SCWC |            |
|  58 |                   INDEX FULL SCAN                          | IDX_TRAN_REL_TRANSACTION_ID           |  8058 |       |       |    20   (0)| 00:00:01 |       |       |  Q1,01 | SCWP |            |
|* 59 |               HASH JOIN RIGHT OUTER                        |                                       |  3894K|  6236M|       |  3423K  (1)| 00:02:14 |       |       |  Q1,14 | PCWP |            |
|  60 |                PX RECEIVE                                  |                                       | 55362 |   486K|       | 44541   (1)| 00:00:02 |       |       |  Q1,14 | PCWP |            |
|  61 |                 PX SEND BROADCAST                          | :TQ10002                              | 55362 |   486K|       | 44541   (1)| 00:00:02 |       |       |  Q1,02 | S->P | BROADCAST  |
|  62 |                  PX SELECTOR                               |                                       |       |       |       |            |          |       |       |  Q1,02 | SCWC |            |
|  63 |                   TABLE ACCESS BY INDEX ROWID BATCHED      | FIRM_INSTRUMENT                       | 55362 |   486K|       | 44541   (1)| 00:00:02 |       |       |  Q1,02 | SCWC |            |
|  64 |                    INDEX FULL SCAN                         | IDX_FIRM_INSTRUMENT_INSTRUMENT_ID     | 55362 |       |       |   126   (0)| 00:00:01 |       |       |  Q1,02 | SCWP |            |
|* 65 |                HASH JOIN RIGHT OUTER                       |                                       |  3853K|  6136M|       |  3379K  (1)| 00:02:12 |       |       |  Q1,14 | PCWP |            |
|  66 |                 PX RECEIVE                                 |                                       |  3099K|    20M|       |   519   (1)| 00:00:01 |       |       |  Q1,14 | PCWP |            |
|  67 |                  PX SEND BROADCAST                         | :TQ10003                              |  3099K|    20M|       |   519   (1)| 00:00:01 |       |       |  Q1,03 | P->P | BROADCAST  |
|  68 |                   PX BLOCK ITERATOR                        |                                       |  3099K|    20M|       |   519   (1)| 00:00:01 |       |       |  Q1,03 | PCWC |            |
|  69 |                    INDEX FAST FULL SCAN                    | IDX_TRAN_DETAIL_TRANSACTION_ID        |  3099K|    20M|       |   519   (1)| 00:00:01 |       |       |  Q1,03 | PCWP |            |
|* 70 |                 HASH JOIN RIGHT OUTER                      |                                       |  3767K|  5974M|       |  3378K  (1)| 00:02:12 |       |       |  Q1,14 | PCWP |            |
|  71 |                  PX RECEIVE                                |                                       |    84 |   924 |       |  4307   (1)| 00:00:01 |       |       |  Q1,14 | PCWP |            |
|  72 |                   PX SEND BROADCAST                        | :TQ10004                              |    84 |   924 |       |  4307   (1)| 00:00:01 |       |       |  Q1,04 | P->P | BROADCAST  |
|  73 |                    PX BLOCK ITERATOR                       |                                       |    84 |   924 |       |  4307   (1)| 00:00:01 |       |       |  Q1,04 | PCWC |            |
|* 74 |                     TABLE ACCESS FULL                      | TRANSACTION_DETAIL                    |    84 |   924 |       |  4307   (1)| 00:00:01 |       |       |  Q1,04 | PCWP |            |
|* 75 |                  HASH JOIN RIGHT OUTER                     |                                       |  3767K|  5935M|       |  3374K  (1)| 00:02:12 |       |       |  Q1,14 | PCWP |            |
|  76 |                   PX RECEIVE                               |                                       |     1 |    11 |       |  4306   (1)| 00:00:01 |       |       |  Q1,14 | PCWP |            |
|  77 |                    PX SEND BROADCAST                       | :TQ10005                              |     1 |    11 |       |  4306   (1)| 00:00:01 |       |       |  Q1,05 | P->P | BROADCAST  |
|  78 |                     PX BLOCK ITERATOR                      |                                       |     1 |    11 |       |  4306   (1)| 00:00:01 |       |       |  Q1,05 | PCWC |            |
|* 79 |                      TABLE ACCESS FULL                     | TRANSACTION_DETAIL                    |     1 |    11 |       |  4306   (1)| 00:00:01 |       |       |  Q1,05 | PCWP |            |
|* 80 |                   HASH JOIN RIGHT OUTER                    |                                       |  3767K|  5895M|       |  3369K  (1)| 00:02:12 |       |       |  Q1,14 | PCWP |            |
|  81 |                    PX RECEIVE                              |                                       |   193K|  4733K|       |   554   (1)| 00:00:01 |       |       |  Q1,14 | PCWP |            |
|  82 |                     PX SEND BROADCAST                      | :TQ10006                              |   193K|  4733K|       |   554   (1)| 00:00:01 |       |       |  Q1,06 | P->P | BROADCAST  |
|  83 |                      PX BLOCK ITERATOR                     |                                       |   193K|  4733K|       |   554   (1)| 00:00:01 |       |       |  Q1,06 | PCWC |            |
|* 84 |                       TABLE ACCESS FULL                    | INSTRUMENT_IDENTIFICATION             |   193K|  4733K|       |   554   (1)| 00:00:01 |       |       |  Q1,06 | PCWP |            |
|  85 |                    NESTED LOOPS                            |                                       |  2990K|  4609M|       |  3369K  (1)| 00:02:12 |       |       |  Q1,14 | PCWP |            |
|  86 |                     NESTED LOOPS                           |                                       |  3030K|  4609M|       |  3369K  (1)| 00:02:12 |       |       |  Q1,14 | PCWP |            |
|  87 |                      NESTED LOOPS                          |                                       |  3030K|  4456M|       |  2538K  (1)| 00:01:40 |       |       |  Q1,14 | PCWP |            |
|  88 |                       NESTED LOOPS                         |                                       |  3030K|  4415M|       |  1696K  (1)| 00:01:07 |       |       |  Q1,14 | PCWP |            |
|  89 |                        NESTED LOOPS                        |                                       |  3030K|  4351M|       |   854K  (1)| 00:00:34 |       |       |  Q1,14 | PCWP |            |
|* 90 |                         HASH JOIN RIGHT OUTER              |                                       |  3030K|  4297M|       | 12574   (1)| 00:00:01 |       |       |  Q1,14 | PCWP |            |
|  91 |                          PX RECEIVE                        |                                       | 11749 |   160K|       |    77   (0)| 00:00:01 |       |       |  Q1,14 | PCWP |            |
|  92 |                           PX SEND BROADCAST                | :TQ10007                              | 11749 |   160K|       |    77   (0)| 00:00:01 |       |       |  Q1,07 | S->P | BROADCAST  |
|  93 |                            PX SELECTOR                     |                                       |       |       |       |            |          |       |       |  Q1,07 | SCWC |            |
|  94 |                             TABLE ACCESS FULL              | CA_EVENT                              | 11749 |   160K|       |    77   (0)| 00:00:01 |       |       |  Q1,07 | SCWP |            |
|* 95 |                          HASH JOIN                         |                                       |  3030K|  4256M|       | 12495   (1)| 00:00:01 |       |       |  Q1,14 | PCWP |            |
|  96 |                           PX RECEIVE                       |                                       |  3054K|   113M|       |  4656   (1)| 00:00:01 |       |       |  Q1,14 | PCWP |            |
|  97 |                            PX SEND HYBRID HASH             | :TQ10008                              |  3054K|   113M|       |  4656   (1)| 00:00:01 |       |       |  Q1,08 | P->P | HYBRID HASH|
|  98 |                             STATISTICS COLLECTOR           |                                       |       |       |       |            |          |       |       |  Q1,08 | PCWC |            |
|  99 |                              VIEW                          |                                       |  3054K|   113M|       |  4656   (1)| 00:00:01 |       |       |  Q1,08 | PCWP |            |
| 100 |                               PX BLOCK ITERATOR            |                                       |  3054K|    55M|       |  4656   (1)| 00:00:01 |       |       |  Q1,08 | PCWC |            |
| 101 |                                TABLE ACCESS FULL           | TRANSACTION_COST                      |  3054K|    55M|       |  4656   (1)| 00:00:01 |       |       |  Q1,08 | PCWP |            |
| 102 |                           PX RECEIVE                       |                                       |  3030K|  4143M|       |  7833   (1)| 00:00:01 |       |       |  Q1,14 | PCWP |            |
| 103 |                            PX SEND HYBRID HASH             | :TQ10009                              |  3030K|  4143M|       |  7833   (1)| 00:00:01 |       |       |  Q1,09 | P->P | HYBRID HASH|
| 104 |                             PX BLOCK ITERATOR              |                                       |  3030K|  4143M|       |  7833   (1)| 00:00:01 |     1 |1048575|  Q1,09 | PCWC |            |
| 105 |                              VIEW                          |                                       |  3030K|  4143M|       |  7833   (1)| 00:00:01 |       |       |  Q1,09 | PCWP |            |
| 106 |                               TABLE ACCESS FULL            | TRANSACTION                           |  3030K|   323M|       |  7833   (1)| 00:00:01 |     1 |1048575|  Q1,09 | PCWP |            |
| 107 |                         TABLE ACCESS BY INDEX ROWID        | ACCOUNT                               |     1 |    19 |       |     1   (0)| 00:00:01 |       |       |  Q1,14 | PCWP |            |
|*108 |                          INDEX UNIQUE SCAN                 | PK_ACCOUNT                            |     1 |       |       |     0   (0)| 00:00:01 |       |       |  Q1,14 | PCWP |            |
| 109 |                        TABLE ACCESS BY INDEX ROWID         | TRANSACTION_TYPE                      |     1 |    22 |       |     1   (0)| 00:00:01 |       |       |  Q1,14 | PCWP |            |
|*110 |                         INDEX UNIQUE SCAN                  | PK_TRANSACTION_TYPE                   |     1 |       |       |     0   (0)| 00:00:01 |       |       |  Q1,14 | PCWP |            |
| 111 |                       TABLE ACCESS BY INDEX ROWID          | INSTRUMENT                            |     1 |    14 |       |     1   (0)| 00:00:01 |       |       |  Q1,14 | PCWP |            |
|*112 |                        INDEX UNIQUE SCAN                   | PK_INSTRUMENT                         |     1 |       |       |     0   (0)| 00:00:01 |       |       |  Q1,14 | PCWP |            |
|*113 |                      INDEX UNIQUE SCAN                     | PK_INSTRUMENT_GLOBAL                  |     1 |       |       |     0   (0)| 00:00:01 |       |       |  Q1,14 | PCWP |            |
| 114 |                     TABLE ACCESS BY INDEX ROWID            | INSTRUMENT_GLOBAL                     |     1 |    74 |       |     1   (0)| 00:00:01 |       |       |  Q1,14 | PCWP |            |
| 115 |           VIEW PUSHED PREDICATE                            |                                       |     1 |   199 |       |     3   (0)| 00:00:01 |       |       |  Q1,15 | PCWP |            |
| 116 |            TABLE ACCESS BY INDEX ROWID                     | TRANSACTION_EXT                       |     1 |    33 |       |     3   (0)| 00:00:01 |

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.