Database Articles

  Home arrow Database Articles arrow Page 5 - More on Query Optimization for Oracle ...
DATABASE ARTICLES

More on Query Optimization for Oracle Databases
By: O'Reilly Media
  • Search For More Articles!
  • Disclaimer
  • Author Terms
  • Rating: 4 stars4 stars4 stars4 stars4 stars / 9
    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

  •  
     

    SEARCH CODEWALKERS

    TOOLS YOU CAN USE

    advertisement

    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.
    blog comments powered by Disqus

    DATABASE ARTICLES ARTICLES

    - Completing a Book Inventory Management System
    - Uploading Images for a Book Inventory Manage...
    - Finishing the Add Book Story for a Book Inve...
    - Integration Testing for a Book Inventory Man...
    - User Stories for a Book Inventory Management...
    - Unit Testing a Book Inventory Management Sys...
    - Testing a Book Inventory Management System
    - Implementing Models for a Book Inventory Man...
    - Book Inventory Application: Publishers and B...
    - Handling Publishers in a Book Inventory Mana...
    - Publisher Administration for Book Inventory ...
    - Book Inventory Management
    - Using the SQL Reference Manual
    - Using Oracle SQL Developer with SQL Statemen...
    - Fixing Errors with Oracle SQL Developer


    © 2003-2012 by Developer Shed. All rights reserved. DS Cluster 10 - Follow our Sitemap