Jerry Held

Size: px

Start display at page:

Download "Jerry Held"

미진 판
5 years ago
Views:

2 DB / TSC

3 Oracle Database 10g

4 (Self-Managing Database) (Common Infrastructure) (Automatic Workload Repository) (Server-generated Alerts) (Automated Maintenance Tasks) (Advisory Framework) (ADDM) (Self-Managing Components)

5 (Increased Efficiency) (Lower Costs),

6 Oracle Database 10g : Enterprise Manager SQL

7 (Common Infrastructure) Automatic Application Workload & SQL Repository Management Storage Management Server-generated Alert Advisory Intelligent Framework Infrastructure System Resource Management Automatic Maintenance Tasks Backup & Recovery Space Database Management Management Management (Automatic Workload Repository) / (Server-generated Alerts) (Automated Maintenance Tasks) (Advisory Framework) DBA

8 (Automatic Workload Repository: AWR) STATISTICS_LEVEL = TYPICAL or ALL EM SQL*Plus (SGA) Fixed Dictionary view view MMON Workload Repository ADDM

9 AWR Snapshot DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS ( retention IN NUMBER DEFAULT NULL, interval IN NUMBER DEFAULT NULL);

10 AWR snapshot AWR SLA AWR (Baseline)

11 AWR DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE ( - start_snap_id IN NUMBER, end_snap_id IN NUMBER, baseline_name IN VARCHAR2);

12 AWR Report_type: html Num_days: 2 Begin_snap: 150 End_snap: 160 Report_name:

13 AWR Oracle Database 10g AWR Oracle9i / AWR (Oracle9i ) Metric OS Oracle Database 10g AWR

14 Metric : physical reads Metric : Physical Reads Per Sec, Physical Reads Per Txn

15 Metric Event, File, Service, Session, System, Tablespace : System: V$METRICNAME : V$xxxMETRIC WR : DBA_HIST_xxxMETRIC_HISTORY

16 AWR Oracle10g : Response Time = Service Time + Wait Time V$SESS_TIME_MODEL, V$SYS_TIME_MODEL DB Time, DB CPU, OS V$OSSTAT BUSY_TICKS, IDLE_TICKS,, High Water Mark

17 (Server-generated Alerts) Oracle (SGA) Oracle MMON queue push SYS.ALERT_QUE Third party AWR

18 V$ALERT_TYPE Metric (Critical) (Warning) MMON DBA_OUTSTANDING_ALERTS DBA_ALERT_HISTORY Snapshot Too Old Alert Resumable Session Suspended Recovery Area Low On Free Space

19 EM

20 DBMS_SERVER_ALERT GET_THRESHOLD SET_THRESHOLD

21 (Automated Maintenance Tasks) DBMS_SCHEDULER - DBMS_JOBS Enabled Consumer group Job class Resource plan JOB Management window Window group Disabled

22 EM

23 DBMS_SCHEDULER.CREATE_JOB( job_name => 'table_reorg', job_type => 'STORED_PROCEDURE', job_action => 'mypack.tab_reorg_job_proc', job_class => 'AUTO_TASKS_JOB_CLASS', schedule_name => 'WEEKEND_WINDOW'); Statistics collection WEEKEND_WINDOW Table Reorganization 12 a.m. 12 a.m. 10 p.m. 6 a.m. WEEKNIGHT_WINDOW

24 (Advisory Framework) Automatic Database Diagnostic Monitor SQL Tuning Memory Access PGA SGA Buffer Cache Shared Pool (ADDM) Space Segment Advisor Undo AWR, DBMS_ADVISOR

25 AWR Snapshot MMON Advisor Wizard DBA / interruptible

26 (ADDM) A utomatic D atabase D iagnostic M onitor

27 ADDM : Response Time = Service Time + Wait Time Response Time Service Time System Wait RAC Waits Concurrency Buffer Busy Parse Latches Buffer Cache latches (Symptom) IO Waits Top-down (Root Cause)

28 ADDM Excessive logon/logoff Memory undersizing STATSPACK Hot blocks and objects w/sql RAC service issues Locks and ITL contention Checkpointing causes PL/SQL, Java time Top SQL I/O issues Parsing Configuration issues Application usage ADDM

29 ADDM

30 ADDM 1 2 3

31 ADDM

32 DBA ADDM

33 (Self-Managing Components)

34 SGA SGA (ORA-4031) Buffer Cache Buffer Cache Large Pool SQL Cache Java Pool Large Pool SQL Cache Java Pool SGA Pool

35 SGA MMAN MMAN MMAN Buffer Cache Advisor Shared Pool Advisor SPFILE SGA

36 SGA DB_CACHE_SIZE SHARED_POOL_SIZE LARGE_POOL_SIZE JAVA_POOL_SIZE Total SGA size SGA_TARGET Shared Pool Database Buffer Cache Redo Log Buffer SGA_TARGET = 8G Java Pool Fixed SGA Large Pool SGA = 8 GB STATISTICS_LEVEL = TYPICAL or ALL

37 DB_CACHE_SIZE, SHARED_POOL_SIZE, LARGE_POOL_SIZE, JAVA_POOL_SIZE 0 0, SGA KEEP RECYCLE,,, Streams POOL

38 EM

39 UNDO Oracle10g MMON UNDO Undo Advisor AWR Long running query ORA-1555 UNDO MAX_ROLLBACK_SEGMENTS UNDO_SUPPRESS_ERRORS

40 UNDO Proactive UNDO 30 Reactive UNDO Extent UNDO UNDO_RETENTION 15

41 UNDO UNDO Warning : 85% Critical : 97% UNDO_RETENTION UNDO FAST Ramp-Up AWR UNDO, UNDO AWR UNDO

42 UNDO EM

43 UNDO EM

44 Segment AWR Segment Shrink Segment Advisor shrink.,. Growth Trend Report DBA. Proactive Tablespace Monitoring. New Segment Resource Estimation,.

45 Segment Shrink I/O (Full Table Scan) (Compact Tree) In-place : Object Online Shrink insert/delete ROWID.

46 Segment Shrink: 2 ALTER TABLE employees SHRINK SPACE COMPACT; 1 HWM HWM ALTER TABLE employees SHRINK SPACE; 2 HWM

47 Segment Advisor EM

48 EM AWR Space Advisor

49 EM

50 (Self-Managing Database) (Common Infrastructure) (ADDM) (Self-Managing Components)

The Self-Managing Database : Automatic Health Monitoring and Alerting

The Self-Managing Database : Automatic Health Monitoring and Alerting Agenda Oracle 10g Enterpirse Manager Oracle 10g 3 rd Party PL/SQL API Summary (Self-Managing Database) ? 6% 6% 12% 55% 6% Source: IOUG