1 / 27

Automatic Management

Automatic Management. Objectives. After completing this lesson, you should be able to: Describe the various tools used to diagnose database performance issues Access the database advisors Use the SQL Tuning Advisor to improve database performance. Oracle Wait Events.

arthur-diaz
Download Presentation

Automatic Management

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Automatic Management

  2. Objectives • After completing this lesson, you should be able to: • Describe the various tools used to diagnose database performance issues • Access the database advisors • Use the SQL Tuning Advisor to improve database performance

  3. Oracle Wait Events • A collection of wait events provides informationon the sessions or processes that had to wait or must wait for different reasons. • These events are listed in the V$EVENT_NAME view

  4. System Statistics V$SYSSTAT • statistic# • name • class • value • stat_id V$SYSTEM_WAIT_CLASS • wait_class_id • wait_class# • wait_class • total_waits • time_waited V$SGASTAT • pool • name • bytes V$EVENT_NAME • event_number • event_id • name • parameter1 • parameter2 • parameter3 • wait_class V$SYSTEM_EVENT • event • total_waits • total_timeouts • time_waited • average_wait • time_waited_micro

  5. Displaying Session-Related Statistics V$STATNAME • statistic# • name • class V$SESSTAT • sid • statistic# • value V$SESSION • sid • serial# • username • command • osuser • seq# • event# • event • p1/2/3text • p1/2/3 • p1/2/3raw • wait_class • wait_time • seconds_in_wait • state • ... V$SESSION_EVENT • sid • event • total_waits • total_timeouts • time_waited • average_wait • max_wait • time_waited_micro • event_id V$EVENT_NAME • event# • name • parameter1 • parameter2 • parameter3

  6. Troubleshooting and Tuning Views Instance/Database V$DATABASE V$INSTANCE V$PARAMETER V$SPPARAMETER V$SYSTEM_PARAMETER V$PROCESS V$BGPROCESS V$PX_PROCESS_SYSSTAT V$SYSTEM_EVENT Disk V$DATAFILE V$FILESTAT V$LOG V$LOG_HISTORY V$DBFILE V$TEMPFILE V$TEMPSEG_USAGE V$SEGMENT_STATISTICS Contention V$LOCK V$UNDOSTAT V$WAITSTAT V$LATCH Memory V$BUFFER_POOL_STATISTICS V$LIBRARYCACHE V$SGAINFO V$PGASTAT

  7. Statistics Collection • There are different types of statistics: • Optimizer statistics • System statistics • There are different methods of collecting statistics: • Automatically through GATHER_STATS_JOB • Manually with the DBMS_STATS package • By setting database initialization parameters • Importing statistics from another database

  8. Automatic Optimizer Statistics Collection: Overview • Oracle8i Database provides DBMS_STATS package: • DBA determines how to gather statistics. • DBA determines when to gather statistics. • Oracle9i Database determines how to gather statistics: • Statistics can be gathered using a single command. • DBA determines when to gather statistics. • Oracle Database 10g fully automates statistics gathering: • DBA no longer needs to gather statistics. • Table monitoring is used by default.

  9. Dictionary and Special Views • The following dictionary and special views provide useful statistics after using the DBMS_STATS package: • DBA_TABLES, DBA_TAB_COLUMNS • DBA_CLUSTERS • DBA_INDEXES, INDEX_STATS • INDEX_HISTOGRAM, DBA_TAB_HISTOGRAMS • This statistical information is static until you reexecute the appropriate procedures in DBMS_STATS.

  10. Statspack • Execute statspack.snap to collect statistics. • Automate the collection of statistics using the spauto.sql script. • Produce a report using the spreport.sql script. • Set TIMED_STATISTICS to TRUE to collect timing information.

  11. Workload Repository MMON ADDM finds top problems SYSAUX SGA 6:00 a.m. Snapshot 1 In-memorystatistics 7:00 a.m. Snapshot 2 8:00 a.m. Snapshot 3 9:00 a.m. Snapshot 4 9:30 a.m.

  12. AWR Snapshot Baselines Relevant periodin the past DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE ( - start_snap_id IN NUMBER , end_snap_id IN NUMBER , baseline_name IN VARCHAR2);

  13. Advisory Framework Overview PGA Advisor PGA SQL Tuning Advisor Buffer CacheAdvisor Memory SGA ADDM SQL Access Advisor Library CacheAdvisor Segment Advisor Space Undo Advisor

  14. Database Control and Advisors

  15. Typical Advisor Tuning Session Create an advisor task. Adjust task parameters. Perform analysis. Acceptresults? No Yes Implementrecommendations.

  16. Manually Invoking ADDM

  17. Application Tuning Challenges I can doit for you! SQL workload How can Itune myhigh-loadSQL? ADDM SQL Tuning Advisor High-loadSQL DBA

  18. SQL Tuning Advisor Overview Comprehensive SQL Tuning Automatic Tuning Optimizer Statistics Check Optimization Mode Detect Stale/Missing Statistics Plan Tuning Optimization Mode Plan Tuning (SQL Profile) Add Missing Index Run Access Advisor Access Analysis Optimization Mode SQL Tuning Advisor SQL Analysis Optimization Mode Restructure SQL

  19. SQL Tuning Advisor Options and Recommendations

  20. Using the SQL Tuning Advisor • Use the SQL Tuning Advisor to analyze SQL statements and obtain performance recommendations. • Sources for SQL Tuning Advisor to analyze: • Top SQL: Analyzes the top SQL statements currently active • SQL Tuning Sets: Analyzes a set of SQL statements you provide • Snapshots: Analyzes a snapshot • Baselines: Analyzes a baseline

  21. Using the SQL Tuning Advisor: Example

  22. Using the SQL Access Advisor

  23. The Undo Management Page

  24. Automatic Undo Retention Tuning • Proactive tuning: • Undo retention is tuned for longest-running query. • Query duration information is collected every 30 seconds. • Reactive tuning: • Undo retention is gradually lowered under space pressure. • Oldest unexpired extents are used first. • Undo retention never goes below either UNDO_RETENTION or 15 minutes (whichever is less). • Enabled by default

  25. The Undo Advisor Page

  26. Summary • In this lesson, you should have learned how to: • Describe the various tools used to diagnose database performance issues • Access the database advisors • Use the SQL Tuning Advisor to improve database performance

  27. Practice 12 Overview:Optimizing Database Performance • This practice covers proactively tuning your database using ADDM.

More Related