New Ranks & Badges For The Community!
Notice something different? The ranks and associated badges have gone "Star Fleet". See what they all mean HERE
Highlighted
Ensign Ensign
Ensign
487 views

(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,

       qr.user_name,

       qr.session_id,

       qr.transaction_id,

       qr.statement_id,

       qr.request_type,

       qr.success,

       qr.error_count,

       qr.start_timestamp,

       qr.request_duration_ms,

       qr.request

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

 

SELECT

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

    session_id,

    transaction_id,

    statement_id,

    node_name,

    LEFT(query,100)

FROM

    query_profiles

WHERE

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

ORDER BY

    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 Likes
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.