SQL Tuning - File IO Performance
需积分: 0 191 浏览量
2009-06-20
16:00:37
上传
评论
收藏 21KB PDF 举报
SQL Tuning - File I/O Performance
By David Nishimoto
File I/O and Performance Tuning
The following concepts explain the relationship between SQL Area and Shared Pool.
The data dictionary takes its stake from the shared pool first, then the library cache
takes the remainder of the pool.
The Library Cache is used to store SQL statements and PL/SQL blocks to be shared
by users. The library Cache prevents statement reparsing, which increases
response performance.
High reloads means that the SQL Area is reloading sqls from disk.
Steps for evaluating the SQL Area (Library Cache):
1. Check if the SQL is in the SQL Area. Reload if possible.
2. Parse the SQL for syntax errors.
3. Check the data dictionary for objects.
(
tables,
views,
snapshots,
functions,
packages,
and procedures
)
4. Check the object priviledges (based on roles or ownership).
5. Execute the SQL.
Update, Insert, and Delete use the data buffer to the store block header
information. The block header information references the data store in the rollback
segment. The data buffer contains the before and after image of the data.
A commit causes the log writer to flush the redo buffer to the redo logs, redo logs
are checkpointed, and data writer then writes the data cache to disk and
checkpoints the data.
If the log buffer fills up to a third full, the log write flushes the buffer to the redo
logs. Therefore, redo logs can contain both committed and uncommitted
transactions.
6. Measure SQL Area performance by the following indicators:
评论0