Chapter 3 – Systems Tables
“He who asks a question may be a fool for five minutes, but he who never asks a question remains a fool forever.”
- Unknown
Actian Matrix System Tables
Actian Matrix provides access to the following types of system tables:
• STL tables for logging - These system tables are generated from Actian Matrix log files to provide a history of the system. Logging tables have an STL prefix.
• STV tables for snapshot data - These tables are virtual system tables that contain snapshots of the current system data. Snapshot tables have an STV prefix.
• System views - System views contain a subset of data found in several of the STL and STV system tables. Systems views have an SVV or SVL prefix.
• System catalog tables - The system catalog tables store schema metadata, such as information about tables and columns. System catalog tables have a PG prefix.
Every Matrix system automatically contains a number of system tables. These system tables contain information about the installation and about the various queries and processes that are running on the system. You can query these system tables to collect information about the redshift database that is installed.
Trouble Shooting Catalog Table pg_table_def
set search_path to '$user', 'public', 'sql_class';
The above query references the system catalog table named pg_table_def, and it only runs exclusively on the leader node. PG_TABLE_DEF will only return information for tables in schemas that are included in the search path. The first query failed because the 'employee_table' was not in the search_path. Above, we added sql_class to our path. The first query will work now because the database sql_class has been placed in our search path, and that is where the employee_table resides.
Seeing the System Tables in your Nexus Tree
The Matrix catalog is in the pg_catalog database. You can query these tables with SQL or merely do a "Quick Select" by right clicking on any table in the tree. We just did a "Quick Select" on the pg_aggregate table.
Catalog Table pg_table_def
The above query references the system catalog table named pg_table_def, and it only runs exclusively on the leader node. PG_TABLE_DEF will only return information for tables in schemas that are included in the search path. The query we ran on the previous page failed because the 'employee_table' was not in the search_path. The database that contains the employee_table is the sql_class database. Once we added the database sql_class to our search path, the query ran perfectly!
Checking Tables for Skew (Poor Distribution)
SELECT TRIM(name) as Table_Name
,slice
,sum(num_values) as rows
from svv_diskusage
where name in ('Order_Table', 'Customer_Table')
and col =0
group by name, slice
order by name, slice;
Uneven distribution, or data distribution skew, forces some nodes or slices to do more work than others which inhibits query performance. To check for distribution skew, you can query the SVV_DISKUSAGE system view. Each row in the system table SVV_DISKUSAGE records the statistics for one disk block. The num_values column gives the number of rows in that disk block, so when you sum(num_values), it returns the number of rows on each slice.
Checking All Statements That Used the Analyze Command
SELECT xid
,to_char(starttime, 'HH24:MM:SS.MS') as starttime
,date_diff('sec',starttime,endtime ) as secs
,substring(text, 1, 40) as ActualText
FROM svl_statementtext
WHEREsequence = 0
AND xid in (select xid from svl_statementtext s
where s.text like ’matrix_fetch_sample%' )
order by xid desc, starttime;
The query above returns all the statements that ran in every completed transaction that included an ANALYZE command.
Checking Tables for Skew (Poor Distribution)
select P.name as "Table"
,count(*) as "1 MB blocks"
from stv_blocklist as B
INNER JOIN
stv_tbl_perm as P
ON B.tbl = P.id
AND B.slice = P.slice
WHERE P.name in ('Customer_Table', 'Order_Table')
GROUP BY P.name
ORDER BY 1 asc;
You can easily check on how many 1 MB blocks of disk space are used for each table by querying the STV_BLOCKLIST table. This will give you measurements on table sizes.
Checking for Details about the Last Copy Operation
SELECT query as Query
,TRIM(filename) as File
,curtime as Updated
from stl_load_commits
where query = pg_last_copy_id() ;
STLtables for logging - These
system tables are generated from
Actian Matrix log files to provide a
history of the system. Logging
tables have an STLprefix.
The above example returns details for the last COPY operation.
Checking When a Table Has Last Been Analyzed
SELECT query
,rtrim(querytxt)
,starttime
FROM stl_query
WHEREquerytxt like 'matrix_fetch_sample%'
AND querytxt like '%Sales_Table%'
ORDER BY 1 desc;
To find out when ANALYZE commands were run, you can query STL_QUERY. For example, to find out when the Sales_Table was last analyzed, run the query above.
Checking For Column Information on a Table
SELECT Schemaname as "Schema"
,Tablename
,Column
,Type
,Distkey
FROM pg_table_def
WHERE tablename = 'Department_Table';
System catalog tables - The system
catalog tables store schema
metadata, such as information
about tables and columns. System
catalog tables have a PG prefix.
The above example returns information for the Department_Table.
System tables for troubleshooting data loads
SELECT *
FROM ch_loadview
WHERE table_name='Employee_Table';
The example above is helpful in troubleshooting data load issues.
Determining Whether a Query is Writing to Disk
If IS_DISKBASED is true ("t") for any step, then that step wrote data to disk.