[ D-4 ] Oracle9i Database
1. ( ) 2. ( ) 3. ( ) 4. ( )
SQL DB
DB DB SQL I/O OS
init.ora
SQL Oracle Oracle Oracle
Oracle9i Database Tuning New Features
Oracle9i New Features Tuning SGA PGA V$SQL_PLAN UNDO (CPU,DISK I/O) Cursor_sharing=similar FIRST_ROWS_n
SGA SGA REDO Java MTS
SGA SQL PL/SQL OLTP REDO Java Oracle OracleJVM
SGA Oracle9i SGA SGA sga_max_size SGA [byte] sga_max_sixze>=db_cache_size+shared_pool_size+log_buffer +large_pool_size
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]
SGA SQL*Plus ALTER SYSTEM OEM
Oracle Enterprise Manager GUI Alter system set db_cache_advice=on V$DB_CACHE_ADVICE
V$DB_CACHE_ADVICE Physical read factor Physical reads DB_CACHE_ADVICE ALTER SYSTEM ON OFF READY
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 ---------- ----------- ----------- ------------ 2000 2000 250 250 2.02 2.02 802244 4000 4000 500 500 1.79 1.79 712277 6000 6000 750 750 1.61 1.61 638439 8000 8000 1000 1000 1.47 1.47 583440
PGA MTS Dedicated PGA UGA MTS PGA UGA
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
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
SQL V$SYSSTAT V$SESSTAT V$PGASTAT PGA V$PROCESS SQL V$SQL_WORKAREA_ACTIVE V$SQL_WORKAREA
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
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 5390 95 workarea executions - onepass 280 5 workarea executions - multipass 0 0
V$PGASTAT ESTIMATED PGA MEMORY REQUIREMENT FOR ONE-PASS one-pass PGA_AGGREGATE_TARGET multi-pass
V$PROCESS Oracle PGA PGA_USED_MEM PGA_ALLOCATED_MEM PGA_MAX_MEM
V$SQL_WORKAREA_ACTIVE SQL KB SQL NUMBER_PASSES SQL optimal 0 one-pass 1 multi-pass 1
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;
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;
Oracle9i monitoring v$object_usage used YES nomonitoring nomonitoring v$object_usage monitoring
Monitoring monitoring v$object_usage used YES alter index pk_dept monitoring usage;
Monitoring Monitoring v$object_usage end_monitoring monitoring NO alter index pk_dept nomonitoring usage; Select * from v$object_usage where used= NO ;
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)
V$SQL_PLAN EXPLAIN PLAN ALTER SESSION V$SQLAREA V$SQL
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;
hash_value address column id id format 999 999newline 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 = 817628907 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;
SQL ID ID OPERATION OPTIONS OBJECT_NAME OPT OPT COST ---- ------------------------ --------------- ---------------------- --- ------------- 0 SELECT STATEMENT CHO 1 TABLE ACCESS FULL DEPT
UNDO UNDO Oracle9i DB 2 2 undo_management UNDO auto Undo UNDO manual undo_retention UNDO CREATE DATABASE CREATE UNDO TABLESPACE UNDO UNDO_TABLESPACE
Undo Undo V$UNDOSTAT UNDO UNDO UNDO
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')
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; //
~ Statspack ~ BSTAT/ESTAT $ORACLE_HOME/rdbms/admin/UTLBSTAT.SQL,UTLESTAT.SQL Statspack BSTAT/ESTAT BSTAT/ESTAT Statspack SQL connect / as sysdba SQL @?/rdbms/admin/spcreate SQL connect perfstat/perfstat SQL execute statspack.snap: SQL connect / as sysdba SQL @?/rdbms/admin/spreport
Oracle9i Database Tuning Disk I/O
I/O REDO
I/O REDO REDO
I/O REDO OLTP REDO I/O REDO REDO REDO
I/O
RAID RAID0 RAID1
RAID5
RAID0 1 RAID10 RAID UP RAID
Oracle RAID Oracle RAID OLTP DSS SYSTEM RAID RAID RAID RAID RAID RAID RAID RAID REDO RAID RAID RAID RAID RAID REDO RAID RAID
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#;
Oracle9i
Oracle9i SPFILE SGA undo Oracle Managed File internal Server Manager
SPFILE ALTER SYSTEM Oracle9i SGA
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
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 ;
spfile ALTER SYSTEM SCOPE ALTER SYSTEM SET SET = SCOPE=BOTH; SPFILE=BOTH SPFILE SPFILE SCOPE=MEMORY SCOPE=SPFILE SPFILE ALTER SYSTEM SET =
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
Linux & Oracle9i Tips & New Features
kernel 2.4 & Oracle9i SMP 2GB 64GB OS 64GB PentiumIV
Large File Support Oracle8i 2GB 2GB Oracle9i db_block_size 2k 4K 8K 16K Max File Size 8GB 16GB 32GB 64GB
rawdevices rawdevice /etc/fstab raw rc.sysinit raw rawdevices /etc/sysconfig/rawdevices raw /dev/raw/raw1 /dev/sda1
kparam kparam Miracle Linux Oracle SHMMAX ( ) Miracle Linux SHMMAX SHMMAX SHMSEG
kparam /etc/sysconfig/kparam # vi vi /etc/sysconfig/kparam /proc/sys/kernel/semopm 100 100 /proc/sys/kernel/shmmax 262934528 /proc/sys/kernel/sysrq 0 /proc/sys/kernel/panic 10 10 /proc/sys/kernel/sem 250 25032000 100 100128 128 kparam # service kparam restart
sysstat sysstat Linux 3 mpstat sar iostat I/O man top
sar I/O 2 $ sar sar-b -b 2 0 Linux 2.4.7-2.14mlsmp (jpdel38b9.jp.oracle.com) 10/15/01 16:52:30 tps tps rtps rtps wtps wtps bread/s bwrtn/s 16:52:32 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 16:52:34 1.50 1.50 0.00 0.00 1.50 1.50 0.00 0.00 24.00 2 $ sar sar-b -B 2 0 Linux 2.4.7-2.14mlsmp (jpdel38b9.jp.oracle.com) 10/15/01 16:55:17 pgpgin/s pgpgout/s 16:55:19 0.00 0.00 0.00 0.00 16:55:21 0.00 0.00 12.00 16:55:23 0.00 0.00 12.00