4 Replies Latest reply: Feb 15, 2013 4:13 AM by John Stegeman RSS

    Help tuning NESTED LOOPS OUTER joins

    leocoppens
      Hello,

      I have inherited this nasty query (below) that is taking an awful time to complete (more than 2 hrs a day)

      The worst bit is that I need to outer join my fact table so many times as I need bit’s and pieces from other tables/mviews.

      When I look at the explain plan I see that this situation means that the cbo is doing several NESTED LOOPS OUTER join operations. I understand that these nested loops mean going through every row in my primary table to see if there is a match in the secondary table (much smaller) which makes it extremely inefficient, is this right?

      The stats on the tables are all refreshed daily.

      Any ideas on how I can improve the performance here?

      Thanks in advance!

      The query:
      -------------------
      explain plan for
      SELECT x.user_id AS user_id,
      x.login_name AS login_name,
      c.date_of_birth AS date_of_birth,
      x.registration_site AS registration_site,
      x.organisation AS organisation,
      c.user_title AS user_title,
      c.first_name AS first_name,
      c.last_name AS last_name,
      x.email_address AS email_address,
      x.user_status AS user_status,
      x.user_privilege AS user_access_privilege,
      x.date_registration AS date_registration,
      x.affiliate_id AS affiliate_id,
      x.mobile_number AS mobile_number,
      x.optional_parameter AS vt_number,
      gud.display_name AS chat_name,
      REPLACE (s4.address_line_1, ',', '') AS address_line_1,
      REPLACE (s4.address_line_2, ',', '') AS address_line_2,
      REPLACE (s4.town, ',', '') AS town,
      REPLACE (s4.county, ',', '') AS county,
      REPLACE (s4.postcode, ',', '') AS postcode,
      s4.country AS country,
      s3.last_login AS last_login_date,
      x.email_send_newsletter AS email_send_newsletter,
      x.email_give_details_thirdparty AS email_give_details_thirdparty,
      NVL (ia.cash_balance, 0) AS current_cash_balance,
      NVL (ia.bonus_balance, 0) AS current_bonus_balance,
      x.external_affiliate_id AS external_affiliate_id,
      r.currency_code AS currency,
      NVL (ia.points_balance, 0) AS current_loyalty_points_balance,
      p.status AS buyer_status,
      NVL (ia.bi_bonus_balance, 0) AS current_bi_bonus_balance,
      NVL (ia.pending_balance, 0) AS current_pending_balance,
      l.level_name AS current_loyalty_level,
      l.date_level_achieved AS date_level_achieved,
      NVL (l.current_period_loyalty_points, 0) AS current_period_loyalty_points,
      r.region AS user_region,
      x.registration_platform AS registration_platform,
      x.external_user_name AS external_user_name,
      c.home_number AS home_number,
      pr.code AS reg_promo_code,
      g.date_first_buy AS date_first_buy
      FROM gl_user_registrations x,
      gl_region r,
      MVW_USER_BALANCES ia,
      gl_customers c,
      gl_user_display_names gud,
      gl_user_last_login s3,
      (SELECT z.user_id AS user_id,
      z.address_line_1 AS address_line_1,
      z.address_line_2 AS address_line_2,
      z.town AS town,
      z.county AS county,
      z.postcode AS postcode,
      z.country AS country
      FROM gl_user_addresses z
      WHERE z.is_current = 1) s4,
      gl_user_buyer_mapping upm,
      gl_buyer p,
      mvw_user_loyalty_points l,
      MVW_USER_PROMO_CODE_REG pr,
      MVW_USER_FIRST_BUY_DATE g
      WHERE x.base_region = r.region
      AND x.user_id = ia.user_id (+)
      AND x.customer_id = c.customer_id(+)
      AND x.user_id = gud.user_id (+)
      AND x.user_id = s4.user_id (+)
      AND x.user_id = s3.user_id (+)
      AND x.user_id = upm.user_id (+)
      AND upm.buyer_id = p.buyer_id
      AND x.user_id = l.user_id (+)
      AND x.user_id = pr.user_id (+)
      AND x.user_id = g.user_id (+);

      select * from table(dbms_xplan.display);

      Plan hash value: 2158171613

      ---------------------------------------------------------------------------------------------------------------------
      | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
      ---------------------------------------------------------------------------------------------------------------------
      | 0 | SELECT STATEMENT | | 100 | 63100 | 135 (1)| 00:00:01 |
      | 1 | NESTED LOOPS OUTER | | 100 | 63100 | 135 (1)| 00:00:01 |
      | 2 | NESTED LOOPS OUTER | | 100 | 60600 | 120 (1)| 00:00:01 |
      | 3 | NESTED LOOPS OUTER | | 100 | 57100 | 105 (1)| 00:00:01 |
      | 4 | NESTED LOOPS OUTER | | 100 | 55400 | 90 (2)| 00:00:01 |
      | 5 | NESTED LOOPS OUTER | | 100 | 53600 | 70 (2)| 00:00:01 |
      |* 6 | HASH JOIN | | 100 | 47000 | 55 (2)| 00:00:01 |
      | 7 | TABLE ACCESS FULL | GL_REGION | 18 | 252 | 2 (0)| 00:00:01 |
      | 8 | NESTED LOOPS OUTER | | 100 | 22800 | 52 (0)| 00:00:01 |
      | 9 | NESTED LOOPS OUTER | | 100 | 19700 | 47 (0)| 00:00:01 |
      | 10 | NESTED LOOPS OUTER | | 100 | 17600 | 37 (0)| 00:00:01 |
      | 11 | NESTED LOOPS | | 100 | 15800 | 27 (0)| 00:00:01 |
      | 12 | NESTED LOOPS | | 102 | 2754 | 17 (0)| 00:00:01 |
      | 13 | TABLE ACCESS FULL | GL_BUYER | 6143K| 64M| 2 (0)| 00:00:01 |
      | 14 | TABLE ACCESS BY INDEX ROWID| GL_USER_BUYER_MAPPING | 1 | 16 | 1 (0)| 00:00:01 |
      |* 15 | INDEX RANGE SCAN | GL_USER_BUYER_MAPPPING_IX | 1 | | 1 (0)| 00:00:01 |
      | 16 | TABLE ACCESS BY INDEX ROWID | GL_USER_REGISTRATIONS | 1 | 131 | 1 (0)| 00:00:01 |
      |* 17 | INDEX UNIQUE SCAN | PK_GL_USER_REGISTRATIONS | 1 | | 1 (0)| 00:00:01 |
      | 18 | TABLE ACCESS BY INDEX ROWID | GL_USER_LAST_LOGIN | 1 | 18 | 1 (0)| 00:00:01 |
      |* 19 | INDEX UNIQUE SCAN | GL_USER_LAST_LOGIN_PK | 1 | | 1 (0)| 00:00:01 |
      | 20 | TABLE ACCESS BY INDEX ROWID | GL_USER_DISPLAY_NAMES | 1 | 21 | 1 (0)| 00:00:01 |
      |* 21 | INDEX UNIQUE SCAN | PK_GL_USER_DISPLAY_NAMES | 1 | | 1 (0)| 00:00:01 |
      | 22 | TABLE ACCESS BY INDEX ROWID | GL_CUSTOMERS | 1 | 31 | 1 (0)| 00:00:01 |
      |* 23 | INDEX UNIQUE SCAN | PK_GL_CUSTOMERS | 1 | | 1 (0)| 00:00:01 |
      |* 24 | TABLE ACCESS BY INDEX ROWID | GL_USER_ADDRESSES | 1 | 66 | 1 (0)| 00:00:01 |
      |* 25 | INDEX RANGE SCAN | IX_GL_USER_ADDRESSES1 | 1 | | 1 (0)| 00:00:01 |
      | 26 | MAT_VIEW ACCESS BY INDEX ROWID | MVW_USER_FIRST_BUY_DATE | 1 | 18 | 1 (0)| 00:00:01 |
      |* 27 | INDEX RANGE SCAN | MVW_USER_FS_DATE_IDX | 1 | | 1 (0)| 00:00:01 |
      | 28 | MAT_VIEW ACCESS BY INDEX ROWID | MVW_USER_PROMO_CODE_REG | 1 | 17 | 1 (0)| 00:00:01 |
      |* 29 | INDEX RANGE SCAN | MVW_USER_PROMO_CODE_IDX | 1 | | 1 (0)| 00:00:01 |
      | 30 | MAT_VIEW ACCESS BY INDEX ROWID | MVW_USER_LOYALTY_POINTS | 1 | 35 | 1 (0)| 00:00:01 |
      |* 31 | INDEX RANGE SCAN | MVW_USER_LYP_IDX | 1 | | 1 (0)| 00:00:01 |
      | 32 | MAT_VIEW ACCESS BY INDEX ROWID | MVW_USER_BALANCES | 1 | 25 | 1 (0)| 00:00:01 |
      |* 33 | INDEX RANGE SCAN | MVW_USER_BALANCES_IDX | 1 | | 1 (0)| 00:00:01 |
      ---------------------------------------------------------------------------------------------------------------------

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

      6 - access("X"."BASE_REGION"="R"."REGION")
      15 - access("UPM"."BUYER_ID"="P"."BUYER_ID")
      17 - access("X"."USER_ID"="UPM"."USER_ID")
      19 - access("X"."USER_ID"="S3"."USER_ID"(+))
      21 - access("X"."USER_ID"="GUD"."USER_ID"(+))
      23 - access("X"."CUSTOMER_ID"="C"."CUSTOMER_ID"(+))
      24 - filter("Z"."IS_CURRENT"(+)=1)
      25 - access("X"."USER_ID"="Z"."USER_ID"(+))
      27 - access("X"."USER_ID"="G"."USER_ID"(+))
      29 - access("X"."USER_ID"="PR"."USER_ID"(+))
      31 - access("X"."USER_ID"="L"."USER_ID"(+))
      33 - access("X"."USER_ID"="IA"."USER_ID"(+))
        • 1. Re: Help tuning NESTED LOOPS OUTER joins
          riedelme
          I have never been impressed by the performance of outer joins and prefer to avoid them when possible. Sadly it is not often possbile :(

          See if composite indexes exist when the WHERE clause predicates join more than 1 column.

          The full table scan against GL_BUYER is using a lot of resources. Would an index on BUYER_ID or other columns used in the WHERE clause help?

          Also check GL_REGION for indexes although the plan suggests it is very small
          • 2. Re: Help tuning NESTED LOOPS OUTER joins
            Nikolay Savvinov
            Hi,

            1) What you are saying about nested loops is true about any join (except, of course, cartesian joins): you are taking rows from rowsource A and find matching rows from rowsource B. This doesn't make a join method efficient or inefficient.
            2) The plan you posted does not indicate any performance problem whatsoever. I know you have one, but it's not possible to address it without having any information about it. Trace it, get dbms_xplan.display_cursor dump with rowsource stats, or real-time SQL monitoring report (if your version and license allow it) and post the results here, then we'd be able to help
            3) One efficient way to perform queries of your type (big fact table joined to a bunch of small dimension tables) is star transformation, but there are certain pre-requisites for that (like bitmap indexes on FK constraints) -- please read the documentation on star queries/transformations and see if that is an option for you

            Best regards,
            Nikolay
            • 3. Re: Help tuning NESTED LOOPS OUTER joins
              leocoppens
              Thank you guys, I managed to increase the hash_area_size (saw this in http://www.dba-oracle.com/tips_oracle_hash_joins.htm), that changed the exec.plan to hash joins and reduced the time in a few sample queries I did (although the cost went up)
              Thanks for the replies!
              Leo
              • 4. Re: Help tuning NESTED LOOPS OUTER joins
                John Stegeman
                (although the cost went up)
                You cannot really use the cost as a way to decide if a plan is better. My advice: just ignore the cost