This documentation supports the 22.1 version of Action Request System.
To view an earlier version, select the version from the Product version menu.

Example of using the explain query REST API to get the ARJDBC query execution plan

The explain query REST API helps you obtain the query execution plan. The database administrator can analyze the plan and improve system performance for MS SQL, Oracle, and PostgreSQL databases.

Analyzing query execution plan scenario

While generating the quarterly sales report, Seth experiences a delay. He escalated the issue to the IT team.

To understand the cause of the delay, the IT team decided to analyze the ARJDBC query execution plan. The IT team uses the explain query REST API to fetch the ARJDBC query execution plan from the AR System server .

The following infographic shows fetching query execution plan by using the explain query REST API:




Retrieving query execution plan workflow

  • The database administrator, retrieves the query execution plan by using the Postman client and the explain query REST API call.
    The example of the explain query REST API call is as follows:

    POST /api/arsys/v1.0/sql/explain
    Request: RestSqlQuery object
    
    {
        "sql": "select ...",
        "bind_variables": {
            "1": {
                "value": "Value",
                "type": "Type"
            },
            "2": {
                "value": "Value",
                "type": "Type"
            },
            ...
        },
        "properties": {
            "name", "Value",
            "name2", "Value2"
        }
    }
  • The request is processed and a response that contains the query execution plan is sent. 
    See the example below: 

    {
        "execution_plan": "Execution plan as returned by database",
        "table_map" {
            "form/record name": [
                "table_name alias",
                "table_name2 alias2"
            ],
            "form/record name2": [
                "table_name alias",
                "table_name2 alias2"
            ],
            ...
        }
    }
    table_map": {
            "User": {
                "table_info": [
                    {
                        "name": "T35",
                        "alias": "J0"
                    }
                ],
                "field_column_map": {
                    "Full Name": "C8"
                }



Analyzing query execution plan result

The IT team analyzes the query execution plan, and updates the ARJDBC query by adding an index on the Full Name field to speed up the query processing time.

After the Full Name field is indexed, the query fetches results faster.


explain query REST API

The following table lists details about using the explain query REST API to get the query execution plan.

URL qualifier

POST /api/arsys/v1.0/sql/explain

MethodPOST
Headers
HeaderValue
Authorizationtoken
Content-Typeapplication/json
(Optional) X-AR-Client-TypeClient Type ID
(Optional) X-AR-RPC-QueueRPC queue to which the client calls are routed
(Optional) X-AR-Timeout

Timeout (in seconds) for REST request

Default value —120 seconds

(Optional) X-AR-TR-Core-IdCore ID in a trace ID
(Optional) X-AR-TR-CounterCounter in a trace ID
(Optional) X-AR-Trace-IdComplete trace ID
(Optional) X-AR-TR-Is-Counter-LockedLock counter
ReturnsReturns query execution plan in the database specific format.


Sample query and output formats

The AR System server returns the query execution plan in a database-specific format.

See the sample query and output formats for MS SQL, Oracle, and PostgreSQL databases.


Sample query
{
    "sql": "SELECT DISTINCT `JDBCTestFormA`.`Request ID` AS C1, `JDBCTestFormB`.`Integer Field` AS C2, `JDBCTestFormB`.`Short Description` AS C3 FROM `AR System Schema`.`JDBCTestFormA` AS `JDBCTestFormA` INNER JOIN `AR System Schema`.`JDBCTestFormB` AS `JDBCTestFormB` ON ( `JDBCTestFormA`.`Request ID` = `JDBCTestFormB`.`Request ID` ) ) T0 LEFT OUTER JOIN ( SELECT DISTINCT `JDBCTestFormA`.`Request ID` AS C4 FROM `AR System Schema`.`JDBCTestFormA` AS `JDBCTestFormA` INNER JOIN `AR System Schema`.`JDBCTestFormB` AS `JDBCTestFormB` ON ( `JDBCTestFormA`.`Request ID` = `JDBCTestFormB`.`Request ID` ) WHERE ( `JDBCTestFormB`.`Integer Field` =10")
}
Result for the MS SQL database
{
    "execution_plan": "SELECT DISTINCT J0.C1 C1, J1.C536870918 C2, J1.C8 C3 FROM T817 J0 INNER JOIN T818 J1 ON (J0.C1 = J1.C1)\n  |--Nested Loops(Inner Join, OUTER REFERENCES:([J0].[C1]))\n       |--Index Scan(OBJECT:([ARSystem].[dbo].[T817].[I817_0_379] AS [J0]))\n       |--Clustered Index Seek(OBJECT:([ARSystem].[dbo].[T818].[pk_T818] AS [J1]), SEEK:([J1].[C1]=[ARSystem].[dbo].[T817].[C1] as [J0].[C1]) ORDERED FORWARD)",
    "table_map": {
        "JDBCTestFormB": [
            "T818 J1"
        ],
        "JDBCTestFormA": [
            "T817 J0"
        ]
    }
}
Result for the Oracle database
{
    "execution_plan": "Plan hash value: 2696098365\n \n-----------------------------------------------------------------------------------------\n| Id  | Operation                     | Name    | Rows  | Bytes | Cost (%CPU)| Time     |\n-----------------------------------------------------------------------------------------\n|   0 | SELECT STATEMENT              |         |     1 |   160 |     1 (100)| 00:00:01 |\n|   1 |  HASH UNIQUE                  |         |     1 |   160 |     1 (100)| 00:00:01 |\n|   2 |   NESTED LOOPS                |         |     1 |   160 |     0   (0)| 00:00:01 |\n|   3 |    NESTED LOOPS               |         |     1 |   160 |     0   (0)| 00:00:01 |\n|   4 |     INDEX FULL SCAN           | PK_T817 |     1 |     9 |     0   (0)| 00:00:01 |\n|*  5 |     INDEX UNIQUE SCAN         | PK_T818 |     1 |       |     0   (0)| 00:00:01 |\n|   6 |    TABLE ACCESS BY INDEX ROWID| T818    |     1 |   151 |     0   (0)| 00:00:01 |\n-----------------------------------------------------------------------------------------\n \nQuery Block Name / Object Alias (identified by operation id):\n-------------------------------------------------------------\n \n   1 - SEL$58A6D7F6\n   4 - SEL$58A6D7F6 / J0@SEL$1\n   5 - SEL$58A6D7F6 / J1@SEL$1\n   6 - SEL$58A6D7F6 / J1@SEL$1\n \nPredicate Information (identified by operation id):\n---------------------------------------------------\n \n   5 - access(\"J0\".\"C1\"=\"J1\".\"C1\")\n \nColumn Projection Information (identified by operation id):\n-----------------------------------------------------------\n \n   1 - (#keys=3) \"J0\".\"C1\"[VARCHAR2,15], \"J1\".\"C536870918\"[NUMBER,22], \n       \"J1\".\"C8\"[VARCHAR2,254]\n   2 - (#keys=0) \"J0\".\"C1\"[VARCHAR2,15], \"J1\".\"C8\"[VARCHAR2,254], \n       \"J1\".\"C536870918\"[NUMBER,22]\n   3 - (#keys=0) \"J0\".\"C1\"[VARCHAR2,15], \"J1\".ROWID[ROWID,10]\n   4 - \"J0\".\"C1\"[VARCHAR2,15]\n   5 - \"J1\".ROWID[ROWID,10]\n   6 - \"J1\".\"C8\"[VARCHAR2,254], \"J1\".\"C536870918\"[NUMBER,22]",
    "table_map": {
        "JDBCTestFormB": [
            "T818 J1"
        ],
        "JDBCTestFormA": [
            "T817 J0"
        ]
    }
}
Result for the PostgreSQL database
{
    "execution_plan": "HashAggregate  (cost=31.73..33.63 rows=190 width=52) (actual time=0.986..1.002 rows=9 loops=1)\n  Output: j0.c1, j1.c536870918, j1.c8\n  Group Key: j0.c1, j1.c536870918, j1.c8\n  ->  Hash Join  (cost=14.28..30.30 rows=190 width=52) (actual time=0.088..0.946 rows=9 loops=1)\n        Output: j0.c1, j1.c536870918, j1.c8\n        Inner Unique: true\n        Hash Cond: (j0.c1 = j1.c1)\n        ->  Seq Scan on public.t4541 j0  (cost=0.00..15.18 rows=318 width=16) (actual time=0.020..0.301 rows=318 loops=1)\n              Output: j0.c1, j0.c2, j0.c3, j0.c4, j0.c5, j0.c6, j0.c7, j0.c8, j0.c16, j0.c379, j0.c536870913, j0.c536870915, j0.c536870916, j0.c536870917v, j0.c536870917c, j0.c536870917d, j0.c536870917usd, j0.c536870917inr, j0.c536870918, j0.c536870919, j0.c536870920, j0.c536870921, j0.c536870923\n        ->  Hash  (cost=11.90..11.90 rows=190 width=68) (actual time=0.047..0.049 rows=9 loops=1)\n              Output: j1.c536870918, j1.c8, j1.c1\n              Buckets: 1024  Batches: 1  Memory Usage: 9kB\n              ->  Seq Scan on public.t4542 j1  (cost=0.00..11.90 rows=190 width=68) (actual time=0.012..0.023 rows=9 loops=1)\n                    Output: j1.c536870918, j1.c8, j1.c1\nPlanning time: 0.285 ms\nExecution time: 1.085 ms",
    "table_map": {
        "JDBCTestFormB": [
            "T4542 J1"
        ],
        "JDBCTestFormA": [
            "T4541 J0"
        ]
    }
}    


Was this page helpful? Yes No Submitting... Thank you

Comments