Database Tuning (MOSC)

MOSC Banner

SORT ORDER BY

edited Mar 1, 2012 2:50AM in Database Tuning (MOSC) 4 commentsAnswered
Hi,
In the database 11.2.0.2

I have the following query :-

SELECT /*+ INDEX( cro_1_053 IX_CRO_EXTR_1_R9_BIS)*/ cod_pays FROM CRO_1_053 WHERE num_gen = 405 AND nom_fich = 'GDG' ORDER BY date_heure_cro,champ_perso_0;
----------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                      |     1 |    27 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| CRO_1_R9             |     1 |    27 |     3   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IX_CRO_EXTR_1_R9_BIS |   904 |       |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------


This is the GOOD execution plan,now sometimes i get this execution plan as below which is BAD :-

----------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                      |   904 | 24408 |   641   (1)| 00:00:08 |
|   1 |  SORT ORDER BY               |                      |   904 | 24408 |   641   (1)| 00:00:08 |
|   2 |   TABLE ACCESS BY INDEX ROWID| CRO_1_R9             |   904 | 24408 |   640   (1)| 00:00:08 |

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center