SQL & PL/SQL

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"(+))