HOW THE RULE-BASED OPTIMIZER WORKS

But first, let’s start at the beginning…
The rule-based optimizer (RBO) has only a small amount of information to use in deciding upon an execution plan for a SQL statement:
• The text of the SQL statement itself
• Basic information about the objects in the SQL statement, such as the tables, clusters, and views in the FROM clause and the data type of the columns referenced in the other clauses
• Basic information about indexes associated with the tables referenced by the SQL statement
• Data dictionary information is only available for the local database. If you’re referencing a remote database, the remote dictionary information is not available to the RBO…
In order to determine the execution plan, the RBO first examines the WHERE clause of the statement, separating each predicate from one another for evaluation, starting from the bottom of the statement. It applies a score for each predicate, using the fifteen access methods ordered by their alleged merit:
1. Single row by ROWID
2. Single row by cluster join
3. Single row by hash cluster key with unique key
4. Single row by unique index
5. Cluster join
6. Hash cluster key
7. Indexed cluster key
8. Composite key
9. Single-column non-unique index
10. Bounded range search on indexed columns
11. Unbounded range search on indexed columns
12. Sort-merge join
13. MAX or MIN of indexed column
14. ORDER BY on indexed columns
15. Full table-scan




Suggest you check out:

www.evdbt.com/SearchIntelligenceCBO.doc

which is a pretty good paper on this topic.

Optimizer Settings

o optimizer_index_caching - percentage of blocks expected to be found in the buffer cache during an index hit. default of 0 implies that every (logical) LIO is a (physical) PIO.

o optimizer_index_cost_adj - represents relative cost of PIO's for indexed access vs full scan. Default value of 100 indicates that an indexed access is just as costly as a full
access.

Trace Oracle Command

How to Trace Oracle Command

export ORACLE_TRACE=T

ps command - Shows current status of processes

Linux

ps -p XXXX -o %cpu,pid,user,state,start,time,etime,%cpu,%mem,cmd

ps h -e -o %cpu,pid,user,state,start,time,etime,%cpu,%mem,cmd|sort -rn|more

AIX

ps augxww | head

Solaris

/usr/ucb/ps uaxw| more

RAC Commands

find out which NIC (private obviously) was used for Cache Fusion

SQL> oradebug setmypid
SQL> oradebug ipc
SQL> oradebug tracefile_name

Nodes in cluster

$ORA_CRS_HOME/bin/olsnodes -n

CRS

see CRS cheat Sheet

Storage -- List of rawdevices

cat /etc/sysconfig/rawdevices

OCR

see OCR Cheat Sheet

Voting disk

see Voting Disk Cheat Sheet