In the world of the working Oracle DBA, it is possible one could be expected to manage dozens of production instances concurrently. Almost all working Oracle experts interact with their database with OS Commands, SQL*Plus scripts and shell scripts. The Oracle Enterprise Manager (OEM) GUI is primarily used only by the newbies and the more inexperienced DBAs.
OEM makes it easy for a neophyte to “pretend” to be an experienced DBA. Since it takes several years of full-time work to be familiar with the command-line syntax, OEM has been a great favorite among the “poser” crowd.
Anytime I work with a DBA who can only administer a database from OEM, I know that they are likely to belong to one of these groups:
Raw beginners - OEM is a great tool for beginners who have not yet learned that the command line interface is infinitely more flexible than the limited possibilities offered in OEM.
Posers - Fraudulent credentials and fake resumes are all too common in the Oracle industry, and reliance on GUI tools should be a tip-off that you are dealing with someone who has a very limited background in managing large computer systems.
Non-techies - Oracle managers with a non-technical background (no degree in Information Systems or Computer science) love to use OEM because it allows them to do tasks that would normally be far beyond their ability level.
A seasoned DBA knows that a firm knowledge of shell scripting, OS commands and and native SQL and DDL is indispensible for managing multiple instances.
Read more here about tips for managing a server with multiple instances without relying on OEM:
more
Secrets for managing multiple instances
Advanced SQL tips - reading an execution plan
When Codd and Date created the relational data model, the execution plan was an afterthought, largely because the SQL optimizer was always supposed to generate the best execution plan, and hence, there was not real need to understand the internal machinations of Oracle execution plans.
However, in the real world, all SQL tuning experts must be proficient in reading Oracle execution plans and understand the steps within an explain plan and the sequence in which the steps are executed. To successfully understand an explain plan, you must be able to know the order that the plan steps are executed. Reading an explain plan is important for many reasons, and Oracle SQL tuning experts use the explain plans to check many things. For example:
Reading SQL execution plans has always been difficult, but there are some tricks to help determine the correct order that the explain plan steps are executed.
Ordering the sequence of execution plan steps
SQL execution plans are interpreted using a preorder (reverse) transversal algorithm. This is a fancy way of saying:
1 - To read an execution plan, look for the innermost indented statement. That is generally the first statement executed.
2 - In most cases, if there are two statements at the same level, the first statement is executed first.
In other words, execution plans are read inside-out, starting with the most indented operation. There are some general rules for reading an explain plan.
Read on and learn how to read the sequence of steps in any SQL execution plan:
more
The Care and Feeding of a Standby Database
The Oracle Managed Standby Database is one of the most efficient and effective disaster recovery options for Oracle databases. The Managed Standby provides the capability to very quickly recover from the loss of a production database. It also can temporarily take over if the production database requires maintenance. Using an Oracle Managed Standby database can allow the DBA to provide near 24/7 availability of the database, year in and year out.
The standby database can be either local or in a remote location. Many shops have both a local and a remote standby. The local is to support the management of the primary, and the remote supports disaster recovery. One primary database can have up to nine standby databases.
Find the steps critical to maintaining a healthy standby database:
more
Be careful with Oracle silver bullets
Oracle has codified the "silver bullet" approach to Oracle tuning with the Oracle Real Application Testing (RAT) facility. RAT is used along with the SQL Performance Analyzer to provide a complete framework for system-wide testing of Oracle silver bullet changes.
RAT allows the DBA to perform holistic tests when introducing silver bullets, a way to ensure that the global change will have the maximum effect and not clobber the database.
A silver bullet is any single Oracle change that has a profound impact in system-wide performance, both good and bad!
Should you fail to optimize the system for its workload, SQL tuning can be undone with subsequent changes to any of the Silver Bullets, changes that could alter the execution plans of thousands of SQL statements! A silver bullet can be any of these Oracle changes:
- SGA pool sizes (shared_pool_size, db_32k_cache_size, etc.): Pool sizes have a dramatic effect on Oracle performance.
- Materialized Views: MV can pre-join popular tables together and prefetch commonly referenced summary data.
- Indexing : Intelligent indexing ensures SQL query completion with a minimum amount of buffer gets.
- Optimizer knobs (optimizer_mode, Optimizer_index_cost_adj): Changing these global parameters can have a profound impact on Oracle performance, both good and bad.
- I/O subsystem changes (RAID level, multiple blocksizes, SSD): I/O remains the most significant performance bottleneck in most production databases.
- Optimizer statistics : Applying CBO histograms can have a huge benefit to an entire system.
These "broad brush" settings change the way that Oracle processes data in a profound way, and a single change can either make you a hero or get you fired.
Click below to learn more about global tuning with Oracle Silver Bullets:
more