Results 1 to 7 of 7
  1. #1
    Join Date
    May 2010
    Posts
    56

    Unanswered: Query taking more than 1/2 hour for 80 million rows in fact table

    Hi All,

    I am stuck in this query as it it taking more than 35 mins to execute for 80 million rows. My SLA is less than 30 mins for 160 million rows i.e. double the number.
    Below is the query and the Execution Plan.

    SELECT txn_id AS txn_id,
    acntng_entry_src AS txn_src,
    f.hrarchy_dmn_id AS hrarchy_dmn_id,
    f.prduct_dmn_id AS prduct_dmn_id,
    f.pstng_crncy_id AS pstng_crncy_id,
    f.acntng_entry_typ AS acntng_entry_typ,
    MIN (d.date_value) AS min_val_dt,
    GREATEST (MAX (d.date_value),
    LEAST ('07-Feb-2009', d.fin_year_end_dt))
    AS max_val_dt
    FROM Position_Fact f, Date_Dimension d
    WHERE f.val_dt_dmn_id = d.date_dmn_id
    GROUP BY txn_id,
    acntng_entry_src,
    f.hrarchy_dmn_id,
    f.prduct_dmn_id,
    f.pstng_crncy_id,
    f.acntng_entry_typ,
    d.fin_year_end_dt

    Execution Plan is as:

    11 HASH JOIN Cost: 914,089 Bytes: 3,698,035,872 Cardinality: 77,042,414
    9 TABLE ACCESS FULL TABLE Date_Dimension Cost: 29 Bytes: 94,960 Cardinality: 4,748
    10 TABLE ACCESS FULL TABLE Position_Fact Cost: 913,693 Bytes: 2,157,187,592 Cardinality: 77,042,414


    Kindly suggest, how to make it faster.
    Regards,
    Sid

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Code:
    SELECT txn_id
           AS
           txn_id,
           acntng_entry_src
           AS txn_src,
           f.hrarchy_dmn_id
           AS hrarchy_dmn_id,
           f.prduct_dmn_id
           AS prduct_dmn_id,
           f.pstng_crncy_id
           AS pstng_crncy_id,
           f.acntng_entry_typ
           AS acntng_entry_typ,
           MIN (d.date_value)
           AS min_val_dt,
           Greatest (MAX (d.date_value), Least ('07-Feb-2009', d.fin_year_end_dt))
           AS
           max_val_dt
    FROM   position_fact f,
           date_dimension d
    WHERE  f.val_dt_dmn_id = d.date_dmn_id
    GROUP  BY txn_id,
              acntng_entry_src,
              f.hrarchy_dmn_id,
              f.prduct_dmn_id,
              f.pstng_crncy_id,
              f.acntng_entry_typ,
              d.fin_year_end_dt
    are both f.val_dt_dmn_id & d.date_dmn_id indexed?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    May 2010
    Posts
    56
    Hi All,

    I am stuck in this query as it it taking more than 35 mins to execute for 80 million rows. My SLA is less than 30 mins for 160 million rows i.e. double the number.
    Below is the query and the Execution Plan.

    SELECT
    txn_id AS txn_id,
    acntng_entry_src AS txn_src,
    f.hrarchy_dmn_id AS hrarchy_dmn_id,
    f.prduct_dmn_id AS prduct_dmn_id,
    f.pstng_crncy_id AS pstng_crncy_id,
    f.acntng_entry_typ AS acntng_entry_typ,
    MIN (d.date_value) AS min_val_dt,
    GREATEST (MAX (d.date_value),
    LEAST ('07-Feb-2009', d.fin_year_end_dt))
    AS max_val_dt
    FROM
    Position_Fact f, Date_Dimension d
    WHERE f.val_dt_dmn_id = d.date_dmn_id
    GROUP BY
    txn_id,
    acntng_entry_src,
    f.hrarchy_dmn_id,
    f.prduct_dmn_id,
    f.pstng_crncy_id,
    f.acntng_entry_typ,
    d.fin_year_end_dt

    SQL> show parameter optimizer

    NAME TYPE VALUE
    ------------------------------------ ----------- ------------------------------
    optimizer_capture_sql_plan_baselines boolean FALSE
    optimizer_dynamic_sampling integer 2
    optimizer_features_enable string 11.2.0.3
    optimizer_index_caching integer 0
    optimizer_index_cost_adj integer 100
    optimizer_mode string ALL_ROWS
    optimizer_secure_view_merging boolean TRUE
    optimizer_use_invisible_indexes boolean FALSE
    optimizer_use_pending_statistics boolean FALSE
    optimizer_use_sql_plan_baselines boolean TRUE
    SQL> show parameter db_file_multi

    NAME TYPE VALUE
    ------------------------------------ ----------- ------------------------------
    db_file_multiblock_read_count integer 128
    SQL> show parameter db_block_size

    NAME TYPE VALUE
    ------------------------------------ ----------- ------------------------------
    db_block_size integer 8192
    SQL> show parameter cursor_sharing

    NAME TYPE VALUE
    ------------------------------------ ----------- ------------------------------
    cursor_sharing string EXACT
    SQL>
    SQL> column sname format a20
    SQL> column pname format a20
    SQL> column pval2 format a20
    SQL> select
    2 sname
    3 , pname
    4 , pval1
    5 , pval2
    6 from
    7 sys.aux_stats$;

    SNAME PNAME PVAL1 PVAL2
    -------------------- -------------------- ---------- --------------------
    SYSSTATS_INFO STATUS COMPLETED
    SYSSTATS_INFO DSTART 09-17-2011 10:21
    SYSSTATS_INFO DSTOP 09-17-2011 10:21
    SYSSTATS_INFO FLAGS 1
    SYSSTATS_MAIN CPUSPEEDNW 1751.75879
    SYSSTATS_MAIN IOSEEKTIM 10
    SYSSTATS_MAIN IOTFRSPEED 4096
    SYSSTATS_MAIN SREADTIM
    SYSSTATS_MAIN MREADTIM
    SYSSTATS_MAIN CPUSPEED
    SYSSTATS_MAIN MBRC

    SNAME PNAME PVAL1 PVAL2
    -------------------- -------------------- ---------- --------------------
    SYSSTATS_MAIN MAXTHR
    SYSSTATS_MAIN SLAVETHR

    13 rows selected.


    explain plan for

    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 3823586136

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

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

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

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

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

    | 0 | SELECT STATEMENT | | 1 | 343 | 6

    (17)| 00:00:01 |

    | 1 | HASH GROUP BY | | 1 | 343 | 6

    (17)| 00:00:01 |

    | 2 | NESTED LOOPS | | | |
    | |

    | 3 | NESTED LOOPS | | 1 | 343 | 5

    (0)| 00:00:01 |

    | 4 | TABLE ACCESS FULL | Position_Fact | 1 | 323 | 5

    (0)| 00:00:01 |

    |* 5 | INDEX UNIQUE SCAN | DATE_DMN_PK | 1 | | 0

    (0)| 00:00:01 |

    | 6 | TABLE ACCESS BY INDEX ROWID| Date_Dimension | 1 | 20 | 0

    (0)| 00:00:01 |

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

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


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

    5 - access("F"."VAL_DT_DMN_ID"="D"."DATE_DMN_ID")


    Statistics
    ----------------------------------------------------------
    1 recursive calls
    0 db block gets
    10 consistent gets
    0 physical reads
    0 redo size
    686 bytes sent via SQL*Net to client
    349 bytes received via SQL*Net from client
    1 SQL*Net roundtrips to/from client
    0 sorts (memory)
    0 sorts (disk)
    0 rows processed

    SQL>














    From TOAD Execution Plan is as:

    Plan
    SELECT STATEMENT ALL_ROWSCost: 1,835,219 Bytes: 3,698,035,872 Cardinality: 77,042,414
    4 HASH GROUP BY Cost: 1,835,219 Bytes: 3,698,035,872 Cardinality: 77,042,414
    3 HASH JOIN Cost: 914,089 Bytes: 3,698,035,872 Cardinality: 77,042,414
    1 TABLE ACCESS FULL TABLE Date_Dimension Cost: 29 Bytes: 94,960 Cardinality: 4,748
    2 TABLE ACCESS FULL TABLE Position_Fact Cost: 913,693 Bytes: 2,157,187,592 Cardinality: 77,042,414

    Kindly suggest, how to make it faster.
    Regards,
    Sid

  4. #4
    Join Date
    May 2010
    Posts
    56
    Thanks anacedent for the quick response. Yes they are both indexed. And above is the updated plan with all the parameters. This is part of a big query actually. I am trying to find if there is anything that can be done with it. As it's taking most of the time in the big query.
    In big query as well this gives same Hash Join only.

  5. #5
    Join Date
    May 2010
    Posts
    56
    The entire query and plan are as follows:


    WITH MIN_MX_DT
    AS
    ( SELECT
    TXN_ID AS TXN_ID,
    ACNTNG_ENTRY_SRC AS TXN_SRC,
    F.HRARCHY_DMN_ID AS HRARCHY_DMN_ID,
    F.PRDUCT_DMN_ID AS PRDUCT_DMN_ID,
    F.PSTNG_CRNCY_ID AS PSTNG_CRNCY_ID,
    F.ACNTNG_ENTRY_TYP AS ACNTNG_ENTRY_TYP,
    MIN (D.DATE_VALUE) AS MIN_VAL_DT,
    GREATEST (MAX (D.DATE_VALUE), LEAST (:B1, D.FIN_YEAR_END_DT))
    AS MAX_VAL_DT
    FROM
    proj_PSTNG_FCT F, proj_DATE_DMN D
    WHERE
    F.VAL_DT_DMN_ID = D.DATE_DMN_ID
    GROUP BY
    TXN_ID,
    ACNTNG_ENTRY_SRC,
    F.HRARCHY_DMN_ID,
    F.PRDUCT_DMN_ID,
    F.PSTNG_CRNCY_ID,
    F.ACNTNG_ENTRY_TYP,
    D.FIN_YEAR_END_DT),
    SLCT_RCRDS
    AS (
    SELECT
    M.TXN_ID,
    M.TXN_SRC,
    M.HRARCHY_DMN_ID,
    M.PRDUCT_DMN_ID,
    M.PSTNG_CRNCY_ID,
    M.ACNTNG_ENTRY_TYP,
    D.DATE_VALUE AS VAL_DT,
    D.DATE_DMN_ID,
    D.FIN_WEEK_NUM AS FIN_WEEK_NUM,
    D.FIN_YEAR_STRT AS FIN_YEAR_STRT,
    D.FIN_YEAR_END AS FIN_YEAR_END
    FROM
    MIN_MX_DT M, proj_DATE_DMN D
    WHERE
    D.HOLIDAY_IND = 0
    AND D.DATE_VALUE >= MIN_VAL_DT
    AND D.DATE_VALUE <= MAX_VAL_DT),
    DLY_HDRS
    AS (
    SELECT
    S.TXN_ID AS TXN_ID,
    S.TXN_SRC AS TXN_SRC,
    S.DATE_DMN_ID AS VAL_DT_DMN_ID,
    S.HRARCHY_DMN_ID AS HRARCHY_DMN_ID,
    S.PRDUCT_DMN_ID AS PRDUCT_DMN_ID,
    S.PSTNG_CRNCY_ID AS PSTNG_CRNCY_ID,
    SUM
    (
    DECODE
    (
    PNL_TYP_NM,
    :B5, DECODE (NVL (F.PSTNG_TYP, :B2),
    :B2, NVL (F.PSTNG_AMNT, 0) * (-1),
    NVL (F.PSTNG_AMNT, 0)),
    0))
    AS MTM_AMT,
    NVL (
    LAG (
    SUM (
    DECODE (
    PNL_TYP_NM,
    :B5, DECODE (NVL (F.PSTNG_TYP, :B2),
    :B2, NVL (F.PSTNG_AMNT, 0) * (-1),
    NVL (F.PSTNG_AMNT, 0)),
    0)))
    OVER (
    PARTITION BY S.TXN_ID,
    S.TXN_SRC,
    S.HRARCHY_DMN_ID,
    S.PRDUCT_DMN_ID,
    S.PSTNG_CRNCY_ID
    ORDER BY S.VAL_DT),
    0)
    AS YSTDY_MTM,
    SUM (
    DECODE (
    PNL_TYP_NM,
    :B4, DECODE (NVL (F.PSTNG_TYP, :B2),
    :B2, NVL (F.PSTNG_AMNT, 0) * (-1),
    NVL (F.PSTNG_AMNT, 0)),
    0))
    AS CASH_AMT,
    SUM (
    DECODE (
    PNL_TYP_NM,
    :B3, DECODE (NVL (F.PSTNG_TYP, :B2),
    :B2, NVL (F.PSTNG_AMNT, 0) * (-1),
    NVL (F.PSTNG_AMNT, 0)),
    0))
    AS PAY_REC_AMT,
    S.VAL_DT,
    S.FIN_WEEK_NUM,
    S.FIN_YEAR_STRT,
    S.FIN_YEAR_END,
    NVL (TRUNC (F.REVSN_DT), S.VAL_DT) AS REVSN_DT,
    S.ACNTNG_ENTRY_TYP AS ACNTNG_ENTRY_TYP
    FROM
    SLCT_RCRDS S,
    proj_PSTNG_FCT F,
    proj_ACNT_DMN AD,
    proj_PNL_TYP_DMN PTD
    WHERE
    S.TXN_ID = F.TXN_ID(+)
    AND S.TXN_SRC = F.ACNTNG_ENTRY_SRC(+)
    AND S.HRARCHY_DMN_ID = F.HRARCHY_DMN_ID(+)
    AND S.PRDUCT_DMN_ID = F.PRDUCT_DMN_ID(+)
    AND S.PSTNG_CRNCY_ID = F.PSTNG_CRNCY_ID(+)
    AND S.DATE_DMN_ID = F.VAL_DT_DMN_ID(+)
    AND S.ACNTNG_ENTRY_TYP = F.ACNTNG_ENTRY_TYP(+)
    AND SUBSTR (AD.ACNT_NUM, 0, 1) IN (1, 2, 3)
    AND NVL (F.ACNT_DMN_ID, 1) = AD.ACNT_DMN_ID
    AND NVL (F.PNL_TYP_DMN_ID, 1) = PTD.PNL_TYP_DMN_ID
    GROUP BY
    S.TXN_ID,
    S.TXN_SRC,
    S.DATE_DMN_ID,
    S.HRARCHY_DMN_ID,
    S.PRDUCT_DMN_ID,
    S.PSTNG_CRNCY_ID,
    S.VAL_DT,
    S.FIN_WEEK_NUM,
    S.FIN_YEAR_STRT,
    S.FIN_YEAR_END,
    TRUNC (F.REVSN_DT),
    S.ACNTNG_ENTRY_TYP,
    F.TXN_ID)
    SELECT
    D.TXN_ID,
    D.VAL_DT_DMN_ID,
    D.REVSN_DT,
    D.TXN_SRC,
    D.HRARCHY_DMN_ID,
    D.PRDUCT_DMN_ID,
    D.PSTNG_CRNCY_ID,
    D.YSTDY_MTM,
    D.MTM_AMT,
    D.CASH_AMT,
    D.PAY_REC_AMT,
    MTM_AMT + CASH_AMT + PAY_REC_AMT AS DLY_PNL,
    SUM (
    MTM_AMT + CASH_AMT + PAY_REC_AMT)
    OVER (
    PARTITION BY D.TXN_ID,
    D.TXN_SRC,
    D.HRARCHY_DMN_ID,
    D.PRDUCT_DMN_ID,
    D.PSTNG_CRNCY_ID,
    D.FIN_WEEK_NUM || D.FIN_YEAR_STRT || D.FIN_YEAR_END
    ORDER BY D.VAL_DT)
    AS WTD_PNL,
    SUM (
    MTM_AMT + CASH_AMT + PAY_REC_AMT)
    OVER (
    PARTITION BY D.TXN_ID,
    D.TXN_SRC,
    D.HRARCHY_DMN_ID,
    D.PRDUCT_DMN_ID,
    D.PSTNG_CRNCY_ID,
    D.FIN_YEAR_STRT || D.FIN_YEAR_END
    ORDER BY D.VAL_DT)
    AS YTD_PNL,
    D.ACNTNG_ENTRY_TYP AS ACNTNG_PSTNG_TYP,
    'EOD ETL' AS CRTD_BY,
    SYSTIMESTAMP AS CRTN_DT,
    NULL AS MDFD_BY,
    NULL AS MDFCTN_DT
    FROM
    DLY_HDRS D



    Plan
    SELECT STATEMENT ALL_ROWSCost: 11,950,256 Bytes: 3,369,680,886 Cardinality: 7,854,734
    25 WINDOW SORT Cost: 11,950,256 Bytes: 3,369,680,886 Cardinality: 7,854,734
    24 WINDOW SORT Cost: 11,950,256 Bytes: 3,369,680,886 Cardinality: 7,854,734
    23 VIEW Cost: 10,519,225 Bytes: 3,369,680,886 Cardinality: 7,854,734
    22 WINDOW BUFFER Cost: 10,519,225 Bytes: 997,551,218 Cardinality: 7,854,734
    21 SORT GROUP BY Cost: 10,519,225 Bytes: 997,551,218 Cardinality: 7,854,734
    20 HASH JOIN Cost: 10,296,285 Bytes: 997,551,218 Cardinality: 7,854,734
    1 TABLE ACCESS FULL TABLE proj_PNL_TYP_DMN Cost: 3 Bytes: 45 Cardinality: 5
    19 HASH JOIN Cost: 10,296,173 Bytes: 2,695,349,628 Cardinality: 22,841,946
    5 VIEW VIEW index$_join$_007 Cost: 3 Bytes: 84 Cardinality: 7
    4 HASH JOIN
    2 INDEX FAST FULL SCAN INDEX (UNIQUE) proj_ACNT_DMN_PK Cost: 1 Bytes: 84 Cardinality: 7
    3 INDEX FAST FULL SCAN INDEX (UNIQUE) proj_ACNT_DMN_UNQ Cost: 1 Bytes: 84 Cardinality: 7
    18 HASH JOIN RIGHT OUTER Cost: 10,293,077 Bytes: 68,925,225,244 Cardinality: 650,237,974
    6 TABLE ACCESS FULL TABLE proj_PSTNG_FCT Cost: 913,986 Bytes: 4,545,502,426 Cardinality: 77,042,414
    17 VIEW Cost: 7,300,017 Bytes: 30,561,184,778 Cardinality: 650,237,974
    16 MERGE JOIN Cost: 7,300,017 Bytes: 230,184,242,796 Cardinality: 650,237,974
    8 SORT JOIN Cost: 30 Bytes: 87,776 Cardinality: 3,376
    7 TABLE ACCESS FULL TABLE proj_DATE_DMN Cost: 29 Bytes: 87,776 Cardinality: 3,376
    15 FILTER
    14 SORT JOIN Cost: 7,238,488 Bytes: 25,269,911,792 Cardinality: 77,042,414
    13 VIEW Cost: 1,835,219 Bytes: 25,269,911,792 Cardinality: 77,042,414
    12 SORT GROUP BY Cost: 1,835,219 Bytes: 3,698,035,872 Cardinality: 77,042,414
    11 HASH JOIN Cost: 914,089 Bytes: 3,698,035,872 Cardinality: 77,042,414
    9 TABLE ACCESS FULL TABLE proj_DATE_DMN Cost: 29 Bytes: 94,960 Cardinality: 4,748
    10 TABLE ACCESS FULL TABLE proj_PSTNG_FCT Cost: 913,693 Bytes: 2,157,187,592 Cardinality: 77,042,414


    Kindly suggest.

    Regards,
    Sid

  6. #6
    Join Date
    May 2010
    Posts
    56
    Any suggestions are welcome. They would not only help me with the problem but will add to my learning as well.
    I have tried some of the ways I could. And I am sure there are many more things that can be done by this query.

    Thanks and Respect to all,
    Sid.
    Last edited by sudhirc212; 02-07-12 at 13:37.

  7. #7
    Join Date
    Jan 2012
    Posts
    84
    Please tell us which query do you want to optimize, from your first post or the last one?
    Are statistics up to date ?
    Could you post both the query and the explain plan within code tags: [ code ] + [ /code ], this makes your post more readable.
    And also post full explain plan with predicate information (the last one hasn't it), the preffered way is to use these commands:
    Code:
    explain plan for 
    your_query_goes_here;
    
    select * from table(dbms_xplan.display );
    since gui tools like toad can sometimes show false plans.

Posting Permissions

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