Oracle DB Tuning recipe

本文详细介绍了在Oracle数据库中进行性能调优的步骤,包括检查响应时间、CPU使用率、并行和不可扩展行为等关键指标,并提供了针对性的解决策略。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

假设操作系统和数据库的数据都已经收集了,以下是Oracle DB Tuning的一些步骤(可据经验增加)
1. Is the response time/batch run time acceptable for a single user on an empty or
lightly loaded system?
If it is not acceptable, then the application is probably not coded or designed
optimally, and it will never be acceptable in a multiple user situation when system
resources are shared. In this case, get application internal statistics, and get SQL
Trace and SQL plan information. Work with developers to investigate problems in
data, index, transaction SQL design, and potential deferral of work to
batch/background processing.


2. Is all the CPU being utilized?
If the kernel utilization is over 40%, then investigate the operating system for
network transfers, paging, swapping, or process thrashing. Otherwise, move onto
CPU utilization in user space. Check to see if there are any non-database jobs
consuming CPU on the system limiting the amount of shared CPU resources, such
as backups, file transforms, print queues, and so on. After determining that the
database is using most of the CPU, investigate the top SQL by CPU utilization.
These statements form the basis of all future analysis. Check the SQL and the
transactions submitting the SQL for optimal execution. Oracle provides CPU
statistics in V$SQL and V$SQLSTATS.
If the application is optimal and there are no inefficiencies in the SQL execution,
consider rescheduling some work to off-peak hours or using a bigger system.


3. At this point, the system performance is unsatisfactory, yet the CPU resources are
not fully utilized.
In this case, you have serialization and unscalable behavior within the server. Get
the WAIT_EVENTS statistics from the server, and determine the biggest
serialization point. If there are no serialization points, then the problem is most
likely outside the database, and this should be the focus of investigation.
Elimination of WAIT_EVENTS involves modifying application SQL and tuning
database parameters. This process is very iterative and requires the ability to drill
down on the WAIT_EVENTS systematically to eliminate serialization points.
See Also: Oracle Database Reference for more information on
V$SQL and V$SQLSTATS

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值