[20191211]11g streams_pool_size参数.txt
--//以前我看别人的文章,发现streams_pool_size设置为0,会导致expdp,impdp无法运行.
--//我自己测试过,发现没有这种现象,最近才明白问题在哪里.
1.环境:
SYS@book> @ ver1
PORT_STRING VERSION BANNER
-------------------- ----------- ----------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
SYS@book> @ hide streams_pool_size
NAME DESCRIPTION DEFAULT_VALUE SESSION_VALUE SYSTEM_VALUE ISSES ISSYS_MOD
------------------- ------------------------------------ ------------- ------------- ------------ ----- ---------
__streams_pool_size Actual size in bytes of streams pool TRUE 0 0 FALSE IMMEDIATE
streams_pool_size size in bytes of the streams pool FALSE 0 0 FALSE IMMEDIATE
SYS@book> show spparameter streams_pool_size
SID NAME TYPE VALUE
-------- ------------------ ------------ ------
* streams_pool_size big integer 0
2.测试:
--//测试前:
SYS@book> select * from v$sgainfo;
NAME BYTES RES
---------------------------------------- ---------- ---
Fixed SGA Size 2255872 No
Redo Buffers 7487488 No
Buffer Cache Size 427819008 Yes
Shared Pool Size 180355072 Yes
Large Pool Size 12582912 Yes
Java Pool Size 12582912 Yes
Streams Pool Size 0 Yes
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Shared IO Pool Size 0 Yes
Granule Size 4194304 No
Maximum SGA Size 643084288 No
Startup overhead in Shared Pool 133446832 No
Free SGA Memory Available 0
12 rows selected.
--//可以发现streams_pool_size的可以RESIZEABLE的.RESIZEABLE='YES'
--//expdp导出看看.
$ expdp scott/book
Export: Release 11.2.0.4.0 - Production on Wed Dec 11 10:56:25 2019
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SCOTT"."SYS_EXPORT_SCHEMA_01": scott/a*****
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 11.31 MB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/FUNCTION/FUNCTION
Processing object type SCHEMA_EXPORT/FUNCTION/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/INDEX/FUNCTIONAL_INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/FUNCTIONAL_INDEX/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "SCOTT"."TX" 8.219 MB 84768 rows
. . exported "SCOTT"."SESSION_WAIT_RECORD" 386.7 KB 8122 rows
. . exported "SCOTT"."LOCK_OBJECT_RECORD" 308.4 KB 8122 rows
. . exported "SCOTT"."DEPT" 5.929 KB 4 rows
. . exported "SCOTT"."EMP" 8.562 KB 14 rows
. . exported "SCOTT"."EMPY" 8.562 KB 14 rows
. . exported "SCOTT"."EMPYY" 8.570 KB 14 rows
. . exported "SCOTT"."EMPZ" 8.562 KB 14 rows
. . exported "SCOTT"."SALGRADE" 5.859 KB 5 rows
. . exported "SCOTT"."BONUS" 0 KB 0 rows
. . exported "SCOTT"."T" 0 KB 0 rows
Master table "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_SCHEMA_01 is:
/u01/app/oracle/admin/book/dpdump/expdat.dmp
Job "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully completed at Wed Dec 11 10:56:44 2019 elapsed 0 00:00:18
--//ok,没有任何问题.
--//测试后:
SYS@book> select * from v$sgainfo;
NAME BYTES RES
---------------------------------------- ---------- ---
Fixed SGA Size 2255872 No
Redo Buffers 7487488 No
Buffer Cache Size 406847488 Yes
Shared Pool Size 180355072 Yes
Large Pool Size 12582912 Yes
Java Pool Size 12582912 Yes
Streams Pool Size 20971520 Yes
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Shared IO Pool Size 0 Yes
Granule Size 4194304 No
Maximum SGA Size 643084288 No
Startup overhead in Shared Pool 133446832 No
Free SGA Memory Available 0
12 rows selected.
--//Streams Pool Size=20971520
SYS@book> @ hide streams_pool_size
NAME DESCRIPTION DEFAULT_VALUE SESSION_VALUE SYSTEM_VALUE ISSES ISSYS_MOD
------------------- ------------------------------------ ------------- ------------- ------------ ----- ---------
__streams_pool_size Actual size in bytes of streams pool TRUE 0 0 FALSE IMMEDIATE
streams_pool_size size in bytes of the streams pool FALSE 20971520 20971520 FALSE IMMEDIATE
--//oracle会从别的地方借用内存,我这里从Buffer Cache Size .
--//406847488/1024/1024 = 388M
SYS@book> show spparameter db_cache_size
SID NAME TYPE VALUE
-------- ------------- ----------- -----
* db_cache_size big integer 408M
--//我以前总以为如果我定义db_cache_size是使用数据缓存的最小值.实际上还是可以缩小的.
3.继续分析:
SYS@book> show spparameter sga_
SID NAME TYPE VALUE
-------- ------------- ------------ -----------
* sga_max_size big integer
* sga_target big integer
--//实际上我的测试环境并没有配置sga_target,sga_max_size.而实际上启动后设置sga_max_size到内存.
SYS@book> show parameter sga_
NAME TYPE VALUE
------------ ----------- -----
sga_max_size big integer 616M
sga_target big integer 0
--//假设我写入参数文件.
*.sga_max_size=600M
*.sga_target=600M
SYS@book> startup pfile='/tmp/@.ora';
ORA-00821: Specified value of sga_target 600M is too small, needs to be at least 616M
ORA-01078: failure in processing system parameters
--//而如果修改:
*.sga_max_size=600M
*.sga_target=0M
SYS@book> startup pfile='/tmp/@.ora';
ORACLE instance started.
Total System Global Area 643084288 bytes
Fixed Size 2255872 bytes
Variable Size 205521920 bytes
Database Buffers 427819008 bytes
Redo Buffers 7487488 bytes
Database mounted.
Database opened.
SYS@book> show parameter sga_
NAME TYPE VALUE
------------ ----------- ------
sga_max_size big integer 616M
sga_target big integer 0
--//sga_max_size自动在内存中修改参数为616M.
SYS@book> create spfile from pfile='/tmp/@.ora';
File created.
SYS@book> shutdown immediate ;
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@book> startup
ORACLE instance started.
Total System Global Area 643084288 bytes
Fixed Size 2255872 bytes
Variable Size 205521920 bytes
Database Buffers 427819008 bytes
Redo Buffers 7487488 bytes
Database mounted.
Database opened.
SYS@book> show spparameter sga_
SID NAME TYPE VALUE
-------- ------------ ------------ -------
* sga_max_size big integer 600M
* sga_target big integer 0
SYS@book> show parameter sga_
NAME TYPE VALUE
------------ ----------- ------
sga_max_size big integer 616M
sga_target big integer 0
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/267265/viewspace-2667945/ ，如需转载，请注明出处，否则将追究法律责任。
最新文章
- [20261008]验证Shared Memory Segments key以及Semaphore Arrays Key(11g).txt
- [20261008]gdb引入结构体是否可行.txt
- [20261008]bpftrace定义结构体例子.txt
- [20261007]关于oracle sskgph2函数.txt
- [20261007]oracle Semaphore Arrays Key值的冲突.txt
- [20261004]oracle sskgph2函数等价c以及python脚本.txt
- [20261004]bpftrace查看oracle函数调用堆栈问题.txt
- [20261002]验证sskgphpjw函数参与运算的字符串是否存在规律.txt
- [20261002]oracle Semaphore Arrays Key值的计算2.txt
- [20261001]oracle Semaphore Arrays Key值的计算.txt
