Database Reference
In-Depth Information
Next, I'll run some code that will quickly start filling up the shared pool (by pinning multiple PL/SQL procedures
in the shared pool):
SYS@ORA12CR1> conn eoda/foo
Connected.
EODA@ORA12CR1> declare
2 k varchar2(30);
3 ss varchar2(2000);
4 begin
5 for i in 1 .. 100000 loop
6 ss := 'create or replace procedure SP' || i || ' is
7 a number;
8 begin
9 a := 123456789012345678901234567890;
10 a := 123456789012345678901234567890;
11 a := 123456789012345678901234567890;
12 a := 123456789012345678901234567890;
13 a := 123456789012345678901234567890;
14 a := 123456789012345678901234567890;
15 a := 123456789012345678901234567890;
16 a := 123456789012345678901234567890;
17 a := 123456789012345678901234567890;
18 a := 123456789012345678901234567890;
19 a := 123456789012345678901234567890;
20 a := 123456789012345678901234567890;
21 a := 123456789012345678901234567890;
22 a := 123456789012345678901234567890;
23 a := 123456789012345678901234567890;
24 end;';
25 execute immediate ss;
26 k := 'SP' || i;
27 sys.dbms_shared_pool.keep(k);
28 end loop;
29 end;
30 /
Now, from another session, I'll query the data dictionary to view the memory resize operations as Oracle shifts
memory to the shared pool:
EODA@ORA12CR1> select component, parameter, oper_type, oper_mode from v$memory_resize_ops;
...
DEFAULT buffer cache db_cache_size SHRINK IMMEDIATE
shared pool shared_pool_size GROW IMMEDIATE
DEFAULT buffer cache db_cache_size SHRINK IMMEDIATE
DEFAULT buffer cache db_cache_size SHRINK IMMEDIATE
shared pool shared_pool_size GROW IMMEDIATE
DEFAULT buffer cache db_cache_size SHRINK IMMEDIATE
shared pool shared_pool_size GROW IMMEDIATE
