Page 1 of 3 123 LastLast
Results 1 to 15 of 38

Thread: Query Tuning

  1. #1
    Join Date
    Oct 2005
    Posts
    61

    Unanswered: Query Tuning

    Hi,

    I have run the following query 4times with bouncing the database each time. I want to make this query as efficient as possible.

    The reason I want to get this go as fast as it can is- there are around 40
    million records in the production, and this report is taking around 45mins now.
    Each day it's taking longer and longer as the records are increasing. If this is
    the best this query can do, then is there any other solution by which I can get the report in shorter time or anyway by which I can keep the time for report generation constant irrespective of the number of records?

    SELECT pfact.invoice_year,
    dlr.dealer_key,
    dlr.dealer_number,
    dlr.name,
    dlr.city||', '||dlr.state AS location,
    dlr.country,
    dlr.classification,
    dlr.start_date_active,
    dlr.end_date_active,
    dlr.old_dealer_number,
    regdist.region_code,
    regdist.region_desc,
    regdist.regional_manager,
    regdist.district_code,
    regdist.district_desc,
    regdist.district_manager,
    dlr.facing_dealer,
    dlr.parts_parent_dealer,
    -- dlr.parent_location, -- to be added to dealer_dim
    '',
    dlr.financial_report_dealer,
    dlr.consolidation_dealer,
    dlr.parts_management_group,
    org.organization_code,
    org.division_code,
    supp.supplier_code,
    supp.name,
    pgrp.product_group_desc,
    -- pvdr.parent_code,
    -- pvdr.part_classification,
    -- pvdr.proprietary_ind,
    -- dship.direct_ship_program,
    -- ordr.order_type,
    -- pri.priority_code,
    NVL(DECODE(invoice_year, TO_CHAR(SYSDATE - 1,'YYYY'),
    DECODE(ordr.order_type,'PDC',
    DECODE(pri.priority_code,'S',
    SUM(pfact.local_ext_sales_price), 0) ,0)
    ,0), 0) AS pdc_stock,
    NVL(DECODE(invoice_year, TO_CHAR(SYSDATE - 1,'YYYY'),
    DECODE(ordr.order_type,'PDC',
    DECODE(pri.priority_code,'E',
    SUM(pfact.local_ext_sales_price), 0),
    0), 0), 0) AS pdc_vor,
    NVL(DECODE(invoice_year, TO_CHAR(SYSDATE - 1,'YYYY'),
    DECODE(ordr.order_type,'DSP',
    SUM(pfact.local_ext_sales_price), 0), 0), 0) AS
    vdsp,
    NVL(DECODE(invoice_year, TO_CHAR(SYSDATE - 1,'YYYY'),
    DECODE(ordr.order_type,'SD',
    SUM(pfact.local_ext_sales_price), 0), 0), 0) AS
    ship_direct,
    NVL(DECODE(invoice_year, TO_CHAR(SYSDATE - 1,'YYYY'),
    SUM(pfact.local_ext_sales_price), 0), 0) AS gross_sales,
    NVL(DECODE(invoice_year, TO_CHAR(SYSDATE - 1,'YYYY'),
    SUM(pfact.local_gross_profit), 0), 0) AS gross_profit,
    NVL(DECODE(invoice_year, TO_CHAR(SYSDATE - 1,'YYYY') - 1,
    DECODE(ordr.order_type,'PDC',
    DECODE(pri.priority_code,'S',
    SUM(pfact.local_ext_sales_price), 0),
    0), 0), 0) AS pdc_stock_prev,
    NVL(DECODE(invoice_year, TO_CHAR(SYSDATE - 1,'YYYY') - 1,
    DECODE(ordr.order_type,'PDC',
    DECODE(pri.priority_code,'E',

    SUM(pfact.local_ext_sales_price), 0), 0), 0), 0) AS pdc_vor_prev,
    NVL(DECODE(invoice_year, TO_CHAR(SYSDATE - 1,'YYYY') - 1,
    DECODE(ordr.order_type,'DSP',
    SUM(pfact.local_ext_sales_price), 0), 0), 0) AS
    vdsp_prev,
    NVL(DECODE(invoice_year, TO_CHAR(SYSDATE - 1,'YYYY') - 1,
    DECODE(ordr.order_type,'SD',
    SUM(pfact.local_ext_sales_price), 0), 0), 0) AS
    ship_direct_prev,
    NVL(DECODE(invoice_year, TO_CHAR(SYSDATE - 1,'YYYY') - 1,
    SUM(pfact.local_ext_sales_price), 0), 0) AS
    gross_sales_prev,
    NVL(DECODE(invoice_year, TO_CHAR(SYSDATE - 1,'YYYY') - 1,
    SUM(pfact.local_gross_profit), 0), 0) AS
    gross_profit_prev
    FROM priority_dim pri,
    product_group_dim pgrp,
    order_type_dim ordr,
    organization_dim org,
    dealer_dim dlr,
    region_district_dim regdist,
    price_vendor_dim pvdr,
    part_dim pdim,
    supplier_dim supp,
    part_order_line_fact pfact
    WHERE regdist.type(+) = 'PARTS'
    -- AND dlr.dealer_number = 'A300'
    AND regdist.region_district_key IN
    (SELECT MIN(region_district_key)
    FROM region_district_dim
    WHERE type = 'PARTS'
    AND dealer_key = dlr.dealer_key)
    AND (pfact.invoice_date_key BETWEEN TO_NUMBER(TO_CHAR(SYSDATE-1,
    'yyyy')- 1||'0101')
    AND TO_NUMBER(TO_CHAR(SYSDATE-1,
    'yyyymmdd')))
    AND (pfact.invoice_date_key NOT BETWEEN
    TO_NUMBER(TO_CHAR(ADD_MONTHS(SYSDATE-1, -12), 'yyyymmdd'))
    AND TO_CHAR(TO_CHAR(SYSDATE-1,
    'YYYY')- 1)||'1231')
    --w/ Canada dealers
    -- AND (SUBSTR(dlr.dealer_number, 1, 1) NOT IN ('X','Y'))
    --w/o Canada dealers
    AND (SUBSTR(dlr.dealer_number, 1, 1) NOT IN ('X','Y','N'))
    AND NVL(gl_code, 'MISC') NOT LIKE 'DDSS%'
    AND NVL(gl_code, 'MISC') NOT LIKE 'SSMX%'
    AND NVL(gl_code, 'MISC') NOT LIKE 'SSMU%'
    AND pdim.part_ind = 'Y'
    AND dlr.dealer_key = regdist.dealer_key(+)
    AND dlr.dealer_key = pfact.dealer_key
    -- AND regdist.region_district_key (+) = pfact.region_district_key
    -- for region/district history only
    AND org.organization_key = pfact.organization_key
    AND pgrp.product_group = pvdr.product_group
    -- AND cr.credit_reason_key = pfact.credit_reason_key
    -- AND dship.direct_ship_program_key(+) = pfact.direct_ship_program_key
    AND ordr.order_type_key = pfact.order_type_key
    AND pri.priority_key = pfact.priority_key
    AND pdim.part_key = pfact.shipped_part_key
    AND pdim.organization_key = pfact.organization_key
    AND pvdr.price_vendor_key = pfact.price_vendor_key
    AND supp.supplier_key = pfact.supplier_key
    GROUP BY pfact.invoice_year,
    dlr.dealer_key,
    dlr.dealer_number,
    dlr.name,
    dlr.city||', '||dlr.state,
    dlr.country,
    dlr.classification,
    dlr.start_date_active,
    dlr.end_date_active,
    dlr.old_dealer_number,
    regdist.region_code,
    regdist.region_desc,
    regdist.regional_manager,
    regdist.district_code,
    regdist.district_desc,
    regdist.district_manager,
    dlr.facing_dealer,
    dlr.parts_parent_dealer,
    -- dlr.parent_location,
    dlr.financial_report_dealer,
    dlr.consolidation_dealer,
    dlr.parts_management_group,
    org.organization_code,
    org.division_code,
    supp.supplier_code,
    supp.name,
    pgrp.product_group_desc,
    -- pvdr.parent_code,
    -- pvdr.part_classification,
    -- pvdr.proprietary_ind,
    -- dship.direct_ship_program,
    ordr.order_type,
    pri.priority_code

    call count cpu elapsed disk query current rows
    ------- ------ -------- ---------- ---------- ---------- ---------- ----------
    Parse 1 0.94 0.91 45 1114 0 0
    Execute 1 0.00 0.00 0 0 0 0
    Fetch 16460 383.89 383.32 91733 37799840 44 246878
    ------- ------ -------- ---------- ---------- ---------- ---------- ----------
    total 16462 384.83 384.24 91778 37800954 44 246878

    Misses in library cache during parse: 1
    Optimizer goal: CHOOSE
    Parsing user id: 24



    2.

    call count cpu elapsed disk query current rows
    ------- ------ -------- ---------- ---------- ---------- ---------- ----------
    Parse 1 0.94 0.94 33 1103 0 0
    Execute 1 0.00 0.00 0 0 0 0
    Fetch 16460 382.68 382.32 91801 37799840 44 246878
    ------- ------ -------- ---------- ---------- ---------- ---------- ----------
    total 16462 383.62 383.26 91834 37800943 44 246878

    P.S - There is no change in the execution plan with the PUSH_SUBQ hint.

    Thanks,
    Srinivas

  2. #2
    Join Date
    Jul 2003
    Posts
    2,296
    formatting
    Code:
    SELECT
       pfact.invoice_year,
       dlr.dealer_key,
       dlr.dealer_number,
       dlr.name,
       dlr.city||', '||dlr.state AS location,
       dlr.country,
       dlr.classification,
       dlr.start_date_active,
       dlr.end_date_active,
       dlr.old_dealer_number,
       regdist.region_code,
       regdist.region_desc,
       regdist.regional_manager,
       regdist.district_code,
       regdist.district_desc,
       regdist.district_manager,
       dlr.facing_dealer,
       dlr.parts_parent_dealer, '',
       dlr.financial_report_dealer,
       dlr.consolidation_dealer,
       dlr.parts_management_group,
       org.organization_code,
       org.division_code,
       supp.supplier_code,
       supp.name,
       pgrp.product_group_desc,
       NVL(DECODE(invoice_year, TO_CHAR(SYSDATE - 1,'YYYY'), DECODE(ordr.order_type,'PDC', DECODE(pri.priority_code,'S', SUM(pfact.local_ext_sales_price), 0) ,0) ,0), 0) AS pdc_stock,
       NVL(DECODE(invoice_year, TO_CHAR(SYSDATE - 1,'YYYY'), DECODE(ordr.order_type,'PDC', DECODE(pri.priority_code,'E', SUM(pfact.local_ext_sales_price), 0), 0), 0), 0) AS pdc_vor,
       NVL(DECODE(invoice_year, TO_CHAR(SYSDATE - 1,'YYYY'), DECODE(ordr.order_type,'DSP', SUM(pfact.local_ext_sales_price), 0), 0), 0) AS vdsp,
       NVL(DECODE(invoice_year, TO_CHAR(SYSDATE - 1,'YYYY'),
       DECODE(ordr.order_type,'SD', SUM(pfact.local_ext_sales_price), 0), 0), 0) AS ship_direct,
       NVL(DECODE(invoice_year, TO_CHAR(SYSDATE - 1,'YYYY'), SUM(pfact.local_ext_sales_price), 0), 0) AS gross_sales, NVL(DECODE(invoice_year, TO_CHAR(SYSDATE - 1,'YYYY'), SUM(pfact.local_gross_profit), 0), 0) AS gross_profit,
       NVL(DECODE(invoice_year, TO_CHAR(SYSDATE - 1,'YYYY') - 1, DECODE(ordr.order_type,'PDC', DECODE(pri.priority_code,'S', SUM(pfact.local_ext_sales_price), 0), 0), 0), 0) AS pdc_stock_prev,
       NVL(DECODE(invoice_year, TO_CHAR(SYSDATE - 1,'YYYY') - 1, DECODE(ordr.order_type,'PDC', DECODE(pri.priority_code,'E', SUM(pfact.local_ext_sales_price), 0), 0), 0), 0) AS pdc_vor_prev,
       NVL(DECODE(invoice_year, TO_CHAR(SYSDATE - 1,'YYYY') - 1, DECODE(ordr.order_type,'DSP', SUM(pfact.local_ext_sales_price), 0), 0), 0) AS
       vdsp_prev, NVL(DECODE(invoice_year, TO_CHAR(SYSDATE - 1,'YYYY') - 1,
       DECODE(ordr.order_type,'SD', SUM(pfact.local_ext_sales_price), 0), 0), 0) AS ship_direct_prev,
       NVL(DECODE(invoice_year, TO_CHAR(SYSDATE - 1,'YYYY') - 1, SUM(pfact.local_ext_sales_price), 0), 0) AS gross_sales_prev,
       NVL(DECODE(invoice_year, TO_CHAR(SYSDATE - 1,'YYYY') - 1, SUM(pfact.local_gross_profit), 0), 0) AS gross_profit_prev
    FROM
       priority_dim pri,
       product_group_dim pgrp,
       order_type_dim ordr,
       organization_dim org,
       dealer_dim dlr,
       region_district_dim regdist,
       price_vendor_dim pvdr,
       part_dim pdim,
       supplier_dim supp,
       part_order_line_fact pfact
    WHERE
       regdist.type(+) = 'PARTS'
       AND regdist.region_district_key IN
       (
        SELECT MIN(region_district_key)
         FROM region_district_dim
         WHERE type = 'PARTS'
           AND dealer_key = dlr.dealer_key
       )
       AND (pfact.invoice_date_key BETWEEN
              TO_NUMBER(TO_CHAR(SYSDATE-1, 'yyyy') - 1||'0101')
          AND TO_NUMBER(TO_CHAR(SYSDATE-1, 'yyyymmdd')))
       AND (pfact.invoice_date_key NOT BETWEEN
              TO_NUMBER(TO_CHAR(ADD_MONTHS(SYSDATE-1, -12), 'yyyymmdd'))
          AND TO_CHAR(TO_CHAR(SYSDATE-1, 'YYYY')- 1)||'1231')
       AND (SUBSTR(dlr.dealer_number, 1, 1) NOT IN ('X','Y','N'))
       AND NVL(gl_code, 'MISC') NOT LIKE 'DDSS%'
       AND NVL(gl_code, 'MISC') NOT LIKE 'SSMX%'
       AND NVL(gl_code, 'MISC') NOT LIKE 'SSMU%'
       AND pdim.part_ind = 'Y'
       AND dlr.dealer_key = regdist.dealer_key(+)
       AND dlr.dealer_key = pfact.dealer_key
       AND org.organization_key = pfact.organization_key
       AND pgrp.product_group = pvdr.product_group
       AND ordr.order_type_key = pfact.order_type_key
       AND pri.priority_key = pfact.priority_key
       AND pdim.part_key = pfact.shipped_part_key
       AND pdim.organization_key = pfact.organization_key
       AND pvdr.price_vendor_key = pfact.price_vendor_key
       AND supp.supplier_key = pfact.supplier_key
    GROUP BY
       pfact.invoice_year,
       dlr.dealer_key,
       dlr.dealer_number,
       dlr.name,
       dlr.city||', '||dlr.state,
       dlr.country,
       dlr.classification,
       dlr.start_date_active,
       dlr.end_date_active,
       dlr.old_dealer_number,
       regdist.region_code,
       regdist.region_desc,
       regdist.regional_manager,
       regdist.district_code,
       regdist.district_desc,
       regdist.district_manager,
       dlr.facing_dealer,
       dlr.parts_parent_dealer,
       dlr.financial_report_dealer,
       dlr.consolidation_dealer,
       dlr.parts_management_group,
       org.organization_code,
       org.division_code,
       supp.supplier_code,
       supp.name,
       pgrp.product_group_desc,
       ordr.order_type,
       pri.priority_code
    
    
    call     count      cpu    elapsed       disk      query    current       rows
    ------- ------ -------- ---------- ---------- ---------- ---------- ----------
    Parse        1     0.94       0.91         45       1114          0          0
    Fetch    16460   383.89     383.32      91733   37799840         44     246878
    ------- ------ -------- ---------- ---------- ---------- ---------- ----------
    total    16462   384.83     384.24      91778   37800954         44     246878
    
    Misses in library cache during parse: 1
    Optimizer goal: CHOOSE
    Parsing user id: 24
    
     
    
    call     count      cpu    elapsed       disk      query    current       rows
    ------- ------ -------- ---------- ---------- ---------- ---------- ----------
    Parse        1     0.94       0.94         33       1103          0          0
    Fetch    16460   382.68     382.32      91801   37799840         44     246878
    ------- ------ -------- ---------- ---------- ---------- ---------- ----------
    total    16462   383.62     383.26      91834   37800943         44     246878
    Last edited by The_Duck; 07-11-06 at 17:57.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  3. #3
    Join Date
    Oct 2005
    Posts
    61
    I apologize for not having formatted the code!

    -Srinivas

  4. #4
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    It looks like the outer joins to regdist are being invalidated by the IN clause, so removing the outer join(+) should help. Also some functional indexes may help on those criteria which use functions. Apart from that the usual things apply like are the stats upto date, are the stats accurate, (check user_tab_columns) etc.

    Alan

  5. #5
    Join Date
    Oct 2005
    Posts
    61
    Alan,

    Thanks for the response!

    I have tried removing the outer join in regdist.type(+) = 'PARTS' but it did not give any gain. I also replaced the in with = in regdist.region_district_key IN
    (SELECT MIN(region_district_key)
    FROM region_district_dim
    WHERE type = 'PARTS'
    AND dealer_key = dlr.dealer_key) , even this did not give any improvement.

    Could you eloborate which outer join are you referring to? Are you referring to the dlr.dealer_key = regdist.dealer_key(+) or to all the all outer joins referenced by regdist?

    Thanks,
    Srinivas

  6. #6
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    I was refering to all the regdist joins which had (+). If one is invalid they should all go.

    Alan

  7. #7
    Join Date
    Oct 2005
    Posts
    61
    I removed all the outer joins referencing the regdist. No good still. Following is the tkprof output -

    call count cpu elapsed disk query current rows
    ------- ------ -------- ---------- ---------- ---------- ---------- ----------
    Parse 1 0.87 0.89 0 0 0 0
    Execute 1 0.00 0.00 0 0 0 0
    Fetch 16462 408.35 727.23 144744 37800321 44 246914
    ------- ------ -------- ---------- ---------- ---------- ---------- ----------
    total 16464 409.22 728.13 144744 37800321 44 246914

    Between could you tell me how you found the outer joins are being invalidated?

    The stats are upto date and gathered using dbms_stats.

    Thanks,
    Srinivas

  8. #8
    Join Date
    Oct 2005
    Posts
    61
    tkprof output is -

    Code:
    call     count       cpu    elapsed       disk      query    current        rows
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    Parse        1      0.87       0.89          0          0          0           0
    Execute      1      0.00       0.00          0          0          0           0
    Fetch    16462    408.35     727.23     144744   37800321         44      246914
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    total    16464    409.22     728.13     144744   37800321         44      246914

  9. #9
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    The outer join is being invalidated by your IN clause.
    If removing all the outer joins to regdist doesnt make any difference it probably means the regdist table is very small thus not making any difference.

    OK, now are the stats accurate, a good way of telling this is looking at the num distinct in user_tab_columns for those columns you reference in your where clause. Also do you know of any columns with skewed data, if you do make sure there are an adaquate number of buckets for the stats. Dont forget dbms_stats has a large number of options, picking the right options can have a big effect.

    Now looking at the execution plan it looks like its all driven from the fact table, part_order_line_fact, I presume this is the largest table? Now if its doing the fact table first using just the invoice date index that would seem to indicate it gets a very large result set which it then prunes down. This may mean if the result set using invoice date is much bigger than your final result set that you may be better off driving off the dealer_dim and regdist tables (use the ORDERED hint with regdist and dealer_dim before your fact table in the from clause, may also help to have an index on pfact.dealer_key). The other criteria which may help is pdim.part_ind if this is very selective.

    Beyond this you would have to look at wether the indexes are optimal (also how they are build i.e. would index compression etc help). Then there are the database configuration parameters but these are usually a last resort (though you could alter your session parameters i.e. maybe set the sort_area manually to help with the group by). Other things to look at would be materialized views which could pre aggregate your data before hand.

    HTH

    Alan

  10. #10
    Join Date
    Oct 2005
    Posts
    61
    Yes, the fact table is the largest with around 4 million records.
    Ordered hint (regdist,dlr,pfact) in same order gives an unable to extend temp tablespace error. And it took much longer to just give this error, which means the optimizer's choice was better.

    I am attaching the execution plan from tkrpof, please let me know if you find anything tunable in this.

    Code:
    call     count       cpu    elapsed       disk      query    current        rows
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    Parse        1      0.86       1.78          0          0          0           0
    Execute      1      0.01       0.00          0          0          0           0
    Fetch    16503    466.94     654.08      81159   38051382         44      247524
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    total    16505    467.81     655.86      81159   38051382         44      247524
    Code:
    Rows     Row Source Operation
    -------  ---------------------------------------------------
     247524  SORT GROUP BY (cr=38051382 r=81159 w=17316 time=642216841 us)
    2376857   CONCATENATION  (cr=38051382 r=63843 w=0 time=464614158 us)
          0    FILTER  (cr=12 r=0 w=0 time=333 us)
          0     FILTER  (cr=12 r=0 w=0 time=332 us)
          0      NESTED LOOPS OUTER (cr=12 r=0 w=0 time=331 us)
          0       NESTED LOOPS  (cr=12 r=0 w=0 time=328 us)
          0        NESTED LOOPS  (cr=12 r=0 w=0 time=327 us)
          0         NESTED LOOPS  (cr=12 r=0 w=0 time=327 us)
          0          NESTED LOOPS  (cr=12 r=0 w=0 time=325 us)
          0           NESTED LOOPS  (cr=12 r=0 w=0 time=325 us)
          0            NESTED LOOPS  (cr=12 r=0 w=0 time=324 us)
          0             NESTED LOOPS  (cr=12 r=0 w=0 time=323 us)
          0              NESTED LOOPS  (cr=12 r=0 w=0 time=322 us)
          0               PARTITION RANGE ALL PARTITION: 1 10 (cr=12 r=0 w=0 time=322 us)
          0                PARTITION LIST ALL PARTITION: 1 1 (cr=12 r=0 w=0 time=184 us)
          0                 TABLE ACCESS BY LOCAL INDEX ROWID PART_ORDER_LINE_FACT PARTITION: 1 10 (cr=12 r=0 w=0 time=159 us)
          0                  INDEX RANGE SCAN INVDATEKEY_BMNDX PARTITION: 1 10 (cr=12 r=0 w=0 time=145 us)(object id 1778698)
          0               TABLE ACCESS BY INDEX ROWID PART_DIM
          0                INDEX UNIQUE SCAN PART_DIM_PK (object id 11357)
          0              TABLE ACCESS BY INDEX ROWID DEALER_DIM
          0               INDEX UNIQUE SCAN DEALER_DIM_PK (object id 1758999)
          0             TABLE ACCESS BY INDEX ROWID ORDER_TYPE_DIM
          0              INDEX UNIQUE SCAN ORDER_TYPE_DIM_PK (object id 11174)
          0            TABLE ACCESS BY INDEX ROWID ORGANIZATION_DIM
          0             INDEX UNIQUE SCAN ORGAN_DIM_PK (object id 11176)
          0           TABLE ACCESS BY INDEX ROWID PRIORITY_DIM
          0            INDEX UNIQUE SCAN PRIORITY_DIM_PK (object id 11385)
          0          TABLE ACCESS BY INDEX ROWID SUPPLIER_DIM
          0           INDEX UNIQUE SCAN SUPPLIER_DIM_PK (object id 11390)
          0         TABLE ACCESS BY INDEX ROWID PRICE_VENDOR_DIM
          0          INDEX UNIQUE SCAN PRICE_VENDOR_DIM_PK (object id 11384)
          0        TABLE ACCESS BY INDEX ROWID PRODUCT_GROUP_DIM
          0         INDEX RANGE SCAN PRODUCT_GROUP_INDX (object id 1775107)
          0       TABLE ACCESS BY INDEX ROWID REGION_DISTRICT_DIM
          0        INDEX RANGE SCAN REGION_DIST_DIM_IDX_TYPEDLR (object id 1778746)
      41549     SORT AGGREGATE (cr=83101 r=0 w=0 time=1012213 us)
      41549      TABLE ACCESS BY INDEX ROWID REGION_DISTRICT_DIM (cr=83101 r=0 w=0 time=711864 us)
      41549       INDEX RANGE SCAN REGION_DIST_DIM_IDX_TYPEDLR (cr=41552 r=0 w=0 time=407470 us)(object id 1778746)
    2376857    FILTER  (cr=38051370 r=63843 w=0 time=463000562 us)
    2376857     FILTER  (cr=37968269 r=63843 w=0 time=456448742 us)
    2376857      NESTED LOOPS OUTER (cr=37968269 r=63843 w=0 time=453596863 us)
    2376857       NESTED LOOPS  (cr=33210727 r=63842 w=0 time=400227238 us)
    2376878        NESTED LOOPS  (cr=30833868 r=63842 w=0 time=365136073 us)
    2376916         NESTED LOOPS  (cr=26080072 r=63833 w=0 time=330752065 us)
    2447153          NESTED LOOPS  (cr=21256001 r=63825 w=0 time=296305535 us)
    2447153           NESTED LOOPS  (cr=18808846 r=63825 w=0 time=272906119 us)
    2447153            NESTED LOOPS  (cr=16361691 r=63825 w=0 time=249824580 us)
    2447153             NESTED LOOPS  (cr=13914536 r=63825 w=0 time=226517465 us)
    2769973              NESTED LOOPS  (cr=8374588 r=63822 w=0 time=181025332 us)
    2770022               PARTITION RANGE ALL PARTITION: 1 10 (cr=64520 r=59946 w=0 time=88755377 us)
    2770022                PARTITION LIST ALL PARTITION: 1 1 (cr=64520 r=59946 w=0 time=86394084 us)
    2770022                 TABLE ACCESS BY LOCAL INDEX ROWID PART_ORDER_LINE_FACT PARTITION: 1 10 (cr=64520 r=59946 w=0 time=84192725 us)
    2950412                  INDEX RANGE SCAN INVDATEKEY_BMNDX PARTITION: 1 10 (cr=3458 r=3426 w=0 time=34701182 us)(object id 1778698)
    2769973               TABLE ACCESS BY INDEX ROWID PART_DIM (cr=8310068 r=3876 w=0 time=82500185 us)
    2770022                INDEX UNIQUE SCAN PART_DIM_PK (cr=5540046 r=152 w=0 time=34353960 us)(object id 11357)
    2447153              TABLE ACCESS BY INDEX ROWID DEALER_DIM (cr=5539948 r=3 w=0 time=36547986 us)
    2769973               INDEX UNIQUE SCAN DEALER_DIM_PK (cr=2769975 r=0 w=0 time=11816039 us)(object id 1758999)
    2447153             TABLE ACCESS BY INDEX ROWID ORDER_TYPE_DIM (cr=2447155 r=0 w=0 time=15635646 us)
    2447153              INDEX UNIQUE SCAN ORDER_TYPE_DIM_PK (cr=2 r=0 w=0 time=4286554 us)(object id 11174)
    2447153            TABLE ACCESS BY INDEX ROWID ORGANIZATION_DIM (cr=2447155 r=0 w=0 time=15701541 us)
    2447153             INDEX UNIQUE SCAN ORGAN_DIM_PK (cr=2 r=0 w=0 time=4101636 us)(object id 11176)
    2447153           TABLE ACCESS BY INDEX ROWID PRIORITY_DIM (cr=2447155 r=0 w=0 time=15981851 us)
    2447153            INDEX UNIQUE SCAN PRIORITY_DIM_PK (cr=2 r=0 w=0 time=4059375 us)(object id 11385)
    2376916          TABLE ACCESS BY INDEX ROWID SUPPLIER_DIM (cr=4824071 r=8 w=0 time=27010625 us)
    2376916           INDEX UNIQUE SCAN SUPPLIER_DIM_PK (cr=2447155 r=3 w=0 time=13618791 us)(object id 11390)
    2376878         TABLE ACCESS BY INDEX ROWID PRICE_VENDOR_DIM (cr=4753796 r=9 w=0 time=27174920 us)
    2376878          INDEX UNIQUE SCAN PRICE_VENDOR_DIM_PK (cr=2376918 r=0 w=0 time=12834461 us)(object id 11384)
    2376857        TABLE ACCESS BY INDEX ROWID PRODUCT_GROUP_DIM (cr=2376859 r=0 w=0 time=24808292 us)
    2376857         INDEX RANGE SCAN PRODUCT_GROUP_INDX (cr=2 r=0 w=0 time=8649982 us)(object id 1775107)
    2376857       TABLE ACCESS BY INDEX ROWID REGION_DISTRICT_DIM (cr=4757542 r=1 w=0 time=39193371 us)
    2376857        INDEX RANGE SCAN REGION_DIST_DIM_IDX_TYPEDLR (cr=2380684 r=0 w=0 time=22280032 us)(object id 1778746)
      41549     SORT AGGREGATE (cr=83101 r=0 w=0 time=1012213 us)
      41549      TABLE ACCESS BY INDEX ROWID REGION_DISTRICT_DIM (cr=83101 r=0 w=0 time=711864 us)
      41549       INDEX RANGE SCAN REGION_DIST_DIM_IDX_TYPEDLR (cr=41552 r=0 w=0 time=407470 us)(object id 1778746)
    
    ********************************************************************************

    And why is many steps taking around 30 million logical reads (as seen in the tkprof)? Is it bacause of poor sql or because of poor design?

    And another big problem I am having is, this system has many indexes and I expect data to be skwed in some columns as well. Now, since I don't know which column and how many columns are skewed, I am not sure if it would adversely affect the performance (of all sql, not the system performance while I gather the stats) if I gather stats using dbms_stats with histograms option for the whole schema.

    Thanks,
    Srinivas

  11. #11
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    The ordered hint would probably only work well if your fact table has an index on dealer_key and if the regdist/dealer_dim criteria are very selective.

    As for skewed columns you only have to check the columns which are in your where clause and since most of its is driven from the fact table thats the one you need to check. There is an option (size auto) in dbms_stats where it will decide which columns are skewed and how many buckets to create.

    Alan

  12. #12
    Join Date
    Jul 2003
    Posts
    2,296
    please post your current query that has your latest edits.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  13. #13
    Join Date
    Oct 2005
    Posts
    61
    The query is -

    Code:
    SELECT pfact.invoice_year,
                    dlr.dealer_key,
                    dlr.dealer_number,
                    dlr.name,
                    dlr.city||', '||dlr.state AS location,
                    dlr.country,
                    dlr.classification,
                    dlr.start_date_active,
                    dlr.end_date_active,
                    dlr.old_dealer_number,
                    regdist.region_code,
                    regdist.region_desc,
                    regdist.regional_manager,
                    regdist.district_code,
                    regdist.district_desc,
                    regdist.district_manager,
                    dlr.facing_dealer,
                    dlr.parts_parent_dealer,
    --              dlr.parent_location,    -- to be added to dealer_dim
                    '',
                    dlr.financial_report_dealer,
                    dlr.consolidation_dealer,
                    dlr.parts_management_group,
                    org.organization_code,
                    org.division_code,
                    supp.supplier_code,
                    supp.name,
                    pgrp.product_group_desc,
    --              pvdr.parent_code,
    --              pvdr.part_classification,
    --              pvdr.proprietary_ind,
    --              dship.direct_ship_program,
    --              ordr.order_type,
    --              pri.priority_code,
                    NVL(DECODE(invoice_year, TO_CHAR(SYSDATE - 1,'YYYY'),
                            DECODE(ordr.order_type,'PDC',
                                    DECODE(pri.priority_code,'S',
                                            SUM(pfact.local_ext_sales_price), 0) ,0) ,0), 0) AS pdc_stock,
                    NVL(DECODE(invoice_year, TO_CHAR(SYSDATE - 1,'YYYY'),
                            DECODE(ordr.order_type,'PDC',
                                    DECODE(pri.priority_code,'E',
                                            SUM(pfact.local_ext_sales_price), 0), 0), 0), 0) AS pdc_vor,
                    NVL(DECODE(invoice_year, TO_CHAR(SYSDATE - 1,'YYYY'),
                            DECODE(ordr.order_type,'DSP',
                                    SUM(pfact.local_ext_sales_price), 0), 0), 0) AS vdsp,
                    NVL(DECODE(invoice_year, TO_CHAR(SYSDATE - 1,'YYYY'),
                            DECODE(ordr.order_type,'SD',
                                    SUM(pfact.local_ext_sales_price), 0), 0), 0) AS ship_direct,
                    NVL(DECODE(invoice_year, TO_CHAR(SYSDATE - 1,'YYYY'),
                            SUM(pfact.local_ext_sales_price), 0), 0) AS gross_sales,
                    NVL(DECODE(invoice_year, TO_CHAR(SYSDATE - 1,'YYYY'),
                            SUM(pfact.local_gross_profit), 0), 0) AS gross_profit,
                    NVL(DECODE(invoice_year, TO_CHAR(SYSDATE - 1,'YYYY') - 1,
                            DECODE(ordr.order_type,'PDC',
                                    DECODE(pri.priority_code,'S',
                                            SUM(pfact.local_ext_sales_price), 0), 0), 0), 0) AS pdc_stock_prev,
                    NVL(DECODE(invoice_year, TO_CHAR(SYSDATE - 1,'YYYY') - 1,
                            DECODE(ordr.order_type,'PDC',
                                    DECODE(pri.priority_code,'E',
                                            SUM(pfact.local_ext_sales_price), 0), 0), 0), 0) AS pdc_vor_prev,
                    NVL(DECODE(invoice_year, TO_CHAR(SYSDATE - 1,'YYYY') - 1,
                            DECODE(ordr.order_type,'DSP',
                                    SUM(pfact.local_ext_sales_price), 0), 0), 0) AS vdsp_prev,
                    NVL(DECODE(invoice_year, TO_CHAR(SYSDATE - 1,'YYYY') - 1,
                            DECODE(ordr.order_type,'SD',
                                    SUM(pfact.local_ext_sales_price), 0), 0), 0) AS ship_direct_prev,
                    NVL(DECODE(invoice_year, TO_CHAR(SYSDATE - 1,'YYYY') - 1,
                            SUM(pfact.local_ext_sales_price), 0), 0) AS gross_sales_prev,
                    NVL(DECODE(invoice_year, TO_CHAR(SYSDATE - 1,'YYYY') - 1,
                            SUM(pfact.local_gross_profit), 0), 0) AS gross_profit_prev
            FROM    priority_dim             pri,
                    product_group_dim              pgrp,
                    order_type_dim           ordr,
                    organization_dim         org,
                    dealer_dim               dlr,
                    region_district_dim      regdist,
                    price_vendor_dim         pvdr,
                   part_dim                 pdim,
                    supplier_dim             supp,
                    part_order_line_fact     pfact
            WHERE regdist.type(+) = 'PARTS'
    --        AND dlr.dealer_number = 'A300'
              AND regdist.region_district_key IN
                                    (SELECT MIN(region_district_key)
                                     FROM region_district_dim
                                     WHERE type = 'PARTS'
                                     AND dealer_key = dlr.dealer_key)
              AND (pfact.invoice_date_key BETWEEN TO_NUMBER(TO_CHAR(SYSDATE-1, 'yyyy')- 1||'0101')
                                              AND TO_NUMBER(TO_CHAR(SYSDATE-1, 'yyyymmdd')))
              AND (pfact.invoice_date_key NOT BETWEEN TO_NUMBER(TO_CHAR(ADD_MONTHS(SYSDATE-1, -12), 'yyyymmdd'))
                                                  AND TO_CHAR(TO_CHAR(SYSDATE-1, 'YYYY')- 1)||'1231')
    --w/ Canada dealers
    --        AND (SUBSTR(dlr.dealer_number, 1, 1) NOT IN ('X','Y'))
    --w/o Canada dealers
              AND (SUBSTR(dlr.dealer_number, 1, 1) NOT IN ('X','Y','N'))
              AND NVL(gl_code, 'MISC') NOT LIKE 'DDSS%'
              AND NVL(gl_code, 'MISC') NOT LIKE 'SSMX%'
              AND NVL(gl_code, 'MISC') NOT LIKE 'SSMU%'
              AND pdim.part_ind = 'Y'
              AND   dlr.dealer_key = regdist.dealer_key(+)
              AND dlr.dealer_key = pfact.dealer_key
    --        AND regdist.region_district_key (+) = pfact.region_district_key       -- for region/district history only
              AND org.organization_key = pfact.organization_key
            AND pgrp.product_group = pvdr.product_group
    --        AND cr.credit_reason_key = pfact.credit_reason_key                    -- used for Volvo sales data only
    --        AND dship.direct_ship_program_key(+) = pfact.direct_ship_program_key
              AND ordr.order_type_key = pfact.order_type_key
              AND pri.priority_key = pfact.priority_key
              AND pdim.part_key = pfact.shipped_part_key
              AND pdim.organization_key = pfact.organization_key
              AND pvdr.price_vendor_key = pfact.price_vendor_key
              AND supp.supplier_key = pfact.supplier_key
            GROUP BY pfact.invoice_year,
                    dlr.dealer_key,
                    dlr.dealer_number,
                    dlr.name,
                    dlr.city||', '||dlr.state,
                    dlr.country,
                    dlr.classification,
                    dlr.start_date_active,
                    dlr.end_date_active,
                    dlr.old_dealer_number,
                    regdist.region_code,
                    regdist.region_desc,
                   regdist.regional_manager,
                    regdist.district_code,
                    regdist.district_desc,
                    regdist.district_manager,
                    dlr.facing_dealer,
                    dlr.parts_parent_dealer,
    --              dlr.parent_location,
                    dlr.financial_report_dealer,
                    dlr.consolidation_dealer,
                    dlr.parts_management_group,
                    org.organization_code,
                    org.division_code,
                    supp.supplier_code,
                    supp.name,
                    pgrp.product_group_desc,
    --              pvdr.parent_code,
    --              pvdr.part_classification,
    --              pvdr.proprietary_ind,
    --              dship.direct_ship_program,
                    ordr.order_type,
                    pri.priority_code;
    I will post the tkprof stats in the reply.

  14. #14
    Join Date
    Oct 2005
    Posts
    61
    Code:
    call     count       cpu    elapsed       disk      query    current        rows
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    Parse        1      0.86       1.78          0          0          0           0
    Execute      1      0.01       0.00          0          0          0           0
    Fetch    16503    466.94     654.08      81159   38051382         44      247524
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    total    16505    467.81     655.86      81159   38051382         44      247524
    
    Misses in library cache during parse: 1
    Optimizer goal: CHOOSE
    Parsing user id: 24
    
    Rows     Row Source Operation
    -------  ---------------------------------------------------
     247524  SORT GROUP BY (cr=38051382 r=81159 w=17316 time=642216841 us)
    2376857   CONCATENATION  (cr=38051382 r=63843 w=0 time=464614158 us)
          0    FILTER  (cr=12 r=0 w=0 time=333 us)
          0     FILTER  (cr=12 r=0 w=0 time=332 us)
          0      NESTED LOOPS OUTER (cr=12 r=0 w=0 time=331 us)
          0       NESTED LOOPS  (cr=12 r=0 w=0 time=328 us)
          0        NESTED LOOPS  (cr=12 r=0 w=0 time=327 us)
          0         NESTED LOOPS  (cr=12 r=0 w=0 time=327 us)
          0          NESTED LOOPS  (cr=12 r=0 w=0 time=325 us)
          0           NESTED LOOPS  (cr=12 r=0 w=0 time=325 us)
          0            NESTED LOOPS  (cr=12 r=0 w=0 time=324 us)
          0             NESTED LOOPS  (cr=12 r=0 w=0 time=323 us)
          0              NESTED LOOPS  (cr=12 r=0 w=0 time=322 us)
          0               PARTITION RANGE ALL PARTITION: 1 10 (cr=12 r=0 w=0 time=322 us)
          0                PARTITION LIST ALL PARTITION: 1 1 (cr=12 r=0 w=0 time=184 us)
          0                 TABLE ACCESS BY LOCAL INDEX ROWID PART_ORDER_LINE_FACT PARTITION: 1 10 (cr=12 r=0 w=0 time=159 us)
          0                  INDEX RANGE SCAN INVDATEKEY_BMNDX PARTITION: 1 10 (cr=12 r=0 w=0 time=145 us)(object id 1778698)
          0               TABLE ACCESS BY INDEX ROWID PART_DIM
          0                INDEX UNIQUE SCAN PART_DIM_PK (object id 11357)
          0              TABLE ACCESS BY INDEX ROWID DEALER_DIM
          0               INDEX UNIQUE SCAN DEALER_DIM_PK (object id 1758999)
          0             TABLE ACCESS BY INDEX ROWID ORDER_TYPE_DIM
          0              INDEX UNIQUE SCAN ORDER_TYPE_DIM_PK (object id 11174)
          0            TABLE ACCESS BY INDEX ROWID ORGANIZATION_DIM
          0             INDEX UNIQUE SCAN ORGAN_DIM_PK (object id 11176)
          0           TABLE ACCESS BY INDEX ROWID PRIORITY_DIM
          0            INDEX UNIQUE SCAN PRIORITY_DIM_PK (object id 11385)
          0          TABLE ACCESS BY INDEX ROWID SUPPLIER_DIM
          0           INDEX UNIQUE SCAN SUPPLIER_DIM_PK (object id 11390)
          0         TABLE ACCESS BY INDEX ROWID PRICE_VENDOR_DIM
          0          INDEX UNIQUE SCAN PRICE_VENDOR_DIM_PK (object id 11384)
          0        TABLE ACCESS BY INDEX ROWID PRODUCT_GROUP_DIM
          0         INDEX RANGE SCAN PRODUCT_GROUP_INDX (object id 1775107)
          0       TABLE ACCESS BY INDEX ROWID REGION_DISTRICT_DIM
          0        INDEX RANGE SCAN REGION_DIST_DIM_IDX_TYPEDLR (object id 1778746)
      41549     SORT AGGREGATE (cr=83101 r=0 w=0 time=1012213 us)
      41549      TABLE ACCESS BY INDEX ROWID REGION_DISTRICT_DIM (cr=83101 r=0 w=0 time=711864 us)
      41549       INDEX RANGE SCAN REGION_DIST_DIM_IDX_TYPEDLR (cr=41552 r=0 w=0 time=407470 us)(object id 1778746)
    2376857    FILTER  (cr=38051370 r=63843 w=0 time=463000562 us)
    2376857     FILTER  (cr=37968269 r=63843 w=0 time=456448742 us)
    2376857      NESTED LOOPS OUTER (cr=37968269 r=63843 w=0 time=453596863 us)
    2376857       NESTED LOOPS  (cr=33210727 r=63842 w=0 time=400227238 us)
    2376878        NESTED LOOPS  (cr=30833868 r=63842 w=0 time=365136073 us)
    2376916         NESTED LOOPS  (cr=26080072 r=63833 w=0 time=330752065 us)
    2447153          NESTED LOOPS  (cr=21256001 r=63825 w=0 time=296305535 us)
    2447153           NESTED LOOPS  (cr=18808846 r=63825 w=0 time=272906119 us)
    2447153            NESTED LOOPS  (cr=16361691 r=63825 w=0 time=249824580 us)
    2447153             NESTED LOOPS  (cr=13914536 r=63825 w=0 time=226517465 us)
    2769973              NESTED LOOPS  (cr=8374588 r=63822 w=0 time=181025332 us)
    2770022               PARTITION RANGE ALL PARTITION: 1 10 (cr=64520 r=59946 w=0 time=88755377 us)
    2770022                PARTITION LIST ALL PARTITION: 1 1 (cr=64520 r=59946 w=0 time=86394084 us)
    2770022                 TABLE ACCESS BY LOCAL INDEX ROWID PART_ORDER_LINE_FACT PARTITION: 1 10 (cr=64520 r=59946 w=0 time=84192725 us)
    2950412                  INDEX RANGE SCAN INVDATEKEY_BMNDX PARTITION: 1 10 (cr=3458 r=3426 w=0 time=34701182 us)(object id 1778698)
    2769973               TABLE ACCESS BY INDEX ROWID PART_DIM (cr=8310068 r=3876 w=0 time=82500185 us)
    2770022                INDEX UNIQUE SCAN PART_DIM_PK (cr=5540046 r=152 w=0 time=34353960 us)(object id 11357)
    2447153              TABLE ACCESS BY INDEX ROWID DEALER_DIM (cr=5539948 r=3 w=0 time=36547986 us)
    2769973               INDEX UNIQUE SCAN DEALER_DIM_PK (cr=2769975 r=0 w=0 time=11816039 us)(object id 1758999)
    2447153             TABLE ACCESS BY INDEX ROWID ORDER_TYPE_DIM (cr=2447155 r=0 w=0 time=15635646 us)
    2447153              INDEX UNIQUE SCAN ORDER_TYPE_DIM_PK (cr=2 r=0 w=0 time=4286554 us)(object id 11174)
    2447153            TABLE ACCESS BY INDEX ROWID ORGANIZATION_DIM (cr=2447155 r=0 w=0 time=15701541 us)
    2447153             INDEX UNIQUE SCAN ORGAN_DIM_PK (cr=2 r=0 w=0 time=4101636 us)(object id 11176)
    2447153           TABLE ACCESS BY INDEX ROWID PRIORITY_DIM (cr=2447155 r=0 w=0 time=15981851 us)
    2447153            INDEX UNIQUE SCAN PRIORITY_DIM_PK (cr=2 r=0 w=0 time=4059375 us)(object id 11385)
    2376916          TABLE ACCESS BY INDEX ROWID SUPPLIER_DIM (cr=4824071 r=8 w=0 time=27010625 us)
    2376916           INDEX UNIQUE SCAN SUPPLIER_DIM_PK (cr=2447155 r=3 w=0 time=13618791 us)(object id 11390)
    2376878         TABLE ACCESS BY INDEX ROWID PRICE_VENDOR_DIM (cr=4753796 r=9 w=0 time=27174920 us)
    2376878          INDEX UNIQUE SCAN PRICE_VENDOR_DIM_PK (cr=2376918 r=0 w=0 time=12834461 us)(object id 11384)
    2376857        TABLE ACCESS BY INDEX ROWID PRODUCT_GROUP_DIM (cr=2376859 r=0 w=0 time=24808292 us)
    2376857         INDEX RANGE SCAN PRODUCT_GROUP_INDX (cr=2 r=0 w=0 time=8649982 us)(object id 1775107)
    2376857       TABLE ACCESS BY INDEX ROWID REGION_DISTRICT_DIM (cr=4757542 r=1 w=0 time=39193371 us)
    2376857        INDEX RANGE SCAN REGION_DIST_DIM_IDX_TYPEDLR (cr=2380684 r=0 w=0 time=22280032 us)(object id 1778746)
      41549     SORT AGGREGATE (cr=83101 r=0 w=0 time=1012213 us)
      41549      TABLE ACCESS BY INDEX ROWID REGION_DISTRICT_DIM (cr=83101 r=0 w=0 time=711864 us)
      41549       INDEX RANGE SCAN REGION_DIST_DIM_IDX_TYPEDLR (cr=41552 r=0 w=0 time=407470 us)(object id 1778746)
    
    ********************************************************************************

  15. #15
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    Looking at your execution plan it doesnt look like you have very selective criteria, your initial resultset from the fact table is 2.95 million rows which gets pruned down to 2.37 million before the group by. Now you might be able to improve performance a bit by getting it to join the fact table to dealer_dim before the join to part_dim as the dealer_dim join seems to be the most selective. You might be able to get the optimizer to do this by maybe collecting more accurate stats (higher sample rate or matbe use histograms) otherwise try a ordered hint.

    The other thing to look at is wether using the index on the fact table is better than doing a full table scan (as it selects 2.95 million rows from 4 million) on the partitions in question.

    Also what version of Oracle are you on?

    Alan

Posting Permissions

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