query tuning - Page 2
DBAsupport.com Forums - Powered by vBulletin
Page 2 of 6 FirstFirst 1234 ... LastLast
Results 11 to 20 of 54

Thread: query tuning

  1. #11
    Join Date
    Jan 2001
    Posts
    2,828
    Hi

    Please use code tags to post the plan i am afraid without that the plan is unreadable


    regards
    Hrishy

  2. #12
    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

  3. #13
    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

  4. #14
    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 05:59 AM. Reason: increase details

  5. #15
    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')

  6. #16
    Join Date
    Jan 2007
    Posts
    231
    yes Hrishy,Thanks

    I think PAVB lost his patient and went home

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

    Can you create indexes on

    a)PERSON (person_nr ,status_code)

    b)JOB_QUEUE(job_queue_type_nr,status_code)

    run dbms_stats to analyze the indexes and tables and post the query and the plan using code tags please

    regards
    Hrishy

  8. #18
    Join Date
    Jan 2007
    Posts
    231
    I have indexes on these two columns in person and in job_queue table.

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

    Nope i dont see composite indexes anywhere on these two tables

    You need to have those composite indexes

    PERSON (person_nr ,status_code)

    JOB_QUEUE(job_queue_type_nr,status_code)

    run dbms_stats and run the explain plan again

    regards
    Hrishy

  10. #20
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Let me add a couple of suggestions.

    1- Your main query is doing a Full Table Scan on PERSON table because of the p.email not like '%test%' predicate.

    Since your are filtering out p.email like '%test%' I'm wondering if you can just delete these offending rows from PERSON table and get rid of your not like predicate.

    2- For each row on your main query you are executing both of your inline views -namely subqueries. That means the individual performance of each one of your subqueries has a big effect in the overall performance.

    I would focus in fine tuning each one of the subqueries -individually and then, fine tune the main query.

    Just my two cents.
    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.

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