oracle体系结构学习＝＝＝＝＝＝＝＝＝＝＝＝＝＝[1.1] 内存设置 [1.1.1]9i SGA设置 [1.1.2]10G SGA设置 [1.1.3]9i PGA设置 [1.1.3]10g PGA设置[1.2] 进程设置 [1.1.1]9i SGA设置＝＝＝＝＝＝＝＝＝＝[1.1.1.1] SGAsqlplus "/as sysdba"SQL> show sgaTotal System Global Area 285212672 bytesFixed Size 1218992 bytesVariable Size 71304784 bytesDatabase Buffers 209715200 bytesRedo Buffers 2973696 bytesSQL> show parameter sga_max_sizeNAME TYPE VALUE------------------------------------ ----------- ------------------------------sga_max_size big integer 272M＝＝9I SGA_MAX_SIZE是ORACLE能从OS里能分配的最大共享内存，它不能动态修改，需要重起＝＝但不不见得完全分配给ORACLE使用了，其它所有共享内存的总和要小于等于SGA_MAX_SIZESQL> !ipcs -sa------ Shared Memory Segments --------key shmid owner perms bytes nattch status 0xc936d330 0 oracle 640 287309824 18 ------ Semaphore Arrays --------key semid owner perms nsems 0xa3042ca8 98304 oracle 640 154 ------ Message Queues --------key msqid owner perms used-bytes messages ＝＝使用ipcs -sa命令可以查看共享内存段＝＝大家可以比较Shared Memory Segments的bytes总和与sga的值!ipcs -sm (aix)＝＝AIX执行上面语句＝＝shared memory segments 可能更大一点，因为操作系统有保护页compute sum of bytes on poolbreak on pool skip 1select pool, name, bytes from v$sgastat order by pool, name; POOL NAME BYTES----------- -------------------------- ----------java pool free memory 33554432*********** ----------sum 33554432large pool free memory 8388608*********** ----------sum 8388608shared pool 1M buffer 2098176 Checkpoint queue 282304 FileIdentificatonBlock 323292POOL NAME BYTES----------- -------------------------- ----------shared pool FileOpenBlock 695504 KGK heap 3756 KGLS heap 561932 KQR M PO 225828 KQR S PO 55040 KSXR large reply queue 166104 KSXR pending messages que 841036 KSXR receive buffers 1033000 MTTR advisory 4116 PLS non-lib hp 2068 VIRTUAL CIRCUITS 265160POOL NAME BYTES----------- -------------------------- ----------shared pool character set memory 163288 character set object 375756 db_handles 108000 dictionary cache 1610880 enqueue 171860 event statistics per sess 1718360 fixed allocation callback 220 free memory 47563280 joxs heap init 4220 kgl simulator 565956 kglsim sga 136104POOL NAME BYTES----------- -------------------------- ----------shared pool krvxrr 126528 ksm_file2sga region 148652 library cache 1941880 message pool freequeue 834752 miscellaneous 3436004 processes 144000 sessions 410720 sim memory hea 21164 sql area 1067292 table definiti 168 trigger defini 1360POOL NAME BYTES----------- -------------------------- ----------shared pool trigger inform 1044 trigger source 60*********** ----------sum 67108864 buffer_cache 25165824 fixed_sga 453492 log_buffer 656384*********** ----------sum 26275700select component, granule_size from v$sga_dynamic_components;COMPONENT GRANULE_SIZE---------------------------------------------------------------- ------------shared pool 4194304large pool 4194304buffer cache 4194304 ＝＝这个就是颗粒[1.1.1.2] redo log＝＝＝＝＝＝＝＝＝＝＝SQL> alter system set log_buffer=1 scope=spfile;系统已更改。SQL> startup forceORACLE 例程已经启动。Total System Global Area 289406976 bytesFixed Size 1248600 bytesVariable Size 92275368 bytesDatabase Buffers 192937984 bytesRedo Buffers 2945024 bytes数据库装载完毕。数据库已经打开。SQL> SQL> show parameter log_bufferNAME TYPE VALUE------------------------------------ ----------- ------------------------------log_buffer integer 2927616SQL> ＝＝比较下下面两个值Redo Buffers2973696log_buffer 2927616 ＝＝为什么redo buffer 比较大啊？ ＝＝我们看到两个值不同,是因为有保护页 ＝＝注意,设置log_buffer=1,但是实际上log_buffer最终不是1 ＝＝因为log_buffer有个最小值 ＝＝log_buffer最少是(128 * number of CPUs)或者512k ＝＝所以大家看到不是1 [1.1.1.3] db buffer＝＝＝＝＝＝＝＝＝＝＝SQL> show parameter db_cache_sizeNAME TYPE VALUE------------------------------------ ----------- ------------------------------db_cache_size big integer 503316480＝＝注意要在9I非ASMM（自动共享内存管理）环境下才有值 SQL> SELECT o.object_name, COUNT(1) number_of_blocks 2 FROM DBA_OBJECTS o, V$BH bh 3 WHERE o.object_id = bh.objd 4 AND o.owner != 'SYS' 5 GROUP BY o.object_name 6 ORDER BY count(1) 7 ;OBJECT_NAME NUMBER_OF_BLOCKS-------------------------------------------------------------------------------- ----------------MGMT_DUPLICATE_TARGETS_PK 1DEF$_AQCALL 1AQ$_QUEUE_TABLES 1MGMT_METRIC_THRESHOLDS_IDX_02 1MGMT_JOB_PK 1CURRENT_SEVERITY_PRIMARY_KEY 1MGMT_METRIC_COLLECTIONS_PK ＝＝该语句可以查看对象在DATABASE BUFFER CACHE中的块数 ＝＝上面看到的是我们的对象在DB BUFFER的情况 ＝＝这个database buffer cache的数据字典是x$bh ＝＝bh是buffer header的意思 SQL> create tablespace ts_16k 2 datafile '/tmp/test_block_size.dbf' size 5m 3 blocksize 16k;create tablespace ts_16k* ERROR 在行 1:ORA-29339: 表格空间区块大小 16384 与设定的区块大小不符合＝＝我们来模拟非标准块，由于没有配置非标准块缓存，所以创建失败SQL> alter system set db_16k_cache_size = 50m;System altered. create tablespace ts_16k datafile '/tmp/test_block_size.dbf' size 5m blocksize 16k; SQL> create table test_block_16K_size tablespace ts_16k as select * from dba_objects;create table test_block_16K_size tablespace ts_16k as select * from dba_objects *第 1 行出现错误:ORA-01652: 无法通过 64 (在表空间 TS_16K 中) 扩展 temp 段SQL> r 1 create table test_block_16K_size tablespace ts_16k 2* as select * from dba_objects where rownum<1001Table created. ＝＝注意表空间是否足够大容纳数据SQL> SELECT NAME, BLOCK_SIZE, SUM(BUFFERS) 2 FROM V$BUFFER_POOL 3 GROUP BY NAME, BLOCK_SIZE 4 HAVING SUM(BUFFERS) > 0;NAME BLOCK_SIZE SUM(BUFFERS)-------------------- ---------- ------------DEFAULT 8192 15968DEFAULT 16384 3276＝＝这里看到标准块缓冲区和非标准块缓冲区的大小[1.1.1.4] shared pool＝＝＝＝＝＝＝＝＝＝＝＝SQL> select sum(bytes) from v$sgastat where pool = 'shared pool';SUM(BYTES)---------- 50331648＝＝注意要在9I非ASMM（自动共享内存管理）环境下才有值NAME TYPE VALUE------------------------------------ ----------- ------------shared_pool_size big integer 50331648 SQL> select * from V$LIBRARYCACHE ;NAMESPACE GETS GETHITS GETHITRATIO PINS PINHITS PINHITRATIO RELOADS INVALIDATIONS DLM_LOCK_REQUESTS DLM_PIN_REQUESTS DLM_PIN_RELEASES DLM_INVALIDATION_REQUESTS DLM_INVALIDATIONS--------------- ---- ------- ----------- ---- ------- ----------- ------- ------------- ----------------- ---------------- ---------------- ------------------------- -----------------SQL AREA 1821 42 0.023064250 1227 11093 0.904074979 167 2 0 0 0 0 0TABLE/PROCEDURE 3100 1120 0.361290322 4852 2893 0.596248969 18 0 0 0 0 0 0BODY 218 187 0.857798165 291 260 0.893470790 0 0 0 0 0 0 0TRIGGER 17 9 0.529411764 84 76 0.904761904 0 0 0 0 0 0 0INDEX 38 0 0 38 0 0 0 0 0 0 0 0 0CLUSTER 91 83 0.912087912 259 251 0.969111969 0 0 0 0 0 0 0OBJECT 0 0 1 0 0 1 0 0 0 0 0 0 0PIPE 0 0 1 0 0 1 0 0 0 0 0 0 0JAVA SOURCE 0 0 1 0 0 1 0 0 0 0 0 0 0JAVA RESOURCE 0 0 1 0 0 1 0 0 0 0 0 0 0JAVA DATA 0 0 1 0 0 1 0 0 0 0 0 0 0＝＝查看LIBRARY CACHE＝＝我们看到左边是命名空间，什么样的对象类型存放在LIBRARY CACHE，命中率多少等信息SQL> select * from V$ROWCACHE ; CACHE# TYPE SUBORDINATE# PARAMETER COUNT USAGE FIXED GETS GETMISSES SCANS SCANMISSES SCANCOMPLETES MODIFICATIONS FLUSHES DLM_REQUESTS DLM_CONFLICTS DLM_RELEASES---------- ----------- ------------ -------------------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ------------- ------------- ---------- ------------ ------------- ------------ 25 PARENT dc_hintsets 0 0 0 0 0 0 0 0 0 0 0 0 0 7 SUBORDINATE 0 dc_users 0 0 0 0 0 0 0 0 0 0 0 0 0 8 SUBORDINATE 0 dc_object_grants 4 4 0 10 4 0 0 0 0 0 0 0 0 16 SUBORDINATE 0 dc_histogram_data 558 558 0 1916 751 0 0 0 0 0 0 0 0 16 SUBORDINATE 1 dc_histogram_data 73 73 0 744 118 0 0 0 0 0 0 0 0 19 SUBORDINATE 0 dc_partition_scns 0 0 0 0 0 0 0 0 0 0 0 0 0 7 SUBORDINATE 1 dc_users 2 2 0 18 2 0 0 0 0 0 0 0 0 7 SUBORDINATE 2 dc_users 0 0 0 0 0 0 0 0 0 0 0 0 0 21 SUBORDINATE 0 rule_fast_operators 0 0 0 0 0 0 0 0 0 0 0 0 0＝＝查看数据字典CACHE＝＝v$rowcache看到的字典数据信息及命中率 ＝＝dc开头的是字典类型[1.1.1.5] 共享SQL区＝＝＝＝＝＝＝＝＝＝＝＝私有SQL区＝＝＝＝＝SELECT * FROM v$open_cursor where rownum < 10; ＝＝大家用plsqldev来看 共享SQL区＝＝＝＝＝SELECT * FROM V$SQLAREA where rownum < 10;＝＝LIBRARY CACHE的共享SQL用v$sqlarea来查看 ＝＝私有的CURSOR,要查看v$open_cursor [1.1.1.6] large pool＝＝＝＝＝＝＝＝＝＝＝＝SQL> alter system set large_pool_size = 30m;系统已更改。＝＝9i SGA内的组件是动态SGA组件，可以在数据库打开的时候改变共享内存大小SQL> show parameter large_pool;NAME TYPE VALUE------------------------------------ ----------- -----------------large_pool_size big integer 32M ＝＝注意这里不是ASMM＝＝我们看到是32M,而不是30M＝＝有位32是4的倍数，是粒度4M的倍数。 ＝＝GRANULE_SIZE[1.1.1.6] java pool＝＝＝＝＝＝＝＝＝＝＝＝show parameter java_pool_size;NAME TYPE VALUE------------------------------------ ----------- ------------------------------java_pool_size big integer 32M ＝＝这里不是ASMMSQL> alter system set java_pool_size = 30m;alter system set java_pool_size = 30m *ERROR 位于第 1 行:ORA-02095: 无法修改指定的初始化参数 ＝＝10g java pool可以修改。 SQL> select ISSYS_MODIFIABLE,ISMODIFIED from v$parameter where upper(name) like '%JAVA%';ISSYS_MOD ISMODIFIED--------- ----------FALSE FALSEFALSE FALSEFALSE FALSE ＝＝查看参数修改模式＝＝需要制定spfile alter system set java_pool_size = 30m scope=spfile; SQL> select ISSYS_MODIFIABLE,ISMODIFIED from v$parameter where upper(name) like '%JAVA%';ISSYS_MOD ISMODIFIED--------- ----------IMMEDIATE SYSTEM_MODFALSE FALSEFALSE FALSE ＝＝10g的结果[1.1.2]10G SGA设置 ＝＝＝＝＝＝＝＝＝＝＝[1.1.2.1] SGA＝＝＝＝＝＝＝＝＝＝＝＝＝10g后支持SGA内的各组件由ORACLE在运行中根据负载应用情况及内存使用情况，自动调整个内存的大小＝＝称为ASMM，自动共享内存管理＝＝比如初始时配置共享池为150M，后来由于应用需要超过150M的共享池，那么在9I就会报4031错误，说共享池不够．＝＝即使数据高速缓存还有很多空闲内存＝＝需要用alter system set shared_pool_size来修改，这显然需要DBA时刻跟踪，而且应用已经报错了＝＝在10g ASMM，则系统自动会从其它内存空闲组件分配一定的内存补给共享池＝＝只要指定SGA_TARGET，那么db_cache_size, shared_pool_size, large_pool_size and java_pool_size＝＝四大组件会在SGA_TARGET指定大小范围内自动在根据需要动态调整大小．＝＝11g 可以实现SGA和PGA统一自动管理MEMORY_TARGET／MEMORY_MAX_TARGETSQL> show parameter sga_target NAME TYPE VALUE------------------------------------ ----------- ---------------sga_target big integer 1000M＝＝注意,sga_target不只包括四大组件，还包括不能自动内存管理的其它组件，如＝＝log_buffer, streams_pool, db_Nk_cache_size, db_keep_cache_size, and db_recycle_cache_size＝＝所以保留池等内存永远是手工设置的SQL> show sga Total System Global Area 1048576000 bytesFixed Size 782424 bytesVariable Size 259002280 bytesDatabase Buffers 788529152 bytesRedo Buffers 262144 bytes＝＝内存分配了sga_target大小SQL> show parameter pool NAME TYPE VALUE------------------------------------ ----------- ------------------------------buffer_pool_keep stringbuffer_pool_recycle stringglobal_context_pool_size stringjava_pool_size big integer 0large_pool_size big integer 0olap_page_pool_size big integer 0shared_pool_reserved_size big integer 12373196shared_pool_size big integer 0streams_pool_size big integer 0＝＝注意，只要设置sga_target>0，则起用ASMM,sga_target=0,人工配置共享内存＝＝注意，人工配置共享内存，则需要db_block_buffers, shared_pool_size, large_pool_size and java_pool_size＝＝我们看到这里不是db_cache_size，而是db_block_buffers__java_pool_size 4194304__large_pool_size 4194304__shared_pool_size 247463936＝＝如果设置ASSM，则初始的内存组件由下画线来查看＝＝create spfile='/tmp/pfile.ora' from spfile;来查看＝＝或者用下面的语句查看隐含参数SELECT name, value, decode(isdefault, 'TRUE', 'Y', 'N') as "Default", decode(ISEM, 'TRUE', 'Y', 'N') as SesMod, decode(ISYM, 'IMMEDIATE', 'I', 'DEFERRED', 'D', 'FALSE', 'N') as SysMod, decode(IMOD, 'MODIFIED', 'U', 'SYS_MODIFIED', 'S', 'N') as Modified, decode(IADJ, 'TRUE', 'Y', 'N') as Adjusted, description FROM ( --GV$SYSTEM_PARAMETER SELECT x.inst_id as instance, x.indx + 1, ksppinm as name, ksppity, ksppstvl as value, ksppstdf as isdefault, decode(bitand(ksppiflg / 256, 1), 1, 'TRUE', 'FALSE') as ISEM, decode(bitand(ksppiflg / 65536, 3), 1, 'IMMEDIATE', 2, 'DEFERRED', 'FALSE') as ISYM, decode(bitand(ksppstvf, 7), 1, 'MODIFIED', 'FALSE') as IMOD, decode(bitand(ksppstvf, 2), 2, 'TRUE', 'FALSE') as IADJ, ksppdesc as description FROM x$ksppi x, x$ksppsv y where x.indx = y.indx and substr(ksppinm, 1, 1) = '_' and lower(ksppinm) like '%pool_size%' and x.inst_id = USERENV('Instance')) order by name;SQL> show parameter sga NAME TYPE VALUE------------------------------------ ----------- ------------------------------lock_sga boolean FALSEpre_page_sga boolean FALSEsga_max_size big integer 1200Msga_target big integer 1008M＝＝sga_max_size>=sga_target＝＝sga_max_size-sga_target部分在磁盘上，可以动态分配SQL> alter system set sga_target = 1200m; System altered.SQL> alter system set sga_target = 1008M; System altered.SQL> select sum(value)/1024/1024 from v$sga; SUM(VALUE)/1024/1024 -------------------- 1200 ＝＝v$sga总显示SGA_MAX_SIZE，但是实际上只到SGA_TARGET，多出来的部分在磁盘上，可以动态分配 SQL> alter system set sga_target = 1300m;alter system set sga_target = 1300m*ERROR at line 1:ORA-02097: parameter cannot be modified because specified value is invalidORA-00823: Specified value of sga_target greater than sga_max_size＝＝超过了SGA_MAX_SIZE[1.2]进程实验＝＝＝＝＝＝＝[1.2.1]连接、会话、进程间的关系 SQL> select a.spid dedicated_server, 2 b.process clientpid 3 from v$process a, v$session b 4 where a.addr = b.paddr 5 and b.sid = (select sid from v$mystat where rownum=1) 6 / DEDICATED_SERVER CLIENTPID ---------------- ------------ 6463 6462 SQL> select username, sid, serial#, server, paddr, status 2 from v$session 3 where username = USER; USERNAME SID SERIAL# SERVER PADDR STATUS ------------------------------ ---------- ---------- --------- -------- --------SYS 147 595 DEDICATED 2CA1C924 ACTIVE ＝＝注意不同的版本，这时可能已经出现多个会话，内部有新的任务＝＝我们接着在同一个连接里，另设置一个任务，就是执行计划自动跟踪统计set autot on ＝＝设置自动统计，需要设置utlxplan.sql和plustrace权限 select username, sid, serial#, server, paddr, status from v$session where username = USER USERNAME SID SERIAL# SERVER PADDR STATUS ------------------------------ ---------- ---------- --------- -------- --------SYS 144 134 DEDICATED 2CA1C924 INACTIVESYS 147 595 DEDICATED 2CA1C924 ACTIVE ＝＝相同的连接,相同的PADDR,也就是相同的进程，有两个会话＝＝活动的会话是原始会话 [1.2.1]DBWn ＝＝n是进程序列号 ＝＝＝＝＝＝＝＝＝＝NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_writer_processes integer 1 SQL> backup_tape_io_slaves boolean FALSE dbwr_io_slaves integer 0 ＝＝这几个参数用来提高DBWR的性能,也影响到LGWR,ARCH db_writer_processes ＝＝＝＝＝＝＝＝＝＝＝＝DBWn的并行写进程,默认为1 ＝＝9I可以设置到10 ＝＝如果为1,我们可以通过设置异步IO来提高写的性能 [oracle@pejoy ~]$ cat /proc/slabinfo | grep kio kioctx 18 20 192 20 1 : tunables 120 60 8 : slabdata 1 1 0 kiocb 0 0 128 31 1 : tunables 120 60 8 : slabdata 0 0 0 异步IO＝＝＝＝＝＝＝如果同步IO写100个块 for i in 1 .. 100 loop 写请求第i个块到OS,等待.... 直到OS反馈写完成 end loop 同步IO＝＝＝＝＝＝＝如果同步IO写100个块 for i in 1 .. 100 loop 写请求第i个块到OS,马上写请求第i+1块到OS 不等OS反馈 end loop 查看操作系统的异步IO ＝＝＝＝＝＝＝＝＝＝AIX :lsdev -C|grep aioLINUX:cat /proc/slabinfo | grep kio [oracle@pejoy ~]$ cat /proc/slabinfo | grep kio kioctx 18 20 192 20 1 : tunables 120 60 8 : slabdata 1 1 0 kiocb 0 0 128 31 1 : tunables 120 60 8 : slabdata 0 0 0 ＝＝如果启动异步IO,db_writer_processes只要设置1就可以了 ＝＝如果没有启动异步IO,我们可以用IO从进程来代替 SQL> NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ backup_tape_io_slaves boolean FALSE dbwr_io_slaves integer 0 ＝＝你可以设置dbwr_io_slaves=10 ＝＝当一个写进程在等待时,另一个从进程不用等待，可以异步的去执行 ＝＝如果启动了异步IO,请不要设置dbwr_io_slaves ＝＝如果启动了异步IO,也不要将DB_writer_processes>1 ＝＝如果没有异步IO,也不用IO从进程来模拟异步IO ,那么可以用并行写的后台进程来实现,DB_writer_processes>1 ＝＝但是DB_writer_processes不要大于CPU数 CPU数计算 ＝＝＝＝＝＝AIX:lsdev -C|grep proc LINUX/SOLRAIS: dmesg -a|grep -i "CPU" ＝＝db_writer_processes需要CPU支持 ＝＝在CPU消耗非常厉害的系统建议不要使用disk_asynch_io和db_writer_processes大于1 ＝＝如果设置了DBWR_IO_SLAVES or BACKUP_TAPE_IO_SLAVES ,那么就要设置LARGE_POOL＝＝否则对于这种写从进程IO到共享池将严重的造成共享池错误，如ORA-4031，IO从进程会用到大池
