shared pool 调整

最新推荐文章于 2021-04-16 06:18:18 发布

cojm55771

最新推荐文章于 2021-04-16 06:18:18 发布

阅读量150

点赞数

文章标签：人工智能 java 数据库

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