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