SQL and Explain sometimes not visible in query monitor

Viewpoint
Enthusiast

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

9 REPLIES
Teradata Employee

Re: SQL and Explain sometimes not visible in query monitor

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. 

Enthusiast

Re: SQL and Explain sometimes not visible in query monitor

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

Enthusiast

Re: SQL and Explain sometimes not visible in query monitor

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

Teradata Employee

Re: SQL and Explain sometimes not visible in query monitor

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. 

Enthusiast

Re: SQL and Explain sometimes not visible in query monitor

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.

Teradata Employee

Re: SQL and Explain sometimes not visible in query monitor

What version of Viewpoint? 

Enthusiast

Re: SQL and Explain sometimes not visible in query monitor

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

Enthusiast

Re: SQL and Explain sometimes not visible in query monitor

Hi Gryback,Mahesh,Duri

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

Teradata Employee

Re: SQL and Explain sometimes not visible in query monitor

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.