Anatomy of Query Optimizer

System statistics:
The query optimizer must know the power of the system it’s running on to provide accurate estimates


Object statistics:
Table, index, and column statistics, which are stored in the data dictionary, are essential because they describe the data stored in the database.


Constraints:
The query optimizer takes advantage of NOT NULL constraints, unique key constraints, primary key constraints, foreign key constraints, and some check constraints


Physical Design
There are three main physical design areas that have an effect on the query optimizer. First, Oracle Database offers five strategies to store data: heap-organized tables (this is the default), index-organized tables, external tables, index clusters, and hash clusters. In addition, heap-organized tables and index-organized tables can be partitioned. One or several access paths are associated to each strategy


SQL control
features like stored outlines, SQL profiles, and SQL plan baselines allow you to store in the data dictionary information that influences the decisions taken by the query optimizer while generating execution plans


Execution environment:
A set of initialization parameters controls the behavior of the query optimizer.
Bind variables: Bind variables has a strong influence on the execution plans generated by the query optimizer


Cardinality feedback(also called statistics feedback):
Either because of complex predicates or missing input information, the query optimizer can’t always compute accurate estimations.