Home » RDBMS Server » Performance Tuning » Performance Tuning
Performance Tuning [message #64675] Fri, 19 December 2003 05:55 Go to next message
Vinod Nair
Messages: 6
Registered: December 2003
Junior Member
1. Why do we require tuning since Oracle internally
use the low cost approach for finding the access
path?

2. what is the filter operation in explain plan?
3. What does Oracle actually does on
Analyze Table command and Analyze index command.
4. What is the projection view in Oracle
5. Which join is applied in which conditions
please explain?
6. In which situation NOSORT option is used, bcoz
in some case "sort group by NO SORT " is used
when using index and in some cases NO SORT is
not used.
Re: Performance Tuning [message #64677 is a reply to message #64675] Fri, 19 December 2003 09:08 Go to previous message
Thiru
Messages: 1089
Registered: May 2002
Senior Member
1.Choosing the right access path is just one of the many things that constitute 'Oracle tuning' (ie there are so many things that the optimizer cant/doesnt do(eg sizing shared pool,setting up statistics etc). Even in there,the optimizer is not perfect ,although it has made tremendous progress over the past few years.

2.Filter : eliminate(filter out) rows defined by the where clause

3. computes various statistics(eg NUM_ROWS,CHAIN_CNT,BLOCKS,LEAF_BLOCKS,BLEVEL etc) and populates data dictionary views (dba_tables/dba_indexes etc) accordingly.
Analyze ..Validate structure checks table integrity.

4.Projection is basically selecting columns.Are you talking about select-project-join views ?

5.huge question. Here's an extract from the documentation :
"A nested loop join is inefficient when a join returns a large number of rows (typically, more than 10,000 rows is considered large), and the optimizer might choose not to use it. The cost of a nested loop join is calculated by the following formula:
cost= access cost of A + (access cost of B * number of rows from A)

If you are using the CBO, then a hash join is the most efficient join when a join returns a large number or rows. The cost of a hash join is calculated by the following formula:
cost= (access cost of A * number of hash partitions of B) + access cost of B

If you are using the RBO, then a merge join is the most efficient join when a join returns a large number or rows. The cost of a merge join is calculated by the following formula:
cost= access cost of A + access cost of B +(sort cost of A + sort cost of B)
"

It depends on various factors such as presence of indexes , group by/order by clauses , optimizer settings , hash_join settings , pga settings , hints etc.

6.When the data is already sorted,the index can be created with NOSORT option. CBO picks up SORT or NOSORT depending on how the indexed column is used.

-- Table is loaded by object_name order

SQL> create table t as select * from all_objects order by object_name;

Table created.

-- Index created with NOSORT

SQL> create index t_idx on t(object_name) NOSORT;

Index created.

SQL> analyze table t compute statistics;

Table analyzed.

SQL> set autotrace on

SQL> select count(*),object_name from t where object_name < 'A' and rownum < 10 group by object_name;

COUNT(*) OBJECT_NAME
---------- ------------------------------
2 /1005bd30_LnkdConstant
2 /10076b23_OraCustomDatumClosur
2 /1025308f_SunTileScheduler
2 /10297c91_SAXAttrList
1 /103a2e73_DefaultEditorKitEndP

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=36 Card=9 Bytes=207)
1 0 SORT (GROUP BY NOSORT) (Cost=36 Card=9 Bytes=207)
2 1 COUNT (STOPKEY)
3 2 INDEX (RANGE SCAN) OF 'T_IDX' (NON-UNIQUE) (Cost=36 Ca
rd=7086 Bytes=162978)

-- CBO uses NOSORT grouping becos of object_name < 'A'

SQL> select count(*),object_name from t where rownum < 10 group by object_name;

COUNT(*) OBJECT_NAME
---------- ------------------------------
2 /1005bd30_LnkdConstant
2 /10076b23_OraCustomDatumClosur
2 /1025308f_SunTileScheduler
2 /10297c91_SAXAttrList
1 /103a2e73_DefaultEditorKitEndP

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=129 Card=9 Bytes=207
)

1 0 SORT (GROUP BY) (Cost=129 Card=9 Bytes=207)
2 1 COUNT (STOPKEY)
3 2 INDEX (FAST FULL SCAN) OF 'T_IDX' (NON-UNIQUE) (Cost=1
5 Card=29126 Bytes=669898)

-- CBO uses SORTed Group by here without the filtering condition on the NOSORTed Index column.

-Thiru
Previous Topic: explain plan
Next Topic: View
Goto Forum:
  


Current Time: Thu Sep 24 16:33:37 CDT 2020