Dontcheff

Archive for the ‘Grid Control’ Category

On DBA innovation: who is afraid to fail will keep falling forever

In Cloud, Database tuning, DBA, Exadata, Grid Control, Oracle database on April 24, 2012 at 19:06

Managers always ask software engineers to deliver something sooner rather than waiting to deliver everything later.

How come it is fine to deliver an incomplete low quality IT product just because it is delivered on time? Most of us have been on several occasions limited by that deadline and time-schedule in terms of creativity, innovation and pro-activeness.

Innovation in database management and database administration has nothing to do with DBAs from whom you often hear phrases like “If it ain’t broke, don’t fix it” or DBAs who follow blindly the KISS principle.

In DBA terms, innovation is the process of introducing and implementing new features in the database and using new options and database products.

A good example is the adoption of Oracle Enterprise Manager Grid/Cloud control. I have seen so many excuses for not implementing it or delaying its implementation. And the benefits and savings of OEM implementation can be measures in times!

Do you wonder how quickly and efficiently one can find out all details of a certain SQL statement without Cloud Control? No other database brand has so highly sophisticated tool for performance analysis like Oracle has. See all the details offered with one mouse click: Exadata Cell Offload Efficiency (96% in this case), Wait Activity in detail, use of the Result Cache, etc. All that from a single screen!

Accepting new innovative database properties, tools and appliances is hard for many IT architects, IT managers and most of all for DBAs who have the primary responsibility to test, verify and promote these features.

Let us have a look at one other innovation from Oracle. Implementing Exadata can be strongly considered if any of the following 5 points are in the IT roadmap:

1. Implementing a new Business Intelligence solution
2. Datawarehouse licenses are up for renewal
3. Database platform consolidation exercise
4. Storage requirements are increasing and the performance is decreasing
5. Performance of transactional systems requires major improvements

Look at the list below and think how many of these are used by your company or client:

– Exadata
– Enterprise Manager Cloud Control
– Edition-Based Redefinition
– Advanced Compression
– Oracle Database Appliance
– Automatic SQL Tuning
– Total Recall
– Real Application Testing
– One of these init.ora parameters: db_ultra_safe, result_cache_mode, optimizer_capture_sql_plan_baselines, awr_snapshot_time_offset

Although I did not put Enterprise Manager Cloud Control on top of the list, is it still a must for every Enterprise using Oracle products. On the light side, I was recently asked if you can see the temperature of a given computer from OEM 🙂 Here we go:

Jon Taplin said it very well in his article on Steve Jobs and Innovation: “At the Innovation Lab we try to inculcate the notion that you can’t be afraid. You can’t be afraid to fail. You can’t be afraid to “be different”. You can’t be afraid to celebrate the weird mix of art and science that is true innovation. Steve Jobs embodied all of those qualities. I wrote a bit about him in my new book and there is a cool video in the book of his graduation speech at Stanford that you will see replayed too often in the next few days.”

As a DBA, one should try to step out of his comfort zone where (s)he is surrounded by the everyday administrative tasks and reactive performance tuning work and try to make difference in the enterprise by acting more like a Database Architect than a Database Operator. Prove the complexity and importance of the DBA role!

P.S. Often in IT, the way from Insight to Action is longer than the Way of St. James.

New DBA Features in Oracle Enterprise Manager Cloud Control 12c

In Cloud, Database tuning, DBA, Grid Control, Oracle database on October 22, 2011 at 10:17

During Oracle OpenWorld 2011, Oracle introduced Enterprise Manager Cloud Control 12c Release 1 (12.1.0.1). I have already done the first installation on OEL Linux 6 and I must say I am impressed with the product.

Oracle Enterprise Manager Cloud Control 12c came with new features covered in 12 areas:

– Framework Enhancements
Database Management Features
– Fusion Middleware Management Features
– Oracle Fusion Applications Management
– Application Performance Management Features
– Cloud Management Features
– Incident Management Features
– My Oracle Support Integration
– Security Features
– Lifecycle Management and Provisioning Features
– Extensibility Support
– Coherence Management Features

In this post, I will present the new 20 DBA features of OEM CC 12c as documented by Oracle (the screenshots in between are from my own installation):

1. Database Creation Via Enterprise Manager Cloud Control

A wizard allows you to create an Oracle Database from within Enterprise Manager Cloud Control. You can create different configurations, including Single Instance and Real Application Clusters (RAC) databases, as well as file system and Automatic Storage Management (ASM) storage options.

2. Database Upgrade Via Enterprise Manager Cloud Control

You can now upgrade Single Instance and Real Application Clusters (RAC) Oracle databases through Cloud Control. This feature makes it possible to manage upgrades from a single console. You no longer have to access each individual database machine to perform upgrades.

3. Database Cloning Enhancements

Cloning procedures have been enhanced to capture configuration properties as well as the software payload. This is particularly useful when migrating databases from development to test to production or the reverse. A new EMCLI verb (clone_database) allows for database cloning using the same job type as the existing Clone Database feature of Cloud Control.

4. Oracle Exadata Server Management Enhancements

Oracle Exadata management capabilities now provide support for full target lifecycle management:

– Automatically discover Exadata targets
– Automatically create an Enterprise Manager System for end-to-end monitoring
– Provide extensive administration capabilities for databases, Exadata cells and Infiniband switches
– Simplify performance diagnostics with the help of in-depth performance charts covering all Exadata components

5. Manage Automatic Storage Management (ASM) Clusters as a Target

You can now manage clustered ASM resources as a single target, instead of each individual ASM instance having to be managed separately. Incident management and metric monitoring can be managed for the entire cluster.

6. Database Configuration Compliance Standards Support

Oracle database configuration data can now be managed within the new configuration and compliance standards frameworks.

7. Emergency Performance

This feature allows a DBA to diagnose and troubleshoot a hung or extremely slow database using the memory access mode. This mode is designed to bypass the SQL information retrieval layer and read performance statistics directly from the SGA of the target.

8. Database Backup and Restore Enhancements

You can now use Enterprise Manager Cloud Control to centrally maintain the settings for Oracle database and file system backups. This enhancement enables you to apply uniform settings to multiple databases and file systems when backing up multiple objects—all in one operation. Users can perform a backup on multiple databases concurrently or sequentially in one deployment procedure. An Oracle Home OSB tape backup can be restored either to the original or to a different location, and the restored Oracle Home can be reconfigured to function in the newly restored location.

9. Database System Discovery Enhancements

As the database system is now built upon the new target and association model, you can use it to monitor and manage a database’s storage, connectivity, and high availability. This also enables you to easily monitor and manage the applications that are dependent on the database. The database discovery functionality is enhanced to work with the new discovery framework and to provide a reliable workflow to create a database system.

10. Change Plans Added to Change Management Pack

As part of the Oracle Change Management Pack, the new Change Plans function allows application developers and database administrators to encapsulate schema changes needed to be made to a database into a “change plan,” which can be used to document, capture, and apply schema changes. Change Plans are also integrated with developer and DBA tasks into SQL*Developer and Oracle Enterprise Manager task automation. This integration reduces the manual processes between the various stakeholders involved in the process of promoting planned changes across enterprise databases while ensuring the integrity of the process.

11. Compare Period Advisor

This feature compares the performance of a database over two different time ranges. It analyzes changes in performance, workload, configuration, and hardware to highlight changes between the two time periods. The Compare Period Advisor gives the DBA the ability to compare two arbitrary periods of time.

12. Compare Functionality

The Compare functionality has been enhanced with new capabilities such as template support, system level comparison, and change notification. Users can now selectively include or ignore types of differences. Output of a comparison can easily be saved and exported, both in printable (for example, plain text) and data-centric (for example, CSV) formats. Users can select comparison start and end dates and view a history of changes for composite targets.

13. Active Reports

A new Active Reports function allows users to save performance data into an HTML file. Once saved, the report can be used for offline analysis or sent to other users, including Oracle Support. Active Reports enhances the visual representation of performance data and facilitates the convenient exchange of complex data.

14. Real Application Testing and Data Masking Integration

Real Application Testing and Data Masking integration provides users with the ability to perform secure testing in situations where data in production needs to be shared by nonproduction users due to organization or business requirements. Typically testing is done in a nonproduction environment or by a different group or organization. This integration addresses a common requirement that the data used for testing be shared in a manner that adheres to data privacy and compliance regulations.

15. Application Templates for Data Masking and Data Subsetting

This feature provides predefined data masking and data subsetting templates for applications. It allows users to automatically create test systems based on best practices recommendations.

16. Data Subsetting

Data subsetting provides the ability to create a smaller sized copy of the original production data that can be given to developers for testing. While it is a data subset, the referential relationships are preserved so that the data set is complete. This allows enterprises to lower storage costs while making production data available to developers for testing, without having to incur the storage footprint of the entire production database.

17. Application Data Model Support for Data Masking

The application data model (ADM) now stores the sensitive data elements used to generate mask definitions dynamically. Instead of having to manually discover sensitive data, the application data model identifies and stores the sensitive data elements.

18. Reversible Data Masking

Using encryption and decryption algorithms, reversible masking allows encryption of a user’s data deterministically into a format chosen by the user as a regular expression. Unmasking reverses the process to revert back to the original data. This feature is useful in environments where sensitive data needs to be masked and sent to a third party for processing. Coupling integrated masking with the application data model (ADM), an application’s data model is now available for certain packaged applications and can serve as a knowledge base containing sensitive column and data relationships.

19. Performance Diagnostics Enhancements

With the interactive user interface in the Active Session History (ASH) Viewer, users now can visualize the many performance dimensions that were not available to them in earlier releases. The Enhanced Enterprise Manager Performance and Top Activity pages allow users to visualize the multidimensional data in ASH. The ASH viewer enhances the performance troubleshooting capabilities of a DBA by providing the facility to detect skews in workload. Emergency ADDM adds performance diagnostics for databases suffering from severe performance problems.

20. Streams and XStreams Support

Streams and XStreams configurations can now be managed and monitored using Cloud Control. In addition to improvements in configuration and performance monitoring screens, logical change record (LCR) tracking is available for high-level diagnosis of replication issues. Cloud Control also simplifies the management and monitoring of replicated environments.

Oracle Enterprise Manager Cloud Control 12c

In Cloud, DBA, Grid Control, Oracle database on October 4, 2011 at 00:57

It is now official: Oracle announced on October 3rd the new version of Enterprise Manager Cloud Control 12c Release 1 (12.1.0.1).


This is the first solution in IT to combine management of the full Oracle stack with complete enterprise cloud lifecycle management.

You may download it only for Linux x86-64 (64-bit).

The Enterprise Manager Cloud Control Documentation gives you the following:

Basic Installation Guide
Advanced Installation and Configuration Guide
Upgrade Guide
Administrator’s Guide
Cloud Administration Guide
Licensing Information

There are 4 mandatory plug-ins that are installed by default with Enterprise Manager Cloud Control:

Oracle Database plug-in
Oracle Fusion Middleware plug-in
Oracle My Oracle Support plug-in
Oracle Exadata plug-in

Enterprise Manager offers the following management packs for Oracle Database:

Diagnostics Pack for Oracle Database
Tuning Pack for Oracle Database
Database Lifecycle Management Pack for Oracle Database
Configuration Management Pack for Oracle Database
Provisioning and Patch Automation Pack for Oracle Database
Change Management Pack for Oracle Database
Oracle Data Masking Pack for Oracle and non-Oracle Databases
Oracle Test Data Management Pack for Oracle and non-Oracle Databases

Top 3 new features for DBAs:

1. Real-time ADDM. You can connect to the database and investigate what is going on when you cannot connect to the database because it is hanging on it is extremely slow. This is for real, connection to the database is in diagnostic mode, there is direct connections to the SGA.

2. Active Session History (ASH) Analytics. It is the adavnced version of Top Activity.

This is a snapshot from load map view mode:

3. Compare period ADDM. You can identify why during a certain period activity was slow when no changes to the SQL statements were made. Here is a snapshot from ADDM compare:

Great product from Oracle and this is not any longer just a DBA tool.

From yesterday’s Total Cloud Control session: Richard Sarwal and Sushil Kumar:

More to follow on DBA new features in “New DBA Features in Oracle Enterprise Manager Cloud Control 12c”.

Oracle Database Appliance and Automatic Bug Fixing in the Cloud

In Bugs, Database tuning, DB2 database, DBA, Grid Control, Oracle database on September 21, 2011 at 19:13

Mathematician Alfred North Whitehead said: “Civilization advances by extending the number of important operations which we can perform without thinking about them.”

Same holds for the Database!

Oracle have just announced the new “Oracle Database Appliance” with self managing automatic features preconfigured (simple, highly reliable, affordable database system for small/midsize enterprises and departments):

I have just gathered a list of the automated processes offered by Oracle within the Oracle database (in no order whatsoever):

– Automatic Statistics Collection
– Automatic Tuning Optimizer (ATO)
– Automatic Repair in a Data Guard Configuration
– Automatic Undo Management
– Automatic Undo Retention Tuning
– Automatic Shared Memory Management
– Automatic Space Segment Management (ASSM)
– Automatic PGA Memory Management
– Automatic Memory Management (AMM)
– Automatic Degree of Parallelism
– Automatic Storage Management (ASM)
– Automatic RAC Database Startup/Restart
– Automatic Maintenance Tasks
– Automatic Tablespace Point In Time Recovery
– Automatic Workload Repository (AWR)
– Automatic Service Registration
– Automatic SQL Tuning (my favourite!)
– Automatic Database Diagnostic Monitor
– Automatic Segment Advisor
– Automatic Diagnostic Repository (ADR)
– Automatic Checkpoint Tuning
– Automatic Maintenance Jobs
– Automatic Global Index Maintenance During DDL
– Automatic Client Failover
– Automatic OCR Backup Rotation
– Automatic Plan Capture with SPM
– Automatic Refresh of Materialized Views
– Automatic VIP failback
– Automatic Block Recover
– Automatic Disaster Recovery Fails with RMAN
– Automatic Channel Failover
– Automatic Synchronization of Oracle Text Index
– Automatic Registration of the Database with the Default Listener
– Automatic Offlining of the Datafile in Noarchivelog
– Automatic Datatype Conversion
– Automatic Tape Drive Cleaning and a Cleaning Tape in a Tape Library
– Automatic Controlfile Backup
– Automatic Eject Of Tape After Backup Using Oracle Secure Backup
– Automatic BackupSet Failover On Missing or Corrupt BackupPieces
– Automatic BMR (Block Media Recovery)
– Automatic System Tasks
– Automatic Database Performance Monitoring
– Automatic Archiving
– Automatic Propagation in Replication
– Automatic Job Scheduling
– Automatic Resume of DataPump

Quite a list I would say, right?

An excellent paper from Oracle called Oracle Database 11g vs. IBM DB2 UDB V9.7 points out the most important trend of database manageability: the self automation of the database product. Let me quote (part of) the conclusion of the paper:

“The Automatic Database Diagnostic Monitor (ADDM), SQL Advisors and Real Application Testing are just some of the unique Oracle Database 11g features that are yet unmatched by DB2 version 9.7. Oracle Database 11g is the only database product available today that automatically collects and manages historical performance data for self-management purposes, periodically and automatically analyses this data and makes tuning recommendations. Oracle Database 11g is also the only RDBMS with rich software quality management features for real workload testing. These distinct technologies are at the core of the next generation of Oracle databases that represent simplicity, ease of management and software quality management while still providing the most robust, reliable and secure of relational databases.”

How true indeed! But what we would like to see in the future is even more: how about automatic bug fixing in the Cloud? Just like this:

1. First, we set the credentials with Oracle Support: How to set the ‘My Oracle Support’ Preferred Credentials in the Grid Console? [ID 1168603.1]
2. The Oracle database creates an incident and it is transferred to Oracle Support via the Enterprise Manager Support Workbench.
3. Then it is internally verified if the problems is bug related.
4. If it is a bug then Oracle’s own BugDB checks for a patch or workaround which fixes the bug.
5. If there is a patch available, then the patch is automatically uploaded to the clients cloud environment and then applied (online of course)!
6. If there is a workaround with an init.ora parameter, then the “alter system” command is automatically applied, as what Oracle Support can remotely run in the client’s database is controlled by the client with a new init.ora parameter called mos_cloud_permission_level.

That is what I call automation!

Note that something similar is even now offered by Oracle via the SQL Repair Advisor. It is a semi-automatic patching of SQL statements throwing ORA-600 or ORA-7445. But here by patch Oracle mean more of an SQL transformation than a standard patch downloadable from MOS.

Oracle OpenWorld 2011

In Database tuning, DBA, Grid Control, OOW, Oracle database, Oracle utilities, Personal on September 14, 2011 at 05:45

I am Speaking at Oracle OpenWorld 2011 and I hope you will join me there!

Welcome to my session: Tuning Toolkit for Advanced DBAs: Learn from the Past, and Prepare for the Future. The presentation will be on Tuesday at 03:30 PM, Moscone South – 104.

There will be over 80 Oracle ACEs and ACE Directors who will speak at OOW 2011!

The content catalog shows 75 sessions on Database performance and scalability out of the 306 database conference session. Just a reminder that 4-5 years ago the database conference sessions were about 100.

The database stream has been now divided into the following 11 substreams:

– Cloud Consolidation: 39 sessions
– Data Warehousing: 32 sessions
– Database Manageability: 55 sessions
– Database Platforms: 31 sessions
– Database Security: 23 sessions
– High Availability: 47 sessions
– MySQL: 29 sessions
– Oracle Exadata: 57 sessions
– Performance and Scalability: 75 sessions
– Specialty Data and Search: 15 sessions
– Storage Management: 24 sessions

One of my favorite topics, Oracle Enterprise Manager (part of the Cross Stream track), will be covered in 161 conference sessions. I have said it before, I have had it in several final conference slides, I will say it now: Oracle Enterprise Manager Grid Control is probably the best feature/tool/utility in Oracle after 9i.

And for the DBAs, as this is a DBA blog, here is a complete list of all sessions having the work “DBA” in the title:

14642: AWR and ASH in 3-D: Performance Analysis Tools No DBA Has Seen Before
20880: Becoming a Rock Star MySQL DBA
9218: DBA Fusion: An Introduction to Oracle Enterprise Manager 11g Architecture and Installation
13781: Day in the Life of a DBA: Cloud-Ready Management Solutions for Any IT Shop
14641: Extreme Database Administration: New Features for Expert DBAs
13081: Mastering Oracle Data Pump: Technical Deep Dive into Performance/Internals for Hands-on DBAs
15143: MySQL for Oracle DBAs, or How to Speak MySQL for Beginners
13446: Oracle Database Vault: DBA Best Practices
8046: Oracle Exadata Management for Oracle DBAs
14365: Oracle WebLogic Server Management for DBAs: Cross-Tier Visibility from JVM to Database
6681: Trends in Database Administration and the Changing Role of the DBA
14644: Tuning Toolkit for Advanced DBAs: Learn from the Past, and Prepare for the Future
8044: What If Kramer Were Your DBA and Seinfeld Tuned Your Database?
28900: Launching the IOUG Virtualization SIG: 360 Degrees of Virtualization for Oracle DBAs

If you wonder why so many people attend OOW, here are the high-level benefits of attending Oracle OpenWorld according to Oracle:

– Participate in sessions and hands-on workshops led by the world’s foremost IT experts
– Find out firsthand how to streamline upgrades and deployments
– Attend sessions in the all-new Server and Storage Systems stream
– Dig deep into application deployment, scaling, upgrading, and best practices
– Meet with your industry and technology peers
– Share notes in special interest groups, and product and industry sessions
– See hundreds of partners and customers as they present product demos and solutions in three Exhibition Halls

And here is a picture from LJE’s keynote on Cloud Computing I took last year from my seat:

P.S. I cannot find a single session on Oracle bugs and we all know that they play a major role in the database world. For sure, many DBAs would be very interested in a presentation called “11gR2 top 10 bugs“.

The Business of Business is Business

In Bugs, Database tuning, DBA, Grid Control, Oracle database, RAC on July 11, 2011 at 21:58

Winston Churchill said: “Some regard private enterprise as if it were a predatory tiger to be shot. Others look upon it as a cow that they can milk. Only a handful see it for what it really is – the strong horse that pulls the whole cart.”

Have a glimpse at Nasdaq’s article: Upside to Oracle’s Dominant Position in Database Software to double check that namely Oracle is the strong horse. “Oracle continues to maintain its leadership position in the database software market with a share of close to 50%, thanks to constant innovation in its database business and positive feedback from the Exadata line of servers.”

According to Gartner’s market share numbers by relational database management systems, Oracle leads worldwide RDBMS software market share and total software revenue, with its Linux and Unix garnering 74.3% and 60.7% market share respectively.

But for good horses, you need good bug control:

Let me show you an example of a perfectly well working RAC database where all of a sudden you see this “Other” activity in Grid Control:

Drilling down in EM, you witness the unorthodox event “latch: ges resource hash list”. Note that GES resources (GES = Global Enqueue Service) are accessed via a hash array where each resource is protected by a ges resource hash list child latch.

So, what have we done wrong that we have the privilege of witnessing this horrible histogram below (doesn’t the one above look like the top of the head of Bart Simpson)?

Turns out it is Bug 11690639 – High enqueue activity results in “latch: ges resource hash list” waits (Doc ID 11690639.8). Question: is the document ID named after the bug number or is it vice versa?

This is just an example. Many DBAs are nowadays mildly frustrated with the daily routine of bug fixing, looking for workarounds and possible patches. While some years ago the first thing a DBA would do for a problem was to try and tune the database, improve the SQL, etc., now most DBAs try to overrule at first the possibility of a bug.

Bugs are fixed but they re-appear. Fixing a bug can bring another one. Bugs bring systems down. You have covered the infinite availability SLA with RAC, Gloden Gate, Active Dataguard, etc. but one small bug can cause a so severe performance problem that all your efforts and diagnostic tools count for nothing.

How many Database experts talk about bugs at database events? How often do you see blog articles tagged with the word bug? Have you ever tried to look on the Internet for books on database bugs? Check out Martin Decker’s ora-solutions.net! I enjoy the site a lot!

Oracle keep all their bugs in a database called BugDB. It is a mission critical one and was the first internal application in Oracle to be upgraded with EBR. Check this one out: Online Application Upgrade of Oracle’s Bug DB with EBR!

A Bug DBA is something one would probably not like to be called (unlike a performance tuning DBA) but this phenomenon in the database world is significantly growing its importance and the faster the DBA spots out the bug the less the downtime will be. Unlike using the traditional way where anyone can open an SR with MOS/Metalink and wait for a (possible) resolution.

Bug spotting is of extreme importance nowadays as hours and days of slow performance or downtime can be devastating for the enterprise. And the work of the Bug Buster does not finish after the identification of the bug: then (s)he must decide between recommending patching or using a workaround. And all over again..

On gathering dictionary statistics: do I analyze the SYS schema?

In Database tuning, DBA, Grid Control, Oracle database on June 4, 2011 at 21:29

Attorneys specialize nowadays in neighbor’s-trees-blocking-your-view litigation, I try to specialize in fine-grain-database-tuning.

First you specialize in databases, then you narrow to Oracle, then you specialize in performance tuning. And then I narrow to certain types of tuning: I think automatic tuning is the future. But who knows, I have often been wrong before. People ask me: “Julian, are you getting lazy nowadays that you do not want to do the manual work?”

Anyway, you express opinion on a certain subject, some people take it for granted, some want to see a bulletproof one. So, how about this question: should we analyze the SYS schema? “Beats me” is the most common answer.

Rusted DBAs who still go memory lane 7.3.4 after a glass of whiskey will probably tell you no way! But most DBAs from the new school have already read 245051.1, where it clearly says: “In Oracle 10g collecting of data dictionary statistics is mandatory for CBO to work properly”.

1. Dictionary Statistics. In another MOS note, 457926.1, we read the following: “Gather_schema_stats gathers statistics for objects owned by the SYS Schema. We recommend gathering statistics for the SYS schema, specifically if you are using Oracle APPS.” Here is more:

2- SQL Performance Analyzer. Question is, is this enough convincing? Granted that all we have seen documented and praised, implemented in reality is not always a one-to-one match. Right?

Solution is to use the SQL Performance Analyzer with a SQL Tuning set containing only system queries. Delete the data dictionary statistics first, flush the shared pool and analyze the SYS schema without publishing the statistics. I did it and here is the report (click on the picture to enlarge the image):

22% improvement is nothing great but at least it clearly shows that you better analyze SYS than not. Of course, with a different SQL set you might have a different result. Try for yourself! Look at the top 10 statements affecting the performance, I witnessed the following:

Have a look also at the following good articles: Data Dictionary Statistics Gathering and
Gathering System Statistics in Oracle.

3. How and when to gather SYS stats. Granted we agree that we have to analyze SYS, it would be quite natural to ask: when and how often? Usually, I would do this after big changes to the database. Like creation of a new schema with lots of objects, before/after big datapump operations, etc. Check for example: Poor Performance With DataPump Export on Large Databases [ID 473423.1]

Or even better have it as a database job. If you check “Oracle Sun Database Machine Application Best Practices for Data Warehousing” [ID 1094934.1], you will note the following:

“When to gather dictionary statistics: Statistics on the dictionary tables will be maintained via the automatic statistics gathering job run during the nightly maintenance window. If you choose to switch off the automatic statistics gathering job for your main application schema consider leaving it on for the dictionary tables. You can do this by changing the value of AUTOSTATS_TARGET to ORACLE instead of AUTO using the procedure DBMS_STATS.SET_GLOBAL_PREF.

EXEC DBMS_STATS.SET_GLOBAL_PREFS(AUTOSTATS_TARGET,’ORACLE’);

If you choose to switch off the auto job complete you will have to maintain the dictionary statistics manually using the GATHER_DICTIONARY_STATS procedure.”

By default, automatic optimizer statistics collection calls the DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC procedure. By default GATHER_SYS is true (?).

4. Fixed tables’ statistics. Neil Johnson wrote a good one on When to Gather Fixed Object Optimiser Statistics. The example is enough convincing to me at least.

Fixed Objects Statistics Considerations [ID 798257.1] suggests why those statistics are needed:

1. Missing or bad statistics on the X$ / fixed tables can lead to performance degradation or hangs. Various X$ views are protected by latches and as a result can be very expensive to query in large / busy systems.
2. Most commonly this issue is seen on the underlying X$ tables for DBA_EXTENTS, V$ACCESS, V$RMAN_BACKUP_JOB_DETAILS, and V$RMAN_STATUS, but any fixed table protected through latching can experience this.
3. Another commonly seen symptom is extreme TEMP space usage driven by poor plans against the fixed tables.
4. RMAN, Data Guard, Streams, and Grid Control make heavy usage of the fixed tables through the DBA/V$ views and so can often bear the brunt of performance issues.

5. Locked statistics in the Data Dictionary. Starting 11.2.0.2, statistics for some objects in the Dtata Dictionary are locked by default with a likely goal of preserving access paths for those queries. And in fact, not only in the SYS schema! You can modify the following SQL to find out what is locked in your database by default:

select TABLE_NAME, LAST_ANALYZED, STATTYPE_LOCKED 
from DBA_TAB_STATISTICS
where STATTYPE_LOCKED is not null and owner='SYS'
/

Let me finish with a screenshot of the statements which Oracle considered unsupported during my SQL Performance Analyzer test:

Do you know a DBA specialized in Oracle bugs?

Larry Ellison at the Churchill Club

In DBA, Grid Control, Oracle database, PL/SQL on March 21, 2011 at 11:02

Today is exactly one and half years after Larry Ellison was interviewed by Ed Zander at the Churchill Club.

Ben Rockwood wrote a must-read article about the event and I would not like to repeat what he said but just highlight few things looking retrospectively into this probably most interesting and flamboyant LJE interview ever.

Here are 5 interesting IBM related quotes:

1. The company that does more Oracle installations than any other company in the world: that will be IBM.
2. IBM is who we’re targeting, its who we feel we need to compete with to be successful ourselves. We are happy to compete with IBM on level plain field.
3. I’d like IBM to explain what they meant when they said they took 250 customers away from Sun. I don’t think there is a single example of any Sun customer who’s replaced all of their machines with IBM computers.
4. I will be very clear: Solaris is way better than AIX.
5. Sun machines run faster than IBM machines and cost less.

How do you like the part on Cloud Computing? I just can’t disagree with him 🙂

Do you remember also this one?

And finally the part from last year’s OOW (2010):

Poll: which concept do you least trust, which one do you find most fake?

Watson, IBM InfoSphere Streams and Oracle Streams

In DB2 database, Grid Control, Oracle database, Replication on February 24, 2011 at 19:30

Initially, my plan was to write only on Oracle Streams but after Watson’s victory on Jeopardy, I got an interesting question from an ex-colleague of mine: Is this supercomputer HA? What if the database crashes and there is no source for answers? Funny, isn’t it 🙂 After all Watson is an example of an analytics workload optimized system, not an online sales platform.

But first, have a look at this, how Ken Jennings and Brad Rutter, the show’s two biggest winners, took on their database rival on Feb. 14-16 2011. Note that according to the president of WABC – New York City’s ABC television affiliate Monday’s broadcast of the IBM Watson/Jeopardy Challenge was the most watched show on any channel in the NY Metro area.






Watson is a mixture of 10 racks of IBM POWER 750 servers, Linux OS, 15 TB of RAM, 2880 processor cores and operates at 80 teraflops. The data is stored in a DB2 database. Another one of the 4 major technologies Watson is based on is IBM InfoSphere Streams. While IBM InfoSphere Streams enables continuous and super fast analysis of massive volumes of information, Oracle Streams enables continuous and very fast replication of massive volumes of information. Well, at least when it works and there are no errors. Say one like this:

Looking at the details of the error, what do we understand?

As much as we understand from the statistics:

My personal opinion is that Streams is as good as Advanced Replication was. After all, Oracle will not develop it any longer. Oracle bought Golden Gate and will try to use it for all data integration/replication solutions.

P.S. If only Sherlock Holmes had this type of Watson 🙂

Oracle and computers with zero CPUs

In Bugs, DBA, Grid Control, Oracle database, OS on January 20, 2011 at 20:50

How do you license a computer with zero CPUs? Of course, there are no such computers but still according to Oracle’s Grid Control this is not always the case 🙂 Have a look:

These hardware inventions should not bring us any cost, shouldn’t they 🙂

It is worth asking the question are there any computers with zero IO devices and NULL CPU boards:

Looks like also that the number of CPU boards cannot be visible for some computers, not to mention that although the documentation claims that “MGMT$CSA_COLLECTIONS displays top-level information about all client configurations”, I can still see nothing from this view:

You notice that this is in Grid Control 11g? I remember similar cases in 10g as well, here is an old snapshot of mine:

When will computers run without CPUs? But it is like a person without a brain says a colleague of mine.

And finally, is Oracle wrong or did HP forget to put a ventilator in the computer 🙂