This discussion is archived
10 Replies Latest reply: Nov 14, 2012 8:30 AM by BSalesRashid RSS

Index Clustering_factor issue

BSalesRashid Explorer
Currently Being Moderated
Hi people,

We are using Oracle RAC 10.2.0.5 with 3 nodes, and Oracle Linux with kernel 2.6.18-92.1.13.el5

I am having some trouble with a process that usually run in 3 minutes; But today it already took more than 1 hour and did not finished yet.

Here is the explain plan.

PLAN_TABLE_OUTPUT
----
select C.EMISOR ,C.SUCURSAL_EMISOR ,C.PRODUCTO ,C.NUMERO_CUENTA ,C.TIPO_DE_D
,C.DOCUMENTO ,C.GRUPO_AFINIDAD ,C.ESTADO ,C.FECHA_ESTADO ,C.ENVIAR_RESUMEN
,C.COBRA_FRANQUEO ,C.CIERRE ,C.LIMITE_CREDITO ,C.FECHA_LIMITE ,C.TIPO_MONEDA
,C.BONIFICACION_TASAS ,C.FECHA_NO_RENOVACION ,C.CAUSA_NO_RENUEVA ,C.PUBLICA_N
,C.AUTOMATICO_A_BOLETIN ,C.SALDO_CIERRE_ML ,C.SALDO_CIERRE_MR ,C.PAGO_MINIMO_
,C.PAGO_MINIMO_MR ,C.COTIZACION ,C.ULTIMO_VTO_IMPAGO ,C.PAGOS_ML ,C.PAGOS_MR
,C.PAGOS_C_ML ,C.PAGOS_C_MR ,C.AJUSTES_C_M_CRED ,C.INTERESES_FIN_C_M_CRED
,C.INTERESES_MORA_C_M_CRED ,C.MOVTOS_VARIOS_C_M_CRED from CUENTAS C where (((
and C.PRODUCTO=:b2) and C.CIERRE=:b3) and C.NUMERO_CUENTA not in (select NUMERO
VARIACION_CAMBIAL V where (((((V.EMISOR=C.EMISOR and V.SUCURSAL_EMISOR=C.SUCURSA
V.PRODUCTO=C.PRODUCTO) and V.NUMERO_CUENTA=C.NUMERO_CUENTA) and V.PERIODO_CIERRE
V.MOMENTO_INFORME=:"SYS_B_0"))) and C.NUMERO_CUENTA in (select NUMERO_CUENTA f
MOVTOS_CUENTAS M where (((((M.EMISOR=C.EMISOR and M.SUCURSAL_EMISOR=C.SUCURSAL_E
M.PRODUCTO=C.PRODUCTO) and M.NUMERO_CUENTA=C.NUMERO_CUENTA) and M.PERIODO_CIERRE
M.TIPO_DE_MONEDA<>:b5)))
Plan hash value: 2096700802
----
| Id | Operation | Name | Rows | Bytes |
----
| 0 | SELECT STATEMENT | | | |
| 1 | NESTED LOOPS ANTI | | 1 | 156 |
| 2 | NESTED LOOPS SEMI | | 1 | 136 |
| 3 | TABLE ACCESS BY INDEX ROWID| CUENTAS | 502 | 57228 |
| 4 | INDEX RANGE SCAN | IX_CUENTA_CIERRE | 502 | |
| 5 | TABLE ACCESS BY INDEX ROWID| MOVTOS_CUENTAS | 1 | 22 |
| 6 | INDEX RANGE SCAN | I_MOVTOS_CTAS_C | 2 | |
| 7 | INDEX UNIQUE SCAN | PK_EXCHANGE_VARIATION | 1 | 20 |
----

I checked the query is consuming a lot of Buffer_gets and low I/O.

The thing is i think that this table CUENTAS have some bad indexes (a bad clustering factor):

num_rows: 3846818
CLUSTERING FACTOR(IX_CUENTA_CIERRE): 204217
blocks: 548864

According to http://docs.oracle.com/cd/B19306_01/server.102/b14237/statviews_1069.htm#i1578369
The column clustering factor should be nearest the number of blocks than number of rows.

Do you guys think that it could be the problem?

Any help will be welcome.

Thanks in advance.
  • 1. Re: Index Clustering_factor issue
    Mohamed Houri Pro
    Currently Being Moderated
    First of all you should use the code tag in order to format your sql code.

    Second if you want help then do this:
    1. alter session set statistics_level=ALL;
    2. execute your query;
    3. select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
    And post here the generated execution plan together with its predicate part.

    As such, we might start helping you

    Best regards

    Mohamed Houri
    www.hourim.wordpress.com
  • 2. Re: Index Clustering_factor issue
    BSalesRashid Explorer
    Currently Being Moderated
    Hi,
    Thanks for replying.

    First of all, how do i use 'the code tag' ?

    Second:
    1 - The parameter were already set to ALL;
    2 -
    3 - i did so:
    select * from table(dbms_xplan.display_awr('&sql_id')); And then i entered the SQL_ID.

    What would be the difference between what you asked, and what i did ?



    and this is the exactly output:

    PLAN_TABLE_OUTPUT
    --------------------------------------------------------------------------------
    SQL_ID c99yz7wsr12bh
    --------------------
    select C.EMISOR ,C.SUCURSAL_EMISOR ,C.PRODUCTO ,C.NUMERO_CUENTA
    ,C.TIPO_DE_DOCUMENTO ,C.DOCUMENTO ,C.GRUPO_AFINIDAD ,C.ESTADO
    ,C.FECHA_ESTADO ,C.ENVIAR_RESUMEN ,C.COBRA_FRANQUEO ,C.CIERRE
    ,C.LIMITE_CREDITO ,C.FECHA_LIMITE ,C.TIPO_MONEDA_CREDITO
    ,C.BONIFICACION_TASAS ,C.FECHA_NO_RENOVACION ,C.CAUSA_NO_RENUEVA
    ,C.PUBLICA_NO_RENOVACION ,C.AUTOMATICO_A_BOLETIN ,C.SALDO_CIERRE_ML
    ,C.SALDO_CIERRE_MR ,C.PAGO_MINIMO_ML ,C.PAGO_MINIMO_MR ,C.COTIZACION
    ,C.ULTIMO_VTO_IMPAGO ,C.PAGOS_ML ,C.PAGOS_MR ,C.PAGOS_C_ML
    ,C.PAGOS_C_MR ,C.AJUSTES_C_M_CRED ,C.INTERESES_FIN_C_M_CRED
    ,C.INTERESES_MORA_C_M_CRED ,C.MOVTOS_VARIOS_C_M_CRED from CUENTAS C
    where ((((C.EMISOR=:b1 and C.PRODUCTO=:b2) and C.CIERRE=:b3) and
    C.NUMERO_CUENTA not in (select NUMERO_CUENTA from VARIACION_CAMBIAL
    V where (((((V.EMISOR=C.EMISOR and V.SUCURSAL_EMISOR=C.SUCURSAL_EMISOR)
    and V.PRODUCTO=C.PRODUCTO) and V.NUMERO_CUENTA=C.NUMERO_CUENTA) and
    V.PERIODO_CIERRE=:b4) and V.MOMENTO_INFORME=:"SYS_B_0"))) and
    C.NUMERO_CUENTA in (select NUMERO_CUENTA from MOVTOS_CUENTAS M where
    (((((M.EMISOR=C.EMISOR and M.SUCURSAL_EMISOR=C.SUCURSAL_EMISOR) and
    M.PRODUCTO=C.PRODUCTO) and M.NUMERO_CUENTA=C.NUMERO_CUENTA) and
    M.PERIODO_CIERRE=:b4) and M.TIPO_DE_MONEDA<>:b5)))
    Plan hash value: 671547300
    --------------------------------------------------------------------------------
    | Id | Operation | Name | Rows | Bytes |
    --------------------------------------------------------------------------------
    | 0 | SELECT STATEMENT | | | |
    | 1 | NESTED LOOPS SEMI | | 1 | 156 |
    | 2 | NESTED LOOPS ANTI | | 1 | 134 |
    | 3 | TABLE ACCESS BY INDEX ROWID| CUENTAS | 1 | 114 |
    | 4 | INDEX RANGE SCAN | IX_CUENTA_CIERRE | 1 | |
    | 5 | INDEX UNIQUE SCAN | PK_EXCHANGE_VARIATION | 1 | 20 |
    | 6 | TABLE ACCESS BY INDEX ROWID | MOVTOS_CUENTAS | 1 | 22 |
    | 7 | INDEX RANGE SCAN | I_MOVTOS_CTAS_C | 2 | |
    --------------------------------------------------------------------------------
    SQL_ID c99yz7wsr12bh
    --------------------
    select C.EMISOR ,C.SUCURSAL_EMISOR ,C.PRODUCTO ,C.NUMERO_CUENTA ,C.TIPO_DE_D
    ,C.DOCUMENTO ,C.GRUPO_AFINIDAD ,C.ESTADO ,C.FECHA_ESTADO ,C.ENVIAR_RESUMEN
    ,C.COBRA_FRANQUEO ,C.CIERRE ,C.LIMITE_CREDITO ,C.FECHA_LIMITE ,C.TIPO_MONEDA
    ,C.BONIFICACION_TASAS ,C.FECHA_NO_RENOVACION ,C.CAUSA_NO_RENUEVA ,C.PUBLICA_N
    ,C.AUTOMATICO_A_BOLETIN ,C.SALDO_CIERRE_ML ,C.SALDO_CIERRE_MR ,C.PAGO_MINIMO_
    ,C.PAGO_MINIMO_MR ,C.COTIZACION ,C.ULTIMO_VTO_IMPAGO ,C.PAGOS_ML ,C.PAGOS_MR
    ,C.PAGOS_C_ML ,C.PAGOS_C_MR ,C.AJUSTES_C_M_CRED ,C.INTERESES_FIN_C_M_CRED

    PLAN_TABLE_OUTPUT
    --------------------------------------------------------------------------------
    ,C.INTERESES_MORA_C_M_CRED ,C.MOVTOS_VARIOS_C_M_CRED from CUENTAS C where (((
    and C.PRODUCTO=:b2) and C.CIERRE=:b3) and C.NUMERO_CUENTA not in (select NUMERO
    VARIACION_CAMBIAL V where (((((V.EMISOR=C.EMISOR and V.SUCURSAL_EMISOR=C.SUCURSA
    V.PRODUCTO=C.PRODUCTO) and V.NUMERO_CUENTA=C.NUMERO_CUENTA) and V.PERIODO_CIERRE
    V.MOMENTO_INFORME=:"SYS_B_0"))) and C.NUMERO_CUENTA in (select NUMERO_CUENTA f
    MOVTOS_CUENTAS M where (((((M.EMISOR=C.EMISOR and M.SUCURSAL_EMISOR=C.SUCURSAL_E
    M.PRODUCTO=C.PRODUCTO) and M.NUMERO_CUENTA=C.NUMERO_CUENTA) and M.PERIODO_CIERRE
    M.TIPO_DE_MONEDA<>:b5)))
    Plan hash value: 2096700802
    --------------------------------------------------------------------------------
    | Id | Operation | Name | Rows | Bytes |
    --------------------------------------------------------------------------------
    | 0 | SELECT STATEMENT | | | |
    | 1 | NESTED LOOPS ANTI | | 1 | 156 |
    | 2 | NESTED LOOPS SEMI | | 1 | 136 |
    | 3 | TABLE ACCESS BY INDEX ROWID| CUENTAS | 502 | 57228 |
    | 4 | INDEX RANGE SCAN | IX_CUENTA_CIERRE | 502 | |
    | 5 | TABLE ACCESS BY INDEX ROWID| MOVTOS_CUENTAS | 1 | 22 |
    | 6 | INDEX RANGE SCAN | I_MOVTOS_CTAS_C | 2 | |
    | 7 | INDEX UNIQUE SCAN | PK_EXCHANGE_VARIATION | 1 | 20 |
    --------------------------------------------------------------------------------
    SQL_ID c99yz7wsr12bh
    --------------------
    select C.EMISOR ,C.SUCURSAL_EMISOR ,C.PRODUCTO ,C.NUMERO_CUENTA ,C.TIPO_DE_D
    ,C.DOCUMENTO ,C.GRUPO_AFINIDAD ,C.ESTADO ,C.FECHA_ESTADO ,C.ENVIAR_RESUMEN
    ,C.COBRA_FRANQUEO ,C.CIERRE ,C.LIMITE_CREDITO ,C.FECHA_LIMITE ,C.TIPO_MONEDA
    ,C.BONIFICACION_TASAS ,C.FECHA_NO_RENOVACION ,C.CAUSA_NO_RENUEVA ,C.PUBLICA_N
    ,C.AUTOMATICO_A_BOLETIN ,C.SALDO_CIERRE_ML ,C.SALDO_CIERRE_MR ,C.PAGO_MINIMO_
    ,C.PAGO_MINIMO_MR ,C.COTIZACION ,C.ULTIMO_VTO_IMPAGO ,C.PAGOS_ML ,C.PAGOS_MR
    ,C.PAGOS_C_ML ,C.PAGOS_C_MR ,C.AJUSTES_C_M_CRED ,C.INTERESES_FIN_C_M_CRED
    ,C.INTERESES_MORA_C_M_CRED ,C.MOVTOS_VARIOS_C_M_CRED from CUENTAS C where (((
    and C.PRODUCTO=:b2) and C.CIERRE=:b3) and C.NUMERO_CUENTA not in (select NUMERO
    VARIACION_CAMBIAL V where (((((V.EMISOR=C.EMISOR and V.SUCURSAL_EMISOR=C.SUCURSA
    V.PRODUCTO=C.PRODUCTO) and V.NUMERO_CUENTA=C.NUMERO_CUENTA) and V.PERIODO_CIERRE
    V.MOMENTO_INFORME=:"SYS_B_0"))) and C.NUMERO_CUENTA in (select NUMERO_CUENTA f
    MOVTOS_CUENTAS M where (((((M.EMISOR=C.EMISOR and M.SUCURSAL_EMISOR=C.SUCURSAL_E
    M.PRODUCTO=C.PRODUCTO) and M.NUMERO_CUENTA=C.NUMERO_CUENTA) and M.PERIODO_CIERRE
    M.TIPO_DE_MONEDA<>:b5)))
    Plan hash value: 3785864856
    --------------------------------------------------------------------------------
    | Id | Operation | Name | Rows | Bytes |
    --------------------------------------------------------------------------------

    PLAN_TABLE_OUTPUT
    --------------------------------------------------------------------------------
    | 0 | SELECT STATEMENT | | | |
    | 1 | NESTED LOOPS ANTI | | 1 | 158 |
    | 2 | NESTED LOOPS | | 1 | 138 |
    | 3 | SORT UNIQUE | | 1 | 22 |
    | 4 | TABLE ACCESS BY INDEX ROWID| MOVTOS_CUENTAS | 1 | 22 |
    | 5 | INDEX RANGE SCAN | I_MOVTOS_CTAS_P | 1 | |
    | 6 | TABLE ACCESS BY INDEX ROWID | CUENTAS | 1 | 116 |
    | 7 | INDEX UNIQUE SCAN | PK_CUENTAS | 1 | |
    | 8 | INDEX UNIQUE SCAN | PK_EXCHANGE_VARIATION | 1 | 20 |
    --------------------------------------------------------------------------------

    104 rows selected

    Executed in 1,264 seconds

    Regards,
  • 3. Re: Index Clustering_factor issue
    Dom Brooks Guru
    Currently Being Moderated
    What would be the difference between what you asked, and what i did ?
    display_awr gets historical plans from awr - not something that is obviously relevant yet.

    display_cursor with the format tag 'allstats last' gets the last execution metrics including the all important ACTUAL execution metrics so you can compare against ESTIMATES.

    For examining historic executions, especially where the statement was running for 1 hour rather than 3 minutes, you might find looking at the raw ash data useful. You need to be licensed for diagnostic pack.
    e.g.
    SELECT *
    FROM   dba_hist_active_sess_history h
    WHERE sql_id = '<your sql id>'
    ORDER BY sample_time DESC;
    Edited by: Dom Brooks on Nov 13, 2012 1:36 PM
  • 4. Re: Index Clustering_factor issue
    BSalesRashid Explorer
    Currently Being Moderated
    Fine, the other dba here is doing an analyze on an index. as soon as he finishes it, the process will start and i will do this and post here the output.

    Thanks for showing the difference.

    Edited by: BSalesRashid on 13/11/2012 06:00
  • 5. Re: Index Clustering_factor issue
    BSalesRashid Explorer
    Currently Being Moderated
    We solve the things out;
    That was a bad index, we had to HINT for another index.

    Thanks.
  • 6. Re: Index Clustering_factor issue
    Dom Brooks Guru
    Currently Being Moderated
    We solve the things out;
    That was a bad index, we had to HINT for another index.
    Prove it.
    And I don't mean show us that the hint works, show us why the hint works and that other, more sensible resolutions do not exist.

    Why was the other plan slow? Where was the time actually spent?
    Are the estimates in the plan even accurate?
  • 7. Re: Index Clustering_factor issue
    BSalesRashid Explorer
    Currently Being Moderated
    Well, how will i prove it?

    We made a new explain plan(using plsql developer) with the real parameters used in the query, instead of using bind variables. I would like to show you, but i am unable to show the explain plan, or load a screenshot. And i tried to generate an explain plan by text using this query:

    explain plan for &query;
    SELECT OPERATION||' '||OPTIONS,OBJECT_OWNER,OBJECT_NAME,COST,CPU_COST, IO_COST,CARDINALITY,BYTES FROM plan_table;

    But did not matched the explain plan generated by plsql developer;

    The explain plan (plsqldev) showed us an 'INDEX SKIP SCAN' and a Cardinality of 500000+.
    Then we decided to use hint to an other index in the subquery.
    /*+ INDEX ( M I_MOVTOS_CTAS_C) */

    Then it worked in 6 minutes.
  • 8. Re: Index Clustering_factor issue
    Dom Brooks Guru
    Currently Being Moderated
    This is just piffle.


    How do you post a sql tuning request:
    HOW TO: Post a SQL statement tuning request - template posting


    Statements where the estimates show 1 and are accurate rarely take one hour

    Do you understand how to get actual cardinalities and compare to estimates?


    My guess (because you've failed to provide adequate information) is:
    Inaccurate estimates + poor execution plans + lack of node affinity on rac = classic performance problem.

    Edited by: Dom Brooks on Nov 13, 2012 9:31 PM
  • 9. Re: Index Clustering_factor issue
    jgarry Guru
    Currently Being Moderated
    First of all, how do i use 'the code tag' ?
    You put the marker {noformat}
    {noformat} before and after your code, so it 
    looks like this
    Then you check by clicking on the Preview tab to be sure it looks good.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
  • 10. Re: Index Clustering_factor issue
    BSalesRashid Explorer
    Currently Being Moderated
    Thanks!

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points