Oracle Training Oracle Tips Oracle Forum Class Catalog Remote DBA Oracle Tuning Emergency 911 RAC Support Apps Support Analysis Design Implementation Oracle Support ![]()
Optimal intermediate row sets. Oracle histograms and cardinality
Histograms are used to predict cardinality and the number of rows returned to a query. Let's assume that we have a vehicle_type index and that 65 percent of the values are for the CAR type. Whenever a query with where vehicle_type = 'CAR' is specified, a full-table scan would be the fastest execution plan, while a query with where vehicle_type = 'TRUCK' would be faster when using access via an index.
Histograms affect performance and should only be used when they are required for a faster CBO execution plan. They incur additional overhead during the parsing phase of an SQL query. Histograms can be used effectively only when:
Tools to assist in Optimizer cardinality estimation
There are many tools to assist with SQL tuning, but the best tools will expose all of the internal metrics of the data dictionary. The Ion tool does a great job at aiding SQL tuning:
The Ion tool is an easy way to analyze Oracle SQL performance and Ion also allows you to spot hidden SQL performance trends.
Also see these related notes on cardinality estimation:
![]()
Query Processing and Cardinality Estimation in Modern Database Systems. A Dissertation submitted in. 5.4.1 Software Implementations.
![]() Comments are closed.
|
AuthorWrite something about yourself. No need to be fancy, just an overview. Archives
December 2022
Categories |