[Django] #20689: Django 1.5.1: severe performance degradation on Oracle
Django
-------------------------------------+-------------------------------------
Reporter: zakharov.vv@… | Owner: nobody
Type: Uncategorized | Status: new
Component: Database layer | Version: 1.5
(models, ORM) | Keywords: Oracle, cx_oracle,
Severity: Normal | unicode
Triage Stage: Unreviewed | Has patch: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
After migrating to Django 1.5.1, several queries takes thousands times
more time comparing to Django 1.4.5.
Example query generated by ORM:
{{{
SELECT COUNT(*) FROM "TICKETS" WHERE ("TICKETS"."T_TYPE" IN (:arg0, :arg1,
:arg2, :arg3) AND "TICKETS"."T_PARTNER_CODE" = :arg4 AND
"TICKETS"."T_CHANEL" = :arg5 AND "TICKETS"."T_WORKER_CODE" = :arg6 AND
"TICKETS"."T_CDATE" <= :arg7 AND "TICKETS"."T_CDATE" >= :arg8 )
}}}
Django 1.4.5: ~7ms
Django 1.5.1: ~193574ms (about 27000 times more!)
`git bisect` points to this commit:
https://github.com/django/django/commit/d194f290571f7e9dda7d2fd7a6f2b171120f2f14.
Indeed, forcing `convert_unicode` to be equal to `force_bytes` solves the
issue.
The only found difference is in params to query:
Django 1.4.5:
{{{
[1, 38, 76, 92, '777-61', 61, 'pupkin', '2013-07-03 00:00:00', '2013-06-01
00:00:00']
}}}
Django 1.5.1:
{{{
[1, 38, 76, 92, u'777-61', 61, u'pupkin', u'2013-07-03 00:00:00',
u'2013-06-01 00:00:00']
}}}
Django 1.5.1 hangs on this string
(''django/db/backends/oracle/base.py:710''):
{{{
return self.cursor.execute(query,
self._param_generator(params))
}}}
Directly using cx_Oracle for this query shows no difference for two
variants of query params.
Used versions:
* cx-Oracle 5.1.2
* instantclient 11.2.0.3.0 (same for instantclient 10.2.0.5)
* Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit
Production
--
Ticket URL: <https://code.djangoproject.com/ticket/20689>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
Django
-------------------------------------+-------------------------------------
Reporter: zakharov.vv@… | Owner: nobody
Type: Uncategorized | Status: new
Component: Database layer | Version: 1.5
Severity: Normal | Triage Stage:
Keywords: Oracle, cx_oracle, | Unreviewed
unicode | Needs documentation: 0
Has patch: 0 | Patch needs improvement: 0
Needs tests: 0 | UI/UX: 0
Easy pickings: 0 |
-------------------------------------+-------------------------------------
Changes (by zakharov.vv@…):
* needs_better_patch: => 0
* needs_tests: => 0
* needs_docs: => 0
Comment:
It seems, that issue affects only large tables. Table from example in
description has about 100 million records. For small tables (thousands
records) query time doesn't change after upgrading to 1.5.1.
--
Ticket URL: <https://code.djangoproject.com/ticket/20689#comment:1>
Django
-------------------------------------+-------------------------------------
Reporter: zakharov.vv@… | Owner: nobody
Component: Database layer | Version: 1.5
Severity: Normal | Triage Stage: Accepted
Keywords: Oracle, cx_oracle, | Needs documentation: 0
unicode | Patch needs improvement: 0
Has patch: 0 | UI/UX: 0
Needs tests: 0 |
Easy pickings: 0 |
-------------------------------------+-------------------------------------
Changes (by wim@…):
* type: Uncategorized => Bug
* stage: Unreviewed => Accepted
Comment:
Accepted. If you would like to contribute and would like to submit a patch
(or even a pull request), you are welcome to do so!
--
Ticket URL: <https://code.djangoproject.com/ticket/20689#comment:2>
Django
-------------------------------------+-------------------------------------
Reporter: zakharov.vv@… | Owner: nobody
Component: Database layer | Version: 1.5
Severity: Normal | Triage Stage: Accepted
Keywords: Oracle, cx_oracle, | Needs documentation: 0
unicode | Patch needs improvement: 0
Has patch: 0 | UI/UX: 0
Needs tests: 0 |
Easy pickings: 0 |
-------------------------------------+-------------------------------------
* status: new => closed
* resolution: => needsinfo
Comment:
Hi,
This ticket refers to versions of Django (1.5) and Oracle server (10g)
which are no longer supported by their respective vendors, so I am closing
it.
If the problem can be reproduced on newer versions (Oracle>=11 and
Django>=1.7), please re-open.
(Although Django 1.7 nominally supports Oracle 10g, the core team has no
access to an Oracle 10 server, which makes it very hard for us to solve
problems against it).
--
Ticket URL: <https://code.djangoproject.com/ticket/20689#comment:3>