SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Bad performance!, Bad performance!

474007Apr 10 2006 — edited May 30 2006

Hi!

Query1 and the queries of the inline views of query2 separate are about 1 sec response-time, but when i try to get multiple value into one collumn with SYS_CONNECT_BY_PATH than the response-time is >18 minutes(didn't finish the query). Query1 is my original query with good performance and query2 is what i have tried with the multiple values in one collumn. I have tried to get rid of the group by in the third inline view and have tried to combine the second and third view but that didn't work. I also don't know if the second query will give me the right results. The results of query1 and the inline_views of query2 seems to be accurate though.

Can anybody help me with tuning this query?

QUERY1:

SELECT DISTINCT inline_view.u_plot_number AS Research_Group,
       inline_view.external_reference AS Parental_Line, 
       inline_view.u_box_code AS Box,
       inline_view.description AS Test,
	   CASE WHEN inline_view_finalresult.ResultCount < 8
            THEN null
            ELSE CASE WHEN inline_view_finalresult.ResultDistinct > 1
                      THEN 'spl'
                      ELSE inline_view_finalresult.ResultFinal
                 END
       END AS Result
FROM ( SELECT DISTINCT sdg.sdg_id,
              sample.sample_id, 
              test_template.test_template_id,
              sample_user.u_plot_number,
              sdg.external_reference,
              sample_user.u_box_code, 
              test.description
       FROM sdg,sdg_user,sample,sample_user,aliquot,test,test_template
       WHERE sdg.sdg_id = sdg_user.sdg_id
          AND sdg.sdg_id = sample.sdg_id
          AND sample.sample_id = sample_user.sample_id
          AND aliquot.sample_id = sample.sample_id
          AND test.aliquot_id = aliquot.aliquot_id
          AND test_template.test_template_id = test.test_template_id
          AND sdg.sdg_id BETWEEN 559 ANd 566
     ) inline_view,
     ( SELECT DISTINCT u_finalresult_user.u_sdg_id,
              u_finalresult_user.u_calculated_result,
              u_finalresult_user.u_overruled_result,
              u_finalresult_user.u_sample_id,
              u_finalresult_user.u_test_template_id,
			  COUNT(NVL(u_finalresult_user.u_overruled_result,u_finalresult_user.u_calculated_result)) 
          	     OVER (PARTITION BY CONCAT(u_finalresult_user.u_sdg_id,u_finalresult_user.u_test_template_id)) AS ResultCount,
       		  COUNT(DISTINCT NVL(u_finalresult_user.u_overruled_result,u_finalresult_user.u_calculated_result)) 
          	  	 OVER (PARTITION BY CONCAT(u_finalresult_user.u_sdg_id,u_finalresult_user.u_test_template_id)) AS ResultDistinct,
      		  NVL(u_finalresult_user.u_overruled_result,u_finalresult_user.u_calculated_result) as Resultfinal,
			  ROW_NUMBER() 
			     OVER (PARTITION BY CONCAT(u_finalresult_user.u_sdg_id,u_finalresult_user.u_test_template_id) 
				 ORDER BY u_finalresult_user.u_sdg_id,u_finalresult_user.u_test_template_id ) AS Ranking
	   FROM u_finalresult_user
       WHERE u_finalresult_user.u_requested = 'T'
	      AND NVL(u_finalresult_user.u_overruled_result,u_finalresult_user.u_calculated_result) <> 'X'
     ) inline_view_finalresult
WHERE inline_view.sample_id = inline_view_finalresult.u_sample_id (+) 
   AND inline_view.test_template_id = inline_view_finalresult.u_test_template_id (+)
   AND inline_view_finalresult.Ranking =1
ORDER BY inline_view.description,inline_view.u_box_code

QUERY2:

SELECT DISTINCT inline_view.u_plot_number AS Research_Group,
       inline_view.external_reference AS Parental_Line, 
       inline_view.u_box_code AS Box,
       inline_view.description AS Test,
      CASE WHEN inline_view_finalresult.ResultCount < 8
            THEN null
            ELSE CASE WHEN inline_view_finalresult.ResultDistinct > 1
                      THEN 'spl'
                      ELSE inline_view_finalresult.ResultFinal
                 END
      END AS Result,
      CASE WHEN LEVEL >=2 
           THEN SUBSTR(SYS_CONNECT_BY_PATH(ValCount,','),2)
      END AS Spl
FROM ( SELECT DISTINCT sdg.sdg_id,
              sample.sample_id, 
              test_template.test_template_id,
              sample_user.u_plot_number,
              sdg.external_reference,
              sample_user.u_box_code, 
              test.description
       FROM sdg,sdg_user,sample,sample_user,aliquot,test,test_template
       WHERE sdg.sdg_id = sdg_user.sdg_id
          AND sdg.sdg_id = sample.sdg_id
          AND sample.sample_id = sample_user.sample_id
          AND aliquot.sample_id = sample.sample_id
          AND test.aliquot_id = aliquot.aliquot_id
          AND test_template.test_template_id = test.test_template_id
          AND sdg.sdg_id BETWEEN 559 ANd 566
     ) inline_view,
     ( SELECT DISTINCT u_finalresult_user.u_sdg_id,
              u_finalresult_user.u_calculated_result,
              u_finalresult_user.u_overruled_result,
              u_finalresult_user.u_sample_id,
              u_finalresult_user.u_test_template_id,
           COUNT(NVL(u_finalresult_user.u_overruled_result,u_finalresult_user.u_calculated_result)) 
                 OVER (PARTITION BY CONCAT(u_finalresult_user.u_sdg_id,u_finalresult_user.u_test_template_id)) AS ResultCount,orumID=75, Hi!

Query1 and the queries of the inline views of query2 separate are about 1 sec response-time, but when i try to get multiple value into one collumn with SYS_CONNECT_BY_PATH than the response-time is >18 minutes(didn't finish the query). Query1 is my original query with good performance and query2 is what i have tried with the multiple values in one collumn. I have tried to get rid of the group by in the third inline view and have tried to combine the second and third view but that didn't work. I also don't know if the second query will give me the right results. The results of query1 and the inline_views of query2 seems to be accurate though.

Can anybody help me with tuning this query?

QUERY1:
[pre]
SELECT DISTINCT inline_view.u_plot_number AS Research_Group,
       inline_view.external_reference AS Parental_Line, 
       inline_view.u_box_code AS Box,
       inline_view.description AS Test,
	   CASE WHEN inline_view_finalresult.ResultCount < 8
            THEN null
            ELSE CASE WHEN inline_view_finalresult.ResultDistinct > 1
                      THEN 'spl'
                      ELSE inline_view_finalresult.ResultFinal
                 END
       END AS Result
FROM ( SELECT DISTINCT sdg.sdg_id,
              sample.sample_id, 
              test_template.test_template_id,
              sample_user.u_plot_number,
              sdg.external_reference,
              sample_user.u_box_code, 
              test.description
       FROM sdg,sdg_user,sample,sample_user,aliquot,test,test_template
       WHERE sdg.sdg_id = sdg_user.sdg_id
          AND sdg.sdg_id = sample.sdg_id
          AND sample.sample_id = sample_user.sample_id
          AND aliquot.sample_id = sample.sample_id
          AND test.aliquot_id = aliquot.aliquot_id
          AND test_template.test_template_id = test.test_template_id
          AND sdg.sdg_id BETWEEN 559 ANd 566
     ) inline_view,
     ( SELECT DISTINCT u_finalresult_user.u_sdg_id,
              u_finalresult_user.u_calculated_result,
              u_finalresult_user.u_overruled_result,
              u_finalresult_user.u_sample_id,
              u_finalresult_user.u_test_template_id,
			  COUNT(NVL(u_finalresult_user.u_overruled_result,u_finalresult_user.u_calculated_result)) 
          	     OVER (PARTITION BY CONCAT(u_finalresult_user.u_sdg_id,u_finalresult_user.u_test_template_id))

Locked Post

New comments cannot be posted to this locked post.

Locked on Jun 27 2006

Added on Apr 10 2006

3 comments

3,248 views