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.
/ 9 