More than any other area of Oracle, teaching Oracle troubleshooting is extremely challenging. Troubleshooting is largely an on-the-job skill, and no amount of book learning can match the skill of an experienced Oracle troubleshooter.
Oracle has become the world's most sophisticated and flexible database, and the sheer complexity of Oracle makes it very difficult to troubleshoot.
So, how can you learn Oracle troubleshooting skills? First, it is important that students have a strong background in the internal machinations of Oracle and understand the underlying mechanisms of the Oracle engine. It is also important to recognize how Oracle alerts the DBA to problems. Finally, it is vital to know how to properly interpret error messages.
When an end user gets hung up with a process, the source of the problem could be system wise or it could be unique to their session. It is up to the Oracle DBA to understand the right questions to ask and know the right places to look for further information. The goals of Oracle troubleshooting include:
* Learn a troubleshooting methodology for use in analyzing any Oracle database
* Understand ratio-based and bottleneck troubleshooting analysis
* Learn techniques for monitoring and optimizing memory usage
* Know how to quickly pinpoint and resolve I/O hotspots at the database, storage and object level
* Using scripts to uncover session-related bottlenecks
* Understand techniques for locating and fixing problem SQL
There are also specific tip for Oracle troubleshooting that can guide a neophyte in the right direction:
* Always check the first error message - Oracle will frequently throw multiple errors, but it's always the first error that will lead you to the root cause of the problem.
* Root cause analysis is not always practical
* Know your OFA structure
* Don't be afraid to bounce
Click this link for detailed tips for the neophyte as well as other Oracle troubleshooting tips:
more
Oracle Troubleshooting tips and secrets
Find opportunities for materialized views
Oracle says that a well defined set of materialized views can be made to cover a whole schema, but that is easier said than done. Resourceful DBAs know about the SQLTuning Advisor, but the wisest are always on the lookout for techniques they can use to find all opportunities for materialized views.
Materialized views are one of the single most important SQL tuning tools.
They are a true silver bullet, allowing the DBA to pre-join complex views and pre-compute summaries for super fast response time.
Read more about how to use your existing workload to determine the best materialized view for super fast response time:
more
Inside Oracle Fully Automated SQL Tuning
Each new release of Oracle brings new tools and techniques for performance optimization, but it is a formidable challenge to create tools that can identify and correct database bottlenecks for every possible situation. I run a large remote DBA operation, monitoring and tuning mission-critical databases around the globe. With such a large base of experience, I am in a unique position to see hundreds of systems in action and understand the best approaches to achieving optimal performance.
Drawing upon my vast experience base, I will attempt to present the unvarnished truth about which Oracle tuning tools and techniques are the most effective. Oracle Corporation is a cheerleader for their product, saying that all of their features are wonderful; however, they are not always forthcoming about the risks and rewards of using new tools and techniques. For example, back in the last days of Oracle 7, word came forth from Oracle Corporation that the rule-based optimizer (RBO) was being removed from Oracle 8, and all shops must move quickly to adopt the cost-based optimizer (CBO).
The CBO was not quite ready for primetime, much to the consternation of those shops who had attempted to migrate to it back in Oracle 8. Even in 11g, we still see improvements to the CBO, and despite dire warnings that the RBO will disappear, RULE hints appear in hundreds of Oracle’s own internal SQL statements.
Read on for more facts about the evolution of Oracle’s tuning tools and techniques:
More
What's the Value of a Professional DBA?
In these recessionary times, it's tempting to cut corners and replace well-trained (and well compensated) DBA's with cheap foreign pretenders. It's penny-wise and pound-foolish behavior, sure to cause issues in the future. Oracle customers are often not aware of the inherent complexities of Oracle that make it the world's most robust and flexible data platform.
Penny wise and pound foolish:
You get what you pay for. If you want a DBA with an MBA and good technical experience, it can easily cost over $100,000 per year.
In the hands of a competent expert, Oracle gives a level of control over that data that is unprecedented, and a good DBA can control every aspect of their mission-critical database. In the hands of an inept reckless beginner, Oracle's powerful features become a weapon of destruction.
Untrained and inexperienced Oracle DBA's are the bane of corporate America. Oracle Corporation white papers suggest that more than 75% of all Oracle database outages are attributable to human error.
Read more on the value of a professional DBA here:
http://oracle-tips.c.topica.com/maamXYHabPbQ3bLGJrib/
Inside Adaptive Cursor Sharing
Oracle has improved cursor_sharing several times over the years. Remember, adaptive cursor sharing is only deployed in rare cases where a skewed column distribution (as noted by the histogram) indicates that a different execution plan would be faster. For example, a query with a popular bind variable value would be best served with a full table scan while an unpopular bind variable value would benefit from an index access plan.
But remember, this is a rare occurrence in many systems.
Based on extensive experience in the industry, it appears that about 80% of shops have uniformly distributed data. Large tables remain large, and the distribution of values within a column remain unchanged.
On the other hand, roughly 20% of databases experience highly volatile data loads, where tables are small on one day and huge the next, or cases where there is a "difference that makes a difference". In these databases, huge changes in the tables data (usually associated with high DML) changes the distribution of data values, necessitating a re-analysis of column histograms.
Read more about 11g adaptive cursor sharing:
http://oracle-tips.c.topica.com/maamW0iabO3zjbLGJrib/
Inside Oracle Cloud Computing
Cloud computing has been touted as a radical "paradigm shift", where IT power is delivered to an application over the internet as you need it, rather than drawn from a centralized server.
What they don’t want you to know is that cloud computing is a new name for a feature that’s almost half a century old! Back in the olden-days of data processing, customers rented all of the resources they needed, software, disk, CPU and RAM, all under the umbrella of a MVS mainframe, and they were billed according to their actual usage.
These cloud computing announcements in the media are complete nonsense, and the idea of "cloud computing" is nothing new. Back in the 1980’s, companies regularly rented usage of mainframe computers to third parties, and the mechanisms for apportioning shared computing resources have been around for almost half a century.
Read more about Oracle's implementation of cloud computing and learn the truth behind the hype:
http://oracle-tips.c.topica.com/maamVKuabOS1TbLGJrib/
Oracle 11g Data Compression Tips for the DBA
One of the exciting new features of Oracle 11g is their inline data compression utility. While it is true that data storage prices have fallen dramatically over the last decade, and continue to fall rapidly, Oracle data compression has far more appealing benefits than simply saving on disk storage cost. Because indexes and the data itself can be highly compressed, information can be fetched off of the disk devices with less physical IO, which radically improves query performance under certain conditions.
Let's take a closer look at how one would implement Oracle 11g Data Compression in order to achieve the optimal results.
Understanding data compression
Data compression techniques, such as the Huffman algorithm, have been around for nearly a century, but only today are being put to use within main stream information systems processing. Using these techniques, a decompression utility is called immediately upon the data block fetch. Within the Oracle data buffers, the fully uncompressed version of the data remains in the data buffers, even though the information remains compressed on the data blocks themselves. This leads to an anomaly between the size of information on the data blocks and the size of the information within the data buffers. Upon applying Oracle data compression, people will find that far more rows will fit on a data block of a given size, but there is still no impact on the data base management system from the point of view of the SGA (system global area). Because the decompression routine is called upon block fetch, the Oracle data buffers remain largely unchanged while the data blocks themselves tend
to have a lot more data on them.
Tests show that 11g compression results in slower transaction throughput but creates less writes because of higher row density on the data block. Overall, the benchmark slows that I/O writes being reduced while CPU increases, resulting in slowing SQL throughput:
* Slower transaction throughput – As we expect, Oracle transactions run faster without the encryption/decryption processing overhead. This encryption benchmark shows significantly slower throughput when deploying TDE, almost 20% (81 transactions/second with TDE, 121 transactions/second with TDE).
* Less Disk Writes – Since transparent data encryption compresses the data, the benchmark with TDE required less disk writes.
* More CPU required - As we would expert, TDE required CPU cycles for the encrypt/decrypt operations, and in this benchmark test we see User CPU rise from 46 to 80 when using TDE data encryption.
See more details on data compression here:
http://oracle-tips.c.topica.com/maamRxVabOkFObLGJrib/
Global SQL Optimization
True Oracle professionals will routinely undertake a holistic tuning approach prior to delving into the tuning of specific SQL statements. This holistic approach has been the bread and butter of successful DBAs since the earliest days of Oracle.
In Oracle 11g, this holistic approach is codified in the SQL Performance Analyzer (SPA), a new tool used to simplify the setting of optimizer statistics and initialization parameters. In the SPA, the DBA chooses a representative workload and runs it, comparing the overall SQL execution plans with different sets of CBO statistics and the settings for the silver bullet initialization parameters.
Click below to read more about Global SQL Optimization:
http://oracle-tips.c.topica.com/maamRxLabOkFabLGJrib/
Would you care to CTAS?
If you don't want to use dbms_redefinition, the Create Table As Select (CTAS) statement is one method for reorganizing an Oracle table or moving the table to another tablespace. Instead of spending hours setting up parameter files and job steps, you can copy and rename the table in three simple SQL statements. The CTAS statement can be used to change storage parameters for a table (INITIAL, NEXT, FREELISTS) and also change the physical sequence of the table rows. CTAS has the following syntax:
create table xxx_new
tablespace new_tablespace_name
storage (initial new_initial next new_next freelists new_freelist_number )
as
select * from xxx
order by primary_index_key_values;
There are several ways to execute CTAS to reorganize table; many of the options depend upon the version of Oracle and the particular configuration of the Oracle database.
Read more about the CTAS here:
http://oracle-tips.c.topica.com/maamRxyabOkDebLGJrib/
The High Cost of Oracle Data Breaches
Maintaining Oracle data security is essential, and all Oracle managers have a fiduciary responsibility to protect confidential information. The Oracle professional must be aware of the financial risk associated with inadvertent disclosure of their Oracle data.
Whether it's an external breach (hacker attack) or internal data management malfeasance (i.e. using an overseas remote DBA provider), disclosure of confidential data has expensive consequences, and these costs can be identified.
I’ve seen firsthand how choosing a bargain offshore Oracle remote DBA provider has lead to widespread data theft, leaving the customer with little or no legal recourse. Many Oracle shops choose their remote DBA provider within the USA because of our strict data protection statutes.
Read more about these risks, Oracle hacker horror stories, and data loss calculators as this article continues:
http://oracle-tips.c.topica.com/maamP6kabN8rNbLGJrib/