Am I right ? on Query run time
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 msQuery_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
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.