Oracle Optimizer Operations
- Evaluation of expressions and conditions
- Statement transformation
- Choice of optimizer approaches
- Choice of access paths
- Choice of join orders
- Choice of join methods
Oracle Hash Join and nested join
- In a nested loop join, the database reads and index, builds a list of ROW ID’s and then probes into the second table for the matching rows.
-In a hash join, the database does a full-scan of the driving table, builds a RAM hash table, and then probes for matching rows in the other table. For certain types of SQL
-The propensity of the SQL optimizer to invoke a hash join is heavily controlled by the setting for the
hash_area_size Oracle parameter
Oracle Plan Table
use UTLXPLAN.SQL to create PLAN_TABLE
use UTLXPLS.SQL to generate report.
use command "explain plan for my-sql-statement"
or "explain plan into table_name for my-sql-statement" to generate execution plan
The fields (attributes) within the plan table
- the most important fields with in plan table are:
operation, option, object_name, id and
parent_idselect substr (lpad(' ', level-1) operation ' (' options ')',1,30 ) "Operation", object_name "Object"
from plan_table
start with id = 0
connect by prior id=parent_id;
Oracle SQL Processing
sql processing use the following main components to execute a SQL query
- The parser checks both syntax and semantic analysis
- The Optimizer uses costing methods, cost-based optimizer(CBO), or internal rules, rule-based optimizer(RBO), to determine the most efficient way of producing the result of the query.
- The Row Source Generator receives the optimal plan from the optimizer and outputs the execution plan for the SQL statement.( execution plan defines how oracle finds or writes the data. For example, an important decision that Oracle has to take is if it uses indexes or not. And if there are more indexes, which of these is used.)
- The SQL Execution Engine operates on the execution plan associated with a SQL statement and then produces the results of the query.
oracle how to retrieve sid information
Method 1 (for Oracle 7.x and up)
SELECT sid FROM V$SESSION WHERE audsid = userenv('sessionid');
Method 2 (for Oracle 8i and up - uses new function SYS_CONTEXT)
SELECT sid FROM V$SESSION WHERE audsid = SYS_CONTEXT('userenv','sessionid');
Analyze snappack output on oraperf website
http://www.oraperf.com/login: jamesp
> set line 500
> set serveroutput on
> execute oraperf.analyze_lio(10,2);
Server log monitoring
use logsurfer or swatch to monitor server log files.
