HomeOracle Page 3 - Large Database Features In Oracle, conclusion
Self-Managing Databases - Oracle
When you are working with large databases with Oracle Database 10g, there are certain topics with which you need to be familiar. These topics are covered in this article, which is the second of two parts. It is excerpted from chapter nine of the book Oracle Database 10g A Beginner's Guide, written by Ian Abramson et al (McGraw-Hill/Osborne, 2004; ISBN: 0072230789).
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.
Large Database Issue
Reduced maintenance efforts
Improved performance through data striping
Number of users and applications
Multiple nodes available to service requests Improved performance (workload balancing)
Complexity of requests
Improved performance (workload balancing)
Improved performance (data striping)
Multiple nodes available to service requests
RAC and ASM
Use of lower-cost servers and generic disks
Reduced maintenance efforts (aware of all grid components)
TABLE 9-17. Large Database Issues Addressed by Grid Computing
Unified Scheduler Used to schedule routine administration tasks, as specified by the administrator or based on the information in the Automatic Workload Repository.
Space Manageability Includes automatic undo management, Oracle-managed files, free space management, and multiple block sizes.
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 Timeto Recovery.
Automatic Database Diagnostic Monitor (ADDM) Used to diagnose performance bottlenecks.
Server-Generated Alerts Alerts are sent when the database encounters or anticipates a problem.
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.
Advisors Used to analyze objects and recommend actions. Advisors include
Space Advisors Segment Advisor and Undo Advisor
Backup and Recovery Advisors Logfile Size Advisor and MTTR Advisor
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.
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.
Progress Check Answers
The Global Cache Service (or Cache Fusion) connects the nodes to the shared storage.
The existing data is automatically redistributed among all disks in the disk group.
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.
The Automatic Workload Repository contains workload and performance statistics used for self-management activities.
RAC, ASM, and OEM are the database components that are part of grid computing.
The Cluster Manager monitors the status of each database instance in the cluster and enables communication between the instances.
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.
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: