12 Replies Latest reply: Apr 17, 2013 11:03 PM by sulimo RSS

    Queries on oracle hang after index creation

    949362
      Hi,

      I have a problem where queries issued on an oracle database hang after I create indexes on some of the tables used by these queries.

      I have a script where I drop indexes :

      DROP INDEX EVP_PCON00_CDSITC_IX;
      DROP INDEX EVP_PCS202_STIMP1_IX;
      DROP INDEX EVP_PECT00_ONAECT_IX;
      *.....*
      DROP INDEX EVP_PVAL01_ONAVAL_IX;


      After this script I load data into those tables.

      Then I launch another script where I recreate the indexes I just dropped :

      CREATE INDEX EVP_PCON00_CDSITC_IX  ON EVP_PCON00(CDSITC) TABLESPACE TS_ODS_INDEX;
      COMMIT;
      CREATE INDEX EVP_PCS202_STIMP1_IX  ON EVP_PCS202(STIMP1) TABLESPACE TS_ODS_INDEX;
      COMMIT;
      *....*
      CREATE INDEX EVP_PVAL01_CDORIV_IX  ON EVP_PVAL01(CDORIV) TABLESPACE TS_ODS_INDEX;
      COMMIT;

      When the script ends, I try to execute a query using some of the tables I created indexes on :

      SELECT ...
      FROM ....
      WHERE ....


      The query never return a result set, it just hangs, I look at the session browser in toad and the scan of the first table used in the query hangs.

      When I drop those indexes and re execute the query everything works fine.


      Thanks

      Edited by: 946359 on Jul 13, 2012 9:20 AM
        • 1. Re: Queries on oracle hang after index creation
          sb92075
          946359 wrote:
          Hi,

          I have a problem where queries issued on an oracle database hangs after I create indexes on some of the tables used by these queries.

          I have a script where I drop indexes :

          DROP INDEX EVP_PCON00_CDSITC_IX;
          DROP INDEX EVP_PCS202_STIMP1_IX;
          DROP INDEX EVP_PECT00_ONAECT_IX;
          *.....*
          DROP INDEX EVP_PVAL01_ONAVAL_IX;


          After this script I load data into those tables.

          Then I launch another script where I recreate the indexes I just dropped :

          CREATE INDEX EVP_PCON00_CDSITC_IX  ON EVP_PCON00(CDSITC) TABLESPACE TS_ODS_INDEX;
          COMMIT;
          CREATE INDEX EVP_PCS202_STIMP1_IX  ON EVP_PCS202(STIMP1) TABLESPACE TS_ODS_INDEX;
          COMMIT;
          *....*
          CREATE INDEX EVP_PVAL01_CDORIV_IX  ON EVP_PVAL01(CDORIV) TABLESPACE TS_ODS_INDEX;
          COMMIT;

          When the script ends, I try to execute a query using some of the tables I created indexes on :

          SELECT ...
          FROM ....
          WHERE ....


          The query never return a result set, it just hangs, I look at the session browser in toad and the scan of the first table used in the query hangs.

          When I drop those indexes and re execute the query everything works fine.


          Thanks
          you have a mystery & we have NO clues.

          post results from following SQL

          SELECT * FROM V$VERSION;
          • 2. Re: Queries on oracle hang after index creation
            949362
            Hi,

            I'm sorry I forgot to put my system information :


            BANNER
            --------------------------------------------------------------------------------
            Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
            PL/SQL Release 11.2.0.1.0 - Production
            CORE     11.2.0.1.0     Production
            TNS for Linux: Version 11.2.0.1.0 - Production
            NLSRTL Version 11.2.0.1.0 - Production

            5 rows selected.
            • 3. Re: Queries on oracle hang after index creation
              sb92075
              do as below but use your own SQL that "hangs"
              bcm@bcm-laptop:~$ sqlplus user1/user1
              
              SQL*Plus: Release 11.2.0.1.0 Production on Fri Jul 13 09:35:20 2012
              
              Copyright (c) 1982, 2009, Oracle.  All rights reserved.
              
              
              Connected to:
              Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
              With the Partitioning, OLAP, Data Mining and Real Application Testing options
              
              09:35:20 SQL> set autotrace trace explain
              09:35:34 SQL> select sysdate from dual;
              
              Execution Plan
              ----------------------------------------------------------
              Plan hash value: 1388734953
              
              -----------------------------------------------------------------
              | Id  | Operation      | Name | Rows     | Cost (%CPU)| Time     |
              -----------------------------------------------------------------
              |   0 | SELECT STATEMENT |     |     1 |     2   (0)| 00:00:01 |
              |   1 |  FAST DUAL      |     |     1 |     2   (0)| 00:00:01 |
              -----------------------------------------------------------------
              
              09:35:44 SQL> 
              • 4. Re: Queries on oracle hang after index creation
                949362
                This is the result I get :


                Execution Plan
                ----------------------------------------------------------
                Plan hash value: 1198043594

                -----------------------------------------------------------------------------------------------------------
                | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
                -----------------------------------------------------------------------------------------------------------
                | 0 | SELECT STATEMENT | | 1 | 1343 | 12 (17)| 00:00:01 |
                | 1 | HASH UNIQUE | | 1 | 1343 | 12 (17)| 00:00:01 |
                |* 2 | HASH JOIN OUTER | | 1 | 1343 | 11 (10)| 00:00:01 |
                | 3 | NESTED LOOPS OUTER | | 1 | 1257 | 9 (12)| 00:00:01 |
                | 4 | NESTED LOOPS OUTER | | 1 | 1033 | 8 (13)| 00:00:01 |
                | 5 | NESTED LOOPS OUTER | | 1 | 809 | 7 (15)| 00:00:01 |
                |* 6 | HASH JOIN | | 1 | 585 | 6 (17)| 00:00:01 |
                | 7 | NESTED LOOPS | | | | | |
                | 8 | NESTED LOOPS | | 1 | 490 | 3 (0)| 00:00:01 |
                | 9 | TABLE ACCESS FULL | EVP_PTIP00 | 1 | 452 | 2 (0)| 00:00:01 |
                |* 10 | INDEX RANGE SCAN | EVP_PTIC00_CDROLE_IX | 1 | | 1 (0)| 00:00:01 |
                |* 11 | TABLE ACCESS BY INDEX ROWID| EVP_PTIC00 | 1 | 38 | 1 (0)| 00:00:01 |
                | 12 | TABLE ACCESS FULL | EVP_PTIF00 | 1 | 95 | 2 (0)| 00:00:01 |
                |* 13 | TABLE ACCESS BY INDEX ROWID | EVP_PTAB00 | 1 | 224 | 1 (0)| 00:00:01 |
                |* 14 | INDEX RANGE SCAN | EVP_PTAB00_CLTABL_IX | 1 | | 1 (0)| 00:00:01 |
                |* 15 | TABLE ACCESS BY INDEX ROWID | EVP_PTAB00 | 1 | 224 | 1 (0)| 00:00:01 |
                |* 16 | INDEX RANGE SCAN | EVP_PTAB00_CLTABL_IX | 1 | | 1 (0)| 00:00:01 |
                |* 17 | TABLE ACCESS BY INDEX ROWID | EVP_PTAB00 | 1 | 224 | 1 (0)| 00:00:01 |
                |* 18 | INDEX RANGE SCAN | EVP_PTAB00_CLTABL_IX | 1 | | 1 (0)| 00:00:01 |
                | 19 | TABLE ACCESS FULL | EVP_PRIB00 | 1 | 86 | 2 (0)| 00:00:01 |
                -----------------------------------------------------------------------------------------------------------

                Predicate Information (identified by operation id):
                ---------------------------------------------------

                2 - access("EVP_PRIB00"."NOTIER"(+)="EVP_PTIP00"."NOTIER")
                6 - access("EVP_PTIF00"."NOTIER"="EVP_PTIP00"."NOTIER")
                10 - access("EVP_PTIC00"."CDROLE"='EMP')
                11 - filter("EVP_PTIC00"."CDDDRX"='CL' AND "EVP_PTIP00"."NOTIER"="EVP_PTIC00"."NOTIER")
                13 - filter("EVP_PTAB00_SITF"."CDTABL"(+)="EVP_PTIP00"."CDSITF")
                14 - access("EVP_PTAB00_SITF"."CLTABL"(+)='SITF')
                15 - filter("EVP_PTAB00_TITR"."CDTABL"(+)="EVP_PTIP00"."CDTITR")
                16 - access("EVP_PTAB00_TITR"."CLTABL"(+)='TITR')
                17 - filter("EVP_PTAB00_RMAT"."CDTABL"(+)="EVP_PTIP00"."CDRMAT")
                18 - access("EVP_PTAB00_RMAT"."CLTABL"(+)='RMAT')
                • 5. Re: Queries on oracle hang after index creation
                  sb92075
                  946359 wrote:
                  This is the result I get :
                  PLAN shows 1 row being returned in 1 second.

                  Do all tables & indexes involved have current & accurate statistics?
                  • 6. Re: Queries on oracle hang after index creation
                    Zoltan Kecskemethy
                    - no commit needed after a DDL like create index.
                    - gather table statistics using cascade options after a new index added before measure of performance. use it for all affected tables.
                    EXEC DBMS_STATS.GATHER_TABLE_STATS (ownname => 'YOURUSER', tabname => 'EVP_PCON00',estimate_percent => 10, CASCADE =>true);
                    • 7. Re: Queries on oracle hang after index creation
                      949362
                      sb92075 wrote:
                      946359 wrote:
                      This is the result I get :
                      PLAN shows 1 row being returned in 1 second.

                      Do all tables & indexes involved have current & accurate statistics?
                      Hi, the query (when I drop all the indexes I created before) returns 1 600 000 rows.


                      No I just create drop and create the indexes, I never knew I needed to update the statictics.

                      Edited by: 946359 on Jul 13, 2012 12:36 PM
                      • 8. Re: Queries on oracle hang after index creation
                        949362
                        Zoltan Kecskemethy wrote:
                        - no commit needed after a DDL like create index.
                        - gather table statistics using cascade options after a new index added before measure of performance. use it for all affected tables.
                        EXEC DBMS_STATS.GATHER_TABLE_STATS (ownname => 'YOURUSER', tabname => 'EVP_PCON00',estimate_percent => 10, CASCADE =>true);
                        Hi,

                        I tried using the ANALYZE TABLE tablename COMPUTE STATISTICS FOR ALL INDEX; on all tables I created indexes on. The query still hangs. I can let the query execute for two hours it stills hangs.

                        When I use toad session browser I see that it always hangs at this part

                        *9      TABLE ACCESS FULL      EVP_PTIP00      1      452      2 (0)      00:00:01*

                        While if I drop the indexes the query start returning the rows in 3 minutes.

                        The index are created on very large tables (more than 130 millions rows) I need them for others queries that will populate fact tables

                        Edited by: 946359 on Jul 13, 2012 12:39 PM

                        Edited by: 946359 on Jul 13, 2012 12:42 PM
                        • 9. Re: Queries on oracle hang after index creation
                          Osama_Mustafa
                          Run GATHER_TABLE_STATS maybe its works
                          • 10. Re: Queries on oracle hang after index creation
                            1003771
                            -- Deleted this question as it was suggested that I was hijacking the thread by asking if there was a final solution. FY sb92075

                            Edited by: user2014449 on 17/04/2013 18:38
                            • 11. Re: Queries on oracle hang after index creation
                              sb92075
                              user2014449 wrote:
                              Did you ever get a solution to this?

                              I am having the exact same problem now
                              If I create the index then the query hangs, if I delete the index then it works.
                              do NOT HIJACK threads started by other folks.
                              start your own thread.


                              How do I ask a question on the forums?
                              SQL and PL/SQL FAQ
                              • 12. Re: Queries on oracle hang after index creation
                                sulimo
                                Please post... Execution plans for both statements...

                                Also results for:
                                select table_name, num_rows
                                from dba_tables
                                where table_name in ('EVP_PTIP00','EVP_PTIC00','EVP_PTIF00','EVP_PTAB00','EVP_PRIB00')
                                order by 2 desc;
                                
                                select /*+ full(EVP_PTIC00) parallel (EVP_PTIC00,8) */
                                count(*)
                                from EVP_PTIC00
                                where "CDROLE"='EMP';
                                
                                select /*+ full(EVP_PTIC00) parallel (EVP_PTIC00,8) */
                                count(*)
                                from EVP_PTIC00
                                where "CDROLE"='EMP'
                                and "EVP_PTIC00"."CDDDRX"='CL'; 
                                
                                select /*+ full(EVP_PTAB00) parallel (EVP_PTAB00,8) */
                                CLTABL, count(*) 
                                from EVP_PTAB00
                                where "CLTABL" in ('SITF','TITR','RMAT')
                                group by CLTABL;
                                thanks