Results 1 to 6 of 6

Thread: optimization

  1. #1
    Join Date
    Jul 2005
    Posts
    276

    Unanswered: optimization

    Hi all,
    Can someone please help me with optimizing this query.

    I'm running an ETL on a table and its taking very long time to finish.
    Basically Im loading data from 4 tables into dest_tab

    Ps_Asset- driving table ~ 14mil records
    TAB_B -14mil
    TAB_C - 440
    TAB_D -562

    I have indexes on all the source tables in the same order as the conditions are. I ran an explain plan on it and its making index scans on all the tables except the driving table ps_asset which is fair enough as I want to get all the data from ps_asset.

    I tried to insert in parallel and select in parallel but it didnt help much. Then I used index hints only after which the tab_b was making an index scan.

    Please find the ETL below. I deleted few columns to show here but in original it has 93 columns.

    INSERT /* +APPEND PARALLEL(DW,6) */ INTO DEST_TAB DW
    (
    BUSINESS_UNIT
    ,ASSET_ID
    ,ASSET_TYPE
    ...
    ...
    ,CRM_DEPT_SETID
    ,GROUP_ASSET_ID
    ,BUSINESS_UNIT_DESCR
    )
    SELECT /*+ first_rows index(c, c_IDX2) index (b,b_IDX1) */
    --/*+ PARALLEL(ps,6)
    -- PARALLEL(dwal,6)
    -- PARALLEL(dwct,6)
    -- PARALLEL(fs,6)
    -- */
    ps.BUSINESS_UNIT
    ,ps.ASSET_ID
    ,ps.ASSET_TYPE
    ...
    ..
    ,dwct.DESCR50
    FROM PS_ASSET ps,
    tab_b b,
    tab_c c,
    tab_d d
    WHERE
    ps.business_unit = b.business_unit(+) and
    ps.BUSINESS_UNIT=d.BUSINESS_UNIT(+) and
    ps.asset_id=b.asset_id(+) and
    ps.asset_class=c.asset_class(+)
    Can someone please give me few suggestions.

  2. #2
    Join Date
    Jan 2005
    Location
    Atlanta
    Posts
    58

    Can you change to the new ANSI JOIN syntax?

    Can you change to the new ANSI JOIN syntax?

    ...
    FROM PS_ASSET ps
    LEFT OUTER JOIN tab_b b
    ON ps.business_unit = b.business_unit
    AND ps.asset_id = b.asset_id
    LEFT OUTER JOIN tab_c c
    ON ps.asset_class= c.asset_class
    LEFT OUTER JOIN tab_d d
    ON ps.business_unit = d.business_unit

    ???

    So ... you have
    an index on tab_b with columns (business_unit, asset_id)
    an index on tab_c with columns (asset_class)
    and an index on tab_d with columns (business_unit)
    ... correct?

    Are there any other columns to join PS_ASSET with table b, c, or d?

  3. #3
    Join Date
    Jul 2005
    Posts
    276
    the indexes are correct and as far as I know there are no other columns that I can join on with PS_ASSET.

  4. #4
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    1) You need to show the explain plan and also switch on autotracing so you can try different things and see what works best.

    2) You usually find indexes give an advantage when you are filtering out rows which your query isnt doing. So I would have thought the full table scan on all 4 tables (probably using a hash join) would usually be much quicker. full table scans usually work well with parallel query aswell.

    3) Ensure all your tables are analyzed and if any of the join columns are skewed in terms of data distribution then histograms may help.

    4) Using ANSI joins wont improve performance.

    5) Try as many different ways as you can, the Oracle optimizer is such a complex beast that you cant predict how it will behave, sometimes apparently bad sql can work very well. Also try things like index compression etc.

    6) Identify wether the select is slow or the insert is the bottleneck. If it is the insert you may need to look at things like the redo log size, log buffer size etc.

    7) Finally do you really need to create a table or would you better off creating a view which you can query against (maybe with more filtering criteria)?

    Alan

  5. #5
    Join Date
    Jul 2005
    Posts
    276
    Thank you so much Alan for all the options. Let me see what I can apply from here to my situation.

  6. #6
    Join Date
    Jul 2005
    Posts
    276
    All the tables are analyzed.

    SQL> @plan.sql
    Enter value for id: asset
    old 5: start with id = 0 and statement_id = '&&id'
    new 5: start with id = 0 and statement_id = 'asset'
    old 6: connect by prior id = parent_id and statement_id = '&&id'
    new 6: connect by prior id = parent_id and statement_id = 'asset'

    OPERATION OPTIONS OBJECT_NAME ID POS
    -------------------------- ------------ --------------------------- --- ---
    SELECT STATEMENT 0 ###
    NESTED LOOPS OUTER 1 1
    NESTED LOOPS OUTER 2 1
    NESTED LOOPS OUTER 3 1
    TABLE ACCESS FULL PS_ASSET 4 1
    TABLE ACCESS BY INDEX ROW PS_BUS_UNIT_TBL_FS 5 2
    ID

    INDEX UNIQUE SCAN PS_BUS_UNIT_TBL_FS_IDX1 6 1
    TABLE ACCESS BY INDEX ROW DW_ASSET_CLASS_TBL 7 2
    ID

    INDEX FULL SCAN ASSET_CLASS_TBL_IDX2 8 1
    TABLE ACCESS BY INDEX ROW ASSET_LOCATION 9 2
    ID

    INDEX RANGE SCAN ASSET_LOCATION_IDX1 10 1

    11 rows selected.


    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 4261150170

    --------------------------------------------------------------------------------
    ----------

    | Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
    Time |

    --------------------------------------------------------------------------------
    ----------

    | 0 | SELECT STATEMENT | | 26 | 5694 | 4 (25)|
    00:00:01 |

    | 1 | SORT ORDER BY | | 26 | 5694 | 4 (25)|
    00:00:01 |

    |* 2 | CONNECT BY WITH FILTERING| | | | |
    |

    |* 3 | FILTER | | | | |
    |

    | 4 | TABLE ACCESS FULL | PLAN_TABLE$ | 26 | 5694 | 3 (0)|
    00:00:01 |

    |* 5 | HASH JOIN | | | | |
    |

    | 6 | CONNECT BY PUMP | | | | |
    |

    | 7 | TABLE ACCESS FULL | PLAN_TABLE$ | 26 | 5694 | 3 (0)|
    00:00:01 |

    | 8 | TABLE ACCESS FULL | PLAN_TABLE$ | 26 | 5694 | 3 (0)|
    00:00:01 |

    --------------------------------------------------------------------------------
    ----------


    Predicate Information (identified by operation id):
    ---------------------------------------------------

    2 - filter("ID"=0 AND "STATEMENT_ID"='asset')
    3 - filter("ID"=0 AND "STATEMENT_ID"='asset')
    5 - access("PARENT_ID"=NULL)
    filter("STATEMENT_ID"='asset')

    Note
    -----
    - dynamic sampling used for this statement


    Statistics
    ----------------------------------------------------------
    12 recursive calls
    0 db block gets
    43 consistent gets
    0 physical reads
    0 redo size
    834 bytes sent via SQL*Net to client
    236 bytes received via SQL*Net from client
    2 SQL*Net roundtrips to/from client
    8 sorts (memory)
    0 sorts (disk)
    11 rows processed

    SQL>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •