OAC - Usage Tracking missing log only for Init block table

Daniel Vornicu

Hello,

We activated the Usage Tracking for OAC (with the September 2023 version) and we we created also database object 'UsageTracking' and the connection pool in the semantic model.

Configuration in OAC-System settings:

UsageTracking.SchemaName.UT_LOGICAL_QUERIES  - for logical table

UsageTracking.SchemaName.UT_INIT_BLOCK_INFO  - for init block table

UsageTracking.SchemaName.UT_PHYSICAL_QUERIES - for physical table

and the pool;

UsageTracking.UsageTrackingConnectionPool

I see that only 2 tables are filled with data in real time(logical et physical tables) but not the init block table that stays empty.

Despite this we have 3 init blocks defined in our semantic model that are executed normally but without having the log for them in UT_INIT_BLOCK_INFO table.

Thanks for help,

Daniel

Mallikarjuna Kuppauru-Oracle

Hi Daniel,

Thanks for using Oracle Communities.

You can refer below Troubleshoot guide to debug the issue -

Configure And Troubleshoot Usage Tracking Data Issues In OAC (Doc ID 2812776.1)

Regards,

Arjun

Daniel Vornicu

Hello,

Thanks. Ok, I read the guide and there are only 3 troubleshoting cases. I'm not in none of this cases.

I used the new Web Semantic Modeler as modeling tool, not the Oracle BI Adminstration Tool old classic application as described in guide.

We have on-premise Oracle database using Private Access Chanel (PAC) and a VPN channel. No wallet.sso needed because we don't use a cloud ADW database like in the guide. But our configuration supports very well Usage Tracking like the guide says.

I repet, all the usage tracking parameter are well and 2 of 3 tracking tables are populated in real time.

Only one table(for connection pool logs) is emply. I think it's most probably a bug on the OAC side, because I saw the support for Usage Tracking directlly in the Web Semantic Model is added recenly in September 2023 version of OAC.

Thanks for help!

Gianni Ceresa

Using the Semantic Modeler doesn't forbid you from download the RPD and checking if everything inside is fine by using the Admin tool.

Also, usage tracking should not be "real time" by default, it's asynchronous to not slow down the process. For the normal usage tracking (I imagine it is the one about LSQL and physical SQL executions) you generate a lot of logs easily, filling the buffer and triggering the insert quite often.

Is it possible that your init blocks logs are simply not generating much and therefore you still don't see them because the buffer is still filling up? Is that a live prod environment with a number of users or just a sandbox or something barely used? Or maybe you don't have init blocks executed often, like multiple at each user login to populate session variables, or something like that?

Did you have data inserted the init block usage tracking table before?

Just asking...

GayathriAnand-Oracle

The 3 Init Blocks you created, are they 'Repository Init Blocks' or 'Session Init Blocks' ?

Please note that for OAC currently only Session Init Blocks get recorded in Init Block Usage Tracking Tables.

Repository init blocks do not populate usage tracking table. Please test using Session init block and let us know of outcome.

Daniel Vornicu

Thank you Gianni and Gayathri for suggestions.

All the 3 init block are typed 'Session' and are well executed at each connection. We apply after a filter in a fact table using the values returned by the Init Blocks. The filter works well and each user retrieve the own values from the fact table.

For more check I also activated the Diagnostic Logging for OAC in OCI console. I can see each init block execution and the query sent to the database.

I did't use the usage tracking tables before with BI Admin tool. I started directly to make the configuration in the Web Semantic Model and OAC System Settings after I saw that we have the September 2023 version that can do that. I couldn't compare with a old version or another tool.

I just suppose that is a issue in the last version of OAC. If anyone test it and works fine in the same configuration (on-prem oracle database, PAC, VPN) i'm curious.

Thanks!

GayathriAnand-Oracle

Hi

I know this works with admin tool rpd but havent tested with semantic modeler. If tables get created fine, means it's able to refer the connectionpool/connection. Someone has to actually look at backend logs to see if there are any errors preventing the insert for which we need more details on your instance. Appreciate if you can create SR with support, so we can review the logs and assist further.

Thanks

Gayathri

Daniel Vornicu

Hello,

I create the SR 3-34540243571 related to Usage Tracking in My Oracle Support and I put some error logs there.

Khader Belgoud(d'Oracle) send me the logs when we tried to find some problems related to filter latency (on another SR). I don't know if I can find myself these backend logs in OAC console.

He advised me to deactivate for moment the usage tracking (for the filter latency problem) because OAC try to access and write to the table and throw this 2 errors: ORA-01722 and ORA-12170.

The truth is that without usage tracking the filter works much better but I would like to reactivate this capability in the future.

Thanks,

Daniel

SteveF-Oracle

@Daniel Vornicu - Thank you for following up for everyone's benefit. Once the UT issue is resolved, you should be able to reactivate it.