DBAsupport.com Forums - Powered by vBulletin
Page 1 of 6 123 ... LastLast
Results 1 to 10 of 54

Thread: query tuning

  1. #1
    Join Date
    Jan 2007
    Posts
    231

    query tuning

    SELECT rp.person_nr,rp.regimen_sequence,rp.regimen_type_code FROM REGIMEN_PRES rp,
    PERSON p WHERE regimen_type_code = 'CD' AND prescription_status_code= 'FNSH'
    AND rp.person_nr = p.person_nr and p.email not like '%test%' AND
    regimen_sequence = (SELECT MAX(regimen_sequence) FROM REGIMEN_PRES r
    WHERE r.person_nr = p.person_nr AND regimen_type_code = 'CD')
    AND p.person_nr IN (SELECT a.person_nr FROM JOB_QUEUE b,REGIMEN_PRES rp,PERSON a
    WHERE a.person_nr = b.person_nr AND a.person_nr = rp.person_nr AND b.job_queue_type_nr = 4 AND b.status_code = 'FNSH'
    AND (a.status_code = 'ACTV' or a.status_code = 'CHNG'))
    ORDER BY p.person_nr;

    Here is the output of plan table:

    Execution Plan
    ----------------------------------------------------------
    0 SELECT STATEMENT Optimizer=CHOOSE (Cost=5059 Card=516 Bytes=29928)
    1 0 SORT (ORDER BY) (Cost=5059 Card=516 Bytes=29928)
    2 1 HASH JOIN (SEMI) (Cost=5046 Card=516 Bytes=29928)
    3 2 HASH JOIN (Cost=2413 Card=516 Bytes=27864)
    4 3 HASH JOIN (Cost=1684 Card=7084 Bytes=297528)
    5 4 TABLE ACCESS (FULL) OF 'PERSON' (Cost=703 Card=7084 Bytes=177100)
    6 4 VIEW OF 'VW_SQ_1' (Cost=969 Card=63454 Bytes=1078718)
    7 6 SORT (GROUP BY) (Cost=969 Card=63454 Bytes=507632)
    8 7 INDEX (FAST FULL SCAN) OF 'REGIMEN_PRES_PK' (UNIQUE) (Cost=367 Card=176165 Bytes=1409320)
    9 3 TABLE ACCESS (FULL) OF 'REGIMEN_PRES' (Cost=715 Card=58722 Bytes=704664)
    10 2 VIEW OF 'VW_NSO_2' (Cost=2628 Card=342716 Bytes=1370864)
    11 10 HASH JOIN (Cost=2628 Card=342716 Bytes=7539752)
    12 11 HASH JOIN (Cost=2094 Card=42816 Bytes=770688)
    13 12 TABLE ACCESS (FULL) OF 'PERSON' (Cost=703 Card=40479 Bytes=323832)
    14 12 TABLE ACCESS (FULL) OF 'JOB_QUEUE' (Cost=1369 Card=42817 Bytes=428170)
    15 11 INDEX (FAST FULL SCAN) OF 'REGIMEN_PRES_QX28' (NON-UNIQUE) (Cost=348 Card=528495 Bytes=2113980)

    I have index on person,job_queue & regimen_pres tables on that particular column also.Why it is accessing Full table scan.
    please help.,

  2. #2
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    Please, next time enclose your code into CODE tags like...

    Code:
    SELECT  rp.person_nr,
            rp.regimen_sequence,
            rp.regimen_type_code 
    FROM    REGIMEN_PRES rp,
            PERSON p 
    WHERE   regimen_type_code = 'CD' 
        AND prescription_status_code= 'FNSH' 
        AND rp.person_nr = p.person_nr 
        and p.email not like '%test%' 
        AND regimen_sequence = (SELECT  MAX(regimen_sequence) 
                                FROM    REGIMEN_PRES r 
                                WHERE   r.person_nr = p.person_nr 
                                    AND regimen_type_code = 'CD')
        AND p.person_nr IN     (SELECT  a.person_nr 
                                FROM    JOB_QUEUE b,
                                        REGIMEN_PRES rp,
                                        PERSON a
                                WHERE   a.person_nr = b.person_nr 
                                    AND a.person_nr = rp.person_nr 
                                    AND b.job_queue_type_nr = 4 
                                    AND b.status_code = 'FNSH'
                                    AND (a.status_code = 'ACTV' or 
                                         a.status_code = 'CHNG')) 
    ORDER BY p.person_nr;
    Questions:
    1- Do you have up-to-date statistics on affected tables and indexes?
    2- Could you publish your indexes DDL?

    Comments:
    Your code has everything it needs to perform badly,
    a) you have two in-line views -which will execute once per each base row, one of them in an IN clause.
    b) you have one of the ugliest predicates you can think about "not like %*%"
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  3. #3
    Join Date
    Nov 2002
    Location
    Mooresville, NC
    Posts
    349
    You have indexes on those columns, that's fine. But how much data is being retrieved using those indexed columns. Usually if the data returned is more than 10% of total number of records it's better to go for a FTS, this 10% value change depending upon the situation. Hope this explains.
    http://www.perf-engg.com
    A performance engineering forum

  4. #4
    Join Date
    Jan 2002
    Location
    Canada
    Posts
    195
    You can remove table rp in the subquery. Seems to me it's not used in where clause.

  5. #5
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    Quote Originally Posted by dba1
    You can remove table rp in the subquery. Seems to me it's not used in where clause.
    You are wrong.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  6. #6
    Join Date
    Jan 2007
    Posts
    231
    1. I don't have up-to-date statistics on affected tables and indexes.I took that long back(may be 2-3 months).

    2.
    Table : JOB_QUEUE

    INDEX NAME UNIQUE COLUMN
    ********* ****** ******
    JOB_QUEUE_PK UNIQUE JOB_QUEUE_NR
    JOB_QUEUE_QX11 NONUNIQUE JOB_QUEUE_TYPE_NR
    JOB_QUEUE_QX12 NONUNIQUE STATUS_CODE
    JOB_QUEUE_QX13 NONUNIQUE PERSON_NR


    Table : PERSON

    INDEX NAME UNIQUE COLUMN
    ********* ****** ******
    PERSON_PK UNIQUE PERSON_NR
    PERSON_AK_1 UNIQUE USERNAME
    PERSON_IDX1 NONUNIQUE EMAIL
    PERSON_QX101 NONUNIQUE STATUS_CODE

    Table : REGIMEN_PRES

    INDEX NAME UNIQUE COLUMN
    ********* ****** *******
    REGIMEN_PRES_PK UNIQUE REGIMEN_TYPE_CODE
    REGIMEN_PRES_PK UNIQUE PERSON_NR
    REGIMEN_PRES_PK UNIQUE REGIMEN_SEQUENCE
    REGIMEN_PRES_QX28 NONUNIQUE PERSON_NR


    In regimen_pres table i have two index refer to same person_nr column.Here i have posted only the required column alone.
    Last edited by ams-jamali; 08-30-2007 at 05:57 AM. Reason: increase details

  7. #7
    Join Date
    Jan 2001
    Posts
    2,828
    Hi

    You should run update stats on all tables and indexes

    You should also post the plan using dbms_xplan which includes everything otherwise you wont get good respnoses from folks here

    regards
    Hrishy

  8. #8
    Join Date
    Jan 2007
    Posts
    231
    here is the X_plan after i done stats on affected tables and indexes.

    Plan
    --------------------------------------------------------------------------
    SELECT STATEMENT
    SORT ORDER BY
    HASH JOIN SEMI
    HASH JOIN
    HASH JOIN
    TABLE ACCESS FULL PERSON
    VIEW VW_SQ_1
    SORT GROUP BY
    INDEX FAST FULL SCAN REGIMEN_PRES_PK
    TABLE ACCESS FULL REGIMEN_PRES
    VIEW VW_NSO_2
    HASH JOIN
    HASH JOIN
    TABLE ACCESS FULL PERSON
    TABLE ACCESS FULL JOB_QUEUE
    INDEX FAST FULL SCAN REGIMEN_PRES_QX28

    16 rows selected.

    Same thing it shows nothing changed.

  9. #9
    Join Date
    Jan 2001
    Posts
    2,828
    Hi

    Please post the predicate information with the complete explain plan using code tags its very very difficult if you don't do so.

    regards
    Hrishy

  10. #10
    Join Date
    Jan 2007
    Posts
    231
    PLAN_TABLE_OUTPUT
    --------------------------------------------------------------------------------
    --------------------------------------------------------------------------------------------------------------------------------------------------------------
    | Id | Operation | Name | Rows | Bytes |TempSpc | Cost |
    --------------------------------------------------------------------------------------------------------------------------------------------------------------
    | 0 | SELECT STATEMENT | | 399 | 23142| | 5337 |
    | 1 | SORT ORDER BY | | 399 | 23142| | 5337 |
    |*2 | HASH JOIN SEMI | | 399 | 23142| | 5326 |
    |*3 | HASH JOIN | | 399 | 21546| | 2570 |
    |*4 | HASH JOIN | | 7084 | 290K| | 1810 |
    |*5 | TABLE ACCESS FULL | PERSON | 7084 | 172K| | 703 |
    | 6 | VIEW | VW_SQ_1 | 87401 | 1450K| | 1092 |
    | 7 | SORT GROUP BY | | 87401 | 682K| 5360K | 1092 |
    |*8 | INDEX FAST FULL SCAN | REGIMEN_PRES_PK | 184K| 1438K| | 413 |
    |*9 | TABLE ACCESS FULL | REGIMEN_PRES | 61385 | 719K| | 745 |
    |10 | VIEW | VW_NSO_2 | 209K| 817K| | 2753 |
    |* 11 | HASH JOIN | | 209K| 4495K| 1080K | 2753 |
    |* 12 | HASH JOIN | | 36761 | 646K| | 2190 |
    |* 13 | TABLE ACCESS FULL | JOB_QUEUE | 36761 | 358K| | 1466 |
    |* 14 | TABLE ACCESS FULL | PERSON | 40479 | 316K| | 703 |
    | 15 | INDEX FAST FULL SCAN | REGIMEN_PRES_QX28 | 552K| 2158K| | 373 |

    --------------------------------------------------------------------------------------------------------------------------------------


    Predicate Information (identified by operation id):
    ---------------------------------------------------

    2 - access("P"."PERSON_NR"="VW_NSO_2"."$nso_col_1")

    PLAN_TABLE_OUTPUT
    --------------------------------------------------------------------------------
    3 - access("RP"."PERSON_NR"="P"."PERSON_NR" AND "RP"."REGIMEN_SEQUENCE"="VW_SQ_1"."VW_COL_1")
    4 - access("VW_SQ_1"."PERSON_NR"="P"."PERSON_NR")
    5 - filter("P"."EMAIL" NOT LIKE '%test%')
    8 - filter("R"."REGIMEN_TYPE_CODE"='CD')
    9 - filter("RP"."REGIMEN_TYPE_CODE"='CD' AND "RP"."PRESCRIPTION_STATUS_CODE"='FNSH')
    11 - access("A"."PERSON_NR"="RP"."PERSON_NR")
    12 - access("A"."PERSON_NR"="B"."PERSON_NR")
    13 - filter("B"."JOB_QUEUE_TYPE_NR"=4 AND "B"."STATUS_CODE"='FNSH')
    14 - filter("A"."STATUS_CODE"='ACTV' OR "A"."STATUS_CODE"='CHNG')

    Note: cpu costing is off

    Hrishy is this enough,if anythingelse i left pls specify.

    thnx
    Last edited by ams-jamali; 08-31-2007 at 05:09 AM. Reason: increase details

Posting Permissions

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


Click Here to Expand Forum to Full Width