SQL> select shared_pool_size_for_estimate spsfe,
2 shared_pool_size_factor spsf,
3 estd_lc_size els,
4 estd_lc_memory_objects elmo,
5 estd_lc_time_saved elts,
6 estd_lc_time_saved_factor eltsf,
7 estd_lc_memory_object_hits elmoh
8* from v$shared_pool_advice
SQL> /
SPSFE SPSF ELS ELMO ELTS ELTSF ELMOH
---------- ---------- ---------- ---------- ---------- ---------- ----------
92 .7931 0 46 2228 .9973 909
104 .8966 12 2531 2228 .9973 49719
116 1 24 4064 2234 1 49910
128 1.1034 30 5385 2234 1 49928
140 1.2069 30 5385 2234 1 49928
152 1.3103 30 5385 2234 1 49928
164 1.4138 30 5385 2234 1 49928
176 1.5172 30 5385 2234 1 49928
188 1.6207 30 5385 2234 1 49928
200 1.7241 30 5385 2234 1 49928
212 1.8276 30 5385 2234 1 49928
SPSFE SPSF ELS ELMO ELTS ELTSF ELMOH
---------- ---------- ---------- ---------- ---------- ---------- ----------
224 1.931 30 5385 2234 1 49928
236 2.0345 30 5385 2234 1 49928
已选择13行。
SQL> show sga
Total System Global Area 535662592 bytes
Fixed Size 1334380 bytes
Variable Size 138412948 bytes
Database Buffers 390070272 bytes
Redo Buffers 5844992 bytes
SQL> show parameter shared_pool_size
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
shared_pool_size big integer 0
SQL> select id,name,block_size,size_for_estimate sfe,
2 size_factor sf,
3 estd_physical_read_factor eprf,
4 estd_physical_reads epr
5 from v$db_cache_advice;
ID NAME BLOCK_SIZE SFE SF EPRF
---------- -------------------- ---------- ---------- ---------- ----------
EPR
----------
3 DEFAULT 8192 36 .0968 1.1109
14878
3 DEFAULT 8192 72 .1935 1.0132
13569
3 DEFAULT 8192 108 .2903 1
13392
ID NAME BLOCK_SIZE SFE SF EPRF
---------- -------------------- ---------- ---------- ---------- ----------
EPR
----------
3 DEFAULT 8192 144 .3871 1
13392
3 DEFAULT 8192 180 .4839 1
13392
3 DEFAULT 8192 216 .5806 1
13392
ID NAME BLOCK_SIZE SFE SF EPRF
---------- -------------------- ---------- ---------- ---------- ----------
EPR
----------
3 DEFAULT 8192 252 .6774 1
13392
3 DEFAULT 8192 288 .7742 1
13392
3 DEFAULT 8192 324 .871 1
13392
ID NAME BLOCK_SIZE SFE SF EPRF
---------- -------------------- ---------- ---------- ---------- ----------
EPR
----------
3 DEFAULT 8192 360 .9677 1
13392
3 DEFAULT 8192 372 1 1
13392
3 DEFAULT 8192 396 1.0645 1
13392
ID NAME BLOCK_SIZE SFE SF EPRF
---------- -------------------- ---------- ---------- ---------- ----------
EPR
----------
3 DEFAULT 8192 432 1.1613 1
13392
3 DEFAULT 8192 468 1.2581 1
13392
3 DEFAULT 8192 504 1.3548 1
13392
ID NAME BLOCK_SIZE SFE SF EPRF
---------- -------------------- ---------- ---------- ---------- ----------
EPR
----------
3 DEFAULT 8192 540 1.4516 1
13392
3 DEFAULT 8192 576 1.5484 1
13392
3 DEFAULT 8192 612 1.6452 1
13392
ID NAME BLOCK_SIZE SFE SF EPRF
---------- -------------------- ---------- ---------- ---------- ----------
EPR
----------
3 DEFAULT 8192 648 1.7419 1
13392
3 DEFAULT 8192 684 1.8387 1
13392
3 DEFAULT 8192 720 1.9355 .745
9977
已选择21行。
SQL> show parameter db_cache_ad
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_cache_advice string ON
SQL> show parameter sga_max_size
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sga_max_size big integer 512M
SQL> show sga_max
Total System Global Area 535662592 bytes
Fixed Size 1334380 bytes
Variable Size 142607252 bytes
Database Buffers 385875968 bytes
Redo Buffers 5844992 bytes
SP2-0158: 未知的 SHOW 选项 "_max"
SQL> show parameter sga_max
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sga_max_size big integer 512M
SQL> show parameter memory_target
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
memory_target big integer 820M
SQL> show parameter memory_max_
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
memory_max_target big integer 820M
SQL> show parameter sga
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
lock_sga boolean FALSE
pre_page_sga boolean FALSE
sga_max_size big integer 512M
sga_target big integer 0
SQL> show sga
Total System Global Area 535662592 bytes
Fixed Size 1334380 bytes
Variable Size 146801556 bytes
Database Buffers 381681664 bytes
Redo Buffers 5844992 bytes
SQL> select 535662592/1024/1024 from dual;
535662592/1024/1024
-------------------
510.847656
SQL> show parameter shared_pool_size
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
shared_pool_size big integer 0
SQL> conn system/yang as sysdba
已连接。
SQL> select x.ksppinm name,
2 y.ksppstvl value,
3 x.ksppdesc describ
4 from sys.x$ksppi x,sys.x$ksppcv y
5 where x.inst_id = userenv('Instance') and y.inst_id =userenv('Instance')
6 and x.indx=y.indx
7* and x.ksppinm like '%pool_size%'
SQL> /
NAME
--------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
DESCRIB
--------------------------------------------------------------------------------
_NUMA_pool_size
Not specified
aggregate size in bytes of NUMA pool
__shared_pool_size
130023424
Actual size in bytes of shared pool
NAME
--------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
DESCRIB
--------------------------------------------------------------------------------
shared_pool_size
0
size in bytes of shared pool
__large_pool_size
4194304
NAME
--------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
DESCRIB
--------------------------------------------------------------------------------
Actual size in bytes of large pool
large_pool_size
0
size in bytes of large pool
__java_pool_size
NAME
--------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
DESCRIB
--------------------------------------------------------------------------------
12582912
Actual size in bytes of java pool
java_pool_size
0
size in bytes of java pool
NAME
--------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
DESCRIB
--------------------------------------------------------------------------------
__streams_pool_size
0
Actual size in bytes of streams pool
streams_pool_size
0
size in bytes of the streams pool
NAME
--------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
DESCRIB
--------------------------------------------------------------------------------
_io_shared_pool_size
4194304
Size of I/O buffer pool from SGA
_backup_io_pool_size
1048576
NAME
--------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
DESCRIB
--------------------------------------------------------------------------------
memory to reserve from the large pool
__shared_io_pool_size
0
Actual size of shared IO pool
_shared_io_pool_size
NAME
--------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
DESCRIB
--------------------------------------------------------------------------------
0
Size of shared IO pool
global_context_pool_size
Global Application Context Pool Size in Bytes
NAME
--------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
DESCRIB
--------------------------------------------------------------------------------
olap_page_pool_size
0
size of the olap page pool in bytes
_trace_pool_size
trace pool size in bytes
NAME
--------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
DESCRIB
--------------------------------------------------------------------------------
已选择16行。
SQL> alter system set shared_pool_size = 116m ;
系统已更改。
