Oracle数据库PGA管理揭秘：私有与共享SQL区的关键区别

周同学的技术栈

已于 2024-08-13 20:58:31 修改

阅读量866

点赞数 31

分类专栏：数据库技术之路文章标签：数据库

于 2024-08-13 20:57:19 首次发布

本文链接：https://blog.csdn.net/zgt_certificate/article/details/141173646

版权

130 篇文章 3 订阅

订阅专栏

在这里插入图片描述

PGA是数据库服务器进程在内存中分配的一个区域，专用于存储该进程的私有数据和控制信息。PGA是私有的，不属于任何实例，而是属于服务器进程的私有区域。只有当前的服务器进程可以访问其对应的PGA。

PGA由两个主要部分组成：

私有SQL区
私有SQL区保存了SQL语句的绑定信息和运行时的内存结构。当用户执行一条SQL语句时，服务器进程为此语句分配一个私有SQL区。如果多个用户执行相同的SQL语句，这些私有SQL区可能会被映射为共享SQL区。私有SQL区的位置与会话的连接方式有关。
- 持久区：保存SQL语句的绑定信息，仅当游标关闭时才释放。
- 运行时区：在服务器进程接收到SQL执行请求时创建，并在执行结束时释放。
会话内存区
会话内存区用于存储会话变量和其他会话信息。对于共享服务器模式，这些信息是共享的，而不是私有的。
- SQL工作区：当执行复杂查询操作时，私有SQL区的运行时区可能被用于排序、位图合并等操作，这部分内存区域称为“SQL工作区”。

在Oracle 11g中，可以通过初始化参数自动管理SQL工作区的大小。例如，设置WORKAREA_SIZE_POLICY为AUTO，SQL工作区的管理将自动进行，无需通过参数如SORT_AREA_SIZE等手动指定内存区域。

为了更好地理解Oracle数据库中PGA的工作机制，特别是私有SQL区和会话内存区的运作方式，以下提供详细的举例说明。

有一个数据库用户执行了一条SQL查询语句：

SELECT * FROM employees WHERE department_id = 10;

这个查询语句将在以下几个方面涉及到私有SQL区：

SQL解析与执行
当用户提交这条SQL语句时，数据库服务器进程会首先在PGA中为这条语句分配一个私有SQL区。这个区域用于保存SQL语句的解析信息（如解析树和执行计划）和绑定信息（如变量绑定的数据类型、长度等）。如果是一个重复执行的查询，比如多个用户分别执行了相同的查询语句，Oracle可能会通过共享SQL区来优化资源利用。但在首次执行时，每个用户都会有自己的私有SQL区。
SQL执行计划生成
私有SQL区还会保存SQL语句的执行计划。例如，这个查询可能需要执行一个全表扫描操作，服务器进程会在私有SQL区中记录这些执行计划步骤，以及与每一步相关的资源信息（如需要访问的表和索引的数量）。在查询执行期间，这些信息在私有SQL区中动态更新。
绑定变量
如果查询中使用了绑定变量，比如：
```
SELECT * FROM employees WHERE department_id = :dept_id;
```
绑定变量:dept_id的值（如10）将在私有SQL区中存储。绑定的值可能会根据用户的输入发生变化，每个用户的私有SQL区都可能保存不同的绑定值。
内存释放
当查询执行完毕，服务器进程不再需要这些解析信息和绑定信息时，私有SQL区中的内存会被释放。这样可以确保内存资源的高效利用。如果用户关闭了游标，持久区也会被释放。

考虑一个复杂的SQL操作，比如一条排序查询语句：

SELECT * FROM employees ORDER BY salary DESC;

这条查询语句涉及排序操作，将触发SQL工作区的使用：

SQL工作区的分配
当用户执行这条查询语句时，数据库服务器进程会在PGA中分配一个SQL工作区用于排序操作。SQL工作区属于会话内存区的一部分，它根据操作的复杂性和数据量的大小动态分配内存。
排序操作
假设员工表（employees）中有上万条记录，数据库需要在SQL工作区中为排序操作分配足够的内存。排序操作将在SQL工作区中进行，包括对所有员工的工资进行排序并将结果存储在内存中。如果SQL工作区的大小不足以执行整个排序操作，数据库将使用临时表空间中的临时段来完成排序操作。
内存释放
排序操作完成后，SQL工作区将被释放，所占用的内存空间将返回给PGA。这样可以避免内存泄漏，并确保其他查询操作有足够的资源使用。
自动管理
假设数据库管理员已经设置了WORKAREA_SIZE_POLICY为AUTO，那么SQL工作区的大小将自动根据查询操作的需要进行动态调整，无需手动干预。数据库会智能地决定分配多少内存，以便优化性能。

在共享服务器模式下，多个用户会话共享同一个服务器进程：

会话变量的共享
多个用户通过同一个服务器进程连接到数据库时，数据库会将会话变量和其他相关信息存储在共享的会话内存区。例如，如果多个用户查询相同的表和数据集，这些查询结果的缓存可能会存储在共享的会话内存区中，以便提高查询效率。
临时表空间的使用
如果排序操作超出了SQL工作区的内存容量，临时表空间将被用来存储中间结果。在共享服务器模式下，多个会话可能同时使用临时表空间，这需要数据库在后台进行内存和磁盘I/O的管理，以确保各个会话能够正常运行。

非常感谢您读到这里！如果您觉得这篇文章对您有帮助，可以关注一下博主。关注后，您将第一时间获得最新的AI、云计算、运维（Linux、数据库，容器等）技术，以及更多实用的技能干货。