All Forums Viewpoint
Douglas 31 posts Joined 06/10
19 Jul 2010
Help with Query Request Portlet

I am trying to develop a portlet that will display the worst queries in terms of CPU time, skew, or I/O usage over a certain period of time like a week or day. I started by pulling all of the session data by using the getSessions() function inside the sessionDAO. Then I filter out the individual session requests from that. This is currently maxing out the application memory that java allocates my program if the time frame is at one day, because of all the sessions. Is there a different way to get all the sessionRequests between two time periods?

gryback 271 posts Joined 12/08
19 Jul 2010

I'm going to have to defer on your specific request about getting the session requests from multiple time periods but I do want to let you know that the functional portlet you are developing (identification of queries utilizing various resources over a range of time) is already being developed by R&D. So I'm not sure you need to continue your efforts. If you want specific details, please contact me directly at gary.ryback@teradata.com

stever 149 posts Joined 08/09
22 Jul 2010

As of the 13.03 release of the PDK, the SessionDAO does not have methods to easily handle this request. Your best option would be to implement DataHandler and call void getSessions(DataHandler, int, Timestamp, Timestamp). Your DataHandler would then be able to filter the sessions that meet the metric criteria. Hopefully this will limit the number of Session objects that are in memory at any given time. However, please be extremely careful with this since as you already realized, you can easily cause an OutOfMemoryError in the Viewpoint JVM, which affects all of Viewpoint, not just your portlet. I would suggest setting a maximum number of Session objects that you will allow to be saved in your DataHandler. I'm not sure what the best limit is, but I would suggest no more than 1,000.

Douglas 31 posts Joined 06/10
22 Jul 2010

Here' s what I did. I broke down the time frame into one hour chunks and grabbed all the sessions from that frame, filtered out the ones i didn't need and added the keepers to a separate list for them.

My problem is in the DataHandler I have created. I have implemented it as such

interface Handler extends DataHandler
{
public List getSessions();
}

class SessionHandler implements Handler
{
List dataList;

SessionHandler()
{
dataList = new ArrayList();
}
public void handleData(T value)
{
dataList.add(value);
}
public List getSessions()
{
return dataList;
}
}

because it is implemented using a generic object "" for its I cannot filter the session objects out in the handleData method because since it is a generic java object I cannot use the session functions to check each element, that would be an ideal solution cause I could drop them here. I am forced to keep them all in a list before I can go through them.

Is there a way to implement a dataHandler without using a generic object type?
By the way it would not compile if i used instead of .

Douglas 31 posts Joined 06/10
22 Jul 2010

Would not let me use the greater than less than sign here is my class with quotes instead

interface Handler"T extends Object" extends DataHandler"T"
{

public List"T" getSessions();

}

class SessionHandler"T extends Object" implements Handler"T"
{

List"T" dataList;

SessionHandler()
{

dataList = new ArrayList"T"();

}

public void handleData(T value)
{

dataList.add(value);

}

public List"T" getSessions()
{

return dataList;

}

}

I tried using "Session" instead of "T" and it wouldn't work. I think it is because Teradata sends the elements as java objects.

stever 149 posts Joined 08/09
22 Jul 2010

Try this, with the same syntax you used above for the greater than and less than signs:

public class SessionDataHandler implements DataHandler"Session" {
List"Session" list = new ArrayList"Session"();

public List"Session" getList() {
return list;
}

public void handleData(Session session) {
Float cpuSkew = session.getCpuSkew();
if (cpuSkew != null) {
if (cpuSkew.compareTo(50f) > 0) {
list.add(session);
}
}
}
}

Douglas 31 posts Joined 06/10
27 Jul 2010

That worked great thanks still have memory problem but it can hold more than it use to and I don't have to do the time slices like I was.

Douglas 31 posts Joined 06/10
27 Jul 2010

Also I was looking at the tables in postgreSQL database and I saw that there is a query log table and a session request table. I was wondering what each table contains. I know that each row in the session request table stands for one session request. But what about the query log? Is that were all the statistics from each query is held and if so how do i reference the request to the query log.

gryback 271 posts Joined 12/08
27 Jul 2010

Access to the Viewpoint DCS should be done via the Portlet Development Kit (PDK) libraries.

Douglas 31 posts Joined 06/10
27 Jul 2010

I realize that, that is how I am accessing them I am wondering when I make a call to get querylog data what each Object represents. If each querylog object represents a query how can I use it to reference the sessionRequest object.

Or does the queryLog object represent something completely different.

Douglas 31 posts Joined 06/10
27 Jul 2010

also how do I calculate the execution time of the queries

Douglas 31 posts Joined 06/10
27 Jul 2010

I am currently calculating the execution time of the requests by using the getRequestCollectionTime() - getRequestStartTime() i am not sure if this gives me the execution time of the request.

Douglas 31 posts Joined 06/10
03 Aug 2010

I am just wondering if each querylog object represents one query or multiple. Because of the variables live average cpu usage. Are these variable that contain averages over all the steps of the query or over a time period or ? Also the get delay time min max, average makes it sound like there are multiple items here are they queries or steps or ?

stever 149 posts Joined 08/09
09 Aug 2010

The query log and session request are totally separate.

The query log contains data from the DBQL tables in Teradata. This data is summarized in 15 minute intervals.

The session request represents a snapshot of a query for which Viewpoint has collected SQL and explain. Using getRequestCollectionTime() - getRequestStartTime() will give you a good estimate of the current duration of the query. However, this data is collected every collection interval. So, if a query runs for an hour and your session sampling rate is 30 seconds, you will have about 120 different entries in the session request table.

As Gary mentioned previously, R&D is already developing a portlet and the necessary data structures to back the analysis of query execution times. Once this functionality is in place, the methods to get this data will be much simpler and able to perform in a way that will provide quick responses and not risk any kind of memory problems on the server.

Douglas 31 posts Joined 06/10
09 Aug 2010

Yeah its no big deal I rewrote the portlet to query the Teradata database directly.

Douglas 31 posts Joined 06/10
09 Aug 2010

I have the top worst queries setup in a table widget now and I am trying to make it so that when you click on a row of the table widget then it calls a function in my viewcontroller. I have the function setup and I can call it by clicking a button in my summary.jsp file but i am wondering how to set it up so that the table widget knows to use a certain url.

You must sign in to leave a comment.