2

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
  • 1
    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
1

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')));
2

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;

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