Cost based optimizer approach

Hi,
Performance tuning of Database and queries is very important from DBA's point of view it is no-doubt very vast and many people say it is a deep ocean and tuning can
be done to unlimited extent when we really know our data very well but many times
developers and designers will be having details of data in a better way so tuning as per me is a combined effort where DBA's,developers and Database Designers work together to make a well tune system.In the below example I'm starting with the basics
but in my future posts I will surely put some real time tuning techniques in a detailed way.
We all know how important is the 'optimizer' as depending on setting
of the optimizer and statistics the query will execute better.We all know there are two types of optimizers
1)Rule-based optimizer
2)cost-based optimizer

Let us start with the below example:

For checking the optimizer mode we can use the below approach:

SELECT * FROM V$PARAMETER WHERE NAME = 'optimizer_mode';
(OR)
SQL> show parameter %OPTIMIZER%

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_capture_sql_plan_baselines boolean FALSE
optimizer_dynamic_sampling integer 2
optimizer_features_enable string 11.2.0.1
optimizer_index_caching integer 0
optimizer_index_cost_adj integer 100
optimizer_mode string ALL_ROWS

This will tell us the mode for the database. If the value is 'CHOOSE' than the
database is in Cost-Based.However,if there are no statistics on the tables
that a query is based upon, then it is in RULE mode.

To tell for a particular query,we will need to do an EXPLAIN PLAN on the
query. If we see costs associated with the query, then the query is in
COST-BASED mode. Otherwise it is in RULE-BASED mode.

Note 1: we can use the RULE hint to force rule-based mode on any query.
Note 2:
1) Possible values for optimizer_mode = choose/ all_rows/ first_rows/ first_rows[n]
2) The ALL_ROWS optimizer mode uses a cost-based approach to determine the best access path to access your tables.

I'm not focusing on rule-based much as it is not suggested much to use.

Eg:

SQL> conn schema_test
Enter password:
Connected.
SQL> select *from TTTT;

no rows selected

SQL> SET AUTOTRACE TRACEONLY;
SQL> select *from TTTT;

no rows selected


Execution Plan
----------------------------------------------------------

----------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
----------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 2 |
| 1 | TABLE ACCESS FULL| TTTT | 1 | 13 | 2 |
----------------------------------------------------------

Note
-----
- 'PLAN_TABLE' is old version


Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
330 bytes sent via SQL*Net to client
512 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed



Hope it helps...

Happy Oracle Performance tuning learning...

Best regards,

Rafi.