Oracleorderby sorting Optimization

The impact of orderby sorting on performance -********************************* ** case study -*********************************** altersyste

Influence of order by sorting on performance -******************************** * ** case study -********************************** * alter lead E

Homepage → Database Technology

Background:

Read News

Oracle order by sorting Optimization

[Date: 2013-06-26] Source: Linux community Author: ocpyang [Font:]

Effect of order by sorting on Performance

-***********************************

Case Study

-***********************************

Alter system flush shared_pool;

Set autotrace traceonly explain stat;

Select * from t3 where sid> 90;

Execution Plan

----------------------------------------------------------

Plan hash value: 4161002650

--------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |

--------------------------------------------------------------------------

| 0 | select statement | 10 | 330 | 2 (0) | 00:00:01 |

| * 1 | table access full | T3 | 10 | 330 | 2 (0) | 00:00:01 |

--------------------------------------------------------------------------

Predicate Information (identified by operation id ):

---------------------------------------------------

1-filter ("SID"> 90)

Note

-----

-Dynamic sampling used for this statement (level = 2)

Statistics

----------------------------------------------------------

10 recursive CILS

4 db block gets

10 consistent gets

0 physical reads

496 redo size

818 bytes sent via SQL * Net to client

519 bytes encoded ed via SQL * Net from client

2 SQL * Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

10 rows processed

Select * from t3 where sid> 90 order by sid desc;

Execution Plan

----------------------------------------------------------

Plan hash value: 1749037557

---------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |

---------------------------------------------------------------------------

| 0 | select statement | 10 | 330 | 3 (34) | 00:00:01 |

| 1 | sort order by | 10 | 330 | 3 (34) | 00:00:01 |

| * 2 | table access full | T3 | 10 | 330 | 2 (0) | 00:00:01 |

---------------------------------------------------------------------------

Predicate Information (identified by operation id ):

---------------------------------------------------

2-filter ("SID"> 90)

Note

-----

-Dynamic sampling used for this statement (level = 2)

Statistics

----------------------------------------------------------

9 recursive cballs

4 db block gets

9 consistent gets

1 physical reads

540 redo size

818 bytes sent via SQL * Net to client

519 bytes encoded ed via SQL * Net from client

2 SQL * Net roundtrips to/from client

1 sorts (memory) -- sorting

0 sorts (disk)

10 rows processed

It can be seen that the CPU has changed. If there are many sort statements, the performance will be more affected.

-***********************************

Solution

-***********************************

Create index index_sid on t3 (sid desc );

Exec dbms_stats.gather_table_stats ('sys ', 't3', cascade => TRUE );

Select * from t3 where sid> 90 order by sid desc;

Execution Plan

---------------------------------------------------------

Lan hash value: 243714934

Bytes ----------------------------------------------------------------------------------------

Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |

Bytes ----------------------------------------------------------------------------------------

0 | select statement | 10 | 140 | 2 (0) | 00:00:01 |

1 | table access by index rowid | T3 | 10 | 140 | 2 (0) | 00:00:01 |

* 2 | index range scan | INDEX_SID | 1 | 1 (0) | 00:00:01 |

Bytes ----------------------------------------------------------------------------------------

Redicate Information (identified by operation id ):

--------------------------------------------------

2-access (SYS_OP_DESCEND ("SID ")

Filter (SYS_OP_UNDESCEND (SYS_OP_DESCEND ("SID")> 90)

Ote

----

-SQL plan baseline "SQL _PLAN_78qgapzz4mwhwd7223dec" used for this statement

Statistics

---------------------------------------------------------

0 recursive cballs

0 db block gets

4 consistent gets

0 physical reads

0 redo size

818 bytes sent via SQL * Net to client

519 bytes encoded ed via SQL * Net from client

2 SQL * Net roundtrips to/from client

0 sorts (memory) -- no sorting

0 sorts (disk)

10 rows processed

0

Initialize the bash shell script for Oracle users and tablespaces

IMP/EXP data migration (2)

Oracle order