Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2002
    Location
    USA-CA
    Posts
    36

    Unanswered: To improve performance of the PL/SQL

    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'
    )

    )
    John Smith

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    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."
    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
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    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.

  4. #4
    Join Date
    Jun 2004
    Posts
    2
    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.

Posting Permissions

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