大 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.extents   c5,
 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_type   mytype,
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_name   name
 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池

alter   table   user.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$bh   a,
 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 "Session|Status"
COLUMN system_status        FORMAT A10 HEADING "System|Status"
COLUMN activation_level     FORMAT A10 HEADING "Activation|Level"
COLUMN session_settable     FORMAT A10 HEADING "Session|Settable"

SELECT statistics_name,
       session_status,
       system_status,
       activation_level,
       session_settable
FROM   v$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  FROM   v$statistics_level
  7  ORDER BY statistics_name;

                               Session    System     Activation Session
Statistics Name                Status     Status     Level      Settable
------------------------------ ---------- ---------- ---------- ----------
Buffer Cache Advice            DISABLED   DISABLED   TYPICAL    NO
MTTR Advice                    DISABLED   DISABLED   TYPICAL    NO
PGA Advice                     DISABLED   DISABLED   TYPICAL    NO
Plan Execution Statistics      DISABLED   DISABLED   ALL        YES
Segment Level Statistics       DISABLED   DISABLED   TYPICAL    NO
Shared Pool Advice             DISABLED   DISABLED   TYPICAL    NO
Timed OS Statistics            DISABLED   DISABLED   ALL        YES
Timed Statistics               DISABLED   DISABLED   TYPICAL    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  FROM   v$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      DISABLED   DISABLED   ALL        YES
Segment Level Statistics       ENABLED    ENABLED    TYPICAL    NO
Shared Pool Advice             ENABLED    ENABLED    TYPICAL    NO
Timed OS Statistics            DISABLED   DISABLED   ALL        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  FROM   v$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...

查看本文来源

--内存调整

select * from v$sga;

--调整前SGA

NAME                      VALUE
-------------------- ----------
Fixed Size               452184
Variable Size         402653184
Database Buffers      251658240
Redo Buffers             667648

select * from v$sgastat;

POOL        NAME                            BYTES
----------- -------------------------- ----------
            fixed_sga                      452184
            buffer_cache                251658240
            log_buffer                     656384
shared pool errors                           8940
shared pool enqueue                        171860
shared pool KGK heap                         3756
shared pool KQR M PO                      1393788
shared pool KQR S PO                       177272
shared pool KQR S SO                         5120
shared pool sessions                       410040
shared pool sql area                     61446860

POOL        NAME                            BYTES
----------- -------------------------- ----------
shared pool 1M buffer                     2098176
shared pool KGLS heap                     2613480
shared pool PX subheap                      19684
shared pool parameters                      39012
shared pool free memory                 125812664
shared pool PL/SQL DIANA                  3445584
shared pool FileOpenBlock                  695504
shared pool PL/SQL MPCODE                  637644
shared pool PL/SQL PPCODE                   48400
shared pool PL/SQL SOURCE                   14344
shared pool library cache                19376952

POOL        NAME                            BYTES
----------- -------------------------- ----------
shared pool miscellaneous                 8639216
shared pool PLS non-lib hp                   2068
shared pool joxs heap init                   4220
shared pool table definiti                   2632
shared pool trigger defini                   1128
shared pool trigger inform                    528
shared pool trigger source                    624
shared pool Checkpoint queue               564608
shared pool VIRTUAL CIRCUITS               265160
shared pool dictionary cache              1614976
shared pool KSXR receive buffers          1032500

POOL        NAME                            BYTES
----------- -------------------------- ----------
shared pool character set object           432136
shared pool FileIdentificatonBlock         319452
shared pool message pool freequeue         833032
shared pool KSXR pending messages que      840636
shared pool event statistics per sess     1908760
shared pool fixed allocation callback         268
large pool  free memory                  83886080
java pool   free memory                  83886080

41 rows selected.

--UGA的大小,UGA主要包含一下部分的内存设置

show parameters  area_size;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
bitmap_merge_area_size               integer     1048576
create_bitmap_area_size              integer     8388608
hash_area_size                       integer     1048576
sort_area_size                       integer     524288
workarea_size_policy                 string      AUTO

--计算数据缓冲区命中率

select value from v$sysstat  where name='physical reads'  4383475

select * from v$sysstat  where name='physical reads direct'   3834798

select * from v$sysstat  where name='physical reads direct (lob)'  374616

select * from v$sysstat  where name like 'consistent gets'  1198738167

select * from v$sysstat  where name like 'db block gets'  53472785

x=physical reads direct+physical reads direct (lob)

100-(physical reads-x)/(consistent gets+db block gets-x)*100

100-(4383475-3834798-374616)/(1198738167+53472785-3834798-374616)*100

--共享池的命中率
select sum(pinhits)/sum(pins)*100  "hit radio" from v$librarycache;

--关于排序部分

select name,value  from v$sysstat where name like '%sort%';

select sorts(disk)/(sorts (memory)+sorts(disk)) from dual

select 0/(17038425+0) from dual

--关于log_buffer

select name,value from v$sysstat 
where  name in('redo entries','redo buffer allocation retries');

redo buffer allocation retries/redo entries  >1%  考虑增加log_buffer

--其他视图
v$db_cache_advice、v$pga_target_advice、v$java_pool_advice 和 v$db_shared_pool_advice

查看本文来源