What does it Mean when a Select Statement in Oracle is using 100% CPU?

14 02 2013

February 14, 2013

A couple of days ago I noticed that an interesting set of search keywords were used to access this blog.  The search keywords:

What does it mean when a select statement in Oracle is using 100% cpu

I had two thoughts when I first saw that set of search keywords:

  • Well Done!
  • What an inconsiderate Oracle developer!

I feel a bit conflicted about the two first thought answers.  Think about the above bullet points for a moment.

I was interested enough in the keywords that were phrased in the form of a question, that I went though the search keywords used to access this blog for the last two weeks, and found the following related search keywords:

  • ora-01861 Oracle causing cpu 100% usage
  • Oracle one session 100% cpu
  • Oracle cpu utilisation 100%, is it a good or bad sign
  • Oracle database server cpu utilization is 100

Interesting, and possibly thought provoking.  I recalled an article that I wrote on this blog roughly three years ago.

Possible related thoughts of a temporarily rogue DBA:

  1. I fixed your problem without changing the execution plan… take a look at the EXPLAIN PLAN output for yourself.
  2. I fixed your problem without changing the execution plan… the server is more efficient now that we have consolidated three more database instances onto the server.
  3. I fixed your problem without changing the execution plan… that PGA_AGGREGATE_TARGET parameter was clear on the other side of 100 MB, so I set it to 10 MB.
  4. I fixed your problem without changing the execution plan… a group of the guys in the office found a way to load the game Doom on the server.
  5. I fixed your problem without changing the execution plan… I took half of the hard drives out of the array and moved the server to a 10 Mb hub.

Took a close look at the bulletpointed search terms.  If someone were to ask you one of the following questions, how would you respond if you were earnestly attempting to help the person?

  1. What does it mean when a Select statement in Oracle is using 100% CPU?
  2. The server admin reported that the Oracle database server cpu utilization hits 100% too frequently, what should I do about the problem?
  3. Oracle is throwing an ORA-01861 error, and that is causing 100% CPU usage.  What should I do?

Share your thoughts.

Summary of Suggestions/Causes Shared by Readers (Last Updated February 14, 2013):

  • Intended or unintended Cartesian product between row sources – does the query have a DISTINCT clause.  (Rodger)
  • Intended or unintended data type conversions – is the data model storing numbers or date values in VARCHAR2 columns.  (Jeremy Kendrick)

Actions

Information

4 responses

14 02 2013
Rodger

In my experience, 100% CPU can be caused by a cartesian product in a SQL query.

That resolves to billions of rows. When any and all tables in the query have only hundreds or thousands or rows.

I’ve come up with 5 ways to get cartesian product in a SQL query. See my research and presentation here:

http://rodgersnotes.wordpress.com/2010/09/15/stamping-out-cartesian-products/

14 02 2013
Jeremy Kendrick

the quick response from googling the error code seems to be something to do with data type conversion. That answer sounds plausible for all three questions. I seem to recall something similar happening with some of your custom built software after updating the Oracle database and the ERP software there. Some of the fields had changed from a string to a date format (Vice/versa) or they went to using a new field format that we learned was meant for handling files instead of small data strings (Blobs or something like that).

14 02 2013
Charles Hooper

Good ideas so far to help people in search of solutions to the problems that might have led to the Internet searches – I will try to summarize the ideas as they arrive, listed at the bottom of the blog article.

The blog article intentionally suggests in a subtle (but possibly humorous) way that maybe a distinction is necessary. Is the SQL statement performing necessary work, and if so, what does it indicate when that SQL statement uses 100% CPU? In such a case, is it necessarily bad that the SQL statement uses 100% of the CPU, or is that level of CPU consumption possibly ideal. What is the alternative to using CPU… would it be better or worse if the session were instead in a wait event rather than consuming CPU time?

15 02 2013
Charles Hooper

I thought that I would explore the following question a little:
1. What does it mean when a Select statement in Oracle is using 100% CPU?

One of my first questions would be how was it determined that a SELECT statement was using 100% CPU? The duration of the 100% CPU usage, if in fact it existed, is also important. Does the server only have a single CPU core, or is the query implementing parallel query? What more important task is interrupted when the query uses a lot of CPU time?

It might be worthwhile to look at a couple of execution plans that were previously provided by a reader (see: http://hoopercharles.wordpress.com/2011/01/03/no_query_transformation-hint-is-ignored-well-almost-ignored/#comment-2597 )

SQL_ID  3u689tu6mfkdw, child number 0
-------------------------------------
select count(*) from kso.little_skew a left outer join kso.little_skew
b on a.pk_col = b.pk_col
 
Plan hash value: 1386718486
 
---------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name        | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |             |       |       |       |  4984 (100)|          |
|   1 |  SORT AGGREGATE             |             |     1 |    12 |       |            |          |
|*  2 |   HASH JOIN OUTER           |             |  1210K|    13M|    18M|  4984   (1)| 00:01:00 |
|   3 |    TABLE ACCESS STORAGE FULL| LITTLE_SKEW |  1100K|  6445K|       |  1546   (1)| 00:00:19 |
|   4 |    TABLE ACCESS STORAGE FULL| LITTLE_SKEW |  1100K|  6445K|       |  1546   (1)| 00:00:19 |
---------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("A"."PK_COL"="B"."PK_COL")
SQL_ID  6x3g2dtmwgvyu, child number 0
-------------------------------------
select /*+ NO_QUERY_TRANSFORMATION */ count(*) from kso.little_skew a
left outer join kso.little_skew b on a.pk_col = b.pk_col
 
Plan hash value: 2156590542
 
---------------------------------------------------------------------------------------------
| Id  | Operation                     | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |             |       |       |  1703M(100)|          |
|   1 |  SORT AGGREGATE               |             |     1 |       |            |          |
|   2 |   VIEW                        |             |    12G|       |  1703M  (1)|999:59:59 |
|   3 |    NESTED LOOPS OUTER         |             |    12G|    67G|  1703M  (1)|999:59:59 |
|   4 |     TABLE ACCESS STORAGE FULL | LITTLE_SKEW |  1100K|  6445K|  1546   (1)| 00:00:19 |
|   5 |     VIEW                      |             | 11001 |       |  1548   (1)| 00:00:19 |
|*  6 |      TABLE ACCESS STORAGE FULL| LITTLE_SKEW | 11001 | 66006 |  1548   (1)| 00:00:19 |
---------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
   6 - filter("A"."PK_COL"="B"."PK_COL")

Is it the 100 in the Cost (%CPU) column that is of concern? Both of the above execution plans show the same (100) value on the first line of the execution plans.

(Edit Feb 15, 2013 – I just noticed that Tanel Poder posted a related blog article yesterday that describes an approach to troubleshooting excessive CPU utilization for a SQL statement: http://blog.tanelpoder.com/2013/02/14/troubleshooting-high-cpu-usage-with-poor-mans-stack-profiler-in-a-one-liner/ )

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s




Follow

Get every new post delivered to your Inbox.

Join 140 other followers

%d bloggers like this: