I have experimented with this a bit in various configurations and with different workloads. For short transactions and high concurrency, the impact is pretty high. But that's really the absolute worst case - thousands of concurrent transactions with hundreds of thousands of queries in under a minute that previously weren't logged, all on a cluster that was tuned for the workload, not the workload + query logging.
For the more general case, a workload where the queries are in, say, the 10-59 second category and there aren't thousands running at once, then the impact of setting the threshold down to zero is much lower. Depending on the cluster load, it may not be highly noticed. So this is definitely worth trying, but be aware that there can be performance impacts to setting the threshold to 0, depending on the workload. In some cases, there might be a need to increase the cluster capacity (add more nodes/disks) to achieve workload SLAs while permanently capturing data for all queries. -Carol P. On Sun, Jul 29, 2018 at 11:13 PM Liu, Yuan (Yuan) <[email protected]> wrote: > We can also add below configuration into dcs-site.xml to save queries less > than 60 seconds into repository. > > <property> > <name> dcs.server.user.program.statistics.limit.time</name> > <value>0</value> > </property> > > The value 0 means all sql will be saved into repository. We can set the > value as we want. > > > Best regards > > 刘源（Yuan） > 上海易鲸捷信息技术有限公司 > 地址：上海市浦东新区金科路2889号长泰广场A座603 > 手机：13671935540 > 邮箱：[email protected] > > > -----Original Message----- > From: Selva Govindarajan <[email protected]> > Sent: Monday, July 30, 2018 1:49 PM > To: [email protected] > Subject: RE: How to analyze the completed sql statement > > Statistics of the running queries are stored in shared segment. To avoid > shared segment becoming full, the statistics is removed as soon as the > query is deallocated. I believe If the query takes longer than 60 seconds, > the end statistics will be written to repository by default. This time > duration of the query to be written can be configured. > > In addition, the monitored queries are kept in the shared segment even > after the query is deallocated. You can start monitoring the query by > issuing get statistics for qid <qid> or any other equivalent command > involving query id. However, if the query takes a very short time, it is > possible that query could complete before you start monitoring it. > > Thanks, Selva > > -----Original Message----- > From: Yang, Peng-Peng <[email protected]> > Sent: Sunday, July 29, 2018 9:55 PM > To: [email protected] > Subject: How to analyze the completed sql statement > > Hi Trafodioneers, > > > Sometimes we need to analyze the sql running at the end, how can we get > the statistics of the completed sql? > Or configure sql statistics to stay longer in the offender? > Not "get statistics for qid current;", > > > Regards, > Pengpeng > >
