Was this helpful?
System Catalogs for X100 Query Profiling Views
The views that provide profiling information on X100 queries are based on the following Ingres system catalogs:
iivwprof_query
iivwprof_op
iivwprof_io
iivwprof_stage
iivwprof_expr
iivwprof_parse_tree
Note:  System catalogs with the name iivwprof_*_global (for example iivwprof_query_global) contain information on all sessions (not only the current session). These tables have an additional attribute "session_id", which is an 8-byte integer.
iivwprof_query Catalog
The iivwprof_query table contains the following columns. Each entry represents one query executed in the current session.
Column Name
Type
Description
query_id
bigint
Unique identifier of a query within a session, ascending
start_time
timestamp(6) with local time zone
Time stamp of the start of VW query execution (millisecond precision)
execution_time
interval day to second(6)
Total real time taken by the VW server expressed as an interval (millisecond precision)
query_text
varchar(4000)
Text of the query as generated by the optimizer
mem
bigint
Memory (in bytes) allocated by the query at end of execution
mem_tot
bigint
Maximum amount of memory allocated by the query at any point of the execution
mem_vm
bigint
Virtual memory address space reserved by the query at end of execution
mem_tot_vm
bigint
Maximum amount of virtual memory address space reserved by the query at any point of execution
iivwprof_op Catalog
The iivwprof_op table contains the following columns. Each entry represents a relational operator executed by the queries in the current session.
Column Name
Type
Description
query_id
bigint
Unique identifier of a query within a session, ascending
op_id
integer
Identifier of a relational operator within a query tree, post-order starting with 1
name
varchar(20)
Name (type) of the operator (for example Select/Project/HashJoinN/etc)
ticks
bigint
Execution time spent in this operator in CPU clock cycles
cum_ticks
bigint
Execution time sped in this operator and all operators in its sub-tree
in_tuples
bigint
Number of tuples on the input to this operator
tuples
bigint
Number of tuples generated by the oprator
himem
bigint
Maximum amount of memory allocated by this operator at any point in the execution (in bytes)
qhimem
bigint
Maximum amount of memory allocated by the query
level
integer
Level of the operator in the execution tree
alloc_ticks
bigint
Time spent (in CPU clock cycles) to initialise the operator
build_ticks
bigint
For HashTable based operators - time spent in Hash Table build phase
probe_ticks
bigint
For HashTable based operators - time spent in Hash Table probe phase
post_ticks
bigint
For HashTable based operators - time spent in Hash Table post phase
est_cost
bigint
Execution cost (in arbitrary units) as estimated by the parallelization algorithm
est_tuples
bigint
Generated number of tuples as estimated by the Ingres optimizer
iivwprof_io Catalog
The iivwprof_io table contains the following columns. Each entry contains I/O statistics for one PAX group read by a scan operator executed by queries in the current session.
Column Name
Type
Description
query_id
bigint
Unique identifier of a query within a session, ascending
op_id
integer
Identifier of a relational operator within a query tree, pre-order, starting with 0
table
varchar(32)
Scanned table name
columns
varchar(64)
Scanned column name
async_fetch
bigint
Number asynchronous block requests generated by the scan
async_miss
bigint
Number of asynchronous requests for blocks not found in the cache
sync_fetch
bigint
Number of synchronous block requests generated by the scan
sync_miss
bigint
Number of synchronous requests for blocks not found in the cache
iivwprof_stage Catalog
The iivwprof_stage table contains the following columns. Each entry contains information about time spent by the queries in different parts of the query life cycle.
Column Name
Type
Description
query_id
bigint
Unique identifier of a query within a session, ascending
name
varchar(20)
Name of a stage. One of:
scanstate - query text tokenizer
parse - query text parser
rewrite - query execution tree rewriter
build - initializing execution operators
execute - actual query execution
profile - generating profile information
free - freeing the resources
total - total time of the above stages
substage
varchar(20)
Name of a substage for rewrite stage
ticks
bigint
Time spent in this stage in CPU clock cycles
iivwprof_expr Catalog
The iivwprof_expr table contains the following columns. Each entry represents one vector function used during execution of a query.
Column Name
Type
Description
query_id
bigint
Unique identifier of a query within a session, ascending
op_id
bigint
Identifier of a relational operator within a query tree, pre-order, starting with 0
ticks
bigint
Total time spent (in CPU clock cycles) in this function
processed_cnt
bigint
Number of tuples processed
call_cnt
bigint
Number of vectors processed
function_name
varchar(60)
Function name
name
varchar(30)
Name of the expression executing the function
context
varchar(100)
Encoded names of functions in the expression tree and the operator executing this function
iivwprof_parse_tree Catalog
The iivwprof_parse_tree table contains the following columns. Each entry represents a node in the tree representation of the internally constructed query (query_text) of the current session queries.
Column Name
Type
Description
query_id
bigint
Unique identifier of a query within a session, ascending
is_operator
boolean
The node representing a relational operator
name
varchar(24)
Name of the node type
value
varchar(40)
Value stored in the node (depends on the type, for example function name or literal value)
est_card
bigint
For operator nodes - output number of tuples estimated by the optimizer
level
bigint
Level of the node in the tree
node_num
bigint
Node index in the pre-order sequence
 
Last modified date: 01/30/2023