Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2016
    Posts
    1

    Unanswered: Perplexing, long-running query

    I'm working on a Django application that has an Oracle back-end, and there is a query that's driving me nuts because it consistently takes around 200 seconds through the application but only a fraction of a second from SQLplus:

    SELECT "SERVERS"."ID", "SERVERS"."NAME", "SERVERS"."CREATED_AT", "SERVERS"."UPDATED_AT", "SERVERS"."CONFIG_ID", "SERVERS"."IS_ACTIVE" FROM "SERVERS" WHERE "SERVERS"."ID" IN (SELECT DISTINCT U0."SERVER_ID" FROM "MESSAGES" U0 WHERE (U0."ID" <= 4560030 AND U0."ID" > 4558940 )) ORDER BY "SERVERS"."NAME" ASC;
    The SERVERS table is very small in my dev environment -- four rows, two of which have the IS_ACTIVE value set to 1. The ID column is the Django-generated, numeric primary key for the table. MESSAGES.SERVER_ID is a foreign key reference to SERVERS.ID, and it is indexed. Index stats for MESSAGES.SERVER_ID say there are 2 unique values for 3,838,184 rows, blevel is 2, sample size is 292,595, cluster factor is 1,120,333, avg leaf is 7,851, and avg data is 560,166. MESSAGES.ID is also a numeric primary key.

    I would write this off as "Don't index a field with only two unique values," but the discrepancy in time between the Django query vs. SQLplus makes me wonder what else is going on to cause this. The only other difference is that SQLplus is on the same host with the database, and the Python/Django code is on another system, but it's all in a VMware vSphere cluster with little going on because it's in a lab.

    The database is Oracle 10g Enterprise Edition release 10.2.0.1.0. The cx_Oracle version is 5.2, upgraded from an older version that was exhibiting the same verion. I've got Django 1.3 (yes, I know it's old), and Python 2.6.6, and both systems are Red Hat Enterprise Linux Server 6.1.

    I'll appreciate any wisdom anyone can offer. Thanks!

  2. #2
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    First: please read how to use the code tags properly: http://www.dbforums.com/misc.php?do=bbcode#code

    Second: please post the execution plan for that query (don't forget to format it properly)

    One thing: the distinct in the sub-query is useless and I don't know if that ancient Oracle version was smart enough to remove that.
    So you should try without the DISTINCT in the sub-query.

    Another option might be to see if Oracle 10 handles an EXISTS condition better than the IN:
    Code:
    SELECT svr.id,
           svr.name,
           svr.created_at,
           svr.updated_at,
           svr.config_id,
           svr.is_active
    FROM servers svr
    WHERE EXISTS (SELECT *
                  FROM messages u0
                  WHERE u0.id > 4558940
                  AND   u0.id <= 4560030
                  AND   u0.server_id = svr.id)
    ORDER BY servers.name ASC
    For a current Oracle versions (11,12) this wouldn't make a difference as the query optimizer generates the same plans for your statement and the one above.

    The index on MESSAGES.SERVER_ID won't help if you only have two unique values. An index on MESSAGES.ID might be better if the condition on the ID reduces the number of rows that are returned from that table.

    You should also really plan your upgrade to a supported version of Oracle (which today effectively only is 12c if I'm not mistaken)
    Last edited by shammat; 02-09-16 at 12:27.
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

  3. #3
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    As shammat said, there is a difference in the two query plans being generated. Is the application connecting to Oracle via an ODBC connection?

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •