1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48

┌─────────────────────────────────────────────────────────────────┐
│                    Oracle数据库整体架构                              │
├─────────────────────────────────────────────────────────────────┤
│                                                                 │
│   ┌─────────────────────────────────────────────────────────┐   │
│   │                                                         │   │
│   │                    用户进程                              │   │
│   │                    (User Process)                       │   │
│   │                        │                                │   │
│   │                        │ SQL语句                         │   │
│   │                        ▼                                │   │
│   │   ┌─────────────────────────────────────────────────┐   │   │
│   │   │                 连接进程                          │   │   │
│   │   │              (Server Process)                   │   │   │
│   │   │                     │                            │   │   │
│   │   │                     │ 操作                       │   │   │
│   │   │   ┌────────────────┼────────────────┐          │   │   │
│   │   │   │                │                │          │   │   │
│   │   │   ▼                ▼                ▼          │   │   │
│   │   │ ┌────────┐   ┌────────┐   ┌────────────┐      │   │   │
│   │   │ │ PGA    │   │ SGA    │   │  后台进程  │      │   │   │
│   │   │ │(私有区)│   │(共享区)│   │(Background)│     │   │   │
│   │   │ └────────┘   └────────┘   └────────────┘      │   │   │
│   │   └─────────────────────────────────────────────────┘   │   │
│   │                        │                                │   │
│   │                        │ 数据读写                        │   │
│   │                        ▼                                │   │
│   │   ┌─────────────────────────────────────────────────┐   │   │
│   │   │                 数据文件                         │   │   │
│   │   │   ┌────────┐  ┌────────┐  ┌────────┐          │   │   │
│   │   │   │System  │  │ Sysaux │  │  User  │          │   │   │
│   │   │   │表空间  │  │表空间  │  │表空间  │          │   │   │
│   │   │   └────────┘  └────────┘  └────────┘          │   │   │
│   │   └─────────────────────────────────────────────────┘   │   │
│   │                                                         │   │
│   └─────────────────────────────────────────────────────────┘   │
│                                                                 │
│   ┌─────────────────────────────────────────────────────────┐   │
│   │                   物理存储结构                          │   │
│   │                                                         │   │
│   │   ┌────────┐  ┌────────┐  ┌────────┐  ┌────────┐    │   │
│   │   │ Control│  │  Redo  │  │ Archive│  │  Alert │    │   │
│   │   │  Files │  │  Logs  │  │  Logs  │  │   Log  │    │   │
│   │   └────────┘  └────────┘  └────────┘  └────────┘    │   │
│   │                                                         │   │
│   └─────────────────────────────────────────────────────────┘   │
│                                                                 │
└─────────────────────────────────────────────────────────────────┘

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36

┌─────────────────────────────────────────────────────────────────┐
│                    Oracle核心组件关系图                            │
├─────────────────────────────────────────────────────────────────┤
│                                                                 │
│   ┌─────────────────────────────────────────────────────────┐   │
│   │                                                         │   │
│   │                    Oracle Instance                      │   │
│   │                    (Oracle实例)                        │   │
│   │                                                         │   │
│   │   ┌───────────────────┬───────────────────────┐       │   │
│   │   │                   │                       │       │   │
│   │   ▼                   ▼                       ▼       │   │
│   │ ┌─────────┐     ┌─────────┐           ┌───────────┐   │   │
│   │ │   SGA   │     │  PGA   │           │ 后台进程  │   │   │
│   │ │共享内存区│     │私有内存区│           │           │   │   │
│   │ └────┬────┘     └─────────┘           └─────┬─────┘   │   │
│   │      │                                      │         │   │
│   └──────┼──────────────────────────────────────┼─────────┘   │
│          │                                      │             │
│          │          ┌──────────────────────────┘             │
│          │          │                                        │
│          ▼          ▼                                        │
│   ┌─────────────────────────────────────────────────────┐      │
│   │               Oracle Database                          │      │
│   │               (Oracle数据库)                          │      │
│   │                                                        │      │
│   │   ┌──────────┐  ┌──────────┐  ┌──────────┐         │      │
│   │   │ 数据文件  │  │ 控制文件  │  │ 日志文件  │         │      │
│   │   │  (Dbf)   │  │  (CTL)   │  │  (LOG)   │         │      │
│   │   └──────────┘  └──────────┘  └──────────┘         │      │
│   │                                                        │      │
│   └────────────────────────────────────────────────────────┘      │
│                                                                 │
│   关键点：Instance和Database是一对一关系                         │
│                                                                 │
└─────────────────────────────────────────────────────────────────┘

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39

┌─────────────────────────────────────────────────────────────────┐
│                    Oracle逻辑存储层次结构                          │
├─────────────────────────────────────────────────────────────────┤
│                                                                 │
│   ┌─────────────────────────────────────────────────────────┐   │
│   │                                                         │   │
│   │                      Database                            │   │
│   │                      (数据库)                            │   │
│   │                         │                               │   │
│   │                         ▼                               │   │
│   │              ┌─────────────────┐                       │   │
│   │              │    Tablespace    │                       │   │
│   │              │     (表空间)     │                       │   │
│   │              └────────┬────────┘                       │   │
│   │                       │                               │   │
│   │                       ▼                               │   │
│   │              ┌─────────────────┐                       │   │
│   │              │     Segment      │                       │   │
│   │              │     (段)        │                       │   │
│   │              └────────┬────────┘                       │   │
│   │                       │                               │   │
│   │                       ▼                               │   │
│   │              ┌─────────────────┐                       │   │
│   │              │    Extent       │                       │   │
│   │              │     (区)        │                       │   │
│   │              └────────┬────────┘                       │   │
│   │                       │                               │   │
│   │                       ▼                               │   │
│   │              ┌─────────────────┐                       │   │
│   │              │     Block       │                       │   │
│   │              │    (数据块)     │                       │   │
│   │              └─────────────────┘                       │   │
│   │                                                         │   │
│   └─────────────────────────────────────────────────────────┘   │
│                                                                 │
│   大小关系：                                                   │
│   Block < Extent < Segment < Tablespace < Database            │
│                                                                 │
└─────────────────────────────────────────────────────────────────┘

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35

┌─────────────────────────────────────────────────────────────────┐
│                    数据块（Data Block）结构                        │
├─────────────────────────────────────────────────────────────────┤
│                                                                 │
│   ┌─────────────────────────────────────────────────────────┐   │
│   │                                                         │   │
│   │              Oracle Data Block (数据块)                   │   │
│   │                                                         │   │
│   │   ┌─────────────────────────────────────────────────┐   │   │
│   │   │              Block Header (块头)                  │   │   │
│   │   │   ├── Cache Layer (缓存层)                      │   │   │
│   │   │   ├── Row Directory (行目录)                   │   │   │
│   │   │   ├── Table Directory (表目录)                  │   │   │
│   │   │   └── Free Space (可用空间)                     │   │   │
│   │   ├─────────────────────────────────────────────────┤   │   │
│   │   │              Row Data (行数据)                   │   │   │
│   │   │                                                 │   │   │
│   │   │   ┌─────────────────────────────────────────┐   │   │   │
│   │   │   │  Row 1  │  Row 2  │  Row 3  │ ...   │   │   │   │
│   │   │   └─────────────────────────────────────────┘   │   │   │
│   │   │                                                 │   │   │
│   │   └─────────────────────────────────────────────────┘   │   │
│   │                                                         │   │
│   └─────────────────────────────────────────────────────────┘   │
│                                                                 │
│   块大小配置：                                                 │
│   ├── 默认：8KB                                               │
│   ├── 常见：2KB, 4KB, 8KB, 16KB, 32KB                       │
│   └── 最大：65536字节 (64KB)                                  │
│                                                                 │
│   行链接与行迁移：                                             │
│   ├── 行链接：行太大，跨越多个块                              │
│   └── 行迁移：行被更新变大，整行移动到新块                      │
│                                                                 │
└─────────────────────────────────────────────────────────────────┘

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34

┌─────────────────────────────────────────────────────────────────┐
│                    区（Extent）管理                               │
├─────────────────────────────────────────────────────────────────┤
│                                                                 │
│   区的定义：                                                    │
│   ├── 区是逻辑上连续的数据块集合                               │
│   ├── 由多个连续的数据块组成                                   │
│   └── 是空间分配的最小单位                                     │
│                                                                 │
│   区的分配过程：                                               │
│   ┌─────────────────────────────────────────────────────────┐   │
│   │                                                         │   │
│   │   创建表时：                                           │   │
│   │   ├── 初始分配一个区 (Initial Extent)                 │   │
│   │   └── 区大小由表空间决定                               │   │
│   │                                                         │   │
│   │   区满时：                                             │   │
│   │   ├── 自动分配下一个区 (Next Extent)                   │   │
│   │   └── 直到达到最大区数量                               │   │
│   │                                                         │   │
│   └─────────────────────────────────────────────────────────┘   │
│                                                                 │
│   区的类型：                                                   │
│   ├── Initial Extent: 首次分配的区                            │
│   ├── Next Extent: 后续分配的区                              │
│   ├── Min Extents: 最小区数                                  │
│   └── Max Extents: 最大区数 (可设置为UNLIMITED)              │
│                                                                 │
│   查看区的信息：                                               │
│   SELECT extent_id, block_id, bytes, blocks                    │
│   FROM user_extents                                          │
│   WHERE segment_name = 'EMPLOYEES';                           │
│                                                                 │
└─────────────────────────────────────────────────────────────────┘

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38

┌─────────────────────────────────────────────────────────────────┐
│                    段（Segment）类型                             │
├─────────────────────────────────────────────────────────────────┤
│                                                                 │
│   ┌─────────────────────────────────────────────────────────┐   │
│   │                                                         │   │
│   │   段类型：                                               │   │
│   │                                                         │   │
│   │   ┌─────────────────────────────────────────────────┐   │   │
│   │   │  Table (表段)                                    │   │   │
│   │   │  ├── 普通表                                      │   │   │
│   │   │  ├── 分区表 (每个分区是一个段)                   │   │   │
│   │   │  └── 集群表 (多表共享存储)                       │   │   │
│   │   └─────────────────────────────────────────────────┘   │   │
│   │                                                         │   │
│   │   ┌─────────────────────────────────────────────────┐   │   │
│   │   │  Index (索引段)                                  │   │   │
│   │   │  ├── B-tree索引                                  │   │   │
│   │   │  ├── 位图索引                                    │   │   │
│   │   │  └── 函数索引                                    │   │   │
│   │   └─────────────────────────────────────────────────┘   │   │
│   │                                                         │   │
│   │   ┌─────────────────────────────────────────────────┐   │   │
│   │   │  Temporary (临时段)                              │   │   │
│   │   │  ├── SQL排序操作                                │   │   │
│   │   │  └── GROUP BY/ORDER BY/JOIN                    │   │   │
│   │   └─────────────────────────────────────────────────┘   │   │
│   │                                                         │   │
│   │   ┌─────────────────────────────────────────────────┐   │   │
│   │   │  Undo (回滚段)                                  │   │   │
│   │   │  ├── 事务回滚                                   │   │   │
│   │   │  ├── 读一致性                                   │   │   │
│   │   │  └── 数据库恢复                                 │   │   │
│   │   └─────────────────────────────────────────────────┘   │   │
│   │                                                         │   │
│   └─────────────────────────────────────────────────────────┘   │
│                                                                 │
└─────────────────────────────────────────────────────────────────┘

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40

┌─────────────────────────────────────────────────────────────────┐
│                    表空间（Tablespace）详解                        │
├─────────────────────────────────────────────────────────────────┤
│                                                                 │
│   ┌─────────────────────────────────────────────────────────┐   │
│   │                                                         │   │
│   │   系统表空间：                                          │   │
│   │   ├── SYSTEM: 存储数据字典                             │   │
│   │   ├── SYSAUX: 辅助系统表空间                          │   │
│   │   └── UNDO_TBS: 回滚表空间                            │   │
│   │                                                         │   │
│   │   非系统表空间：                                        │   │
│   │   ├── USER_TBS: 用户数据表空间                         │   │
│   │   ├── TEMP_TBS: 临时表空间                            │   │
│   │   └── 自定义表空间                                     │   │
│   │                                                         │   │
│   └─────────────────────────────────────────────────────────┘   │
│                                                                 │
│   表空间管理方式：                                             │
│   ┌─────────────────────────────────────────────────────────┐   │
│   │                                                         │   │
│   │   字典管理 (Dictionary Managed - 默认到Oracle 9i)      │   │
│   │   ├── 区分配信息存储在数据字典中                       │   │
│   │   └── 缺点：争用数据字典                              │   │
│   │                                                         │   │
│   │   本地管理 (Locally Managed - 推荐)                    │   │
│   │   ├── 区分配信息存储在表空间头部的位图中              │   │
│   │   ├── 自动管理 (AUTOALLOCATE)                        │   │
│   │   └── 统一大小 (UNIFORM SIZE)                        │   │
│   │                                                         │   │
│   └─────────────────────────────────────────────────────────┘   │
│                                                                 │
│   创建表空间示例：                                            │
│   CREATE TABLESPACE user_data                                  │
│     DATAFILE '/u01/oradata/db01/user_data01.dbf' SIZE 100M    │
│     AUTOEXTEND ON NEXT 10M MAXSIZE 10G                        │
│     EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M                   │
│     SEGMENT SPACE MANAGEMENT AUTO;                            │
│                                                                 │
└─────────────────────────────────────────────────────────────────┘

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69

┌─────────────────────────────────────────────────────────────────┐
│                    Oracle物理文件结构                              │
├─────────────────────────────────────────────────────────────────┤
│                                                                 │
│   ┌─────────────────────────────────────────────────────────┐   │
│   │                                                         │   │
│   │                 Oracle Database (物理存储)                 │   │
│   │                                                         │   │
│   │   ┌─────────────────────────────────────────────────┐   │   │
│   │   │              数据文件 (Data Files)                │   │   │
│   │   ├─────────────────────────────────────────────────┤   │   │
│   │   │                                                 │   │   │
│   │   │   ├── *.dbf 文件                                │   │   │
│   │   │   ├── 属于表空间                               │   │   │
│   │   │   ├── 存储表、索引、数据                       │   │   │
│   │   │   └── 可设置自动扩展                           │   │   │
│   │   │                                                 │   │   │
│   │   └─────────────────────────────────────────────────┘   │   │
│   │                                                         │   │
│   │   ┌─────────────────────────────────────────────────┐   │   │
│   │   │              控制文件 (Control Files)             │   │   │
│   │   ├─────────────────────────────────────────────────┤   │   │
│   │   │                                                 │   │   │
│   │   │   ├── *.ctl 文件                               │   │   │
│   │   │   ├── 数据库结构信息                           │   │   │
│   │   │   ├── 数据文件位置                            │   │   │
│   │   │   ├── 日志文件位置                            │   │   │
│   │   │   ├── 检查点信息                              │   │   │
│   │   │   └── 建议至少3份冗余                        │   │   │
│   │   │                                                 │   │   │
│   │   └─────────────────────────────────────────────────┘   │   │
│   │                                                         │   │
│   │   ┌─────────────────────────────────────────────────┐   │   │
│   │   │              在线日志文件 (Online Redo Logs)     │   │   │
│   │   ├─────────────────────────────────────────────────┤   │   │
│   │   │                                                 │   │   │
│   │   │   ├── *.log 文件                              │   │   │
│   │   │   ├── 记录所有事务                           │   │   │
│   │   │   ├── 用于实例恢复                           │   │   │
│   │   │   ├── 最少2组，建议3+组                     │   │   │
│   │   │   └── 循环写入                              │   │   │
│   │   │                                                 │   │   │
│   │   └─────────────────────────────────────────────────┘   │   │
│   │                                                         │   │
│   │   ┌─────────────────────────────────────────────────┐   │   │
│   │   │              归档日志文件 (Archive Logs)          │   │   │
│   │   ├─────────────────────────────────────────────────┤   │   │
│   │   │                                                 │   │   │
│   │   │   ├── 在线日志的历史副本                       │   │   │
│   │   │   ├── 启用归档模式后产生                       │   │   │
│   │   │   ├── 用于介质恢复                            │   │   │   │
│   │   │   └── 必须保留，用于恢复                      │   │   │
│   │   │                                                 │   │   │
│   │   └─────────────────────────────────────────────────┘   │   │
│   │                                                         │   │
│   │   ┌─────────────────────────────────────────────────┐   │   │
│   │   │              其他重要文件                          │   │   │
│   │   ├─────────────────────────────────────────────────┤   │   │
│   │   │                                                 │   │   │
│   │   │   ├── Alert Log: 警告日志                      │   │   │
│   │   │   ├── Trace Files: 跟踪文件                    │   │   │
│   │   │   ├── Parameter File: 参数文件                 │   │   │
│   │   │   └── Password File: 密码文件                  │   │   │
│   │   │                                                 │   │   │
│   │   └─────────────────────────────────────────────────┘   │   │
│   │                                                         │   │
│   └─────────────────────────────────────────────────────────┘   │
│                                                                 │
└─────────────────────────────────────────────────────────────────┘

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39

┌─────────────────────────────────────────────────────────────────┐
│                    控制文件内容详解                                │
├─────────────────────────────────────────────────────────────────┤
│                                                                 │
│   控制文件记录的关键信息：                                       │
│                                                                 │
│   ┌─────────────────────────────────────────────────────────┐   │
│   │                                                         │   │
│   │   1. 数据库信息                                        │   │
│   │   ├── 数据库名称                                       │   │
│   │   ├── 创建时间                                       │   │
│   │   └── 数据库唯一标识 (DBID)                          │   │
│   │                                                         │   │
│   │   2. 数据文件信息                                      │   │
│   │   ├── 文件名称和路径                                 │   │
│   │   ├── 检查点SCN                                      │   │
│   │   └── 文件状态 (ONLINE/OFFLINE)                      │   │
│   │                                                         │   │
│   │   3. 日志文件信息                                      │   │
│   │   ├── 日志组编号                                     │   │
│   │   ├── 日志成员路径                                   │   │
│   │   ├── 序列号                                        │   │
│   │   └── 状态 (ACTIVE/CURRENT/INACTIVE)                │   │
│   │                                                         │   │
│   │   4. 检查点信息                                        │   │
│   │   ├── 检查点SCN                                      │   │
│   │   └── 检查点进度信息                                  │   │
│   │                                                         │   │
│   │   5. 备份信息                                        │   │
│   │   ├── RMAN备份元数据                                │   │
│   │   └── 备份集信息                                    │   │
│   │                                                         │   │
│   └─────────────────────────────────────────────────────────┘   │
│                                                                 │
│   查看控制文件信息：                                            │
│   SQL> SELECT name, sequence#, status FROM v$log;              │
│   SQL> SELECT name, checkpoint_change# FROM v$datafile;         │
│                                                                 │
└─────────────────────────────────────────────────────────────────┘

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43

┌─────────────────────────────────────────────────────────────────┐
│                    重做日志（Redo Log）机制                        │
├─────────────────────────────────────────────────────────────────┤
│                                                                 │
│   重做日志的作用：                                              │
│   ├── 记录所有数据的修改                                       │
│   ├── 用于实例恢复                                             │
│   └── 用于介质恢复                                             │
│                                                                 │
│   ┌─────────────────────────────────────────────────────────┐   │
│   │                                                         │   │
│   │   日志写入机制：                                        │   │
│   │                                                         │   │
│   │   ┌─────────────────────────────────────────────────┐   │   │
│   │   │                                                 │   │   │
│   │   │     Group 1 (Member1.log)                       │   │   │
│   │   │            │                                   │   │   │
│   │   │            │  当前组                            │   │   │
│   │   │            ▼                                    │   │   │
│   │   │     Group 2 (Member1.log)                      │   │   │
│   │   │            │                                   │   │   │
│   │   │            │  归档中                           │   │   │
│   │   │            ▼                                    │   │   │
│   │   │     Group 3 (Member1.log)                      │   │   │
│   │   │            │                                   │   │   │
│   │   │            │  等待使用                         │   │   │
│   │   │            ▼                                    │   │   │
│   │   │       [循环回到Group 1]                        │   │   │
│   │   │                                                 │   │   │
│   │   └─────────────────────────────────────────────────┘   │   │
│   │                                                         │   │
│   └─────────────────────────────────────────────────────────┘   │
│                                                                 │
│   LGWR写入时机：                                               │
│   ├── 事务提交时 (COMMIT)                                    │
│   ├── 日志缓冲区1/3满时                                      │
│   ├── 日志缓冲区达到1MB时                                    │
│   └── 每3秒一次                                              │
│                                                                 │
│   日志切换：                                                  │
│   ALTER SYSTEM SWITCH LOGFILE;                               │
│                                                                 │
└─────────────────────────────────────────────────────────────────┘

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68

┌─────────────────────────────────────────────────────────────────┐
│                    SGA（System Global Area）                      │
├─────────────────────────────────────────────────────────────────┤
│                                                                 │
│   ┌─────────────────────────────────────────────────────────┐   │
│   │                                                         │   │
│   │                    SGA (System Global Area)               │   │
│   │                    共享内存区域                           │   │
│   │                                                         │   │
│   │   ┌─────────────────────────────────────────────────┐   │   │
│   │   │              Database Buffer Cache              │   │   │
│   │   │              (数据库缓冲区缓存)                   │   │   │
│   │   ├─────────────────────────────────────────────────┤   │   │
│   │   │                                                 │   │   │
│   │   │   ┌──────────┐ ┌──────────┐ ┌──────────┐     │   │   │
│   │   │   │   DEFAULT │ │   KEEP   │ │  RECYCLE │     │   │   │
│   │   │   │   POOL   │ │   POOL   │ │   POOL   │     │   │   │
│   │   │   └──────────┘ └──────────┘ └──────────┘     │   │   │
│   │   │                                                 │   │   │
│   │   │   功能：缓存数据块                              │   │   │
│   │   │   大小：由DB_CACHE_SIZE决定                    │   │   │
│   │   │                                                 │   │   │
│   │   └─────────────────────────────────────────────────┘   │   │
│   │                                                         │   │
│   │   ┌─────────────────────────────────────────────────┐   │   │
│   │   │              Shared Pool                        │   │   │
│   │   │              (共享池)                            │   │   │
│   │   ├─────────────────────────────────────────────────┤   │   │
│   │   │                                                 │   │   │
│   │   │   ┌──────────────┐ ┌──────────────┐           │   │   │
│   │   │   │  Library    │ │    Data    │           │   │   │
│   │   │   │   Cache     │ │  Dictionary │           │   │   │
│   │   │   │  (库缓存)   │ │   Cache    │           │   │   │
│   │   │   │ • SQL区    │ │ (数据字典) │           │   │   │
│   │   │   │ • PL/SQL   │ │ • 表定义   │           │   │   │
│   │   │   │ • 控制结构 │ │ • 权限信息 │           │   │   │
│   │   │   └──────────────┘ └──────────────┘           │   │   │
│   │   │                                                 │   │   │
│   │   └─────────────────────────────────────────────────┘   │   │
│   │                                                         │   │
│   │   ┌─────────────────────────────────────────────────┐   │   │
│   │   │              Redo Log Buffer                   │   │   │
│   │   │              (重做日志缓冲区)                   │   │   │
│   │   │                                                 │   │   │
│   │   │   功能：缓存重做条目                           │   │   │
│   │   │   大小：由LOG_BUFFER决定                      │   │   │
│   │   │                                                 │   │   │
│   │   └─────────────────────────────────────────────────┘   │   │
│   │                                                         │   │
│   │   ┌─────────────────────────────────────────────────┐   │   │
│   │   │              Large Pool                        │   │   │
│   │   │              (大池)                            │   │   │
│   │   │                                                 │   │   │
│   │   │   功能：RMAN备份/IO服务器/共享服务器           │   │   │
│   │   │                                                 │   │   │
│   │   └─────────────────────────────────────────────────┘   │   │
│   │                                                         │   │
│   │   ┌─────────────────────────────────────────────────┐   │   │
│   │   │              Java Pool                          │   │   │
│   │   │              (Java池)                           │   │   │
│   │   │                                                 │   │   │
│   │   │   功能：Java代码和共享内存                     │   │   │
│   │   │                                                 │   │   │
│   │   └─────────────────────────────────────────────────┘   │   │
│   │                                                         │   │
│   └─────────────────────────────────────────────────────────┘   │
│                                                                 │
└─────────────────────────────────────────────────────────────────┘

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52

┌─────────────────────────────────────────────────────────────────┐
│                    PGA（Program Global Area）                      │
├─────────────────────────────────────────────────────────────────┤
│                                                                 │
│   PGA定义：                                                    │
│   ├── 每个服务器进程私有的内存区域                              │
│   ├── 非共享                                                   │
│   └── 存储会话信息、排序区、游标信息等                          │
│                                                                 │
│   PGA组成：                                                   │
│   ┌─────────────────────────────────────────────────────────┐   │
│   │                                                         │   │
│   │                    PGA                                  │   │
│   │                                                         │   │
│   │   ┌─────────────────────────────────────────────────┐   │   │
│   │   │              Sort Area                           │   │   │
│   │   │              (排序区)                            │   │   │
│   │   │              • ORDER BY                         │   │   │
│   │   │              • GROUP BY                         │   │   │
│   │   │              • JOIN                             │   │   │
│   │   │              • DISTINCT                         │   │   │
│   │   └─────────────────────────────────────────────────┘   │   │
│   │                                                         │   │
│   │   ┌─────────────────────────────────────────────────┐   │   │
│   │   │              Session Information                  │   │   │
│   │   │              (会话信息)                          │   │   │
│   │   │              • 用户权限                         │   │   │
│   │   │              • 审计信息                         │   │   │
│   │   │              • 游标状态                         │   │   │
│   │   └─────────────────────────────────────────────────┘   │   │
│   │                                                         │   │
│   │   ┌─────────────────────────────────────────────────┐   │   │
│   │   │              Stack Space                        │   │   │
│   │   │              (栈空间)                           │   │   │
│   │   │              • 过程变量                        │   │   │
│   │   │              • 数组                           │   │   │
│   │   └─────────────────────────────────────────────────┘   │   │
│   │                                                         │   │
│   │   ┌─────────────────────────────────────────────────┐   │   │
│   │   │              Cursor Information                 │   │   │
│   │   │              (游标信息)                          │   │   │
│   │   │              • SQL语句                         │   │   │
│   │   │              • 执行计划                        │   │   │
│   │   └─────────────────────────────────────────────────┘   │   │
│   │                                                         │   │
│   └─────────────────────────────────────────────────────────┘   │
│                                                                 │
│   PGA配置建议：                                                │
│   ├── OLTP系统：PGA_AGGREGATE_TARGET = 内存20%                 │
│   └── OLAP系统：PGA_AGGREGATE_TARGET = 内存40%                 │
│                                                                 │
└─────────────────────────────────────────────────────────────────┘

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

-- 查看SGA配置
SHOW SGA;

-- 查看内存组件大小
SELECT component, current_size, min_size, max_size
FROM v$memory_dynamic_components
ORDER BY current_size DESC;

-- 修改SGA参数（动态）
ALTER SYSTEM SET sga_target = 2G SCOPE = BOTH;
ALTER SYSTEM SET sga_max_size = 4G SCOPE = SPFILE;

-- 修改PGA参数
ALTER SYSTEM SET pga_aggregate_target = 1G SCOPE = BOTH;

-- 自动内存管理 (AMM) - Oracle 11g+
ALTER SYSTEM SET memory_max_target = 8G SCOPE = SPFILE;
ALTER SYSTEM SET memory_target = 8G SCOPE = SPFILE;

-- 自动共享内存管理 (ASMM) - Oracle 10g+
ALTER SYSTEM SET sga_target = 4G;
ALTER SYSTEM SET sga_max_size = 6G SCOPE = SPFILE;
ALTER SYSTEM SET db_cache_size = 2G;
ALTER SYSTEM SET shared_pool_size = 1G;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39

┌─────────────────────────────────────────────────────────────────┐
│                    Oracle进程类型                                 │
├─────────────────────────────────────────────────────────────────┤
│                                                                 │
│   ┌─────────────────────────────────────────────────────────┐   │
│   │                                                         │   │
│   │                    用户进程 (User Process)                │   │
│   │                         │                               │   │
│   │                         ▼                               │   │
│   │               ┌─────────────────┐                        │   │
│   │               │  服务器进程     │                        │   │
│   │               │(Server Process)│                       │   │
│   │               └────────┬────────┘                        │   │
│   │                        │                                │   │
│   └───────────────────────┼─────────────────────────────────┘   │
│                            │                                    │
│                            ▼                                    │
│   ┌─────────────────────────────────────────────────────────┐   │
│   │                                                         │   │
│   │                    后台进程 (Background Processes)        │   │
│   │                                                         │   │
│   │   ┌───────────┐ ┌───────────┐ ┌───────────┐          │   │
│   │   │   SMON    │ │   PMON    │ │   DBWn   │          │   │
│   │   │  系统监控  │ │  进程监控  │ │  数据库写入│          │   │
│   │   └───────────┘ └───────────┘ └───────────┘          │   │
│   │                                                         │   │
│   │   ┌───────────┐ ┌───────────┐ ┌───────────┐          │   │
│   │   │   LGWR    │ │   CKPT    │ │   ARCH    │          │   │
│   │   │  日志写入  │ │ 检查点    │ │  归档进程  │          │   │
│   │   └───────────┘ └───────────┘ └───────────┘          │   │
│   │                                                         │   │
│   │   ┌───────────┐ ┌───────────┐ ┌───────────┐          │   │
│   │   │   RECO    │ │   CJQ0    │ │   MMAN    │          │   │
│   │   │  分布式恢复│ │  作业队列  │ │  内存管理  │          │   │
│   │   └───────────┘ └───────────┘ └───────────┘          │   │
│   │                                                         │   │
│   └─────────────────────────────────────────────────────────┘   │
│                                                                 │
└─────────────────────────────────────────────────────────────────┘

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89

┌─────────────────────────────────────────────────────────────────┐
│                    核心后台进程详解                                │
├─────────────────────────────────────────────────────────────────┤
│                                                                 │
│   ┌─────────────────────────────────────────────────────────┐   │
│   │  DBWn - Database Writer (数据库写入进程)                   │   │
│   ├─────────────────────────────────────────────────────────┤   │
│   │                                                         │   │
│   │   职责：                                                │   │
│   │   ├── 将脏数据块从缓冲区写入数据文件                     │   │
│   │   └── 释放缓冲区空间                                    │   │
│   │                                                         │   │
│   │   触发时机：                                            │   │
│   │   ├── 检查点发生时                                      │   │
│   │   ├── 缓冲区达到阈值                                   │   │
│   │   ├── 服务器进程找不到空闲缓冲区                        │   │
│   │   └── 表空间OFFLINE时                                  │   │
│   │                                                         │   │
│   │   n：可配置多个写进程 (DB_WRITER_PROCESSES)             │   │
│   │                                                         │   │
│   └─────────────────────────────────────────────────────────┘   │
│                                                                 │
│   ┌─────────────────────────────────────────────────────────┐   │
│   │  LGWR - Log Writer (日志写入进程)                       │   │
│   ├─────────────────────────────────────────────────────────┤   │
│   │                                                         │   │
│   │   职责：                                                │   │
│   │   ├── 将重做日志缓冲区写入在线日志文件                  │   │
│   │   └── 确保数据写入的持久性                              │   │
│   │                                                         │   │
│   │   触发时机：                                            │   │
│   │   ├── 用户提交事务 (COMMIT)                            │   │
│   │   ├── 日志缓冲区1/3满                                 │   │
│   │   ├── 日志缓冲区达到1MB                               │   │
│   │   └── 每3秒一次                                       │   │
│   │                                                         │   │
│   │   ⚠️ 重要：LGWR是数据库恢复的关键                      │   │
│   │                                                         │   │
│   └─────────────────────────────────────────────────────────┘   │
│                                                                 │
│   ┌─────────────────────────────────────────────────────────┐   │
│   │  SMON - System Monitor (系统监控进程)                   │   │
│   ├─────────────────────────────────────────────────────────┤   │
│   │                                                         │   │
│   │   职责：                                                │   │
│   │   ├── 实例恢复 (实例启动时)                            │   │
│   │   ├── 合并空闲空间                                    │   │
│   │   ├── 清理临时段                                      │   │
│   │   └── 维护SMON_SCN_TO_TIME映射                       │   │
│   │                                                         │   │
│   └─────────────────────────────────────────────────────────┘   │
│                                                                 │
│   ┌─────────────────────────────────────────────────────────┐   │
│   │  PMON - Process Monitor (进程监控进程)                   │   │
│   ├─────────────────────────────────────────────────────────┤   │
│   │                                                         │   │
│   │   职责：                                                │   │
│   │   ├── 清理失败的进程                                  │   │
│   │   ├── 回滚未完成的事务                                │   │
│   │   ├── 释放进程持有的锁                               │   │
│   │   └── 释放进程占用的资源                              │   │
│   │                                                         │   │
│   └─────────────────────────────────────────────────────────┘   │
│                                                                 │
│   ┌─────────────────────────────────────────────────────────┐   │
│   │  CKPT - Checkpoint (检查点进程)                         │   │
│   ├─────────────────────────────────────────────────────────┤   │
│   │                                                         │   │
│   │   职责：                                                │   │
│   │   ├── 通知DBWn写入数据                                │   │
│   │   ├── 更新控制文件中的检查点信息                       │   │
│   │   └── 减少实例恢复时间                                 │   │
│   │                                                         │   │
│   └─────────────────────────────────────────────────────────┘   │
│                                                                 │
│   ┌─────────────────────────────────────────────────────────┐   │
│   │  ARCH - Archiver (归档进程)                             │   │
│   ├─────────────────────────────────────────────────────────┤   │
│   │                                                         │   │
│   │   职责：                                                │   │
│   │   ├── 归档已满的在线日志文件                           │   │
│   │   ├── 保存事务历史                                    │   │
│   │   └── 支持介质恢复和增量备份                          │   │
│   │                                                         │   │
│   │   前提：数据库必须处于ARCHIVELOG模式                   │   │
│   │                                                         │   │
│   └─────────────────────────────────────────────────────────┘   │
│                                                                 │
└─────────────────────────────────────────────────────────────────┘

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56

┌─────────────────────────────────────────────────────────────────┐
│                    Oracle事务ACID特性                             │
├─────────────────────────────────────────────────────────────────┤
│                                                                 │
│   ┌─────────────────────────────────────────────────────────┐   │
│   │                                                         │   │
│   │                    事务 (Transaction)                     │   │
│   │                                                         │   │
│   │   ┌─────────────────────────────────────────────────┐   │   │
│   │   │              A - Atomicity (原子性)              │   │   │
│   │   ├─────────────────────────────────────────────────┤   │   │
│   │   │                                                 │   │   │
│   │   │   事务中的操作要么全部成功，要么全部失败         │   │   │
│   │   │                                                 │   │   │
│   │   │   BEGIN;                                        │   │   │
│   │   │     UPDATE account SET balance = balance - 100; │   │   │
│   │   │     UPDATE account SET balance = balance + 100; │   │   │
│   │   │   COMMIT; -- 或 ROLLBACK;                       │   │   │
│   │   │                                                 │   │   │
│   │   └─────────────────────────────────────────────────┘   │   │
│   │                                                         │   │
│   │   ┌─────────────────────────────────────────────────┐   │   │
│   │   │              C - Consistency (一致性)            │   │   │
│   │   ├─────────────────────────────────────────────────┤   │   │
│   │   │                                                 │   │   │
│   │   │   事务执行前后，数据库状态保持一致              │   │   │
│   │   │   约束、触发器、级联操作保证数据完整性          │   │   │
│   │   │                                                 │   │   │
│   │   └─────────────────────────────────────────────────┘   │   │
│   │                                                         │   │
│   │   ┌─────────────────────────────────────────────────┐   │   │
│   │   │              I - Isolation (隔离性)              │   │   │
│   │   ├─────────────────────────────────────────────────┤   │   │
│   │   │                                                 │   │   │
│   │   │   并发事务之间相互隔离，互不干扰                │   │   │
│   │   │                                                 │   │   │
│   │   │   隔离级别：                                    │   │   │
│   │   │   ├── READ UNCOMMITTED                         │   │   │
│   │   │   ├── READ COMMITTED (默认)                    │   │   │
│   │   │   ├── REPEATABLE READ                         │   │   │
│   │   │   └── SERIALIZABLE                            │   │   │
│   │   │                                                 │   │   │
│   │   └─────────────────────────────────────────────────┘   │   │
│   │                                                         │   │
│   │   ┌─────────────────────────────────────────────────┐   │   │
│   │   │              D - Durability (持久性)            │   │   │
│   │   ├─────────────────────────────────────────────────┤   │   │
│   │   │                                                 │   │   │
│   │   │   事务提交后，对数据库的修改永久保存            │   │   │
│   │   │   即使系统故障也不会丢失                        │   │   │
│   │   │                                                 │   │   │
│   │   └─────────────────────────────────────────────────┘   │   │
│   │                                                         │   │
│   └─────────────────────────────────────────────────────────┘   │
│                                                                 │
└─────────────────────────────────────────────────────────────────┘

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34

┌─────────────────────────────────────────────────────────────────┐
│                    Oracle隔离级别详解                             │
├─────────────────────────────────────────────────────────────────┤
│                                                                 │
│   ┌─────────────────────────────────────────────────────────┐   │
│   │                                                         │   │
│   │   隔离级别          │ 脏读 │ 不可重复读 │ 幻读         │   │
│   │   ─────────────────────────────────────────────        │   │
│   │   READ UNCOMMITTED │  可能  │    可能    │    可能       │   │
│   │   READ COMMITTED    │  不可能│    可能    │    可能       │   │
│   │   REPEATABLE READ   │  不可能│    不可能  │    可能       │   │
│   │   SERIALIZABLE      │  不可能│    不可能  │    不可能     │   │
│   │                                                         │   │
│   └─────────────────────────────────────────────────────────┘   │
│                                                                 │
│   脏读 (Dirty Read)：                                          │
│   读取未提交事务的数据                                          │
│                                                                 │
│   不可重复读 (Non-repeatable Read)：                           │
│   同一事务中，两次读取同一行数据，结果不同                      │
│                                                                 │
│   幻读 (Phantom Read)：                                        │
│   同一事务中，两次执行相同查询，结果集不同                      │
│                                                                 │
│   Oracle默认隔离级别：READ COMMITTED                            │
│                                                                 │
│   设置隔离级别：                                               │
│   SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;               │
│   SET TRANSACTION ISOLATION LEVEL READ COMMITTED;             │
│                                                                 │
│   ⚠️ 注意：Oracle不真正支持REPEATABLE READ                     │
│   Oracle使用多版本并发控制(MVCC)实现类似效果                     │
│                                                                 │
└─────────────────────────────────────────────────────────────────┘

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53

┌─────────────────────────────────────────────────────────────────┐
│                    Oracle锁机制                                   │
├─────────────────────────────────────────────────────────────────┤
│                                                                 │
│   ┌─────────────────────────────────────────────────────────┐   │
│   │                                                         │   │
│   │   锁类型：                                              │   │
│   │                                                         │   │
│   │   ┌─────────────────────────────────────────────────┐   │   │
│   │   │            DML锁 (数据锁)                        │   │   │
│   │   ├─────────────────────────────────────────────────┤   │   │
│   │   │                                                 │   │   │
│   │   │   ├── 行级锁 (TX Lock)                          │   │   │
│   │   │   │   • 锁定某一行                             │   │   │
│   │   │   │   • 允许并发修改不同行                     │   │   │
│   │   │   │   • 数量多，开销大                        │   │   │
│   │   │   │                                             │   │   │
│   │   │   └── 表级锁 (TM Lock)                         │   │   │
│   │   │       • 锁定整个表                             │   │   │
│   │   │       • RS/RX/SRX/SX                          │   │   │
│   │   │                                                 │   │   │
│   │   └─────────────────────────────────────────────────┘   │   │
│   │                                                         │   │
│   │   ┌─────────────────────────────────────────────────┐   │   │
│   │   │            DDL锁 (字典锁)                        │   │   │
│   │   ├─────────────────────────────────────────────────┤   │   │
│   │   │                                                 │   │   │
│   │   │   ├── 排他DDL锁 (防止其他DDL)                  │   │   │
│   │   │   ├── 共享DDL锁 (保护对象结构)                  │   │   │
│   │   │   └── 可中断DDL锁                              │   │   │
│   │   │                                                 │   │   │
│   │   └─────────────────────────────────────────────────┘   │   │
│   │                                                         │   │
│   │   ┌─────────────────────────────────────────────────┐   │   │
│   │   │            系统锁 (Latches/Mutexes)             │   │   │
│   │   ├─────────────────────────────────────────────────┤   │   │
│   │   │                                                 │   │   │
│   │   │   ├── Latch: 轻量级锁，保护内存结构             │   │   │
│   │   │   └── Mutex: 互斥锁，保护对象                  │   │   │
│   │   │                                                 │   │   │
│   │   └─────────────────────────────────────────────────┘   │   │
│   │                                                         │   │
│   └─────────────────────────────────────────────────────────┘   │
│                                                                 │
│   查看锁信息：                                                 │
│   SELECT l.session_id, l.locked_mode, o.object_name            │
│   FROM v$locked_object l, dba_objects o                       │
│   WHERE l.object_id = o.object_id;                            │
│                                                                 │
│   杀掉锁会话：                                                 │
│   ALTER SYSTEM KILL SESSION 'sid, serial#';                  │
│                                                                 │
└─────────────────────────────────────────────────────────────────┘

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70

┌─────────────────────────────────────────────────────────────────┐
│                    Oracle SQL执行完整流程                          │
├─────────────────────────────────────────────────────────────────┤
│                                                                 │
│   ┌─────────────────────────────────────────────────────────┐   │
│   │                                                         │   │
│   │                    SQL语句                               │   │
│   │                       │                                │   │
│   │                       ▼                                │   │
│   │              ┌─────────────────┐                       │   │
│   │              │  1. 语法解析     │                       │   │
│   │              │ (Syntax Parse)  │                       │   │
│   │              │                 │                       │   │
│   │              │ • 检查语法错误   │                       │   │
│   │              │ • 分解SQL结构   │                       │   │
│   │              └────────┬────────┘                       │   │
│   │                       │                                │   │
│   │                       ▼                                │   │
│   │              ┌─────────────────┐                       │   │
│   │              │  2. 语义解析     │                       │   │
│   │              │(Semantic Parse) │                       │   │
│   │              │                 │                       │   │
│   │              │ • 检查对象存在   │                       │   │
│   │              │ • 检查权限     │                       │   │
│   │              │ • 验证数据类型 │                       │   │
│   │              └────────┬────────┘                       │   │
│   │                       │                                │   │
│   │                       ▼                                │   │
│   │              ┌─────────────────┐                       │   │
│   │              │  3. 共享池检查   │                       │   │
│   │              │ (Shared Pool)   │                       │   │
│   │              │                 │                       │   │
│   │              │ • 检查执行计划  │                       │   │
│   │              │ • 软解析/硬解析 │                       │   │
│   │              └────────┬────────┘                       │   │
│   │                       │                                │   │
│   │            ┌──────────┴──────────┐                    │   │
│   │            │                     │                      │   │
│   │            ▼                     ▼                      │   │
│   │   ┌──────────────┐      ┌──────────────┐             │   │
│   │   │   软解析      │      │    硬解析     │             │   │
│   │   │ (Soft Parse) │      │ (Hard Parse) │             │   │
│   │   │              │      │              │             │   │
│   │   │ 找到执行计划 │      │ 生成执行计划  │             │   │
│   │   │ 直接执行     │      │ 优化器优化   │             │   │
│   │   └──────────────┘      │ 执行计划缓存 │             │   │
│   │            │             └───────┬──────┘             │   │
│   │            │                     │                    │   │
│   │            └──────────┬──────────┘                    │   │
│   │                       ▼                                │   │
│   │              ┌─────────────────┐                       │   │
│   │              │  4. 优化器      │                       │   │
│   │              │  (Optimizer)   │                       │   │
│   │              │                 │                       │   │
│   │              │ • RBO/CBO优化  │                       │   │
│   │              │ • 选择最佳计划 │                       │   │
│   │              └────────┬────────┘                       │   │
│   │                       │                                │   │
│   │                       ▼                                │   │
│   │              ┌─────────────────┐                       │   │
│   │              │  5. 执行计划    │                       │   │
│   │              │ (Row Source)   │                       │   │
│   │              │                 │                       │   │
│   │              │ • 生成执行树   │                       │   │
│   │              │ • 返回结果集  │                       │   │
│   │              └─────────────────┘                       │   │
│   │                                                         │   │
│   └─────────────────────────────────────────────────────────┘   │
│                                                                 │
└─────────────────────────────────────────────────────────────────┘

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49

┌─────────────────────────────────────────────────────────────────┐
│                    Oracle优化器                                   │
├─────────────────────────────────────────────────────────────────┤
│                                                                 │
│   ┌─────────────────────────────────────────────────────────┐   │
│   │          RBO - Rule-Based Optimizer (基于规则的优化器)    │   │
│   ├─────────────────────────────────────────────────────────┤   │
│   │                                                         │   │
│   │   特点：                                                │   │
│   │   ├── 使用固定规则优先级                                │   │
│   │   ├── 规则等级 1-15 (1最高)                           │   │
│   │   ├── 不考虑数据分布                                   │   │
│   │   └── Oracle 10g后废弃                                 │   │
│   │                                                         │   │
│   │   规则等级示例：                                       │   │
│   │   1. ROWID = 常量                                      │   │
│   │   2. CLUSTER SCAN JOIN                                 │   │
│   │   3. HASH JOIN CLUSTER                                 │   │
│   │   ...                                                  │   │
│   │   15. FULL TABLE SCAN                                  │   │
│   │                                                         │   │
│   └─────────────────────────────────────────────────────────┘   │
│                                                                 │
│   ┌─────────────────────────────────────────────────────────┐   │
│   │        CBO - Cost-Based Optimizer (基于成本的优化器)       │   │
│   ├─────────────────────────────────────────────────────────┤   │
│   │                                                         │   │
│   │   特点：                                                │   │
│   │   ├── 基于统计信息计算成本                             │   │
│   │   ├── 考虑IO/CPU/网络成本                              │   │
│   │   ├── 考虑数据分布                                     │   │
│   │   └── Oracle推荐使用                                   │   │
│   │                                                         │   │
│   │   统计信息：                                            │   │
│   │   ├── 表统计: 行数、块数、平均行长度                  │   │
│   │   ├── 列统计: 唯一值数、空值数、直方图                │   │
│   │   └── 索引统计: B树深度、叶节点数                   │   │
│   │                                                         │   │
│   │   成本模型：                                           │   │
│   │   Cost = I/O Cost + CPU Cost                          │   │
│   │                                                         │   │
│   └─────────────────────────────────────────────────────────┘   │
│                                                                 │
│   设置优化器模式：                                             │
│   ALTER SESSION SET optimizer_mode = 'CHOOSE';                │
│   ALTER SESSION SET optimizer_mode = 'ALL_ROWS';             │
│   ALTER SESSION SET optimizer_mode = 'FIRST_ROWS';           │
│                                                                 │
└─────────────────────────────────────────────────────────────────┘

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

-- 查看执行计划
EXPLAIN PLAN FOR
SELECT e.employee_id, e.last_name, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id
WHERE e.salary > 5000;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

-- 使用AUTOTRACE查看执行计划
SET AUTOTRACE ON;
SET AUTOTRACE TRACEONLY EXPLAIN;

-- 使用SQL Monitor
SELECT * FROM V$SQL_MONITOR WHERE SQL_TEXT LIKE '%SELECT%';

-- 查看SQL执行统计
SET AUTOTRACE ON STATISTICS;

-- 常见执行计划操作
-- Table Access Full: 全表扫描
-- Index Range Scan: 索引范围扫描
-- Index Unique Scan: 索引唯一扫描
-- Nested Loop Join: 嵌套循环连接
-- Hash Join: 哈希连接
-- Sort Merge Join: 排序合并连接

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70

┌─────────────────────────────────────────────────────────────────┐
│                    Oracle表类型详解                               │
├─────────────────────────────────────────────────────────────────┤
│                                                                 │
│   ┌─────────────────────────────────────────────────────────┐   │
│   │               普通堆表 (Heap Table)                      │   │
│   ├─────────────────────────────────────────────────────────┤   │
│   │                                                         │   │
│   │   特点：                                                │   │
│   │   ├── 数据无序存储                                      │   │
│   │   ├── 按INSERT顺序存放                                │   │
│   │   └── 默认表类型                                       │   │
│   │                                                         │   │
│   └─────────────────────────────────────────────────────────┘   │
│                                                                 │
│   ┌─────────────────────────────────────────────────────────┐   │
│   │               分区表 (Partitioned Table)                │   │
│   ├─────────────────────────────────────────────────────────┤   │
│   │                                                         │   │
│   │   优点：                                                │   │
│   │   ├── 提高查询性能 (分区裁剪)                           │   │
│   │   ├── 便于数据管理 (分区维护)                          │   │
│   │   ├── 提高可用性 (单分区故障不影响)                    │   │
│   │   └── 便于备份恢复 (单分区操作)                        │   │
│   │                                                         │   │
│   │   分区类型：                                            │   │
│   │   ├── 范围分区 (RANGE)                                 │   │
│   │   ├── 列表分区 (LIST)                                 │   │
│   │   ├── 哈希分区 (HASH)                                 │   │
│   │   ├── 组合分区 (RANGE-LIST等)                         │   │
│   │   └── 间隔分区 (INTERVAL)                             │   │
│   │                                                         │   │
│   └─────────────────────────────────────────────────────────┘   │
│                                                                 │
│   ┌─────────────────────────────────────────────────────────┐   │
│   │               索引组织表 (IOT - Index Organized Table)  │   │
│   ├─────────────────────────────────────────────────────────┤   │
│   │                                                         │   │
│   │   特点：                                                │   │
│   │   ├── 主键索引存储表数据                               │   │
│   │   ├── 按主键排序                                       │   │
│   │   ├── 无需额外主键索引                                 │   │
│   │   └── 适合主键查询频繁的场景                          │   │
│   │                                                         │   │
│   └─────────────────────────────────────────────────────────┘   │
│                                                                 │
│   ┌─────────────────────────────────────────────────────────┐   │
│   │               集群表 (Cluster Table)                     │   │
│   ├─────────────────────────────────────────────────────────┤   │
│   │                                                         │   │
│   │   特点：                                                │   │
│   │   ├── 多表共享存储                                     │   │
│   │   ├── 按集群键物理存储在一起                           │   │
│   │   └── 适合经常一起查询的表                             │   │
│   │                                                         │   │
│   └─────────────────────────────────────────────────────────┘   │
│                                                                 │
│   ┌─────────────────────────────────────────────────────────┐   │
│   │               临时表 (Temporary Table)                    │   │
│   ├─────────────────────────────────────────────────────────┤   │
│   │                                                         │   │
│   │   特点：                                                │   │
│   │   ├── 会话级：会话结束自动清空                         │   │
│   │   ├── 事务级：事务结束自动清空                        │   │
│   │   ├── 数据不跨会话共享                                 │   │
│   │   └── 适合复杂报表/排序                                │   │
│   │                                                         │   │
│   └─────────────────────────────────────────────────────────┘   │
│                                                                 │
└─────────────────────────────────────────────────────────────────┘

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37

-- 创建范围分区表
CREATE TABLE sales (
    sale_id     NUMBER,
    sale_date   DATE,
    amount      NUMBER,
    region      VARCHAR2(20)
)
PARTITION BY RANGE (sale_date) (
    PARTITION p2023_q1 VALUES LESS THAN (TO_DATE('2023-04-01', 'YYYY-MM-DD')),
    PARTITION p2023_q2 VALUES LESS THAN (TO_DATE('2023-07-01', 'YYYY-MM-DD')),
    PARTITION p2023_q3 VALUES LESS THAN (TO_DATE('2023-10-01', 'YYYY-MM-DD')),
    PARTITION p2023_q4 VALUES LESS THAN (TO_DATE('2024-01-01', 'YYYY-MM-DD')),
    PARTITION p_max VALUES LESS THAN (MAXVALUE)
);

-- 创建列表分区表
CREATE TABLE customers (
    customer_id   NUMBER,
    name          VARCHAR2(100),
    country       VARCHAR2(50),
    credit_limit  NUMBER
)
PARTITION BY LIST (country) (
    PARTITION p_china VALUES ('CHINA', 'TAIWAN', 'HONGKONG'),
    PARTITION p_usa VALUES ('USA', 'CANADA'),
    PARTITION p_europe VALUES ('UK', 'GERMANY', 'FRANCE'),
    PARTITION p_others VALUES (DEFAULT)
);

-- 查询分区数据
SELECT * FROM sales PARTITION (p2023_q1);
SELECT * FROM sales WHERE sale_date BETWEEN '2023-01-01' AND '2023-03-31';

-- 分区维护操作
ALTER TABLE sales TRUNCATE PARTITION p2023_q1;
ALTER TABLE sales DROP PARTITION p2022_q1;
ALTER TABLE sales ADD PARTITION p2024_q1 VALUES LESS THAN (TO_DATE('2024-04-01'));

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63

┌─────────────────────────────────────────────────────────────────┐
│                    Oracle索引类型                                 │
├─────────────────────────────────────────────────────────────────┤
│                                                                 │
│   ┌─────────────────────────────────────────────────────────┐   │
│   │               B-tree索引 (默认)                          │   │
│   ├─────────────────────────────────────────────────────────┤   │
│   │                                                         │   │
│   │   结构：                                                │   │
│   │   ┌─────────────────────────────────────────────────┐   │   │
│   │   │                     Root                         │   │   │
│   │   │                      │                          │   │   │
│   │   │           ┌─────────┴─────────┐                │   │   │
│   │   │           ▼                   ▼                │   │   │
│   │   │        Branch              Branch              │   │   │
│   │   │          │                   │                 │   │   │
│   │   │     ┌─────┴─────┐       ┌─────┴─────┐        │   │   │
│   │   │     ▼           ▼       ▼           ▼        │   │   │
│   │   │   Leaf         Leaf    Leaf       Leaf      │   │   │
│   │   │   (数据块)     (数据块) (数据块)   (数据块)   │   │   │
│   │   └─────────────────────────────────────────────────┘   │   │
│   │                                                         │   │
│   │   特点：                                                │   │
│   │   ├── 适用于高选择性列                                 │   │
│   │   ├── 支持范围查询和等值查询                           │   │
│   │   └── 根节点常驻内存                                   │   │
│   │                                                         │   │
│   └─────────────────────────────────────────────────────────┘   │
│                                                                 │
│   ┌─────────────────────────────────────────────────────────┐   │
│   │               位图索引 (Bitmap Index)                    │   │
│   ├─────────────────────────────────────────────────────────┤   │
│   │                                                         │   │
│   │   结构：                                                │   │
│   │   ┌─────────────────────────────────────────────────┐   │   │
│   │   │                                                 │   │   │
│   │   │   性别   │ Row1 │ Row2 │ Row3 │ Row4 │ Row5   │  │   │
│   │   │   ─────────────────────────────────────────    │  │   │
│   │   │   男     │  1   │  0   │  1   │  0   │  1    │  │   │
│   │   │   女     │  0   │  1   │  0   │  1   │  0    │  │   │
│   │   │                                                 │  │   │
│   │   └─────────────────────────────────────────────────┘   │   │
│   │                                                         │   │
│   │   特点：                                                │   │
│   │   ├── 适用于低选择性列 (如性别、状态)                  │   │
│   │   ├── 位运算快速 (AND/OR/NOT)                          │   │
│   │   └── 不适合高并发DML环境                             │   │
│   │                                                         │   │
│   └─────────────────────────────────────────────────────────┘   │
│                                                                 │
│   ┌─────────────────────────────────────────────────────────┐   │
│   │               其他索引类型                               │   │
│   ├─────────────────────────────────────────────────────────┤   │
│   │                                                         │   │
│   │   ├── 唯一索引 (UNIQUE): 值唯一                        │   │
│   │   ├── 函数索引 (FUNCTION-BASED): 基于表达式            │   │
│   │   ├── 复合索引 (COMPOSITE): 多列组合                  │   │
│   │   ├── 反向键索引 (REVERSE KEY): 适合序列插入          │   │
│   │   └── 全文索引 (CONTEXT): 文本搜索                    │   │
│   │                                                         │   │
│   └─────────────────────────────────────────────────────────┘   │
│                                                                 │
└─────────────────────────────────────────────────────────────────┘

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34

-- 创建B-tree索引
CREATE INDEX idx_emp_name ON employees(last_name, first_name);

-- 创建唯一索引
CREATE UNIQUE INDEX idx_emp_email ON employees(email);

-- 创建位图索引
CREATE BITMAP INDEX idx_emp_dept ON employees(department_id);

-- 创建函数索引
CREATE INDEX idx_emp_upper ON employees(UPPER(last_name));

-- 创建索引时指定表空间
CREATE INDEX idx_emp_name ON employees(last_name) TABLESPACE users;

-- 查看索引
SELECT index_name, table_name, uniqueness, status
FROM user_indexes;

-- 查看索引列
SELECT index_name, column_name, column_position
FROM user_ind_columns;

-- 重建索引
ALTER INDEX idx_emp_name REBUILD;

-- 合并索引
ALTER INDEX idx_emp_name COALESCE;

-- 删除索引
DROP INDEX idx_emp_name;

-- 分析索引
ANALYZE INDEX idx_emp_name VALIDATE STRUCTURE;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60

┌─────────────────────────────────────────────────────────────────┐
│                    Oracle备份类型                                  │
├─────────────────────────────────────────────────────────────────┤
│                                                                 │
│   ┌─────────────────────────────────────────────────────────┐   │
│   │                                                         │   │
│   │   按备份方式分：                                         │   │
│   │                                                         │   │
│   │   ┌─────────────────────────────────────────────────┐   │   │
│   │   │               物理备份                          │   │   │
│   │   ├─────────────────────────────────────────────────┤   │   │
│   │   │                                                 │   │   │
│   │   │   ├── 冷备份 (Cold Backup)                      │   │   │
│   │   │   │   • 数据库关闭状态备份                     │   │   │
│   │   │   │   • 一致性备份                             │   │   │
│   │   │   │   • 需要停机                               │   │   │
│   │   │   │                                             │   │   │
│   │   │   └── 热备份 (Hot Backup)                      │   │   │
│   │   │       • 数据库运行状态备份                     │   │   │
│   │   │       • 需要ARCHIVELOG模式                    │   │   │
│   │   │       • 不影响业务                             │   │   │
│   │   │                                                 │   │   │
│   │   └─────────────────────────────────────────────────┘   │   │
│   │                                                         │   │
│   │   ┌─────────────────────────────────────────────────┐   │   │
│   │   │               逻辑备份                          │   │   │
│   │   ├─────────────────────────────────────────────────┤   │   │
│   │   │                                                 │   │   │
│   │   │   ├── EXP/IMP (传统导出导入)                   │   │   │
│   │   │   └── EXPDP/IMPDP (数据泵)                     │   │   │
│   │   │                                                 │   │   │
│   │   │   特点：                                        │   │   │
│   │   │   • 备份表结构和数据                           │   │   │
│   │   │   • 跨平台恢复                                 │   │   │
│   │   │   • 可选择特定对象                             │   │   │
│   │   │                                                 │   │   │
│   │   └─────────────────────────────────────────────────┘   │   │
│   │                                                         │   │
│   └─────────────────────────────────────────────────────────┘   │
│                                                                 │
│   ┌─────────────────────────────────────────────────────────┐   │
│   │                                                         │   │
│   │   按备份内容分：                                         │   │
│   │                                                         │   │
│   │   ├── 全量备份 (Full Backup)                           │   │
│   │   │   • 备份所有数据文件                               │   │
│   │   │   • 备份控制文件                                   │   │
│   │   │   • 备份参数文件                                   │   │
│   │   │                                                     │   │
│   │   ├── 增量备份 (Incremental Backup)                    │   │
│   │   │   • 仅备份自上次备份以来变化的数据                 │   │
│   │   │   • 差异增量: 自上次全量/增量备份                 │   │
│   │   │   • 累积增量: 仅自上次全量备份                    │   │
│   │   │                                                     │   │
│   │   └── 部分备份 (Partial Backup)                       │   │
│   │       • 仅备份特定表空间或文件                        │   │
│   │                                                         │   │
│   └─────────────────────────────────────────────────────────┘   │
│                                                                 │
└─────────────────────────────────────────────────────────────────┘

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38

-- 查看数据库归档模式
ARCHIVE LOG LIST;

-- 切换到归档模式
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;

-- RMAN备份示例
-- 连接RMAN
rman target /

-- 备份全库
BACKUP DATABASE PLUS ARCHIVELOG;

-- 备份表空间
BACKUP TABLESPACE users;

-- 备份数据文件
BACKUP DATAFILE '/u01/oradata/db01/users01.dbf';

-- 备份控制文件
BACKUP CURRENT CONTROLFILE;

-- 备份归档日志
BACKUP ARCHIVELOG ALL DELETE INPUT;

-- 增量备份
BACKUP INCREMENTAL LEVEL 0 DATABASE;
BACKUP INCREMENTAL LEVEL 1 DATABASE;

-- 查看备份
LIST BACKUP;
LIST BACKUP SUMMARY;

-- 验证备份
RESTORE DATABASE VALIDATE;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57

┌─────────────────────────────────────────────────────────────────┐
│                    Oracle恢复场景                                 │
├─────────────────────────────────────────────────────────────────┤
│                                                                 │
│   ┌─────────────────────────────────────────────────────────┐   │
│   │           实例恢复 (Instance Recovery)                    │   │
│   ├─────────────────────────────────────────────────────────┤   │
│   │                                                         │   │
│   │   触发：实例异常关闭 (断电/崩溃)                        │   │
│   │                                                         │   │
│   │   恢复过程：                                            │   │
│   │   1. SMON自动执行                                      │   │
│   │   2. 前滚：应用在线重做日志                            │   │
│   │   3. 打开数据库                                        │   │
│   │   4. 回滚：撤销未提交事务                             │   │
│   │                                                         │   │
│   │   使用：联机重做日志，自动完成                         │   │
│   │                                                         │   │
│   └─────────────────────────────────────────────────────────┘   │
│                                                                 │
│   ┌─────────────────────────────────────────────────────────┐   │
│   │           介质恢复 (Media Recovery)                      │   │
│   ├─────────────────────────────────────────────────────────┤   │
│   │                                                         │   │
│   │   触发：数据文件损坏/丢失                              │   │
│   │                                                         │   │
│   │   恢复过程：                                            │   │
│   │   1. 还原数据文件 (RESTORE)                            │   │
│   │   2. 应用归档日志和重做日志 (RECOVER)                  │   │
│   │   3. 恢复一致性                                        │   │
│   │                                                         │   │
│   │   恢复命令：                                            │   │
│   │   RMAN> RESTORE DATAFILE '/u01/.../users01.dbf';      │   │
│   │   RMAN> RECOVER DATAFILE '/u01/.../users01.dbf';      │   │
│   │                                                         │   │
│   └─────────────────────────────────────────────────────────┘   │
│                                                                 │
│   ┌─────────────────────────────────────────────────────────┐   │
│   │           时间点恢复 (Point-in-Time Recovery)            │   │
│   ├─────────────────────────────────────────────────────────┤   │
│   │                                                         │   │
│   │   场景：误删除数据，需要恢复到某个时间点                │   │
│   │                                                         │   │
│   │   方法：                                                │   │
│   │   ├── 基于时间: UNTIL TIME                             │   │
│   │   ├── 基于SCN: UNTIL SCN                              │   │
│   │   ├── 基于序列: UNTIL SEQUENCE                        │   │
│   │   └── 基于取消: UNTIL CANCEL                          │   │
│   │                                                         │   │
│   │   示例：                                                │   │
│   │   RMAN> RESTORE DATABASE;                             │   │
│   │   RMAN> RECOVER DATABASE UNTIL TIME                   │   │
│   │     "TO_DATE('2024-01-15 10:00:00', 'YYYY-MM-DD HH24:MI:SS')";  │
│   │                                                         │   │
│   └─────────────────────────────────────────────────────────┘   │
│                                                                 │
└─────────────────────────────────────────────────────────────────┘

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34

┌─────────────────────────────────────────────────────────────────┐
│                    Oracle性能优化方法论                           │
├─────────────────────────────────────────────────────────────────┤
│                                                                 │
│   优化方法论：                                                 │
│   ┌─────────────────────────────────────────────────────────┐   │
│   │                                                         │   │
│   │   1. 识别问题 (Identify)                               │   │
│   │   ├── 收集性能数据                                      │   │
│   │   ├── 确定性能基线                                      │   │
│   │   └── 发现性能瓶颈                                      │   │
│   │                                                         │   │
│   │   2. 分析问题 (Analyze)                                │   │
│   │   ├── 分析等待事件                                      │   │
│   │   ├── 检查执行计划                                      │   │
│   │   └── 分析SQL语句                                      │   │
│   │                                                         │   │
│   │   3. 实施方案 (Implement)                             │   │
│   │   ├── 优化SQL语句                                      │   │
│   │   ├── 调整参数                                          │   │
│   │   ├── 添加索引                                          │   │
│   │   └── 修改架构                                          │   │
│   │                                                         │   │
│   │   4. 验证效果 (Verify)                                 │   │
│   │   ├── 验证性能改善                                      │   │
│   │   ├── 对比优化前后                                      │   │
│   │   └── 监控持续性能                                      │   │
│   │                                                         │   │
│   └─────────────────────────────────────────────────────────┘   │
│                                                                 │
│   优化优先级：                                                │
│   应用层优化 > SQL优化 > 内存优化 > 磁盘IO优化 > 架构优化      │
│                                                                 │
└─────────────────────────────────────────────────────────────────┘

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41

-- 查看当前等待事件 (最常见的性能问题来源)
SELECT event, wait_class, time_waited, waits
FROM v$session_event
WHERE sid = (SELECT sid FROM v$session WHERE username = 'SCOTT')
ORDER BY time_waited DESC;

-- 查看系统等待事件
SELECT event, total_waits, time_waited, average_wait
FROM v$system_event
ORDER BY time_waited DESC;

-- 查看当前SQL最耗资源的
SELECT sql_id, executions, cpu_time, elapsed_time, buffer_gets
FROM v$sql
ORDER BY cpu_time DESC
FETCH FIRST 10 ROWS ONLY;

-- 查看会话信息
SELECT sid, serial#, username, status, program, machine
FROM v$session
WHERE username IS NOT NULL;

-- 查看锁等待
SELECT l.session_id, l.locked_mode, l.oracle_username,
       o.object_name, o.object_type
FROM v$locked_object l, dba_objects o
WHERE l.object_id = o.object_id;

-- 查看SGA使用情况
SELECT component, current_size, min_size, max_size
FROM v$memory_dynamic_components
ORDER BY current_size;

-- 查看表统计信息
SELECT table_name, num_rows, blocks, avg_row_len
FROM user_tables
ORDER BY num_rows DESC;

-- 查看索引统计
SELECT index_name, table_name, blevel, leaf_blocks, distinct_keys
FROM user_indexes;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41

-- 避免SELECT *
SELECT employee_id, last_name, salary FROM employees;

-- 使用绑定变量
-- 避免：
SELECT * FROM employees WHERE department_id = 10;
SELECT * FROM employees WHERE department_id = 20;

-- 推荐：
SELECT * FROM employees WHERE department_id = :dept_id;

-- 避免在索引列上使用函数
-- 避免：
SELECT * FROM employees WHERE UPPER(last_name) = 'SMITH';

-- 推荐：使用函数索引
CREATE INDEX idx_emp_upper ON employees(UPPER(last_name));

-- 避免LIKE以通配符开头
-- 避免：
SELECT * FROM employees WHERE last_name LIKE '%MIT%';

-- 推荐：
SELECT * FROM employees WHERE last_name LIKE 'SMIT%';

-- 使用EXISTS替代IN
-- 对于子查询返回大结果：
SELECT * FROM departments d
WHERE EXISTS (SELECT 1 FROM employees e WHERE e.dept_id = d.dept_id);

-- 分页查询优化
SELECT * FROM (
    SELECT a.*, ROWNUM rn FROM (
        SELECT * FROM employees ORDER BY hire_date DESC
    ) a WHERE ROWNUM <= 20
) WHERE rn > 10;

-- 使用DECODE/CASE减少全表扫描
SELECT SUM(DECODE(department_id, 10, salary, 0)) AS dept10_sal,
       SUM(DECODE(department_id, 20, salary, 0)) AS dept20_sal
FROM employees;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50

-- 收集表统计信息
BEGIN
    DBMS_STATS.GATHER_TABLE_STATS(
        ownname => 'SCOTT',
        tabname => 'EMPLOYEES',
        estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
        method_opt => 'FOR ALL COLUMNS SIZE AUTO',
        degree => DBMS_STATS.AUTO_DEGREE,
        cascade => TRUE
    );
END;
/

-- 收集索引统计信息
BEGIN
    DBMS_STATS.GATHER_INDEX_STATS(
        ownname => 'SCOTT',
        indname => 'IDX_EMP_DEPT'
    );
END;
/

-- 收集系统统计信息
BEGIN
    DBMS_STATS.GATHER_SYSTEM_STATS(
        gathering_mode => 'NOWORKLOAD'
    );
END;
/

-- 查看统计信息
SELECT table_name, num_rows, last_analyzed
FROM user_tables;

-- 删除统计信息
BEGIN
    DBMS_STATS.DELETE_TABLE_STATS('SCOTT', 'EMPLOYEES');
END;
/

-- 导入导出统计信息
BEGIN
    DBMS_STATS.EXPORT_TABLE_STATS(
        ownname => 'SCOTT',
        tabname => 'EMPLOYEES',
        statown => 'SCOTT',
        stattab => 'STATS_TABLE'
    );
END;
/

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

┌─────────────────────────────────────────────────────────────────┐
│                    PL/SQL程序结构                                │
├─────────────────────────────────────────────────────────────────┤
│                                                                 │
│   ┌─────────────────────────────────────────────────────────┐   │
│   │                                                         │   │
│   │   PL/SQL块结构：                                        │   │
│   │                                                         │   │
│   │   DECLARE                                                │   │
│   │     -- 声明变量、常量、游标                              │   │
│   │     v_name VARCHAR2(100);                               │   │
│   │   BEGIN                                                  │   │
│   │     -- 程序逻辑                                          │   │
│   │     SELECT last_name INTO v_name                        │   │
│   │     FROM employees WHERE employee_id = 100;             │   │
│   │   EXCEPTION                                              │   │
│   │     -- 异常处理                                          │   │
│   │     WHEN NO_DATA_FOUND THEN                             │   │
│   │       DBMS_OUTPUT.PUT_LINE('Not found');                │   │
│   │   END;                                                  │   │
│   │                                                         │   │
│   └─────────────────────────────────────────────────────────┘   │
│                                                                 │
│   变量类型：                                                   │
│   ├── 标量类型: VARCHAR2, NUMBER, DATE, BOOLEAN              │
│   ├── 复合类型: RECORD, TABLE, VARRAY                        │
│   ├── 引用类型: REF CURSOR, REF object_type                  │
│   └── LOB类型: CLOB, BLOB, NCLOB, BFILE                      │
│                                                                 │
└─────────────────────────────────────────────────────────────────┘

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54

-- 创建存储过程
CREATE OR REPLACE PROCEDURE raise_salary (
    p_emp_id    IN employees.employee_id%TYPE,
    p_raise_pct IN NUMBER
) AS
    v_salary employees.salary%TYPE;
BEGIN
    -- 获取当前薪资
    SELECT salary INTO v_salary
    FROM employees
    WHERE employee_id = p_emp_id;
    
    -- 更新薪资
    UPDATE employees
    SET salary = salary * (1 + p_raise_pct / 100)
    WHERE employee_id = p_emp_id;
    
    COMMIT;
    
    DBMS_OUTPUT.PUT_LINE('Salary updated from ' || v_salary || 
                          ' to ' || v_salary * (1 + p_raise_pct / 100));
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('Employee not found');
    WHEN OTHERS THEN
        ROLLBACK;
        RAISE;
END raise_salary;
/

-- 调用存储过程
EXEC raise_salary(101, 10);
CALL raise_salary(101, 10);

-- 创建函数
CREATE OR REPLACE FUNCTION get_annual_sal (
    p_emp_id IN employees.employee_id%TYPE
) RETURN NUMBER AS
    v_annual_sal NUMBER;
BEGIN
    SELECT salary * 12 INTO v_annual_sal
    FROM employees
    WHERE employee_id = p_emp_id;
    
    RETURN v_annual_sal;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        RETURN NULL;
END get_annual_sal;
/

-- 调用函数
SELECT employee_id, last_name, get_annual_sal(employee_id) AS annual_sal
FROM employees;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54

-- 创建行级触发器
CREATE OR REPLACE TRIGGER trg_emp_salary
BEFORE UPDATE OF salary ON employees
FOR EACH ROW
WHEN (NEW.salary < OLD.salary)
DECLARE
    v_diff NUMBER;
BEGIN
    v_diff := :OLD.salary - :NEW.salary;
    
    -- 记录薪资降低日志
    INSERT INTO salary_audit (
        employee_id, old_salary, new_salary, change_date
    ) VALUES (
        :OLD.employee_id, :OLD.salary, :NEW.salary, SYSDATE
    );
    
    DBMS_OUTPUT.PUT_LINE('Warning: Salary reduced by ' || v_diff);
END trg_emp_salary;
/

-- 创建语句级触发器
CREATE OR REPLACE TRIGGER trg_emp_audit
AFTER INSERT OR UPDATE OR DELETE ON employees
DECLARE
    v_action VARCHAR2(10);
BEGIN
    IF INSERTING THEN v_action := 'INSERT';
    ELSIF UPDATING THEN v_action := 'UPDATE';
    ELSIF DELETING THEN v_action := 'DELETE';
    END IF;
    
    INSERT INTO audit_log (action_name, action_date, user_name)
    VALUES (v_action, SYSDATE, USER);
END trg_emp_audit;
/

-- 创建DDL触发器
CREATE OR REPLACE TRIGGER trg_ddl_audit
AFTER DDL ON SCHEMA
BEGIN
    INSERT INTO ddl_log (username, owner, object_type, object_name, 
                         operation, created)
    VALUES (USER, ORA_LOGIN_USER, ORA_DICT_OBJ_TYPE, 
            ORA_DICT_OBJ_NAME, ORA_SYSEVENT, SYSDATE);
END trg_ddl_audit;
/

-- 禁用/启用触发器
ALTER TRIGGER trg_emp_salary DISABLE;
ALTER TRIGGER trg_emp_salary ENABLE;

-- 删除触发器
DROP TRIGGER trg_emp_salary;