When doing a performance review I often do talk with the DBA about the CPU utilization of the server. How reliable is the server CPU utilization with tools like top or the host CPU utilization in the AWR-report? E.g. on an Linux Intel x86-64 server with 8 Cores and 16 logical CPUs (Intel Hyperthreading), what does a utilization of 50% mean?
\nAs I had an ODA X7-M in a test lab available, I thought I’ll do some tests on that.<\/p>\n
In my old days at Oracle Support we used a small script to test the CPU single thread performance of an Oracle DB-server:<\/p>\n
The SQL just burns a CPU-Core for around 20 seconds. Depending on your CPU single thread performance it may take a bit longer or completes faster.<\/p>\n On the ODA X7-M I have 16 Cores enabled and as hyperthreading enabled I do get 32 CPUs in \/proc\/cpuinfo:<\/p>\n The CPU-speed was at 2.3 GHZ all the time:<\/p>\n The CPU is capable of running up to 3.7 GHZ, but that did not happen on my machine.<\/p>\n Running my SQL-script on the ODA X7-M actually took 17.49 seconds:<\/p>\n I continued to do the following tests (a job means running above SQL-script): Here the result:<\/p>\n When running with 16 Jobs top showed a utilization of around 50-52%. However running more than 16 Jobs showed an increase of the average time a job takes. I.e. with 16 Jobs the 16-Cores-Server is already almost fully utilized. Running with 32 Jobs results in an average elapsed time of 1.9 times compared to running 16 jobs (or less) concurrently. As it is 1.9 times and not 2 times I can conclude that there is an advantage of running with hyperthreading enabled, but it’s only around 5-10%. <\/p>\n So when calculating the utilization of your server then base it on the number of cores and not on the number of threads. When looking at your host CPU-utilization in top or in the AWR-report on an hyperthreaded-enabled server then it’s a good idea to multiply the server-utilization by 1.9.<\/p>\n","protected":false},"excerpt":{"rendered":" When doing a performance review I often do talk with the DBA about the CPU utilization of the server. How reliable is the server CPU utilization with tools like top or the host CPU utilization in the AWR-report? E.g. on an Linux Intel x86-64 server with 8 Cores and 16 logical CPUs (Intel Hyperthreading), what […]<\/p>\n","protected":false},"author":35,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[229,198,59],"tags":[1296,423,630,96,67],"type_dbi":[],"class_list":["post-10888","post","type-post","status-publish","format-standard","hentry","category-database-administration-monitoring","category-database-management","category-oracle","tag-core","tag-cpu","tag-cpu-thread","tag-oracle","tag-performance"],"acf":[],"yoast_head":"\n
\nset echo on
\nset linesize 120
\nset timing on time on
\nwith t as ( SELECT rownum FROM dual CONNECT BY LEVEL <= 60 )
\nselect \/*+ ALL_ROWS *\/ count(*) from t,t,t,t,t
\n\/
\n<\/code><\/p>\n
\noracle@dbi-oda01:\/home\/oracle\/cbleile\/ [CBL122] grep processor \/proc\/cpuinfo | wc -l
\n32
\noracle@dbi-oda01:\/home\/oracle\/cbleile\/ [CBL122] lscpu | egrep \"Thread|Core|Socket|Model name\"
\nThread(s) per core: 2
\nCore(s) per socket: 8
\nSocket(s): 2
\nModel name: Intel(R) Xeon(R) Gold 6140 CPU @ 2.30GHz
\n<\/code><\/p>\n
\n[root@dbi-oda01 ~]# for a in `ls -l \/sys\/devices\/system\/cpu\/cpu*\/cpufreq | grep cpufreq | cut -d \"\/\" -f6 | cut -d \"u\" -f2`; do echo \"scale=3;`cat \/sys\/devices\/system\/cpu\/cpu${a}\/cpufreq\/cpuinfo_cur_freq`\/1000000\" | bc; done
\n2.301
\n2.301
\n2.301
\n2.301
\n2.301
\n2.301
\n2.301
\n2.301
\n2.301
\n2.301
\n2.301
\n2.301
\n2.301
\n2.301
\n2.301
\n2.301
\n2.301
\n2.301
\n2.301
\n2.301
\n2.301
\n2.301
\n2.301
\n2.301
\n2.301
\n2.301
\n2.301
\n2.301
\n2.301
\n2.301
\n2.301
\n2.301
\n<\/code><\/p>\n
\n18:44:00 SQL> with t as ( SELECT rownum FROM dual CONNECT BY LEVEL <= 60 )
\n18:44:00 2 select \/*+ ALL_ROWS *\/ count(*) from t,t,t,t,t
\n18:44:00 3 \/
\n
\n COUNT(*)
\n----------
\n 777600000
\n
\nElapsed: 00:00:17.49
\n<\/code><\/p>\n
\n– 1 Job alone
\n– 2 Jobs concurrently
\n– 4 Jobs concurrently
\n– 8 Jobs concurrently
\n– 16 Jobs concurrently
\n– 24 Jobs concurrently
\n– 32 Jobs concurrently
\n– 40 Jobs concurrently
\n– 50 Jobs concurrently
\n– 60 Jobs concurrently
\n– 64 Jobs concurrently
\n– 128 Jobs concurrently<\/p>\n
\nJobs\t\tMin Time\tMax Time\tAvg Time\tJobs\/Cores\tJobs\/Threads\tAvg\/Single-Time\tThread utilization
\n
\n1\t\t17.49\t\t17.49\t\t17.49\t\t0.06\t\t0.03\t\t\t1.00\t\t1.00
\n2\t\t17.51\t\t17.58\t\t17.55\t\t0.13\t\t0.06\t\t\t1.00\t\t1.00
\n4\t\t17.47\t\t17.86\t\t17.62\t\t0.25\t\t0.13\t\t\t1.01\t\t0.99
\n8\t\t17.47\t\t17.66\t\t17.55\t\t0.50\t\t0.25\t\t\t1.00\t\t1.00
\n16\t\t17.64\t\t21.65\t\t18.50\t\t1.00\t\t0.50\t\t\t1.06\t\t0.95
\n24\t\t18\t\t27.38\t\t24.20\t\t1.50\t\t0.75\t\t\t1.38\t\t0.72
\n32\t\t32.65\t\t34.57\t\t33.21\t\t2.00\t\t1.00\t\t\t1.90\t\t0.53
\n40\t\t34.76\t\t42.74\t\t40.31\t\t2.50\t\t1.25\t\t\t2.30\t\t0.54
\n50\t\t48.26\t\t52.64\t\t51.21\t\t3.13\t\t1.56\t\t\t2.93\t\t0.53
\n60\t\t52.4\t\t63.6\t\t60.63\t\t3.75\t\t1.88\t\t\t3.47\t\t0.54
\n64\t\t54.2\t\t68.4\t\t64.27\t\t4.00\t\t2.00\t\t\t3.67\t\t0.54
\n128\t\t119.49\t\t134.34\t\t129.01\t\t8.00\t\t4.00\t\t\t7.38\t\t0.54
\n<\/code><\/p>\n