服务热线:13616026886

技术文档 欢迎使用技术文档,我们为你提供从新手到专业开发者的所有资源,你也可以通过它日益精进

位置:首页 > 技术文档 > 数据库技术 > Oracle技术 > Oracle开发 > 查看文档

数据库维护

第一章 数据库维护

1.1 ORACLE数据库用户和权限管理

1.1.1 用户管理

1、创建一个ORACLE用户必须拥有DBA权限
2、创建用户语法如下:
svrmgrl > create user username identified by password
default tablespace tablesapce_name
temporary tablespace tablespace_name
quota [size]/[unlimited] on tablespace_name
profile [default]/[profile_name];
删除用户语法如下:
svrmgrl > drop user username cascade;
修改用户语法如下:
svrmgrl > alter user username [identified by passwd]
[default tablespace tablespace_name]
[temporary tablespace tablespace_name]
[profile default/profile_name]
3、注:除非有特殊需要,否则严禁将用户建在系统表空间

1.1.2 权限管理

1、ORACLE的安全性分成以下几个级别:
1)合法用户的帐户安全性
ORACLE对所创建的每一个用户的口令均以加密的方式存储在数据字典中。
2)2数据库对象的访问安全性
在数据库中访问一个对象是通过权限来完成的。
例如:
grant select on emp to public;       ----提供emp表的查询权限给所有用户
grant insert on emp to scott;  ----提供emp表的插入权限SCOTT用户
或者创建一个角色(role):
create role new_role;                    ----创建一个角色new-role 
grant select on emp to new_role;   ----提供emp表的查询权限给new-role角色
grant select on dept to new_role;
grant new_role to scott;         ----授与scott用户角色new-role
3)ORACLE系统级权限
如:
CREATE [ALL] [CLUSTER/INDEX … ]
       ALTER [ALL] [CLUSTER/INDEX …]
       SYSDBA                              ----可以执行象建立数据库这样的所有系统管理功能
       SYSOPER                           ----执行数据库关闭/启动、备份有关的系统管理功能
       … …
4)ORACLE提供了8个系统级角色
CONNECT                                  ----最终用户的基本系统权限,允许创建一个新的会话
RESOURCE                        ----开发商的基本系统权限,拥有创建表、索引、存储
  过程等数据库对象的权限
IMP_FULL_DATABASE              ----允许进行数据库的完整输入(imp)
EXP_FULL_DATABASE             ----允许进行数据库的完整输出(exp)
DBA                                          ----所有系统级权限
EXECUTE_CATALOG_ROLE      ----允许用户执行数据字典中部分过程和函数
SELECT_CATALOG_ROLE  ----允许用户在部分数据字典中进行查询操作
DELETE_CATALOG_ROLE        ----允许用户从表SYS.AUD$中删除数据    
2、用户环境文件
设定环境文件(profile文件)可以限制系统和数据库资源对用户开放,对管理口令进行管理、终止、重新使用、限制等。在创建一个用户时。使用环境变量可以最大限度利用系统资源,提高系统的安全性。
系统中­使用的默认环境文件,它对所有的用户开放,没有进行资源的限制。
例如:
        svrmgrl > create profile new_profile
cpu_per_session 20
session_per_user 1
idle_time 30;
session_per_user                          ----一个用户可以同时拥有的对话数
cpu_per_user                               ----一个对话可以使用数个百分之一秒
cpu_per_call                               ----语法分析、执行、取数可使用数个百分之一秒
logical_reads_per_second             ----一个会话可以读取的ORACLE块最大数
idle_time                                    ----无键盘活动最长时间(分钟)
connect_time                               ----一个会话连接最长时间(分钟)
failed_login_attempts                   ----导致帐户加锁的连续登录失败次数
password_left_time                      ----口令可以使用天数
password_reuse_time                    ----口令再次生效所需要天数
 

1.1.3 审计

数据库具有审计发生在其内部的所有动作的能力,审计记录写入SYS.AVD$或操作系统的审计trace文件。数据库可以审计三种操作:登录企图、对象访问和数据库操作。
1、设定AUDIT_TRAIL参数:
AUDIT_TRAIL=   OS                 ----写入操作系统trace文件
                                   DB                ----写入数据库SYS.AVD$
                                   NONE            ----不进行审计
2、审计登录
audit session;                             
audit session whenever successful; ----仅审计登录成功的会话
audit session whenever not successful;
noaudit session;                                  ----禁止审计
3、操作审计
audit role;
例如:
audit alte index ;
4、对象审计
例如:
audit insert on scott.emp;
audit all on scott.dept;

1.2 ORACLE数据库空间管理

1.2.1 表空间管理

1、管理表空间必须有DBA权限
2、创建表空间语法:
svrmgrl> create tablespace TABLESPACE_NAME datafile ‘FULL_PATH_FILENAME’
> reuse size SIZE online;
   例如:
       svrmgrl> create tablespace test datafile ‘/oracle/dbf/test1.dbf’ reuse size 100m online;
   修改表空间:
       svrmgrl> alter tablespace test offline;
       svrmgrl> alter tablespace test add datafile ‘/oracle/dbf/test2.dbf’ size 100m;
删除表空间:
1)删除建在该表空间上的用户(删除建在该表空间上的回滚段)
2)使该表空间offline
3)Svrmgrl > drop tablespace test cascade;
3、如果数据文件基于raw device(裸设备),则必须先在操作系统下利用操作系统命令创建好文件后再建表空间,修改该文件的用户及属组,同时根据操作系统的不同,创建的表空间需要比生成的裸设备小64K不等(为便于管理,我们建议表空间比生成的裸设备小1m):
例如:
AIX操作系统下,先用smit创建一个raw device ‘/dev/rrbs’ 大小为101m
# chown oracle:dba /dev/rrbs
Svrmgrl > create tablespace rbs datafile ‘/dev/rrbs’ size 100m online;
4、定时对数据库空间的使用情况进行跟踪:
例如:查询各表空间剩余空间大小:
svrmgrl > select sum(bytes/1024/1024),tablespace_name
from dba_free_space
group by tablespace_name;

1.2.2 回滚段管理

1、创建回滚段语法
svrmgrl > create rollback segment r01 tablespace rbs
storage (initial 20k next 20k optimal 3m);
修改回滚段:
svrmgrl > alter rollback segment r01 offline;
svrmgrl > alter rollback segment r01 storage ( next 128k optimal 4m);
initial不能修改
删除回滚段:
1)该回滚段不正被任何一个事务使用
2)Offline该回滚段
3)Svrmgrl > drop rollback segment r01;
4)修改初始化文件init.ora,修改rollback_segment=(r01,r02,r03,…)=>删除r01
2、使用optimal参数使回滚段自动回缩
3、定时监控数据库回滚段使用情况
例如:
SQL> select rn.name,rs.gets ,rs.waits,(rs.waits/rs.gets)*100 ratio from
  2   v$rollstat rs,v$rollname rn where rs.usn=rn.usn
  3  /
NAME                                GETS     WAITS     RATIO
------------------------------------------------------------------ --------- --------- ---------
SYSTEM                               663         0         0
R01                                 1506         0         0
R02                                 2061         0         0
R03                                 1781         0         0
R04                                 1660         0         0
查询到5记录.
如果结果的比例大于百分之二,则在回滚段中存在竞争,应该用Create rollback segment语句创建更多的回滚段。创建更多的回滚段可以减少多个进程同时访问相同回滚段的机会。一个非常好的原则是每四个并发任务建立一个回滚段。在确定回滚段大小时应分析在数据库上所执行的任务的类型。OLTP任务写的数据较多,需要较大的回滚段。执行大量查询所写数据不多的DSS决策支持系统可以使用较小的回滚段。

1.3 ORACLE数据库日常操作及监控

1、  数据库关闭
数据库关闭应该尽可能采用shutdown normal或shutdown immediate命令关闭,除非万不得已,不要采用shutdown abort命令关闭数据库。正确关闭数据库步骤:
1)停止监听进程
$ lsnrctl stop
2)查看系统中有无活动进程
svrmgrl > select username,sid,serial# from v$session where status=’ACTIVE’
           > and username is not null;
3)关闭数据库
svrmgrl > shutdown [immediate]
2、  数据库启动
在OPS下,启动数据库步骤:
1)在双机上启动DLM锁管理进程
例如:
$ opsctl start
2)在双机上启动并行数据库
svrmgrl > startup parallel
3)启动监听进程
$ lsnrctl start
3、  检查alertSID.log文件。Oracle会将系统的主要事件和错误写在alert.log文件中,应该每天查看alert.log文件并将查看过的文件删除或作为历史记录存放到别处。

1.4 ORACLE数据库日常监控

1、  检查alertSID.log文件。Oracle会将系统的主要事件和错误写在alert.log文件中,应该每天查看alert.log文件并将查看过的文件删除或作为历史存放到别处。
2、  用utlbstat/utlestat产生数据库性能报告,在报告中应该主要查看下列项目:
1) library 的gethitratio建议值>0.99,否则需要增加shared-pool-size。
2) db block buffer的命中率
HITS=100*((db block gets + consisten reads)/(db block gets + consistent reads) + physical reads)
建议值>0.8,否则需要增加db-block-buffers
3) fileI/O,(files)各个磁盘组上的I/O应该基本均衡,如果有很大差异,检查数据库的物理设计。
4) 在OPS环境下,需要检测lock hit ratio 和 ping write ratio
SQL> select name,value from v$sysstat where
name in (‘db block gets’, ‘consistent gets’,’physical_reads’);
 
计算命中率:
Lock hit ratio =
  ((consistent gets ?C global lock converts(anync))/consistent gets)
          Ping write ratio =
DBWR cross instance writes / physical writes)
 
在单机情况下,lock hit ratio =1,ping write ratio = 0
在OPS下,两者均为0-1之间的值,如果lock hit ratio 降低或ping write ratio 升高,则需要重新检查数据库的OPS设计。
5) 通过对v$sysstat等视图进行查询
6) 将报告和以前的报告进行比较,对其中显著不同的进行分析。
3、  对应用的SQL 语句进行分析,将结果反馈给开发者。
详见数据库性能检测和优化
4、  对OPS来说,必须经常查看v$ 表观察instance 在数据上 的冲突,于OPS有关的表有v$bh,v$ping,v$false_ping等。利用下列的命令可以检查各个datafile 上的冲突情况:
SQL> select file#,sun(xnc)
>from v$bh/v$ping/v$false_ping
>group by file#
 
Instance之间的冲突表现为PCM锁的转换,转换次数(SUM(XNC))多的datafile即是冲突比较多的datafile。
5、  为了系统能够更好地进行运行,对数据库中的表和索引应该经常进行统计和检查,方法是对所有的表和索引用如下命令:
SQL> analyze index/table <name> compute statistics;
 
进行统计,统计的结果将存放如Oracle的系统表中,Oracle Optimazer在制定SQL执行计划时会根据统计的结果进行优化。
用如下的命令对表和索引的存储结构进行检查:
SQL> analyze index/table <name> validate structure
 
6、  对某些SQL语句直接利用执行计划进行分析,可以了解该SQL语句的执行效率。
SQL > explain plan for <SQL 语句>
SQL > select options,cost,operations from plan_table;
 
如果发现某些SQL语句使用全表扫描,通过增加索引或修改SQL语句使之能够利用到索引,可以大大提高查找的速度。
7、  对ORACLE的错误,采用如下命令进行简单定位:
$ oerr ora error-no
如果出现的错误是ORA-00600错误或ORA-07445等错误则需要通知ORACLE技术中心以取得解决。
详见数据库故障处理。
8、  如果得到ORACLE公司的新补丁,按照以下步骤进行:
1)关闭数据库
2)执行如下命令:
$ sh patch.sh
 
3)重新启动数据库
4)如果发现在打完补丁后情况更糟,则关闭数据库后执行如下命令:
$ sh undo_prePATCHNO.sh

1.5 备份与恢复

1.5.1 数据库备份策略

数据库的备份方案有以下几种:
l       全部或部分卸出备份(exp)
l       增量卸出备份
l       冷备份(映象备份)
l       热备份
l       归档备份(备份archive 日志文件)
l       整个文件系统的复制(不适用于数据文件为raw device数据库备份)
建议数据库的备份采用冷备份(热备份)+ 归档备份与export卸出备份相结合的方式。
1、归档备份:
l       修改$ORACLE_HOME/dbs/init$ORACLE_SID.ora 增加如下参数:
log_archive_start = true
 
l       svrmgrl > startup mount
       svrmgrl > alter database archive log;
svrmgrl > select * from v$database;
NAME   CREATED     LOG_MODE   CHECKPOINT  ARCHIVE_CH
---------------------------------------------------------------------------------------------------------------
ORA1  07/19/97 07:47:48  ARCHIVELOG  40490076      40434464
1 row selected. 
 
2、冷备份
关闭数据库后进行的数据库文件的拷贝备份。相对于热备份,冷备份还必须备份数据库的在线日志文件(redolog文件)。
3、热备份
用数据库的“alter tablespace system begin backup”命令将数据库表空间处于开始备份模式下对数据库文件进行备份,在数据库文件备份工作完成后用相应命令使相应表空间结束备份状态。
4、卸出备份
采用数据库的备份命令exp,适用于对某些重要数据的单独备份。
5、整个文件系统的复制
该备份方法只适合于基于文件系统的数据库,效果较差。
6、建议数据库的备份采用以上几种备份方式相结合。
7、建议数据备份至少保存一个月;保存一个月归档日志文件。采取每周备份一次数据库(完全),每天备份生成的archive日志文件的方法进行数据库的备份;同时采用数据库的卸出(exp)备份方法备份数据库中重要数据(按用户或按表)。
8、控制文件的备份
              svrmgrl > alter database backup controlfile to ‘/usr/con.bak’
       或   svrmgrl > alter database backup controlfile to trace;
 
(在$ORACLE_BASE/admin/$ORACLE_SID/udump下将生成一个trace文件,编辑该文件为一个sql脚本。
Example:
 
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "YY1" NORESETLOGS NOARCHIVELOG
              MAXLOGFILES 32
              MAXLOGMEMBERS 2
       MAXDATAFILES 256                   ---最大数据文件数
              MAXINSTANCES 8
              MAXLOGHISTORY 800
LOGFILE
             GROUP 1 '/u01/log1'  SIZE 10M,
             GROUP 2 '/u01/log2'  SIZE 10M,
             GROUP 3 '/u01/log3'  SIZE 10M,
         DATAFILE
             ‘/u02/system.dbf',
             '/u02/rbs.dbf',
             '/u02/temp.dbf',
             '/u02/tools.dbf',
             '/u02/usr.dbf';
;
# Recovery is required if any of the datafiles are restored backups,
# or if the last shutdown was not normal or immediate.
RECOVER DATABASE
# Database can now be opened normally.
由于数据库并未受到破坏,无需对库进行恢复,因而以上这些语句可以删除。
ALTER DATABASE OPEN;

1.5.2  ORACLE恢复方案

1、用户表空间
故障现象:
在启动数据库时出现ORA-01157,ORA-01110或操作系统级错误例如ORA-07360,在关闭数据库(使用shutdown normal或shutdown immediate) 时将导致错误ORA-01116,ORA-01110以及操作系统级错误ORA-07368。
解决方法:
以下有两种解决方案:
l       用户的表空间可以被轻易地重建
即最近导出的对象是可用的或表空间中的对象可以被轻易地重建等。在这种情况下,最简单的方法是offline并删除该数据文件,删除表空间并重建表空间以及所有的对象。
              svrmgrl> startup mount
              svrmgrl> alter database datafile filename offline drop;
              svrmgrl> alter database open;
              svrmgrl> drop tablespace tablespace_name including contents;
 
l       用户的表空间不能够被轻易地重建
在大多数情况下,重建表空间是不可能及太辛苦的工作。方法是倒备份及做介质恢复。如果您的系统运行在NOARCHIVELOG模式下,则只有丢失的数据在online redo log中方可被恢复。
步骤如下:
       1)Restore the lost datafile from a backup
       2)svrmgrl> startup mount
       3)svrmgrl> select v1.group#,member,sequence#,first_change#
         > from v$log v1,v$logfile v2
                      > where v1.group#=v2.group#;
       4)如果数据库运行在NOARCHIVELOG模式下则:
         svrmgrl> select file#,change# from v$recover_file;
如果CHANGE# 大于最小的FIRST_CHANGE#则数据文件可以被恢复。
如果CHANGE# 小于最小的FIRST_CHANGE#则数据文件不可恢复。恢复最近一次的全备份或采用方案一。
       5)svrmgrl> recover datafile filename;
       6)确认恢复成功
      7)svrmgrl> alter database open resetlogs;
只读表空间无需做介质恢复,只要将备份恢复即可。唯一的例外是:
           表空间在最后一次备份后被改为read-write 模式
           表空间在最后一次备份后被改为read-only  模式
在这两种情况下,均需进行介质恢复
2、临时表空间
临时表空间并不包含真正的数据,恢复的方法是删除临时表空间并重建即可.
3、系统表空间
如果备份不可用,则只能采用重建数据库的方法
4、回滚表空间
有两种情况
l       数据库已经完全关闭(使用shutdown immediate或shutdown命令)
    1) 确认数据库完全关闭
    2) 修改init.ora文件,注释"rollback-segment"
    3) svrmgrl> startup restrict mount
    4) svrmgrl> alter database datafile filename offline drop;
    5) svrmgrl> alter database open;
     基于出现的结果:
      "statement processed" 转(7)
       "ORA-00604,ORA-00376,ORA-01110"转(6)
    6) svrmgrl> shutdown immediate
    修改init.ora文件,增加如下一行:
    _corrupted_rollback_segments = (<roll1>,...<rolln>)
     svrmgrl> startup restrict
    7) svrmgrl> drop tablespace tablespace_name including contents;
    8) 重建表空间及回滚段
    9) svrmgrl> alter system disable restricted session;
   10) 修改init.ora文件
l       数据库未完全关闭(数据库崩溃或使用shutdown abort命令关闭数据库)
    1) 恢复备份
    2) svrmgrl> startup mount
    3) svrmgrl> select file#,name,status from v$datafile;
      svrmgrl> alter database datafile filename online;
    4) svrmgrl> select v1.group#,member,sequence#,first_change#
                      > from v$log v1,v$logfile v2
  > where v1.group#=v2.group#;
    5) svrmgrl> select file#,change# from v$recover_file;
见第一种情况“数据库已经完全关闭”中第2)~4)步。
    6) svrmgrl> recover datafile filename;
    7) svrmgrl> alter database open;
l       数据库处于打开状态
   1) 删除回滚段和表空间
   2) 重建表空间和回滚段
5、控制文件恢复
l       所有的控制文件均被破坏,且备份完好
   将备份的控制文件拷贝至原目录下
   对于raw device,则:
dd if='con.bak' of='/dev/rdrd/drd1' seek=128(根据操作系统不同,有所不同)
l       所有控制文件均被破坏,且无备份
       重建控制文件
l       并非所有的控制文件均被破坏
   关闭数据库,复制控制文件,重新启动数据库

1.6 数据库优化

1.6.1 数据库优化过程

调整数据库的性能必须有一个明确的目标,总的来说可以是以下的几个目标之一或多个:
l       改善指定类型的SQL语句的性能
l       改善专门的数据库应用的性能
l       改善所有同时应用数据库的用户及其应用的所有性能
在调整ORACLE性能之前,首先要有一个性能良好的应用设计及高效的SQL语句,在此基础上调整ORACLE性能的过程有三步:
l       调整内存分配
l       调整I/O
l      调整资源争用

1.6.2 数据库优化内容

1.6.2.1 ORACLE系统的准备知识

1、  ORACLE数据库系统的数据存储的物理结构和逻辑结构构成
2、  模式对象的组成
3、  ORACLE数据库系统的进程以及内存结构构成
4、  ORACLE锁的概念介绍
5、  二阶段提交的概念
6、  用户、角色、权限的概念的介绍
7、  ORACLE处理一个事务步骤:
l       首先必须有一台主机或数据库服务器运行一个ORACLE INSTANCE。
l       一台本地机器或客户端工作站运行一个应用,它试图通过适当的SQLNET驱动同服务器取得联系
l       如果该服务器也正在运行适当的SQLNET驱动,服务器检测到应用的连接请求,开始为此用户进程创建一个专用的服务器进程。
l       客户端的用户执行一个SQL语句并提交此进程。
l       服务器进程收到此SQL语句,并开始检验在ORACLE的共享池中是否存在同样的SQL语句。如果在共享池中发现该SQL语句,服务器进程开始检验该用户是否对请求的数据有操作的权限,然后使用在共享池中的SQL语句去执行该语句。如果该SQL语句在共享池中不存在,就为此语句分配一个新的共享池区以便它能够被解析、执行。
l       服务器进程从实际的数据文件或共享池中取回必需的数据。
l       服务器进程在在共享池中修改数据。在上述所作的生效之后,DBWR后台进程把修改后的数据块永久的写入硬盘。在此事务提交成功之后,LGWR进程立即把此事务记录到在线的redo log file。
l       如果此事务成功,服务器进程通过网络返回一个成功的信息给应用程序。如果该事务不成功,将返回一个适当的信息。
l      在上述的事务过程中,其余的后台进程同样在运行,等待着条件符合而被触发。此外,数据库服务器还管理着其他用户的事务,并且在不同事务之间提供数据一致性,防止不同事务对相同数据操作

1.6.2.2 在安装数据库时作的优化

在数据库安装时作的优化工作主要是关于DB_BLOCK_SIZE参数的设置,该参数决定了ORACLE每次操作多少的数据。该参数在安装时一经确认就不能修改,除非重新安装数据库。对于一个中型的应用系统,它的DB_BLOCK_SIZE大小设为4K,而对于一个较大型的应用而言,它的DB_BLOCK_SIZE一般设为8K或更大一点为16K。
对于一­个较大的DB_BLOCK_SIZE,可以加快系统的运行速度,(因为从系统的I/O吞吐能力来说,一次性读取较多的数据可以比一次性读取较少的数据的过程减少I/O的读取次数)而且可以有较大的系统扩展能力。
DB_BLOCK_SIZE和MAX EXTENTS的对应关系如下:
DB_BLOCK_SIZE
MAX EXTENTS
512BYTES
25
1K
57
2K
121
4K
249
8K
505

1.6.2.3 对INITXXX.ORA文件的优化

在安装之后,在数据库初始化时必须对INITXXX.ORA文件作优化。
l       对于SHARE_POOL_SIZE的设置:对于不同的系统根据用户对于内存区的要求,考虑用户是否需要多少的内存空间存放用户的存储过程或要多少空间存放用户要编译的程序。
l       对于需要进行大量数据操作的用户可考虑增大用户的DB_BLOCK_BUFFERS的数目,该参数可以使用户在缓冲区中的数据较大,使用户查找的数据尽可能的在缓冲区中,不要到表中去再次查找。
l       根据用户的实际需要,设置较好的PROCESS该参数决定能够有多少个用户在系统中运行,如果该参数设置不当会导致用户无法正常运行。并且该参数与操作系统的参数有关,该类型的参数限制了每个用户允许最大多少用户登录的限制。
l       根据用户实际使用系统的SQL语句的多少,决定最终要开的OPEN_CURSORS数目的多少,因为一个SQL的DML语句就是一个隐含的CURSOR,如果上述参数的数目开的不够大的话,系统会提示用户的SGA区不足,导致系统出错。
l       对于要进行大量数据分组和排序工作的应用要加大系统的SORT_AREA_SIZE的大小,该参数决定分配给每一位用户的排序空间,该参数用到系统的内存空间。
l       为保证系统能够正常运转,要保证系统有足够的DML_LOCKS,如果该值不够的话,会导致系统发生中断,半途终止系统。
l       为保证系统能够有足够的数据库链路可用,要保证OPEN_LINKS的数目足够大。
l       设置shadow_core_dump=none避免生成core文件填充系统空间使数据库系统崩溃。

1.6.2.4 在进行空间设计时作的优化

表空间设计的原则为:
l       把由用户创建的其余表空间同SYSTEM表空间进行分离
l       最重要的原则是把系统的数据表空间同索引表空间分离
l       把操作频繁和不经常操作的表划分在不同的表空间中
l       把用户数据与数据字典数据分开
l       分离用户数据和回滚段的数据以防止某个磁盘出现故障丢失数据。
l       为特殊类型的数据库使用保留某个表空间
l      在数据库的表空间设计时,建议每一个表空间对应的数据物理文件的大小应该小于1G大小。(因为某些系统的操作系统不支持大于2G大小的文件。同时,从数据库的备份角度考虑,对小文件的备份不仅可以提高备份的速度,也可以提高备份的安全性)

1.6.2.5 在系统设计时作的优化

1、  在这里先讨论以下系统的物理存储结构,在ORACLE系统中,数据的存储是以BLOCK为基本单位,一个BLOCK的打下为系统在安装时确认下的DB_BLOCK_SIZES的大小。多个BLOCK构成一个EXTENT,多个EXTENT构成一个SEGMENT。
现介绍一下关于一个BLOCK的结构:
公用的变长标题(common and variable header)    A
表目录(table directory)                             B
行目录(row directory)                                        C
未用空间(free space)                                                 D
行数据(row data)                                               E
现对上述结构中用到的每一个段进行说明:
1) A部分包含一般的块信息,如块地址、段的类型(数据段、索引段或回滚段等)。
2) B部分包含所存放的表的信息。
3) C部分包含块中的行信息(如行数据区中的每一行的行地址)。
4) D部分包含可用于插入新行及修改
5) E部分包含在表或索引数据。需要的附加空间的行或额外的事务头。
2、  建表
对于建表而言,虽然同一张表有相同的表结构,但是由于有不同的存储策略,最终会导致不同的性能结果。
CREATE TABLE [schema.]table
     ( { column datatype [DEFAULT expr] [column_constraint] ...
       | table_constraint}
    [, { column datatype [DEFAULT expr] [column_constraint] ...
       | table_constraint} ]...)
    [ [PCTFREE  integer] [PCTUSED  integer]
      [INITRANS integer] [MAXTRANS integer]
      [TABLESPACE tablespace]
      [STORAGE storage_clause]
    [  PARALLEL ( [ DEGREE { integer | DEFAULT } ]
                  [ INSTANCES { integer | DEFAULT } ]
                )
     | NOPARALLEL ]
    [  CACHE | NOCACHE  ]
    | [CLUSTER cluster (column [, column]...)] ]
    [ ENABLE   enable_clause
    | DISABLE disable_clause ] ...
    [AS subquery]
 
l       PCTFREE和PCTUSED两个参数的使用:
PCTFREE参数为块中保留的未用空间的百分数,用于修改已存在块中的行。该块保留出的空间用于数据的修改使用。
PCTUSED当一数据块为填满后(由PCTFREE决定),该块不能考虑插入新行,但是当它使用低于PCTUSED参数时,又可插入。在达到PCTUSED值之前,该未用空间仅用于修改行。上述两个参数的值的总和不能超过100。
l       INITRANS参数的使用
该参数决定了同时能够允许多少个用户对表或索引进行处理,也就是指定表的每一数据块中分配的事务项的初值。由于ORACLE在对表或索引进行操作之前,对每一数据块的修改,都要在块中申请一个事务项,该事务项的值的大小依赖于OS。该参数保证可同时并发修改块的最小事务数,它避免动态事务项的开销。
l       STORAGE参数的使用:
INITIAL:该参数决定了ORACLE系统初始分配给该表的空间的大小,对于该参数的值应该尽可能的能够容纳该表的所有数据。以尽量避免该表进行无谓的扩展。ORACLE公司建议把对表的扩展控制在5个以下。因为太多的扩展将造成I/O开销的增加。
NEXT:该参数决定了一张表在INITIAL空间用完之后,系统分配给该表多大的空间。
PCTINCREASE:该参数影响到系统的存储空间的使用问题,经常由于该参数设置不当的缘故,导致存储空间的无谓的浪费。例如,如果一张表的PCTINCREASE为50的话,那么在上一个NEXT用完之后,它将按照一定的几何比例进行扩展。具体公式如下:next*(1+pctincrease)…,该表扩展几次,就乘以(1+PCTINCREASE)几次。如果没有特殊的要求,建议把该参数值设置为0。
l       对表的空间大小的确定
对于表大小空间的确定可以进行估算和精确计算,现以2K字节大小的数据块进行一张表大小的估算,估算公式如下:
 
greatest(4,ceil(ROW_COUNT/((round((1958-(initrans*23))*((100-PCT_FREE)/100))/ADJ_ROW_SIZE))))*2
 
其中:
1) 一个块的实际可用字节数为1958BYTE。
2) 每个INITRANS使用23BYTE
3) PCT_FREE为建表时要指定的值
4) 表中每行估计的已调试的行大小(ADJ_ROW_SIZE)
5) 表的估计行数(ROW_COUNT)
对于公式中用到的函数greatest,ceil,round,其中greatest函数取多个值中的最大值,ceil取加1的整数,round具有四舍五入的功能。
对于已有数据的旧的数据库结构中,对于表的空间大小的计算公式如下:
select avg(nvl(vsize(col1),0)) +avg(nvl(vsize(col2),0)) +…+ avg(nvl(vsize(coln),n)) from table_name
通过对现有表结构以及数据量的分析,可以准确的确定现有的数据结构。以分析出来的数据量为依据,可以准确的确定表的存储参数。通过制定行之有效的表的存储策略,可以提高应用的效率以及利用存储空间的效率。
3、  建索引
索引本身也是一个数据库对象。对于在表的索引列上的每一值将包含一项(ENTRY),为行提供直接的快速存取。索引的存储参数的取值和使用同表的存储参数的使用一致。
l       使用索引的优缺点
在下列情况下ORACLE可利用索引改进性能:按指定的索引列的值查找行,按索引列的顺序存取表。然而,索引虽然可以加快查询的速度,但是它减慢了INSERT、DELETE和UPDATE语句的执行速度,由于这些操作要影响索引列的值,ORACLE必须对索引数据和表数据进行维护。
l       创建索引的限制
一个索引最多可包含16列,索引项为每一列的数据值的连串,按指定的列的顺序连串。这一顺序对ORACLE如何使用该索引非常重要。ORACLE在一张表上可建立多个索引,索引的数目没有限制。但是应该注意增加索引会增加维护表所需的处理时间。建议只有当要索引的数据在表中所占的数据量,少于总数据量的25%时,使用索引才会提高查询的速度,否则对表的全表扫描的速度将比使用索引的速度快。
l       创建索引的原则
对于索引列的选择遵从下列的准则:
1) 经常用于WHERE子句中使用的列考虑作索引的列。
2) 经常用于SQL语句中连结表的列考虑作为索引的列。
3) 一个索引列要具有好的选择性。一个索引的选择性(Selectivity)是对具有相同值的表中的百分比。一个索引的选择性好,就是很少行有相同值。可以通过用具有不同索引值的数目除表中的行数来决定索引的可选择性。可以用ANALYZE命令获取这些值。用这种方法计算的可选择性应该解释为百分比。
4) 不要将具有很少不同值的列作为索引列。这样的列具有很差的选择性,并且不能优化性能,除非频繁选择的值比其他列值来更少出现。例如:在表中如果存在以YES或NO为值的列,那么尽量不要用该列作为索引列,因为用此列作为索引不会提高系统的性能,但是如果在表中YES的值很少出现,而在运用中又经常以YES值为查询,则以该列为索引可能会改进系统的性能。
5) 不要将频繁修改的列作为索引列。因为修改索引列的UPDATE语句和修改索引表的INSERT和DELETE语句将比没有索引要用更多的时间。这样的语句必须修改索引中的数据,还要修改表中的数据。
6) 不要将只出现在带函数或操作符的WHERE子句中的列作为索引列。使用带索引列的函数(不是MIN或MAX函数)或操作符的WHERE语句并不使用索引的存取路径。
7) 在大量并发INSERT、UPDATE和DELETE语句存取父表和子表的情况下,考虑对参考完整性约束的外部键作索引。也就是说对表的外键在可能的情况下创建索引以加快操作的速度,改进性能。因为如果对于一张表而言,如果在它的外键上没有索引的话,那么在对它操作的同时,ORACLE将对此外键对应的主表加一个表级锁,以保证数据的完整性和一致性。如果在外键上有索引的的话,那么它就可以通过索引找到主表的ROWID,只对主表加一个行级锁。
l       索引空间大小的确认
 
greatest(4,1.01*(ROW_COUNT/((floor(((2048-113-(initrans*23))*(1-(pct_free/100)))/((10+uniqueness)+number_col_index+(total_col_length))))))*2)
 
其中:
1) 表的估计行数(ROW_COUNT)
2) 块的实际可用行数(1935或2048-113)
3) 为索引指定的自由百分比(PCT_FREE)
4) 索引是否是唯一的,对字节数是有影响的(如果索引是唯一的,UNIQUENESS变量为1,否则为0)
5) 索引的列数(NUMBER_COL_INDEX)
6) 索引的估计长度(NUMBER_COL_LENGTH)
4、  回滚段的设计
回滚段在系统中发挥的作用是不容忽视的。一个系统如果没有回滚段,那么系统根本无法运行。ORACLE系统的二阶段提交的特性就是通过回滚段的功能来实现的。
l       ROLLBACK段空间的分配基本原则:
1) 一个事务的回退信息可以写到当前例程中正在被使用的任何的一个ROLLBACK 段中
2) 一个事务的所有回退信息只能写入一个ROLLBACK段中
3) 多个事务的回退信息可以同时写入同一个ROLLBACK段中,甚至可以同时写入一个EXTENT中
4) 当ROLLBACK段的EXTENT中的所有事务都被提交或回退了,此EXTENT就可以被重新使用
5) 一旦ROLLBACK段的某个EXTENT被重新使用,在此EXTENT中的所有信息都不在可用
6) 在系统需要更多的空间时,如果ROLLBACK段的下一个EXTENT中有活动事务,例程就会扩展此ROLLBACK段
7) SELECT、INSERT、UPDATE和DELETE语句都可能会使数据库从多个ROLLBACK段去读数据
8) ROLLBACK的头永远不会移入当前被ROLLBACK的事务所占用的EXTENT中
9) 当ROLLBACK的头进行扩展时,是按顺序的,不会跳过环中的EXTENT
10)      如果ROLLBACK段的头不能使用下一个EXTENT,它就会另外分配一个EXTENT,并把它插入到环中
l       如何管理ROLLBACK段
ROLLBACK段是由EXTENT组成的,ROLLBACK段是按顺序以环的方式使用EXTENT的,当前的EXTENT写满后,ROLLBACK段就移到下一个EXTENT。一个事务把一条记录写到ROLLBACK段的当前位置并根据记录的大小移动指针。在此,当前正在写记录的位置叫做ROLLBACK段的‘头’。ROLLBACK段中的记录只有当事务回退或提交后才可被覆盖(即重新写)。ROLLBACK段中最旧的有活动事务记录的开始位置叫做ROLLBACK段的‘尾’。
为了对ROLLBACK段做一般配置,平衡ROLLBACK段的数量和大小之间的关系,使所需的空间小于可用的磁盘空间,必须确保有足够多的ROLLBACK段以减少竞争,同时还必须确保单个ROLLBACK段足够大,能处理相应的事务。
在判断ROLLBACK段是否足够大时,首先必须确保事务不会引起ROLLBACK段的‘头’扩展的太快以至于到达ROLLBACK段的‘尾’。其次,如果有长查询(即对经常更新的数据进行长时间的查询),就必须保证ROLLBACK段不会覆盖段中查询需要读取的数据,以保证数据的读一致性(否则回出现‘ORA-1555错误’)。
使用OPTIMAL参数时进行EXTENT的回收。
l       灵活利用ROLLBACK段
日常ORACLE的ROLLBACK段的运用可分三种情况而定, 它们分别是:平稳小事务、经常性大事务和非经常性大事务。
对于平稳小事务,可以创建一个ROLLBACK表空间,计算好最小的EXTENT,并使所有的EXTENT的大小一致。为安全起见,可以在ROLLBACK表空间中多分配点空间使ROLLBACK段可以根据需要扩展,同时要使用OPTIMAL参数来强迫所有的ROLLBACK段进行回缩,OPTIMAL的值要大于最小覆盖值。
对于经常性大事务的处理,不要设置OPTIMAL参数。通常在这种情况下有两种配置方案:一为减少ROLLBACK段的数目使每个ROLLBACK段都足够大(足以处理最大的事务)。二为创建一两个大的ROLLBACK段并且使大的事务使用它们。使用“SET TRANSACTION USE ROLLBACK SEGMENT”命令指定特定的ROLLBACK段或在PL/SQL中使用DBMS_TRANACTION.USE_ROLLBACK_SEGMENT过程来指定特定ROLLBACK段。
对于非经常性的大的事务的处理,设置OPTIMAL参数来建立一个灵活的ROLLBACK段结构。

1.6.2.6 对SQL语句的优化

1、优化方式简介
在ORACLE系统中提供了两种优化方式,它们分别是COST_BASED(基于代价)和RULE_BASED(基于规则)的优化方法。在ORACLE7之后的版本中ORACLE默认使用COST_BASED的优化方式。用户可以根据自身的实际情况对系统进行配置。在系统中,用户可以配置优化方法为COST_BASED,RULE_BASED或者二者自动选择的方法。
COST_BASED的优化方式,一般同选择基于规则的优化方法一样好,或者更好一点。特别是在带多连接或多索引的大量查询时是这样的。基于代价的方法还可通过消除自己调整自己的SQL语句的需要来改进性能。
基于规则的优化方法,ORACLE公司给出下列对SQL语句的存取路径。它总的包括16条规则,分别是:
Rank
Access Path
1
Single Row By Rowid
2
Single Row By Cluster Join
3
Single Row By Hash Cluster Key With Uniqe or Primary Key
4
Single Row By Unique Or Primary Key
5
Cluster Join
6
Hash Cluster Key
7
Indexed Cluster Key
8
Compoite Key
9
Single _column indexes
10
Bounded Range Search On Indexed Columns
11
Unbound Range Search on Indexed Columns
12
Sort_merge Join
13
Max or Min indexed Column
14
Order by on indexed columns
15
Full Table Scan
对于使用基于规则的优化方法的ORACLE系统,ORACLE将严格使用上述的顺序进行SQL语句的优化。
2、对索引的使用
在日常的应用中,索引是否引用是关系到该SQL语句效率的一个非常关键的因素。在日常应用中应该注意到如果在SQL语句中出现下列语法,则系统无法利用到索引:
l       COLUMN1>COLUMN2
l       COLUMN1<COLUMN2
l       COLUMN1>=COLUMN2
l       COLUMN1<=COLUMN2
其中COLUMN1和COLUMN2在同一张表中。
l       COLUMN IS NULL
l       COLUMN IS NOT NULL
l       COLUMN NOT IN
l       COLUMN !=EXPR
l       COLUMN LIKE ‘%ANYTHING’
在上述条件中不管COLUMN上是否有建索引,SQL语句都不会利用索引。
l       EXPR是一个表达式,它用运算符或函数操作在该列上,不管列上是否有索引,例如:EXPR*COLUMN=ANYTHING,则不能利用索引。
l       NOT EXISTS SUBQUERY
l       不包含未被索引的列的任何条件
l       在LIKE表达式中如果模糊的列为数字或日期的话,那么该语句同样无法利用索引。
l       在ORACLE内部的数据转换将引起ORACLE索引列的不被使用
在日常SQL语句使用时还应该注意使用索引的原则:
在使用复合索引时,应该注意是否有利用到该索引的前导部分,在使用该复合索引时只有使用到该索引的前导部分,该索引才能被利用,否则就根本无法发挥索引作用。例如:用户在一张表的三个列(X,Y,Y)张建立一复合索引,该复合索引的顺序为XYZ。在SQL语句的WHERE条件中使用XY,XYZ,XZ等都可利用到索引,但是如果使用YZ,Z等就无法利用到索引。如果对索引的所有列都有引用的话,则无所谓对字段位置的使用了。
3、如何写高效的SQL语句
l       写相同的SQL语句,保证程序能够利用共享池的内容,加快程序的执行速度。
l       写能够利用索引并且能够符合基于代价的优化条件的SQL语句
l       尽量利用PL/SQL的特性减少网络的传输
l      尽可能使用存储过程,以减少网络的传输和提高系统的编译速度

1.6.2.7 对内存作的优化

对于内存的优化总的来说就是对系统全局区(SGA)的优化。SGA总体上由share_spool_size ,open_cursors , db_block_buffer, sort_area_size等参数决定的内存构成。
1、对库高速缓存的处理
­通过以下语句进行监控:
select sum(pins),sum(reloads),sum(reloads)/sum(pins) reload_ratio from v$librarycache ;
对库高速缓存进行监控,如果reload_ratio的值大于1%的话,那么系统的库高速缓存就是有较高的不命中率。就必须进行调整。
2、对数据字典高速缓存的处理
3、对缓冲区高速缓存的处理

1.6.2.8 对I/O、CPU作的优化

1、如何对在用系统进行系统性能性能瓶颈分析
l       通过UNIX下的IOSTAT、VMSTAT命令或WINDOWS NT下的PERFORMANCE MONITOR图形工具对当前系统进行监视,以发现相应的系统问题
l       通过V$SESSION_WAIT对每一个用户进程等待的事件以及的时间进行分析,得到系统性能瓶颈所在
l       通过V$LOCK,V$SESSION,ALL_OBJECTS表得到当前系统中影响性能的锁对应的用户名,锁的类型,锁住的对象名。对于一般的情况可以通过 ALTER SYSTEM KILL SESSION 对锁住的进程进行释放
l       通过对V$SQLAREA区的内容进行分析,查找系统中耗费大量CPU或I/O的SQL语句。找出程序上造成系统性能下降的进程和程序,把此类问题提交给程序设计人员,通过修改程序修正错误
l      使用TKPROF工具对特定用户的使用情况进行跟踪分析,找出此用户的运用的性能瓶颈所在

1.6.2.9 常见问题分析

l       表空间的剩余空间的问题
对于日常出现的问题中,关于表空间满而导致的问题屡见不鲜,例如由于表空间满,导致系统出错等问题。因此在系统运行一段时间之后,要经常查看系统的表空间剩余情况,预防由于表空间满而导致的其余问题的出现。
对此类问题的解决可通过对视图DBA_FREE_SPACE查询到每一表空间的剩余空间,及时作出相应的处理方法。
l       关于表的锁的问题
l       关于表的链接问题
如果数据在一个数据块中无法存放的话,而在其余块中无法找到有足够的空间存放改行数据的话,那么就会发生行链接的问题(ROW CHAIN)。
如果在其余块中能找到足够的空间存放该行的数据,那么,这一整行的数据就全部转移到另一能够容纳下该行数据的块中。这一现象叫做行迁移(ROW MIGRATION)。发生行迁移和行链接时,将加大系统I/O的负担。
使用如下语句查找哪些表发生了行链接问题:
Analyze table table_name list chained rows into chained_rows
 
做上述操作之前需建立表CHAINED_ROWS该表可