This discussion is archived
5 Replies Latest reply: Apr 13, 2012 12:54 PM by Marc Fielding RSS

Cell Offload will Happen for pl/sql Block with variables

895567 Newbie
Currently Being Moderated
Hello Experts,

i am working on procedures on exadata now. i was confused with cell offload in exadata. somehow offload is not happening when i ran the sql statement in in pl/sql block with variables.
here are my findings.

when i ran insert into from select with values at toad, my query response time is very good. the total process is completed in less than a minute.
i checked offload is happening.

same sql statement is placed in plsql block with variable, procedure is taking lot of time and it is not completing. this case offload is not happening.

is it true, if i use variables in pl/sql block will not use cell offload and smart scan?
if yes, what is the work around.


Thanks

#! Pavan
  • 1. Re: Cell Offload will Happen for pl/sql Block with variables
    Marc Fielding Journeyer
    Currently Being Moderated
    Hello Pavan,

    Can you show us the query and PL/SQL block you're using? Does the PL/SQL block give the same results when you run it from TOAD just like your query?

    And how are you determining that offload is or is not happening?

    Thanks!

    Marc
  • 2. Re: Cell Offload will Happen for pl/sql Block with variables
    895567 Newbie
    Currently Being Moderated
    Hello Marc,

    Thanks for quick response.
    when i ran the query with literals in toad session i am getting response.
    when i run it with pl/sql block , block is not completing at all.


    here is the plsql block:

    My Apologies for sending big code,with out proper format.


    ----------------------------------------------------------------------
    DECLARE
    P_BUSINESS_DATE DATE;
    P_BATCH_ID NUMBER;
    UTC_OFFSET NUMBER;

    BEGIN
    P_BUSINESS_DATE := to_date('02/01/2012', 'MM/DD/YYYY');
    P_BATCH_ID := 1;
    UTC_OFFSET := 0;

    INSERT /*+ APPEND */ INTO UPL_CLIENT_tbl
    ( reportdate,
    LastName,
    FirstName,
    MiddleInitial,
    AccountNumber,
    Address,
    City,
    State,
    Zip,
    HomePhone,
    WorkPhone,
    BirthDate,
    Age,
    Sex,
    NumberOfChildren,
    Occupation,
    LeadSource,
    Consultant,
    ProgramDirector,
    CallTaker,
    LeadDate,
    FirstVisitDate,
    LastVisitDate,
    BillType,
    ClientType,
    PreviousClientType,
    AppointmentDate,
    DoctorLetterRequired,
    OneYearPermStabilizationDate,
    UnlimitedPermStabilizationDate,
    MaritalStatus,
    ReferrerName,
    ReferrerCentreID,
    CentreID,
    PaymentDateOne,
    PaymentAmountOne,
    PaymentDateTwo,
    PaymentAmountTwo,
    PaymentDateThree,
    PaymentAmountThree,
    PaymentDateFour,
    PaymentAmountFour,
    LibraryPurchased,
    BalanceDue,
    FoodNSFBalance,
    ProductNSFBalance,
    ProgramNSFBalance,
    StartWeight,
    CurrentWeight,
    GoalWeight,
    Height,
    DateGoalWeightAchieved,
    DateSuccessPlusPurchased,
    ReturnToActiveDate,
    VersionNumber,
    HalfWayDate,
    LastLSCDate,
    LastUpdatedDate,
    VitaminWaiverSigned,
    LastSupplementPurchaseDate,
    LastSupplementCodePurchased,
    LastTotalSupplementSupplyCycle,
    LastAddtlSupplPurchaseDate,
    LastAddtlSupplCodePurchased,
    LastAddtlSupplSupplyCycle,
    DiabetesClient,
    DietControlled,
    TakingOralMed,
    TakingInsulin,
    EmailId,
    CTADate,
    RWLDate,
    Address2)
    (SELECT /*+ full(S_CONTACT) full(REFERRER) full(Consultant) full(ProgramDirector) full(CallTaker) full(S_CONTACT_X) full(a) full(a2) full (a3) */ distinct p_business_date reportdate,
    SUBSTR(S_CONTACT.LAST_NAME,1,25) AS LastName,
    SUBSTR(S_CONTACT.FST_NAME,1,25) AS FirstName,
    SUBSTR(S_CONTACT.MID_NAME,1,1) AS MiddleInitial,
    S_CONTACT.X_JC_ACNT_NUM + 900000000 AS AccountNumber,
    SUBSTR(S_ADDR_PER.ADDR,1,40) AS ADDRESS,
    SUBSTR(S_ADDR_PER.CITY,1,20) AS City,
    S_ADDR_PER.STATE AS State,
    SUBSTR(S_ADDR_PER.ZIPCODE,1,15) AS Zip,
    SUBSTR(REPLACE(S_CONTACT.HOME_PH_NUM,'-',''),1,10) AS HomePhone,
    SUBSTR(REPLACE(S_CONTACT.WORK_PH_NUM,'-',''),1,10) AS WorkPhone,
    S_CONTACT.BIRTH_DT AS BirthDate,
    CASE WHEN FLOOR((p_business_date - S_CONTACT.BIRTH_DT)/360) < 0 THEN NULL ELSE FLOOR((p_business_date - S_CONTACT.BIRTH_DT)/360) END AS AGE,
    S_CONTACT.SEX_MF AS SEX,
    NULL AS NumberOfChildren,
    S_CONTACT_X.ATTRIB_34 AS OCCUPATION,
    CASE WHEN SUBSTR(S_CONTACT_X.ATTRIB_37,1,4)='Othe' THEN 'Othr'
    WHEN SUBSTR(S_CONTACT_X.ATTRIB_37,1,4)='Inte' THEN 'Intr'
    WHEN SUBSTR(S_CONTACT_X.ATTRIB_37,1,4)='Prin' THEN 'News'
    WHEN SUBSTR(S_CONTACT_X.ATTRIB_37,1,4)='Gues' THEN 'Gst'
    ELSE SUBSTR(S_CONTACT_X.ATTRIB_37,1,4) END AS LeadSource,
    SUBSTR(Consultant.EMP_NUM,1,10) AS CONSULTANT,
    ProgramDirector.EMP_NUM AS ProgramDirector,
    CallTaker.EMP_NUM CallTaker,
    S_CONTACT.X_LEAD_DT AS LeadDate,
    LEAST(nvl(S_CONTACT.X_LAST_CONSULTATION_DATE,O.FirstPurchaseDate ), nvl(O.FirstPurchaseDate,S_CONTACT.X_LAST_CONSULTATION_DATE+1) ) AS FirstVisitDate, --X_LAST_CONSULTATION_DATE stores the performed date or the legacy client firstvisitdate
    GREATEST(nvl(S_CONTACT_XM.X_CONSULTATION_DT ,S_CONTACT_X.ATTRIB_29), nvl(S_CONTACT_X.ATTRIB_29, S_CONTACT_XM.X_CONSULTATION_DT-1) ) AS LastVisitDate,
    CASE WHEN S_CONTACT.X_INSTALLMENT_BALANCE > 0 THEN 'B' ELSE NULL END AS BillType,
    ct.current_client_type ClientType,
    SUBSTR(ct.saved_client_type,1,1) PreviousClientType,
    S_CONTACT.LAST_CREDIT_DT AS AppointmentDate,
    CASE WHEN a.X_DR_LETTER_STATUS IS NOT NULL THEN 'Y' ELSE 'N' END AS DoctorLetterRequired,
    NULL AS OneYearPermStabilizationDate,
    DECODE(S_PROD_INT.X_PROGRAM_CLASSIFICATION,'Premium',a.START_DT ,NULL) AS UnlimitedPermStabilizationDate,
    SUBSTR(S_CONTACT.MARITAL_STAT_CD,1,1) AS MaritalStatus,
    SUBSTR(REFERRER.FST_NAME ||' '|| REFERRER.LAST_NAME,1,34) AS ReferrerName,
    ORGEXT_REF.LOC AS ReferrerCentreID,
    S_ORG_EXT.LOC AS CentreID,
    NULL AS PaymentDateOne,
    NULL AS PaymentAmountOne,
    NULL AS PaymentDateTwo,
    NULL AS PaymentAmountTwo,
    NULL AS PaymentDateThree,
    NULL AS PaymentAmountThree,
    NULL AS PaymentDateFour,
    NULL AS PaymentAmountFour,
    NULL AS LibraryPurchased,
    nvl(S_CONTACT.X_INSTALLMENT_BALANCE,0) + nvl(S_CONTACT.X_PREPAID_BALANCE,0) AS BalanceDue, -- Changed operation from (-) prepaid to (+) prepaid since the sign was flipped in OLTP.
    NULL AS FoodNSFBalance,
    NULL AS ProductNSFBalance,
    NULL AS ProgramNSFBalance,
    a2.X_START_WEIGHT AS StartWeight,
    a2.X_CURRENT_WEIGHT AS CurrentWeight,
    a2.X_GOAL_WEIGHT AS GoalWeight,
    a3.X_HEIGHT AS Height,
    a2.X_FAXSENT_DATETIME DateGoalWeightAchieved,
    DECODE(S_PROD_INT.X_PROGRAM_CLASSIFICATION,'Premium',a.START_DT,NULL) AS DateSuccessPlusPurchased,
    CASE WHEN A2.ARCHIVE_FLG = 'N' THEN a2.START_DT ELSE NULL END AS ReturnToActiveDate,
    600 VersionNumber,
    a2.X_FAXRECV_DATETIME AS HalfWayDate,
    NULL AS LastLSCDate,
    TRUNC(S_CONTACT.LAST_UPD-UTC_OFFSET/24) AS LastUpdatedDate,
    NULL AS VitaminWaiverSigned,
    LastSupplementPurchaseDate,
    LastSupplementCodePurchased,
    LastTotalSupplementSupplyCycle,
    LastAddtlSupplPurchaseDate,
    LastAddtlSupplCodePurchased,
    LastAddtlSupplSupplyCycle,
    CASE WHEN (a.X_DIABETES_NO_MEDS_FLG='Y' OR a.X_DIABETES_ORAL_MEDS_FLG = 'Y' OR a.X_DIABETES_ON_INSULIN_FLG = 'Y') THEN 'Y' ELSE 'N' END AS DiabetesClient,
    DECODE(a.X_DIABETES_NO_MEDS_FLG,'Y','Y','N') AS DietControlled,
    a.X_DIABETES_ORAL_MEDS_FLG AS TakingOralMed,
    a.X_DIABETES_ON_INSULIN_FLG AS TakingInsulin,
    S_CONTACT.EMAIL_ADDR AS EmailId,
    NULL CTADATE,
    NULL RWLDATE,
    SUBSTR(S_ADDR_PER.ADDR_LINE_2,1,40) AS Address2
    FROM S_CONTACT,
    S_CONTACT REFERRER,
    S_CONTACT Consultant,
    S_CONTACT ProgramDirector,
    S_CONTACT CallTaker,
    S_CONTACT_X,
    (SELECT /*+ parallel full(S_CONTACT_XM) */ PAR_ROW_ID, attrib_05, MAX(X_CONSULTATION_DT) AS X_CONSULTATION_DT FROM S_CONTACT_XM
    WHERE (S_CONTACT_XM.last_upd_by < '1-14WD'
    or S_CONTACT_XM.last_upd_by > '1-14WD')
    AND S_CONTACT_XM.ATTRIB_05 IN (SELECT row_id FROM S_ORG_EXT WHERE S_ORG_EXT.ACCNT_TYPE_CD IN ('Corporate Centre','Franchise Centre')) LOC IN (SELECT centreid FROM UPL_LIVE_CENTRES WHERE LIVE = 'Y' AND BATCHID = p_batch_id)) where S_ORG_EXT.ACCNT_TYPE_CD IN ('Corporate Centre','Franchise Centre')) --
    GROUP BY PAR_ROW_ID, attrib_05) S_CONTACT_XM,
    (SELECT CONTACT_ID, ACCNT_ID,
    MAX(LastSupplementPurchaseDate) AS LastSupplementPurchaseDate,
    MAX(LastSupplementCodePurchased) AS LastSupplementCodePurchased,
    MAX(LastTotalSupplementSupplyCycle) AS LastTotalSupplementSupplyCycle,
    MAX(LastAddtlSupplPurchaseDate) AS LastAddtlSupplPurchaseDate,
    MAX(LastAddtlSupplCodePurchased) AS LastAddtlSupplCodePurchased,
    MAX(LastAddtlSupplSupplyCycle) AS LastAddtlSupplSupplyCycle,
    MIN(FirstPurchaseDate) AS FirstPurchaseDate,
    MAX(LastPurchaseDate) AS LastPurchaseDate
              FROM (
              SELECT /*+ parallel full(S_ORDER) full(S_ORDER_XM) */ S_ORDER.CONTACT_ID AS CONTACT_ID,S_ORDER.ACCNT_ID,
    NULL AS LastSupplementPurchaseDate,
    NULL AS LastSupplementCodePurchased,
    NULL AS LastTotalSupplementSupplyCycle,
    NULL AS LastAddtlSupplPurchaseDate,
    NULL AS LastAddtlSupplCodePurchased,
    NULL AS LastAddtlSupplSupplyCycle,
    (S_ORDER_XM.X_BUSINESS_DATE) FirstPurchaseDate,
    (S_ORDER_XM.X_BUSINESS_DATE) LastPurchaseDate
              FROM S_ORDER,S_ORDER_XM
              WHERE S_ORDER.ROW_ID = S_ORDER_XM.PAR_ROW_ID
              AND S_ORDER.STATUS_CD IN ('Complete', 'Submitted', 'Ready')
              AND TRUNC(S_ORDER_XM.X_BUSINESS_DATE - UTC_OFFSET/24) <= (p_business_date)
              --GROUP BY S_ORDER.CONTACT_ID
              UNION ALL
              SELECT /*+ parallel full(S_ORDER) full (S_ORDER_ITEM) */ S_ORDER.CONTACT_ID AS CONTACT_ID,S_ORDER.ACCNT_ID,
              (CASE WHEN SUBSTR(SUBSTR(S_PROD_INT.PART_NUM,1,INSTR(S_PROD_INT.PART_NUM,'-',1,1)-1),2,4) = '931' THEN S_ORDER.CREATED ELSE NULL END) AS LastSupplementPurchaseDate,
              (CASE WHEN SUBSTR(SUBSTR(S_PROD_INT.PART_NUM,1,INSTR(S_PROD_INT.PART_NUM,'-',1,1)-1),2,4) = '931' THEN 931 ELSE NULL END) AS LastSupplementCodePurchased,
              (CASE WHEN SUBSTR(SUBSTR(S_PROD_INT.PART_NUM,1,INSTR(S_PROD_INT.PART_NUM,'-',1,1)-1),2,4) = '931' THEN 7 ELSE NULL END) AS LastTotalSupplementSupplyCycle,
              (CASE WHEN SUBSTR(SUBSTR(S_PROD_INT.PART_NUM,1,INSTR(S_PROD_INT.PART_NUM,'-',1,1)-1),2,4) = '920' THEN S_ORDER.CREATED ELSE NULL END) AS LastAddtlSupplPurchaseDate,
              (CASE WHEN SUBSTR(SUBSTR(S_PROD_INT.PART_NUM,1,INSTR(S_PROD_INT.PART_NUM,'-',1,1)-1),2,4) = '920' THEN 920 ELSE NULL END) AS LastAddtlSupplCodePurchased,
              (CASE WHEN SUBSTR(SUBSTR(S_PROD_INT.PART_NUM,1,INSTR(S_PROD_INT.PART_NUM,'-',1,1)-1),2,4) = '920' THEN 28 ELSE NULL END) AS LastAddtlSupplSupplyCycle,
              NULL FirstPurchaseDate,
              NULL LastPurchaseDate
              FROM S_ORDER,S_ORDER_ITEM, S_PROD_INT
              WHERE S_ORDER_ITEM.PROD_ID = S_PROD_INT.ROW_ID
                   AND S_ORDER.ROW_ID = S_ORDER_ITEM.ORDER_ID
                   AND S_ORDER_ITEM.qty_req <> 0
                   AND s_order.created_by <> '1-14WD'
                   AND S_ORDER_ITEM.PAR_ORDER_ITEM_ID is null
                   AND (S_ORDER_ITEM.PAR_ORDER_ITEM_ID is null
                   OR EXISTS (select 1 from S_ORDER_ITEM i2,s_prod_int p
    where i2.row_id = S_ORDER_ITEM.PAR_ORDER_ITEM_ID
    and SUBSTR(SUBSTR(S_PROD_INT.PART_NUM,1,INSTR(S_PROD_INT.PART_NUM,'-',1,1)-1),2,4) IN ('931','920')
    and i2.prod_id = p.row_id
    ))
                   AND S_ORDER.status_cd in ('Complete', 'Submitted', 'Ready')
                   and SUBSTR(SUBSTR(S_PROD_INT.PART_NUM,1,INSTR(S_PROD_INT.PART_NUM,'-',1,1)-1),2,4) IN ('931','920')
         )
    GROUP BY CONTACT_ID,ACCNT_ID) O,
    S_CONTACT_TNTX,
    S_ORG_EXT,
    S_ORG_EXT ORGEXT_REF,
    S_ADDR_PER,
    S_ASSET a,
    S_PROD_INT,
    S_ASSET a2,
    S_ASSET a3,
    UPL_CLIENT_TYPES ct,
    (select /*+ parallel */ o.contact_id, o.accnt_id
    from S_ORDER o, S_ORDER_XM oxm
    where o.row_id = oxm.par_row_id
    and trunc(oxm.X_BUSINESS_DATE - (UTC_OFFSET/24)) = trunc(p_business_date)
    group by o.contact_id, o.accnt_id) oxm2
    WHERE S_CONTACT.ROW_ID = S_CONTACT_X.PAR_ROW_ID
    AND S_CONTACT_X.ROW_ID = S_CONTACT_XM.PAR_ROW_ID (+)
    AND (S_ORG_EXT.ROW_ID = S_CONTACT.PR_DEPT_OU_ID
    OR S_ORG_EXT.ROW_ID = oxm2.accnt_id
    OR S_ORG_EXT.ROW_ID = S_CONTACT_XM.attrib_05)
    AND ORGEXT_REF.ROW_ID(+) = REFERRER.PR_DEPT_OU_ID
    AND S_CONTACT.CON_ASST_PER_ID = Consultant.ROW_ID
    AND S_ORG_EXT.X_DIRECTOR_ID = ProgramDirector.ROW_ID (+)
    AND S_CONTACT.CREATED_BY = CallTaker.ROW_ID
    AND S_CONTACT.ROW_ID = a.PR_CON_ID (+)
    AND S_CONTACT.PR_PER_ADDR_ID = S_ADDR_PER.ROW_ID (+)
    AND S_CONTACT_TNTX.PAR_ROW_ID (+) = S_CONTACT.ROW_ID
    AND REFERRER.ROW_ID(+) = S_CONTACT_TNTX.REFERRED_BY_ID
    AND a.PROD_ID = S_PROD_INT.ROW_ID (+)
    AND O.CONTACT_ID (+) = S_CONTACT.ROW_ID
    AND a.STATUS_CD (+) = 'Active'
    AND a.TYPE_CD (+) ='Program'
    AND S_CONTACT.ROW_ID = a2.PR_CON_ID (+)
    AND a2.STATUS_CD (+) = 'Active'
    AND a2.TYPE_CD (+) = 'Lifecycle'
    AND a3.PR_CON_ID(+) = S_CONTACT.ROW_ID
    AND a3.STATUS_CD (+) = 'Active'
    AND a3.TYPE_CD (+) = 'HealthSheet'
    AND S_CONTACT.X_JC_ACNT_NUM = ct.CLIENT_NUMBER (+)
    --AND S_ORG_EXT.LOC NOT LIKE 'F%'
    AND S_ORG_EXT.ACCNT_TYPE_CD NOT IN 'Division'
    --AND S_ORG_EXT.Loc in (select to_char(centreid) from UPL_LIVE_CENTRES where LIVE = 'Y')
    AND (trunc(S_CONTACT.LAST_UPD - (UTC_OFFSET/24)) = trunc(p_business_date) or trunc(S_CONTACT_X.LAST_UPD - (UTC_OFFSET/24)) = trunc(p_business_date) OR (S_CONTACT_XM.X_CONSULTATION_DT = p_business_date) OR oxm2.CONTACT_ID IS NOT NULL)
    AND S_CONTACT.last_upd_by not in ('1-14WD')
    AND oxm2.CONTACT_ID (+) = o.CONTACT_ID
    AND S_ORG_EXT.LOC <> 'CW_846'
    AND (a.pr_accnt_id in (select row_id from S_ORG_EXT where S_ORG_EXT.LOC IN (Select CentreID from UPL_Live_Centres where BATCHID = p_batch_id)) or a.pr_accnt_id is null)
    AND (a2.pr_accnt_id in (select row_id from S_ORG_EXT where S_ORG_EXT.LOC IN (Select CentreID from UPL_Live_Centres where BATCHID = p_batch_id)) or a2.pr_accnt_id is null)
    AND (a3.pr_accnt_id in (select row_id from S_ORG_EXT where S_ORG_EXT.LOC IN (Select CentreID from UPL_Live_Centres where BATCHID = p_batch_id)) or a3.pr_accnt_id is null));

    rollback;

    END;
    /
    --------------------------------------------------------------------------------------------------
  • 3. Re: Cell Offload will Happen for pl/sql Block with variables
    Marc Fielding Journeyer
    Currently Being Moderated
    Thanks Pavan. I've put together a quick testcase and run it over here:

    -- Force direct path reads so as not to worry about size thresholds
    alter session set "_serial_direct_read" = always;

    -- Create 10m row table
    CREATE TABLE SOURCE_TABLE NOCOMPRESS AS
    SELECT 'Sample row data' AS COL1, rownum AS COL2 FROM DUAL
    CONNECT BY level <= 10000000;

    CREATE TABLE DEST_TABLE NOCOMPRESS AS
    SELECT * FROM SOURCE_TABLE WHERE ROWNUM = 0;

    select bytes/1024/1024 as megs from user_segments where segment_name = 'SOURCE_TABLE';
    -- 232

    -- Get starting statistics
    select name, value from v$statname n, v$mystat m
    where n.statistic# = m.statistic#
    and name in ('physical read total bytes',
    'cell physical IO interconnect bytes returned by smart scan');
    -- physical read total bytes     262152192
    -- cell physical IO interconnect bytes returned by smart scan     0

    insert /*+append*/ into dest_table select * from source_table;

    select name, value from v$statname n, v$mystat m
    where n.statistic# = m.statistic#
    and name in ('physical read total bytes',
    'cell physical IO interconnect bytes returned by smart scan');

    --physical read total bytes     609140736
    --cell physical IO interconnect bytes returned by smart scan     276094224

    truncate table dest_table;

    -- Now run in PL/SQL
    begin
    insert /*+append*/ into dest_table select * from source_table;
    end;
    /

    select name, value from v$statname n, v$mystat m
    where n.statistic# = m.statistic#
    and name in ('physical read total bytes',
    'cell physical IO interconnect bytes returned by smart scan');

    --physical read total bytes     913760256
    --cell physical IO interconnect bytes returned by smart scan     552188144


    So we can see that the rows were returned by smart scan, both from SQL and from PL/SQL.

    I see your PL/SQL sets some parameters at the top. Are you sure you're using the exact same parameters as when running the query individually? Do you wnat to try a test like mine where you take a literal query, wrap it it in begin/end, and compare results?

    Cheers,

    Marc
  • 4. Re: Cell Offload will Happen for pl/sql Block with variables
    895567 Newbie
    Currently Being Moderated
    Hello Marc,

    Thanks for Quick response.

    couple of things i noticed in your post. you have created source_table and dest_table.
    i believe source_table don't have any indexes as well as your are selecting select * from source_table.
    in that case, i believe it will go for FTS which interns cause cell offload.

    in my case i have lots of indexes.

    i tried to change the parameter you mentioned "alter session set "_serial_direct_read" = true" at session level.
    i will try alter session set "_serial_direct_read" = always; now and test it again.

    please correct me if i am wrong.
  • 5. Re: Cell Offload will Happen for pl/sql Block with variables
    Marc Fielding Journeyer
    Currently Being Moderated
    Hi Pavan,

    I was assuming that, if you determined you were getting offload happening when you ran the query in TOAD, that it would be using an offloadable plan (ie full scan of some type). Regardless, it should be pretty easy to determine: look at the explain plan, and see if it's doing full scans. If not, you're entirely right: the query will not be offloaded.

    The reason I set serialdirect_read is to force the database to do direct path reads (even without parallel query and with relatively small tables), which in turn can be offloaded by the storage servers. If you see changes in your PL/SQL execution (and not your TOAD direct execution), this could point to environment differences: parallel query settings perhaps?

    Marc

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points