TPDC Manual (G06.24+)

Using the SPAM Performance Data Charts and
Reports
Tandem Performance Data Collector (TPDC) Manual—526357-001
5-67
Low Selectivity (Needle in a Haystack) SQL Table
Access Report (HAYSTCK)
Low Selectivity (Needle in a Haystack) SQL Table Access Report for System \P20ET (C30)
--------------------------------------------------------------
<AVERAGE PER SECOND OVER> Sec
<--WINDOW--> <---OPEN---> per
<----TABLE NAME-------> [HH:MM]Select- Records Disc Records Disc <-------OPENING PROCESS-------> Log
Filename $Volume.Subvol From To ivity% Accessd Reads Accsd Reads Procss PID Pri Program File Name IO
======== -------------- ==== -- ------ ------- ----- ----- ----- ------ --- --- --------------------
(1) (2) (3) (4) (5) (6) (7) (8) (9) (10) (11)(12) (13) (14)
THIS REPORT SHOWS SPECIFIC OPENS (OF (6) The number of records ACCESSED per second, averaged
NonStop SQL TABLES) THAT EXHIBIT LOW over the measurement window; this report EXCLUDEs
'SELECTIVITY'; THAT IS, WHERE THE RATIO opens that accessed less than 0.10 records per
OF RECORDS USED TO RECORDS ACCESSED IS second, when averaged over the measurement window.
RELATIVELY SMALL.
(7) The number of physical disc READS per second,
In some instances, re-wording an SQL averaged over the measurement window.
statement or adding an alternate index
may result in many fewer records (rows) (8) The number of records ACCESSED per second, averaged
being accessed in order to satisfy the over the life of the particular OPEN.
statement; in other instances, low
selectivity scans are unavoidable. (9) The number of physical disc READS per second,
averaged over the life of the particular OPEN.
For the unavoidable cases, partitioning
the table across many disc volumes (10) The name of the process that opened the table;
connected to many CPUs can result in a since MEASURE may record a File open slightly before
dramatic improvement in the time required it records the Process itself, this report ALLOWs
to return query results, since NonStop the starting timestamp of a File record to be as
SQL Release 2's Parallel Query Execution much as 5.00 seconds earlier than that of the
feature can utilize all available Process.
resources in parallel (look for
'$SYSTEM.SYSTEM.SQLESP’ in the OPENING (11) The PID [<cpu>,<pin>] of the opening process.
PROCESS Program File Name column).
(12) The execution priority of the opening process; Disc
(1) The physical file name of the table; Processes service requests according to the prior-
the report is sorted by this name and, ities of the requesters. In order not to degrade
within this name, by time of opening. the performance of more critical transactions (if
any) that execute concurrently with processes
(2) The volume and subvolume name of the reported on here, these low-selectivity processes
table; this will often be the name of should usually have execution priorities below
the 'root' partition, but it may also those of processes that service the more critical
be the name of an 'extra' partition. transactions.
(3) The time [hh:mm] of this particular (13) The program file name of the opening process. Any
table OPEN (or of the start of the process running from the $SYSTEM.SYSTEM.SQLESP file
measurement window, whichever comes is a copy of the NonStop SQL Executor Server
later). Process that accomplishes automatic parallel query
execution on behalf of application SQL statements.
(4) The time [hh:mm] of this particular
table CLOSE (or of the end of the
measurement window, whichever comes (14) The average elapsed SECONDS per LOGICAL table I/O;
earlier). this includes time spent accessing unused records.
(5) The selectivity of table accesses made
via this open; that is, the number of
records USED divided by the number of
records ACCESSED, expressed as a
percentage. Note that a selectivity of
.0001% implies that approximately ONE
IN A MILLION of the records accessed
was actually used; blanks in this
column indicate a selectivity less than
.00005%. This report shows only opens
with a selectivity less than or equal
to 5.0000%.