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!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

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

474007

Something did go wrong in the previous post so this is the whole message

***********************
Hi!

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,
              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,
    ( SELECT CONCAT(u_finalresult_user.u_sdg_id,u_finalresult_user.u_test_template_id),
              NVL(u_finalresult_user.u_overruled_result,u_finalresult_user.u_calculated_result),
           TO_CHAR(COUNT(*)) || 'x' || NVL(u_finalresult_user.u_overruled_result,u_finalresult_user.u_calculated_result) As ValCount,
           ROW_NUMBER()
              OVER (PARTITION BY CONCAT(u_finalresult_user.u_sdg_id,u_finalresult_user.u_test_template_id)
                ORDER BY COUNT(*) DESC, NVL(u_finalresult_user.u_overruled_result,u_finalresult_user.u_calculated_result)) AS Rank,
           COUNT(*)
              OVER (PARTITION BY CONCAT(u_finalresult_user.u_sdg_id,u_finalresult_user.u_test_template_id)) AS MaxLevel   
      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'
      GROUP BY CONCAT(u_finalresult_user.u_sdg_id,u_finalresult_user.u_test_template_id),NVL(u_finalresult_user.u_overruled_result,u_finalresult_user.u_calculated_result)
    )
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
   AND LEVEL = MaxLevel
     START WITH rank = 1 CONNECT BY 
     PRIOR inline_view_finalresult.u_sdg_id = inline_view_finalresult.u_sdg_id
     AND PRIOR inline_view_finalresult.u_test_template_id = inline_view_finalresult.u_test_template_id
     AND PRIOR Rank = Rank - 1

user503699

Hi,

Few questions:

1. What is the problem that you are trying to solve with Query 2?
2. Why is the last inline view in Query 2 not joined to other inline views ?
3. What is it that you are trying to achieve with following:
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
Since NULL rows from "inline_view_finalresult" due to no match with corresponding rows from "inline_view" will anyway do not satisfy "Ranking = 1" condition.
Do you really need that outer join ?

It will be helpful if you can post answers to above queries.

Thanks

474007

The problem is already solved but for the completion of this forum are here my answers:

1. For better understanding of the problem that is solved read the solution of the post of Aketi Jyuuzou in this link: 1269410
I transformed his (Oracle 9i) solution in my situation in this forum but with serious perfomance issues.
2. The reason of the second inline view is because it is not possible to use 2 outer join on the same table and this solution seems to be a workaround.
3. "AND inline_view_finalresult.Ranking =1 " was a ugly solution to filter undesired rows. See this link for better understanding 1268455

The solution for the performance problem AND the undesired rows was very simple:

Change

WHERE inline_view.sample_id = inline_view_finalresult.u_sample_id (+)
AND inline_view.test_template_id = inline_view_finalresult.u_test_template_id (+)

into

WHERE inline_view.sdg_id = inline_view_finalresult.u_sdg_id (+)
AND inline_view.test_template_id = inline_view_finalresult.u_test_template_id (+)

and it works perfectly! Shame on me :/

1 - 3

Locked Post

New comments cannot be posted to this locked post.

Locked on Jun 27 2006

Added on Apr 10 2006

3 comments

3,230 views

SQL & PL/SQL

Bad performance!, Bad performance!

Comments

Post Details