Oracle for Linux を使いこなす技

Size: px

Start display at page:

Download "Oracle for Linux を使いこなす技"

こうじかんざとばる
9 years ago
Views:

1 Oracle9i for Linux

2 Linux Monitoring the Oracle Monitoring the Server

4 vs vs OS vs Oracle vs SQL vs

7 3 I/O I/O

8 STEP1: STEP2:DB DB STEP3: STEP4: DB etc

9 STEP5:SQL SQL STEP6: Oracle STEP7: I/O I/O STEP8: STEP9:OS

10 1. ( ) 2. ( ) 3. ( ) 4. ( )

11 SQL

12 Oracle OS DB: STATSPACK OS: vmstat, iostat I/O Oracle 20%

13 Linux

14 Linux

15 MIRACLE LINUX V2.0 I/O MIRACLE LINUX V2.0 CD-ROM kernel Web kernel Red Hat Linux 7.1 CD-ROM kernel Web kernel-2.4.9

16 Oracle SGA SGA Oracle9i Database R1(9.0.1) 1.8GB Oracle9i Database R2(9.2) 62GB 4GB

17 SHMMAX 1 SHMMAX SHMSEG Oracle SHMMAX 1/2 SGA 1 SGA<SHMMAX kparam sysctl ipcs Oracle sysresv

18 kparam kparam MIRACLE LINUX MIRACLE LINUX

19 kparam /etc/sysconfig/kparam # vi /etc/sysconfig/kparam /proc/sys/kernel/semopm 100 /proc/sys/kernel/shmmax /proc/sys/kernel/sysrq 0 /proc/sys/kernel/panic 10 /proc/sys/kernel/sem kparam # service kparam restart

262934528 /proc/sys/kernel/sysrq 0 /proc/sys/kernel/panic

20 Oracle OS MIRACLE LINUX V2.0/2.1 ext2, ext3, ReiserFS, RAW I/O RAW ext2 ext3 HA ext3 MIRACLE LINUX V2.0/V2.1 ext3 ext3 mkinitrd

21 i-node -j ext3 # mke2fs [-j] -b -T i-node Oracle OS OS : 4K Oracle 4K, 8K, 16K # tune2fs -l

22 # mke2fs -b -T i-node 1024,2048,4098 i-node News Largefile Largefile4 4KB 1node 1MB 1node 4MB 1node # mke2fs -j -b T largefile4 /dev/sdb1

23 UNIX/Linux atime 3 mtime ctime atime atime

24 atime 2 /etc/fstab noatime /dev/sdb2 /home ext2 defaults,noatime 1 2 $ chattr +A * $ cd < > stat $ stat

25 Monitoring the Oracle

26 SQL Oracle SQL /tkprof EXPLAIN PLAN SQL*Plus BSTAT/ESTAT STATSPACK Oracle Enterprise Manager Diagnostics Pack/Tuning Pack V$

27 Oracle8i R8.1.6 STATSPACK BSTAT/ESTAT 2

28 STATSPACK STATSPACK $ sqlplus / as sysdba 2 SQL> connect perfstat/pertstat SQL> execute statspack.snap # SQL> execute statspack.snap(i_snap_level=>5)

29 STATSPACK SQL> connect perfstat/pertstat Top 5 Wait Events ~~~~~~~~~~~~~~~~~ Wait % Total Event Waits Time (s) Wt Time log file parallel write SQL*Net more data from dblink 39, SQL*Net message from dblink sort segment request

30 Monitoring the Server

31 I/O CPU I/O I/O CPU CPU Oracle

32 Linux Linux vmstat sysstat sar,iostat,mpstat,isag netstat ps aux ipcs free uptime Linux /proc

33 CPU Run Queue/Wait Queue vmstat cpu(usr, sys, idl) sar -U vmstat r/b vmstat memory (free,swpd,buff,cache) sar -r vmstat bi/bo Disk I/O sar -B sar -b vmstat si/so I/O sar -W iostat -x await DiskQueue iostat -x avgqu-sz

34 vmstat vmstat IO CPU 1 OS CPU I/O Excel vmstat [s] $ vmstat procs memory swap io system cpu r b w swpd free buff cache si so bi bo in cs us sy id

35 2 $ vmstat 2 vmstat CTRL+C n 1 tee Excel $ vmstat -n 2 tee output_vmstat

36 Process r: CPU vmstat b: I/O r CPU b Memory swpd: (KB) free: (KB) buff: (KB) cache: (KB)

37 vmstat Swap si: (kb/s) so: (kb/s)

38 vmstat IO bi: (blocks/s) bo: (blocks/s) CPU CPU us: sy: id:

39 I/O iostat CPU iostat [s] $ iostat -dt 2 Linux mlsmp (host.your.domain.name) 03/01/02 Time: 07:35:06 Device: tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn dev dev Time: 07:35:08 Device: tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn dev dev

40 iostat iostat $ iostat -d -x $ iostat -d -x sdb 2 Linux mlsmp (dhcp-0198.miraclelinux.com) 03/01/02 Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz avgqu-sz await svctm %util sdb Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz avgqu-sz await svctm %util sdb

41 iostat avgqu-sz I/O I/O await I/O [m sec] %util I/O CPU

43 STATSPACK, iostat, vmstat

44 STATSPACK STATSPACK A Load Profile ~~~~~~~~~~~~ Per Second Per Transaction Redo size: 443, ,577, Logical reads: , Block changes: , Physical reads: , Physical writes: ,148.89

45 STATSPACK 1 Instance Efficiency Percentages (Target 100%) ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Buffer Nowait %: Redo NoWait %: Buffer Hit %: In-memory Sort %: Library Hit %: Shared Pool Statistics Begin End Memory Usage %: % SQL with executions>1: % Memory for SQL w/exec>1: SQL 2

46 STATSPACK Top5 Wait Events Top 5 Wait Events ~~~~~~~~~~~~~~~~~ Wait %Total Event Waits Time (s) Wt Time log file switch (checkpoint incomplete) 1,182 1, log file parallel write 4, log buffer space 3, control file parallel write 1, log file switch completion % Wait REDO

47 vmstat (CPU, Process) Wait queue 1

48 vmstat ( free

49 STATSPACK CPU wait queue 1 I/O REDO REDO REDO :10MB 1GB REDO :500KB 5MB 2

50 Appendix: sar/isag free Web

51 sysstat sar iostat mpstat CPU sar sadc sar isag sar sadc iostat I/O

52 sar UNIX/Linux CPU -o -f

53 sar I/O 2 10 $ sar -b 2 10 Linux mlsmp (host.your.domain.name) 10/15/01 16:52:30 tps rtps wtps bread/s bwrtn/s 16:52: $ sar -W 2 0 Linux mlsmp (host.your.domain.name) 10/15/01 16:55:17 pswpin/s pswpout/s 16:55: :55: B pgpgin/s pgpgout/s I/O

54 2 sar 100 outfile $ sar -A o outfile outfile I/O $ sar -b -f ourfile /var/lib/sa $ sar

55 MIRACLE LINUX V2.0 sadc(sar) 10 /etc/cron.hourly/sysstat, /etc/cron.daily/sysstat /var/lib/sa $ ls /var/log/sa sa21 sa23 sa25 sa27 sar21 sar23 sar25 sar27 sa22 sa24 sa26 sa28 sar22 sar24 sar26 sar28 sann NN sarnn NN

56 isag(interactive System Activity Grapher) sar/sadc I/O CPU etc

57 isag isag [-p datafile_path] datafile_path /var/lib/sa $ isag -p. Chart

58 free $ free total used free shared buffers cached Mem: /+ buffers/cache: Swap: Mem Total OS Used Free OS (cached) (buffers) Shared

59 free -/+ buffers/cache Used Mem:Used - buffers - cached Free Mem:Free + buffers + cached Swap Total swap Used Free Kernel-4.html

60 Web MIRACLE LINUX MIRACLE LINUX MIRACLE LINUX MIRACLE LINUX

61 Web Oracle Technology Network Japan Linux & Windows Linux OTN Oracle Technology Network

62 Web How to Get Maximum Performance from Linux and Oracle Linux Performance Tuning Oracle FAQ sysstat home page

63 Oracle9i Oracle9i Oracle 13 Oracle 2 Mark Gurry, Peter Corrigan Oracle Oracle8

64 Oracle SQL Tuning Pocket Reference Mark Gurry, O REILLY Oracle9i Oracle9i for Windows 2000 Tips & Techniques Scott Jesse,Matthew Hart, OSBORNE Oracle9i Windows 2000 Windows 2000 Oracle9i ORACLE High-Performance SQL Tuning Donald K.Burleson, OSBORNE SQL

65 Oracle SQL High-Performance Tuning 2nd Guy Harrison,Prentice Hall 1 cle DBA on Unix and Linux Michael Wessler, SAMS Linux/UNIX

~~~~~~~~~~~~~~~~~~ wait Call CPU time 1, latch: library cache 7, latch: library cache lock 4, job scheduler co

072 DB Magazine 2007 September ~~~~~~~~~~~~~~~~~~ wait Call CPU time 1,055 34.7 latch: library cache 7,278 750 103 24.7 latch: library cache lock 4,194 465 111 15.3 job scheduler coordinator slave wait