Results 1 to 6 of 6

Thread: Query tuning

  1. #1
    Join Date
    Jul 2013
    Posts
    2

    Unanswered: Query tuning

    SELECT A.claim_type_cde,
    A.diag_gid,
    A.ptnt_age_hip_nbr,
    A.ptnt_gndr_cde,
    COUNT(DISTINCT A.prc_rel_gid) prctr_cnt,
    COUNT(DISTINCT A.ptnt_gid) ptnt_cnt,
    COUNT(DISTINCT A.claim_gid) claim_cnt
    FROM p_unprj_diag_ptnt_prctr A
    WHERE A.mktf_prtn_key BETWEEN prd.agg_strt_key AND prd.agg_end_key
    GROUP BY A.diag_gid,
    A.ptnt_age_hip_nbr,
    A.ptnt_gndr_cde,
    ROLLUP(A.claim_type_cde);

    This query is used in insert statement of 9 procedures.
    All the procedures are using group by clause with different select columns but all the selects are from the same table.
    Each procedure will run in different sessions.
    Table contains huge records, so each procedure taking min 8 hrs to execute.
    I want to increase the performance by using only one select for all the procedures.
    Anybody can give idea to use some other sql statement instead of this to improve performance..
    Thanks in advance

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Since NOBODY can optimize SQL just by looking at it, we need a few more details.
    Please refer to URL above & be sure to provide the details requested:
    1) DDL for all tables & indexes
    2) EXPLAIN PLAN
    3) output from SQL_TRACE & tkprof
    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
    Jul 2013
    Posts
    2
    DDL of the Table:

    CREATE TABLE MKTFOCUS.P_UNPRJ_DIAG_PTNT_PRCTR
    (
    MKTF_PRTN_KEY NUMBER(6) NOT NULL,
    DIAG_GID NUMBER(18),
    DIAG_SBGRP_GID NUMBER(18),
    DIAG_CTGRY_GID NUMBER(18),
    CLAIM_TYPE_CDE CHAR(1 BYTE),
    PTNT_GID NUMBER(18),
    PTNT_GNDR_CDE VARCHAR2(1 BYTE),
    PTNT_AGE_HIP_NBR NUMBER,
    PRCTR_GID NUMBER(18),
    PRC_REL_GID NUMBER(18),
    SPCLT_CODE VARCHAR2(10 BYTE),
    CLAIM_GID NUMBER(18) NOT NULL
    )

    we do not have indexes since those are warehouse tables.

    Inserts from the procedures:
    1) Insert Query for 1st procedure

    INSERT /*+ APPEND */ INTO t_diagnosis_agg_test(rptg_mth_end_dte,
    agg_tme_prd_id,
    claim_cnt_typ_cde,
    rx_rcpnt_ind,
    diag_gid,
    prctr_cnt,
    ptnt_cnt,
    claim_cnt,
    lst_updt_id,
    lst_updt_tstmp)
    SELECT to_date('06302012','MMDDYYYY'),
    '2012',
    CASE WHEN agg.claim_cnt_typ_cde IS NULL THEN '4'
    ELSE agg.claim_cnt_typ_cde
    END claim_cnt_typ_cde,
    'T' rx_rcpnt_ind,
    agg.diag_gid,
    agg.prctr_cnt,
    agg.ptnt_cnt,
    agg.claim_cnt,
    USER,
    CURRENT_TIMESTAMP
    FROM (
    SELECT diag.claim_type_cde claim_cnt_typ_cde,
    diag.diag_gid,
    COUNT(DISTINCT diag.prc_rel_gid) prctr_cnt,
    COUNT(DISTINCT diag.ptnt_gid) ptnt_cnt,
    COUNT(DISTINCT diag.claim_gid) claim_cnt
    FROM mktfocus.p_unprj_diag_ptnt_prctr diag
    WHERE diag.mktf_prtn_key between 201201 and 201206
    GROUP BY diag.diag_gid, ROLLUP(diag.claim_type_cde)) agg;

    commit;
    --48832 rows created.

    --Elapsed: 00:49:57.48
    -----------------------------------------------------------------------------------------------------------------------------------
    --| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
    -----------------------------------------------------------------------------------------------------------------------------------
    --| 0 | INSERT STATEMENT | | 24885 | 1336K| | 2819K (2)| 00:03:51 | | |
    --| 1 | LOAD AS SELECT | T_DIAGNOSIS_AGG_TEST | | | | | | | |
    --| 2 | VIEW | | 24885 | 1336K| | 2819K (2)| 00:03:51 | | |
    --| 3 | SORT GROUP BY ROLLUP | | 24885 | 874K| 53G| 2819K (2)| 00:03:51 | | |
    --| 4 | PARTITION RANGE ITERATOR | | 1101M| 36G| | 352K (4)| 00:00:29 | 22 | 27 |
    --|* 5 | TABLE ACCESS STORAGE FULL| P_UNPRJ_DIAG_PTNT_PRCTR | 1101M| 36G| | 352K (4)| 00:00:29 | 22 | 27 |
    -----------------------------------------------------------------------------------------------------------------------------------

    2) Insert Query for 2nd procedure


    INSERT /*+ APPEND */
    INTO t_diagnosis_ptnt_agg (rptg_mth_end_dte,
    agg_tme_prd_id,
    claim_cnt_typ_cde,
    rx_rcpnt_ind,
    diag_gid,
    ptnt_age_hip_nbr,
    ptnt_gndr_cde,
    prctr_cnt,
    ptnt_cnt,
    claim_cnt,
    lst_updt_id,
    lst_updt_tstmp)
    SELECT to_date('06302012','MMDDYYYY'),
    '2012',
    CASE
    WHEN agg.claim_cnt_typ_cde IS NULL THEN '4'
    ELSE agg.claim_cnt_typ_cde
    END
    claim_cnt_typ_cde,
    'T' rx_rcpnt_ind,
    agg.diag_gid,
    agg.ptnt_age_hip_nbr,
    agg.ptnt_gndr_cde,
    agg.prctr_cnt,
    agg.ptnt_cnt,
    agg.claim_cnt,
    USER,
    CURRENT_TIMESTAMP
    FROM ( SELECT diag.claim_type_cde claim_cnt_typ_cde,
    diag.diag_gid,
    diag.ptnt_age_hip_nbr,
    diag.ptnt_gndr_cde,
    COUNT (DISTINCT diag.prc_rel_gid) prctr_cnt,
    COUNT (DISTINCT diag.ptnt_gid) ptnt_cnt,
    COUNT (DISTINCT diag.claim_gid) claim_cnt
    FROM mktfocus.p_unprj_diag_ptnt_prctr diag
    WHERE diag.mktf_prtn_key between 201201 and 201206
    GROUP BY diag.diag_gid,
    diag.ptnt_age_hip_nbr,
    diag.ptnt_gndr_cde,
    ROLLUP (diag.claim_type_cde)) agg;
    commit;
    --4291679 rows created.

    --Elapsed: 01:02:40.76
    -----------------------------------------------------------------------------------------------------------------------------------
    --| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
    -----------------------------------------------------------------------------------------------------------------------------------
    --| 0 | INSERT STATEMENT | | 2015K| 134M| | 3163K (2)| 00:04:19 | | |
    --| 1 | LOAD AS SELECT | T_DIAGNOSIS_PTNT_AGG | | | | | | | |
    --| 2 | VIEW | | 2015K| 134M| | 3163K (2)| 00:04:19 | | |
    --| 3 | SORT GROUP BY ROLLUP | | 2015K| 80M| 65G| 3163K (2)| 00:04:19 | | |
    --| 4 | PARTITION RANGE ITERATOR | | 1101M| 43G| | 352K (4)| 00:00:29 | 22 | 27 |
    --|* 5 | TABLE ACCESS STORAGE FULL| P_UNPRJ_DIAG_PTNT_PRCTR | 1101M| 43G| | 352K (4)| 00:00:29 | 22 | 27 |
    -----------------------------------------------------------------------------------------------------------------------------------

    The following are the run time for these procedures for last month production data -


    3) Insert Query for 3rd procedure –

    INSERT /*+ APPEND */ INTO t_diagnosis_category_agg(rptg_mth_end_dte,
    agg_tme_prd_id,
    claim_cnt_typ_cde,
    rx_rcpnt_ind,
    diag_ctgry_gid,
    prctr_cnt,
    ptnt_cnt,
    claim_cnt,
    lst_updt_id,
    lst_updt_tstmp)
    SELECT to_date('06302012','MMDDYYYY'),
    '2012',
    CASE WHEN agg.claim_cnt_typ_cde IS NULL THEN '4' -- ROLLUP function creates summary rows with NULL
    ELSE agg.claim_cnt_typ_cde
    END claim_cnt_typ_cde,
    'T' rx_rcpnt_ind,
    agg.diag_ctgry_gid,
    agg.prctr_cnt,
    agg.ptnt_cnt,
    agg.claim_cnt,
    USER,
    CURRENT_TIMESTAMP
    FROM (
    SELECT diag.claim_type_cde claim_cnt_typ_cde,
    diag.diag_ctgry_gid,
    COUNT(DISTINCT diag.prc_rel_gid) prctr_cnt,
    COUNT(DISTINCT diag.ptnt_gid) ptnt_cnt,
    COUNT(DISTINCT diag.claim_gid) claim_cnt
    FROM mktfocus.p_unprj_diag_ptnt_prctr diag
    WHERE diag.mktf_prtn_key between 201201 and 201206
    GROUP BY diag.diag_ctgry_gid,
    ROLLUP(diag.claim_type_cde)) agg;
    --849 rows created.

    --Elapsed: 01:04:37.44

    commit;

    ----------------------------------------------------------------------------------------------------------------------------
    --| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
    ----------------------------------------------------------------------------------------------------------------------------
    --| 0 | INSERT STATEMENT | | 401 | 22055 | 388K (13)| 00:00:32 | | |
    --| 1 | LOAD AS SELECT | T_DIAGNOSIS_CATEGORY_AGG | | | | | | |
    --| 2 | VIEW | | 401 | 22055 | 388K (13)| 00:00:32 | | |
    --| 3 | SORT GROUP BY ROLLUP | | 401 | 14035 | 388K (13)| 00:00:32 | | |
    --| 4 | PARTITION RANGE ITERATOR | | 1101M| 35G| 352K (4)| 00:00:29 | 22 | 27 |
    --|* 5 | TABLE ACCESS STORAGE FULL| P_UNPRJ_DIAG_PTNT_PRCTR | 1101M| 35G| 352K (4)| 00:00:29 | 22 | 27 |
    ----------------------------------------------------------------------------------------------------------------------------


    Cannot able to provide the trace file result.

  4. #4
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Please format your SQL code and execution plans properly: dBforums - BB Code List
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

  5. #5
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by mrm_23 View Post
    we do not have indexes since those are warehouse tables.
    What's that supposed to mean?
    ---
    "It does not work" is not a valid problem statement.

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    might perform better with an index on MKTF_PRTN_KEY
    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.

Tags for this Thread

Posting Permissions

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