一、进程与文件的读写
1、进程读写文件的权限
2、调优准则
3、指导方针
4、表空间的使用方法
5、Locally Managed system Tablespace
6、Monitoring Use of Files
7、File Striping
8、Tuning Full Table Scan Operations
9、Table Scan Statistics
10、监控全表扫描操作
11、Checkpoints
12、Performance Manager: Response Time
14、Defining and Monitoring Fast Start Checkpointing
15、Online Redo Log File Configuration
16、Increasing the Performance of Archiving[@more@]一、进程与文件的读写
1、进程读写文件的权限
进程 Data files Log Archive Control
CKPT Read/Write Read/Write
DBWn Write
LGWR Write Read/Write
ARCn Read Write Read/Write
SERVER Read/write Read Write Read/Write
说明：
The CKPT process only accesses the data file headers; the DBWn process writes out the actual table data.
The server process reads the redo logs, writes to the archive logs, and read/writes to the control file under certain backup and recovery operations.
2、调优准则
. 减少IO
. Spread your disk load across disk devices and controllers.
. 恰当地使用临时表空间
具体做法：
创建用户时，临时表空间要与其它数据库对象分开
表和索引使用不同的表空间
LOB类型的字段放入单独的空间
3、指导方针
.In general, to reduce the activity on an overloaded disk, move one or more of its
heavily accessed files to a less active disk.如：REDO LOG FILES、archive logs
.Try to eliminate I/O unrelated to the Oracle server on disks that contain database files.
.Knowing the types of operation that predominate in your application and the speed
with which your system can process the corresponding I/Os, you can choose the disk
layout that maximizes performance.
4、表空间的使用方法
. Reserve the system tablespace for data dictionary objects.
. Create locally managed tablespaces to avoid management issues.
. Split tables and indexes into separate tablespaces.
. Create rollback segments in their own tablespaces.
. Store very large objects in their own tablespace.
. Create one or more temporary tablespaces.
说明：
a、Each database should have separate tablespaces specified for:
Data dictionary objects
Rollback segments and undo segments
Temporary segments
Tables
Indexes
Very large objects：These objects can be CLOBs, BLOBs, tables or partitions.
5、Locally Managed system Tablespace
It is strongly recommended that all tablespaces be locally managed. Having locally
managed tablespaces eliminates extent fragmentation and provides superior
performance benefits over dictionary managed tablespaces.
CREATE DATABASE mydb
…
DATAFILE 'system01.dbf' SIZE 100M EXTENT
MANAGEMENT LOCAL
…
DEFAULT TEMPORARY TABLESPACE temp
TEMPFILE 'temp01.dbf' SIZE 15M
…;
When the system tablespace is locally managed:
. It is not possible to create dictionary managed tablespaces.
. It is not possible to migrate locally managed tablespace to dictionary managed type.
. It is possible to transport dictionary managed tablespaces into a database with
locally managed system tablespace. Note that after the transport, dictionary
managed tablespaces cannot be made read/write. The tablespaces can be migrated
to locally managed and then made read/write.
. It is possible to DROP the READ ONLY dictionary managed tablespace when SYSTEM tablespace is locally managed.
6、Monitoring Use of Files
A、 v$filestat view
. file#: File number (join to file# in v$datafile for the name)
. Phyrds: Number of physical reads done
. Phywrts: Number of physical writes done
. phyblkrd: Number of physical blocks read
. phyblkwrt: Number of physical blocks written
. readtim: Time spent doing reads
. writetim: Time spent doing writes
Note: The readtim and writetim columns contain 0 if the TIMED_STATISTICS parameter is set to False.
SQL> SELECT d.tablespace_name TABLESPACE,
2 d.file_name, f.phyrds, f.phywrts
3 FROM v$filestat f, dba_data_files d
4 WHERE f.file# = d.file_id;
B、 v$tempstat view
C、 File I/O monitor using Oracle Enterprise Manager
D、 File statistics in Statspack
7、File Striping
With OS striped database files with a stripe width close to (or less than)
DB_FILE_MULTIBLOCK_READ_COUNT x DB_BLOCK_SIZE,
8、Tuning Full Table Scan Operations
初始化参数db_file_multiblock_read_count 影响Oracle在执行全表扫描时一次读取的block的数量.
db_file_multiblock_read_count的设置要受OS最大IO能力影响，也就是说，如果 你系统的硬件IO能力有限，
即使设置再大的db_file_multiblock_read_count也是没有用 的。
理论上，最大db_file_multiblock_read_count和系统IO能力应该有如下关系:
Max(db_file_multiblock_read_count) = MaxOsIOsize/db_block_size
当然这个Max(db_file_multiblock_read_count)还要受Oracle的限制，
目前Oracle所支持的最大db_file_multiblock_read_count 值为128.
9、Table Scan Statistics
SQL> SELECT name, value FROM v$sysstat
2 WHERE name LIKE '%table scan%';
The query on the above slide provides an overview of how many full table scans are taking place.
The values for table scans (long tables) and table scans (short tables) relate to full table
scans. A long table is one with more than 4 blocks and a short table is 4 or less blocks.
If the value of table scans (long tables) is high, then a large percentage of the tables
accessed were not indexed lookups. Your application may need tuning or you should add
indexes. Make sure that the appropriate indexes are in place and valid.
10、监控全表扫描操作
A、查看长时间数据库操作的方法:
SQL> SELECT sid, serial#, opname,
TO_CHAR(start_time,'HH24:MI:SS') AS "START",
(sofar/totalwork)*100 AS PERCENT_COMPLETE
FROM v$session_longops;
查询对应SQL语句：
select a.sql_text from v$sqlarea a,v$session_longops b where a.HASH_VALUE=b.SQL_HASH_VALUE;
要求设置TIMED_STATISTICS或SQL_TRACE参数值为true。
v$session_longops视图显示运行超过6秒的操作的状态，包括备份，恢复，统计信息收集，查询等等。
V$SESSION_LONGOPS列说明：
SID：Session标识
SERIAL#：Session串号
OPNAME：操作简要说明
TARGET：操作运行所在的对象
TARGET_DESC：目标对象说明
SOFAR：至今为止完成的工作量
TOTALWORK：总工作量
UNITS：工作量单位
START_TIME：操作开始时间
LAST_UPDATE_TIME：统计项最后更新时间
TIME_REMAINING：预计完成操作的剩余时间(秒)
ELAPSED_SECONDS：从操作开始总花费时间(秒)
CONTEXT：前后关系
MESSAGE：统计项的完整描述
USERNAME：执行操作的用户ID
SQL_ADDRESS：用于连接查询的列
SQL_HASH_VALUE：用于连接查询的列
QCSID：
B、查看某些特定应用程序的长时间运行操作的信息
The dbms_application_info package contains a procedure,
set_session_longops, to populate the view from an application.
dbms_application_info.set_session_longops_nohint
11、Checkpoints
The two most common types of checkpoints are:
. Incremental checkpoints
– CKPT updates the control file.
– During a log switch CKPT updates the control file
and the data file headers.
. Full checkpoints
– CKPT updates the control file and the data file headers.
– DBWn writes out all buffers on the checkpoint queue.
12、Performance Manager: Response Time
使用企业管理器进入OMS模式进行查看
13、Regulating the Checkpoint Queue
Regulate the checkpoint queue with the following initialization parameters:
. FAST_START_IO_TARGET
. LOG_CHECKPOINT_INTERVAL
. LOG_CHECKPOINT_TIMEOUT
. FAST_START_MTTR_TARGET
14、Defining and Monitoring Fast Start Checkpointing
Use v$instance_recovery to obtain the following
information:
. RECOVERY_ESTIMATED_IOS
. LOG_FILE_SIZE_REDO_BLKS
. LOG_CHKPT_TIMEOUT_REDO_BLKS
. LOG_CHKPT_INTERVAL_REDO_BLKS
. TARGET_MTTR
. ESTIMATED_MTTR
15、Online Redo Log File Configuration
. Size redo log files to minimize contention.
. Provide enough groups to prevent waiting.
. Store redo log files on separate, fast devices.
. Monitor the redo log file configuration with:
– v$logfile
– v$log
– v$log_history
16、Increasing the Performance of Archiving
. Allow the LGWR process to write to a disk different from the one the ARCn process is reading.
. Share the archiving work during a temporary increase in workload:
ALTER SYSTEM ARCHIVE LOG ALL
TO
. Increase the number of archive processes
. Change archiving speed:
– LOG_ARCHIVE_MAX_PROCESSES
– LOG_ARCHIVE_DEST_n
相关视图及参数：
v$archive_dest
v$archived_log
v$archive_processes
LOG_ARCHIVE_DEST_STATE_n
1、进程读写文件的权限
2、调优准则
3、指导方针
4、表空间的使用方法
5、Locally Managed system Tablespace
6、Monitoring Use of Files
7、File Striping
8、Tuning Full Table Scan Operations
9、Table Scan Statistics
10、监控全表扫描操作
11、Checkpoints
12、Performance Manager: Response Time
14、Defining and Monitoring Fast Start Checkpointing
15、Online Redo Log File Configuration
16、Increasing the Performance of Archiving[@more@]一、进程与文件的读写
1、进程读写文件的权限
进程 Data files Log Archive Control
CKPT Read/Write Read/Write
DBWn Write
LGWR Write Read/Write
ARCn Read Write Read/Write
SERVER Read/write Read Write Read/Write
说明：
The CKPT process only accesses the data file headers; the DBWn process writes out the actual table data.
The server process reads the redo logs, writes to the archive logs, and read/writes to the control file under certain backup and recovery operations.
2、调优准则
. 减少IO
. Spread your disk load across disk devices and controllers.
. 恰当地使用临时表空间
具体做法：
创建用户时，临时表空间要与其它数据库对象分开
表和索引使用不同的表空间
LOB类型的字段放入单独的空间
3、指导方针
.In general, to reduce the activity on an overloaded disk, move one or more of its
heavily accessed files to a less active disk.如：REDO LOG FILES、archive logs
.Try to eliminate I/O unrelated to the Oracle server on disks that contain database files.
.Knowing the types of operation that predominate in your application and the speed
with which your system can process the corresponding I/Os, you can choose the disk
layout that maximizes performance.
4、表空间的使用方法
. Reserve the system tablespace for data dictionary objects.
. Create locally managed tablespaces to avoid management issues.
. Split tables and indexes into separate tablespaces.
. Create rollback segments in their own tablespaces.
. Store very large objects in their own tablespace.
. Create one or more temporary tablespaces.
说明：
a、Each database should have separate tablespaces specified for:
Data dictionary objects
Rollback segments and undo segments
Temporary segments
Tables
Indexes
Very large objects：These objects can be CLOBs, BLOBs, tables or partitions.
5、Locally Managed system Tablespace
It is strongly recommended that all tablespaces be locally managed. Having locally
managed tablespaces eliminates extent fragmentation and provides superior
performance benefits over dictionary managed tablespaces.
CREATE DATABASE mydb
…
DATAFILE 'system01.dbf' SIZE 100M EXTENT
MANAGEMENT LOCAL
…
DEFAULT TEMPORARY TABLESPACE temp
TEMPFILE 'temp01.dbf' SIZE 15M
…;
When the system tablespace is locally managed:
. It is not possible to create dictionary managed tablespaces.
. It is not possible to migrate locally managed tablespace to dictionary managed type.
. It is possible to transport dictionary managed tablespaces into a database with
locally managed system tablespace. Note that after the transport, dictionary
managed tablespaces cannot be made read/write. The tablespaces can be migrated
to locally managed and then made read/write.
. It is possible to DROP the READ ONLY dictionary managed tablespace when SYSTEM tablespace is locally managed.
6、Monitoring Use of Files
A、 v$filestat view
. file#: File number (join to file# in v$datafile for the name)
. Phyrds: Number of physical reads done
. Phywrts: Number of physical writes done
. phyblkrd: Number of physical blocks read
. phyblkwrt: Number of physical blocks written
. readtim: Time spent doing reads
. writetim: Time spent doing writes
Note: The readtim and writetim columns contain 0 if the TIMED_STATISTICS parameter is set to False.
SQL> SELECT d.tablespace_name TABLESPACE,
2 d.file_name, f.phyrds, f.phywrts
3 FROM v$filestat f, dba_data_files d
4 WHERE f.file# = d.file_id;
B、 v$tempstat view
C、 File I/O monitor using Oracle Enterprise Manager
D、 File statistics in Statspack
7、File Striping
With OS striped database files with a stripe width close to (or less than)
DB_FILE_MULTIBLOCK_READ_COUNT x DB_BLOCK_SIZE,
8、Tuning Full Table Scan Operations
初始化参数db_file_multiblock_read_count 影响Oracle在执行全表扫描时一次读取的block的数量.
db_file_multiblock_read_count的设置要受OS最大IO能力影响，也就是说，如果 你系统的硬件IO能力有限，
即使设置再大的db_file_multiblock_read_count也是没有用 的。
理论上，最大db_file_multiblock_read_count和系统IO能力应该有如下关系:
Max(db_file_multiblock_read_count) = MaxOsIOsize/db_block_size
当然这个Max(db_file_multiblock_read_count)还要受Oracle的限制，
目前Oracle所支持的最大db_file_multiblock_read_count 值为128.
9、Table Scan Statistics
SQL> SELECT name, value FROM v$sysstat
2 WHERE name LIKE '%table scan%';
The query on the above slide provides an overview of how many full table scans are taking place.
The values for table scans (long tables) and table scans (short tables) relate to full table
scans. A long table is one with more than 4 blocks and a short table is 4 or less blocks.
If the value of table scans (long tables) is high, then a large percentage of the tables
accessed were not indexed lookups. Your application may need tuning or you should add
indexes. Make sure that the appropriate indexes are in place and valid.
10、监控全表扫描操作
A、查看长时间数据库操作的方法:
SQL> SELECT sid, serial#, opname,
TO_CHAR(start_time,'HH24:MI:SS') AS "START",
(sofar/totalwork)*100 AS PERCENT_COMPLETE
FROM v$session_longops;
查询对应SQL语句：
select a.sql_text from v$sqlarea a,v$session_longops b where a.HASH_VALUE=b.SQL_HASH_VALUE;
要求设置TIMED_STATISTICS或SQL_TRACE参数值为true。
v$session_longops视图显示运行超过6秒的操作的状态，包括备份，恢复，统计信息收集，查询等等。
V$SESSION_LONGOPS列说明：
SID：Session标识
SERIAL#：Session串号
OPNAME：操作简要说明
TARGET：操作运行所在的对象
TARGET_DESC：目标对象说明
SOFAR：至今为止完成的工作量
TOTALWORK：总工作量
UNITS：工作量单位
START_TIME：操作开始时间
LAST_UPDATE_TIME：统计项最后更新时间
TIME_REMAINING：预计完成操作的剩余时间(秒)
ELAPSED_SECONDS：从操作开始总花费时间(秒)
CONTEXT：前后关系
MESSAGE：统计项的完整描述
USERNAME：执行操作的用户ID
SQL_ADDRESS：用于连接查询的列
SQL_HASH_VALUE：用于连接查询的列
QCSID：
B、查看某些特定应用程序的长时间运行操作的信息
The dbms_application_info package contains a procedure,
set_session_longops, to populate the view from an application.
dbms_application_info.set_session_longops_nohint
11、Checkpoints
The two most common types of checkpoints are:
. Incremental checkpoints
– CKPT updates the control file.
– During a log switch CKPT updates the control file
and the data file headers.
. Full checkpoints
– CKPT updates the control file and the data file headers.
– DBWn writes out all buffers on the checkpoint queue.
12、Performance Manager: Response Time
使用企业管理器进入OMS模式进行查看
13、Regulating the Checkpoint Queue
Regulate the checkpoint queue with the following initialization parameters:
. FAST_START_IO_TARGET
. LOG_CHECKPOINT_INTERVAL
. LOG_CHECKPOINT_TIMEOUT
. FAST_START_MTTR_TARGET
14、Defining and Monitoring Fast Start Checkpointing
Use v$instance_recovery to obtain the following
information:
. RECOVERY_ESTIMATED_IOS
. LOG_FILE_SIZE_REDO_BLKS
. LOG_CHKPT_TIMEOUT_REDO_BLKS
. LOG_CHKPT_INTERVAL_REDO_BLKS
. TARGET_MTTR
. ESTIMATED_MTTR
15、Online Redo Log File Configuration
. Size redo log files to minimize contention.
. Provide enough groups to prevent waiting.
. Store redo log files on separate, fast devices.
. Monitor the redo log file configuration with:
– v$logfile
– v$log
– v$log_history
16、Increasing the Performance of Archiving
. Allow the LGWR process to write to a disk different from the one the ARCn process is reading.
. Share the archiving work during a temporary increase in workload:
ALTER SYSTEM ARCHIVE LOG ALL
TO
. Increase the number of archive processes
. Change archiving speed:
– LOG_ARCHIVE_MAX_PROCESSES
– LOG_ARCHIVE_DEST_n
相关视图及参数：
v$archive_dest
v$archived_log
v$archive_processes
LOG_ARCHIVE_DEST_STATE_n
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/594892/viewspace-927647/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/594892/viewspace-927647/
扫一扫
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
