This discussion is archived
12 Replies Latest reply: Apr 17, 2013 9:03 PM by sulimo RSS

Queries on oracle hang after index creation

949362 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    - 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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    Run GATHER_TABLE_STATS maybe its works
  • 10. Re: Queries on oracle hang after index creation
    1003771 Newbie
    Currently Being Moderated
    -- 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 Guru
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    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

Legend

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