Oracle9i
数据库系统优化与调整
目 录
Oracle9i.............................................................................................1
数据库系统优化与调整........................................................................1
第一部分 ORACLE 系统优化基本知识..............................................71
第 1 章 ORACLE 结构回顾................................................................71
§1.1 Oracle 数据库结构............................................................................................71
§1.1.1 Oracle数据字典.......................................................................................................71
§1.1.2 表空间与数据文件.....................................................................................................72
§1.1.3 Oracle 实例(Instance)..................................................................................................72
Oracle 实例是由一组后台进程和内存结构组成。每个运行的数据库系
统都与实例有关。.............................................................................72
Oracle 进程.......................................................................................73
Oracle 的内存结构(SGA)..................................................................73
§1.2 Oracle 文件........................................................................................................74
§1.2.1 数据文件.....................................................................................................................74
§1.2.2 控制文件.....................................................................................................................74
§1.2.3 重做日志文件.............................................................................................................74
§1.2.4 其它支持文件.............................................................................................................75
§1.3 数据块、区间和段............................................................................................76
§1.3.1 数据块（data block）.................................................................................................76
§1.3.2 区间（extent）...........................................................................................................76
§1.3.3 段(segment).................................................................................................................76
§1.4 SQL 语句处理...................................................................................................77
§1.4.1 SQL 语句处理顺序.....................................................................................................78
§1.4.2 COMMIT 语句处理顺序............................................................................................80
Oracle 处理 COMMIT 的顺序是：.....................................................80
Oracle 处理 ROLLBACK 的顺序是：.................................................80
§1.5 共享池................................................................................................................81
§1.6 块缓存(数据高速缓冲区).................................................................................81
§1.7 数据库写入进程................................................................................................82
2
2
§1.8 日志写进程........................................................................................................82
§1.9 数据库检查点....................................................................................................82
ALTER system switch logfile;............................................................83
§1.10 归档处理..........................................................................................................83
ALTER SYSTEM ARCHIVE LOG START;........................................83
§1.11 程序全局区（PGA）......................................................................................83
第 2 章 警告日志与跟踪日志..............................................................83
§2.1 警告与日志文件................................................................................................84
§2.1.1 经常查看警告日志文件内容.....................................................................................84
§2.1.2 熟悉警告日志的事件或消息.....................................................................................84
LICENSE_MAX_SESSION=0...........................................................84
LICENSE_SESSION_WARNING=0.................................................84
Completed: alter database open.......................................................84
Sat Mar 20 07:59:12 1999................................................................85
Completed:ALTER DATABASE DISMOUNT....................................85
Sun May 23 11:52 2000....................................................................85
Thu Jul 1 16:32:11 2000...................................................................85
Completed checkpoint up to RBA
[0x763.2.10]，SCN:0x0000.076684d5.............................................85
Thu Jul 1 16:37:46 2000...................................................................85
Thu Jul 1 16:55:27 2000...................................................................85
Thu Jul 1 17:05:11 2000...................................................................86
3
3
Thu Jul 13 19:42:34 2000.................................................................86
Sun Jul 04 21:32:58 2000.................................................................86
Thu Jul 1 21:54:04 2000...................................................................87
Thu Jul 1 22:14:50 2000...................................................................87
The Jul 1 22:26:45 2000...................................................................87
Alter database archivelog.................................................................87
Completed:alter database archivelog...............................................87
§2.1.3 分析警告日志错误.....................................................................................................88
Thu Mar 13 13:37:49 1999...............................................................88
Mon Aug 10 13:44:52 1999...............................................................88
Mon jan 6 16:54:22 1999..................................................................89
Thread 1 cannot allocate new log，sequence 1881.........................90
Checkpoint not complete..................................................................90
Sat Dec 12 16:21:07 1999................................................................90
Mon Jul 7 23:18:48 1999..................................................................90
Error in file
/opt/apps/oracle/admin/DT08/bdump/pmon_DT08_16593.trc:.........91
ORA-00600 : internal error code ，argments:[6856]，[0]，[0] ，[]，
[]，[]，[].............................................................................................91
4
4
Mon Jul 7 23:18:57 1999..................................................................91
Errors in file
/opt/apps/oracle/admin/DT08/bdump/pmon_DT08_16593.trc:.........91
ORA-01578 :ORACLE data block corrupted ( file # 10 ， block #
32379 )..............................................................................................91
ORA-01110:data file
10:’/opt/apps/oracle/odata8/DT08/usr_dbf/miki_data01.dbf’............91
ORA-00600 : internal error code ，argments:[6856]，[0]，[0] ，[]，
[]，[]，[].............................................................................................91
Tue Jun 22 00:02:27 1999................................................................91
Errors in file
/opt/apps/oracle/admin/DT08/udump/ora_DT08_9949.trc:...............91
ORA-00600:internet error code，argments: [4406]， [2306963552]，
..........................................................................................................91
ORA-00060:deadlock detected while waiting for resource...............91
§2.2 对警告日志进行归档........................................................................................91
§2.3 跟踪文件............................................................................................................92
§2.3.1 跟踪文件的产生.........................................................................................................92
SQL>select name, description from v$bgprocess;...........................92
§2.3.2 分析跟踪文件的信息.................................................................................................93
SQL>select segment_name, segment_type from dba_segments....93
§2.3.3 跟踪文件的内容.........................................................................................................93
§2.4 关于 NET 的日志与跟踪..................................................................................95
5
5
