Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have written a query utilizing Oracle's with clause. I have identified one particular with clause in a larger query to be really, really slow. Here are three versions of the same query, they all have the same execution plan!

Version 1 (takes 30 seconds):

with abc as 
(
  select count(distinct id) as my_count, some_column
  from  large_view v where some_column in (...constant values here...)
  group by some_column
) select my_count from abc;

Version 2 (takes 50ms):

  select count(distinct id) as my_count, some_column
  from  large_view v where some_column in (...constant values here...)
  group by some_column;

Version 3 (takes 50ms);

select my_count from (
  select count(distinct id) as my_count, some_column
  from  large_view v where some_column in (...constant values here...)
  group by some_column
);

I'd really like to use the with clause to keep the large query tidy. However, the difference in execution time is dramatic. Version 2 and 3 only take a few ms and Version 1 runs for about 30 seconds.

What is it that oracle does so that the statement with the with clause is that much slower and how can I get rid of it? Any explanation appreciated. I suspect that Version 1 is materializing every time but don't know for sure and how I can test it. Also, if that's the case, can I get rid of it (with a hint or a setting)? However, I could be wrong on this because with the materialize hint explicitly set the query runs even slower.

Oracle version:

  • Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit
  • Production PL/SQL Release 11.2.0.1.0 - Production
  • "CORE 11.2.0.1.0 Production"
  • TNS for Linux: Version 11.2.0.1.0 -
  • Production NLSRTL Version 11.2.0.1.0 - Production
share|improve this question
    
Are you certain the explain plans are the same? Also, it's possible that the first one had to go to disk and the 2nd/3rd queries came from cache. Is the first query slow if you run it a second time? –  Phil Dec 4 '12 at 16:21
    
@Phil Yes, it always runs slow. I suspect that it is materializing every time. Do you know if I can get rid of this behaviour? –  Falcon Dec 4 '12 at 16:24
    
The tkprof report of a session trace could help with explaining the difference in execution time. –  Vincent Malgrat Dec 4 '12 at 16:36
    
Please post the execution plans for the slow and the fast queries –  a_horse_with_no_name Dec 4 '12 at 17:46
    
Did you actually trace the three executions or is just a explain plan? They might differ a lot. And what it would be the supposed advantage of the query with the WITH clause? What are you trying to say to the query optimizer? –  fs63 Dec 7 '12 at 18:00

2 Answers 2

I had a reverse case with Oracle10g - a complex query with WITH behaved much faster... I can propose some steps that might help you find the culprit.

  1. Run them
  2. Check V$SQLSTATS - something like select a.* from V$SQLSTATS a where sql_text like '%select count(*) as my_count%'; You may find something right there (I'm just guessing, but I believe the most likely you will see difference in USER_IO_WAIT_TIME column[surely, CPU_TIME and ELAPSED_TIME will be different as well])
  3. Then check real execution plan for each sql_id from step 2 - SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR(('put_sql_id_here')));
share|improve this answer

Try the hint INLINE

with abc as 
(
  select /*+ INLINE */ count(distinct id) as my_count, some_column
  from  large_view v where some_column in (...constant values here...)
  group by some_column
) select my_count from abc;
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.