Database monitor performance analysis example 2

 

Similar to the preceding example that showed which SQL applications were implemented with table scans, the following example shows all queries that are implemented with table scans.

SELECT A.System_Table_Schema, A.System_Table_Name, 
     A.Table_Total_Rows, A.Index_Advised, 
     B.Open_Id, B.Open_Time, 
     C.Clock_Time_to_Return_All_Rows, C.Number_Rows_Returned, D.Result_Rows, 
     (D.End_Timestamp - D.Start_Timestamp) AS TOT_TIME, 
     D.Statement_Text_Long 
   FROM LIB/QQQ3000 A INNER JOIN LIB/QQQ3014 B 
     ON (A.Join_Column = B.Join_Column AND 
     A.Unique_Count = B.Unique_Count) 
     LEFT OUTER JOIN LIB/QQQ3019 C 
     ON (A.Join_Column = C.Join_Column AND A.Unique_Count = C.Unique_Count) 
     LEFT OUTER JOIN LIB/QQQ1000 D 
     ON (A.Join_Column = D.Join_Column AND A.Unique_Count = D.Unique_Count)

In this example, the output for all queries that performed table scans are shown in the table below.

The columns selected from table QQQ1000 do return NULL default values if the query was not executed using SQL. For this example assume the default value for character data is blanks and the default value for numeric data is an asterisk (*).

Table 1. Output for All Queries that Performed Table Scans

Lib Name	Table Name	Total Rows	Index Advised	Query OPNID	ODP Open Time	Clock Time	Recs Rtned	Rows Rtned	TOT_ TIME	Statement Text
LIB1	TBL1	20000	Y		1.1	4.7	10	10	6.2	SELECT * FROM LIB1/TBL1 WHERE FLD1 = 'A'
LIB1	TBL2	100	N		0.1	0.7	100	100	0.9	SELECT * FROM LIB1/TBL2
LIB1	TBL1	20000	Y		2.6	4.4	32	32	7.1	SELECT * FROM LIB1/TBL1 WHERE FLD1 = 'A' AND FLD2 > 9000
LIB1	TBL4	4000	N	QRY04	1.2	4.2	724	*	*	*

If the SQL statement text is not needed, joining to table QQQ1000 is not necessary. You can determine the total time and rows selected from data in the QQQ3014 and QQQ3019 rows.

 

Parent topic:

Database monitor examples