Databases Reference
In-Depth Information
of data. Performance speedup for star queries with multiple dimension tables can be
greatly improved since bitwise operations in star transformations can now be
eliminated.
Performing queries in parallel also obviously improves performance. Joins and sorts are
frequently used to solve business intelligence queries. Parallelism is described in
Chap‐
ter 7
. That chapter lists functions that Oracle can perform in parallel (see the section
“What Can Be Parallelized?” on page 187
).
Real Application Clusters, which replaced Oracle Parallel Server as of Oracle9
i
, further
expands parallelism by enabling queries to transparently scale across nodes in clusters
or in grids of computer systems.
Since Oracle Database 10
g
, statistics gathering automatically popu‐
lates the Automatic Workload Repository, an important source of in‐
formation for Oracle's cost-based optimizer. For example, the SQL
Access Advisor leverages this information when making tuning rec‐
ommendations. Oracle Database 12
c
provides support for adaptive
plans, query plans that are adjusted at run-time based on current da‐
ta. Adaptive statistics at run-time and compile-time in Oracle Data‐
base 12
c
enable optimization “learning” for future queries.
Optimization Provided by the Exadata Storage Server Software
The introduction of the
Oracle Exadata Database Machine
as an engineered system in
2008 enabled Oracle to define balanced hardware server and storage configurations that
are linked via a high-speed interconnect (InfiniBand). Today, Exadata contains powerful
Sun server components including those that provide the Exadata Storage Server cells.
The Exadata Storage Server software enables the Oracle Database to perform unique
query and analytics optimization in Exadata storage transparently to the applications.
Smart Scans offload query predicate evaluation to storage, performing row, column,
and join filtering in the Exadata Storage Server cells. During star join filtering provided
by Smart Scans, dimension table predicates are transformed into filters that are applied
to the fact table scan. Storage indexes in the Exadata Storage Server software further
assist by transparently keeping track of minimum and maximum values of columns
stored in tables on a cell, eliminating scan I/O if a WHERE clause in the query is outside
the bounds of those values. The optimizations are important in that they minimize both
the occurrence of much slower full table scans and the need for extensive indexing to
eliminate such full table scans.
Smart Scans transparently handle a variety of complex cases including uncommitted
data and locked rows, chained rows, compressed tables, national language processing,
date arithmetic, regular expression searches, and partitioned tables. Smart Scans also
