# OCA: Oracle Database 11g Administrator Certified Associate Study Guide- P18

Chia sẻ: Thanh Cong | Ngày: | Loại File: PDF | Số trang:50

0
60
lượt xem
16

## OCA: Oracle Database 11g Administrator Certified Associate Study Guide- P18

Mô tả tài liệu
Download Vui lòng tải xuống để xem tài liệu đầy đủ

OCA: Oracle Database 11g Administrator Certified Associate Study Guide- P18: There is high demand for professionals in the information technology (IT) industry, and Oracle certifications are the hottest credential in the database world. You have made the right decision to pursue certification, because being Oracle Database 11g certified will give you a distinct advantage in this highly competitive market.

Chủ đề:

Bình luận(0)

Lưu

## Nội dung Text: OCA: Oracle Database 11g Administrator Certified Associate Study Guide- P18

1. Proactive Database Maintenance 781 EXTSTAT -------------------------------- SYS_STUZVS6GX30A0GN_5YRYSD2LPM SQL> SQL> exec dbms_stats.gather_table_stats(null, ‘customers’, method_opt=>’for all columns size skewonly’); PL/SQL procedure successfully completed. SQL> select column_name, num_distinct, histogram 2 from user_tab_col_statistics 3* where table_name = ‘CUSTOMERS’ SQL> / COLUMN_NAME NUM_DISTINCT HISTOGRAM ------------------------------ ------------ --------------- CUST_NAME 47692 HEIGHT BALANCED CUST_STATE 6 FREQUENCY CUST_COUNTRY 3 FREQUENCY SYS_STUZVS6GX30A0GN_5YRYSD2LPM 8 FREQUENCY SQL> select * from customers where cust_country = ‘India’ and cust_state = ‘TN’; ------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 86 | 2580 | 137 (1)| 00:00:02 | |* 1 | TABLE ACCESS FULL| CUSTOMERS | 86 | 2580 | 137 (1)| 00:00:02 | ------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(“CUST_STATE”=’TN’ AND “CUST_COUNTRY”=’India’) As you can see in the example, before extended statistics were collected, the estimated number of rows was 1447, whereas after the extended statistics collection, the number of rows optimizer estimated to return is 86.
2. 782 Chapter 14 N Maintaining the Database and Managing Performance To drop the extend statistics, use the DROP_EXTENDED_STATISTICS procedure: SQL> exec dbms_stats.drop_extended_stats(null,’CUSTOMERS’, ‘(CUST_COUNTRY, CUST_STATE)’); PL/SQL procedure successfully completed. SQL> To define the extension and collect statistics in one step, you can do the following: SQL> exec dbms_stats.gather_table_stats(null, ‘customers’, method_opt=>’for all columns size skewonly for columns (cust_country, cust_state)’); PL/SQL procedure successfully completed. SQL> select extension_name, extension from user_stat_extensions 2 where table_name = ‘CUSTOMERS’; EXTENSION_NAME EXTENSION ------------------------------------ ----------------------------- SYS_STUZVS6GX30A0GN_5YRYSD2LPM (“CUST_COUNTRY”,”CUST_STATE”) In the next section, you’ll learn to enable and disable the automatic statistics collection as well as perform other AutoTask jobs. Configuring Automated Maintenance Tasks Using EM The following are three default automated maintenance tasks: NÛ Gathering optimizer statistics NÛ Running the Segment Advisor NÛ Running the SQL Tuning Advisor You can also enable and disable the AutoTask jobs using EM Grid Control. On the Server tab, choose Automated Maintenance Tasks under Oracle Scheduler. Figure 14.8 shows the Automated Maintenance Tasks screen. By clicking the Configure button, you can enable or disable the default AutoTask jobs, as well as adjust the days on which these tasks are run, as shown in Figure 14.9. To learn more about Automated Maintenance Tasks and Oracle Scheduler, read the “Oracle Database Administrator’s Guide 11g Release 1 (11.1) Part Number B28310-04” Oracle documentation.
3. Proactive Database Maintenance 783 F i g u r e 14 . 8 Automated Maintenance Tasks screen F i g u r e 14 . 9 Configure Automated Maintenance Tasks screen
4. 784 Chapter 14 N Maintaining the Database and Managing Performance Gathering Performance Statistics Oracle Database generates several performance statistics that are used for self-tuning purposes and are available for administrators to better tune the database. Most of the performance statistics information is available through V$dictionary views (also known as dynamic performance views). The information in the V$ views are not persistent; that is, informa- tion is lost when the database is shut down. Automatic Workload Repository (AWR) saves the performance information in system tables and is made available for analysis through EM Database Control and other third-party tools. AWR information is persistent across database shutdowns. The AWR data is captured at a system or database level, and session-level information is captured using another mechanism called the Active Session History (ASH). You will learn about AWR and ASH in the following sections. Using Automatic Workload Repository Two background processes are responsible for collecting the performance statistics: Memory Monitor (MMON) and Memory Monitor Light (MMNL). These processes work together to collect performance statistics directly from the system global area (SGA). The MMON process does most of the work by waking up every 60 minutes and gathering statistical information from the data dictionary views, dynamic performance views, and optimizer and then storing this information in the database. The tables that store these sta- tistics are the Automatic Workload Repository. These tables are owned by the user SYSMAN and are stored in the SYSAUX tablespace. To activate the AWR feature, you must set the pfile/spfile’s parameter STATISTICS_LEVEL to the appropriate value. The values assigned to this parameter determine the depth of the statistics that the MMON process gathers. Table 14.3 shows the values that can be assigned to the STATISTICS_LEVEL parameter. ta b l e 1 4 . 3 Specifying Statistics Collection Levels Collection Level Description BASIC Disables the AWR and most other diagnostic monitoring and advisory activities. Few database statistics are gathered at each collection inter- val when operating the instance in this mode. TYPICAL Activates the standard level of collection activity. This is the default value for AWR and is appropriate for most environments. ALL Captures all the statistics gathered by the TYPICAL collection level, plus the execution plans and timing information from the operating system.
5. Proactive Database Maintenance 785 Once gathered, the statistics are stored in the AWR for a default duration of eight days. However, you can modify both the frequency of the snapshots and the duration for which they are saved in the AWR. One way to modify these intervals is by using the Oracle-supplied package DBMS_WORKLOAD_REPOSITORY. The following SQL command shows the DBMS_WORKLOAD_ REPOSITORY package being used to change the AWR collection interval to 1 hour and the retention period to 30 days: SQL> execute dbms_workload_repository.modify_snapshot_settings (interval=>60,retention=>43200); PL/SQL procedure successfully completed. The 30-day retention value shown here is expressed in minutes: 60 minutes per hour × 24 hours per day × 30 days = 43,200 minutes. You can also change the AWR collection interval, retention period, and collection depth using EM Database Control. Choose the Server tab, and click Automatic Workload Reposi- tory under Statistics Management (see Figure 14.10). F i g u r e 1 4 .1 0 AWR statistics collection and retention using EM Click the Edit button to change the settings, as shown in Figure 14.11.
6. 786 Chapter 14 N Maintaining the Database and Managing Performance F i g u r e 1 4 .11 Changing AWR statistics collection and retention using EM In Figure 14.11, the retention period for statistics gathered by the MMON process is set to 15 days, and statistics are collected every 30 minutes. You can also modify the depth at which statistics are collected by the AWR by clicking the Collection Level link. Clicking this link opens the Initialization Parameters screen where you can specify any of the three predefined collection levels shown in Table 14.3. Figure 14.12 shows the AWR collection level being changed from TYPICAL to ALL. F i g u r e 1 4 .1 2 Changing the AWR statistics collection level
7. Proactive Database Maintenance 787 Take care when specifying the AWR statistics collection interval. Gath- ering snapshots too frequently requires additional space in the SYSAUX tablespace and adds database overhead each time the statistics are col- lected. AWR does not use any space in the SGA. Using EM Database Control, you can view the AWR report. Click the Run AWR Report but- ton on the Automatic Workload Repository screen shown earlier in Figure 14.10. You can get the same report using SQL*Plus by running the script $ORACLE_HOME/rdbms/admin/awrrpt.sql. You can manage the AWR snapshots with SQL*Plus by utilizing the DBMS_WORKLOAD_ REPOSITORY package, as described in the next section. Managing AWR Snapshots Manually You can create AWR snapshots by using the CREATE_SNAPSHOT procedure, as shown here: SQL> EXECUTE DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT (); PL/SQL procedure successfully completed. SQL> You can use the DROP_SNAPSHOT_RANGE procedure to delete a range of snapshots, and you can query valid snapshot IDs from the DBA_HIST_SNAPSHOT view. The following example shows how to query the DBA_HIST_SNAPSHOT view: SQL> SELECT snap_id, begin_interval_time, end_interval_time 2 FROM dba_hist_snapshot 3 ORDER BY snap_id; SNAP_ID BEGIN_INTERVAL_TIME END_INTERVAL_TIME ---------- ------------------------------ ------------------------------ 1 24-SEP-08 02.06.11.000 AM 24-SEP-08 03.00.14.156 AM 2 25-SEP-08 12.06.26.000 AM 25-SEP-08 12.17.55.437 AM 3 25-SEP-08 12.17.55.437 AM 25-SEP-08 01.00.51.296 AM 4 25-SEP-08 01.00.51.296 AM 25-SEP-08 02.00.22.109 AM … … … 27 27-SEP-08 07.03.17.375 PM 29-SEP-08 04.03.47.687 AM 28 29-SEP-08 04.03.47.687 AM 29-SEP-08 05.00.39.437 AM 29 29-SEP-08 05.00.39.437 AM 29-SEP-08 05.42.13.718 AM To delete snapshots in the range 5–15, you can execute the following code. Note that the ASH (discussed in the next section) data is also purged between the time periods specified by the snapshot range. SQL> BEGIN 2 DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE (5, 15); 3 END; 4 / PL/SQL procedure successfully completed. SQL> 8. 788 Chapter 14 N Maintaining the Database and Managing Performance Once AWR snapshots are taken and stored in the database, the Automatic Database Diagnostic feature uses the AWR data, as described in the “Automatic Database Diagnostic Monitoring” section. Active Session History ASH is sampled data at specified intervals from the current state of all active sessions. The data is collected in memory and can be accessed by V$ views. The ASH information is also written to a persistent store by the AWR snapshots. The V$ACTIVE_SESSION_HISTORY provides the information collected by the ASH sampler. The sessions are sampled every second and are stored in a circular buffer in SGA. Each session is stored as a row. The current and historical information is available in the data dictionary view DBA_HIST_ACTIVE_SESS_HISTORY. ASH information also includes the execu- tion plan for each SQL captured. Oracle provides a script to generate an ASH report,$ORACLE_HOME/rdbms/admin/ ashrpt.sql. You will be prompted for the report type (HTML or text), the begin time in minutes prior to SYSDATE, the duration in minutes for the report, and a name for the report. You can also use EM Database Control to generate the ASH report. On the EM Database Control home screen, click the Performance tab, and click the Run ASH Report button, as shown in Figure 14.13. F i g u r e 1 4 .1 3 Performance screen in EM Database Control
9. Proactive Database Maintenance 789 The screen shown in Figure 14.14 captures parameters for the ASH report. Specify the start time and end time for the report, and click the Generate Report button. F i g u r e 1 4 .1 4 ASH report parameters Any session that is connected to the database and does not wait for a wait event that belongs to the idle wait class is considered as an active session. AWR Baselines It is a good practice to baseline your database when everything is working as expected. When things go south, you can use this baseline to compare system statistics and perfor- mance metrics. AWR baselines contain performance data from a specific time period that is preserved for comparison when problems occur. This baseline data is excluded from the AWR purging process. You can create two types of baselines: a single baseline and a repeating baseline. A single baseline is captured at a single fixed-time interval, such as October 5 between 10 a.m. and 1 p.m. A repeating baseline repeats during a time interval for a specific period, such as every Friday between 10 a.m. and 1 p.m. You can create and delete AWR baselines using EM Database Control or SQL*Plus. Managing AWR Baselines Using SQL*Plus To create a single baseline, use the CREATE_BASELINE procedure as shown in the following code. You can include the optional expiration parameter to automatically delete the snap- shot after the specified number of days.
10. 790 Chapter 14 N Maintaining the Database and Managing Performance SQL> BEGIN 2 DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE( 3 start_snap_id => 27, 4 end_snap_id => 29, 5 baseline_name => ‘OCP Example’, 6 expiration => 21); 7 END; SQL> / PL/SQL procedure successfully completed. SQL> To drop a baseline, use the DROP_BASELINE procedure as shown in the following code. The cascade parameter specifies that only the baseline should be dropped, not the snap- shots associated with the baseline. SQL> BEGIN 2 DBMS_WORKLOAD_REPOSITORY.DROP_BASELINE( 3 baseline_name => ‘OCP Example’, 4 cascade => FALSE); 5 END; SQL> / PL/SQL procedure successfully completed. SQL> You can create a baseline for the future date and time. These are called baseline templates. The following code creates a baseline template: SQL> BEGIN 2 DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE_TEMPLATE( 3 start_time => TO_DATE(‘01-JAN-09 05.00.00’,’DD-MON-YY HH.MI.SS’), 4 end_time => TO_DATE(‘01-JAN-09 08.00.00’,’DD-MON-YY HH.MI.SS’), 5 baseline_name => ‘baseline_090101’, 6 template_name => ‘template_090101’, 7 expiration => 21); 8 END; SQL> / PL/SQL procedure successfully completed. SQL> AWR baselines and baseline templates are never dropped automatically (or purged) from the database unless explicitly dropped by the DBA or the expiration period ends. Managing AWR Baselines Using EM Database Control Using EM Database Control to create, rename, and drop AWR baselines is easier than using SQL*Plus and error-free. From the database home page, click the Server tab (shown
11. Proactive Database Maintenance 791 earlier in Figure 14.1). Click the AWR Baselines link under Statistics Management. The current baselines are displayed, as shown in Figure 14.15. F i g u r e 1 4 .1 5 AWR Baselines screen Click the Create button to create a new baseline. You will be presented with the option to create a single baseline or a repeating baseline. If you choose a single baseline, you will be presented with the screen shown in Figure 14.16. Enter the name of the baseline. You can specify the snapshots to include in the baseline by using the snapshot IDs or using a time range. F i g u r e 1 4 .1 6 AWR Create Single Baseline screen
12. 792 Chapter 14 N Maintaining the Database and Managing Performance If you choose to create a repeating baseline, you’ll see the screen shown in Figure 14.17. Enter a baseline name, and specify the frequency. F i g u r e 1 4 .17 AWR Create Repeating Baseline screen You can drop a baseline by choosing the baseline and clicking the Delete button on the AWR Baselines screen (Figure 14.15). Automatic Database Diagnostic Monitoring Following each AWR statistics-collection process, the Automated Database Diagnostic Monitoring (ADDM) feature automatically analyzes the gathered statistics and compares them to the statistics gathered by the previous two AWR snapshots. By comparing the cur- rent statistics to these two previous snapshots, the ADDM can easily identify potential database problems such as these: NÛ CPU and I/O bottlenecks NÛ Resource-intensive SQL or PL/SQL or Java execution NÛ Lock contention NÛ Utilization of Oracle’s memory structures within the SGA NÛ RAC-specific issues NÛ Issues with Oracle Net configuration NÛ Data-concurrency issues
13. Proactive Database Maintenance 793 Based on these findings, the ADDM may recommend possible remedies. The goal of these recommendations is to minimize DB Time. DB Time is composed of two types of time measures for nonidle database users: CPU time and wait time. This information is stored as the cumulative time that all database users have spent either using CPU resources or waiting for access to resources such as CPU, I/O, or Oracle’s memory structures. High or increasing values for DB Time indicate that users are requesting increasingly more server resources and may also be experiencing waits for those resources, which can lead to less than optimal performance. In this way, minimizing DB Time is a much better way to mea- sure overall database performance than Oracle’s old ratio-based tuning methodologies. DB Time is calculated by combining all the times from all nonidle user ses- sions into one number. Therefore, it is possible for the DB Time value to be larger than the total time that the instance has been running. Once ADDM completes its comparison of the newly collected statistics to the previously collected statistics, the results are stored in the AWR. You can use these statistics to establish baselines against which future performance will be compared, and you can use deviations from these baseline measures to identify areas that need attention. In this manner, ADDM allows you to not only better detect and alert yourself to potential management and per- formance problems in the database but also allows you to automatically take corrective actions to rectify those problems quickly and with little or no manual intervention. The following sections introduce the interfaces, features, and functionality of ADDM and explain how you can use this utility to monitor and manage database storage, security, and performance. We’ll begin by examining the EM Database Control tools you can use to view the results of ADDM analysis. Using EM Database Control to View ADDM Analysis EM Database Control graphically displays the results of the ADDM analysis on several screens, including the following: NÛ The Performance Findings link under the Diagnostic Summary section of the EM Database Control main screen NÛ The Performance tab of the EM Database Control main screen NÛ The ADDM screen located by clicking the Advisor Central link at the bottom of the EM Database Control main screen You’ll see sample output from each of the EM Database Control screens in the following sections. The EM Database Control Performance Findings Link The EM Database Control home screen contains a section called Diagnostic Summary. One of the links under this section is ADDM Findings. Figure 14.18 shows this section.
14. 794 Chapter 14 N Maintaining the Database and Managing Performance F i g u r e 1 4 .1 8 The Diagnostic Summary section of the EM Database Control home screen The output in Figure 14.18 shows that ADDM discovered four performance-related findings. Clicking the link for these four performance findings displays the ADDM sum- mary screen, at the bottom of which is the Performance Analysis section, as shown in Figure 14.19. The Findings section on this screen shows the ADDM analysis and the recommendation to resolve the issue. F i g u r e 1 4 .1 9 ADDM summary screen
15. Proactive Database Maintenance 795 The EM Database Control Performance Tab You can also click the Performance tab on the EM Database Control main screen to view performance data collected by AWR and analyzed by ADDM. You can click the Run ADDM Now button to take an AWR snapshot and perform ADDM analysis. Figure 14.20 shows the Performance tab of EM Database Control. F i g u r e 14 . 2 0 EM Database Control Performance tab Using Active Sessions of the Performance tab, you can drill down into detailed informa- tion that has been identified as having an impact on performance. Click the Scheduler link, which will take you to the screen shown in Figure 14.21.
16. 796 Chapter 14 N Maintaining the Database and Managing Performance F i g u r e 14 . 21 Detailed performance information The Advisor Central Screen The Advisor Central screen also contains ADDM findings. The link for the Advisor Central screen is at the bottom of the EM Database Control home screen. Click this link to display the Advisor Central screen, shown in Figure 14.22. Click the ADDM link in the Advisors section of this screen to display a graph that shows all the recent AWR snapshots taken by the MMON process. As stated earlier, the ADDM automatically compares the most recent AWR snapshot with the last two AWR snapshots when formulating its recommendations. However, you can use this Create ADDM Task screen to manually select any two AWR snapshot times and formulate ADDM recommendations for activity that occurred between those two points in time. To start this process, click the Period Start Time radio button, and then select a start date and time by clicking the point in the graph’s timeline that corresponds to the beginning period that you want to use. Repeat this process to specify the end-process timestamp. You can also manually perform an ADDM analysis without EM Database Control by using the addmrpt.sql script located in the \$ORACLE_HOME/ rdbms/admin directory.
17. Proactive Database Maintenance 797 F i g u r e 14 . 2 2 The Advisor Central screen You can use the DBMS_ADDM package to manually analyze AWR snapshots. Table 14.4 shows some of the subprograms in DBMS_ADDM that can be used to manually manage ADDM analysis. ta b l e 1 4 . 4 Partial List of DBMS_ADDM Subprograms Procedure Name Description ANALYZE_DB Creates an ADDM analysis by specifying the begin and end AWR snapshot IDs DELETE Deletes an ADDM task INSERT_FINDING_DIRECTIVE Excludes certain findings from ADDM reporting INSERT_SEGMENT_DIRECTIVE Excludes a certain schema, object, or segment from ADDM reporting (do not run Segment Advisor on these segments) INSERT_SQL_DIRECTIVE Excludes certain SQL from ADDM reporting