Use the vwinfo command to display information about a database that uses Vector tables.

By default, vwinfo displays various statistics about the database. You can supply options on the command to display other information. Issue vwinfo ‑h to see the list of available options.

Issue any one of the following commands at the operating system prompt. All these commands produce the same output:

Example statistics output is shown under vwinfo Statistics Display.

Issue either of these commands, which produce the same output:

The current configuration settings for the specified database are displayed. Here is an excerpt from a typical output:

Issue either of these commands, which produce the same output:

To limit the output to a specified table, use the ‑t (or ‑‑table) option, as follows:

Here is an excerpt from a typical output:

Issue either of these commands, which produce the same output:

To limit the output to a specified table, use the -t (or --table) option, as follows:

Issue either of these commands, which produce the same output:

Here is an example of the statistics output from the vwinfo command:

The information provided by vwinfo -o can answer question such as:

Here is an example of the Transactions output from the vwinfo command:

The information provided by vwinfo -M can be used to identify tables that are good candidates for MODIFY...TO COMBINE operations to free main memory used by the PDTs (Positional Delta Trees).

Here is an excerpt from a typical output:

Information on the execution plan of recent queries can be accessed through the following predefined views:

The iivwquery view provides information about the recently executed queries. It provides the following attributes:

The iivwprof view contains the main information about the execution stages of recently executed queries. It contains the following attributes:

The data_blocks, async_io, and sync_io attributes in the iivwprof view are related and correspond to how I/O is performed in Vector.

Data is divided into I/O blocks (as configured on the [cbm] block_size parameter). Whenever the query processing layer needs to access a given data block, a data_blocks counter is increased. If this block is not in memory, this causes a synchronous (blocking) load request, increasing the sync_io counter. At the same time, if the query knows it will require additional blocks in the future, an asynchronous (non-blocking) load (sometimes called look-ahead load or prefetching) for some future blocks is issued (this allows overlapping processing and I/O). If these blocks are not in memory, the async_io parameter is increased. Note that if the “synchronous” request for the same block is issued before the “asynchronous” look-ahead request is finalized, the sync_io counter for that block will also be increased.

This logic translates to the following “rules of thumb:”

The iivwprof_last view contains the same information as the iivwprof view, but for the last query only.

These examples of using query profiling views use the DBT-3 schema and dataset of size 1 GB. The section How to Run the Benchmark on page 28 describes how to create and load this dataset.

Let us run the following simple query:

We can now see that this query appeared in the “iivwquery” view:

In this view, we see queries from the current session only. Queries can be identified, for example, by the “start_time” attribute. We see that this query has an ID of “29”.

We can now select the profiling information for this query:

We see that three Operators are used by this query. The “MScan(region)” operator (with op_id 1) scanned the “region” table and produced 5 “tuples”, as this table contains 5 records. The Select operator selected records matching our selection filter, producing 3 tuples. Finally, the Project operator was used to format the result.

Looking at the “ticks,” we see that Project and Select consume extremely little time (keep in mind that 1 tick on a 2 GHz machine is only 0.5 nanosecond). The MScan operator uses slightly more, as it needs to perform more complex actions, such as checking if the data is in memory and decompressing it. Similarly, the memory usage is trivial for Project and Select, and slightly higher for MScan.

For a more complex example, let’s run this query:

After looking up the query_id for this query (as shown in the previous example), we can see the profiling information for this query by running:

We focus on operators 1 through 4. (Operators 5 through 8 are “management” operations, which we will ignore here.) There are two MScan operators scanning the “lineitem” and “order” tables and producing respectively 6 million and 1.5 million tuples. These tables are combined in the HashJoinN operator, responsible for performing the JOIN construct in SQL. To perform that operation, a significant amount of memory is used—about 60 MB. This operator also consumes most of the query time. See that the output count of the HashJoinN operator matches the count of the “lineitem” table, because every tuple matches something in the “order” table. Finally, the count of the join result is counted in the Aggr operator.

By default, query profiling is turned on. The profiling feature can be turned off by setting the [server] profiling parameter to false.

The maximum number of profiles to be kept in memory can be configured using the [server] max_profiles_per_database and max_profiles_per_session configuration parameters.

The views that provide profiling information on Vector queries are based on the system catalogs iivwprof_query, iivwprof_op, iivwprof_io, iivwprof_stage, iivwprof_expr, and iivwprof_parse_tree. For more information, see “System Catalogs” in the SQL Language Guide.