Oracle order by 排序最佳化

order by 排序對效能的影響

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

案例示範

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

alter system flush  shared_pool;

set autotrace traceonly explain stat;

select * from t3 where sid>90  ;

執行計畫

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

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)

統計資訊

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

        10  recursive calls

          4  db block gets

        10  consistent gets

          0  physical reads

        496  redo size

        818  bytes sent via SQL*Net to client

        519  bytes received 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;

執行計畫

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

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)

統計資訊

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

          9  recursive calls

          4  db block gets

          9  consistent gets

          1  physical reads

        540  redo size

        818  bytes sent via SQL*Net to client

        519  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          1  sorts (memory)  --有排序

          0  sorts (disk)

        10  rows processed

可以看出CPU發生變化,如果排序語句很多的情況下,效能影響更大.

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

解決辦法

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

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;

執行計畫

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

lan hash value: 243714934

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

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

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

  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 |

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

redicate Information (identified by operation id):

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

  2 - access(SYS_OP_DESCEND("SID")<HEXTORAW('3EA4FF') )

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

ote

----

  - SQL plan baseline "SQL_PLAN_78qgapzz4mwhwd7223dec" used for this statement

統計資訊

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

        0  recursive calls

        0  db block gets

        4  consistent gets

        0  physical reads

        0  redo size

      818  bytes sent via SQL*Net to client

      519  bytes received via SQL*Net from client

        2  SQL*Net roundtrips to/from client

        0  sorts (memory)  --無排序

        0  sorts (disk)

        10  rows processed