Database monitor performance analysis example 1

 

Determine which queries in your SQL application are implemented with table scans. The complete information can be obtained by joining two views: QQQ1000, which contains information about the SQL statements, and QQQ3000, which contains data about queries performing table scans.

The following SQL query can be used:

SELECT A.System_Table_Schema, A.System_Table_Name, A.Table_Total_Rows, A.Index_Advised, 
   C.Number_Rows_Returned, (B.End_Timestamp - B.Start_Timestamp) 
   AS TOT_TIME, B.Statement_Text_Long    FROM LIB/QQQ3000 A, LIB/QQQ1000 B, LIB/QQQ3019 C    WHERE A.Join_Column = B.Join_Column 
   AND A.Unique_Count = B.Unique_Count    AND A.Join_Column = C.Join_Column 
   AND A.Unique_Count = C.Unique_Count 

Sample output of this query is shown in the table below. Key to this example are the join criteria:

 WHERE A.Join_Column = B.Join_Column    AND A.Join_Column = C.Join_Column

A lot of data about many queries is contained in multiple rows in table LIB/PERFDATA. It is not uncommon for data about a single query to be contained in 10 or more rows within the table. The combination of defining the logical views and then joining the views together allows you to piece together all the data for a query or set of queries. Column QQJFLD uniquely identifies all queries within a job; column QQUCNT is unique at the query level. The combination of the two, when referenced in the context of the logical views, connects the query implementation to the query statement information.

Table 1. Output for SQL Queries that Performed Table Scans

Lib Name	Table Name	Total Rows	Index Advised	Rows Returned	TOT_ TIME	Statement Text
LIB1	TBL1	20000	Y	10	6.2	SELECT * FROM LIB1/TBL1 WHERE FLD1 = 'A'
LIB1	TBL2	100	N	100	0.9	SELECT * FROM LIB1/TBL2
LIB1	TBL1	20000	Y	32	7.1	SELECT * FROM LIB1/TBL1 WHERE FLD1 = 'B' AND FLD2 > 9000

If the query does not use SQL, the SQL information row (QQQ1000) is not created. This makes it more difficult to determine which rows in LIB/PERFDATA pertain to which query. When using SQL, row QQQ1000 contains the actual SQL statement text that matches the monitor rows to the corresponding query. Only through SQL is the statement text captured. For queries executed using the OPNQRYF command, the OPNID parameter is captured and can be used to tie the rows to the query. The OPNID is contained in column Open_Id of row QQQ3014.

 

Parent topic:

Database monitor examples