|
Column histograms should be created only when you have highly-skewed values in a column. Histograms tell the CBO when a column's values aren't distributed evenly, and the CBO will then use the literal value in the query's WHERE clause and compare it to the histogram statistics buckets. The SQL*Plus auto trace is another method to get actual execution plan with some additional statistics. When set to ON automatically display execution plan after each SQL statement with Statistics SQL Trace and TKPROF let you to accurately assess the efficiency of the SQL statements when an application runs. For best results, use these tools with EXPLAIN PLAN rather than using EXPLAIN PLAN alone. When a SQL query is submitted to an Oracle database, Oracle query optimizer decide how to access the data. The process of making this decision is called query optimization, Oracle looks for the optimal way to retrieve the data, using the execution path. |
Materialized view allow us to pre-join complex views and pre-compute summaries for super-fast response time
An execution plan is the sequence of operations Oracle performs to run the statement. The EXPLAIN PLAN statement displays execution plans chosen by the Oracle optimizer for SELECT, UPDATE, INSERT, and DELETE statements. It is goog idea to start with system-level SQL tuning, else later changes might undo your tuned execution plans. Optimize the server kernel - Always tune your disk and network I/O subsystem (RAID, DASD bandwidth, network) to optimize the I/O time, network packet size and dispatching frequency. |
