title: Why can't I seem to force Oracle 11g to consume more CPUs for a single SQL query
body: <p>I have some humongous queries that run on gigantic tables. These queries seem to be CPU bottlenecked, and run for hours. I understand that Oracle has a lot of new features with 11g, release 2 to internally paralellize the execution of a query. However no matter what kinds of hints I put in the query, I cant seem to use more than 1 CPU on the database box. I have a very respectable Solaris machine with 8 CPUs, however everytime I run this query, I end up just pushing one CPU to 100% and then sit there for hours.</p>
  
  <p>The hints I have tried are:</p>
  
  <pre><code>SELECT /*+ PARALLEL */ ...
  SELECT /*+ PARALLEL(5) */ ...
  SELECT /*+ PARALLEL(10) */ ...
  </code></pre>
  
  <p>None of that appeared to work when looking at overall CPU consumption on the box. It always seemed to peg one CPU at 100%. Unfortunately even explain plan seems to take forever to run. I will try and get different explain plans with the different hints and see if that helps. Is it possible that some queries are simply un-paralleable, even if their runtime is in the hours?!!?
  The main table in this query has 335 million rows.</p>
  
  <p>SQL Query Text:</p>
  
  <p><a href="http://pastie.org/8634380" rel="nofollow">http://pastie.org/8634380</a></p>
  
  <p>System Parameters:</p>
  
  <p><a href="http://pastie.org/8634383" rel="nofollow">http://pastie.org/8634383</a></p>
  
  <p><strong>Edit</strong>:</p>
  
  <p>Detailed Explain Plan - No Parallelism:</p>
  
  <p><a href="http://pastebin.com/HkZgbPpf" rel="nofollow">http://pastebin.com/HkZgbPpf</a></p>
  
  <p>Optimizer related system parameters:</p>
  
  <p><a href="http://pastie.org/8639841" rel="nofollow">http://pastie.org/8639841</a></p>
  
  <p><strong>Further EDIT:</strong> 
  We have reached out to Oracle to understand why EXPLAIN PLAN takes more than 2 hours. We are timing out trying to run the various explain plans.</p>