Oracle数据库

应用角色和知识分类

数据库应用角色

一、开发。能利用 SQL语句完成数据库的查增删改的基本操作；能用 PL/SQL语句完成及各类逻辑的实现。

二、管理。能完成数据库的安装、部署、参数调试、备份恢复、数据迁移等与系统相关的工作；能完成分配用户、控制权限、表空间划分等与管理相关的工作；能进行故障定位、问题分析等与数据库诊断修复相关的工作。

三、优化。在深入了解数据库运行原理的基础上，利用各类工具及手段发现并解决数据库存在的性能问题，从而提升数据库运行效率。

四、设计。深刻理解业务需求和数据库原理，合理高效地完成数据库模型的建设，设计出各类表及索引等数据库对象，让后续应用开发可以高效稳定。

基础知识分类

20211023164644

各角色要求

20211023164702

各角色选择

从企业需求来说，数据库开发所需人数往往是最多的，学会了SQL和PL/SQL的技巧后，大部分人直接就可以从事产品相关的开发，而无论哪个IT企业，从事产品开发的人员数量总是最多的。不仅是数据库开发，从事Java开发、C语言开发的人员，只要他的应用和数据库有关系，就必然会应用到SQL和PL/SQL，差别仅仅是多了一些封装工具而已。由此可见，数据库开发的学习是受众最广的，但是开发就离不开算法，对从业人员的逻辑思维能力要求比较高，尤其是面对复杂需求的时候。相比数据库开发来说，数据库管理人员的人数需求在IT 市场要少得多。这是由工作性质决定的。无论生产还是测试环境，搭建数据库都不可能非常频繁。另外，如果数据库崩溃需要恢复、数据需要迁移、紧急故障需要处理的情况频繁出现，那这个企业基本上也无法正常运营下去了。但是一旦出现问题，管理人员无法及时恢复故障，将会受到来自各方面的指责，压力非常大。和开发人员相比，管理人员不需要每时每刻地忙碌着，但是却要时刻注意充电，提升自己的应急处理能力，还需要时刻对系统进行健康检查，以防不测。此外，虽然开发人员在逻辑思维方面的要求要高于管理人员，但是责任和压力却远没有管理人员这么大。数据库优化的角色，不少企业没有设置专门的数据库优化角色，它可能被融入资深开发、资深管理和资深设计人员的技能之中。对于有这样角色的企业来说，场景是这样的：生产环境运行缓慢，数据库管理人员通过跟踪诊断，查出问题所在，原来是SQL语句运行缓慢导致的整个数据库性能低下。那这个时候对于数据库管理人员来说，他的工作结束了，然后优化人员介入，利用自己的知识优化这些SQL语句。在没有专门角色的场景下，可能是这个管理人员有着丰富的技能，他优化了这些SQL语句，也可能是资深开发人员或者是资深设计人员优化了这些SQL语句。但是从工作职责划分、从更专业的角度来说，应该设置专职人员。数据库优化所需要的人员是最难估算的，或许很多，或许很少，甚至没有，但是却是最重要的技能之一。其实开发、管理、设计三个角色，对优化相关知识的学习应该多多益善，懂得越多，工作越得心应手。数据库设计人员需要掌握的知识点最多，从事数据库设计是最不容易的，这属于核心岗位的位置，少数人的规划和部署决定了产品最终的质量和生命力。从市场需求来说，从事设计的人员最少。一般来说，一个应届毕业生在相关开发、管理岗位努力工作两年后，就可以把开发及管理工作做得比较出色，优化工作做得得心应手应该至少要三年以上，但是要想从事设计相关工作，一般需要五年以上的工作经验。

Oracle介绍

数据库容量大小分类

小型数据库

Access、Foxbase

负载量小，用户一百人以内，成本一千元以内，安全性要求不高

中型数据库

SqlServer、MySQL

负载量，日访问5000-10000；成本万元内

大型数据库

sybase、db2、Oracle

海量负载，处理能力（sybase<Oracle<db2）安全性高

Oracle版本名称后缀意义

c cloud
g grade 分布式搭建
i internet

Oracle特性

自治数据库

自治数据库是什么 | Oracle 中国 | Oracle 中国

自动索引Auto Indexing
处理海量数据时的稳定性，性能好
物联网内存优化
补丁不宕机，备库闪回，多表祖，sqlci
语言支持多
大数据，数仓支持
dba的各种细节操作，索引删加，in memory内外表，列存取

CBO优化器（ Cost-Based Optimization 基于代价的优化器 ） CBO 是计算各种可能 “执行计划” 的“代价”，即 COST，从中选用 COST 最低的执行方案，作为实际运行方案。它依赖数据库对象的统计信息，统计信息的准确与否会影响 CBO 做出最优的选择。如果对一次执行 SQL 时发现涉及对象（表、索引等）没有被分析、统计过，那么 ORACLE 会采用一种叫做动态采样的技术，动态的收集表和索引上的一些数据信息。以前为RBO: Rule-Based Optimization 基于规则的优化器，已被淘汰。

Oracle结构体系

Oracle由磁盘上数据库（DB文件）和数据库管理系统（DBMS）构成，分别对应数据存储结构和软件结构。

Untitled

20211023191224

Oracle数据库的存储结构（Storage Structure）分为物理存储结构和逻辑存储结构两种，分别描述了在操作系统中和数据库系统内部数据的组织与管理方式。
Oracle数据库的软件结构，即Oracle实例（Instance），包括内存结构【共享内存区SGA（System Global Area）】与后台进程结构两个部分。
PGA（ProgramGlobal Area）区，这也是一块内存区，和SGA最明显的区别在于，PGA不是共享内存，是私有不共享的，S理解为共享的首字母。用户对数据库发起的无论查询还是更新的任何操作，都先在PGA进行预处理，然后才进入实例区域，由SGA和后台进程共同完成。
用户的请求发起经历的顺序一般如下：1区→2区→3区；或者1区→2区。
预处理作用主要有三点：第一，保存用户的连接信息，如会话属性、绑定变量等；第二，保存用户权限等重要信息，当用户进程与数据库建立会话时，系统会将这个用户的相关权限查询出来，然后保存在这个会话区内；第三，当发起的指令需要排序的时候，PGA（ProgramGlobal Area）正是这个排序区，如果在内存中可以放下排序的尺寸，就在内存PGA区内完成，如果放不下，超出的部分就在临时表空间中完成排序，也就是在磁盘中完成排序。
SQL执行第二次就会比第一次更快？因为第一次执行保存了共享池的解析动作，缓存了磁盘中的数据，第二次无须再解析，无须再去物理磁盘读取数据（如果开启执行计划Oracle性能调优以数值的形式显示两次执行sql语句的差异、首次执行产生的递归调用，逻辑读，物理读数据与第二次相比都有增加）其它提升了性能又如索引的体积一般比表小得多，如果能只访问索引完成需求一定比在表中进行更快，这也是少做事。再比如分区表，如果没有设置分区表，则需要全表扫描，如果设置了分区表，或许只需要在某个分区中查找即可，访问的路径少多了，这也是少做事
一条sql语句执行过程
1. 用户发起数据请求，PGA内预处理，连接只是完成了两点，即用户连接信息的保存和权限的保存，并且该指令匹配成唯一的hash值
2. 指令进入共享池，检查其中是否已经存在相同的hash值，不存在则服务器进程将在 CGA 中, 配合 UGA 内容对sql进行解析（首先查询自己的语句语法是否正确（比如from是否写成了form）、语义是否正确（比如id字段根本就不存在）、是否有权限，通过后hash值存储下来，再根据 ORACLE 选用的优化模式以及数据字典中是否存在相应对象的统计数据和是否使用了存储大纲来生成一个执行计划或从存储大纲中选用一个执行计划, 然后再用数据字典核对此用户对相应对象的执行权限, 最后生成一个编译代码。）ORACLE 将这条sql 语句的本身实际文本、HASH 值、编译代码、与此语名相关联的任何统计数据和该语句的执行计划缓存在共享池的 library cache 中，加入锁存器确保不被覆盖。
3. sql分析阶段从数据字典中核对表、视图结构时，需要将数据字典从磁盘读入数据缓冲区。（如果设计写或修改等事务磁盘读写加锁，并考虑回滚、恢复和一致性读等问题）
更新数据与回滚是怎样同时实现的，发起提交后，据缓存区中的数据一定会立即被DBWR进程写进磁盘吗？

存储结构

![Untitled 1](F:\xmTemporare\Export-1bf8f116-4377-48b5-ac13-0aaba877e881\img\Untitled 1.png)

物理存储结构

即一系列操作系统文件组成
- 数据文件是数据库中所有数据的实际存储空间，所有数据文件大小的和构成了数据库的大小。根据数据信息类型不同，数据文件分为永久性数据文件和临时数据文件两种。
- 控制文件是记录数据库结构信息的重要的二进制文件，由Oracle系统进行读写操作，DBA不能直接操作控制文件。数据库启动时，根据初始化参数文件中的CONTROL_FILES参数找到控制文件，然后根据控制文件中的信息，进行数据库数据文件和重做日志文件的加载，最后打开数据库。一个数据库至少需要一个控制文件，如果控制文件损坏将导致数据库无法启动。
- 重做日志文件是以重做记录的形式记录、保存用户对数据库所进行的变更操作，是数据库中最重要的物理文件。利用重做日志文件可以进行事务的重做（REDO）或回退（UNDO），是数据库实例恢复的基础。
- 归档重做日志文件是历史联机重做日志文件的集合，是联机重做日志文件被覆盖之前备份的副本。如果数据库工作在归档模式，数据库归档进程会自动将写满的重做日志文件在覆盖之前复制到指定的位置。归档重做日志文件是数据库出现介质故障后实现数据库完全恢复的必要条件。
- 初始化参数文件是数据库启动过程所必需的文件，记录了数据库显式参数的设置。数据库启动的第一步就是根据初始化参数文件中的设置，创建并启动实例，即分配内存空间、启动后台进程。
- 跟踪文件是数据库中重要的诊断文件跟踪文件中包含数据库系统运行过程中所发生的重大事件的有关信息，可以为数据库运行故障的解决提供重要信息。
- 告警文件也是数据库中重要的诊断文件，记录数据库在启动、关闭和运行期间后台进程的活动情况。在数据库出现故障时，应首先查看该文件，但文件中的信息与任何错误状态没有必然的联系。

逻辑存储结构

数据库创建后利用逻辑概念描述数据内部数据的组织和管理形式，存储在数据库的**数据字典（存放数据库对象所有信息，存在形式为字典表或者字典视图，有动态与静态之分）**中

数据字典 Oracle数据字典保存数据库本身的系统信息及所有数据库对象信息，包括：
- 各种数据库对象的定义信息，包括表、视图、索引、同义词、序列、存储过程、函数、包、触发器及其他各种对象；
- 数据库存储空间分配信息，如为某个数据库对象分配了多少空间，已经使用了多少空间等；
- 数据库安全信息，包括用户、权限、角色、完整性等；
- 数据库运行时的性能和统计信息；其他数据库本身的基本信息。
![Untitled 3](F:\xmTemporare\Export-1bf8f116-4377-48b5-ac13-0aaba877e881\img\Untitled 3.png)
详细介绍
- 数据块，数据库中最小的逻辑存储单元。在Oracle 11g数据库中，数据块分为标准块和非标准块两种。 Oracle数据库的默认数据缓冲区就是由标准数据块构成，由数据库初始化参数DB_BLOCK_SIZE设置。
- 区是由一系列连续的数据块构成的逻辑存储单元，是存储空间分配的最小单位。当创建一个数据库对象时，Oracle为对象分配若干个区，以构成一个段来为对象提供初始的存储空间。当段中已分配的区都写满后，Oracle会为段分配一个新区，以容纳更多的数据。
- 段是由一个或多个连续或不连续的区组成的逻辑存储单元。段分类表段索引段临时段回退段（回退段实现事务回滚，数据库恢复，一致性）
- 段是由一个或多个连续或不连续的区组成的逻辑存储单元。段分类表段索引段临时段回退段表空间是Oracle数据库最大的逻辑存储单元，数据库的大小从逻辑上看就是由表空间决定表空间与数据库文件直接关联，一个表空间包含一个或多个数据文件，一个数据文件只能从属于某一个表空间，数据库对象就是存储在表空间对应的一个或多个数据文件中。
- 表空间是Oracle数据库最大的逻辑存储单元，数据库的大小从逻辑上看就是由表空间决定表空间与数据库文件直接关联，一个表空间包含一个或多个数据文件，一个数据文件只能从属于某一个表空间，数据库对象就是存储在表空间对应的一个或多个数据文件中。

软件结构

由内存结构和后台进程组成。用户操作数据库实际是与数据库实例进行连接，通过实例操作数据库过程。

内存结构

SGA

SGA是由Oracle分配的共享内存结构，包含一个数据库实例的数据和控制信息。 SGA数据供所有的服务器进程和后台进程共享，所以SGA又称为共享全局区（Shared Global Area）。
- 数据缓存区存储从数据文件中检索出来的数据拷贝。应用程序要访问的数据必须从磁盘的数据文件读到数据缓冲区中处理。在数据缓冲区中被修改后的数据由数据写入进程写到硬盘的数据文件中永久保存。提高获取和更新数据的性能。
- 重做日志缓冲区用于缓存用户对数据库进行修改操作时生成的重做记录。为了提高工作效率，重做记录并不是直接写入重做日志文件中，而是首先被服务器进程写入重做日志缓冲区中，在一定条件下，再由日志写入进程（LGWR）把重做日志缓冲区的内容写入重做日志文件中做永久性保存。在归档模式下，当重做日志切换时，由归档进程（ARCH）将重做日志文件的内容写入归档文件中
- 共享池用于缓存最近执行过的SQL语句、PL/SQL程序和数据字典信息，是对SQL语句、PL/SQL程序进行语法分析、编译、执行的区域。库缓存库缓存用于缓存已经解释并执行过的SQL语句和PL/SQL程序代码，以提高SQL或PL/SQL程序的执行效率。包括SQL工作区和PL/SQL工作区数据字典缓存区数据字典缓存区保存最常用的数据字典信息
- 大型池是一个可选的内存配置项，主要为Oracle多线程服务器、服务器I/O进程、数据库备份与恢复操作、执行具有大量排序操作的SQL语句、执行并行化的数据库操作等需要大量缓存的操作提供内存空间。如果没有在SGA区中创建大型池，上述操作所需要的缓存空间将在共享池或PGA中分配，因而影响共享池或PGA的使用效率。（可选）
- Java池提供对Java程序设计的支持，用于存储Java代码、Java语句的语法分析表、Java语句的执行方案和进行Java程序开发。（可选）
- 流池是一个可选的内存配置项，用于对流的支持。流使用的机制是：从重做日志提取变更向量，并使用这些重新构造执行的语句，或具有相同效果的语句。这些语句在远程数据库执行。从重做日志提取更改的进程以及应用更改的进程将用到内存：此内存就是流池（可选）

PGA

PGA是一个私有的内存区，不能共享，每个服务器进程只能访问自己的PGA，因此PGA又称为私有全局区（Private Global Area）。系统同时为每个后台进程分配私有的PGA区。所有服务器进程PGA与所有后台进程PGA大小的和，即为实例的PGA的大小。 PGA随着服务器进程与后台进程的启动而分配，随着服务器进程和后台进程的终止而被释放。主要作用：第一，保存用户的连接信息，如会话属性、绑定变量等；第二，保存用户权限等重要信息，当用户进程与数据库建立会话时，系统会将这个用户的相关权限查询出来，然后保存在这个会话区内；第三，当发起的指令需要排序的时候，PGA（ProgramGlobal Area）正是这个排序区，如果在内存中可以放下排序的尺寸，就在内存PGA区内完成，如果放不下，超出的部分就在临时表空间中完成排序，也就是在磁盘中完成排序。

后台进程

DBWR 数据库写入进程
LGWR 日志写入进程
CKPT 检查点进程
SMON 系统监控进程
PMON 进程监控进程
ARCH 归档进程
恢复进程（RECO）
锁进程（LCKn）
度进程（Dnnn）等

应用调优

数据高速缓冲区越大，用户需要的数据在内存中的可能性就越大，即缓存命中率越高，从而减少了Oracle访问硬盘数据的次数，提高了数据库系统执行的效率。然而，如果数据高速缓冲区的值太大，Oracle就不得不在内存中寻找更多的块来定位所需要的数据，反而降低了系统性能。显然需要确定一个合理的数据高速缓冲区大小。

sql执行

sql-- 完全删除表，单独drop放入回收站
DROP TABLE t PURGE;
-- 共享池清空
ALTER SYSTEM flush SHARE_POOL;
-- 创建表
CREATE TABLE t (x NUMBER);

--此存储过程执行需要约等于 40 秒
CREATE OR REPLACE PROCEDURE proc1
AS 
BEGIN
FOR i IN 1..100000 LOOP
		execute IMMEDIATE 'INSERT INTO t VALUES ('||i||')';
COMMIT;
END LOOP;
END;
sql-- 完全删除表，单独drop放入回收站
DROP TABLE t PURGE;
-- 共享池清空
ALTER SYSTEM flush SHARE_POOL;
-- 创建表
CREATE TABLE t (x NUMBER);

--此存储过程执行需要约等于 8.14 秒，进行了变量绑定
CREATE OR REPLACE PROCEDURE proc2
BEGIN
FOR i IN 1..100000 LOOP
		execute IMMEDIATE 'INSERT INTO t VALUES (':x')' using i;
COMMIT;COMMIT;
END LOOP;
END;
sql-- 完全删除表，单独drop放入回收站
DROP TABLE t PURGE;
-- 共享池清空
ALTER SYSTEM flush SHARE_POOL;
-- 创建表
CREATE TABLE t (x NUMBER);

--此存储过程执行需要约等于 6 秒，没有动态执行，改为静态sql
CREATE OR REPLACE PROCEDURE proc3
AS 
BEGIN
FOR i IN 1..100000 LOOP
	INSERT INTO t VALUES (i); 
COMMENT;
END LOOP;
END;
sql-- 完全删除表，单独drop放入回收站
DROP TABLE t PURGE;
-- 共享池清空
ALTER SYSTEM flush SHARE_POOL;
-- 创建表
CREATE TABLE t (x NUMBER);

--此存储过程执行需要约等于 2秒，循环外COMMIT，减少触发LGWR将Redo Buffer写出到Redo Log中
CREATE OR REPLACE PROCEDURE proc4
AS 
BEGIN
FOR i IN 1..100000 LOOP
	INSERT INTO t VALUES (i); 
END LOOP;
COMMIT;
END;
sql
-- 完全删除表，单独drop放入回收站
DROP TABLE t PURGE;
-- 共享池清空
ALTER SYSTEM flush SHARE_POOL;
-- 创建表
CREATE TABLE t (x NUMBER);

--此插入约等于 0.25 秒，由一条一条依次插入，转为集合，整批写入数据缓存区中
INSERT INTO SELECT ROWNUM FROM dual CONNECT BY LEVEL <= 100000;

之后插入数据量上升至1000万

sql-- 完全删除表，单独drop放入回收站
DROP TABLE t PURGE;
-- 共享池清空
ALTER SYSTEM flush SHARE_POOL;
-- 创建表
CREATE TABLE t (x NUMBER);

--此插入约等于 23 秒
INSERT INTO SELECT ROWNUM FROM dual CONNECT BY LEVEL <= 10000000;
sql-- 完全删除表，单独drop放入回收站
DROP TABLE t PURGE;
-- 共享池清空
ALTER SYSTEM flush SHARE_POOL;
-- 创建表，并插入数据
CREATE TABLE t AS SELECT ROWNUM x FROM dual CONNECT BY LEVEL <= 10000000;
-- 此插入约等于 10 秒 create table t 的方式跳过了数据缓存区，直接将数据写进磁盘(直接路径读写方式)
sql-- 完全删除表，单独drop放入回收站
DROP TABLE t PURGE;
-- 共享池清空
ALTER SYSTEM flush SHARE_POOL;
-- 创建表，并插入数据
CREATE TABLE t nologging parallel 16
AS SELECT ROWNUM x FROM dual CONNECT BY LEVEL <= 10000000;
--此插入约等于 3 秒 最小化日志产生的数量,并设置并行执行

对关系型数据库的批评

主要是著名IT博主王垠对数据库的一些看法。

如何看待王垠对数据库的理解？

关系式模型的实质

SQL，NoSQL 以及数据库的实质

数据库学习的相关建议

数据库的广阔市场一直都在，但随着云计算的应用，企业正将新应用向云转移，对数据存储和计算分析的能力要求不断加强。而早在2012年，工信部就提出去“IOE”化。（IOE三者均为海外IT巨头，其中IBM代表硬件以及整体解决方案服务商，Oracle甲骨文代表数据库，EMC易安信代表数据存储。）同时又由于中美贸易战的缘由，以及十四五的信创计划（即一系列信息化创新技术，可理解为数字基建，是新基建最底层的一环，主要包括核心芯片、基础硬件、操作系统、中间件和数据服务器等领域）就更是加大了对国产数据库的扶持。并且在后端开发，服务的构建也都离不开数据库的支持，所以数据库的学习十分有必要。

入门

关系型数据库的学习可以由任一种DBMS入手，大部分sql语言都是通用的（只是不同DBMS支持的标准不同，以及一些语句的实现原理不同，同时不同DBMS有自家的sql拓展）

SQL语言的学习十分简单，主要是多做练习巩固SQL查找的思维方式，就推荐《SQL必知必会》一两天即可看完。

数据库原理的学习推荐两本书：

王珊, 萨师煊. 数据库系统概论(第5版). 北京: 高等教育出版社, 2014 （萨师煊是是我国数据库学科的奠基人之一，本书内容也讲的十分清晰全面）
A First Course in Database Systems, Jennifer Widom. 杨冬青等译. 数据库系统基础教程(原书第3版). 北京: 机械工业出版社, 2009（这本书是斯坦福大学CS145教材,讲得浅显易懂,书中对概念基本都举出了清晰的例子，每章的参考文献也很棒，列出了许多数据库领域重要的论文，是深入数据的基础）

进阶

经过数据库的入门学习，就应该大致了解数据库的相关应用，根据自身需求进一步选择对应的角色进行学习，这里根据数据内核设计提供一些建议（需要操作系统的基础，推荐CMU15213）

Hector Garcia-Mlina, Jeffrey D. Ullman, Jennifer Widom. 杨冬青等译. 数据库系统实现. 北京: 机械工业出版社, 2010（为斯坦福CS245及一部分CS346课程编写，主要讲解DBMS的实现）
Abraham Silberschatz, Henry F.Korth, S. Sudarshan. 杨冬青等译. 数据库系统概念(第6版). 北京: 机械工业出版社, 2012（帆船书，主要作为工具书使用，大而全）
李海翔. 数据库查询优化器的艺术原理解析与SQL性能优化. 北京: 机械工业出版社, 2014（对数据执行器的讲解，更便实践）

在学习的同时可以结合一些数据库的比赛，将自己学的知识落地，比如阿里天池和oceanbase、TIDB等官网都常年有数据库相关比赛（偏向于内核设计），同时也可参加国内知名数据库厂商培养课程如pincap的数据库的 Talent Plan 课程。

Ecrose's Blog

Oracle