Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

How to check slow queries in vertica

for few read operations are taking morethan time, how to analys and command to fetch slow queries?

Answers

  • Top 50 queries in execution for > 30 minutes.
    A query running for an abnormally longer duration, can consume and hold resources which can impact other queries.
    Can you try the below query?
    select /+label(LongRunningQueries)/ s.node_name,s.user_name,transaction_id,statement_id,DATEDIFF('MINUTE',s.statement_start,ss.max_timestamp) query_runtime_minutes,s.current_statement||'...'::varchar(150) from sessions s , (select max(login_timestamp) max_timestamp from sessions ) ss where statement_id is not null and DATEDIFF('MINUTE',s.statement_start,ss.max_timestamp) > 30 order by 6 desc limit 50;

  • Try this query without the label.
    select s.node_name,s.user_name,transaction_id,statement_id,DATEDIFF('MINUTE',s.statement_start,ss.max_timestamp) query_runtime_minutes,s.current_statement||'...'::varchar(150) from sessions s , (select max(login_timestamp) max_timestamp from sessions ) ss where statement_id is not null and DATEDIFF('MINUTE',s.statement_start,ss.max_timestamp) > 30 order by 6 desc limit 50;

  • Can you show how you are reading a file?

  • From our application, we are triggering select queries with dynamic where conditions.

    Above query not get any record and I have changed the value from 30 to 1,

  • moshegmosheg Employee
    edited January 12

    In addition, see this: https://www.vertica.com/blog/vsql-visual-storytelling-through-query-language/
    Or try something like the following query to find Top10 long running queries in the last 6 hours:

    SELECT QC.TRANSACTION_ID,QC.STATEMENT_ID, QC.CPU_CYCLES_US, QC.DURATION_MS, LEFT(QR.REQUEST,30) QUERY
    FROM QUERY_CONSUMPTION QC, QUERY_REQUESTS QR
    WHERE QC.request_type='QUERY' AND
          QC.success AND
          ( NOW() - QC.END_TIME) < '6 hour' AND
          QC.TRANSACTION_ID = QR.TRANSACTION_ID AND
          QC.STATEMENT_ID = QR.STATEMENT_ID
    ORDER BY 3 DESC,4
    LIMIT 10;
    
      TRANSACTION_ID   | STATEMENT_ID | CPU_CYCLES_US | DURATION_MS |             QUERY
    -------------------+--------------+---------------+-------------+--------------------------------
     45035996274039564 |           40 |         58233 |          64 | SELECT 'tests.mogo_campaigns.N
     45035996274039646 |           38 |         36983 |          43 | SELECT 'tests.mogo_campaigns.N
     45035996274039792 |            4 |         31320 |          72 | SELECT QC.TRANSACTION_ID,QC.ST
     45035996274039792 |            5 |         28960 |          69 | SELECT QC.TRANSACTION_ID,QC.ST
     45035996274039731 |           40 |         27826 |          33 | SELECT 'tests.mogo_campaigns.N
     45035996274039792 |            3 |         18350 |          69 | SELECT transaction_id,statemen
     45035996274039792 |            2 |          8766 |          23 | Select * from dc_optimizer_sta
     45035996274039564 |           47 |          8711 |          13 | SELECT 'tests.mogo_campaigns.i
     45035996274039731 |           47 |          8651 |          13 | SELECT 'tests.mogo_campaigns.i
     45035996274039646 |           45 |          8554 |          13 | SELECT 'tests.mogo_campaigns.i
    (10 rows)
    
    
    
  • @PavanVejju
    You can also try something like a below query from query_profiles to find what time query started, how long is running and is_executing.

    The below example showing, delete query took 28 hours to execute.

    select query_start::date, substr(query,0,30) ,floor(query_duration_us/1000000/60/60) as query_duration_hour, is_executing from query_profiles order by 3 desc limit 10;

    query_start | substr | query_duration_hour | is_executing
    -------------+-------------------------------+---------------------+--------------
    2021-01-08 | DELETE /+ DIRECT */ FROM EPP | 28 | f
    2021-01-10 | INSERT /+DIRECT/ INTO v_tem | 17 | f
    2021-01-10 | CREATE LOCAL TEMP TABLE TAAA 17 | f
    2021-01-10 | update TEMP_ABB | 14 | f
    2021-01-10 | INSERT /
    +DIRECT*/ INTO PTT | 13 | f

    (10 rows)

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.