Self-Managing Databases - Oracle

One of the new features of Oracle Database 10g is its self-managing capabilities. The components of this feature are as follows:

• Automatic Workload Repository   Automatically collects workload and performance statistics to be used for all self-management activities.
  
  

• Unified Scheduler  Used to schedule routine administration tasks, as specified by the administrator or based on the information in the Automatic Workload Repository.

1. Space Manageability  Includes automatic undo management, Oracle-managed files, free space management, and multiple block sizes.
   
2. Backup and Recovery Manageability  Used to create reusable backup configurations, automate management of backups and logs, and ensure restart within a specified time after system failure. Includes Recovery Manager and Mean Time to Recovery.
   
3. Automatic Database Diagnostic Monitor (ADDM)  Used to diagnose performance bottlenecks.
   
4. Server-Generated Alerts  Alerts are sent when the database encounters or anticipates a problem.
   
5. Database Resource Manager  Used to prioritize work within the database, ensuring that high priority users or jobs get the resources they need, when they need them.
   
6. Advisors  Used to analyze objects and recommend actions. Advisors include
   
7. Space Advisors  Segment Advisor and Undo Advisor
   
8. Backup and Recovery Advisors  Logfile Size Advisor and MTTR Advisor
   
9. SQLAccess Advisor
   
10. SQL Tuning Advisor

• Memory Advisors  Shared Pool Advisor, Buffer Cache Advisor, and PGA Advisor

Grid computing has the potential to offer tremendous benefits in the areas of availability, performance and maintenance, all while reducing costs. Grid computing will be the last in our discussions on design and architectural issues. We’ll take a progress check and then explore some of the analytical aspects offered by Oracle Database 10g when working with large databases.

----------------------------------------------------------------------

Progress Check

1. In an RAC system, what component connects the nodes to the shared storage?
   
2. When a disk is added to an ASM disk group, what happens to the existing data in the group?
   
3. True or False: A database instance in an ASM system accesses the disk groups?
   
4. What component of a self-managing database contains workload and performance statistics used for self-management activities?
   
5. What are the database-related components that are part of grid computing?
   
6. What is the function of the Cluster Manager in RAC systems?

    

----------------------------------------------------------

CRITICAL SKILL 9.9

Use SQL Aggregate and Analysis Functions

Once your database has been loaded with data, your users or applications will, of course, want to use that data to run queries, perform analysis, produce reports, extract data, and so forth. Oracle Database 10g provides many sophisticated aggregation and analysis functions that can help ease the pain sometimes associated with analyzing data in large databases.

1. The Global Cache Service (or Cache Fusion) connects the nodes to the shared storage.
   
2. The existing data is automatically redistributed among all disks in the disk group.
   
3. False. The database instance communicates with the ASM instance to determine which ASM files to access directly. Only the ASM instance works with the disk groups.
   
4. The Automatic Workload Repository contains workload and performance statistics used for self-management activities.
   
5. RAC, ASM, and OEM are the database components that are part of grid computing.
   
6. The Cluster Manager monitors the status of each database instance in the cluster and enables communication between the instances. 

Aggregation Functions

Oracle Database 10g provides extensions to the standard SQL group by clause of the select statement that generate other totals as part of the result set that previously required multiple queries, nested subqueries, or importing into spreadsheet type applications. These extensions are rollup and cube.

rollup

The rollup extension generates subtotals for attributes specified in the group by clause, plus another row representing the grand total. The following is an example of the rollup extension, using the SALES table we have seen throughout this chapter:

select c.cust_gender gender,
       b.channel_class channel_class, 
       to_char(a.time_id, 'yyyy-mm') month,
       count(*) unit_count,
       sum(a.amount_sold) amount_sold
from sales a, channels b, customers c
where a.channel_id = b.channel_id
and   a.cust_id = c.cust_id
and   to_char(a.time_id, 'yyyy-mm') between '2001-01' and '2001-02'
group by rollup(c.cust_gender,
               b.channel_class,
               to_char(a.time_id, 'yyyy-mm'));

In the results, we can see that counts and sums of amount_sold are returned at the following levels:

1. By GENDER, CHANNEL_CLASS, and MONTH
   
2. Subtotals by CHANNEL_CLASS within GENDER
   
3. Subtotals by GENDER
   
4. Grand total