Oracle 10g第4章：深入理解SGA、PGA与UGA内存结构

需积分: 0 101 浏览量更新于2024-08-02 收藏 689KB PDF 举报

本章节是《Expert one-on-one Oracle 10g》第四章，作为Apress Beta Book Program的一部分提供。请注意，由于最终书籍出版前内容可能会有所变动，作者和出版社不对因章节内信息导致的任何损失或损害承担责任。版权所有，未经版权所有者和出版商事先书面许可，任何形式的复制、录音或通过任何信息存储和检索系统都不能进行。在Oracle 10g中，主要探讨了三种关键内存结构： 1. **System Global Area (SGA)**：这是一个大型的共享内存段，几乎所有Oracle进程在某个时刻都会对其进行访问。SGA包含了数据库运行时管理所需的核心组件，如数据缓存（Shared Pool）、重做日志缓冲区、控制文件缓存等。这些区域确保了多个进程间的协作和高效数据处理，是Oracle数据库架构的基础。 2. **Program Global Area (PGA)**：与SGA不同，PGA是私有的，专门为单个处理器线程保留，其他进程或线程无法访问。PGA内存用于存储每个会话特定的信息，例如临时表空间、参数值、游标和用户定义的变量等。它提供了进程间隔离，并且可以根据需要动态调整大小。 3. **User Global Area (UGA)**：这是与用户会话相关的内存，它可能存在于SGA或PGA中，具体取决于系统的配置和功能需求。UGA包含用户的程序代码、结果集缓存以及与当前操作紧密相关的临时数据。对于多用户环境，每个用户的UGA都是独立的，确保了数据的安全性和隔离性。理解并管理这些内存结构对优化数据库性能至关重要，因为它们直接影响到数据读写速度、内存利用率以及并发执行的效率。熟练掌握SGA、PGA和UGA的工作原理及其配置，有助于DBA进行有效的内存管理和性能调优。在实际操作中，监控和调整这些区域的大小可以避免性能瓶颈，提高数据库的整体响应能力。

side effect of the system recursive SQL that took place. Also, we can see that we did 2,859

writes and reads to and from temp – all of the physical IO reported by the autotrace report

was actually due to temporary space reads (and autotrace does not report physical write IO).

NOTE: System Recursive SQL is that SQL Oracle executes in order to process our queries, it could be for space

allocation, parsing, temporary space management, quota checking and so on.

You can see from the Autotrace statistics, that Oracle has reported that we’ve done 8

recursive calls of some sort during the processing of our query. By the time we finished our

query and exhausted the result set, we can see our UGA memory went back down to what it

started at (we released the sort areas from our UGA) and the PGA shrunk back somewhat

(note that in Oracle 8i and before, you would not expect to see the PGA shrink back at all,

this is new with Oracle 9i and later). Now, let’s retry that operation but play around with the

size of our SORT_AREA increasing it to 1 MB:

ops$tkyte@ORA10G> alter session set sort_area_size=1048576;

Session altered.

ops$tkyte@ORA10G> set autotrace traceonly statistics;

ops$tkyte@ORA10G> select * from t order by 1, 2, 3, 4;

48353 rows selected.

Statistics

----------------------------------------------------------

6 recursive calls

8 db block gets

671 consistent gets

676 physical reads

0 redo size

3461588 bytes sent via SQL*Net to client

35961 bytes received via SQL*Net from client

3225 SQL*Net roundtrips to/from client

0 sorts (memory)

1 sorts (disk)

48353 rows processed

ops$tkyte@ORA10G> set autotrace off

Now, in the other session we can measure our memory usage again:

ops$tkyte@ORA10G> @watch_stat

6 rows merged.

NAME VALUE DIFF

------------------------------------------- ---------- ----------

physical reads direct temporary tablespace 3535 676

physical writes direct temporary tablespace 3535 676

session pga memory 563788 0

session pga memory max 2464332 1769472

session uga memory 152176 0

session uga memory max 1265064 981960

6 rows selected.

As you can see, our PGA had grown considerably this time during the processing of our

query. It temporarily grew by about 1,728 KB, but the amount of physical IO we had to do in

order to sort this data dropped considerably as well (use more memory, swap to disk less

often). Now, going to an extreme here:

ops$tkyte@ORA10G> alter session set sort_area_size=1048576000;

Session altered.

ops$tkyte@ORA10G> set autotrace traceonly statistics;

ops$tkyte@ORA10G> select * from t order by 1, 2, 3, 4;

48353 rows selected.

Statistics

----------------------------------------------------------

0 recursive calls

0 db block gets

671 consistent gets

0 physical reads

0 redo size

3461588 bytes sent via SQL*Net to client

35961 bytes received via SQL*Net from client

3225 SQL*Net roundtrips to/from client

1 sorts (memory)

0 sorts (disk)

48353 rows processed

ops$tkyte@ORA10G> set autotrace off

And measuring from the other session, we can see the memory used so far:

ops$tkyte@ORA10G> @watch_stat

6 rows merged.

NAME VALUE DIFF

------------------------------------------- ---------- ----------

physical reads direct temporary tablespace 3535 0

physical writes direct temporary tablespace 3535 0

session pga memory 563788 0

session pga memory max 7510604 5046272

session uga memory 152176 0

session uga memory max 7091360 5826296

6 rows selected.

We can observe that even though we allocated up to 1 gigabyte of memory to the

SORT_AREA, we really only used about 10 megabytes . This shows that the

SORT_AREA_SIZE setting is an upper bound, not the default and only allocation size. Here

notice also that we did only 1 sort again, but this time it was entirely in memory, there was no

temporary space on disk used as evidenced by the lack of physical IO.

剩余36页未读，继续阅读

jade_nju

粉丝: 0
资源: 8

Oracle 10g第4章：深入理解SGA、PGA与UGA内存结构

expert one-on-one oracle 10g chap5

中国石油大学(北京)克拉玛依校区在广东2021-2024各专业最低录取分数及位次表.pdf

浙江越秀外国语学院在广东2021-2024各专业最低录取分数及位次表.pdf

网站模板源代码（组件划分规范，二改省力）

华中科技大学 Java课程设计实验.zip

4G语音质差参数优化调整建议.xlsx

仲恺农业工程学院在广东2021-2024各专业最低录取分数及位次表.pdf

《Java语言程序设计（基础篇）》课后编程练习题.zip

电影订票后台系统（系分课程设计）.zip

基于SVM方法的步态识别

最新资源