1

When I use the bind variable approach found here: https://cx-oracle.readthedocs.io/en/latest/user_guide/bind.html#bind and here: Python cx_Oracle bind variables my query takes around 8 minutes, but when I use hardcoded values (literals), it takes around 20 seconds.

I'm struggling to comprehend what's happening "behind-the-scenes" (variables/memory access/data transfer/query parsing) to see if there's any way for me to adhere to the recommended approach of using bind variables and get the same ~20s performance.

This python script will be automated and the values will be dynamic, so I definitely can't use hardcoded values.

Technical background: Python 3.6; Oracle 11g; cx_Oracle 8

---- python portion of code -----

first version
param_dict = {“startDate:”01-Jul-21”, “endDate:”31-Jul-2021”}

conn = (typical database connection code….)

cur = conn.cursor()

###### this query has the bind variables and param_dict keys match bind variable aliases; runtime ~480s (8mins)
cur_df = pandas.DataFrame(cur.execute("inserted_query_here", param_dict))
second version
conn = (typical database connection code….)

cur = conn.cursor()

###### this query has the hardcoded values (literals); runtime ~20s
cur_df = pandas.DataFrame(cur.execute("inserted_query_here"))
Christopher Jones
  • 9,449
  • 3
  • 24
  • 48
successfulmike
  • 171
  • 1
  • 6
  • 1
    Show us the code you're using in both cases. – John Gordon Jul 30 '21 at 15:28
  • hi John, do you want to see the SQL portion of the code, the python portion or both? – successfulmike Jul 30 '21 at 16:38
  • I think just the Python part should be enough. – John Gordon Jul 30 '21 at 17:10
  • @JohnGordon, I've updated the original post with code as requested. Thoughts? – successfulmike Jul 30 '21 at 18:12
  • 1
    thanks for layout edit @Luke Woodward. Guys, I might have to move this thread to the Oracle section :-( I'm running more tests in SQL Developer directly and I'm having the same results (bind vs literal performance), so this may not be a cx_Oracle/Python issue at all. I'm not a DBA nor have the permissions to look into compute stats at database level, but that's what I'm seeing in most of articles I'm fining with current research. – successfulmike Jul 30 '21 at 19:02
  • We really need to see a complete test case, including SQL that creates the table & data. Are the dates used in a WHERE clause? One guess is that there is a date conversion not handled well by that old version of Oracle (e.g. like [this](https://github.com/oracle/node-oracledb/issues/711), though cx_Oracle shouldn't have that exact scenario). Check the EXPLAIN PLAN. (And upgrade!) – Christopher Jones Jul 30 '21 at 23:57
  • It's clearly not a [cx-oracle] question but "why Oracle choses suboptimal plan for prepared statements?" I think you need just correct tags. About your question: it's not a rare situation with prepared statements: you have overhead for SQL parsing each time but generated plans could be more efficient. I don't remember exactly how to deal with them in Oracle, for an explanation in general look on: https://use-the-index-luke.com/sql/myth-directory/dynamic-sql-is-slow – Alex Yu Jul 31 '21 at 00:41

1 Answers1

0

@ChristopherJones and Alex thanks for the referenced articles. I've been able to solve the issue by thoroughly examining the EXPLAIN PLAN. The query that performed faster wasn't using index (faster to do full table scan); the other was (bind variable version of query).

I applied NO_INDEX hint accordingly and now have ~20s result for bind variable version of query.

successfulmike
  • 171
  • 1
  • 6