Large Database Features In Oracle, conclusion - Self-Managing Databases
(Page 3 of 7 )

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	Grid Components	Benefits
Data volumes	ASM	Reduced maintenance efforts
		Improved performance through data striping
Number of users and applications	RAC	Multiple nodes available to service requests Improved performance (workload balancing)
Complexity of requests	RAC	Improved performance (workload balancing)
	ASM	Improved performance (data striping)
Database availability	RAC	Multiple nodes available to service requests
Hardware/software costs	RAC and ASM	Use of lower-cost servers and generic disks
Resource management	OEM	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.

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.

---

Progress Check Answers

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'));

GENDER	CHANNEL_CLASS	MONTH	UNIT_COUNT	AMOUNT_SOLD
----------	----------------------	---------	----------------	-------------------
F	Direct	2001-01	4001	387000.9
F	Direct	2001-02	3208	365860.13
F	Direct		7209	752861.03
F	Others	2001-01	2486	242615.9
F	Others	2001-02	2056	229633.52
F	Others		4542	472249.42
F	Indirect	2001-01	1053	138395.21
F	Indirect	2001-02	1470	189425.88
F	Indirect		2523	327821.09
F			14274	1552931.54
M	Direct	2001-01	7038	719146.28
M	Direct	2001-02	6180	641192.61
M	Direct		13218	1360338.89
M	Others	2001-01	4310	414603.03
M	Others	2001-02	3751	391792.61
M	Others		8061	806395.64
M	Indirect	2001-01	1851	211947.81
M	Indirect	2001-02	2520	285219.79
M	Indirect		4371	497167.6
M			25650	2663902.13
			39924	4216833.67

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

Next: cube >>
 

More Oracle Articles
More By McGraw-Hill/Osborne

Comments On: Large Database Features In Oracle, conclusion

· This article is an excerpt from the book "Oracle Database 10g A Beginner's Guide,"...   >>> Post your comment now!

Buy this book...

This article 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). Check it out at your favorite bookstore. Buy this book now.