Tuning SQL
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Tuning SQL

  1. #1
    Join Date
    Aug 2005
    Posts
    3

    Tuning SQL

    Hi All,
    I am working on one project status report and encountering performance problem. Report is to do counts for different criteria’s. There are such 15 counts to do. I am trying to do it with inline views(thats the only way I can think of, your more then welcome to suggest something else).Only adding 2 inline views making SQL to complete in 30 minutes, you can imagine whats going to happen after adding all remaining 13.Remaining queries may use more then 3 or different tables.


    Requirement
    -----------
    1) Fine tune existing SQL
    2) Any suggestion to change query/not to use inline views
    3) Not sure why optimizer doing Full Scan on opti_studies while there exist index on DOC_ID


    QUERY :-
    ------
    Code:
    Select OAS.app_id,OAS.app_short_desc,OAS.app_long_desc,DE1_Complete,
    DE2_Complete from  OPTI_ACTIVE_STUDIES_V OAS,
    		 (SELECT count(distinct(doc_id)) DE1_Complete,   OPS.app_id FROM opti_studies OPS, crf.item_history IH,crf.image IMG
    WHERE OPS.doc_id = IMG.img_doc_id
    AND IMG.img_scan_date IS NOT NULL
    AND OPS.doc_id = IH.ih_folder_id
    AND OPS.version=0
    AND ih_task_action = 'Data Entry 2'
    group by OPS.app_id) DE1,
                 (SELECT count(distinct(doc_id))  DE2_Complete,OPS1.app_id
     FROM opti_studies OPS1, crf.item_history IH, crf.image IMG
     WHERE OPS1.doc_id = IMG.img_doc_id
     AND IMG.img_scan_date IS NOT NULL
     AND OPS1.doc_id = IH.ih_folder_id
     AND OPS1.version = 0
     AND IH.ih_task_action = 'CDC'
     group by OPS1.app_id) DE2
    WHERE 	  OAS.app_id   =	  DE1.app_id  
    AND	  DE1.app_id   =	  DE2.app_id;
    
    Total Rows in Tables
    -------------------
    
    Item History --> 12591691
    Image        --> 881453
    opti_studies --> 767405
    OPTI_ACTIVE_STUDIES_V -- 24
    
    Note : All table/indexes are fully Analyzed
    
    
    EXPLAIN PLAN :-
    ---------------
    
    Operation	Object Name	Rows	Bytes	Cost	Object Node	In/Out	PStart	PStop
    
    SELECT STATEMENT Optimizer Mode=CHOOSE		2  	 	92  	 	      	             	 
      NESTED LOOPS		2  	168  	92  	 	      	             	 
        NESTED LOOPS		2  	148  	90  	 	      	             	 
          HASH JOIN		2  	104  	88  	 	      	             	 
            VIEW		2  	52  	44  	 	      	             	 
              SORT GROUP BY		2  	142  	44  	 	      	             	 
                TABLE ACCESS BY INDEX ROWID	CRF.ITEM_HISTORY	1  	19  	18  	 	      	             	 
                  NESTED LOOPS		2  	142  	42  	 	      	             	 
                    NESTED LOOPS		2  	104  	6  	 	      	             	 
                      TABLE ACCESS FULL	OPTI_RPT.OPTI_STUDIES	2  	78  	2  	 	      	             	 
                      TABLE ACCESS BY INDEX ROWID	CRF.IMAGE	1  	13  	2  	 	      	             	 
                        INDEX RANGE SCAN	CRF.DMX_IDX_IMG_DOC_ID	1  	 	1  	 	      	             	 
                    INDEX RANGE SCAN	CRF.WF_IDX_IH_FOLDER_ID	46  	 	2  	 	      	             	 
            VIEW		2  	52  	44  	 	      	             	 
              SORT GROUP BY		2  	142  	44  	 	      	             	 
                TABLE ACCESS BY INDEX ROWID	CRF.ITEM_HISTORY	1  	19  	18  	 	      	             	 
                  NESTED LOOPS		2  	142  	42  	 	      	             	 
                    NESTED LOOPS		2  	104  	6  	 	      	             	 
                      TABLE ACCESS FULL	OPTI_RPT.OPTI_STUDIES	2  	78  	2  	 	      	             	 
                      TABLE ACCESS BY INDEX ROWID	CRF.IMAGE	1  	13  	2  	 	      	             	 
                        INDEX RANGE SCAN	CRF.DMX_IDX_IMG_DOC_ID	1  	 	1  	 	      	             	 
                    INDEX RANGE SCAN	CRF.WF_IDX_IH_FOLDER_ID	46  	 	2  	 	      	             	 
          TABLE ACCESS BY INDEX ROWID	CRF.APPLICATION	1  	22  	1  	 	      	             	 
            INDEX UNIQUE SCAN	CRF.PK_APPLICATION	1  	 	 	 	      	             	 
        TABLE ACCESS BY INDEX ROWID	CRF.CRF_PROTOCOL	1  	10  	1  	 	      	             	 
          INDEX UNIQUE SCAN	CRF.CRF_PROTOCOL_PK	1  	 	 
    
    INDEXES :-
    -------
    WF_IDX_IH_FOLDER_ID ON Item History	 	      	             
    DMX_IDX_IMG_DOC_ID  ON Image
    OPTI_DT_ID,OPTI_PK_DOC_ID,OPTI_PN_ID,OPTI_SJ_ID,SITE_ID,VN_ID on opti_studies
    Any help/suggestion is highly appreciated

    Thanks in advance
    Dinesh
    Last edited by marist89; 08-09-2005 at 12:49 PM.

  2. #2
    Join Date
    Jul 2000
    Posts
    521
    Tune the inline queries first. How long are they taking individually ?

    It looks like its possible to combine the two inline queries you have mentioned in this example in one query using either DECODE or CASE.
    svk

  3. #3
    Join Date
    Aug 2005
    Posts
    3
    Hi SVK,
    I fine tuned the inline quaries and now whole query is taking over 5 minutes.Tried DECODE but response time is almost same.

    Is doing count is expensive affiar ? In your openion what should be acceptable time for such type of queries ?

    Note : Please use my previous post for context.

    Thanks for your response.

    Regards
    Dinesh

  4. #4
    Join Date
    Jul 2002
    Location
    Northampton, England
    Posts
    612
    Well, you aren't just doing a count, you are also asking for distinct rows (theres a sort operation going on). Therefore, you might expect it to take a little longer:

    Code:
    select count(i_id) from x;
    
    COUNT(I_ID)
    -----------
        1020399
    
    Elapsed: 00:00:01.19
    
    select count(distinct(i_id)) from x;
    
    COUNT(DISTINCT(I_ID))
    ---------------------
                   720449
    
    Elapsed: 00:00:04.61
    Assistance is Futile...

  5. #5
    Join Date
    Aug 2005
    Posts
    3
    I know its silly question but is there any way of avoiding Distinct ?

    Is there any difference between Distinct and Unique ? (for my query it didn't make any difference)

  6. #6
    Join Date
    Nov 2000
    Posts
    224
    If I understood your logic correctly, you are trying to sum the doc_id for a particular app_id. There may be more than one doc_id for same app_id, but you want to count only one of them. If you have to do this, I can't think of any other way.
    Unique and Distinct are same.

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