[004]常用数据字典+查询（不断更新）

最新推荐文章于 2022-07-24 23:00:00 发布

最新推荐文章于 2022-07-24 23:00:00 发布 · 167 阅读

文章标签：

#SQL #Cache

听NB老师讲Oracle042的笔记专栏收录该内容

7 篇文章

订阅专栏

本文详细介绍了如何使用SQL命令查询Oracle数据库的系统全局区(SGA)配置情况，包括SGA总大小及其各组成部分如固定SGA大小、重做日志缓冲区大小、缓冲区缓存大小、共享池大小等，并展示了当前运行的后台进程。

1. 显示SGA

SQL> show sga

Total System Global Area  285212672 bytes
Fixed Size                  1218992 bytes
Variable Size              92276304 bytes
Database Buffers          188743680 bytes
Redo Buffers                2973696 bytes

2. 现实SGA中各个内存的大小

SQL> select * from v$sgainfo;

NAME                                  BYTES RES
-------------------------------- ---------- ---
Fixed SGA Size                      1218992 No
Redo Buffers                        2973696 No
Buffer Cache Size                 188743680 Yes
Shared Pool Size                   83886080 Yes
Large Pool Size                     4194304 Yes
Java Pool Size                      4194304 Yes
Streams Pool Size                         0 Yes
Granule Size                        4194304 No
Maximum SGA Size                  285212672 No
Startup overhead in Shared Pool    37748736 No
Free SGA Memory Available                 0

3. 查看数据库正在使用的后台进程

SQL> select paddr, name, description
  2  from v$bgprocess
  3  where paddr <> '00';

PADDR    NAME  DESCRIPTION
-------- ----- ----------------------------------------------------------------
30E16830 PMON  process cleanup
30E16DE4 PSP0  process spawner 0
30E17398 MMAN  Memory Manager
30E1794C DBW0  db writer process 0
30E17F00 LGWR  Redo etc.
30E184B4 CKPT  checkpoint
30E18A68 SMON  System Monitor Process
30E1901C RECO  distributed recovery
30E195D0 CJQ0  Job Queue Coordinator
30E1B808 QMNC  AQ Coordinator
30E19B84 MMON  Manageability Monitor Process
30E1A138 MMNL  Manageability Monitor Process 2