DB / TSC
Oracle Database 10g
(Self-Managing Database) (Common Infrastructure) (Automatic Workload Repository) (Server-generated Alerts) (Automated Maintenance Tasks) (Advisory Framework) (ADDM) (Self-Managing Components)
(Increased Efficiency) (Lower Costs),
Oracle Database 10g : Enterprise Manager SQL
(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
(Automatic Workload Repository: AWR) STATISTICS_LEVEL = TYPICAL or ALL EM SQL*Plus (SGA) Fixed Dictionary view view MMON Workload Repository ADDM
AWR Snapshot DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS ( retention IN NUMBER DEFAULT NULL, interval IN NUMBER DEFAULT NULL);
AWR snapshot AWR SLA AWR (Baseline)
AWR DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE ( - start_snap_id IN NUMBER, end_snap_id IN NUMBER, baseline_name IN VARCHAR2);
AWR SQL> @?/rdbms/admin/awrrpt Report_type: html Num_days: 2 Begin_snap: 150 End_snap: 160 Report_name:
AWR Oracle Database 10g AWR Oracle9i / AWR (Oracle9i ) Metric OS Oracle Database 10g AWR
Metric : physical reads Metric : Physical Reads Per Sec, Physical Reads Per Txn
Metric Event, File, Service, Session, System, Tablespace : System: 15 60 V$METRICNAME : V$xxxMETRIC WR : DBA_HIST_xxxMETRIC_HISTORY
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
(Server-generated Alerts) Oracle (SGA) Oracle MMON queue push SYS.ALERT_QUE Third party AWR
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
EM
DBMS_SERVER_ALERT GET_THRESHOLD SET_THRESHOLD
(Automated Maintenance Tasks) DBMS_SCHEDULER - DBMS_JOBS Enabled Consumer group Job class Resource plan JOB Management window Window group Disabled
EM
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
(Advisory Framework) Automatic Database Diagnostic Monitor SQL Tuning Memory Access PGA SGA Buffer Cache Shared Pool (ADDM) Space Segment Advisor Undo AWR, DBMS_ADVISOR
AWR Snapshot MMON Advisor Wizard DBA / interruptible
(ADDM) A utomatic D atabase D iagnostic M onitor
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)
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
ADDM
ADDM 1 2 3
ADDM
DBA ADDM
(Self-Managing Components)
SGA SGA (ORA-4031) Buffer Cache Buffer Cache Large Pool SQL Cache Java Pool Large Pool SQL Cache Java Pool SGA Pool
SGA MMAN MMAN MMAN Buffer Cache Advisor Shared Pool Advisor SPFILE SGA
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
DB_CACHE_SIZE, SHARED_POOL_SIZE, LARGE_POOL_SIZE, JAVA_POOL_SIZE 0 0, SGA KEEP RECYCLE,,, Streams POOL
EM
UNDO Oracle10g MMON UNDO Undo Advisor AWR Long running query ORA-1555 UNDO MAX_ROLLBACK_SEGMENTS UNDO_SUPPRESS_ERRORS
UNDO Proactive UNDO 30 Reactive UNDO Extent UNDO UNDO_RETENTION 15
UNDO UNDO Warning : 85% Critical : 97% UNDO_RETENTION UNDO FAST Ramp-Up AWR UNDO, UNDO AWR UNDO
UNDO EM
UNDO EM
Segment AWR Segment Shrink Segment Advisor shrink.,. Growth Trend Report DBA. Proactive Tablespace Monitoring. New Segment Resource Estimation,.
Segment Shrink I/O (Full Table Scan) (Compact Tree) In-place : Object Online Shrink insert/delete ROWID.
Segment Shrink: 2 ALTER TABLE employees SHRINK SPACE COMPACT; 1 HWM HWM ALTER TABLE employees SHRINK SPACE; 2 HWM
Segment Advisor EM
EM AWR Space Advisor
EM
(Self-Managing Database) (Common Infrastructure) (ADDM) (Self-Managing Components)