大 db_block_size;
大; db_cache_size
使用多个块大小
多个数据库写入(DBWR)进程
大sort_area_size
大的在线重作日志
Oracle; 对象 在数据库内部(表和索引的设置可以减少物理磁盘IO) 低pctused; pctuseed的值越小,随后的sql插入中出现的io就越少
低pctfree 假如设置了pctfree,以答应在没有分割的情况下扩展所有行,那么在随后的sql选择中就会产生更少的磁盘io
使用索引将表重新组织成簇行; 假如以最常使用索引的相同物理次序放置表 3 oracle; sql
在sql语句内,有许多技术可以减少物理磁盘io 使用索引或提示(hint)防止不必要的全表搜索 使用位映射(bitmapped)索引 应用sql提示 oracle; 内部结构和磁盘io ; --查找稀疏表(自由表失去平衡!) select
substr(dt.table_name,1,10) c3,
ds.extentsc5,
ds.bytes/1048576;c4,
dt.next_extent/1048576; c8,
(dt.empty_blocks*4096)/1048576 c7,
(ds.bytes*4096)/1048576;;c6,
(avg_row_len*num_rows)/(db.blocks*4096) c10 from sys.dba_segments; ds ,
sys.dba_tables;dt
where

--调整oracle数据库实例 接下来我们要调整oracle数据库实例,以及查看所有影响性能的 参数,配置和设定 用STATAPACK检测实例潜在的性能问题 1.oracle实例概述 2.调整oracle 数据缓冲区 3.调整共享池概述 4.调整库高速缓存 5.调整oracle排序 6.调整回滚段 7.oracle 9i RAM 内存调整 通常的角度看; oracle实例包括了两个组件:; 系统全局区(SGA) 以及 oracle后台进程 我们通常通过调整oracle参数来控制SGA和后台进程 当oracle启动时 oracle就会使用malloc()命令去建立一个RAM内存区域,这个SGA通常也称为oracle区域 oracle DBA 可以控制SGA的规模; 正确的SGA治理可以极大的影响性能
尽管初始化参数成百上千
但是只有很少的oracle9i参数对调整非常重要:
buffer_pool_keep这个数据缓冲池用于存储执行全表扫描的小表
buffer_pool_recycle这个池用来保存进行全表扫描的非常大的表的表块
db_cache_size;这个参数会决定ORACLE; SGA; 中数据库块缓冲区的数量,它是oracle内存的最重要的参数
db_block_size 数据库块大小能够对性能产生(作为一个一般的规则,块尺寸越大,物理IO就越少,整体性能就越快)
db_file_multiblock_read_count; 这个参数用于全表搜索或者大表范围扫描的时候,进行多块读入
large_pool_szie 这是一个使用多线程服务器的时候,保留用于SGA使用的共享池中的非凡区域.最大池也用于并行查询RAM进程
log_buffer这个参数会决定为oracle重作日志缓冲区分配的内存数量.假如具有大量的更新活动，就应该给log_buffer分配更多的空间 shared_pool_size这个参数会定义系统中所有用户的共享池,包括SQL区域和数据字典高速缓存.
--有三个oracle参数可以影响数据缓冲区的大小
db_cache_size
buffer_pool_keep
buffer_pool_recycle oracle建议缓冲区的命中率要超过90％ DBA可以通过给初始化参数增加数据块数量来控制数据缓冲区命中率 数据库缓冲池的内部结构 --使用statspack监视缓冲池的使用
--缓冲池命中率和statpack select * from stats$buffer_pool_statistics SGA_MAX_SIZE=6000M
DB_BLOCK_SIZE=16384
DB_CACHE_SIZE=5000M
BUFFER_POOL_KEEP=(1400,3)
BUFFER_POOL_RECYCLE=(900,3)
--在oracle8; 可以使用 ALTER; TABLE CUSTOMER; STORAGE(buffer_pool; KEEP);

ALTER TABLE USER.TABLE_NAME;STORAGE(buffer_pool; keep); --高级KEEP池候选识别
除了进行全表扫描的小表之外，keep缓冲池还非常适合放置频繁使用的数据段的数据块 --使用x$bh视图来识别平均块接触次数超过5次，并且在缓存中占用超过20个数据块的对象
--
hot_buffer.sql
--识别热点对象 ; select object_typemytype,
object_name;myname ,
blocks,
count(1) buffers,
avg(tch) avg_toUChes
from
sys.x$bh a,
dba_objects; b,
dba_segments s
where
a.obj=b.object_id
and
b.object_name=s.segment_name
and
b.owner; not in('SYS','SYSTEM')
GROUP BY object_name,object_type,
blocks,obj
having; avg(tch)>5
and count(1)>20;
识别出热点对象后，可以决定将对象隔离放入keep池中
作为一般的规则，应该有足够的RAM存储可以用于整个表或者索引
列如，假如希望为keep池增加页表，就需要给init.ora的buffer_pool_keep; 参数增加104个数据块 --调整 recycle 池
在recycle池放置对象的目标是将全表搜索频率的大表进行分离，为了找到进行全表搜索的大表，我们必须求助于从
Access.sql中获得的全表搜索报告： access_recycle_syntax.sql select
'alter table 'p.owner'.'p.name' storage (buffer_pool; recyle);'
from
dba_tables t,
dba_segments s,
sqltemp s,
(select distinct
statement_id; stid,
object_owner; owner,
object_namename
from
plan_table
where
operation='TABLE ACCESS'
and
options='FULL') p
where
s.addr':'TO_CHAR(s.hashval)=p.stid
and
t.table_name=p.name
and
t.owner=p.owner
and t.buffer_pool<>'RECYCLE'
having; s.blocks>1000
group by
p.owner,p.name,t.num_rows,s.blocks
order by
sum(s.executions) desc;
--给表分配recycle池 altertableuser.table_name storage(buffer_pool; recycle); 注重：在将任何表加入到RECYCLE池之前，DBA都应该抽取sql源代码，并且验证这个查询是否获取超过了表中行的40％ --高级recycle池调整
下列查询使用了x$bh.tch来识别具有一次缓冲区接触计数，但是总量超过了整个缓存的5％的数据缓存中的对象
，这些数据段是潜在的在recycle缓冲池中放置的候选对象，因为他们可能会让不会重用的数据块占用大量的缓存空间 select object_type; mytype,
object_name; myname,
blocks,
count(1) buffers,
100*(count(1)/totsize); pct_cache
from
sys.x$bha,
dba_objects b,
dba_segments s,
()
--取消跟踪功能
alter system set trace_enabled=false;
--STATISTICS_LEVEL The STATISTICS_LEVEL parameter was introduced in Oracle9i Release 2 (9.2) to control all major statistics collections or advisories in the database. The level of the setting affects the number of statistics and advisories that are enabled: BASIC: No advisories or statistics are collected. TYPICAL: The following advisories or statistics are collected: Buffer cache advisory

MTTR advisory
Shared Pool sizing advisory
Segment level statistics
PGA target advisory
Timed statistics
ALL: All of TYPICAL, plus the following:
Timed operating system statistics
Row source execution statistics
The parameter is dynamic and can be altered using: ALTER SYSTEM SET statistics_level=basic;
ALTER SYSTEM SET statistics_level=typical;
ALTER SYSTEM SET statistics_level=all;
Current settings for parameters can be shown using: SHOW PARAMETER statistics_level
SHOW PARAMETER timed_statistics
Oracle can only manage statistic collections and advisories whose parameter setting is undefined in the spfile.
By default the TIMED_STATISTICS parameter is set to TRUE so this must be reset for it to be controled by the statistics level,
along with any other conflicting parameters: ALTER SYSTEM RESET timed_statistics scope=spfile sid='*';
This setting will not take effect until the database is restarted. At this point the affect of the statistics level can be shown using the following query: COLUMN statistics_name;;;FORMAT A30 HEADING "Statistics Name"
COLUMN session_status;;;;FORMAT A10 HEADING "SessionStatus"
COLUMN system_status;;;;;FORMAT A10 HEADING "SystemStatus"
COLUMN activation_level;;FORMAT A10 HEADING "ActivationLevel"
COLUMN session_settable;;FORMAT A10 HEADING "SessionSettable" SELECT statistics_name,
session_status,
system_status,
activation_level,
session_settable
FROMv$statistics_level
ORDER BY statistics_name;
A comparison between the levels can be shown as follows: SQL> ALTER SYSTEM SET statistics_level=basic; System altered. SQL> SELECT statistics_name,
2 session_status,
3 system_status,
4 activation_level,
5 session_settable
6; FROMv$statistics_level
7; ORDER BY statistics_name; ;;;;Session;System;;Activation Session
Statistics Name;;;;;Status;;Status;;Level;;;Settable
------------------------------ ---------- ---------- ---------- ----------

Buffer Cache Advice;DISABLEDDISABLEDTYPICAL;NO
MTTR Advice;DISABLEDDISABLEDTYPICAL;NO
PGA Advice;;DISABLEDDISABLEDTYPICAL;NO
Plan Execution Statistics;;;DISABLEDDISABLEDALL;;;;;YES
Segment Level Statistics;;;;DISABLEDDISABLEDTYPICAL;NO
Shared Pool Advice;;DISABLEDDISABLEDTYPICAL;NO
Timed OS Statistics;DISABLEDDISABLEDALL;;;;;YES
Timed Statistics;;;;DISABLEDDISABLEDTYPICAL;YES 8 rows selected. SQL> ALTER SYSTEM SET statistics_level=typical; System altered. SQL> SELECT statistics_name,
2 session_status,
3 system_status,
4 activation_level,
5 session_settable
6; FROMv$statistics_level
7; ORDER BY statistics_name; ;;;;Session;System;;Activation Session
Statistics Name;;;;;Status;;Status;;Level;;;Settable
------------------------------ ---------- ---------- ---------- ----------
Buffer Cache Advice;ENABLED;ENABLED;TYPICAL;NO
MTTR Advice;ENABLED;ENABLED;TYPICAL;NO
PGA Advice;;ENABLED;ENABLED;TYPICAL;NO

Plan Execution Statistics;;;DISABLEDDISABLEDALL;;;;;YES
Segment Level Statistics;;;;ENABLED;ENABLED;TYPICAL;NO
Shared Pool Advice;;ENABLED;ENABLED;TYPICAL;NO
Timed OS Statistics;DISABLEDDISABLEDALL;;;;;YES
Timed Statistics;;;;ENABLED;ENABLED;TYPICAL;YES 8 rows selected. SQL> ALTER SYSTEM SET statistics_level=all; System altered. SQL> SELECT statistics_name,
2 session_status,
3 system_status,
4 activation_level,
5 session_settable
6; FROMv$statistics_level
7; ORDER BY statistics_name; ;;;;Session;System;;Activation Session
Statistics Name;;;;;Status;;Status;;Level;;;Settable
------------------------------ ---------- ---------- ---------- ----------
Buffer Cache Advice;ENABLED;ENABLED;TYPICAL;NO
MTTR Advice;ENABLED;ENABLED;TYPICAL;NO
PGA Advice;;ENABLED;ENABLED;TYPICAL;NO
Plan Execution Statistics;;;ENABLED;ENABLED;ALL;;;;;YES
Segment Level Statistics;;;;ENABLED;ENABLED;TYPICAL;NO
Shared Pool Advice;;ENABLED;ENABLED;TYPICAL;NO

Timed OS Statistics;ENABLED;ENABLED;ALL;;;;;YES
Timed Statistics;;;;ENABLED;ENABLED;TYPICAL;YES 8 rows selected. SQL>
Hope this helps. Regards Tim...