Oracle DB Tuning recipe
来源:互联网 发布:淘宝魔兽世界金币 编辑:程序博客网 时间:2024/06/07 03:05
假设操作系统和数据库的数据都已经收集了,以下是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
- Oracle DB Tuning recipe
- DB Tuning
- Oracle Tuning
- ORACLE PERFORMANCE SQL TUNING
- oracle Tuning(zt)
- Oracle Performance Tuning
- Oracle Tuning技巧总结
- Oracle Tuning收藏
- oracle sql tuning
- Oracle sql statement tuning
- Oracle application tuning
- Oracle Performance tuning - Histogram
- Oracle SQL tuning 步骤
- Oracle SQL tuning 步骤
- ORACLE SQL Performance Tuning
- Oracle SQL tuning 步骤
- Oracle SQL Tuning知识
- Oracle SQL Tuning Advisor
- log4j有关学习
- 世界经典管理书籍 鱼
- sina 网站接入 oauth2授权错误 error:redirect_uri_mismatch
- 防止重复提交表单
- FireMonkey下的如何实现WndProc
- Oracle DB Tuning recipe
- real210:从摄像头读取一帧数据并压缩成jpeg图片
- javascript判断查找是否包含指定字符串[indexOf]
- 求矩阵行列式的方法
- 安卓客户端与服务器端交互
- 闪回查询——基于事物级别的闪回
- 软件加壳
- android HandlerThread使用小例
- D3D绘制图元理论基础