[ D-4 ] Oracle9i Database

Transcription

1

2 [ D-4 ] Oracle9i Database

3

4

5 1. ( ) 2. ( ) 3. ( ) 4. ( )

6 SQL DB

7 DB DB SQL I/O OS

8 init.ora

9 SQL Oracle Oracle Oracle

10 Oracle9i Database Tuning New Features

11 Oracle9i New Features Tuning SGA PGA V$SQL_PLAN UNDO (CPU,DISK I/O) Cursor_sharing=similar FIRST_ROWS_n

12 SGA SGA REDO Java MTS

13 SGA SQL PL/SQL OLTP REDO Java Oracle OracleJVM

14 SGA Oracle9i SGA SGA sga_max_size SGA [byte] sga_max_sixze>=db_cache_size+shared_pool_size+log_buffer +large_pool_size

15 SGA db_cache_size [byte] db_block_buffers db_block_buffers db_nk_cache_size db_block_size Ex. db_4k_cache_size shared_pool_size [byte] log_buffer [byte] large_pool_size [byte]

16 SGA SQL*Plus ALTER SYSTEM OEM

17 Oracle Enterprise Manager GUI Alter system set db_cache_advice=on V$DB_CACHE_ADVICE

18 V$DB_CACHE_ADVICE Physical read factor Physical reads DB_CACHE_ADVICE ALTER SYSTEM ON OFF READY

19 V$DB_CACHE_ADVICE SELECT SIZE_FOR_ESTIMATE EST_SIZE, BUFFERS_FOR_ESTIMATE EST_BUFFERS, ESTD_PHYSICAL_READ_FACTOR PHYSIC_FACT, ESTD_PHYSICAL_READS PHYSIC_READS FROM FROM V$DB_CACHE_ADVICE WHERE WHERE NAME NAME = 'DEFAULT' AND AND BLOCK_SIZE = '8192'; EST_SIZE EST_BUFFERS PHYSIC_FACT PHYSIC_READS

20 PGA MTS Dedicated PGA UGA MTS PGA UGA

21 PGA PGA PGA SORT_AREA_SIZE HASH_AREA_SIZE BITMAP_MERGE_AREA_SIZE CREATE_BITMAP_AREA_SIZE PGA_AGGREGATE_TARGET Oracle9i 1 OK PGA MTS

22 PGA OLTP PGA_AGGREGATE_TARGET = (TOTAL_MEM * 80%) * 20% DSS PGA_AGGREGATE_TARGET = (TOTAL_MEM * 80%) * 50% V$PGASTAT V$SYSSTAT V$SESSTAT V$PROCESS V$SQL_WORKAREA_ACTIVE V$SQL_WORKAREA

23 SQL V$SYSSTAT V$SESSTAT V$PGASTAT PGA V$PROCESS SQL V$SQL_WORKAREA_ACTIVE V$SQL_WORKAREA

24 V$SYSSTAT V$SESSTAT optimal one-pass multi-pass PGA workarea memory allocated workarea executions optimal workarea executions onepass one-pass workarea executions - multipass multi-pass

25 V$SYSTAT V$SESSTAT V$SYSTAT PGA SQL SELECT name profile, cnt, decode(total,0,0,round(cnt*100/total)) percentage FROM (SELECT NAME,VALUE cnt,(sum(value) over()) total FROM V$SYSSTAT WHERE NAME like 'workarea exec%'); PROFILE CNT PERCENTAGE workarea executions - optimal workarea executions - onepass workarea executions - multipass 0 0

26 V$PGASTAT ESTIMATED PGA MEMORY REQUIREMENT FOR ONE-PASS one-pass PGA_AGGREGATE_TARGET multi-pass

27 V$PROCESS Oracle PGA PGA_USED_MEM PGA_ALLOCATED_MEM PGA_MAX_MEM

28 V$SQL_WORKAREA_ACTIVE SQL KB SQL NUMBER_PASSES SQL optimal 0 one-pass 1 multi-pass 1

29 V$SQL_WORKAREA_ACTIVE SQL SQL SELECT to_number(decode(sid, 65535, NULL, SID)) sid, operation_type OPERATION, trunc(work_area_size/1024) WSIZE, trunc(expected_size/1024) ESIZE, trunc(actual_mem_used/1024) MEM, trunc(max_mem_used/1024) "MAX MEM", NUMBER_PASSES PASS FROM V$SQL_WORKAREA_ACTIVE ORDER BY 1,2;

30 V$SQL_WORKAREA SQL one-pass mult-pass SQL one-pass multi-pass SELECT SQL_TEXT, sum(onepass_executions), sum(multipasses_executions) FROM V$SQL s, V$SQL_WORKAREA wa WHERE s.address = wa.address GROUP BY SQL_TEXT HAVING sum(onepass_executions+multipasses_executions)>0;

31 Oracle9i monitoring v$object_usage used YES nomonitoring nomonitoring v$object_usage monitoring

32 Monitoring monitoring v$object_usage used YES alter index pk_dept monitoring usage;

33 Monitoring Monitoring v$object_usage end_monitoring monitoring NO alter index pk_dept nomonitoring usage; Select * from v$object_usage where used= NO ;

34 SQL> desc v$object_usage NULL? INDEX_NAME NOT NULL VARCHAR2(30) TABLE_NAME NOT NULL VARCHAR2(30) MONITORING VARCHAR2(3) USED VARCHAR2(3) START_MONITORING VARCHAR2(19) END_MONITORING VARCHAR2(19)

35 V$SQL_PLAN EXPLAIN PLAN ALTER SESSION V$SQLAREA V$SQL

36 SQL TOP5 SELECT * FROM (SELECT buffer_gets,address,hash_value sql_text FROM V$SQLAREA ORDER BY BYbuffer_gets DESC) WHERE ROWNUM < 6; 6; TOP5 SELECT * FROM (SELECT buffer_gets,address,hash_value sql_text FROM V$SQLAREA ORDER BY BYbuffer_gets DESC) WHERE ROWNUM < 6; 6;

37 hash_value address column id id format newline column operation format a20 a20 column options format a15 a15 column object_name format a22 a22trunc column optimizer format a3 a3trunc SELECT id id,, lpad lpad (' ('',', depth) operation operation, options,, object_name, optimizer, cost cost FROM V$SQL_PLAN WHERE hash_value = AND address = '5765E0DC' START WITH id id = 0 CONNECT BY BY (( prior prior id id = parent_id AND prior priorhash_value = hash_value AND prior priorchild_number = child_number )) ORDER SIBLINGS BY BY id, id, position;

38 SQL ID ID OPERATION OPTIONS OBJECT_NAME OPT OPT COST SELECT STATEMENT CHO 1 TABLE ACCESS FULL DEPT

39 UNDO UNDO Oracle9i DB 2 2 undo_management UNDO auto Undo UNDO manual undo_retention UNDO CREATE DATABASE CREATE UNDO TABLESPACE UNDO UNDO_TABLESPACE

40 Undo Undo V$UNDOSTAT UNDO UNDO UNDO

41 ANALYZE DBMS_STATS.GATHER_TABLE_STATS('SCOTT','EMP') 10% DBMS_STATS.GATHER_TABLE_STATS('SCOTT','EMP',esti mate_percent=>10) SCOTT 10% DBMS_STATS.GATHER_SCHEMA_STATS('SCOTT',10,option s=>'gather') DBMS_STATS.DELETE_TABLE_STATS('SCOTT','EMP')

42 1. Monitoring ALTER ALTER TABLE TABLEemp empmonitoring; Oracle8i 2. user_tab_modifications SELECT * FROM FROMuser_tab_modifications; 3. GATHER STALE BEGIN BEGIN DBMS_STATS.GATHER_SCHEMA_STATS('SCOTT', options=>'gather STALE'); END; END; //

43 ~ Statspack ~ BSTAT/ESTAT $ORACLE_HOME/rdbms/admin/UTLBSTAT.SQL,UTLESTAT.SQL Statspack BSTAT/ESTAT BSTAT/ESTAT Statspack SQL connect / as sysdba SQL connect perfstat/perfstat SQL execute statspack.snap: SQL connect / as sysdba

44 Oracle9i Database Tuning Disk I/O

45 I/O REDO

46 I/O REDO REDO

47 I/O REDO OLTP REDO I/O REDO REDO REDO

48 I/O

49 RAID RAID0 RAID1

50 RAID5

51 RAID0 1 RAID10 RAID UP RAID

52 Oracle RAID Oracle RAID OLTP DSS SYSTEM RAID RAID RAID RAID RAID RAID RAID RAID REDO RAID RAID RAID RAID RAID REDO RAID RAID

53 I/O I/O V$FILESTAT V$DATAFILE I/O I/O I/O SELECT name, phyrds, phywrts FROM v$datafile df, v$filestat fs WHERE df.file# = fs.file#;

54 Oracle9i

55 Oracle9i SPFILE SGA undo Oracle Managed File internal Server Manager

56 SPFILE ALTER SYSTEM Oracle9i SGA

57 1. Startup pfile 2. $ORACLE_HOME/dbs/spfile<SID>.ora 3. $ORACLE_HOME/dbs/spfile.ora 4. $ORACLE_HOME/dbs/init<SID>.ora spfile init<sid>.ora spfile init<sid>.ora spfile

58 SPFILE SYSOPER SYSDBA init.ora spfile $ sqlplus "/ "/ as as sysdba" SQL> CREATE SPFILE ='?/dbs/spfileorcl.ora 2 FROM PFILE='?/dbs/initorcl.ora ; spfile $ sqlplus "/ "/ as as sysdba" SQL> CREATE SPFILE FROM PFILE='?/dbs/initorcl.ora ;

59 spfile ALTER SYSTEM SCOPE ALTER SYSTEM SET SET = SCOPE=BOTH; SPFILE=BOTH SPFILE SPFILE SCOPE=MEMORY SCOPE=SPFILE SPFILE ALTER SYSTEM SET =

60 SQL*Plus sqlplus Server Manager svrmgrl Import imp / Export exp SQL*Loader(sqlldr) Net Configuration Assistant netca Net8 Assistant netasst Oracle Net Manager(netmgr Database Configuration Assistant dbassist dbca Oracle Enterprise Manager Configuration Assistant emca # Linux

61 Linux & Oracle9i Tips & New Features

62 kernel 2.4 & Oracle9i SMP 2GB 64GB OS 64GB PentiumIV

63 Large File Support Oracle8i 2GB 2GB Oracle9i db_block_size 2k 4K 8K 16K Max File Size 8GB 16GB 32GB 64GB

64 rawdevices rawdevice /etc/fstab raw rc.sysinit raw rawdevices /etc/sysconfig/rawdevices raw /dev/raw/raw1 /dev/sda1

65 kparam kparam Miracle Linux Oracle SHMMAX ( ) Miracle Linux SHMMAX SHMMAX SHMSEG

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

67 sysstat sysstat Linux 3 mpstat sar iostat I/O man top

68 sar I/O 2 $ sar sar-b -b 2 0 Linux mlsmp (jpdel38b9.jp.oracle.com) 10/15/01 16:52:30 tps tps rtps rtps wtps wtps bread/s bwrtn/s 16:52: :52: $ sar sar-b -B 2 0 Linux mlsmp (jpdel38b9.jp.oracle.com) 10/15/01 16:55:17 pgpgin/s pgpgout/s 16:55: :55: :55:

69