1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Performance issue on query

Discussion in 'SQL PL/SQL' started by Pinky, Aug 22, 2016.

  1. Pinky

    Pinky Newly Initiated

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    30
    Location:
    Bangalore
    Hi All,

    Currently I am using Oracle 11.2 version Windows env in my current project. When I am running the below query it is taking 3 hours to complete. This query consists of 2 select statement and connecting 2 database. If I am running individual select statement it is taking only 1 min.

    select study,site,patient,visit,crf_count
    from
    (select a.study, b.site,b.patient,b.clin_plan_eve_name,visit,
    count(b.received_dci_id) crf_count
    from table_a@dblink a, table_b@db_link b
    where a.clinical_study_id=b.clinical_study_id
    and b.status_code in ('PASS1 Complete','PASS2 Complete','PASS3 Complete')
    and b.doc_number not like 'D%'
    and b.end_ts = TO_DATE(300000,'J')
    group by a.study, b.site, b.patient, b.clin_plan_eve_name
    ) MEDI,
    (select prot.ptcl_num, site.site_num, sub.x_patient_id,ptemp.name,
    nvl(ext.num_page,0) num_page, svpa_ow_id
    FROM site,
    prot,
    sub,
    ptemp,
    pact,
    svpa,
    ext
    WHERE site.cl_ptcl_id = prot.row_id
    and sub.PTCL_SITE_ID = site.row_id
    and prot.region_cd is null
    AND SUB.ROW_ID = svpa.CL_SUBJ_ID
    and svpa.TMPL_PLANITEM_ID = ptemp.row_id
    AND svpa.cl_ptcl_st_id = site.row_id
    AND svpa.pymnt_flg = 'Y'
    and svpa.CL_SUBJ_ID = sub.row_id
    and svpa.PAR_EVT_ID = pact.ROW_ID
    and svpa.CL_PTCL_ST_ID = site.row_id
    AND ext.par_row_id(+) = svpa.PAR_EVT_ID
    and Pact.TMPL_PLANITEM_ID = ptemp.row_id
    ) COM
    where medi.study = REPLACE (COM.ptcl_num, '-', '_')
    AND medi.site = lpad(COM.site_num,4,'0')
    AND COM.X_BMS_PATIENT_ID = medi.patient
    AND COM.NAME = medi.VISIT
    and medi.crf_count<>COM.NUM_PAGE
    )

    Could you please help me on above query
     
  2. Silambarasan K

    Silambarasan K Active Member

    Messages:
    6
    Likes Received:
    3
    Trophy Points:
    85
    Location:
    Chennai
    Hi ,
    you have connected two different database . if you connect two database it will take more time to run even if the query is less cost also. so you need to create two different temp tables for two select statement and complete all function in select statement instead of where clause (replace, Lpad ) .. if you dont have privilege to create table then use with class statement .


    method 1 :
    -- first select statment creattion :
    create table MEDI
    as
    select a.study, b.site,b.patient,b.clin_plan_eve_name,visit,
    count(b.received_dci_id) crf_count
    from table_a@dblink a, table_b@db_link b
    where a.clinical_study_id=b.clinical_study_id
    and b.status_code in ('PASS1 Complete','PASS2 Complete','PASS3 Complete')
    and b.doc_number not like 'D%'
    and b.end_ts = TO_DATE(300000,'J')
    group by a.study, b.site, b.patient, b.clin_plan_eve_name ;

    ------- second select statement creation with lpad and replace functions :
    create table com as
    select replace(prot.ptcl_num,'-','_') as ptcl_num ,
    lpad(site.site_num,4,0) as site_num,
    sub.x_patient_id,
    ptemp.name,
    nvl(ext.num_page,0) num_page,
    svpa_ow_id
    FROM site,
    prot,
    sub,
    ptemp,
    pact,
    svpa,
    ext
    WHERE site.cl_ptcl_id = prot.row_id
    and sub.PTCL_SITE_ID = site.row_id
    and prot.region_cd is null
    AND SUB.ROW_ID = svpa.CL_SUBJ_ID
    and svpa.TMPL_PLANITEM_ID = ptemp.row_id
    AND svpa.cl_ptcl_st_id = site.row_id
    AND svpa.pymnt_flg = 'Y'
    and svpa.CL_SUBJ_ID = sub.row_id
    and svpa.PAR_EVT_ID = pact.ROW_ID
    and svpa.CL_PTCL_ST_ID = site.row_id
    AND ext.par_row_id(+) = svpa.PAR_EVT_ID
    and Pact.TMPL_PLANITEM_ID = ptemp.row_id ;

    ----
    select study,site,patient,visit,crf_count
    from medi,
    com
    where medi.study = REPLACE (COM.ptcl_num, '-', '_')
    AND medi.site = lpad(COM.site_num,4,'0')
    AND COM.X_BMS_PATIENT_ID = medi.patient
    AND COM.NAME = medi.VISIT
    and medi.crf_count<>COM.NUM_PAGE ;


    step 2 :

    -- first select statment creattion :
    with MEDI
    as
    (
    select a.study, b.site,b.patient,b.clin_plan_eve_name,visit,
    count(b.received_dci_id) crf_count
    from table_a@dblink a, table_b@db_link b
    where a.clinical_study_id=b.clinical_study_id
    and b.status_code in ('PASS1 Complete','PASS2 Complete','PASS3 Complete')
    and b.doc_number not like 'D%'
    and b.end_ts = TO_DATE(300000,'J')
    group by a.study, b.site, b.patient, b.clin_plan_eve_name
    ),
    com as
    (
    select replace(prot.ptcl_num,'-','_') as ptcl_num ,
    lpad(site.site_num,4,0) as site_num,
    sub.x_patient_id,
    ptemp.name,
    nvl(ext.num_page,0) num_page,
    svpa_ow_id
    FROM site,
    prot,
    sub,
    ptemp,
    pact,
    svpa,
    ext
    WHERE site.cl_ptcl_id = prot.row_id
    and sub.PTCL_SITE_ID = site.row_id
    and prot.region_cd is null
    AND SUB.ROW_ID = svpa.CL_SUBJ_ID
    and svpa.TMPL_PLANITEM_ID = ptemp.row_id
    AND svpa.cl_ptcl_st_id = site.row_id
    AND svpa.pymnt_flg = 'Y'
    and svpa.CL_SUBJ_ID = sub.row_id
    and svpa.PAR_EVT_ID = pact.ROW_ID
    and svpa.CL_PTCL_ST_ID = site.row_id
    AND ext.par_row_id(+) = svpa.PAR_EVT_ID
    and Pact.TMPL_PLANITEM_ID = ptemp.row_id
    )
    select study,site,patient,visit,crf_count
    from medi,
    com
    where medi.study = REPLACE (COM.ptcl_num, '-', '_')
    AND medi.site = lpad(COM.site_num,4,'0')
    AND COM.X_BMS_PATIENT_ID = medi.patient
    AND COM.NAME = medi.VISIT
    and medi.crf_count<>COM.NUM_PAGE ;

    it will reduce the time .. if you go on your query that will retrieve the data from other DB and it should come to your DB buffer for joining with your DB tables . so it will take more time .

    i just gave these from my working experience so if it is not working means apologies my self ..,

    Thank You,
    Silambarasan K
     
    Pinky and Sadik like this.
  3. Pinky

    Pinky Newly Initiated

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    30
    Location:
    Bangalore
    Thank you Silambarasan. I tried 2nd method using With clause and it is taking 2hours. May be the query is returning million records so it is taking 2 hours which is still more. Is there any other way we can reduce time.

    Regards
    Pinky
     
  4. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    Hi.
    I believe that it is possible to reduce response time.
    It is necessary to pay attention on order of join of tables : site,
    prot,
    sub,
    ptemp,
    pact,
    svpa,
    ext
    in respect of a query .
    You can provide the plan of a request here?
     
  5. Pinky

    Pinky Newly Initiated

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    30
    Location:
    Bangalore
    Hi Sergey,

    Thank you for reply. Even I tried to pay attention on order of join of tables. I can tell at this moment query execution improved a lot. Now it is taking 30mins for 1 million record. Regarding your opinion on explain plan, Unfortunately I am not able to generate explain plan as i have only read only access in production env and Admins are not coperating to provide explain plan. is there any way we can change doc_num not like D%. May be it is taking time also.

    Regards
    Rajesh