记录一次CPU使用率过高故障分析SQL解决案例

今天处理一个阿里云ECS上自建Oracle数据库，CPU飙升到100%的问题，把整个过程分享给各位小伙伴，希望能帮助到您。

问题现象

早上接到业务系统运维人员电话，告知系统很卡无法运行。问题数据库是一台在阿里云ECS上自建的Oracle数据库。登录到操作系统上排查，先把操作系统环境看一下。4cpu16G内存100G的磁盘还有剩余。操作系统环境基本上没有问题。

Last login: Thu Jun 20 09:03:44 2024 from 223.166.7.46 Welcome to Alibaba Cloud Elastic Compute Service ! [root@iZctgmtjcrowglZ ~]# su - oracle [oracle@iZctgmtjcrowglZ ~]$ lscpu Architecture: x86_64 CPU op-mode(s): 32-bit, 64-bit Byte Order: Little Endian CPU(s): 4 On-line CPU(s) list: 0-3 Thread(s) per core: 2 Core(s) per socket: 2 Socket(s): 1 NUMA node(s): 1 Vendor ID: GenuineIntel CPU family: 6 Model: 79 Model name: Intel(R) Xeon(R) CPU E5-2682 v4 @ 2.50GHz Stepping: 1 CPU MHz: 2499.996 BogoMIPS: 4999.99 Hypervisor vendor: KVM Virtualization type: full L1d cache: 32K L1i cache: 32K L2 cache: 256K L3 cache: 40960K NUMA node0 CPU(s): 0-3 Flags: fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush mmx fxsr sse sse2 ss ht syscall nx pdpe1gb rdtscp lm constant_tsc rep_good nopl cpuid tsc_known_freq pni pclmulqdq ssse3 fma cx16 pcid sse4_1 sse4_2 x2apic movbe popcnt tsc_deadline_timer aes xsave avx f16c rdrand hypervisor lahf_lm abm 3dnowprefetch invpcid_single pti ibrs ibpb stibp fsgsbase tsc_adjust bmi1 hle avx2 smep bmi2 erms invpcid rtm rdseed adx smap xsaveopt nt_good arat [oracle@iZctgmtjcrowglZ ~]$ free -h total used free shared buff/cache available Mem: 15G 614M 5.3G 430M 9.5G 14G Swap: 8.0G 0B 8.0G [oracle@iZctgmtjcrowglZ ~]$ df -h Filesystem Size Used Avail Use% Mounted on devtmpfs 7.7G 0 7.7G 0% /dev tmpfs 7.8G 431M 7.3G 6% /dev/shm tmpfs 7.8G 828K 7.8G 1% /run tmpfs 7.8G 0 7.8G 0% /sys/fs/cgroup /dev/vda1 99G 72G 23G 76% / tmpfs 1.6G 0 1.6G 0% /run/user/54321 tmpfs 1.6G 0 1.6G 0% /run/user/0 [oracle@iZctgmtjcrowglZ ~]$