Database Articles
  Home arrow Database Articles arrow Page 5 - More on Query Optimization for Oracle Databases
Codewalker Forums 
  Tutorials  
Database Articles  
Miscellaneous  
Navigation Usability  
PEAR Articles  
Programming Basics  
Server Administration  
XML Tutorials  
  Reviews  
Database Book Reviews  
Linux Book Reviews  
Miscellaneous Reviews  
PHP Book Reviews  
PHP Software Reviews  
Server Admin Reviews  
SQL Tool Reviews  
  Code Gallery  
Content Management Code  
Contest Code  
Counters Code  
Database Code  
Date Time Code  
Discussion Board Code  
Email Code  
File Manipulation Code  
GUI Code  
Link Farm Code  
Miscellaneous Code  
Search Code  
Site Navigation Code  
User Management Code  
Forums Sitemap 
Dedicated Servers  
Download TestComplete 
IBM® developerWorks
Weekly Newsletter 
 
Developer Updates  
Free Website Content 
 RSS  Articles
 RSS  Forums
 RSS  All Feeds
Write For Us Get Paid 
Request Media Kit
Contact Us 
Site Map 
Privacy Policy 
Support 
 USERNAME
 
 PASSWORD
 
 
  >>> SIGN UP!  
  Lost Password? 
DATABASE ARTICLES

More on Query Optimization for Oracle Databases
By: O'Reilly Media
  • Search For More Articles!
  • Disclaimer
  • Author Terms
  • Rating: 5 stars5 stars5 stars5 stars5 stars / 5
    2008-03-27

    Table of Contents:
  • More on Query Optimization for Oracle Databases
  • Specifying an Optimizer Mode
  • Saving the Optimization
  • Performance and Optimization
  • SQL Advisors

  • Rate this Article: Poor Best 
      ADD THIS ARTICLE TO:
      Del.ici.ous Digg
      Blink Simpy
      Google Spurl
      Y! MyWeb Furl
    Email Me Similar Content When Posted
    Add Developer Shed Article Feed To Your Site
    Email Article To Friend
    Print Version Of Article
    PDF Version Of Article
     
     
    ADVERTISEMENT

    Stay one step ahead of the competition. Evaluate and give feedback on some of the hottest web development tools on the market today. Make your opinion heard! Click Here

    More on Query Optimization for Oracle Databases - SQL Advisors


    (Page 5 of 5 )

     

    Oracle Database 10g added a tool called the SQL Tuning Advisor. This tool performs advanced optimization analysis on selected SQL statements, using workloads that have been automatically collected into the Automatic Workload Repository or that you have specified yourself. Once the optimization is done, the SQL Tuning Advisor makes recommendations, which could include updating statistics, adding indexes, or creating a SQL profile. This profile is stored in the database and is used as the optimization plan for future executions of the statement, which allows you to “fix” errant SQL plans without having to touch the underlying SQL.

    The tool is often used along with the SQL Access Advisor since that tool provides advice on materialized views and indexes. Oracle Database 11g introduces a SQL Advisor tool that combines functions of the SQL Tuning Advisor and the SQL Access Advisor (and now includes a new Partition Advisor). The Partition Advisor component advises on how to partition tables, materialized views, and indexes in order to improve SQL performance.

    Data Dictionary Tables

    The main purpose of the Oracle data dictionary is to store data that describes the structure of the objects in the Oracle database. Because of this purpose, there are many views in the Oracle data dictionary that provide information about the attributes and composition of the data structures within the database.

    All of the views listed in this section actually have three varieties, which are identified by their prefixes:

    DBA_
      
    Includes all the objects in the database. A user must
       have DBA privileges to use this view.

    USER_
      
    Includes only the objects in the user’s own database
       schema.

    ALL_ 
       
    Includes all the objects in the database to which a
       particular user has access. If a user has been
       granted rights to objects in another user’s schema,
       these objects will appear in this view.

    This means that, for instance, there are three views that relate to tables: DBA_TABLES, USER_TABLES, and ALL_TABLES.

    Some of the more common views that directly relate to the data structures are described in Table 4-2.

    Table 4-2. Data dictionary views about data structures

    Data dictionary view Type of information
    ALL_TABLES Information about the object and relational tables
    TABLES Information about the relational tables
    TAB_COMMENTS Comments about the table structures
    TAB_HISTOGRAMS Statistics about the use of tables
    TAB_PARTITIONS Information about the partitions in a partitioned table
    TAB_PRIVS* Different views detailing all the privileges on a table, the privileges granted by the user, and the privileges granted to the user
    TAB_COLUMNS Information about the columns in tables and views
    COL_COMMENTS Comments about individual columns
    COL_PRIVS* Different views detailing all the privileges on a column, the privileges granted by the user, and the privileges granted to the user
    LOBS Information about large object (LOB) datatype columns
    VIEWS Information about views
    INDEXES Information about the indexes on tables
    IND_COLUMNS Information about the columns in each index
    IND_PARTITIONS Information about each partition in a partitioned index
    PART_* Different views detailing the composition and usage patterns for partitioned tables and indexes
    CONS_COLUMNS Information about the columns in each constraint
    CONSTRAINTS Information about constraints on tables
    SEQUENCES Information about sequence objects
    SYNONYMS Information about synonyms
    TAB_COL_STATISTICS Statistics used by the cost-based analyzer
    TRIGGERS Information about the triggers on tables
    TRIGGER_COLS Information about the columns in triggers
     


    DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware.

       · This article is an excerpt from the book "racle Essentials, Fourth Edition Oracle...
     

    Buy this book now. This article is excerpted from chapter four of the book Oracle Essentials, Fourth Edition Oracle Database 11g, written by Rick Greenwald, Robert Stackowiak, and Jonathan Stern (O'Reilly, 2007; ISBN: 0596514549). Check it out today at your favorite bookstore. Buy this book now.

    DATABASE ARTICLES ARTICLES

    - More on Query Optimization for Oracle Databa...
    - Query Optimization in Oracle
    - Clusters and Other Data Structures for Oracle
    - Using Indexes with an Oracle Database
    - The Basics of Data Structures in Oracle
    - Oracle Data Structures
    - Best Practices for PL/SQL Variables
    - What`s Code Without Variables?
    - Clauses, Sorting, and SQL Queries
    - The From Clause and SQL Queries
    - Query Primer
    - Full Text Searches and Strings
    - Searching with Strings
    - Pattern Matching with Strings
    - Working with Cases of Strings






    © 2003-2008 by Developer Shed. All rights reserved. DS Cluster 2 hosted by Hostway