Big news! The community will be moving to a new platform April 21. Read more.
Big news! The community will be moving to a new platform April 21. Read more.
Ensign Ensign

(OBR) Support Tip: slow queries or reports not running on Vertica

1) Identify the slow query with two methods:


Identify the slowest queries with specific long time duration


SELECT qr.node_name,











FROM   v_monitor.query_requests qr

WHERE  qr.request_duration_ms >= 13000

ORDER  BY qr.start_timestamp;



Identify the slowest queries during a specified period of time



    (query_duration_us/1000000)::NUMERIC(10,3) duration_sec,









    query_start BETWEEN '2018-07-19 00:00:0' AND '2018-07-19 23:00:00'


    duration_sec DESC;


2) Once you have identified which query you want to analyze, use that query's transaction_id and statement_id to extract the full query statement so that you can profile the query

SELECT query FROM query_profiles

   WHERE transaction_id = :t_id and statement_id = :s_id;


3) Put the query or queries in file and each query separeted by semicolon.


4) Save the file onto one of vertica node (in /home/vertdba, for example – any directory dbadmin has full access to)

5) On the node, Launch admintools > Configuration Menu > Run Database Designer(DBD).

6) Follow the screen to provide path to the text file containing the query (in step 4).

7) Choose “Incremental” for the Design type.

😎 Select all schema

9) Select “Update Statistics” and “Deploy Design”

10) DBD will create new projections and will populate the projections with data.  This will take time.  Let it run to completion.

11) Once DBD is done, please test the slow reports again

0 Replies
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.