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