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_nameThis 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 dualI sent that information to Java programmers and they promised to fix this issue.