<BED5BACEBCD32E696E6464>

Size: px

Start display at page:

Download "<BED5BACEBCD32E696E6464>"

성은 견
4 years ago
Views:

3 P R E F A C E OWI OWI OS()Shell Script 3

4 4P R E F A C E Oracle Enterprise Linux 5Oracle 11g OUISilent ModeSilent mode ' ' AWR SQL

5 SQLPL SQL 1 2 "" P R E F A C E 5

6 6P R E F A C E prodba(httpcafe naver com prodba) ocp(httpcafe daum net ocp) " " 20135

7 C O N T E N T S 7

8 8C O N T E N T S

9 C O N T E N T S 9

10 10C O N T E N T S

11 11 C O N T E N T S

12 12C O N T E N T S

13 13 C O N T E N T S

14 14C O N T E N T S

15 C O N T E N T S 15

16 16C O N T E N T S

17 ORACLE

18 ORACLE Oracle Oracle Database CPU HDD Oracle HDD Oracle OracleDataBase Management System(DBMS) DatabaseDatabase DBMSOracle 18

19 DatabaseDatabase Oracle Oracle DBMS(DataBase Management SystemSoftware) DBMSSQL DBMS Database DBMS DBMS Database SQL DBMS 19

20 1 Oracle Oracle Oracle Oracle Oracle Oracle '''Cat' Oracle Oracle Oracle Oracle Oracle 20

21 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

22 <Oracle Server> <Oracle InstanceDatabase> 22

23 (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 24 <Oracle Instance> InstanceInstance OracleDB Oracle (startup) ( ) startuporacle Server Process (pfilespfile) OS Kernel OSKernel RAM OS Kernel SGA (Server Process ) Oracle Server Process OSKernel

25 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

26 SGAProcess Process RAM Process (Kernel Panic Blue Screen) OS Process Oracle ServerProcess Process (set) Process releaseunset set set unset Kernel Unix OS SEMMSLProcess 26

27 Oracle ServerOracle 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 = SEMMSN. max ops per semop call = 100 SEMOPM. Oracle Oracle KernelOracle Process 27

28 Kernel (RAM)Oracle( etc sysctl confetc system ) SHMMAX () Oracle SGAServer Process Kernel SHMMAXKernelOracle OracleRAM100MB 20MB100MB SGA Oracle 28

29 kernel shmmax DB ~]$ sqlplus / as sysdba SQL*Plus: Release Production on Wed Jan 26 12:08: 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 MB. SHMMAX32MB Oracle SGASHMMAX 2GB 12GB 1)proc Server SHM- MAX [root@localhost ~]# cat /proc/sys/kernel/shmmax MB. [root@localhost ~]# echo " " > /proc/sys/kernel/shmmax [root@localhost ~]# cat /proc/sys/kernel/shmmax GB. 29

30 2)sysctlSHMMAX # sysctl -w kernel.shmmax= )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) SHMALL bytes 30

31 ~]# cat /proc/sys/kernel/shmall i386red Hat Linux4 096 OS OS SHMMIN (byte) SHMSEG 1Process SHMMNI 1Process Kernel OS Kernel SHMSEG 1Process KernelSGA Oracle3 segment SGAsegment segment 31

32 segment SGAfixed Area 1 segment Oracle SGA 10 32

33 SGA SGA Oracle Kernel SGA OS Oracle Instance SGA OracleOracle ProcessOS KernelSGA SGA ( ) SGA Oracle SGA Oracle 33

34 SGASGA SGA Database Buffer Cache Database Buffer CacheBuffer CacheSGA Database Buffer Cache Database Buffer Cache SELECT DML SQL Database Buffer CacheSGA 34

35 (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

36 LRU LRU List SGA SGA SGA100MB 150MB SGA () LRU SGAShared PoolDatabase Buffer Cache LRU DB Buffer CacheLRU LRU List Oracle LRU ListLRULRUW LRU List LRUW List( ) 36

37 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

38 List Server ProcessList DB 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

39 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

40 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

41 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

42 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

43 SYS>begin 2 for i in 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(*) scott rtest result cache SYS> set timing on ; SYS> select * from scott.rtest; ( ) CuGrkBkCp gtorbpzin ALKtoMizV JVLKcecKe OERavgPqe Result Cache rows selected. Elapsed: 00:00:

44 SYS> select /*+ result_cache */ * from scott.rtest ; ( ) TIaHwHgaX CuGrkBkCp gtorbpzin ALKtoMizV *result cache *result cache JVLKcecKe OERavgPqe rows selected. Elapsed: 00:00: 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

45 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

46 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

47 Fixed SGA Oracle Process Database Process Lock Oracle SGA SGA Oracle ProcessOS KernelRAMSGA SGA <SGA> 47

48 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

49 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 bytes Fixed Size bytes Variable Size bytes Database Buffers bytes Redo Buffers bytes SGA background process Shared poollarge PoolJava pool DB Buffer CacheRedo Buffers 49

50 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

51 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

52 Oracle SGAPGASGA SGAProcess PGA Process SGA PGA PGA Process Oracle ServerProcessPGA Server ProcessProcess PGA Server Process PGAInstance PGA Server ProcessPGA 52

53 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 DB Buffer CachePGA Fetch 100Fetch Runtime Area 53

54 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

55 - 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 bytes aggregate PGA auto target bytes global memory bound bytes total PGA inuse bytes total PGA allocated bytes maximum PGA allocated bytes total freeable PGA memory bytes process count 28 max processes count 37 PGA memory freed back to OS bytes total PGA used for auto workareas 0 bytes maximum PGA used for auto workareas bytes total PGA used for manual workareas 0 bytes maximum PGA used for manual workareas 0 bytes over allocation count 0 bytes processed bytes 55

56 extra bytes read/written 0 bytes cache hit percentage 100 percent recompute count (total) rows selected. Oracle Server Oracle InstanceBackground Process 3 Oracle OracleOracle SGASQL SQL 56

목 차

목 차 Oracle 9i Admim 1. Oracle RDBMS 1.1 (System Global Area:SGA) 1.1.1 (Shared Pool) 1.1.2 (Database Buffer Cache) 1.1.3 (Redo Log Buffer) 1.1.4 Java Pool Large Pool 1.2 Program Global Area (PGA) 1.3 Oracle