10 Replies Latest reply: Nov 14, 2012 10:30 AM by BSalesRashid RSS

    Index Clustering_factor issue

    BSalesRashid
      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
          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
            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
              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
                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
                  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
                    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
                      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
                        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
                          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
                            Thanks!