Indexes are the easiest way to improve the performance of long running queries with full table scans. Indexes allow the database to search the smaller indexes as opposed to searching the large table. This can improve not only the SELECT queries, but also UPDATEs and DELETEs.
Finding the most efficient index is difficult. Most indexes will be the default B_Tree type. This type of index has been in use for years and has been highly optimized.
Having too many indexes can slow down performance of UPDATEs and DELETEs. Use too few indexes, and all types of queries may run slower, even UPDATEs and DELETEs. If it were just a trade off, performance of UPDATEs and INSERTs versus performance of SELECTs, optimizing indexes in a database would be fairly easy. However, it is not that simple and finding the right balance is the challenge.
Learn secrets for finding balance when index planning:
http://oracle-tips.c.topica.com/maam5JAabQj2zbLGJrib/
*****************************************
Can you see your Oracle bottlenecks?
After several years of hard work, Ion for Oracle is finally available for general release. Ion is unlike any other Oracle tool on the market, a tool that encapsulates expert techniques to allow a DBA to quickly visualize important trends and signatures.
Predictive analytics if the key to repairing Oracle problems before they cripple your database, and I created Ion for Oracle to allow any DBA to quickly identify changing workloads and unobtrusive patterns of data access. Follow the link to try Ion for free:
http://oracle-tips.c.topica.com/maam5JAabQj2AbLGJrib/
Indexing....The Good, The Bad, and the Ugly
Finding trends and signatures for proactive tuning
One shortcoming with reactive tuning approaches like Oracle's automatic memory management (AMM) is that it is reactive, and it waits until a problem is signaled before taking remedial actions. To be truly proactive, we must develop techniques that will analyze repeating trends and identify processing signatures.
These trends, in turn, will predict changes in workload characteristics. If you know in-advance when a workload is going to change, you can adjust the SGA just in time to accommodate the new workload, fixing the issue before it bothers your end-user community.
This is the idea behind creating a self-tuning Oracle database, a proactive technique that I've been working on for over a decade.
To get started, you must use AWR or STATSPACK queries to identify changing workloads, analyzed in a linear regression, as well as by hour of the day and day of the week. Once you do this, "signatures" will become apparent, and you will see when your workload changes, the first step in proactive self-tuning for an Oracle database.
I am excited to announce that after years of work, I have finally completed a comprehensive tool for identifying hidden trends and signatures, Ion for Oracle. Ion uses applied artificial intelligence to remove the tedium from proactive analysis, leaving you free to do the intuitive work:
http://oracle-tips.c.topica.com/maam4PSabQcc1bLGJrib/
Special Report: The Oracle - Sun deal
Oracle's announcement of its acquisition of Sun Microsystems will provide it with a complete lineup of hardware and software technologies -- an advantage few companies can boast. Stay tuned to SearchOracle.com for all of the latest headlines circulating around the exciting acquisition.
Check out our Special Report, offering a complete round-up of all the latest news stories buzzing around Oracle’s big move:
http://oracle-tips.c.topica.com/maam385abP6qObLGJrib/
Read news articles from across the TechTarget network including:
* Oracle-Sun combo: What does it mean for enterprise Java?
* Oracle-Sun acquisition raises MySQL, hardware concerns
* Analysts see Oracle-Sun deal as storage 'game changer'
* In Oracle-Sun deal, analysts predict identity management fallout
* VARs turn wary eye on Sun-Oracle combo
Access this Special Report today to stay up-to-date on all aspects of the latest Oracle acquisition:
http://oracle-tips.c.topica.com/maam385abP6qObLGJrib/
Experts tricks for Oracle SQL Tuning
The Oracle cost-based SQL optimizer (the Cost Based Optimizer - CBO) is one of the world's most complex software packages ever created. Imagine, writing a program that will always generate the "best" execution plan for any SQL statement. Oracle's implementation of SQL optimization is the best in the world, and along with this great power and flexibility comes great complexity.
Oracle SQL tuning is phenomenally complex, but there are some secrets that can help you succeed. Here are a few tricks that they do not teach you in Oracle University:
- Do the system-level tuning first - Setting the optimizer parms properly (especially optimizer_mode, optimizer_index_cost_adj, and optimizer_index_caching) can tune thousands of SQL statements in a single action.
- Try a /*+ RULE */ hint - The first thing I do when tuning a query is to invoke the rule-based optimizer. If the Rule Based Optimizer (RBO) generates a fast plan, then you know that it is not a problem with missing indexes, and you can focus on sub-optimal schema statistics (especially histogram).
- Choose the optimizer_mode wisely - The default optimizer mode of all_rows is designed to minimize computing resources. If you want to optimizer your SQL for fast response time, you need first_rows (or first_rows_10, first_rows_100).
- Avoid hints whenever possible - In Oracle, hints are a last resort, and you can adjust your CBO statistics (with dbms_stats) and use histograms to reproduce the execution plan from a hint. This has the side benefit of tuning other SQL statements as well!
- Use Histograms wisely - Oracle sometimes makes incorrect "guesses" (cardinality estimates), resulting in incorrect index usage and improper table join order. Make sure to deploy histograms to resolve these issues. Here are my notes on tuning with histograms: link
Remember, adjusting initialization parms, adding missing indexes and re-computing schema statistics can impact thousands of SQL statements, reducing the amount of manual SQL tuning. These system-wide approaches are codified in the 11g SQL Performance Analyzer, and many of these "silver bullets" are described in the book "Oracle Silver Bullets"
http://oracle-tips.c.topica.com/maam3F4abP15obLGJrib/
To learn more secrets for SQL tuning, see my book Oracle Tuning: The Definitive Reference:
more