P R E F A C E OWI OWI OS()Shell Script 3
4P R E F A C E Oracle Enterprise Linux 5Oracle 11g OUISilent ModeSilent mode ' ' AWR SQL
SQLPL SQL 1 2 "" P R E F A C E 5
6P R E F A C E prodba(httpcafe naver com prodba) ocp(httpcafe daum net ocp) " " 20135
C O N T E N T S 7
8C O N T E N T S
C O N T E N T S 9
10C O N T E N T S
11 C O N T E N T S
12C O N T E N T S
13 C O N T E N T S
14C O N T E N T S
C O N T E N T S 15
16C O N T E N T S
ORACLE
ORACLE Oracle Oracle Database CPU HDD Oracle HDD Oracle OracleDataBase Management System(DBMS) DatabaseDatabase DBMSOracle 18
DatabaseDatabase 1 1 1 2 Oracle Oracle DBMS(DataBase Management SystemSoftware) DBMSSQL DBMS Database DBMS DBMS Database SQL DBMS 19
1 Oracle Oracle Oracle Oracle Oracle Oracle '''Cat' Oracle Oracle Oracle Oracle Oracle 20
1Oracle Server() 2Instance 3SGA 4Dynamic SGA 5PGA Oracle Server OracleOracle OracleOracle Server(Oracle Oracle Server ) (Data files)db(control files)(redo log files) (Database) Oracle Server 21
<Oracle Server> <Oracle InstanceDatabase> 22
(System Global Area)(Background Process)SGA ProcessOracle Server Oracle Oracle Oracle 10g Automatic Shared Memory Management(ASMM)11g AMM(Automatic Memory Management) InstanceDatabase ASMMAMM Oracle Instance Oracle InstanceSGA Background Process(11g ) 23
24 <Oracle Instance> InstanceInstance OracleDB Oracle (startup) ( ) startuporacle Server Process (pfilespfile) OS Kernel OSKernel RAM OS Kernel SGA (Server Process ) Oracle Server Process OSKernel
OS Kernel(etc sysctl conf etc system) (SGA ) OS KernelOracle RAM1G 2GOracle OS Kernel etc sysctl conf 500MBOracle1G 500MB RAM RAMetc sysctl conf SGA Oracle Server Process OS Kernel Kernel SGAOracle Server ProcessSGA InstanceSGA OS KernelRAMOracle RAM Server Process Oracle OS () Kernel OSOS Oracle 25
SGAProcess Process RAM Process (Kernel Panic Blue Screen) OS Process Oracle ServerProcess Process (set) Process releaseunset set set unset Kernel Unix OS SEMMSLProcess 26
Oracle ServerOracle 10 100 Oracle 11gPROCESSES150 SEMMNI Oracle100 SEMMNS SEMMSL X SEMMNI SEMOPM1call(1) SEMMSLSEMOPMSEMMSL [oracle@localhost ~]$ ipcs -ls ------ Semaphore Limits -------- max number of arrays = 128 SEMMNI. max semaphores per array = 250 SEMMSL. max semaphores system wide = 32000 SEMMSN. max ops per semop call = 100 SEMOPM. Oracle Oracle KernelOracle Process 27
Kernel (RAM)Oracle( etc sysctl confetc system ) SHMMAX () Oracle SGAServer Process Kernel SHMMAXKernelOracle 10 101 OracleRAM100MB 20MB100MB 5 10 5 10 12 1028 SGA Oracle 28
kernel shmmax DB [oracle@localhost ~]$ sqlplus / as sysdba SQL*Plus: Release 10.2.0.5.0 - Production on Wed Jan 26 12:08:05 2011 Copyright (c) 1982, 2010, Oracle. All Rights Reserved. ERROR: ORA-12547: TNS:lost contact Enter user-name: Server. ORA-27123unable to attach to shared memory segment SHMMAX [root@localhost ~]# cat /proc/sys/kernel/shmmax 16777214. 16MB. SHMMAX32MB Oracle SGASHMMAX 2GB 12GB 1)proc Server SHM- MAX [root@localhost ~]# cat /proc/sys/kernel/shmmax 16777214. 16MB. [root@localhost ~]# echo "2147483648" > /proc/sys/kernel/shmmax [root@localhost ~]# cat /proc/sys/kernel/shmmax 2147483648. 2GB. 29
2)sysctlSHMMAX # sysctl -w kernel.shmmax=2147483648 3)etc sysctl confkernel (vi editor) OSroot sysctl -p SHMMNI SHMMNI 4096 OracleRAM10G SHMMAX 2GOracle 5Segment SGA 4096 SHMMNI [root@localhost ~]# cat /proc/sys/kernel/shmmni 4096 SHMALL SHMALL( ) ceil(shmmax/page_size) SHMALL2097152 bytes 30
[root@localhost ~]# cat /proc/sys/kernel/shmall 2097152 i386red Hat Linux4 096 OS OS SHMMIN (byte) SHMSEG 1Process SHMMNI 1Process Kernel OS Kernel SHMSEG 1Process KernelSGA Oracle3 segment SGAsegment segment 31
segment SGAfixed Area 1 segment 10 10 10 Oracle SGA 10 32
SGA SGA Oracle Kernel SGA OS Oracle Instance SGA OracleOracle ProcessOS KernelSGA SGA ( ) SGA Oracle SGA Oracle 33
SGASGA SGA Database Buffer Cache Database Buffer CacheBuffer CacheSGA Database Buffer Cache Database Buffer Cache SELECT DML SQL Database Buffer CacheSGA 34
(kernel panic) (A ) Database Buffer Cache Database Buffer Cache Database Buffer Cache I O OracleDatabase Buffer Cache Block 3 -Pinned BufferBuffer PinnedBufferA -Dirty Buffer Buffer Buffer A -Free Buffer Buffer(Unused)Dirty Buffer Database Buffer CacheBuffer Block Buffer Block Oracle BufferLRU(Least Recently Used) List 35
LRU LRU List SGA SGA SGA100MB 150MB SGA () 10 101 10 101 LRU SGAShared PoolDatabase Buffer Cache LRU DB Buffer CacheLRU LRU List Oracle LRU ListLRULRUW LRU List LRUW List( ) 36
LRU List BufferHotCold BufferDBWRBuffer (Free list) LRUW List Buffer(Dirty list ) DBWR Buffer DB Buffer Cache DB Buffer CacheFree Buffer LRUfree Buffer Buffercoldfree Bufferfree Buffer(10g 40 )Free Buffer ScanDBWR Dirty Buffer(DBWRDB Buffer Cache ) Dirty Buffer Dirty BufferFree BufferLRU List Free Block Database Buffer Cache ( DB Buffer Cache Server Process) BufferLRU List 37
List Server ProcessList 100 100DB Buffer Cache 100DB Buffer Cache100Server Process DB Buffer Cache DB Buffer Cache100FreeBlock 100Server ProcessFree Block Free List 100Server ProcessFree Block Oracle DB Buffer Cache Working Data Set Free Buffer Process Oracle Latch Latch Latch Latch SGA Redo Log Buffer Latch Process Latch 38
Process Latch LatchLock SGADatabase Buffer Cache Redo Log Buffer SGARedo Log Buffer (DDLDML ) Redo log Redo log Redo log redo log Oracle Redo Log Redo Log Redo Log BufferRedo Log Buffer Redo Log File DDLDMLTCLRedo logselect Redo log Direct Load(SQL Loaderinsert*AP- PEND * )tableindexnologging 39
Redo lognologging insertupdatedeleteredo Log Direct LoadDatapump Redo LogOracle Recovery Redo log Redo LogRedo Log SGARedo Log BufferRedo Log Redo Log Shared Pool SGAShared Pool <Shared Pool> 40
11g Soft ParseSQL PL SQLParse CodeSQL PLSQL LRU(Soft ParseHard Parse SQL ) DictionaryRowCacheLRU Library CacheDictionary Cache SQL11g Server Result Cache CacheServer (11g)Select DB Buffer Cache(Fetch) PGA SQL DB Buffer Cache DB Buffer CacheDB Buffer Cache ( Latch ) 11gShared PoolServer Result Cache SelectDB Buffer Cache 41
Server Result Cache DB Buffer CacheI O DB Server DB Buffer Cache SQLSQL () RESULT CACHE MODE ForceSQLResult Cache RESULT CACHE MODE Manual Result cacheresult Cache SYS>show parameter result_cache_mode ; NAME TYPE VALUE ----------------------------- ---------- ------------------------------ result_cache_mode string MANUAL SYS>create table scott.rtest 2 (no number, name varchar2(10)); Table created. 42
SYS>begin 2 for i in 1..200000 loop 3 insert into scott.rtest values (i,dbms_random.string('a',9)); 4 end loop; 5 commit; 6 end; 7 / SYS>select count(*) from scott.rtest ; COUNT(*) ------------- 200000 20. 20scott rtest result cache SYS> set timing on ; SYS> select * from scott.rtest; ( ) 184902 CuGrkBkCp 184903 gtorbpzin 184904 ALKtoMizV 184905 JVLKcecKe 184906 OERavgPqe Result Cache 200000 rows selected. Elapsed: 00:00:15.06 15 06. 43
SYS> select /*+ result_cache */ * from scott.rtest ; ( ) 184901 TIaHwHgaX 184902 CuGrkBkCp 184903 gtorbpzin 184904 ALKtoMizV *result cache *result cache 184905 JVLKcecKe 184906 OERavgPqe 200000 rows selected. Elapsed: 00:00:15.07 15 07. SYS>alter system set result_cache_mode=force ; System altered. SYS>show parameter result_cache_mode ; NAME TYPE VALUE ---------------------------- ----------- ------------------------------ result_cache_mode string FORCE result cache modeforce result cache Database Buffer Cache Database Buffer Cache DBWR Buffer DBWR Result Cache Result Cache 44
Database Buffer Cache Result Cache Database Buffer CacheResult Cache 11g Server Result Cache SQL PL SQL Shared Pool Shared Pool5KB(11g) java PL SQLSQL Shared Pool Oracle SHARED POOL RESERVED SIZE Shared Pool Size5 Shared Pool Size50 V SHARED POOL RESERVED V SHARED POOL RESERVEDREQUEST FAILURES REQUEST MISSES0 Shared Pool shared pool size Library CacheDictionary Cache Shared Pool SizeDB (Oracle 9i ) 45
SQL> alter system set shared_pool_size = 100 M ; Oracle (granule) Dynamic SGA Large Pool Large Pool SGA Large Pool -Shared Server modeoracle ServerUGA -Parallel Execution( )Process Message Buffer -RMANRMAN I OBuffer Shared Pool LRU Java Pool SGA java codejava Virtual Machine(JVM) Streams Pool 10gSGA Streams 0 StreamsOracle Streams 46
Fixed SGA Oracle Process Database Process Lock Oracle SGA SGA Oracle ProcessOS KernelRAMSGA SGA <SGA> 47
SGAOracle ProcessKernel SGA SGA Oracle 8i Oracle Instance SELECT Database Buffer Cache 8iDB DB 9i (Redo log buffer) alter system set Database Buffer Cache100MB SYS> alter system set DB_CACHE_SIZE=100 M ; Database Buffer Cache 48
100MB Oracle SGA MAX SIZE 9i SGA MAX SIZE128MB1GranuleSGA MAX SIZE 128MB1Granule10g SGA MAX SIZE9i128MB 10g1GB1Granule10g SGA MAX SIZE1GB1Granule4MB1GB1Granule16M( DB CACHE SIZESGA MAX SIZE ) 1 5(Granule) OracleSGA SYS> show sga ; Total System Global Area 285212672 bytes Fixed Size 1273252 bytes Variable Size 92275292 bytes Database Buffers 184549376 bytes Redo Buffers 7114752 bytes SGA background process Shared poollarge PoolJava pool DB Buffer CacheRedo Buffers 49
SGA MAX SIZE SYS> show parameter sga_max_size ; NAME TYPE VALUE ----------------------------- ------------- ------------------------------ sga_max_size big integer 160M shared pool SYS> show parameter shared_pool_size; NAME TYPE VALUE ----------------------------- ------------- ------------------------------ shared_pool_size big integer 0 DataBase Buffer Cache SYS> show parameter db_cache_size; NAME TYPE VALUE ----------------------------- ------------- ------------------------------ db_cache_size big integer 0 shared pooldb buffer cache0 Oracle 10gASMM ASMM SGAOracle SGASGA Oracle GranuleGranule shared pool size010m 10M4 50
12M SQL> alter system set shared_pool_size=10m; System altered. SQL> show parameter shared_pool_size; NAME TYPE VALUE ----------------------------- ------------- ------------------------------ shared_pool_size big integer 12M SGA MAX SIZE160M1G 1granule 4M SQL> alter system set shared_pool_size=9m; System altered. SQL> show parameter shared_pool_size; NAME TYPE VALUE ---------------------------- ------------ -------------------------------- shared_pool_size big integer 12M 9M 12M. SQL> alter system set shared_pool_size=8m ; 4 8M System altered. SQL> show parameter shared_pool_size; NAME TYPE VALUE ---------------------------- ------------ -------------------------------- shared_pool_size big integer 8M 8M Oracle 51
Oracle SGAPGASGA SGAProcess PGA Process SGA PGA PGA Process Oracle ServerProcessPGA Server ProcessProcess PGA Server Process PGAInstance PGA Server ProcessPGA 52
PGA Private SQL Area SQL User ProcessServer Process User Process SQLServer ProcessUser ProcessSession Memory SQLParseSQL Bind Bind Private SQL Area QueryQuery --Bind (Bind 2SQL ) Private SQL AreaPersistent AreaRuntime Area Persistent AreaBind Runtime AreaSQL 100 100DB Buffer CachePGA Fetch 100Fetch Runtime Area 53
SQL Work Area Sort(Sort Area)Hash PGA 8i (SORT AREA SIZEHASH AREA SIZEBITMAP MERGE AREA SIZECREATE BITMAP AREA SIZE Parameter File ) 9iPGAOracle Server PGA PGA AGGREGATE TARGETWORKAREA SIZE POLICY AUTO PGA Oracle ServerWORKAREA SIZE POLICYMANUAL PGA AGGREGATE TARGET Server ProcessPGA AG- GREGATE TARGET 100M 1Server Process 100M1Server Process PGASMM MAX SIZE 9Oracle PGA Oracle - OLTP PGA_AGGREGATE_TARGET = (<> * 80%) * 20% 54
- DSS PGA_AGGREGATE_TARGET = (< * 80%) * 50% 16GServerPGA - OLTP PGA_AGGREGATE_TARGET = (16G X 0.8) X 0.2 = 2.56 G - DSS PGA_AGGREGATE_TARGET = (16G X 0.8) X 0.5 = 6.4 G ServerPGA V PGASTAT SYS>select * from v$pgastat ; NAME VALUE UNIT --------------------------------------------------- ---------- --------- aggregate PGA target parameter 146800640 bytes aggregate PGA auto target 98648064 bytes global memory bound 29360128 bytes total PGA inuse 37182464 bytes total PGA allocated 52882432 bytes maximum PGA allocated 175848448 bytes total freeable PGA memory 9633792 bytes process count 28 max processes count 37 PGA memory freed back to OS 437452800 bytes total PGA used for auto workareas 0 bytes maximum PGA used for auto workareas 2620416 bytes total PGA used for manual workareas 0 bytes maximum PGA used for manual workareas 0 bytes over allocation count 0 bytes processed 565979136 bytes 55
extra bytes read/written 0 bytes cache hit percentage 100 percent recompute count (total) 9695 19 rows selected. Oracle Server Oracle InstanceBackground Process 3 Oracle OracleOracle SGASQL SQL 56