Home arrow Oracle arrow 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).

TABLE OF CONTENTS:
  1. Large Database Features In Oracle, conclusion
  2. RAC Architecture
  3. Self-Managing Databases
  4. cube
  5. Analysis Functions
  6. Windowing Functions
  7. Project 9-2 Using Analytic SQL Functions and Models
By: McGraw-Hill/Osborne
Rating: starstarstarstarstar / 6
December 15, 2005

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

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



 
 
>>> More Oracle Articles          >>> More By McGraw-Hill/Osborne
 

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

ORACLE ARTICLES

- Oracle Java Security Woes Continue
- Oracle's New IaaS Cloud Option: There's a Ca...
- Oracle Acquires Eloqua to Boost Cloud Presen...
- Choosing Innovation: Oracle Survey Insights
- Oracle Fixes Privilege Escalation Bug
- Oracle`s Communications Service Availability...
- Oracle Releases Exalytics, Taleo Plans
- Oracle Releases Communications Network Integ...
- Oracle Releases Communications Data Model 11...
- Oracle Releases PeopleSoft PeopleTools 8.52
- Oracle Integrates Cloudera Apache Distro, My...
- Oracle Releases MySQL 5.5.18
- Oracle Announces NoSQL Database Availability
- Sorting Database Columns With the SELECT Sta...
- Retrieving Table Data with the LIKE Operator

Developer Shed Affiliates

 


Dev Shed Tutorial Topics: