title: High CPU usage by a query in oracle
body: <p>I have a query like this...</p>
  
  <pre><code>SELECT cust_num, year,credit_cust FROM
  (SELECT cust_num, year,credit_cust,
  ROW_NUMBER () OVER(PARTITION BY cust_num, year ORDER BY credit_cust DESC) rnk
  FROM credit_cust PARTITION (YEAR_2010)
  )
  WHERE rnk=1
  </code></pre>
  
  <p>When this query is executed, the CPU usage of the server where the DB is hosted increases by at least 35%. This really concerns when there is high load in the server as the CPU usage reaches 100% sometimes.</p>
  
  <p><strong>Requirement:</strong></p>
  
  <p>The data is like below...</p>
  
  <pre><code>Cust_num  Year    Credit_cust
    456     2010       Y
    456     2010       N
    456     2009       N
    456     2009       N
  </code></pre>
  
  <p>I expect the query to return only the records given below...</p>
  
  <pre><code>Cust_num  Year    Credit_cust
    456     2010       Y
    456     2009       N
  </code></pre>
  
  <p><strong>Note:</strong> I have used ROW_NUMBER() as a workaround for GROUP BY clause. As, earlier, i thought its just the GROUP BY clause which causing the high CPU usage</p>
  
  <p><strong>Alternate query:</strong> (which is also having >35%CPU usage)</p>
  
  <pre><code>SELECT   cust_num, YEAR, MAX (credit_cust)
      FROM credit_cust PARTITION (year_2010)
  GROUP BY cust_num, YEAR
  
  
  Server : Sun OS 5.10
  Database : Oracle 11g (11.2.0.2.0 -64bit)
  </code></pre>
  
  <p>Structure of <code>credit_cust</code> table is like below...</p>
  
  <pre><code>CREATE TABLE CREDIT_CUST
  (
    CUST_NUM          NUMBER,
    YEAR     NUMBER,
    CREDIT_CUST  CHAR(1)
  )
  TABLESPACE PARTITION_01
  PARTITION BY RANGE (YEAR) 
  SUBPARTITION BY HASH (CUST_NUM)
  (  
    PARTITION YEAR_2009 VALUES LESS THAN (2010)
      SUBPARTITIONS 16 STORE IN (PARTITION_01,PARTITION_02,PARTITION_03,PARTITION_04,PARTITION_05,PARTITION_06,
                                 PARTITION_01,PARTITION_02,PARTITION_03,PARTITION_04,PARTITION_05,PARTITION_06,
                                 PARTITION_01,PARTITION_02,PARTITION_03,PARTITION_04),  
    PARTITION YEAR_2010 VALUES LESS THAN (2011)
      SUBPARTITIONS 1 STORE IN (PARTITION_01))
  PARALLEL ( DEGREE 16 INSTANCES 1 );
  </code></pre>
  
  <p>Please tell me why this issue comes up. Let me know if any more details are required.</p>
  
  <p><strong>EXPLAIN PLAN:</strong></p>
  
  <pre><code>PLAN_TABLE_OUTPUT
  
  Plan hash value: 3927595547
  
  ----------------------------------------------------------------------------------------------------------------------------------------------------------------------
  | Id  | Operation                      | Name                           | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |
  ----------------------------------------------------------------------------------------------------------------------------------------------------------------------
  |   0 | SELECT STATEMENT               |                                |    29M|  1179M|       |  3937   (3)| 00:00:01 |       |       |        |      |            |
  |   1 |  PX COORDINATOR                |                                |       |       |       |            |          |       |       |        |      |            |
  |   2 |   PX SEND QC (RANDOM)          | :TQ10001                       |    29M|  1179M|       |  3937   (3)| 00:00:01 |       |       |  Q1,01 | P-&gt;S | QC (RAND)  |
  |*  3 |    VIEW                        |                                |    29M|  1179M|       |  3937   (3)| 00:00:01 |       |       |  Q1,01 | PCWP |            |
  |*  4 |     WINDOW SORT PUSHED RANK    |                                |    29M|   364M|   674M|  3937   (3)| 00:00:01 |       |       |  Q1,01 | PCWP |            |
  |   5 |      PX RECEIVE                |                                |    29M|   364M|       |  3937   (3)| 00:00:01 |       |       |  Q1,01 | PCWP |            |
  |   6 |       PX SEND HASH             | :TQ10000                       |    29M|   364M|       |  3937   (3)| 00:00:01 |       |       |  Q1,00 | P-&gt;P | HASH       |
  |*  7 |        WINDOW CHILD PUSHED RANK|                                |    29M|   364M|       |  3937   (3)| 00:00:01 |       |       |  Q1,00 | PCWP |            |
  |   8 |         PX BLOCK ITERATOR      |                                |    29M|   364M|       |    34   (3)| 00:00:01 |     1 |     1 |  Q1,00 | PCWC |            |
  |*  9 |          TABLE ACCESS FULL     | CREDIT_CUST                    |    29M|   364M|       |    34   (3)| 00:00:01 |   228 |   228 |  Q1,00 | PCWP |            |
  ----------------------------------------------------------------------------------------------------------------------------------------------------------------------
  
  Predicate Information (identified by operation id):
  ---------------------------------------------------
  
     3 - filter("RNK"=1)
     4 - filter(ROW_NUMBER() OVER ( PARTITION BY "CUST_NUM","YEAR" ORDER BY INTERNAL_FUNCTION("CREDIT_CUST") DESC )&lt;=1)
     7 - filter(ROW_NUMBER() OVER ( PARTITION BY "CUST_NUM","YEAR" ORDER BY INTERNAL_FUNCTION("CREDIT_CUST") DESC )&lt;=1)
     9 - filter("YEAR"=2010)
  
  Note
  -----
     - dynamic sampling used for this statement (level=5)
  </code></pre>