I've created a SQL Script to capture what is in the v_monitor.query_requests table, which keeps a rolling history of queries and their run times. Since this table keeps a rolling history, it needs to be backed up to another history table for long term storage on a regular basis. This allows us to identify long running queries and provide a baseline for measuring performance improvements. This could be expanded to other v_monitor tables as desired.
First we create the table.
Code: Select all
----------------------------------------------------------------------------------
-- Purpose: Creates a table history.query_requests
-- that will be used to store a history of queries in
-- v_monitor.query_requests.
-- Assumptions: This runs under the dbadmin account.
-- A schema called history already exists, or is created using:
-- CREATE SCHEMA history;
----------------------------------------------------------------------------------
CREATE TABLE history.query_requests
(
node_name varchar(128) ENCODING RLE,
user_name varchar(128) ENCODING RLE,
session_id varchar(128),
request_id int ENCODING COMMONDELTA_COMP,
transaction_id int ENCODING COMMONDELTA_COMP,
statement_id int ENCODING COMMONDELTA_COMP,
request_type varchar(128) ENCODING RLE,
request varchar(64000),
request_label varchar(128) ENCODING RLE,
search_path varchar(64000) ENCODING RLE,
memory_acquired_mb float,
success boolean ENCODING RLE,
error_count int ENCODING RLE,
start_timestamp timestamptz ENCODING DELTARANGE_COMP,
end_timestamp timestamptz ENCODING DELTARANGE_COMP,
request_duration_ms int ENCODING DELTARANGE_COMP
)
ORDER BY
user_name,
node_name,
request,
start_timestamp,
request_duration_ms,
end_timestamp,
transaction_id
SEGMENTED BY hash(transaction_id) ALL NODES KSAFE 1;
Then we can schedule this INSERT statement to run via crontab or similar to start building a long term history.
Code: Select all
----------------------------------------------------------------------------------
-- Purpose: Creates a history in the table history.query_requests
-- from the rolling history in v_monitor.query_requests.
-- Assumptions: This runs under the dbadmin account.
-- Schedule this to run every 30 minutes via crontab or similar.
----------------------------------------------------------------------------------
INSERT /*+ direct */
INTO history.query_requests
(
node_name
,user_name
,session_id
,request_id
,transaction_id
,statement_id
,request_type
,request
,request_label
,search_path
,memory_acquired_mb
,success
,error_count
,start_timestamp
,end_timestamp
,request_duration_ms
)
SELECT
node_name
,user_name
,session_id
,request_id
,transaction_id
,statement_id
,request_type
,request
,request_label
,search_path
,memory_acquired_mb
,success
,error_count
,start_timestamp
,end_timestamp
,request_duration_ms
FROM v_monitor.query_requests
WHERE is_executing=false
AND transaction_id <> 0
AND request_type IN ('LOAD','QUERY','TRUNCATE','UTILITY','DDL')
AND HASH
(
node_name
,user_name
,session_id
,request_id
,transaction_id
,statement_id
,request_type
,request
,request_label
,search_path
,memory_acquired_mb
,success
,error_count
,start_timestamp
,end_timestamp
,request_duration_ms
)
NOT IN (
SELECT HASH(
node_name
,user_name
,session_id
,request_id
,transaction_id
,statement_id
,request_type
,request
,request_label
,search_path
,memory_acquired_mb
,success
,error_count
,start_timestamp
,end_timestamp
,request_duration_ms
) AS hash_value
FROM history.query_requests
WHERE start_timestamp >= (SELECT MIN(start_timestamp)
FROM v_monitor.query_requests)
);
SELECT ANALYZE_STATISTICS('history.query_requests');