Q: SlowQueryListener couldn't record bind values

[ethanyhwang]

i want to record slow sql with bind values like ebean debug sql. for example " DEBUG io.ebean.SQL - txn[1016] select t0.relationId, t0.accountId, t0.advertiserType, t0.advertiserId, t0.status, t0.relationType, t0.inviteCode, t0.inviteTime, t0.deleteStatus, t0.ukSuffix, t0.createdTime, t0.lastModifiedTime from TOneAdvertiserRelation t0 where accountId = ? and t0.deleteStatus = ? and t0.relationType in (? ) order by t0.relationId; --bind(183061,false,Array[1]={0}) "

how can i do it

[rbygrave]

Well SlowQueryListener does not have access to the bind log - so you can't. Are you wanting the bind values for the purpose of getting the query plan? If so then note that Ebean has a different mechanism for that via MetaInfoManager.

…

On Sat, 9 Nov 2019, 20:58 ethanwang1225, ***@***.***> wrote: i want to record slow sql with bind values like ebean debug sql. for example " DEBUG io.ebean.SQL - txn[1016] select t0.relationId, t0.accountId, t0.advertiserType, t0.advertiserId, t0.status, t0.relationType, t0.inviteCode, t0.inviteTime, t0.deleteStatus, t0.ukSuffix, t0.createdTime, t0.lastModifiedTime from TOneAdvertiserRelation t0 where accountId = ? and t0.deleteStatus = ? and t0.relationType in (? ) order by t0.relationId; --bind(183061,false,Array[1]={0}) " how can i do it — You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub <#1857?email_source=notifications&email_token=AABTATLRCTI2ZV5VLBTP4G3QSZUTLA5CNFSM4JLELJEKYY3PNVWWK3TUL52HS4DFUVEXG43VMWVGG33NNVSW45C7NFSM4HYD7KAA>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AABTATKIVAXOXFB5EUK5YQLQSZUTLANCNFSM4JLELJEA> .

[ethanyhwang]

I need to know bind values of the slow query to better analyze why it is slow

[rbygrave]

Note that I am working on the metrics reporting service which has the potential to enable the collection of query plans (in a click a button on a dashboard kind of way).

So working on that and looking to come back to this when that is further on (so maybe a few weeks).

[rbygrave]

Documentation update:

To enable query plan capture:

• DatabaseConfig.setCollectQueryPlans(true)
• DatabaseConfig.setCollectQueryPlanThresholdMicros(50_000); // 50_000 = 50 milliseconds

This means that the internal ebean query plans are setup to capture bind values (for the purpose of obtaining query explain plans). The threshold is the minimum query execution time required to capture bind values in microseconds (not millis).

Once bind values are captured they are captured again if the query time is 1.3 * larger than the currently captured query time. (I'll change this to 1.5 times).

Periodically we then capture query plans in the background. When we do this we give the maximum number of plans to capture and or the maximum amount of time we allow query plan capture to take.

    // obtains db query plans ...
    QueryPlanRequest request = new QueryPlanRequest();
    request.setMaxCount(5); // max number of plans we will capture
    request.setMaxTimeMillis(1_000); // max time after which we don't continue collecting plans

    // collect the query plans ... (executes the query using **explain** with the latest captured bind values)
    List<MetaQueryPlan> plans =database.getMetaInfoManager().queryPlanCollectNow(request);

   // we can then log the plans ...
   for (MetaQueryPlan plan : plans) {

     String rawPlan = plan.getPlan();
     String sql = plan.getSql();
     String bindLog = plan.getBind();
     String label = plan.getLabel();
     long queryTimeMicros = plan.getQueryTimeMicros();
     ProfileLocation profileLocation = plan.getProfileLocation();
      
      ...
   }

  // to run a period task like this we could ...
   DB.getBackgroundExecutor().scheduleWithFixedDelay(...)

FYI @xu1009

[rbygrave]

This feature is now in 14.1.0, where we can access the bind values used in the slow query.