-
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.
-
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
-
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
-
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...
-
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)
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|