Tuning SQL

[dinesh_chauhan]

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 :-
------

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