python cx-oracle can control variable OCI_ATTR_MAXDATA_SIZE?

[cfchen8]

Our DBA recently found this sql cause shareable memory grow up and high version_count 。According to trace log analyze,
it might be OCI use this variable OCI_ATTR_MAXDATA_SIZE 。
How to use this variable when i use cx-oracle library establish connection to query table?
sql statement:
SELECT  /*+ INDEX(da I_ASC_DATA_2) */  REGEXP_SUBSTR(da.TARGET_OBJREF,:"SYS_B_00",:"SYS_B_01",:"SYS_B_02") ||:"SYS_B_03"||REGEXP_SUBSTR(da.TARGET_OBJREF,:"SYS_B_04",:"SYS_B_05",:"SYS_B_06") AS KEY, da.SOURCE_OBJREF,da.TARGET_OBJREF,defn.ASC_NAME FROM sysf300.ASC_DATA da , (SELECT tmp.ASC_NAME, tmp.ASC_DEFN_ROW_ID FROM sysf300.ASC_DEFN tmp WHERE ( tmp.ASC_NAME = :"SYS_B_07" OR  tmp.ASC_NAME = :"SYS_B_08" OR tmp.ASC_NAME = :"SYS_B_09" ) AND tmp.SOURCE_TARGET_FLAG = :"SYS_B_10") defn WHERE da.TARGET_OBJREF IN (:0,:1,:2,:3,:4,:5,:6,:7,:8,:9)  AND da.ASC_REFERENCE = defn.ASC_DEFN_ROW_ID AND da.SOURCE_TARGET_FLAG = :"SYS_B_11"
cx_oracle version: 7.1.2
oracle database version: 19.0.0.0.0

[cjbj]

(Technically this should have been asked on https://github.com/oracle/python-cx_Oracle/issues)

@anthony-tuininga will know better what the 'set the max data size, if applicable' code in ODPI-C is handling.

What data are you binding?

Update: what's the actual database version e.g. like 19.3 or 19.18 shown by connection.version? What Oracle Client library version are you using?

[anthony-tuininga]

@cfchen8, that attribute is set based on the size of the data that is being bound in your statement. If the size of your values fluctuates a lot, that might explain what you are seeing. You can eliminate that, however, by using cursor.setinputsizes() as in the following, assuming all values are strings:

cursor.setinputsizes(25, 50, 100, 75, 15)

That will indicate that the first value bound should be a string of no more than 25 characters, the second value bound should be a string of no more than 50 characters, etc. Note that if you exceed these values the driver will happily accept them (and you'll be back to where you were before with it sending lengths based on the actual data you are supplying). If the second and third values bound are numbers, you can do either one of these:

cursor.setinputsizes(25, None, None, 75, 15)

or

cursor.setinputsizes(25, oracledb.DB_TYPE_NUMBER, oracledb.DB_TYPE_NUMBER, 75, 15)

The value None simply indicates that default processing should take place and no predefined variables created.

Can you give that a try and see if the problem settles down?

[cjbj]

Some more comments thanks to our optimizer team:

• it's not uncommon to see SQL statements with 10's or low hundreds of versions.
• is there a real problem you are seeing?
• try confirming the reason. Run:

     set pagesize 1000
     set linesize 150
     select child_number, executions, buffer_gets,
     is_bind_sensitive, is_bind_aware , is_shareable
     from v$sql  where sql_id = '<the_sqlid>' order by 1;
  

  And

     set pagesize 1000
     set long 10000
     set linesize 150
     select child_number, reason from v$sql_shared_cursor where sql_id = '<the_sqlid>' order by 1; 
  

Separately to the issue, I would encourage you to update to at least cx_Oracle 8.3.0, and preferably to the latest python-oracledb