[Django]-Very slow performance in Django with remote Oracle server

by

3👍

✅

OK I found the solution. In Django all binded parameters are defined as strings (%s) so a lot of implicit conversion is made when the parameter is, for instance, a number. The solution is to explicitly typecast them as number using the function TO_NUMBER(%s) in the WHERE clause.

👤David Barreto

1👍

You should run both queries with an explain plan to see the differences. My guess is that in the literal parameter case, an index is being used, and in the bound parameter case it may not be used. IF you can see the difference between the two, you may need to add a “hint” clause to your select statement to force the correct index to be picked. You can read about hints here

If you are using sqlplus, you can turn on autotrace to show you the explain plan and execution statistics, example:

SQL> set autotrace on
SQL> set linesize 200
SQL> set serveroutput on
SQL> spool foo.log
SQL> select count(*) from ucbcust; -- this is just a test table in my database. replace with your SQL.
SQL>   ... output shows...
SQL>  spool off


Output would look something like this (obviously, different for your query):



  Execution Plan
    ----------------------------------------------------------                                  
    Plan hash value: 1527793343                                                                                           ----------------------------------------------------------------------------------------          
    | Id  | Operation             | Name                   | Rows  | Cost (%CPU)| Time     |            

     ----------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 3 (0)| 00:00:01
| 1 | SORT AGGREGATE | | 1 | |
| 2 | INDEX FAST FULL SCAN| UCBCUST_CUST_KEY_INDEX | 2088 | 3 (0)| 00:00:01 

|                                                                                                    ----------------------------------------------------------------------------------------
    Statistics

              0  recursive calls                                                                                                                                                                            
              0  db block gets                                                                                                                                                                              
             17  consistent gets                                                                                                                                                                            
              0  physical reads                                                                                                                                                                             
              0  redo size                                                                                                                                                                                  
            343  bytes sent via SQL*Net to client                                                                                                                                                           
            364  bytes received via SQL*Net from client                                                                                                                                                     
              2  SQL*Net roundtrips to/from client                                                                                                                                                          
              0  sorts (memory)                                                                                                                                                                             
              0  sorts (disk)                                                                                                                                                                               
              1  rows processed

To execute the parameterized query in a similar fashion in sqlplus, you will need to define a variable as a placeholder, example:

SQL> variable foo number
SQL> exec :foo := 1000

PL/SQL procedure successfully completed.

SQL> select :foo from dual
  2  /

      :FOO
----------
      1000

So, in your query, replace all the “%*” parameters with a variable and run in Sqlplus to see the execution details. Hopefully, with those tools, you will see the difference in the execution plans.

👤OldProgrammer

Leave a comment