All Forums Viewpoint
Duri83 17 posts Joined 01/12
24 Apr 2013
SQL and Explain sometimes not visible in query monitor

Hello VP experts,
We have noticed a weird behaviour after VP and DB upgrade to version 14. Our VP is now V14.00.00.05-b49 and DBs are on 14.00.02.05 and 14.00.03.03.
The problem is that sql and explain texts are quite often not visible in VP (usually when you need to see they are not there ^_^)
I have read somewhere that it could be problem with collection rates, but cannot find the post anymore.
Our rates are:
Monitor Rates:
Session sampling - 30s
Node/Vproc Collection - 60s
Node/Vproc Logging - 600s
Data Collectors > Sessions:
Sample rate - Recommended sample rate (I guess this makes it 30s)
SQL and Explain Tresholds
 - Elapsed seconds - 10
 - Total CPU seconds - 5
 
Is there a way how to identify if we hit those tresholds? Any other suggestions?
GLHF,
Yuri

gryback 271 posts Joined 12/08
25 Apr 2013

Visibility of SQL and Explain plans in Viewpoint have a number of dependencies which you have alluded to above. Let me provide a high level explanation for everyone's benefit.
 
The "Session Sampling" which is set in Admin -> Monitor Rates defines how often Teradata DB collects information to support the API interface. The Admin -> Teradata Systems -> Data Collectors collection rate informs Viewpoint how often to pull data from Teradata. As such, the collections rate should not be more often than sampling otherwise you will be unnecssarily pulling duplicate data. 
 
The SQL and Explain thresholds qualifies queries for SQL and Explain collection. If a query doesn't exceed one of those two thresholds, for efficiency, the SQL and Explain is not collected into Viewpoint. 
 
So Duri, your configuration appears to be set to best practices allowing necessary but also efficient SQL and Explain collections. You should be able to verify in Query Monitor based on the "CPU Use" and "Duration" columns. If either of these columns has a value greater than your SQL and Explain thresholds, you should be seeing that information on drill down details. 

Duri83 17 posts Joined 01/12
29 Apr 2013

Hello Gary,
thanks for explaining the relations of the tresholds. However, we often have a bad queries running for long time, consuming high cpu and we do not see thier SQLs and Explain plans. For example today, we had a very bad query running for approx. 2 hrs, consuming 105365,61 CPU.
I have seen that sometimes SQL and Explain info changes from visible to invisible and vice versa during the run of the query. At those case, I can use rewind to see what is/was the query doing.
Any pointers on what could be the cause (before we involve TDCS)?
GLHF,
Yuri

Duri83 17 posts Joined 01/12
29 Apr 2013

Hello again Gary,
I think I have figured it out (partially).
We do not see this information for the queries, no matter what they consume in case they run from a stored procedure.
I did this little test with 3 session on my own:
DDLs:
CREATE SET TABLE DataLab03.aaaa ,NO FALLBACK ,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL,
     CHECKSUM = DEFAULT,
     DEFAULT MERGEBLOCKRATIO
     (
      col1 INTEGER,
      col2 INTEGER,
      col3 INTEGER)
PRIMARY INDEX ( col1 );
REPLACE PROCEDURE datalab03.testproc()

sql security creator
begin
proc:
begin
delete from datalab03.aaaa;
end proc;
end;
1. session:
bt;
lock table datalab03.aaaa for write;
2. session:
call datalab03.testproc();
3. session:
delete from datalab03.aaaa;
The 1. session holds the lock and I keep the transaction open in order to keep the other 2 blocked.
I observe that I never get SQL info for the 2. session, but get SQL infor for the 3. session (most of the time - but not all always).
The question now is - is this intended?
GLHF,
Yuri

gryback 271 posts Joined 12/08
30 Apr 2013

I checked into this and there is an open issue being investigated for getting the SQL/Explain information for stored procedures. I would suggest you submit a Teradata support incident so you can get notified on this issue status directly. 

tgorner 4 posts Joined 10/08
07 May 2013

I'm afraid it's not just stored procedures. I experience the very same behaviour (while having the very same settings as Duri) with basically any SQL.
I have a query that has been running for the last 25 minutes. In Viewpoint, during its execution, I sometimes see the SQL and Explain tabs, sometimes they're greyed out. Mostly greyed out though... I am positive that I am always checking the very same sessionid.
PMON still shows the expected: SQL and execution plan with the current step being highlighted.

gryback 271 posts Joined 12/08
17 May 2013

What version of Viewpoint? 

maheshgodi 25 posts Joined 05/06
19 Aug 2013

After changeing Monitor Rates & Data Collectors > Sessions , Restart you viewpoint server . You will see SQL and Explain in query monitor

varanasianup 14 posts Joined 09/14
15 Jul 2015

Hi Gryback,Mahesh,Duri
 
I am facing this issue, can u let me know the if the above worked ?

gryback 271 posts Joined 12/08
15 Jul 2015

We are already discussing this with you in the other forum thread. No need to address the same issue in two threads. This other issue was 2 years ago ... so very likely not relevant to your current issue.

You must sign in to leave a comment.