SGA Components
For database 9i, there is no dynamic view gv$sgainfo like 10g or 11g that can represent current memory distribution of every component. Another dynamic view gv$sga_dynamic_components might help, but the information is incomplete.
If you have memory issues on database 9i, you could seek gv$sgastat for help, which can provides more detailed information AFTER some processes and combinations. Let's see the sql statement.
$ cat -n CheckSGA.sql
1 SET pagesize 20;
2 column "Now Used (MB)" format 9,999.99;
3 SELECT *
4 FROM
5 (SELECT inst_id,
6 pool component,
7 SUM(bytes)/1024/1024 "Now Used (MB)"
8 FROM gv$sgastat
9 WHERE pool IS NOT NULL
10 GROUP BY inst_id,
11 pool
12 UNION ALL
13 SELECT inst_id,
14 name component,
15 bytes/1024/1024 "Now Used (MB)"
16 FROM gv$sgastat
17 WHERE pool IS NULL
18 )
19 ORDER BY 1,2;Then we execute the script as the following:
SQL> @CheckSGA.sql
INST_ID COMPONENT Now Used (MB)
---------- -------------------------- -------------
1 buffer_cache 40.00
1 fixed_sga 2.12
1 java pool 4.00
1 large pool 4.00
1 log_buffer 2.29
1 shared pool 280.00
2 buffer_cache 40.00
2 fixed_sga 2.12
2 java pool 4.00
2 large pool 4.00
2 log_buffer 2.29
2 shared pool 264.01
12 rows selected.
The result shows the memory distribution of every SGA component by instances.