You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@trafodion.apache.org by "Yang, Peng-Peng" <pe...@esgyn.cn> on 2018/07/30 09:08:29 UTC
答复: How to analyze the completed sql statement
Hi Selva, Yuan, Carol
Thanks for your feedback.
I have set up dcs-site.xml. But I can't get the correct explain plan, it looks like it's garbled? How can I decode it into the text explain plan?
Attachment is a screenshot of the explain plan now.
Regards,
Pengpeng
-----邮件原件-----
发件人: Carol Pearson <ca...@gmail.com>
发送时间: 2018年7月30日 14:53
收件人: dev@trafodion.apache.org
主题: Re: How to analyze the completed sql statement
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) <yu...@esgyn.cn> 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
> 邮箱：yuan.liu@esgyn.cn
>
>
> -----Original Message-----
> From: Selva Govindarajan <se...@esgyn.com>
> Sent: Monday, July 30, 2018 1:49 PM
> To: dev@trafodion.apache.org
> 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 <pe...@esgyn.cn>
> Sent: Sunday, July 29, 2018 9:55 PM
> To: dev@trafodion.apache.org
> 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
>
>
答复: How to analyze the completed sql statement
Posted by "Yang, Peng-Peng" <pe...@esgyn.cn>.
[cid:image001.png@01D42829.D3C96B70]
Regards,
Pengpeng
-----邮件原件-----
发件人: Yang, Peng-Peng <pe...@esgyn.cn>
发送时间: 2018年7月30日 17:12
收件人: dev@trafodion.apache.org
主题: 答复: How to analyze the completed sql statement
Add attachment.
Regards,
Pengpeng
-----邮件原件-----
发件人: Yang, Peng-Peng <pe...@esgyn.cn>>
发送时间: 2018年7月30日 17:08
收件人: dev@trafodion.apache.org<ma...@trafodion.apache.org>
主题: 答复: How to analyze the completed sql statement
Hi Selva, Yuan, Carol
Thanks for your feedback.
I have set up dcs-site.xml. But I can't get the correct explain plan, it looks like it's garbled? How can I decode it into the text explain plan?
Attachment is a screenshot of the explain plan now.
Regards,
Pengpeng
-----邮件原件-----
发件人: Carol Pearson <ca...@gmail.com>>
发送时间: 2018年7月30日 14:53
收件人: dev@trafodion.apache.org<ma...@trafodion.apache.org>
主题: Re: How to analyze the completed sql statement
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) <yu...@esgyn.cn>> 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
> 邮箱：yuan.liu@esgyn.cn<ma...@esgyn.cn>
>
>
> -----Original Message-----
> From: Selva Govindarajan <se...@esgyn.com>>
> Sent: Monday, July 30, 2018 1:49 PM
> To: dev@trafodion.apache.org<ma...@trafodion.apache.org>
> 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 <pe...@esgyn.cn>>
> Sent: Sunday, July 29, 2018 9:55 PM
> To: dev@trafodion.apache.org<ma...@trafodion.apache.org>
> 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
>
>
答复: How to analyze the completed sql statement
Posted by "Yang, Peng-Peng" <pe...@esgyn.cn>.
Thanks Anoop.
-----邮件原件-----
发件人: Anoop Sharma <an...@esgyn.com>
发送时间: 2018年7月31日 0:37
收件人: dev@trafodion.apache.org
主题: RE: How to analyze the completed sql statement
once the query finishes, it is removed from rms and moved to repository unless it is a short running query.
To see explain from repository, use the following command:
(in your example, you are missing the keyword ‘QID’).
>>explain options 'f' qid mxid123456 from repository;
LC RC OP OPERATOR OPT DESCRIPTION CARD
---- ---- ---- -------------------- -------- -------------------- ---------
1 . 2 root 1.00E+002
. . 1 trafodion_scan T011T1 1.00E+002
--- SQL operation complete.
>>
-----Original Message-----
From: Yang, Peng-Peng <pe...@esgyn.cn>
Sent: Monday, July 30, 2018 9:08 AM
To: dev@trafodion.apache.org
Subject: 答复: How to analyze the completed sql statement
Thanks Selva for your reply.
I can't get the correct result. Do seem Its explain plan is not in the shared segment?
The error log as follows:
---------------------------------------------------------------------------
SQL> explain options 'f' for qid
SQL> MXID11001023192212399731868454002000000000906U3333308T150000000_411
SQL> _SQL_CUR_7;
*** ERROR[8017] Explain information is not available for this query. [2018-07-30 23:55:50]
SQL> explain options 'f'
SQL> 'MXID11001023192212399731868454002000000000906U3333308T150000000_41
SQL> 1_SQL_CUR_7' from RMS;
*** ERROR[15001] A syntax error occurred at or before:
explain options 'f' 'MXID11001023192212399731868454002000000000906U3333308T1500
00000_411_SQL_CUR_7' from RMS;
^ (99 characters from start of SQL statement) [2018-07-30 23:57:51]
SQL> explain options 'f'
SQL> MXID11001023192212399731868454002000000000906U3333308T150000000_411
SQL> _SQL_CUR_7 from repository;
*** ERROR[15001] A syntax error occurred at or before:
explain options 'f' MXID11001023192212399731868454002000000000906U3333308T15000
0000_411_SQL_CUR_7 from repository;
^ (102 characters from start of SQL statement) [2018-07-30 23:59:08]
---------------------------------------------------------------------------
The commands I use are as follows:
---------------------------------------------------------------------------
SELECT COMPILE_ELAPSED_TIME/1000, process_name,query_elapsed_time/1000, explain_plan FROM TRAFODION."_REPOS_".METRIC_QUERY_TABLE
where query_id in ('MXID11001023192212399731868454002000000000906U3333308T150000000_411_SQL_CUR_7','MXID11001023604212399731868589001000000001806U3333308T150000000_847_SQL_CUR_1', 'MXID11001023098212399731868320003000000002706U3333308T150000000_1401_SQL_CUR_5');
---------------------------------------------------------------------------
Regards,
Pengpeng
-----邮件原件-----
发件人: Selva Govindarajan <se...@esgyn.com>>
发送时间: 2018年7月30日 23:03
收件人: dev@trafodion.apache.org<ma...@trafodion.apache.org>
主题: RE: How to analyze the completed sql statement
You can issue
explain options ‘f’ for qid <qid> or explain options ‘f’ <qid> from RMS – To get the explain from the RMS shared segment explain options ‘f’ <qid> from repository – To get the explain plan from repository
What command did you use?
Selva
From: Yang, Peng-Peng <pe...@esgyn.cn>>>
Sent: Monday, July 30, 2018 2:28 AM
To: dev@trafodion.apache.org<ma...@trafodion.apache.org>>
Subject: 答复: How to analyze the completed sql statement
[cid:image001.png@01D4282A.AC84AA60]
Regards,
Pengpeng
-----邮件原件-----
发件人: Yang, Peng-Peng <pe...@esgyn.cn>>>>
发送时间: 2018年7月30日 17:12
收件人: dev@trafodion.apache.org<ma...@trafodion.apache.org>>>
主题: 答复: How to analyze the completed sql statement
Add attachment.
Regards,
Pengpeng
-----邮件原件-----
发件人: Yang, Peng-Peng <pe...@esgyn.cn>>>>
发送时间: 2018年7月30日 17:08
收件人: dev@trafodion.apache.org<ma...@trafodion.apache.org>>>
主题: 答复: How to analyze the completed sql statement
Hi Selva, Yuan, Carol
Thanks for your feedback.
I have set up dcs-site.xml. But I can't get the correct explain plan, it looks like it's garbled? How can I decode it into the text explain plan?
Attachment is a screenshot of the explain plan now.
Regards,
Pengpeng
-----邮件原件-----
发件人: Carol Pearson <ca...@gmail.com>>>>
发送时间: 2018年7月30日 14:53
收件人: dev@trafodion.apache.org<ma...@trafodion.apache.org>>>
主题: Re: How to analyze the completed sql statement
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) <yu...@esgyn.cn>>>> 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
> 邮箱：yuan.liu@esgyn.cn<mailto:yuan.liu@esgyn.cn<mailto:yuan.liu@esgyn.cn
> %3cmailto:yuan.liu@esgyn.cn<mailto:yuan.liu@esgyn.cn<mailto:yuan.liu@e
> sgyn.cn<ma...@esgyn.cn>>>
>
>
> -----Original Message-----
> From: Selva Govindarajan
> <selva.govindarajan@esgyn.com<mailto:selva.govindarajan@esgyn.com<mail
> to:selva.govindarajan@esgyn.com%3cmailto:selva.govindarajan@esgyn.com<
> mailto:selva.govindarajan@esgyn.com<mailto:selva.govindarajan@esgyn.co
> m<mailto:selva.govindarajan@esgyn.com%3cmailto:selva.govindarajan@esgy
> n.com>>>>
> Sent: Monday, July 30, 2018 1:49 PM
> To:
> dev@trafodion.apache.org<mailto:dev@trafodion.apache.org<mailto:dev@tr
> afodion.apache.org%3cmailto:dev@trafodion.apache.org<mailto:dev@trafod
> ion.apache.org<mailto:dev@trafodion.apache.org<mailto:dev@trafodion.ap
> ache.org%3cmailto:dev@trafodion.apache.org>>>
> 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
> <pengpeng.yang@esgyn.cn<mailto:pengpeng.yang@esgyn.cn<mailto:pengpeng.
> yang@esgyn.cn%3cmailto:pengpeng.yang@esgyn.cn<mailto:pengpeng.yang@esg
> yn.cn<mailto:pengpeng.yang@esgyn.cn<mailto:pengpeng.yang@esgyn.cn%3cma
> ilto:pengpeng.yang@esgyn.cn>>>>
> Sent: Sunday, July 29, 2018 9:55 PM
> To:
> dev@trafodion.apache.org<mailto:dev@trafodion.apache.org<mailto:dev@tr
> afodion.apache.org%3cmailto:dev@trafodion.apache.org<mailto:dev@trafod
> ion.apache.org<mailto:dev@trafodion.apache.org<mailto:dev@trafodion.ap
> ache.org%3cmailto:dev@trafodion.apache.org>>>
> 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
>
>
RE: How to analyze the completed sql statement
Posted by Anoop Sharma <an...@esgyn.com>.
once the query finishes, it is removed from rms and moved to repository
unless it is a short running query.
To see explain from repository, use the following command:
(in your example, you are missing the keyword ‘QID’).
>>explain options 'f' qid mxid123456 from repository;
LC RC OP OPERATOR OPT DESCRIPTION CARD
---- ---- ---- -------------------- -------- -------------------- ---------
1 . 2 root 1.00E+002
. . 1 trafodion_scan T011T1 1.00E+002
--- SQL operation complete.
>>
-----Original Message-----
From: Yang, Peng-Peng <pe...@esgyn.cn>
Sent: Monday, July 30, 2018 9:08 AM
To: dev@trafodion.apache.org
Subject: 答复: How to analyze the completed sql statement
Thanks Selva for your reply.
I can't get the correct result. Do seem Its explain plan is not in the shared segment?
The error log as follows:
---------------------------------------------------------------------------
SQL> explain options 'f' for qid MXID11001023192212399731868454002000000000906U3333308T150000000_411_SQL_CUR_7;
*** ERROR[8017] Explain information is not available for this query. [2018-07-30 23:55:50]
SQL> explain options 'f' 'MXID11001023192212399731868454002000000000906U3333308T150000000_411_SQL_CUR_7' from RMS;
*** ERROR[15001] A syntax error occurred at or before:
explain options 'f' 'MXID11001023192212399731868454002000000000906U3333308T1500
00000_411_SQL_CUR_7' from RMS;
^ (99 characters from start of SQL statement) [2018-07-30 23:57:51]
SQL> explain options 'f' MXID11001023192212399731868454002000000000906U3333308T150000000_411_SQL_CUR_7 from repository;
*** ERROR[15001] A syntax error occurred at or before:
explain options 'f' MXID11001023192212399731868454002000000000906U3333308T15000
0000_411_SQL_CUR_7 from repository;
^ (102 characters from start of SQL statement) [2018-07-30 23:59:08]
---------------------------------------------------------------------------
The commands I use are as follows:
---------------------------------------------------------------------------
SELECT COMPILE_ELAPSED_TIME/1000, process_name,query_elapsed_time/1000, explain_plan FROM TRAFODION."_REPOS_".METRIC_QUERY_TABLE
where query_id in ('MXID11001023192212399731868454002000000000906U3333308T150000000_411_SQL_CUR_7','MXID11001023604212399731868589001000000001806U3333308T150000000_847_SQL_CUR_1', 'MXID11001023098212399731868320003000000002706U3333308T150000000_1401_SQL_CUR_5');
---------------------------------------------------------------------------
Regards,
Pengpeng
-----邮件原件-----
发件人: Selva Govindarajan <se...@esgyn.com>>
发送时间: 2018年7月30日 23:03
收件人: dev@trafodion.apache.org<ma...@trafodion.apache.org>
主题: RE: How to analyze the completed sql statement
You can issue
explain options ‘f’ for qid <qid> or explain options ‘f’ <qid> from RMS – To get the explain from the RMS shared segment explain options ‘f’ <qid> from repository – To get the explain plan from repository
What command did you use?
Selva
From: Yang, Peng-Peng <pe...@esgyn.cn>>>
Sent: Monday, July 30, 2018 2:28 AM
To: dev@trafodion.apache.org<ma...@trafodion.apache.org>>
Subject: 答复: How to analyze the completed sql statement
[cid:image001.png@01D4282A.AC84AA60]
Regards,
Pengpeng
-----邮件原件-----
发件人: Yang, Peng-Peng <pe...@esgyn.cn>>>>
发送时间: 2018年7月30日 17:12
收件人: dev@trafodion.apache.org<ma...@trafodion.apache.org>>>
主题: 答复: How to analyze the completed sql statement
Add attachment.
Regards,
Pengpeng
-----邮件原件-----
发件人: Yang, Peng-Peng <pe...@esgyn.cn>>>>
发送时间: 2018年7月30日 17:08
收件人: dev@trafodion.apache.org<ma...@trafodion.apache.org>>>
主题: 答复: How to analyze the completed sql statement
Hi Selva, Yuan, Carol
Thanks for your feedback.
I have set up dcs-site.xml. But I can't get the correct explain plan, it looks like it's garbled? How can I decode it into the text explain plan?
Attachment is a screenshot of the explain plan now.
Regards,
Pengpeng
-----邮件原件-----
发件人: Carol Pearson <ca...@gmail.com>>>>
发送时间: 2018年7月30日 14:53
收件人: dev@trafodion.apache.org<ma...@trafodion.apache.org>>>
主题: Re: How to analyze the completed sql statement
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) <yu...@esgyn.cn>>>> 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
> 邮箱：yuan.liu@esgyn.cn<ma...@esgyn.cn>>>
>
>
> -----Original Message-----
> From: Selva Govindarajan
> <se...@esgyn.com>>>>
> Sent: Monday, July 30, 2018 1:49 PM
> To: dev@trafodion.apache.org<ma...@trafodion.apache.org>>>
> 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
> <pe...@esgyn.cn>>>>
> Sent: Sunday, July 29, 2018 9:55 PM
> To: dev@trafodion.apache.org<ma...@trafodion.apache.org>>>
> 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
>
>
答复: How to analyze the completed sql statement
Posted by "Yang, Peng-Peng" <pe...@esgyn.cn>.
Thanks Selva for your reply.
I can't get the correct result. Do seem Its explain plan is not in the shared segment?
The error log as follows:
---------------------------------------------------------------------------
SQL> explain options 'f' for qid MXID11001023192212399731868454002000000000906U3333308T150000000_411_SQL_CUR_7;
*** ERROR[8017] Explain information is not available for this query. [2018-07-30 23:55:50]
SQL> explain options 'f' 'MXID11001023192212399731868454002000000000906U3333308T150000000_411_SQL_CUR_7' from RMS;
*** ERROR[15001] A syntax error occurred at or before:
explain options 'f' 'MXID11001023192212399731868454002000000000906U3333308T1500
00000_411_SQL_CUR_7' from RMS;
^ (99 characters from start of SQL statement) [2018-07-30 23:57:51]
SQL> explain options 'f' MXID11001023192212399731868454002000000000906U3333308T150000000_411_SQL_CUR_7 from repository;
*** ERROR[15001] A syntax error occurred at or before:
explain options 'f' MXID11001023192212399731868454002000000000906U3333308T15000
0000_411_SQL_CUR_7 from repository;
^ (102 characters from start of SQL statement) [2018-07-30 23:59:08]
---------------------------------------------------------------------------
The commands I use are as follows:
---------------------------------------------------------------------------
SELECT COMPILE_ELAPSED_TIME/1000, process_name,query_elapsed_time/1000, explain_plan FROM TRAFODION."_REPOS_".METRIC_QUERY_TABLE
where query_id in ('MXID11001023192212399731868454002000000000906U3333308T150000000_411_SQL_CUR_7','MXID11001023604212399731868589001000000001806U3333308T150000000_847_SQL_CUR_1', 'MXID11001023098212399731868320003000000002706U3333308T150000000_1401_SQL_CUR_5');
---------------------------------------------------------------------------
Regards,
Pengpeng
-----邮件原件-----
发件人: Selva Govindarajan <se...@esgyn.com>
发送时间: 2018年7月30日 23:03
收件人: dev@trafodion.apache.org
主题: RE: How to analyze the completed sql statement
You can issue
explain options ‘f’ for qid <qid> or explain options ‘f’ <qid> from RMS – To get the explain from the RMS shared segment explain options ‘f’ <qid> from repository – To get the explain plan from repository
What command did you use?
Selva
From: Yang, Peng-Peng <pe...@esgyn.cn>>
Sent: Monday, July 30, 2018 2:28 AM
To: dev@trafodion.apache.org<ma...@trafodion.apache.org>
Subject: 答复: How to analyze the completed sql statement
[cid:image001.png@01D4282A.AC84AA60]
Regards,
Pengpeng
-----邮件原件-----
发件人: Yang, Peng-Peng <pe...@esgyn.cn>>>
发送时间: 2018年7月30日 17:12
收件人: dev@trafodion.apache.org<ma...@trafodion.apache.org>>
主题: 答复: How to analyze the completed sql statement
Add attachment.
Regards,
Pengpeng
-----邮件原件-----
发件人: Yang, Peng-Peng <pe...@esgyn.cn>>>
发送时间: 2018年7月30日 17:08
收件人: dev@trafodion.apache.org<ma...@trafodion.apache.org>>
主题: 答复: How to analyze the completed sql statement
Hi Selva, Yuan, Carol
Thanks for your feedback.
I have set up dcs-site.xml. But I can't get the correct explain plan, it looks like it's garbled? How can I decode it into the text explain plan?
Attachment is a screenshot of the explain plan now.
Regards,
Pengpeng
-----邮件原件-----
发件人: Carol Pearson <ca...@gmail.com>>>
发送时间: 2018年7月30日 14:53
收件人: dev@trafodion.apache.org<ma...@trafodion.apache.org>>
主题: Re: How to analyze the completed sql statement
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) <yu...@esgyn.cn>>> 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
> 邮箱：yuan.liu@esgyn.cn<ma...@esgyn.cn>>
>
>
> -----Original Message-----
> From: Selva Govindarajan
> <se...@esgyn.com>>>
> Sent: Monday, July 30, 2018 1:49 PM
> To: dev@trafodion.apache.org<ma...@trafodion.apache.org>>
> 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
> <pe...@esgyn.cn>>>
> Sent: Sunday, July 29, 2018 9:55 PM
> To: dev@trafodion.apache.org<ma...@trafodion.apache.org>>
> 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
>
>
RE: How to analyze the completed sql statement
Posted by Selva Govindarajan <se...@esgyn.com>.
You can issue
explain options ‘f’ for qid <qid> or explain options ‘f’ <qid> from RMS – To get the explain from the RMS shared segment
explain options ‘f’ <qid> from repository – To get the explain plan from repository
What command did you use?
Selva
From: Yang, Peng-Peng <pe...@esgyn.cn>
Sent: Monday, July 30, 2018 2:28 AM
To: dev@trafodion.apache.org
Subject: 答复: How to analyze the completed sql statement
[cid:image001.png@01D4282A.AC84AA60]
Regards,
Pengpeng
-----邮件原件-----
发件人: Yang, Peng-Peng <pe...@esgyn.cn>>
发送时间: 2018年7月30日 17:12
收件人: dev@trafodion.apache.org<ma...@trafodion.apache.org>
主题: 答复: How to analyze the completed sql statement
Add attachment.
Regards,
Pengpeng
-----邮件原件-----
发件人: Yang, Peng-Peng <pe...@esgyn.cn>>
发送时间: 2018年7月30日 17:08
收件人: dev@trafodion.apache.org<ma...@trafodion.apache.org>
主题: 答复: How to analyze the completed sql statement
Hi Selva, Yuan, Carol
Thanks for your feedback.
I have set up dcs-site.xml. But I can't get the correct explain plan, it looks like it's garbled? How can I decode it into the text explain plan?
Attachment is a screenshot of the explain plan now.
Regards,
Pengpeng
-----邮件原件-----
发件人: Carol Pearson <ca...@gmail.com>>
发送时间: 2018年7月30日 14:53
收件人: dev@trafodion.apache.org<ma...@trafodion.apache.org>
主题: Re: How to analyze the completed sql statement
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) <yu...@esgyn.cn>> 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
> 邮箱：yuan.liu@esgyn.cn<ma...@esgyn.cn>
>
>
> -----Original Message-----
> From: Selva Govindarajan <se...@esgyn.com>>
> Sent: Monday, July 30, 2018 1:49 PM
> To: dev@trafodion.apache.org<ma...@trafodion.apache.org>
> 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 <pe...@esgyn.cn>>
> Sent: Sunday, July 29, 2018 9:55 PM
> To: dev@trafodion.apache.org<ma...@trafodion.apache.org>
> 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
>
>
答复: How to analyze the completed sql statement
Posted by "Yang, Peng-Peng" <pe...@esgyn.cn>.
[cid:image001.png@01D4282A.AC84AA60]
Regards,
Pengpeng
-----邮件原件-----
发件人: Yang, Peng-Peng <pe...@esgyn.cn>
发送时间: 2018年7月30日 17:12
收件人: dev@trafodion.apache.org
主题: 答复: How to analyze the completed sql statement
Add attachment.
Regards,
Pengpeng
-----邮件原件-----
发件人: Yang, Peng-Peng <pe...@esgyn.cn>>
发送时间: 2018年7月30日 17:08
收件人: dev@trafodion.apache.org<ma...@trafodion.apache.org>
主题: 答复: How to analyze the completed sql statement
Hi Selva, Yuan, Carol
Thanks for your feedback.
I have set up dcs-site.xml. But I can't get the correct explain plan, it looks like it's garbled? How can I decode it into the text explain plan?
Attachment is a screenshot of the explain plan now.
Regards,
Pengpeng
-----邮件原件-----
发件人: Carol Pearson <ca...@gmail.com>>
发送时间: 2018年7月30日 14:53
收件人: dev@trafodion.apache.org<ma...@trafodion.apache.org>
主题: Re: How to analyze the completed sql statement
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) <yu...@esgyn.cn>> 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
> 邮箱：yuan.liu@esgyn.cn<ma...@esgyn.cn>
>
>
> -----Original Message-----
> From: Selva Govindarajan <se...@esgyn.com>>
> Sent: Monday, July 30, 2018 1:49 PM
> To: dev@trafodion.apache.org<ma...@trafodion.apache.org>
> 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 <pe...@esgyn.cn>>
> Sent: Sunday, July 29, 2018 9:55 PM
> To: dev@trafodion.apache.org<ma...@trafodion.apache.org>
> 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
>
>
答复: How to analyze the completed sql statement
Posted by "Yang, Peng-Peng" <pe...@esgyn.cn>.
Add attachment.
Regards,
Pengpeng
-----邮件原件-----
发件人: Yang, Peng-Peng <pe...@esgyn.cn>
发送时间: 2018年7月30日 17:08
收件人: dev@trafodion.apache.org
主题: 答复: How to analyze the completed sql statement
Hi Selva, Yuan, Carol
Thanks for your feedback.
I have set up dcs-site.xml. But I can't get the correct explain plan, it looks like it's garbled? How can I decode it into the text explain plan?
Attachment is a screenshot of the explain plan now.
Regards,
Pengpeng
-----邮件原件-----
发件人: Carol Pearson <ca...@gmail.com>
发送时间: 2018年7月30日 14:53
收件人: dev@trafodion.apache.org
主题: Re: How to analyze the completed sql statement
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) <yu...@esgyn.cn> 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
> 邮箱：yuan.liu@esgyn.cn
>
>
> -----Original Message-----
> From: Selva Govindarajan <se...@esgyn.com>
> Sent: Monday, July 30, 2018 1:49 PM
> To: dev@trafodion.apache.org
> 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 <pe...@esgyn.cn>
> Sent: Sunday, July 29, 2018 9:55 PM
> To: dev@trafodion.apache.org
> 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
>
>