Use Case:

Init block execution failures cause issues in OAC. Following is some of the common use cases:

1. Data level security not getting correctly / incorrect report data

2. Loading of the subject areas taking a long time while creating analysis

3. "504 Gateway Time Out" Errors

Available Troubleshooting Options:

1. Report’s Query log from Manage sessions - The query log does not capture the Init block execution even with LOGLEVEL set to 7.

2. Testing the Init blocks directly from the RPD in cloud mode - If there are many Init blocks, then it becomes difficult to test each Init block manually.

3. Usage tracking - Usage tracking table for Init blocks does not record the error if there is any failure.

4. Enabling OCI logging - It does not show the query result of the Init block queries (i.e.) success / failure. Even it fails, the error message is not captured.

Idea To Enable Self-Service Troubleshooting:

In OCI logging, we can add a status attribute within "additionalDetails" to show the Init block query result as shown below:

"##############################################\n-------------------- SQL Request, logical request hash:\n5154e5ff\nselect count(distinct amount_sold) from sh.products, sh.times, sh.sales\r\n\n"

{

 "datetime": 1690826268511,

 "logContent": {

   "data": {

     "additionalDetails": {“status”: “[nQSError: 60009] The user request exceeded the maximum query governing execution time.

[nQSError: 17001] Oracle Error code: 1013, message: ORA-01013: user requested cancel of current operation

at OCI call OCIStmtFetch.”},

     "category": "query",

     "ecid": "<ECID>",

     "logLevel": "info",

     "message": "##############################################\n-------------------- SQL Request, logical request hash:\<hash value>\nselect count(distinct amount_sold) from sh.products, sh.times, sh.sales\r\n\n",

     "userId": ""

   },

   "id": "<id>",

   "oracle": {

     "compartmentid": "<OCID of compartment>",

     "ingestedtime": "<timestamp>",

     "loggroupid": "<OCID of log group>",

     "logid": "<log id>",

     "tenantid": "<OCID of tenant>"

   },

   "source": "<OAC Instance OCID>",

   "specversion": "<version>",

   "time": "<timestamp>",

   "type": "com.oraclecloud.analytics.analyticsinstance.diagnostic"

 },

 "regionId": "<region>"

}