Страницы

понедельник, 22 декабря 2014 г.

c3p0 PROBABLYNOT connection test query

I am participating in the project, which uses Play framework version 1.x.
Business logic at the application level, data stored in the Oracle database.
An SQL query below caught my attention:
--sql_id=az33m61ym46y4
SELECT NULL AS table_cat,
       o.owner AS table_schem,
       o.object_name AS table_name,
       o.object_type AS table_type,
       NULL AS remarks
  FROM all_objects o
  WHERE o.owner LIKE :1 ESCAPE '/'
    AND o.object_name LIKE :2 ESCAPE '/'
    AND o.object_type IN ('xxx', 'TABLE')
  ORDER BY table_type, table_schem, table_name
This query was executed 100K times per hour.
I enable SQL trace for a short time period to help diagnose this issue further:
alter system set events 'sql_trace[sql:az33m61ym46y4] bind=true';
"Bad" query was executed by different users but has same bind variable data:
BINDS #18446744071469205160:
Bind#0
  oacdty=01 mxl=32(04) mxlc=00 mal=00 scl=00 pre=00
  oacflg=03 fl2=1000010 frm=01 csi=873 siz=160 off=0
  kxsbbbfp=ffffffff7a760438  bln=32  avl=01  flg=05
  value="%"
Bind#1
  oacdty=01 mxl=128(44) mxlc=00 mal=00 scl=00 pre=00
  oacflg=03 fl2=1000010 frm=01 csi=873 siz=0 off=32
  kxsbbbfp=ffffffff7a760458  bln=128  avl=11  flg=01
  value="PROBABLYNOT"
Looks like we are faced with a c3p0 default connection test query.
Play framework uses the popular c3p0 for its connection pool. This query could be changed for something lightweight, such as:
select 'x' from dual
I sent that information to Java programmers and they promised to fix this issue.