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

Thread: query tuning

Hybrid View

  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,554
    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,554
    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 06:57 AM. Reason: increase details

  7. #7
    Join Date
    Jan 2007
    Posts
    231
    i can't update here properly if i copy that file from notepad and paste here it aligns to left.I have edited previous thread manully but still it doesn't shows any major change,it move only one space

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

    You need to learn how to use the followng tags and enclose your message in those tags

    click on the # symbol and then insert the plan with the query in those tags

    regards
    Hrishy

  9. #9
    Join Date
    Jan 2007
    Posts
    231
    Code:
    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 |
    
    --------------------------------------------------------------------------------------------------------------------------------------
    
    
    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;
    Query copied from previous thread
    thnx Hrishy
    Last edited by ams-jamali; 08-31-2007 at 06:59 AM. Reason: increase details

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

    I wish you would have learned how to post and ask a question

    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;
    Plan is

    Code:
    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")
    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')

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