Oracle Database 10g Automation Features

1. Oracle Database 10g Automation Features Dallas Oracle Users Group - Oct. 2005 Arun Kumar R. Cingular Wireless

2. Oracle Database 10g Automation Features Speaker Bio • Over a decade of database administration and architecture experience in Oracle technologies. • Author of Oracle Database 10g Insider Solutions, Sams Publishing, Aug. 2005 and Easy Oracle Automation, Rampant TechPress, Dec. 2004. • Columnist on Oracle Data Strategies in Database Trends and Applications Journal (www.dbta.com) • Executive Editor, IOUG Select Journal (www.ioug.org/select)

3. Agenda • Introduction • Oracle Database 10g • Automation Features • Grid Control • Security & Recoverability • Wrapping it up • Q & A

4. Introduction • Until Oracle 9i, DBAs spent a lot of time on database monitoring, identifying problem areas and performance bottlenecks • DBA tasks focused on • System Resources • Storage management • Space Management • Application and SQL tuning • Fixing Security Issues • Backup and Recovery Management.

5. Oracle Database 10g • Oracle Database 10g is more sophisticated and powerful, suitable for even small businesses. • Get an overview of all the major automatic features of Oracle Database 10g. • Review improved Enterprise Manager and Grid Control. • Learn how Database Administrators can easily automate many administrative routines. • Let us dive into the details.

6. Manageability Features- Overview • Automatic diagnosis of performance problems, potential or real, and subsequent correction • by Automatic Statistics Collection and Retention using the new Automatic Workload Repository. • Automatic Database Diagnostic Monitor (ADDM) • Automatic Shared Memory Management (ASMM) • Automatic Storage Management (ASM) • helps to add and remove storage as needed and improve the I/O on various disks • Automatic SQL Tuning features. • Better Data Movement, Security, Backup & Recoverability features

7. What is Automatic Workload Repository? • AWR, a built-in repository in every database is the central component of Oracle Database 10g manageability infrastructure. • Provides services to the database to access, collect, process, and maintain performance statistics on various functionalities within the database. • At regular intervals (60 minutes), Oracle Database 10g takes snapshots of all vital statistics and workload data • Stores them in the repository for a period of seven days.

8. AWR (Continued..) • Repository data used for analysis and as statistics for problem detection and self-tuning. • Used for system level and user level analysis. • AWR is the basis for all self-management functionalities of the database. • Gives the Oracle Database with the historical perspective on its usage. • AWR has report generation mechanism to produce summary reports based on database statistics. • Very similar to STATSPACK in previous database versions, but a lot convenient to use !

9. Architecture of AWR • AWR in Oracle Database 10g collects- • Time model statistics • Object statistics • Session and system statistics in v$sesstat and v$sysstat • Optimizer statistics

10. Active Session History • Active Session History (ASH) contains recent session activity. • Memory for ASH comes from the SGA, fixed for the instance lifetime. • ASH samples the v$session view every second and records the events the active sessions are waiting for. • ASH statistics can be viewed from v$active_session_history, one row for each active session per sample with the latest session rows first. • Contents of v$active_session_history flushed to disk as part of the AWR snapshot process.

11. AWR Snapshots • AWR stored in Workload Repository (WR) schema. • WR schema resides in the SYSAUX tablespace. • A snapshot is a set of performance statistics captured at a certain time in the database. • Used for computing the rate of change of a statistic. • Identified by unique snapshot sequence # (snap_id). • Generated every 60 minutes by default. • interval parameter to change snapshot frequency. • Manual snapshots can be taken using the database control or a PL/SQL procedure.

12. Metrics and Thresholds • Metrics are a set of statistics for certain system attributes. • Calculated and stored by the Automatic Workload Repository (AWR). • Results displayed in Enterprise Manager through the All Metrics page under Related Links on the Database Home page. • Thresholds are the boundary values against which the metric values are compared.

13. Statistics Collection Process • Oracle Database 10g metrics can be tracked using AWR. • Two categories of Usage metrics- Database feature usage and High water mark (HWM) value of certain database attributes. • MMON tracks and records the database feature usage and HWM statistics on a weekly basis. • Statistics are recorded in AWR snapshots. • To review statistics, dba_feature_usage_statistics view and dba_high_water_mark_statistics view or Enterprise Manager.

14. How to use AWR reports? • AWR reports are generated using awrrpt.sql, awrrpti.sql, awrddrpt.sql, and awrddrpi.sql in $ORACLE_HOME/rdbms/admin directory. • Reports as html pages or text reports. • awrrpt.sql - all available AWR snapshots. • awrrpti.sql - statistics for a range of snapshot ids on a specified database and instance • awrddrpt.sql - statistics of a particular SQL statement for a range of snapshot ids. • awrddrpi.sql - statistics of a SQL statement for a range of snapshot ids on a specified database and instance.

15. Using AWR reports -1

16. Using AWR reports -2

17. AWR reports for Comparisons

18. Advisory Framework Oracle Database 10g has server components called advisors to provide feedback about resource utilization and performance. • Automatic Database Diagnostic Monitor (ADDM) • SQL Tuning Advisor, SQL Access Advisor • SGA Advisor, PGA Advisor • Buffer Cache Advisor, Library Cache Advisor • Segment Advisor • Undo Advisor

19. Database Advisors • Above database advisors have certain attributes in common. • An advisor can be launched in one of two modes depending on time available for completing the advisory task. • Limited Mode or Comprehensive Mode • Some advisors support both modes.

20. Automatic Database Diagnostic Monitor (ADDM) • ADDM provides proactive and reactive features for monitoring • Statistical information is automatically captured from the SGA • Stored inside the workload repository in the form of snapshots in sixty (60) minute intervals. • Snapshots are then written to disk • Similar to STATSPACK snapshots, but are more detailed.

21. ADDM Performance Monitoring • ADDM initiates the MMON process to run automatically on every database instance • For every snapshot taken, ADDM triggers an analysis of the period corresponding to the last two snapshots. • Helps the ADDM to proactively monitor the instance and detect bottlenecks before they become catastrophic. • Analysis results are stored inside the workload repository. • Accessible through the EM console. • ADDM can be manually invoked using the runad PL/SQL procedure and by the $ORACLE_HOME/rdbms/admin/addmrpt.sql procedure.

22. Automatic Shared Memory Management (ASMM) • Commonly tuned System Global Area (SGA) components are - database buffer cache, the shared pool, the large pool, and the Java pool. • ASMM enables Oracle Database 10g to automatically determine appropriate values within total SGA limits. • DBA can simply mention the total amount of SGA memory to an instance using the sga_target . • Oracle database will automatically distribute this memory among various sub-components.

23. How does ASMM work? • With Automatic SGA memory management enabled, the sizes of different components are flexible to resize to the needs of the workload without additional intervention. • The internal tuning algorithm monitors the workload performance & increases the shared pool as needed to reduce the number of parses. • ASMM uses a new Memory Manager (MMAN) background process. • Coordinates the sizing of the memory components, acts as a memory broker. • Tracks all memory components & pending resize operations.

24. Enable ASMM - EM

25. Memory Advisor through EM -1 • Memory Advisor can be used only when the ASMM is disabled. • Three advisors - for Shared pool in SGA, Buffer Cache in SGA, and PGA.

26. Memory Advisor through EM -2

27. Automatic Storage Management • Automatic Storage Management (ASM) for efficient management of disk drives with 24/7 availability. • Helps the DBA from potentially managing thousands of database files across multiple database instances by creating disk groups. • Disk groups are comprised of disks and files on them. • ASM will not eliminate any existing database functionalities • With ASM, the DBA only needs to manage a smaller number of disk groups. • ASM also serves as a cluster file system for RAC configurations.

28. ASM Advantages • In a nutshell, we can have a mixture of ASM files; Oracle managed files and manually managed files • Existing files can be migrated to ASM if needed. • ASM prevents accidental file deletion by eliminating the file system interface. • Provides raw disk I/O performance for all files, striping them across multiple storage arrays. • Reduces the cost of managing storage with a clustered volume manager and integrated file system functionality.

29. ASM vs. Oracle 9i Storage

30. Automatic SQL Tuning Features SQL Tuning Advisor and SQLAccess Advisor • SQL Tuning Advisor provides tuning advice for SQL statements with out modifying any statement. • For complex applications and large databases, use SQLAccess Advisor. • Provides advice on indexes, materialized views and materialized view logs for a given work load. • Provides advice on database schema issues and determines optimal data access paths.

31. Using Automatic SQL Tuning tools • For both tuning tools- create a task, run the Advisor, generate the recommendations and implement them. • The user can accept or reject the recommendations. • Advisor tools available through Oracle Enterprise Manager 10g (OEM) and from SQL command prompt. • Helps the DBA to save time in application SQL tuning efforts over methods used with Oracle 9i and prior.

32. SQLAccess Advisor • Used for applications with complex queries on large sets of data. • Recommend a combination of indexes, materialized views, and materialized view logs. • Recommends how to optimize materialized views to be refreshable and benefit from general query rewrites. • Recommends the use of bitmap and B-tree indexes. • B-tree indexes are used mainly in data warehouses or large databases to index unique or near-unique keys. • Bitmap indexes improve response time for adhoc queries. • Very little space compared to conventional indexes.

33. SQL Tuning Advisor • SQL Tuning Advisor provides automatic tuning advice for SQL statements. • Takes one or more SQL statements as input and invokes the automatic tuning optimizer to perform SQL tuning with out actually modifying any statement. • Output is a series of advice or recommendations along with the rationale behind each recommendation and its expected benefits. • The user can accept the recommendations or reject them.

34. SQL Tuning Advisor - STS • For tuning multiple SQL statements, a SQL Tuning Set (STS) is created. • STS can be created from command line or Oracle Enterprise Manager 10g. • SQL Tuning Sets can be handled through EM or managed with DBMS_SQLTUNE package procedures. • For using SQL Tuning Set APIs, the developer needs ADMINISTER ANY SQL TUNING SET system privilege.

35. Controlling a Tuning Task • Control the scope and duration of a tuning task • Scope of the task -limited or comprehensive. • With the limited option, the SQL Tuning Advisor produces recommendations based on analysis of statistics, access paths, and SQL structure. • SQL Profile recommendations are not generated. • With comprehensive option, the SQL Tuning Advisor does everything under limited scope and SQL profiling. User can also set the time limit of a tuning task (default value 30 minutes).

36. Deploying SQL Tuning Advisor

37. SQL Tuning Advisor from EM • In EM, using Advisor Central- SQL Tuning Advisor link. • DBMS_SQLTUNE package by anyone with DBA role and ADVISOR privilege.

38. SQL Tuning Advisor using EM-1 • In EM, SQL Tuning Advisor-> Top SQL link.

39. SQL Tuning Advisor using EM- 2 • Choose the SQL & click Run SQL Tuning Advisor

40. SQL Tuning Advisor using EM-3 • Select Scope • Choose • Schedule

41. SQL Tuning Advisor using EM-4 • When the SQL Tuning task’s status is COMPLETED, select the task and click View Recommendations.

42. SQLAccess Advisor SQLAccess Advisor • Will recommend a combination of indexes, materialized views, and materialized view logs. • Can be run from EM using the SQLAccess Advisor Wizard or by invoking the DBMS_ADVISOR package. SQL Tuning with SQLAccess Advisor • 4 steps->create a task, define workload, generate recommendations, & implement them. • A task has all the information relating to the recommendation process and its results. • To create a task, use the wizard in EM or the DBMS_ADVISOR.QUICK_TUNE. • For hands-on approach, use DBMS_ADVISOR.CREATE_TASK procedure.

43. Executing the Task • Create a task and link to a workload. • Use the DBMS_ADVISOR.EXECUTE_TASK procedure to generate the recommendations. • To view the recommendations, use • DBA_ ADVISOR_RECOMMENDATIONS • USER_ADVISOR_RECOMMENDATIONS • Get a script using DBMS_ADVISOR.GET_TASK_SCRIPT • EM to get recommendations. • Recommendations are stored in the SQLAccess Advisor repository. • Repository has benefits being managed by the server, support of historical data etc.

44. Implementing SQLAccess Advisor Recommendations • SQL Access Advisor creates a set of recommendations. • Lets the user decide whether to implement all of its recommendations, or some, or none. • In EM - Select the required recommendations. • You are given a choice on how to implement the recommendations. • Schedule a job in the EM to execute the script and implement the recommendations. • For do-it-yourself DBAs • SQLAccess Advisor generates a SQL script to be used any time. • DBA can edit SQL, naming conventions etc.

45. Advisor Task Implementation-EM-1

46. Advisor Task Implementation-EM-2

47. Advisor Task Implementation-EM-3

48. Advisor Task Implementation-EM-4

49. Deploying SQLAccess Advisor

50. Uses for SQLAccess Advisor • Generally used in large database/data warehouse environments. • Advantages • DBA can tune parts of the database using selective workloads. • Quick and easy to use tool, which helps to build a pattern of usage over a time frame. • DBA gets to decide and select the recommendations to be implemented. • Identify parts of the system which needs tuning, that would have been left out by ordinary tuning procedures.