转）oracle数据库相关

最新推荐文章于 2024-07-09 17:22:14 发布

liujinxing

最新推荐文章于 2024-07-09 17:22:14 发布

阅读量1.8k

点赞数

文章标签： oracle 数据库相关数据库 database sql system

oracle数据库的性能调整

===========================================================

　　　　 oracle 是一个高性能数据库软件。用户可以通过参数的调整，达到性能的优化。性能优化主要分为两部分：一是数据库管理员通过对系统参数的调整达到优化的目的，二是开发人员通过对应用程序的优化达到调整的目的。
　　在此，仅就系统参数的调整进行探讨，而不涉及应用程序的优化。对系统参数的调整，可以分为以下几个部分：
　　 (1) 调整内存分配
　　系统全局区（ SGA ）是一个分配给 ORACLE 包含 ORACLE 数据库实例控制信息的内存段。 SGA 的大小对系统性能的影响极大，其缺省参数设置只适用于配置很低的计算机，不适应收入系统现有设备的需要。这些参数若不作调整，会对系统资源造成巨大浪费。就收入系统的 Alpha 1200 而言， SGA 的大小以 160 兆左右为宜。
　　初始化参数文件中的一些参数对 SGA 的大小有决定性的影响。参数 DB － BLOCK － BUFFERS （ SGA 中存储区高速缓存的缓冲区数目），参数 SHARED － POOL － SIZE （分配给共享 SQL 区的字节数），是 SGA 大小的主要影响者。
　　 DB － BLOCK － BUFFERS 参数是 SGA 大小和数据库性能的最重要的决定因素。该值较高，可以提高系统的命中率，减少 I/O 。每个缓冲区的大小等于参数 DB － BLOCK － SIZE 的大小。 ORACLE 数据库块以字节表示大小。
　　 Oracle SGA 区共享池部分由库高速缓存、字典高速缓存及其它一些用户和服务器会话信息组成，共享池是最大的消耗成分。调整 SGA 区各个结构的大小，可以极大地提高系统的性能。
　　 . 调整 Library Cache
　　库高速缓存（ Library Cache ）中包含私用和共享 SQL 区和 PL/SQL 区。调整 SGA 的重要问题是确保库高速缓存足够大，以使 ORACLE 能在共享池中保持分析和执行语句，提高语句分析和执行效率，降低资源消耗。通过比较 Library Cache 的命中率来决定它的大小。查询 V$LIBRARYCACHE 数据字典视图（其中， pins 表示高速缓存命中率， reloads 表示高速缓存失败）
SQL 〉 SELECT SUM(pins),SUM(reloads)
　　 FROM v$librarycache;
如果 sum(reload)/sum(pins) ≈ 0 ，说明 Library Cache 的命中率比较合适，若大于 1 ，则需要增加共享池（ SHARED － POOL － SIZE ）的大小（在初始化参数文件中）。
　　 . 调整数据字典高速缓存（ Dictionary Cache ）
　　数据字典高速缓存包括了有关数据库的结构、用户、实体信息等。数据字典的命中率对系统有很大的影响。命中率的计算中， getmisses 表示失败次数， gets 表示成功次数。
　　查询 V$ROWCACHE 表：
　　 SQL>SELECT (1-(SUM(getmisses)/(SUM(gets)+SUM(getmisses))))*100
　　 FROM v$rowcache;
　　如果该值 >90% ，说明命中率合适。否则，应增大共享池的大小。
　　 . 调整数据库缓冲区高速缓存
　　 Oracle 在运行期间向数据库高速缓存读写数据，高速缓存命中表示信息已在内存中，高速缓存失败意味着 ORACLE 必需进行磁盘 I/O 。保持高速缓存失败率最小的关键是确保高速缓存的大小。初始化参数 DB － BLOCK － BUFFERS 控制数据库缓冲区高速缓存的大小。可通过查询 V$SYSSTAT 命中率，以确定是否应当增加 DB － BLOCK － BUFFERS 的值。
　　 SQL>SELECT name,value
　　 FROM V$SYSSTAT
　　 WHERE name IN (’dbblock gets’,’consistent gets’,’physical reads’);
　　通过查询结果
　　命中率 =1-physical reads/(dbblock gets+consistent gets)
　　如果命中率 <0.6 ～ 0.7 ，则应增大 DB － BLOCK － BUFFERS 。
　　 (2) 调整磁盘 I/O
　　磁盘 I/O 是系统性能的瓶颈，解决好磁盘 I/O ，可明显提高性能。通过查询 V$FILESTAT 可以知道每个物理文件的使用频率（ phyrds 表示每个数据文件读的次数， phywrts 表示每个数据文件写的次数）
　　 SQL>SELECT name,phyrds,phywrts
　　 FROM v$datafile df,v$filestat fs
　　 WHERE df.file# =fs.file#;
对于使用频率较高的物理文件 , 可以采用以下策略 :
　　 . 将 I/O 尽可能平均分配在尽可能多的磁盘上。
　　 . 为表和索引建立不同的表空间。
　　 . 将数据文件与重做日志文件分离在不同的磁盘上。
　　 . 减少不经 oracle SERVER 的磁盘 I/O 。
　　 (3) 调整竞争
　　当多个进程对相同的资源发出申请时，产生竞争。
　　 . 修改 process 参数
　　该参数定义可以同时连接到 oracle 数据库的最大进程数，缺省值为 50 。注意， oracle 的后台进程也包括在此数目中，建议将该值改为 200 。
　　 . 减少调度进程的竞争
　　减少调度进程的竞争，通过查询 v$dispatcher 表来判定调度进程的竞争
　　 SQL>SELECT network ,sum(busy)/sum(busy)+sum(idle)
　　 FROM v$dispatcher
　　 GROUP BY network;
　　如果某种协议忙的比率超过 50% ，应增加 MTS － DISPATCHERS 的值。
　　 . 减少多线程服务进程竞争
　　首先查询 V$SYSSTAT 表判定是否发生多线程服务进程竞争 :
　　 SQL>SELECT DECODE(totalq,0,’No request’,wait/totalq||’hunderths of seconds’) FROM v$sysstat
　　 WHERE type=’common’;
　　如果共享服务进程数量已达到初始化参数文件中 MTS － MAX － SERVERS 指定的最大值，但应用运行时，平均请求等待时间仍持续增长，那幺，应加大 MTS － MAX － SERVERS 的值。
　　 . 减少重做日志缓冲区竞争
　　通过查询 V$SYSSTAT 表判定 redo log 文件缓冲区是否足够。
　　 SQL>SELECT name,value
　　 FROM v$sysstat
　　 WHERE name= ’ redo log space request ’ ;
　　此处 value 的值应接近于 0 ，否则，应增大初始化参数文件的 LOG － BUFFEQS 的值。
　　 . 减少回退段竞争
　　回退段对性能也有影响，根据事物大小情况来分配合适的回退段。
　　首先判定回退段的数量能否满足系统运行的需要：
　　查询 V$WAITSTAT 表与 V$SYSSTAT 表
　　 SQL>SELECT class,count
　　 FROM v$waitstat
　　 WHERE class IN ( ’ system undo header ’ ,system undo block ’ ,
　　’ undo header ’ , ’ undo block ’ );
　　 SQL>SELECT sum(value)
　　 FROM v$sysstat WHERE name IN (’db block gets’,’consistent gets’);
　　如果任何一个 class/sum(value)>10%, 那幺考虑增加回退段。回退段的数量一般按如下规律设定：
　　用户数　　　　　　　　　回退段个数
n<16 　　　　　　　　　　　　 4
16<n<32 　　　　　　　　　　 8
32<=n 　　　　　　　　　　　 n/4 但不超过 50
　　 . 减少 Free List 竞争
　　当多个进程同时向一个表中插入数据时，产生 Free List 竞争。
　　 SQL>SELECT class,count
　　 FROM v$waitstat
　　 WHERE class= ’ free list ’ ;
　　 SQL>SELECT sum(value)
　　 FROM v$sysstat
　　 WHERE name IN ( ’ db block gets ’ , ’ consistent gets’);
　　如果 class/sum(value)>1% ，则应增加该表的 Free List 的值。

SGA调整

===========================================================

一环境：
1 平台：
IBM AX360,4G内存
windows 2k advServer sp3 + oracle 816
独占模式

2 内存分配相关参数
..processes................=.1000
..shared_pool_size.........=.240000000
..large_pool_size..........=.614400
..java_pool_size...........=.32768
..db_block_buffers.........=.90000
..db_block_size............=.4096
..log_buffer...............=.163840
..log_checkpoint_interval..=.10000
..sort_area_size...........=.65536
..sort_area_retained_size..=.65536
..open_cursors.............=.100
..job_queue_processes......=.4
..job_queue_interval.......=.10
..max_dump_file_size.......=.10240

3 最大并发用户数：850个左右

二故障现象：
....当用户数达到一定的数量时(700多)客户端连接服务器时报ora-12560错误，紧跟着报ora-03114错误，不能连接到服务器。此时已连接到服务器的用户能正常访问数据库。
....首先查看警告日志文件，未见到明显错误信息。
....查看listner.log文件，发现如下信息(很多个类似的错误记录，摘两个上来)
.........................
02-JUL-2003 10:30:09 * (CONNECT_DATA=(SID=ORCL)(CID=(PROGRAM=***.EXE)(HOST=*******)(USER=*****))) * (ADDRESS=(PROTOCOL=tcp)(HOST=*******)(PORT=1136)) * establish * ORCL * 12500
TNS-12500: TNS:listener failed to start a dedicated server process
.TNS-12540: TNS:internal limit restriction exceeded
..TNS-12560: TNS rotocol adapter error
...TNS-00510: Internal limit restriction exceeded
....32-bit Windows Error: 8: Exec format error
02-JUL-2003 10:30:10 * (CONNECT_DATA=(SID=ORCL)(CID=(PROGRAM=***.EXE)(HOST=*****)(USER=***))) * (ADDRESS=(PROTOCOL=tcp)(HOST=******)(PORT=1203)) * establish * ORCL * 12500
TNS-12500: TNS:listener failed to start a dedicated server process
.TNS-12540: TNS:internal limit restriction exceeded
..TNS-12560: TNS rotocol adapter error
...TNS-00510: Internal limit restriction exceeded
....32-bit Windows Error: 8: Exec format error
.....................

此时，原来已连接上服务器的用户还能正常使用。查看此时session达到760多个。

....重启oracle服务后，能连接新的用户数，但当并发用户数达到750个以上时，再次报同样的错误

三原因分析：
....查看oracle帮助文件及相关的错误信息，提示出错的原因为系统资源耗竭，需要找oracle技术支持。那多贵啊，多半是找不起的。自己琢磨琢磨吧。
....系统资源耗竭，意味着系统分配给oracle的内存用尽了。虽然我们有4G的物理内存，但正常情况下系统只能给oracle分配2G的内存，这2G的内存中，包括了SGA、PGA等oracle需要使用的全部内存。在独占模式下，每一个session将单独分配2M左右的内存。在本例中，SGA分配了约600M，按每一个用户分配2M内存计算，连接数达到750个时，总分配内存已达到2G，将不能再增加新的连接数。如果要解决这个问题，在不做大的调整的前提下，要么减小SGA大小，要么减小为每一个会话分配的内存大小，以能连接更多的用户。

四解决过程：
....查阅了oracle文档，文档里提出来了几个解决的办法：
1 重置init.ora参数文件，调小以下四个参数的值：
....short_area_size
....hash_area_size
....bitmap_merge_area_size
....create_bitmap_area_seze
....open_cursone
2 调小SGA的大小
3 减小oracle Job队列数量(job_queue_processes)和并发队列数(parallel_max_servers)
4 重置并减小会话/线程使用的堆栈大小
5 将oracle改为mts模式
6 更换操作系统为windows NT 企业版
7 使用intel的ESMA硬件支持，即使用大内存
..1) 在intel系统上使用 /3G 开关
..2) 使用PSE36内存

....结合本实例的具体情况，决定调整的主要目标为减小用户的PGA大小。
....构成PGA的主要内容有sort_area_size, hash_area_size, open_cursone, 以及oracle 堆栈和TNS 堆栈。在本实例中，排序区为64K，hash区为128K(缺少值)，打开的游标数与应用有关，不能随便减小了，然而oracle堆栈和TNS堆栈都是1M，却有较大的减小的余地。因此，调整的目标定为减小这两个堆栈的大小。
....使用orastack 命令来减小这两个堆栈的大小：
D:oracleora81bin>orastack oracle.exe 500000
Couldn't open file with CreateFile()
GetLastError() == 32

....停止oracle服务和TNS服务，再运行以上命令
D:oracleora81bin>orastack oracle.exe 500000

Dump of file oracle.exe

Current Reserved Memory per Thread = 1048576
Current Committed Memory per Thread = 4096

New Reserved Memory per Thread = 500000

D:oracleora81bin>orastack tnslsnr.exe 500000

Dump of file tnslsnr.exe

Current Reserved Memory per Thread = 1048576
Current Committed Memory per Thread = 4096

New Reserved Memory per Thread = 500000

重新启动oracle服务和TNS服务，打开数据库，用户连接到服务器，经测试，用户数到1350以上时数据库仍然运行正常，解决了本实例存在的问题。

五小结
....事实上，正如oracle文档所指出的那样，要增加用户连接数的途径很多，除了减小用户堆栈之外，还可以减小SGA，或者是更改成MTS方式，或者是使用第三方工具增加oracle可用内存。本人前面小结过如何让oracle在32位的windows操作系统上使用超过2G内存的方法( http://www.itpub.net/showthread.php...15&pagenumber=1 )，在本安全应用中，宜将两者(减小用户堆栈与增加oracle可用内存)结合起来使用，以提高数据库性能。但是，这种方式下，同样不可能无限制地增加用户连接数。要想使用户连接数达到更大，则应使用MTS方式。

以上方法，只是在这样一个具体的实例上进行调整的总结，仅供参考，不能作为规律性的结论。

Oracle 数据库碎片整理

===========================================================

       我们知道， Oracle
      作为一种大型数据库，广泛应用于金融、邮电、电力、民航等数据吞吐量巨大，计算机网络广泛普及的重要部门。对于系统管理员来讲，如何保证网络稳定运行，如何提高数据库性能，使其更加安全高效，就显得尤为重要。作为影响数据库性能的一大因素
      -- 数据库碎片，应当引起 DBA 的足够重视，及时发现并整理碎片乃是 DBA 一项基本维护内容。
      1 、碎片是如何产生的
      - 当生成一个数据库时，它会分成称为表空间（ Tablespace ）的多个逻辑段（ Segment ），如系统（ System ）表空间 ,
      临时（ Temporary ）表空间等。一个表空间可以包含多个数据范围（ Extent ）和一个或多个自由范围块，即自由空间（ Free Space
      ）。
      表空间、段、范围、自由空间的逻辑关系如下：
      当表空间中生成一个段时，将从表空间有效自由空间中为这个段的初始范围分配空间。在这些初始范围充满数据时，段会请求增加另一个范围。这样的扩展过程会一直继续下去，直到达到最大的范围值，或者在表空间中已经没有自由空间用于下一个范围。最理想的状态就是一个段的数据可被存在单一的一个范围中。这样，所有的数据存储时靠近段内其它数据，并且寻找数据可少用一些指针。但是一个段包含多个范围的情况是大量存在的，没有任何措施可以保证这些范围是相邻存储的，如图〈
      1 〉。当要满足一个空间要求时，数据库不再合并相邻的自由范围（除非别无选择），
      而是寻找表空间中最大的自由范围来使用。这样将逐渐形成越来越多的离散的、分隔的、较小的自由空间，即碎片。例如：
      2 碎片对系统的影响
      随着时间推移，基于数据库的应用系统的广泛使用，产生的碎片会越来越多，将对数据库有以下两点主要影响：
      （ 1 ）导致系统性能减弱
      如上所述，当要满足一个空间要求时，数据库将首先查找当前最大的自由范围，而 " 最大 "
      自由范围逐渐变小，要找到一个足够大的自由范围已变得越来越困难，从而导致表空间中的速度障碍，使数据库的空间分配愈发远离理想状态；
      （ 2 ）浪费大量的表空间
      尽管有一部分自由范围（如表空间的 pctincrease 为非 0 ）将会被 SMON
      （系统监控）后台进程周期性地合并，但始终有一部分自由范围无法得以自动合并，浪费了大量的表空间。
      3 、自由范围的碎片计算
      由于自由空间碎片是由几部分组成，如范围数量、最大范围尺寸等，我们可用 FSFI--Free Space Fragmentation Index
      （自由空间碎片索引）值来直观体现：
      FSFI=100*SQRT(max(extent)/sum(extents))*1/SQRT(SQRT(count(extents)))
      - 可以看出， FSFI 的最大可能值为 100 （一个理想的单文件表空间）。随着范围的增加， FSFI 值缓慢下降，而随着最大范围尺寸的减少，
      FSFI 值会迅速下降。
      下面的脚本可以用来计算 FSFI 值：
      rem FSFI Value Compute
      rem fsfi.sql
      column FSFI format 999,99
      select tablespace_name,sqrt(max(blocks)/sum(blocks))*
      (100/sqrt(sqrt(count(blocks)))) FSFI
      from dba_free_space
      group by tablespace_name order by 1;
      spool fsfi.rep;
      /
      spool off;
      比如，在某数据库运行脚本 fsfi.sql, 得到以下 FSFI 值：
      TABLESPACE_NAME FSFI
      ------------------------------ -------
      RBS 74.06
      SYSTEM 100.00
      TEMP 22.82
      TOOLS 75.79
      USERS 100.00
      USER_TOOLS 100.00
      YDCX_DATA 47.34
      YDCX_IDX 57.19
      YDJF_DATA 33.80
      YDJF_IDX 75.55
      统计出了数据库的 FSFI 值，就可以把它作为一个可比参数。在一个有着足够有效自由空间，且 FSFI 值超过 30
      的表空间中，很少会遇见有效自由空间的问题。当一个空间将要接近可比参数时，就需要做碎片整理了。
      4 、自由范围的碎片整理
      （ 1 ）表空间的 pctincrease 值为非 0
      可以将表空间的缺省存储参数 pctincrease 改为非 0 。一般将其设为 1 ，如：
      alter tablespace temp
      default storage(pctincrease 1);
      这样 SMON 便会将自由范围自动合并。也可以手工合并自由范围：
      alter tablespace temp coalesce;
      5 、段的碎片整理
      我们知道，段由范围组成。在有些情况下，有必要对段的碎片进行整理。要查看段的有关信息，可查看数据字典 dba_segments
      ，范围的信息可查看数据字典 dba_extents 。如果段的碎片过多，
      将其数据压缩到一个范围的最简单方法便是用正确的存储参数将这个段重建，然后将旧表中的数据插入到新表，同时删除旧表。这个过程可以用
      Import/Export （输入 / 输出）工具来完成。
      Export （）命令有一个（压缩）标志，这个标志在读表时会引发 Export
      确定该表所分配的物理空间量，它会向输出转储文件写入一个新的初始化存储参数 -- 等于全部所分配空间。若这个表关闭，则使用 Import
      （）工具重新生成。这样，它的数据会放入一个新的、较大的初始段中。例如：
      exp user/password file=exp.dmp compress=Y grants=Y indexes=Y
      tables=(table1,table2);
      若输出成功，则从库中删除已输出的表，然后从输出转储文件中输入表：
      imp user/password file=exp.dmp commit=Y buffer=64000 full=Y
      这种方法可用于整个数据

Oracle 9i中的flash back 查询

===========================================================

语法:
select *
from [TABLE] as of timestamp
to_timestamp('时间', ’时间格式')

作用：
查询某个时间点的数据，在这个时间点之后，数据更改已经提交了。
可以用来更正用户对数据的误操作
可以用来获取数据的更改情况，比如频率等

原理：
当数据update或delete时，原来的数据会保存在undo表空间中，保存的最少时间是undo_retention,
实际的保存时间与undo表空间的大小和数据更改的繁忙程度相关。

示例：
SQL> create table t3
   2 (a number);

Table created.

SQL> alter session set nls_date_format='YYYY-MM-DD HH24:MI:SS';

Session altered.

SQL> insert into t3 values (1);

1 row created.

SQL> commit;

Commit complete.

SQL> select *
   2 from t3;

          A
----------
          1

SQL> select sysdate
   2 from dual;

SYSDATE
-------------------
2004-05-14 15:20:05

SQL> update t3 set a = 2;

1 row updated.

SQL> commit;

Commit complete.

SQL> select * from t3 as of timestamp
   2 to_timestamp('2004-05-14 15:20:05', 'YYYY-MM-DD HH24:MI:SS');

          A
----------
          1

SQL> spool off;

control_files error ORA-01122 ORA-00214

===========================================================

昨天公司数据库出现问题，由于断电（Oracle 处于Open状态下），导致数据库启动时报错ORA-00214: controlfile ‘d:oracleoradataorclcontrol01.ctl’ version 57460 inconsistent with file d:oracleoradataorclcontrol02.ctl’ version 57452.
ORA-01122 ATABASE file1 failed verfication check

这个是由于控制文件版本不同导致。在数据库设计的过程中，从安全的角度考虑，系统使用了三个镜像的控制文件，现在三个控制文件version号不一致，所以数据库Instance启动时报错。

我首先备份了控制文件，启动了数据库到nomount状态下，分别指定系统控制文件为三个中的其中一个
ALTER SYSTEM SET CONTROL_FILES='F:ORACLEORADATAORACASCONTROL01.CTL'
SCOPE=SPFILE
然后启动数据库到Mount状态下，如果还是报错，就指定下一个

ALTER SYSTEM SET CONTROL_FILES='F:ORACLEORADATAORACASCONTROL02.CTL'
SCOPE=SPFILE
然后启动数据库到Mount状态下，如果还是报错，就指定下一个

ALTER SYSTEM SET CONTROL_FILES='F:ORACLEORADATAORACASCONTROL03.CTL'
SCOPE=SPFILE

只要上面三次操作中有一次成功，就可以用那个成功的控制文件来重新作出另外两个控制文件。

如果三次操作都不成功，就是说这三个控制文件都不好使了，这时候需要建立新的控制文件

步骤如下：
1、ALTER DATABASE BACKUP CONTROLFILE TO TRACE;
这时候会在udump目录下生成SID_ora_*.trc文件，根据你是在归档还是非归档模式下，选择一段内容
建立创建脚本
我是在非归档模式下，选择第一段内容
2)根据得到的TRC文件建立ora.sql内容如下：
CREATE CONTROLFILE REUSE DATABASE "ORACAS" NORESETLOGS NOARCHIVELOG
    MAXLOGFILES 50
    MAXLOGMEMBERS 5
    MAXDATAFILES 100
    MAXINSTANCES 1
    MAXLOGHISTORY 226
LOGFILE
GROUP 2 'F:ORACLEORADATAORACASREDO02.LOG' SIZE 100M,
GROUP 3 'F:ORACLEORADATAORACASREDO03.LOG' SIZE 100M
DATAFILE
'F:ORACLEORADATAORACASSYSTEM01.DBF',
'F:ORACLEORADATAORACASUNDOTBS01.DBF',
'F:ORACLEORADATAORACASCWMLITE01.DBF',
'F:ORACLEORADATAORACASDRSYS01.DBF',
'F:ORACLEORADATAORACASEXAMPLE01.DBF',
'F:ORACLEORADATAORACASINDX01.DBF',
'F:ORACLEORADATAORACASODM01.DBF',
'F:ORACLEORADATAORACASTOOLS01.DBF',
'F:ORACLEORADATAORACASUSERS01.DBF',
'F:ORACLEORADATAORACASXDB01.DBF'
CHARACTER SET ZHS16GBK
;
STARTUP NOMOUNT，然后执行ORA.SQL，。
成功以后，尝试打开数据库，失败，需要进行media recovery;
RECOVER DADAFILE   'F:ORACLEORADATAORACASSYSTEM01.DBF',
......
全部恢复以后，就可以启动数据库，ALTER DATABASE OPEN NORESETLOG;
再重新给生成的控制文件做镜像就可以了。
由于是昨天做的操作，可能有些细节步骤没有写上来，但是大体上就是这样了^_^

===========================================================

如何解决ORA-04031 错误

===========================================================

如何解决ORA-04031 错误

对于大多数应用来说，共享池的大小对于Oracle 性能来说都是很重要的。共享池中保存数据字典高速缓冲
和完全解析或编译的的PL/SQL 块和SQL 语句。

当我们在共享池中试图分配大片的连续内存失败的时候，Oracle首先刷新池中当前没使用的所有对象，使空
闲内存块合并。如果仍然没有足够大单个的大块内存满足请求,就会产生ORA-04031 错误。

当这个错误出现的时候你得到的错误信息如下:

Error : ORA 4031
Text : unable to allocate %s bytes of shared memory (%s,%s,%s)
----------------------------------------------------------------------------------------------------------------
Cause : More shared memory is needed than was allocated in the shared pool.
Action : Either use the dbms_shared_pool package to pin large packages, reduce your use of
shared memory, or increase the amount of available shared memory by increasing the value of
the init.ora parameter "shared_pool_size".

1.共享池相关的实例参数

在继续之前，理解下面的实例参数是很重要的:

SHARED_POOL_SIZE – 这个参数指定了共享池的大小，单位是字节。可以接受数字值或者数
字后面跟上后缀"K" 或 "M" 。"K"代表千字节, "M"代表兆字节。

SHARED_POOL_RESERVED_SIZE – 指定了为共享池内存保留的用于大的连续请求的共享池
空间。当共享池碎片强制使Oracle 查找并释放大块未使用的池来满足当前的请求的时候，这个参
数和SHARED_POOL_RESERVED_MIN_ALLOC 参数一起可以用来避免性能下降。

这个参数理想的值应该大到足以满足任何对保留列表中内存的请求扫描而无需从共享池中刷新对
象。既然操作系统内存可以限制共享池的大小，一般来说，你应该设定这个参数为
SHARED_POOL_SIZE 参数的 10% 大小。

SHARED_POOL_RESERVED_MIN_ALLOC –这个参数的值控制保留内存的分配。如果一个足
够尺寸的大块内存在共享池空闲列表中没能找到，内存就从保留列表中分配一块比这个值大的空
间。默认的值对于大多数系统来说都足够了。如果你加大这个值，那么Oracle 服务器将允许从这
个保留列表中更少的分配并且将从共享池列表中请求更多的内存。这个参数在Oracle 8i 是隐藏
的。
2.诊断ORA-04031 错误

ORA-04031 错误通常是因为库高速缓冲中或共享池保留空间中的碎片。在加大共享池大小的时候考虑调整应用
使用共享的SQL 并且调整如下的参数：

SHARED_POOL_SIZE,
SHARED_POOL_RESERVED_SIZE,
SHARED_POOL_RESERVED_MIN_ALLOC.

首先判定是否ORA-04031 错误是由共享池保留空间中的库高速缓冲的碎片产生的。提交下的查询：

SELECT free_space, avg_free_size, used_space, avg_used_size,
request_failures, last_failure_size
FROM v$shared_pool_reserved;

如果:

REQUEST_FAILURES > 0 并且
LAST_FAILURE_SIZE > SHARED_POOL_RESERVED_MIN_ALLOC

那么ORA-04031 错误就是因为共享池保留空间缺少连续空间所致。
要解决这个问题,可以考虑加大SHARED_POOL_RESERVED_MIN_ALLOC 来降低缓冲进共享池保留空间的对
象数目，并增大 SHARED_POOL_RESERVED_SIZE 和SHARED_POOL_SIZE 来加大共享池保留空间的可用
内存。

如果：
REQUEST_FAILURES > 0 并且
LAST_FAILURE_SIZE < SHARED_POOL_RESERVED_MIN_ALLOC

或者

REQUEST_FAILURES 等于0 并且
LAST_FAILURE_SIZE < SHARED_POOL_RESERVED_MIN_ALLOC

那么是因为在库高速缓冲缺少连续空间导致ORA-04031 错误。

第一步应该考虑降低SHARED_POOL_RESERVED_MIN_ALLOC 以放入更多的对象到共享池
保留空间中并且加大SHARED_POOL_SIZE。

3.解决ORA-04031 错误

? ORACLE BUG
要解决这个错误(如果可以称得上错误的话）,进行的诊断的第一步是在你的平台上使用最新的补丁集。
大多数的ORA-04031错误都和BUG 相关，可以通过使用这些补丁来避免。

下面表中总结和和这个错误相关的最常见的BUG，可能的环境和修补这个问题的补丁。

BUG 描述 Workaround Fixed
<Bug:1397603> ORA-4031/SGA memory leak of PERMANENT memory occurs for buffer handles _db_handles_cached = 0 901/ 8172
<Bug:1640583> ORA-4031 due to leak / cache buffer chain contention from AND-EQUAL access Not available 8171/901
<Bug:1318267> INSERT AS SELECT statements may
not be shared when they should be
if TIMED_STATISTICS. It can lead to ORA-4031 _SQLEXEC_PROGRESSION_COST=0
8171/8200
<Bug:1193003> Cursors may not be shared in 8.1
when they should be Not available 8162/8170/ 901

共享池结构中的一些BUG 会引起这个错误，不过通常大量的共享的SQL/PLSQL 语句也会引起
这个错误。一旦打过了最新的补丁，在遇到这个问题的时候我们强烈推荐调整数据库和应用。

要得到已知的BUG 的完整信息，可以参考：

<Note:62143.1>: Main issues affecting the Shared Pool on Oracle 7 , Oracle8 and Oracle8i。

? 共享池碎片
每一次，需要被执行的SQL 或者PL/SQL 语句的解析形式载入共享池中都需要一块特定的连续
的空间。数据库要扫描的第一个资源就是共享池中的空闲可用内存。一旦空闲内存耗尽，数据库
要查找一块已经分配但还没使用的内存准备重用。如果这样的确切尺寸的大块内存不可用，就继
续按照如下标准寻找：

◇ 大块(chunk)大小比请求的大小大
◇ 空间是连续的
◇ 大块内存是可用的(而不是正在使用的)

这样大块的内存被分开，剩余的添加到相应的空闲空间列表中。当数据库以这种方式操作一段时
间之后，共享池结构就会出现碎片。

当共享池存在碎片的问题,分配一片空闲的空间就会花费更多的时间,数据库性能也会下降(整个操
作的过程中，"chunk allocation"被一个叫做"shared pool latch" 的闩所控制) 或者是出现
ORA-04031 错误errors (在数据库不能找到一个连续的空闲内存块的时候)。

-------------------------------------------------------------------------------------
参考 <Note:61623.1>: 可以得到关于共享池碎片的详细讨论。
-------------------------------------------------------------------------------------

如果SHARED_POOL_SIZE 足够大，大多数的 ORA-04031 错误都是由共享池中的动态SQL
碎片导致的。可能的原因如下：

◇非共享的SQL
◇生成不必要的解析调用 (软解析)
◇没有使用绑定变量

要减少碎片的产生你需要确定是前面描叙的几种可能的因素。可以采取如下的一些方法，当然不
只局限于这几种: 应用调整、数据库调整或者实例参数调整。

--------------------------------------------------------------------------------------
请参考 <Note:62143.1>，描述了所有的这些细节内容。这个注释还包括了共享池如何工作的细节。
--------------------------------------------------------------------------------------

下面的视图有助于你标明共享池中非共享的SQL/PLSQL：

V$SQLAREA 视图

这个视图保存了在数据库中执行的SQL 语句和PL/SQL 块的信息。下面的SQL 语句可以
显示给你带有literal 的语句或者是带有绑定变量的语句：

SELECT substr(sql_text,1,40) "SQL", count(*) , sum(executions) "TotExecs"
FROM v$sqlarea
WHERE executions < 5
GROUP BY substr(sql_text,1,40)
HAVING count(*) > 30
ORDER BY 2;

注意: 语句Having 中的 "30"数值可以根据需要调整以得到更为详细的信息。

X$KSMLRU 视图

有一个固定表x$ksmlru 跟踪共享池中导致其它对象换出(age out)的应用。这个固定表可
以用来标记是什么导致了大的应用。

如果很多对象在共享池中都被阶段性的刷新可能导致响应时间问题并且有可能在对象重载
入共享池中的时候导致库高速缓冲闩竞争问题。

关于这个x$ksmlru 表的一个不寻常的地方就是如果有人从表中选取内容这个表的内容就
会被擦除。这样这个固定表只存储曾经发生的最大的分配。这个值在选择后被重新设定这
样接下来的大的分配可以被标记，即使它们不如先前的分配过的大。因为这样的重置，在
查询提交后的结果不可以再次得到，从表中的输出的结果应该小心的保存。
监视这个固定表运行如下操作：

SELECT * FROM X$KSMLRU WHERE ksmlrsiz > 0;

在Oracle8i 中这个表不能被SYS用户之外的用户所选取。

? 小的共享池尺寸

最后,一个小的共享池可以导致ORA-04031 错误, 不过在碎片真正的是个问题的时候增大
共享池的大小的时候要小心。在错误发现的时候通常有延迟现象，不过当在大的共享池的
碎片中找到一片空闲的内存会加大对性能的影响。

下面的信息将有助于你调整共享池的大小：

库高速缓冲命中率
命中率有助于你衡量共享池的使用，基于多少次SQL/PLSQL 需要被解析而不是
重用。下面的SQL 语句有助于你计算库高速缓冲的命中率：

SELECT SUM(PINS) "EXECUTIONS",
SUM(RELOADS) "CACHE MISSES WHILE EXECUTING"
FROM V$LIBRARYCACHE;

如果misses 比上executions 大于1%, 那就应该尝试着通过加大共享池来减少库高速缓冲
的丢失。

Shared Pool Size Calculation

要计算最适合当前工作负荷的共享池大小，参考：

<Note:1012046.6>: HOW TO CALCULATE YOUR SHARED POOL SIZE.

4.对ORA-04031 的高级分析

如果使用如上的解决办法，这个错误仍然出现，在initSID.ora 文件中设定如下的事件并重新启
动实例：

event = "4031 trace name errorstack level 3"

会在下一次错误发生的时候产生一个跟踪文件。这个跟踪文件可以提供给Oracle 支持人员来解决问题。

相关文档

<Note:151790.1> : Oracle8 Tuning Documentation Guide
<Note:62143.1> : Understanding and Tuning the Shared Pool in Oracle7, Oracle8, and Oracle8i.
<Note:1012046.6>: HOW TO CALCULATE YOUR SHARED POOL SIZE
<Note:1012049.6>: TUNING LIBRARY CACHE LATCH CONTENTION
<Note:61623.1> : Resolving Shared Pool Fragmentation In Oracle7
<Note: 146599.1>: 就是这篇文档的英文原稿．

所有Note字样的文档可以从 Metalink.oracle.com 上找到。需要Oracle的CSI帐号。

ORA-03113错误分析

===========================================================

ORA-03113错误分析
版本历史:
2003-5-22 v0.1 Created by Fenng
2003-12-17 v0.3 Edited by Fenng

前言

每一个DBA在进行数据库管理的过程中不可避免的要遇到形形色色的错误(ORA-xxxx)。有些错
误由于频繁出现、原因复杂而被DBA们戏称之为"经典的错误"。其中ORA-3113 "end of file
on communication channel" 就是这样的一个.

我们可以简单的把这个错误理解为Oracle客户端进程和数据库后台进程连接中断。不过，导致
这个错误的原因实际上有很多种,对数据库设置不当、任何能导致数据库后台进程崩溃的行为都
可能产生这个错误.这个错误的出现还经常伴随着其它错误,比如说：

ORA-1034 ORACLE not available。

此外,该错误出现的场景复杂,可能出现在：

启动的Oracle的时侯;
试图创建数据库的时侯;
试图对数据库进行连接的时侯;
在客户端正在运行SQL/PL/SQL的时侯;
备份/恢复数据库的时侯;
其它一些情况下．．．．．．
在论坛上也时常可以看到初级DBA对这个问题的求救. 在这里简单的对该问题进行一下整理.不当之处,请多指教！

错误原因种种

根据网络上大家反映的情况来看,错误原因大约有这些：

Unix核心参数设置不当
Oracle执行文件权限不正确／环境变量问题
客户端通信不能正确处理
数据库服务器崩溃/操作系统崩溃/进程被kill
Oracle 内部错误
特定SQL、PL/SQL引起的错误
空间不够
防火墙的问题
其它原因
在开始解决问题之前,作如下几件事情：

回忆一下在出现错误之前你都做了什么操作,越详细越好;
查看background_dump_dest目录中的alertSID.log文件也是你要做的事情;
Google一下,在互联网上有很多信息等着你去发现,不要什么都问别人.
当然, 如果你找到了一些对你更有帮助的东西――这篇文档就不用看了 :)

Unix核心参数设置不当/ init参数设置不当

如果数据库在安装过程中没有设定正确的操作系统核心变量,可能在安装数据库文件的时侯
没甚么问题,在创建数据库的时侯常常会出现03113错误.和此有关的另一个原因是init.ora
参数文件中的processes参数指定了不合理的值,启动数据库导致错误出现(当然这个归根到
底也是核心参数的问题).

这个错误信息一般如下：

ORA-03113: end-of-file on communication channel
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist

解决办法有两个：

1修改核心参数,加大相应核心参数的值(推荐);
2减小init.ora参数的Processes的值.

需要注意的是：

SEMMSL必须设定为至少要10 +　进程数的最大值.
SEMMNS 也依赖于每个数据库上的进程参数值.
-------------------------------------------------------------------------------
注：
这个错误类型只在Unix平台上出现.在Windows上如果processes的值过大,则会出现：

ORA-00068: invalid value 24200001 for parameter max_rollback_segments, must be
between 2 and 65535

/* 此时指定的参数值超过了65535 */

或者

ORA-27102: out of memory　

/* 小于65535的一个大参数值 */

我的软件环境：

Windows 2000 Version 5.0 Service Pack 3, CPU type 586
ORACLE RDBMS Version: 8.1.7.0.0.
-------------------------------------------------------------------------------

在特定平台上更改核心参数可能会有差别,请参考Oracle
Technet( http://otn.oracle.com)上的安装文档.对特定Unix平台的安装文档也有对核心参
数意义的解释.

Init.ora中的参数如果设置不当,会产生该错误.有经验表明：shared_pool_size设置过小会
出现错误,此外timed_statistics=true的设置也会带来问题.

Oracle执行文件权限不正确/环境变量问题

这个问题只出现在Unix平台上.常见情况是有的时侯管理员为了方便而使用Unix
的tar命令处理过的压缩包进行的安装,或者是系统管理员指定了额外的OS用户也可以管理数
据库却没有指定正确的环境变量.

Oracle执行文件在$ORACLE_HOME/bin目录下,如果出现问题,应该用如下Unix类似命令来纠正：

chmod 7755 $ORACLE_HOME/bin/oracle

有的时侯要对Oracle进行relink操作.
在Unix上通过cp拷贝安装的时候,常常会出现环境变量的问题,和个别执行程序连接问题.LD_
LIBRARY_PATH如果设置的不正确会导致问题,在这种情况下,需要对Oracle进行relink.如果
可执行文件oralcle被破坏,也要对其relink.

如果安装了并行服务器选项而Distributed Lock Manager没有安装或正确运行也会导致错误.
客户端通信不能正确处理

SQL*Net驱动器的问题：
如果使用的版本比较低的驱动器,请更换到新版本的驱动.SQL*Net
的驱动没有连接到Oracle可执行文件会导致错误.

检查网络是否通畅

Windows平台的常见问题：

在Windows平台创建数据库的时侯,如果出现该问题可以考虑用如下的方法：
首先检查本地网络设置.查看网络上是否有同名的结点或有冲突的IP.如果问题依旧,可以保
守的用下面的方法：

1. 禁用网卡：将本地连接状态改为禁用;
2. 将sqlnet.ora文件打开(以记事本形式)将nts验证注释掉：
<&>#SQLNET.AUTHENTICATION_SERVICES= (NTS).
3. 创建数据库;
4. 创建成功后,恢复本地连接.
数据库服务器崩溃/操作系统崩溃/进程被Kill

在连接过程中,如果Oracle数据库的服务器崩溃或者数据库所在的操作系统崩溃,就会出现这
个错误.Oracle
Server崩溃的原因可能因为主要后台进程死掉.被错误的进行了Kill操作.如果是这个原因还
是比较容易解决的.此外,和OS有关的应用程序存在内存泄漏(或者有病毒)的时侯也会导致Or
acle后台程序问题.

推荐排错办法：

1、查看应用软件相关进程是否正常运行;
2、查看有无内存泄漏;
3、查杀病毒;
4、确定系统管理员没有进行误操作;
5、确定无黑客入侵行为.
6、其它不确定因素．．．．．．
Oracle 内部错误/ Bug

如果查看background_dump_dest目录中的alert.log发现有无ora-600等错误,可以到Metalin
k站点上查看具体信息及其解决方案.一般情况下要打软件补丁.

特定SQL、PL/SQL引起的错误

尝试把SQL进行分开执行,也可以用SQL_TRACE来进行跟踪,找到导致问题的SQL语句：
在SQLPlus下：

ALTER SESSION SET SQL_TRACE=TRUE;

SQL语句中的非法字符和不合理的处理结果偶尔会带来问题.

系统空间不够

任何时侯都要确保数据库系统有足够的空间.如果 USER_DUMP_DEST
和BACKGROUND_DUMP_DEST没有剩余空间的话,会导致此问题.此外,如果打开了审计,AUDIT目
录要由足够的空间.如果激活了Trace的话,Trace目录要由足够的空间.

Dave Wotton的文档表明,在对表进行插入数据的时侯,如果文件超过了2G
(而文件系统有2G限制),会导致该问题.

防火墙的问题

如果数据要通过防火墙,请联系系统管理员,询问是否对数据库数据进行了过滤或者是突然禁
止了通行端口.如本地安装有个人防火墙,请检查本地设置.

其它方面说明

导致这个错误的原因有很多种,上面列到的只是一些典型情况.经常去一些数据库技术论坛可
能会有帮助.比如说ITPUB( http://www.itpub.net )、CNOUG( http://www.cnoug.org )等.

参考信息/更多阅读

http://metalink.oracle.com
Oracle的技术支持站点,要有CSI号码才可以登录.

参考Note编号：
Note:17613.1
ORA-3113 on Unix - What Information to Collect
NOTE:131207.1
How to Set UNIX Environment Variables
Note:131321.1
How to Relink Oracle Database Software on UNIX
Note:22080.1

http://www.jlcomp.demon.co.uk/faq/ORA-3113.html
技术专家Jonathan Lewis的站点上的一个FAQ

9i中 UNDO 表空间损坏，如何恢复？

===========================================================

Errors in file d:oracleadmincvaludumpcval_j000_1136.trc:
ORA-12012: 自动执行作业 30 出错
ORA-01115: 从文件 2 读取块时出现 IO 错误 (块 # 1287)
ORA-01110: 数据文件 2: 'D:ORACLEORADATACVALUNDOTBS01.DBF'
ORA-27091: skgfqio: 无法进行 I/O 操作
ORA-27070: skgfdisp: 异步读取/写入失败
OSD-04006: ReadFile() 失败, 无法读取文件
O/S-Error: (OS 1117) 因为 I/O 设备错误，所以无法运行此项请求。

未归档模式。

有没有办法不重建数据库，简单一点的办法恢复？
-------------------------------------------------------------
1 删除undotbs01.dbf
2 连接到数据库
SQL> connect sys/oracle as sysdba
Connected.
SQL> startup force
ORACLE instance started.

Total System Global Area 336662768 bytes
Fixed Size                   450800 bytes
Variable Size             117440512 bytes
Database Buffers          218103808 bytes
Redo Buffers                 667648 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 2 - see DBWR trace file
ORA-01110: data file 2: '/home/oracle/oradata/esal/undotbs01.dbf'
3 查看rollback_segments
SQL> show parameter rollback

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
fast_start_parallel_rollback         string      LOW
max_rollback_segments                integer     37
rollback_segments                    string
transactions_per_rollback_segment    integer     5
4 修改初始化参数
###########################################
# System Managed Undo and Rollback Segments
###########################################
undo_management=MANUAL
undo_retention=10800
undo_tablespace=UNDOTBS01
rollback_segments='SYSTEM'
5 启动数据库
SQL> connect sys/oracle as sysdba
Connected.
SQL> startup force
ORACLE instance started.

SQL> alter database /home/oracle/oradata/esal/undotbs01.dbf' offline;
alter database /home/oracle/oradata/esal/undotbs01.dbf' offline
*
ERROR at line 1:
ORA-02231: missing or invalid option to ALTER DATABASE

SQL> alter database datafile '/home/oracle/oradata/esal/undotbs01.dbf' offline drop;

Database altered.

SQL> alter database open;

Database altered.
---------------------------------------------------------------------
SQL> create spfile from pfile;

File created.

SQL> create undo tablespace undotbs1 datafile '/home/oracle/oradata/esal/undotbs01.dbf' size 200M autoextend on;
create undo tablespace undotbs1 datafile '/home/oracle/oradata/esal/undotbs01.dbf' size 200M autoextend on
*
ERROR at line 1:
ORA-01543: tablespace 'UNDOTBS1' already exists

SQL> drop tablespace undtotbs1;
drop tablespace undtotbs1
*
ERROR at line 1:
ORA-00959: tablespace 'UNDTOTBS1' does not exist

SQL> drop tablespace undotbs1;

Tablespace dropped.

SQL> create undo tablespace undotbs1 datafile '/home/oracle/oradata/esal/undotbs01.dbf' size 200M autoextend on;

Tablespace created.

SQL> show parameter undo

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      MANUAL
undo_retention                       integer     10800
undo_suppress_errors                 boolean     FALSE
undo_tablespace                      string      UNDOTBS01
SQL> alter system set undo_management=auto scope=both;
alter system set undo_management=auto scope=both
                 *
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified

SQL> alter system set undo_management=auto scope=spfile;
alter system set undo_management=auto scope=spfile
*
ERROR at line 1:
ORA-32001: write to SPFILE requested but no SPFILE specified at startup

###########################################
# System Managed Undo and Rollback Segments
###########################################
undo_management=AUTO
undo_retention=10800
undo_tablespace=UNDOTBS1
#rollback_segments='SYSTEM'
"initxzh.ora" 99L, 2989C written
[oracle@WWW2 dbs]$ sqlplus /nolog

SQL*Plus: Release 9.2.0.1.0 - Production on Mon Jul 5 13:17:17 2004

SQL> connect sys/oracle@xzh as sysdba
Connected.
SQL> startup force
ORACLE instance started.

Total System Global Area 336662768 bytes
Fixed Size                   450800 bytes
Variable Size             117440512 bytes
Database Buffers          218103808 bytes
Redo Buffers                 667648 bytes
Database mounted.
Database opened.
-------------------------------------------------------------
我做过试验的，如果数据库非归档模式下，而且当某一个回滚段上有活动的事务，如果这个回滚段突然坏掉，这个时候是不能正常shutdown的，只能shutdown abot，然后重起到mount状态，用alter database drop 掉坏掉的数据文件，可是在执行这个操作的时候，系统提示这个数据文件里有active的回滚段，不能被drop掉，而这时，数据库又不能open，接下来该怎么办？各位大侠

如上: 我也做过测试.
修改9I 的init文件 undo_management=manual 以后再启动mount数据库, 这时应该可以offline drop 文件. 但是还是open 不了. 抱ora -01092错误. alert日志了依次报 604,307,1110 错误;
我还又采用了init 的_allow_resetlogs_corruption 隐含参数, 将日志文件删除,然后recover database until cancel 再直接用open resetlogs 晴空日志, 第1次成功open 了,但很奇怪的是当我想测试日志文件完好而回滚段文件损坏错误的时候,再次运用刚才隐含参数的方法,系统居然又报01092的错误,回到了刚才错误 ,alert日志报604及600错误.
Errors in file d:oracleadminoracle9iudumporacle9i_ora_2704.trc:
ORA-00604: µÝ¹é SQL ²ã 1 ³öÏÖ´íÎó
ORA-00607: µ±¸ü¸ÄÊý¾Ý¿éÊ±³öÏÖÄÚ²¿´íÎó
ORA-00600: ÄÚ²¿´íÎó´úÂë£¬²ÎÊý: [4194], [36], [24], [], [], [], [], []

还有这个东西还是有偶然性,内部的东西还希望高手指点.

liujinxing

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
2
评论
转）oracle数据库相关

oracle数据库的性能调整 =========================================================== 　　　　oracle是一个高性能数据库软件。用户可以通过参数的调整，达到性能的优化。性能优化主要分为两部分：一是数据库管理员通过对系统参数的调整达到优化的目的，二是开发人员通过对应用程序的优化达到调整的目的。　　在此，仅就系统参数的调
复制链接

扫一扫