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

Am I right ? on Query run time

Am i right?

I am looking for run time of the query, and there multiple ways to get this, but here is the conflicting information

I enabled \timing in vsql to capture the run time of query , and the tried to fetch the same information from system tables query_request and query_profile,  the time show by query profile or request is same and matches , but does not match to vsql timing ? 

can i say that additional time show by vsql is the time consumed is due to network latency...( the time result is out and shown on my client tool )

here is what i did

-----------------------------

odm_poc_owner=> select /*+label(cputest)*/ * from dual;

 dummy 

-------

 X

(1 row)


Time: First fetch (1 row): 12.522 ms. All rows formatted: 12.561 ms

odm_poc_owner=> SELECT 

odm_poc_owner-> transaction_id

odm_poc_owner-> ,statement_id

odm_poc_owner-> ,user_name

odm_poc_owner-> ,MEMORY_ACQUIRED_MB

odm_poc_owner-> ,START_TIMESTAMP

odm_poc_owner-> ,END_TIMESTAMP

odm_poc_owner-> ,REQUEST_DURATION_MS

odm_poc_owner-> ,substr(request,1,40)

odm_poc_owner-> FROM v_monitor.query_requests 

odm_poc_owner-> where IS_EXECUTING = 'f' 

odm_poc_owner-> and request like '%cputest%'

odm_poc_owner-> Order by START_TIMESTAMP desc limit 3;

  transaction_id   | statement_id |   user_name   | MEMORY_ACQUIRED_MB |        START_TIMESTAMP        |         END_TIMESTAMP         | REQUEST_DURATION_MS |                 substr                  

-------------------+--------------+---------------+--------------------+-------------------------------+-------------------------------+---------------------+-----------------------------------------

 45035996303895510 |           46 | odm_poc_owner |                100 | 2014-12-19 12:46:49.560056+00 | 2014-12-19 12:46:49.568195+00 |                   8 | select /*+label(cputest)*/ * from dual;

(1 row)


Time: First fetch (1 row): 7186.702 ms. All rows formatted: 7186.772 ms

odm_poc_owner=> 

odm_poc_owner=> select

odm_poc_owner-> transaction_id

odm_poc_owner-> ,statement_id 

odm_poc_owner-> ,schema_name  

odm_poc_owner-> ,projections_used 

odm_poc_owner-> ,query_duration_us

odm_poc_owner-> ,query_start

odm_poc_owner-> ,query_type

odm_poc_owner-> ,user_name

odm_poc_owner-> ,processed_row_count

odm_poc_owner-> ,reserved_extra_memory

odm_poc_owner-> ,substr(query,1,40)

odm_poc_owner-> FROM v_monitor.query_profiles

odm_poc_owner-> where  query like '%cputest%' 

odm_poc_owner-> order by query_start desc limit 3;

  transaction_id   | statement_id | schema_name |    projections_used    | query_duration_us |          query_start          | query_type |   user_name   | processed_row_count | reserved_extra_memory |                  substr                  

-------------------+--------------+-------------+------------------------+-------------------+-------------------------------+------------+---------------+---------------------+-----------------------+------------------------------------------

 45035996303895510 |           46 |             | query projection_usage |       8139.000000 | 2014-12-19 12:46:49.560056+00 | QUERY      | odm_poc_owner |                   1 |                     0 | select /*+label(cputest)*/ * from dual;

(3 rows)

In vsql it is = Time: First fetch (1 row): 12.522 ms. All rows formatted: 12.561 ms

Query_profile =  query_duration_us  ( 8139  Micro seconds) or 8.139 Milli sec

Query Request =  REQUEST_DURATION_MS  ( 8 MS)

So is my inference correct ?

Is there any way to know the time taken due to  network latency  ?

Comments

  • Hi!

    Is there any way to know the time taken due to  network latency  ?
    Can be, but not in this case. Its not a network - its values formatting.
    vsql time always will be bigger than or equals to time in PROFILE. Why?
    Because PROFILE do not measure time for formatting values(dates, interval units, etc) - measure execution time for data processing only. Vertica stores it data in internal format and you must convert it to ascii to read it (you can not read binary data, ascii only).

    Do you did profile to CPU? Vertica provides a tool - vcpuperf. Check that your CPU is good enough.
  • Thanks.. Let me do some search on vcpupref.

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.