Results 1 to 6 of 6
  1. #1
    Join Date
    Jun 2015
    Posts
    3

    Unanswered: my query taking more than 2 hours and temp space taking 100gb

    my query is taking 2hours time and more than 100 gb temp space, but the same quey with diffrent date range (2013)just takes 5 seconds, i have done tables moved to diffrent tablespace, kindly help me to resolve


    SELECT

    ca.local_case_number as "Case"

    , ca.contact_type AS "Contact Type"

    , ca.rb_affiliate_ref_number as " RB Affiliate Number"

    , decode(ca.source,1,'Spontaneous',2,'Report from Study',3,'Other',4,'Not Available') AS "Source"
    , decode_trace(ca.country, 'AES_COUNTRY') AS "Country"
    , duration_to_string(pt.age_at_onset_tiq, pt.age_at_onset_tip) AS "Age at Onset"
    , decode(pt.patient_age_group, 1, 'Neonate', 2, 'Infant', 3, 'Child', 4, 'Adolescent', 5, 'Adult', 6, 'Elderly', 'Null') "Patient Age Group"
    , decode(pt.sex, 1,'M', 2, 'F','UNK') as "Sex"

    , decode(se.serious, 1, 'Serious', 2, 'Non-Serious', 'UNK') as "Case Seriousness"

    , decode_trace(se.outcome, 'AES_OUTCOME') AS "Case Outcome"
    , ed.drug_reported AS "Drug Name"
    , ed.key_ingredient AS "Key Ingredient"
    , nvl(to_char(ca.last_received_tpq,'YYYY'), to_char(ca.first_received_tpq,'YYYY')) AS "Year"
    , ca.first_received_tpq AS "FirstRecDate"
    , decode(ca.medically_confirm,1,'Yes',2,'No','UNK') AS "MedConf"
    , decode(ps.rep_type, 5, 'Consumer' , 4, 'Lawyer', 3, 'Other', 2, 'Pharmacist', 1, 'Physician') AS "RepType"
    , se.comp_causal as "Company Causality"
    , dcael1.term AS "AE SOC"
    , dcael3.term AS "AE HLT"
    , dcael4.term AS "AE PT"
    , ae.event_term AS "AE Verbatim"
    , n.narrative "Narrative"
    FROM
    /* Last approved version in the time period */
    (SELECT c.case_id as case_id, MAX(l.tx_id) as tx_id
    FROM tr_case_audit c
    JOIN v_approved_labels l /* excludes suppressed cases */
    ON c.case_id = l.case_id
    AND c.tx_id <= l.tx_id AND l.tx_id < c.tx_id_next
    where
    (c.first_received_tpq) >= (to_date('01-APR-2014','DD-MON-YYYY'))
    AND (c.first_received_tpq) < (TO_DATE('30-JUN-2014','DD-MON-YYYY')+1)
    GROUP BY c.case_id
    ) ACV
    JOIN tr_case_audit ca
    ON ca.case_id = acv.case_id
    AND ca.tx_id <= acv.tx_id AND acv.tx_id < ca.tx_id_next
    JOIN pf_label pfl
    ON pfl.case_id = acv.case_id
    AND pfl.tx_id = acv.tx_id
    JOIN tr_exposed_drug_audit ed
    ON ed.case_id = acv.case_id
    AND ed.tx_id <= acv.tx_id AND acv.tx_id < ed.tx_id_next
    AND ed.is_suspect = '1'
    LEFT OUTER JOIN TR_DOSE_AUDIT ds
    ON ds.case_id = acv.case_id
    and ds.exposed_drug_id = ed.exposed_drug_id
    AND ds.tx_id <= acv.tx_id AND acv.tx_id < ds.tx_id_next
    LEFT OUTER JOIN tr_primary_source_audit ps
    ON ps.case_id = acv.case_id
    AND ps.tx_id <= acv.tx_id AND acv.tx_id < ps.tx_id_next
    AND ps.importance = 1
    LEFT OUTER JOIN tr_patient_audit pt
    ON pt.case_id = acv.case_id
    AND pt.tx_id <= acv.tx_id AND acv.tx_id < pt.tx_id_next
    LEFT OUTER JOIN tr_seriousness_audit se
    ON se.case_id = acv.case_id
    AND se.tx_id <= acv.tx_id AND acv.tx_id < se.tx_id_next
    LEFT OUTER JOIN tr_adverse_event_audit ae
    ON ae.case_id = acv.case_id
    AND ae.tx_id <= acv.tx_id AND acv.tx_id < ae.tx_id_next
    LEFT OUTER JOIN tr_narrative_audit n
    ON n.case_id = acv.case_id
    AND n.tx_id <= acv.tx_id AND acv.tx_id < n.tx_id_next
    /* Adverse Event MedDRA codes */
    LEFT OUTER JOIN tr_datacode_audit dcae
    ON dcae.source_table = 'TR_ADVERSE_EVENT'
    AND dcae.pk_column = 'ADVERSE_EVENT_ID'
    AND dcae.case_id = acv.case_id
    AND dcae.tx_id <= acv.tx_id AND acv.tx_id < dcae.tx_id_next
    AND dcae.pk_column_value = ae.adverse_event_id
    AND dcae.is_primary_dict = '1'
    LEFT OUTER JOIN tr_datacode_level_audit dcael1
    ON dcael1.datacode_id = dcae.datacode_id
    AND dcael1.case_id = acv.case_id
    AND dcael1.tx_id <= acv.tx_id AND acv.tx_id < dcael1.tx_id_next
    AND dcael1.dict_level = 1
    LEFT OUTER JOIN tr_datacode_level_audit dcael2
    ON dcael2.datacode_id = dcae.datacode_id
    AND dcael2.case_id = acv.case_id
    AND dcael2.tx_id <= acv.tx_id AND acv.tx_id < dcael2.tx_id_next
    AND dcael2.dict_level = 2
    LEFT OUTER JOIN tr_datacode_level_audit dcael3
    ON dcael3.datacode_id = dcae.datacode_id
    AND dcael3.case_id = acv.case_id
    AND dcael3.tx_id <= acv.tx_id AND acv.tx_id < dcael3.tx_id_next
    AND dcael3.dict_level = 3
    LEFT OUTER JOIN tr_datacode_level_audit dcael4
    ON dcael4.datacode_id = dcae.datacode_id
    AND dcael4.case_id = acv.case_id
    AND dcael4.tx_id <= acv.tx_id AND acv.tx_id < dcael4.tx_id_next
    AND dcael4.dict_level = 4
    LEFT OUTER JOIN tr_datacode_level_audit dcael5
    ON dcael5.datacode_id = dcae.datacode_id
    AND dcael5.case_id = acv.case_id
    AND dcael5.tx_id <= acv.tx_id AND acv.tx_id < dcael5.tx_id_next
    AND dcael5.dict_level = 5
    where (
    UPPER (ca.country) = 'GB')

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    post EXPLAIN PLAN for both SQL.

    How many rows returned for both SQL?
    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
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    have statistics been gathered lately?
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  4. #4
    Join Date
    Jun 2015
    Posts
    3
    Yes i have gathered statistic frequently, also tried with move tablespace and gather statistics. but still same

  5. #5
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713
    What does the explain plan look like?
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  6. #6
    Join Date
    Jun 2015
    Posts
    3
    Quote Originally Posted by LKBrwn_DBA View Post
    What does the explain plan look like?

    pls find the explain plan

    Plan
    SELECT STATEMENT ALL_ROWS Cost: 3,689 Bytes: 4,420 Cardinality: 1
    60 NESTED LOOPS OUTER Cost: 3,689 Bytes: 4,420 Cardinality: 1
    57 NESTED LOOPS OUTER Cost: 3,685 Bytes: 4,363 Cardinality: 1
    54 NESTED LOOPS OUTER Cost: 3,681 Bytes: 4,306 Cardinality: 1
    51 NESTED LOOPS OUTER Cost: 3,677 Bytes: 4,249 Cardinality: 1
    48 NESTED LOOPS OUTER Cost: 3,673 Bytes: 4,219 Cardinality: 1
    45 VIEW SYS. Cost: 3,670 Bytes: 4,189 Cardinality: 1
    44 NESTED LOOPS OUTER Cost: 3,670 Bytes: 4,240 Cardinality: 1
    41 VIEW SYS. Cost: 3,667 Bytes: 4,180 Cardinality: 1
    40 NESTED LOOPS OUTER Cost: 3,667 Bytes: 2,873 Cardinality: 1
    37 NESTED LOOPS OUTER Cost: 3,664 Bytes: 2,842 Cardinality: 1
    34 NESTED LOOPS OUTER Cost: 3,661 Bytes: 2,812 Cardinality: 1
    31 NESTED LOOPS OUTER Cost: 3,658 Bytes: 2,767 Cardinality: 1
    28 NESTED LOOPS OUTER Cost: 3,655 Bytes: 1,546 Cardinality: 1
    25 NESTED LOOPS OUTER Cost: 3,653 Bytes: 1,519 Cardinality: 1
    23 VIEW SYS. Cost: 3,651 Bytes: 1,486 Cardinality: 1
    22 FILTER
    21 HASH GROUP BY Cost: 3,651 Bytes: 610 Cardinality: 1
    20 NESTED LOOPS Cost: 3,650 Bytes: 11,590 Cardinality: 19
    17 NESTED LOOPS Cost: 3,626 Bytes: 1,030 Cardinality: 2
    14 NESTED LOOPS Cost: 3,623 Bytes: 385 Cardinality: 1
    11 NESTED LOOPS Cost: 3,618 Bytes: 177 Cardinality: 1
    8 NESTED LOOPS Cost: 3,617 Bytes: 157 Cardinality: 1
    5 NESTED LOOPS Cost: 1,412 Bytes: 87,058 Cardinality: 1,102
    2 TABLE ACCESS BY INDEX ROWID TABLE ETRACEPRD.TR_CASE_AUDIT Cost: 69 Bytes: 7,493 Cardinality: 127
    1 INDEX RANGE SCAN INDEX ETRACEPRD.TR_CASE_AUDIT_IDX18 Cost: 3 Cardinality: 127
    4 TABLE ACCESS BY INDEX ROWID TABLE ETRACEPRD.TR_APPROVAL_AUDIT Cost: 11 Bytes: 180 Cardinality: 9
    3 INDEX RANGE SCAN INDEX ETRACEPRD.TR_APPROVAL_AUDIT_IDX2 Cost: 2 Cardinality: 9
    7 TABLE ACCESS BY INDEX ROWID TABLE ETRACEPRD.PF_LABEL Cost: 2 Bytes: 78 Cardinality: 1
    6 INDEX UNIQUE SCAN INDEX (UNIQUE) ETRACEPRD.PF_CASEVERSION_PK Cost: 1 Cardinality: 1
    10 TABLE ACCESS BY INDEX ROWID TABLE ETRACEPRD.TR_CASE_STATE Cost: 1 Bytes: 20 Cardinality: 1
    9 INDEX UNIQUE SCAN INDEX (UNIQUE) ETRACEPRD.TR_CASE_STATE_IDX1 Cost: 0 Cardinality: 1
    13 TABLE ACCESS BY INDEX ROWID TABLE ETRACEPRD.TR_CASE_AUDIT Cost: 5 Bytes: 208 Cardinality: 1
    12 INDEX RANGE SCAN INDEX ETRACEPRD.TR_CASE_AUDIT_IDX1 Cost: 2 Cardinality: 3
    16 TABLE ACCESS BY INDEX ROWID TABLE ETRACEPRD.TR_EXPOSED_DRUG_AUDIT Cost: 3 Bytes: 130 Cardinality: 1
    15 INDEX RANGE SCAN INDEX ETRACEPRD.TR_EXPOSED_DRUG_AUDIT_IDX5 Cost: 2 Cardinality: 2
    19 TABLE ACCESS BY INDEX ROWID TABLE ETRACEPRD.PF_LABEL Cost: 12 Bytes: 950 Cardinality: 10
    18 INDEX RANGE SCAN INDEX ETRACEPRD.IDX_PF_LABEL_CASE_ID Cost: 2 Cardinality: 10
    24 INDEX RANGE SCAN INDEX ETRACEPRD.TR_DOSE_AUDIT_IDX15 Cost: 2 Bytes: 33 Cardinality: 1
    27 TABLE ACCESS BY INDEX ROWID TABLE ETRACEPRD.TR_PRIMARY_SOURCE_AUDIT Cost: 2 Bytes: 27 Cardinality: 1
    26 INDEX RANGE SCAN INDEX ETRACEPRD.TR_PRIMARY_SOURCE_AUDIT_IDX2 Cost: 1 Cardinality: 1
    30 TABLE ACCESS BY INDEX ROWID TABLE ETRACEPRD.TR_NARRATIVE_AUDIT Cost: 3 Bytes: 1,221 Cardinality: 1
    29 INDEX RANGE SCAN INDEX ETRACEPRD.TR_NARRATIVE_AUDIT_IDX15 Cost: 2 Cardinality: 1
    33 TABLE ACCESS BY INDEX ROWID TABLE ETRACEPRD.TR_ADVERSE_EVENT_AUDIT Cost: 3 Bytes: 45 Cardinality: 1
    32 INDEX RANGE SCAN INDEX ETRACEPRD.TR_ADVERSE_EVENT_AUDIT_IDX15 Cost: 2 Cardinality: 1
    36 TABLE ACCESS BY INDEX ROWID TABLE ETRACEPRD.TR_SERIOUSNESS_AUDIT Cost: 3 Bytes: 30 Cardinality: 1
    35 INDEX RANGE SCAN INDEX ETRACEPRD.TR_SERIOUSNESS_AUDIT_IDX15 Cost: 2 Cardinality: 1
    39 TABLE ACCESS BY INDEX ROWID TABLE ETRACEPRD.TR_PATIENT_AUDIT Cost: 3 Bytes: 31 Cardinality: 1
    38 INDEX RANGE SCAN INDEX ETRACEPRD.TR_PATIENT_AUDIT_IDX2 Cost: 2 Cardinality: 1
    43 TABLE ACCESS BY INDEX ROWID TABLE ETRACEPRD.TR_DATACODE_AUDIT Cost: 3 Bytes: 60 Cardinality: 1
    42 INDEX RANGE SCAN INDEX ETRACEPRD.TR_DATACODE_AUDIT_IDX15 Cost: 2 Cardinality: 1
    47 TABLE ACCESS BY INDEX ROWID TABLE ETRACEPRD.TR_DATACODE_LEVEL_AUDIT Cost: 4 Bytes: 30 Cardinality: 1
    46 INDEX RANGE SCAN INDEX ETRACEPRD.TR_DATACODE_LEVEL_AUDIT_IDX15 Cost: 3 Cardinality: 1
    50 TABLE ACCESS BY INDEX ROWID TABLE ETRACEPRD.TR_DATACODE_LEVEL_AUDIT Cost: 4 Bytes: 30 Cardinality: 1
    49 INDEX RANGE SCAN INDEX ETRACEPRD.TR_DATACODE_LEVEL_AUDIT_IDX15 Cost: 3 Cardinality: 1
    53 TABLE ACCESS BY INDEX ROWID TABLE ETRACEPRD.TR_DATACODE_LEVEL_AUDIT Cost: 4 Bytes: 57 Cardinality: 1
    52 INDEX RANGE SCAN INDEX ETRACEPRD.TR_DATACODE_LEVEL_AUDIT_IDX15 Cost: 3 Cardinality: 1
    56 TABLE ACCESS BY INDEX ROWID TABLE ETRACEPRD.TR_DATACODE_LEVEL_AUDIT Cost: 4 Bytes: 57 Cardinality: 1
    55 INDEX RANGE SCAN INDEX ETRACEPRD.TR_DATACODE_LEVEL_AUDIT_IDX15 Cost: 3 Cardinality: 1
    59 TABLE ACCESS BY INDEX ROWID TABLE ETRACEPRD.TR_DATACODE_LEVEL_AUDIT Cost: 4 Bytes: 57 Cardinality: 1
    58 INDEX RANGE SCAN INDEX ETRACEPRD.TR_DATACODE_LEVEL_AUDIT_IDX15 Cost: 3 Cardinality: 1

Posting Permissions

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