Oracle Database 10g: Self-Managing Database DB TSC

Size: px

Start display at page:

Download "Oracle Database 10g: Self-Managing Database DB TSC"

준기 우
5 years ago
Views:

2 Oracle Database 10g: Self-Managing Database DB TSC

3 Agenda Overview System Resource Application & SQL Storage Space Backup & Recovery

4 ½ Cost

5 ? 6% 12 % 6% 6% 55% : IOUG 2001 DBA Survey

6 ? 6% & 12 % 6% 6% 55% : IOUG 2001 DBA Survey

7 CD CPU,, OS,,CPU database, listeners, agent, OMS, OID

8 (Seed) ( 1 CD, 20 ) initsid.ora 90% ( < 30 ) GUI Database Upgrade Assistant (DBUA) Oracle 8i / 9i 111 steps! Oracle 10g 6 steps!

9 ? 6% & 12 % 6% 6% 55% : IOUG 2001 DBA Survey

10 Oracle Database 10g Data Pump Oracle9i Export 60% Oracle9i Import 15 ~ 20 Export Transportable Tablespace

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

12 ? 6% & 12 % 6% 6% 55% : IOUG 2001 DBA Survey

13 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

14 Oracle Database 10g Now Self-Managing Database Application & SQL Storage System Resource UI Backup & Recovery Database Intelligent Infrastructure Space

15 (Intelligent Infrastructure ) Automated Tasks (Automatic Workload Repository) Server Alerts Advisory Framework (Automatic Maintenance Tasks) Automatic Workload Repository (Server-generated Alerts) (Advisory Infrastructure)

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

17 (Automatic Database Diagnostic Monitor), ADDM finds top problems

18 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

19 ADDM - EM DB

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

21 SGA System Resource SGA DB_CACHE_SIZE SHARED_POOL_SIZE LARGE_POOL_SIZE JAVA_POOL_SIZE (enable) Automatic Shared Memory SGA_TARGET

22 - SQL Application & SQL SQL? SQL? SQL Workload DBA SQL DBA

23 Oracle 10g Application & SQL SQL!!!! ADDM SQL DBA SQL Workload SQL Tuning Advisor

24 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

25 (Automatic Storage ) Storage 3 rd Party ASM DB / (rebalancing)

26 10g ASM Storage 10g ASM Tables Tablespace Files File System Logical Vol Disks Group Tables Tablespace Files File System Logical Vol Disk Group Oracle10g ASM.

27 ASM -EM Storage (Rebalancing)

28 ASM Storage I/O / DBA

29 Space, Capacity Planning 1st Qtr 2nd Qtr 3rd Qtr 4th Qtr Server- Generat ed Alerts,

30 Space, (Shrink) Capacity Planning Server- Generated Alerts Online Segment Shrink st Qtr 2nd Qtr 3rd Qtr 4th Qtr

31 Space Data Unused Space Data Unused Space HWM (Shrink) ALTER TABLE SHRINK SPACE [COMPACT]; HWM

32 Backup & Recovery (Flash Recovery Area) Database Area Flash Recovery Area 2 Recovery Area (incremental) DB ( 20x )

33 - Flashback Database Backup & Recovery Flashback Log (captures) Flashback Database to 2:05 PM Rewind button for the Database

34 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

35 Oracle 10g (AWR) (ADDM) (ATO) (STA) SQL (ASM)

36 Oracle Database 10g 6% 50+% 3% ½ 3% 3% 25 %

Jerry Held

Jerry Held DB / TSC Oracle Database 10g (Self-Managing Database) (Common Infrastructure) (Automatic Workload Repository) (Server-generated Alerts) (Automated Maintenance Tasks) (Advisory Framework) (ADDM) (Self-Managing