Getting query plan status for large queries

Real-time profiling is designed to monitor large (long-running) queries.

Real-time profiling is designed to monitor large (long-running) queries. Take the following steps to monitor plans for large queries:

Get the statement and transaction IDs for the query plan you want to profile by querying system table CURRENT_SESSION:

=> SELECT transaction_id, statement_id from current_session;
  transaction_id   | statement_id
-------------------+--------------
 45035996273955001 |            4
(1 row)

Run the query:

=> SELECT * FROM t1 JOIN t2 ON x=y JOIN ext on y=z;

Query system table QUERY_PLAN_PROFILES, and sort on the transaction_id, statement_id, path_id, and path_line_index columns.

=> SELECT ... FROM query_plan_profiles WHERE transaction_id=45035996273955001 and statement_id=4
   ORDER BY transaction_id, statement_id, path_id, path_line_index;

You can also use the Linux watch command to monitor long-running queries (see Real-time profiling).

Example

The following series of commands creates a table for a long-running query and then queries system table QUERY_PLAN_PROFILES:

Create table longq:

=> CREATE TABLE longq(x int);
CREATE TABLE
=> COPY longq FROM STDIN;
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> 1
>> 2
>> 3
>> 4
>> 5
>> 6
>> 7
>> 8
>> 9
>> 10
>> \.
=> INSERT INTO longq SELECT f1.x+f2.x+f3.x+f4.x+f5.x+f6.x+f7.x
      FROM longq f1
      CROSS JOIN longq f2
      CROSS JOIN longq f3
      CROSS JOIN longq f4
      CROSS JOIN longq f5
      CROSS JOIN longq f6
      CROSS JOIN longq f7;
  OUTPUT
----------
 10000000
(1 row)
=> COMMIT;
COMMIT

Suppress query output on the terminal window by using the vsql \o command:
```
=> \o /home/dbadmin/longQprof
```
Query the new table:
```
=> SELECT * FROM longq;
```

Get the transaction and statement IDs:

=> SELECT transaction_id, statement_id from current_session;
  transaction_id   | statement_id
-------------------+--------------
 45035996273955021 |            4
(1 row)

Turn off the \o command so OpenText™ Analytics Database continues to save query plan information to the file you specified. Alternatively, leave it on and examine the file after you query system table QUERY_PLAN_PROFILES.
```
=> \o
```

Query system table QUERY_PLAN_PROFILES:

=> SELECT
     transaction_id,
     statement_id,
     path_id,
     path_line_index,
     is_executing,
     running_time,
     path_line
   FROM query_plan_profiles
   WHERE transaction_id=45035996273955021 AND statement_id=4
   ORDER BY transaction_id, statement_id, path_id, path_line_index;