To improve performance of the PL/SQL

_John Smith · #1 (**permalink**) 06-14-04, 12:45

Hello,

The following query takes more than 24 hours!

How it possible to improve it to boost performance:

insert into pscnv_products nologging
(...)
select
prd.CUSTOMER_ID, -- ban
'Dm', -- crm_prod_type
prd.PRODUCT_ID, -- product_id
prd.PRODUCT_TYPE, -- product_type
prd.PROD_STATUS, -- prod_status,
prd.INIT_ACTIVATION_DATE, -- init_activation_date
prd.PROD_STATUS_DATE, -- prod_status_date
prd.PROD_STATUS_LAST_ACT, -- prod_status_last_act
null, -- charge_end_date
prd.CAMPAIGN_CODE, -- campaign_code
prd.REP_CODE, -- rep_code
get_pay_cust_type(prd.customer_id,
prd.product_id,
prd.product_type), -- pay_cust_type
pt.PAYING_BAN, -- paying_ban
pt.CURRENCY_TYPE, -- currency_type
pt.AMOUNT, -- amount
prd.DISCOUNT_PERCENT, -- discount_percent
get_pr_user_name(prd.customer_id,
prd.product_id,
prd.product_type), -- pr_user_name
prd.SUB_MARKET, -- sub_market
prd.CARRIER_ID, -- carrier_id
prd.PRV_PROD, -- prv_prod
prd.NEXT_PROD, -- next_prod
nvl(prd.PRV_PROD_CHG_DATE, -- prod_replace_date
prd.NEXT_PROD_CHG_DATE),
prd.PRV_BAN, -- prv_ban
prd.NEXT_BAN, -- next_ban
nvl(prd.PRV_BAN_MOVE_DATE, -- ban_replace_date
prd.NEXT_BAN_MOVE_DATE),
ds_s.INTERNATIONAL_CODE_TO, -- international_code_to
ds_s.EFFECTIVE_DATE, -- effective_date
ds_s.EXPIRATION_DATE, -- expiration_date
ds_s.PARM_VAL1, -- parm_val1
ds_s.DS_SERVICE_ID, -- ds_service_id
ds_s.DS_SERVICE_TYPE, -- ds_service_type
ds_s.SRV_SEQ_NO, -- srv_seq_no
'', -- address_line_1
'', -- address_line_2
'A', -- discount_level
'', -- future_request
prd.TN_PINC_IND, -- tn_pinc_ind
decode(prd.TN_PINC_IND,
'Y',
'007',
decode(ltrim(rtrim(prd.product_type)),
'T',
get_tracking_status(prd.customer_id,
prd.product_id,
prd.prv_prod),
'')
), -- tracking_status
decode(prd.TN_PINC_IND,
'Y',
'00',
decode(ltrim(rtrim(prd.product_type)),
'T',
get_lec_return_status(prd.customer_id,
prd.product_id,
prd.prv_prod),
'')
), -- lec_return_status
'', -- decode(ltrim(rtrim(prd.carrier_id)),
-- 'BEZQ',
-- get_bezeq_prod_status(
-- decode(prd.prod_status,
-- 'C',
-- prd.prod_status_date,
-- prd.sys_creation_date),
-- prd.product_id,
-- prd.prv_prod
-- ),
-- ''), -- bezeq_prod_status
get_ph_sw_status(prd.switch_prod_status,
prd.product_type), -- phone_switch_status
'', -- decode(ltrim(rtrim(prd.carrier_id)),
-- 'BEZQ',
-- get_bezeq_phone_type(
-- decode(prd.prod_status,
-- 'C',
-- prd.prod_status_date,
-- prd.sys_creation_date),
-- prd.product_id,
-- prd.prv_prod
-- ),
-- ''), -- phone_type
decode(ltrim(rtrim(prd.product_type)),
'C',
get_short_dial_ind(prd.product_id),
null), -- short_dial_ind
decode(ltrim(rtrim(prd.product_type)),
'E',
get_orig_country(prd.product_id),
''), -- orig_country
decode(ltrim(rtrim(prd.product_id)),
'E',
get_dest_country(prd.product_id),
''), -- dest_country
'DmR', -- card_type_code
prd.BC_CARRIER_ID, -- bc_carrier_id
null, -- product_sub_type
null, -- infra_type
null, -- infra
'', -- call_service
'' -- sig_value
from
(select
*
from
PRODUCT
) prd, -- PRODUCT
(select
PAYING_BAN,
CURRENCY_TYPE,
AMOUNT,
EFFECTIVE_DATE,
EXPIRATION_DATE,
PRODUCT_TYPE,
PAID_BAN,
PRODUCT_ID
from
PAYING_TERMS
where
effective_date <= '01-Jul-2003'
and
(
expiration_date >= '01-Jul-2003'
or
expiration_date is null
)
) pt, -- PAYING_TERMS
( select
PRODUCT_ID,
PRODUCT_TYPE,
CUSTOMER_ID,
INTERNATIONAL_CODE_TO,
EFFECTIVE_DATE,
EXPIRATION_DATE,
PARM_VAL1,
DS_SERVICE_ID,
DS_SERVICE_TYPE,
SRV_SEQ_NO
from
DS_SERVICES
) ds_s, -- DS_SERVICES
(select
*
from
BILLING_ACCOUNT
) ba -- BILLING_ACCOUNT

where -- *** MAIN WHERE *** ---
prd.product_id = pt.product_id(+)
and
prd.customer_id = pt.paid_ban(+)
and
prd.product_type = pt.product_type(+)
and
prd.product_id = ds_s.product_id(+)
and
prd.customer_id = ds_s.customer_id(+)
and
prd.product_type = ds_s.product_type(+)
and
prd.customer_id = ba.ban
and
(
ba.col_delinq_status = 'D'

or

(
ba.ban_status <> 'C'

and
(prd.product_type not in ('IN', 'L', 'XT', 'VC')
or
prd.product_type = 'E' and prd.prod_status in ('A', 'S')
or
prd.product_type = 'C' and (prd.prod_status = 'A'
or
prd.effective_date >= '01-Jul-2003' )
)
)
)

and
(
not (
prd.bc_carrier_id = 'BZQ'
and
prd.next_prod is not null
and
(instr( prd.product_id, '06') = 1
or
instr( prd.product_id, '07') = 1)
)
)
and
(
not (
prd.carrier_id <> 'BEZQ'
and
prd.next_prod is not null
and
TO_CHAR(prd.effective_date, 'YYYYMMDD') = '20040421'
)

)

anacedent · #2 (**permalink**) 06-14-04, 12:50

SQL Tuning by Dan Tow
O'Reilly & Associates, Paperback, Published November 2003, 314 pages, ISBN 0596005733

http://www.bookpool.com/.x/4om3p4rid0/sm/0596005733

Author Dan Tow outlines a timesaving method he's developed for finding the optimum execution plan--rapidly and systematically--regardless of the complexity of the SQL or the database platform being used. You'll learn how to understand and control SQL execution plans and how to diagram SQL queries to deduce the best execution plan for a query. Key chapters in the book include exercises to reinforce the concepts you've learned. SQL Tuning concludes by addressing special concerns and unique solutions to "unsolvable problems."

andrewst · #3 (**permalink**) 06-14-04, 13:26

Almost impossuble to say without knowing a lot more about your tables and seeing output from trace etc. However, some points to consider:

1) Your FROM clause is over-complicated. It boils down to this:

Code:

from    PRODUCT  prd,
	( select PAYING_BAN, CURRENCY_TYPE, AMOUNT, EFFECTIVE_DATE, EXPIRATION_DATE, PRODUCT_TYPE, PAID_BAN, PRODUCT_ID
	  from PAYING_TERMS
	  where effective_date <= '01-Jul-2003'
	  and ( expiration_date >= '01-Jul-2003' or expiration_date is null )
	) pt, -- PAYING_TERMS
        DS_SERVICES ds_s,
	BILLING_ACCOUNT ba

2) Your SELECT includes all these function calls:

get_pay_cust_type(prd.customer_id, prd.product_id, prd.product_type),
get_pr_user_name(prd.customer_id, prd.product_id, prd.product_type),
get_tracking_status(prd.customer_id, prd.product_id, prd.prv_prod),
get_lec_return_status(prd.customer_id, prd.product_id, prd.prv_prod),
get_ph_sw_status(prd.switch_prod_status, prd.product_type),
get_short_dial_ind(prd.product_id),
get_orig_country(prd.product_id),
get_dest_country(prd.product_id),

Each one is a performance killer, since each function must be called once for each row returned from the query. Since many of these appear to be simple lookups it would be better to replace the function calls by joins to the appropriate tables and give the optimizer some room to manoevre.

madmack · #4 (**permalink**) 06-17-04, 12:57

Tony nailed it.

Functions are brutal in this type of statement. If you can't figure a way to get rid of them in one step using joins you may be better off to create a procedure that creates some temp tables with the information "pre-processed".

The other obvious thing is to ensure you have the proper indexes in place and that all of the objects used in the statement have been recently analysed.