Oracle 优化 - CPU 问题
- 作为 OLTP 应用，一般不太有 CPU 问题，比较少 - 毕竟大多数问题都是 IO 引起；但是偶尔也会有。
- 问题判断
- 很简单 - OS 出现 CPU 很高的问题，持续高于 90%
- 应用可能会表现慢
- 问题调查和解决
- 硬件
- 消耗 CPU 的 SQLs
- 限制消耗 CPU 的 SQLs
- 硬件
- 根据经验和具体情况增加新的 CPUs，曾经很难，在目前虚拟化普遍使用的情况下，不再是太大的困扰。
- 消耗 CPU 的 SQLs
- AWR report 里面的 SQL ordered by CPU Time 会明确的告诉原因。比如下面红色的 SQL ID 69vuwhdz0jw51消耗了 Oracle 57% 的 CPU.
- 这个 SQL 每次执行 0.22 秒 CPU, 实际耗时 （Elapsed Time / Executions = 0.5 秒左右）；再结合具体的 SQL, 没有优化的余地，那么需要 要么修改代码减少调用次数，要么修改代码简化 SQL
SQL ordered by CPU Time
-
- Resources reported for PL/SQL code includes the resources used by all SQL statements called by the code.
- % Total is the CPU Time divided into the Total CPU Time times 100
- Total CPU Time (s): 56,812
- Captured SQL account for 79.1% of Total
- Total CPU Time (s): 56,812
- Captured PL/SQL account for 0.9% of Total
| CPU Time (s) | Elapsed Time (s) | Executions | CPU per Exec (s) | % Total | % Total DB Time | SQL Id | SQL Module | SQL Text |
|---|---|---|---|---|---|---|---|---|
| 32,595 | 96,381 | 149,476 | 0.22 | 57.37 | 47.42 | 69vuwhdz0jw51 | JDBC Thin Client | |
| 1,940 | 5,451 | 190 | 10.21 | 3.41 | 2.68 | 47y171y2nuvy9 | JDBC Thin Client | |
| 1,232 | 3,816 | 5,717 | 0.22 | 2.17 | 1.88 | 3x365dut1trbz | JDBC Thin Client | |
| 1,013 | 3,147 | 4,722 | 0.21 | 1.78 | 1.55 | 8vmfr11dt6bb7 | JDBC Thin Client | |
| 908 | 2,792 | 4,225 | 0.21 | 1.60 | 1.37 | 9kxdmwbytzsbs | JDBC Thin Client | |
| 848 | 2,139 | 451 | 1.88 | 1.49 | 1.05 | 9zv8ryyqzjudh | JDBC Thin Client | |
| 564 | 1,651 | 2,530 | 0.22 | 0.99 | 0.81 | bdd9ay1mz8x22 | JDBC Thin Client | |
| 558 | 1,637 | 2,532 | 0.22 | 0.98 | 0.81 | 1pfxcnwp9ydw9 | JDBC Thin Client | |
| 556 | 1,633 | 2,532 | 0.22 | 0.98 | 0.80 | dwysa8fbxz8du | JDBC Thin Client | |
| 500 | 1,496 | 2,288 | 0.22 | 0.88 | 0.74 | 57jgy744z6rq3 | JDBC Thin Client |
-
- 也可以使用下面的 SQLs 来更细的分析 TOP CPU SQLs.
- 按照 CPU 总耗时排序 （执行次数 X 每次的 CPU 耗时）
select a.SQL_ID,c.username, CPU_TIME*0.000001 cpu_time_secs,
ELAPSED_TIME*0.000001 elapsed_time_secs,round(ELAPSED_TIME*0.000001/nvl(nullif(executions,0),1)) Elap_per_Exec,
executions,ROWS_PROCESSED,b.piece line#,b.sql_text
from v$sql a, v$sqltext b,dba_users c
where
a.address=b.address
and
(
ELAPSED_TIME*0.000001>10
or executions > 1000
)
and executions>0
and c.user_id=a.PARSING_USER_ID
order by CPU_TIME,a.HASH_VALUE, b.piece asc
- 按照 SQL 单次执行的CPU 耗时排序
select c.username,CPU_TIME*0.000001 cpu_time_secs,
ELAPSED_TIME*0.000001 elapsed_time_secs,
round(ELAPSED_TIME*0.000001/nvl(nullif(executions,0),1)) Elap_per_Exec,
executions,LAST_LOAD_TIME,
b.piece line#,
b.sql_text sql_text
from v$sql a, v$sqltext b, dba_users c
where
a.address=b.address
and
(
ELAPSED_TIME*0.000001>10
or executions > 1000
)
and executions>0
and c.user_id=a.PARSING_USER_ID
order by Elap_per_Exec,ELAPSED_TIME,CPU_TIME,a.HASH_VALUE, b.piece asc;
- 资源限制 - 终止 CPU 耗时超过一定阀值的 SQL
Create resource limit to limit SQLs which run more than 5 minutes.
alter system set resource_limit =true scope=both; (Oracle restart required to take effect)
create profile cpu_limit_3_min limit cpu_per_call 30000; (time is in 100ths of a second)
alter user XXXX profile cpu_limit_5_min; (change the user to use the profile)
