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!
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:
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)