Page 1 of 1

CPU Usage by Query

Posted: Thu Aug 18, 2016 10:17 pm
by atello86
Hi all,

I have OEM(Oracle Enterprise Manager) installed in Vertica databases, with this, i can check the activity in the servers. It seems like there a lot of activity (100% CPU utilization) in these servers.

Is there a query or a way to know which query was generating this high CPU? Looks like this happens last May 2016, i would like to know the culprit queries.

Thanks in advance.

Re: CPU Usage by Query

Posted: Mon Aug 22, 2016 6:09 pm
by Victorgm
My guess is Oracle's tool is querying the operating system for CPU usage, and not querying Vertica's system tables. Between the OS and Vertica, CPU readings can be very different.

To look at the CPU usage from Vertica's perspective, use Management Console or query Vertica's system tables yourself.

Many DB's will hold onto the CPU's without yielding to the OS, whether or not the CPU's are actually doing any work. This is by design and is important for database performance. But it makes the CPU's look busy (from an OS perspective) even when they are quiet.

Also, look-up resource pools in the Vertica docs and read about cpuaffinityset and cpuaffinitymode.

See this posting for more info:
viewtopic.php?t=2372

And for a complete list of Vertica system tables see this link (and search the web page for "cpu"):
http://vertica.tips/2014/06/02/under-th ... and-views/

Hope this Helps!

- Victor