Oracle Database 10g: Self-Managing Database DB TSC
Agenda Overview System Resource Application & SQL Storage Space Backup & Recovery
½ Cost
? 6% 12 % 6% 6% 55% : IOUG 2001 DBA Survey
? 6% & 12 % 6% 6% 55% : IOUG 2001 DBA Survey
CD CPU,, OS,,CPU database, listeners, agent, OMS, OID
(Seed) ( 1 CD, 20 ) initsid.ora 90% ( < 30 ) GUI Database Upgrade Assistant (DBUA) Oracle 8i / 9i 111 steps! Oracle 10g 6 steps!
? 6% & 12 % 6% 6% 55% : IOUG 2001 DBA Survey
Oracle Database 10g Data Pump Oracle9i Export 60% Oracle9i Import 15 ~ 20 Export Transportable Tablespace
( Data Pump) - : 16,200,000 - :2 Gb exp direct=y exp direct=y buffer=2m recordlength=64k expdp Parallel=1 Program Elapsed 0 hr 10 min 40 sec 0 hr 04 min 08 sec 0 hr 03 min 12 sec imp imp buffer=2m recordlength=64k impdp Parallel=1 2 hr 26 min 10 sec 2 hr 18 min 37 sec 0 hr 03 min 05 sec
? 6% & 12 % 6% 6% 55% : IOUG 2001 DBA Survey
DBA - Today Application & SQL Optimizer Stats Response time Throughput Schema/Index Backup & Recovery Tapes, MTTR Disaster Recovery System Resource CPU Utilization Memory Pools Processes Internal Space Table growth trend Space fragmentation External Storage Disk Configuration Stripe Size Data Redistribution
Oracle Database 10g Now Self-Managing Database Application & SQL Storage System Resource UI Backup & Recovery Database Intelligent Infrastructure Space
(Intelligent Infrastructure ) Automated Tasks (Automatic Workload Repository) Server Alerts Advisory Framework (Automatic Maintenance Tasks) Automatic Workload Repository (Server-generated Alerts) (Advisory Infrastructure)
(Automatic Workload Repository) BG BG FG FG In-memory statistics AWR Statistics SGA ASH MMON 7:00 a.m. 7:30 a.m. 8:00 a.m. 8:30 a.m. SYSAUX WR Schema Snapshot 1 Snapshot 2 Snapshot 3 Snapshot 4 ADDM finds top problems Seven days 8:30am DBA
(Automatic Database Diagnostic Monitor), ADDM finds top problems
ADDM Snapshots in Automatic Workload Repository Automatic Diagnostic Engine 10 High-load SQL IO / CPU issues RAC issues (Symptoms) (root) CPU,, Lock,I/O, RAC SQL Advisor System Sizing Advice Network + DB config Advice
ADDM - EM DB
(Shared) System Resource Buffer Cache Large Pool SQL Cache Java Pool sort Buffer Cache Large Pool SQL Cache Java Pool sort SGA Pool PGA Pool 2 Pool
SGA System Resource SGA DB_CACHE_SIZE SHARED_POOL_SIZE LARGE_POOL_SIZE JAVA_POOL_SIZE (enable) Automatic Shared Memory SGA_TARGET
- SQL Application & SQL SQL? SQL? SQL Workload DBA SQL DBA
Oracle 10g Application & SQL SQL!!!! ADDM SQL DBA SQL Workload SQL Tuning Advisor
SQL (SQL Tuning Advisor) Automatic Tuning Optimizer Statistics Analysis SQL Tuning Advisor SQL Tuning Recommendations Gather Missing or Stale Statistics System Resource SQL Profiling Access Path Analysis Create a SQL Profile Add Missing Indexes DBA SQL Structure Analysis Modify SQL Constructs
(Automatic Storage ) Storage 3 rd Party ASM DB / (rebalancing)
10g ASM Storage 10g ASM Tables Tablespace Files File System Logical Vol Disks Group 0010 0010 0010 0010 0010 0010 0010 0010 0010 0010 Tables Tablespace Files File System Logical Vol Disk Group Oracle10g ASM.
ASM -EM Storage (Rebalancing)
ASM Storage I/O / DBA
Space, 90 80 70 60 50 40 30 20 10 0 Capacity Planning 1st Qtr 2nd Qtr 3rd Qtr 4th Qtr Server- Generat ed Alerts,
Space, (Shrink) Capacity Planning Server- Generated Alerts Online Segment Shrink 90 80 70 60 50 40 30 20 10 0 1st Qtr 2nd Qtr 3rd Qtr 4th Qtr
Space Data Unused Space Data Unused Space HWM (Shrink) ALTER TABLE SHRINK SPACE [COMPACT]; HWM
Backup & Recovery (Flash Recovery Area) Database Area Flash Recovery Area 2 Recovery Area (incremental) DB ( 20x )
- Flashback Database Backup & Recovery Flashback Log (captures) Flashback Database to 2:05 PM Rewind button for the Database
Backup & Recovery (Human) Database Customer Order Database Flashback Database Flashback Log Table Flashback Table(SCN,TimeStamp) UNDO Flashback Drop (table, index) (Recycle bin) Row Flashback Rows Flashback Query
Oracle 10g (AWR) (ADDM) (ATO) (STA) SQL (ASM)
Oracle Database 10g 6% 50+% 3% ½ 3% 3% 25 %