Ways to limit amount of data from DB(Oracle) to OBIEE 12c

Question

OBIEE 12.2.1.4

What are effective ways to limit the amount of data that Oracle DB returns to the BI

The purpose is to limit amount by rownum or by datasize

We use ResultRowLimit parameter in instanceconfig.xml and its value is 65000.

All logical queries include this limit but some physical dont and we can locate it in in usage tracking tables. It could be queries in pivot tables and smth else.

Probably there are another ways?

We know about row limits for every database in repository and default_db_max_rows parameter in nsqserver. But it doesnt work like we expect.

PS What are

# Following are low priority query governance limits parameters

and

#following are marking query governance limits parameters

sections in nqsconfig.ini?

What are these parameters in these sections?

MARKING_QUERY_DB_MAX_ROWS = 0; # 0 means unlimited. The default value is 0.

MARKING_QUERY_DB_MAX_DATA_SIZE = 0; # 0 means unlimited. The default value is 0. The value can be in KB, MB, GB.

# LOW_PRIORITY_DB_MAX_ROWS = 0; # 0 means unlimited. The default value is 0.

# LOW_PRIORITY_DB_MAX_DATA_SIZE = 0; # 0 means unlimited. The default value is 0. The value can be in KB, MB, GB.

I havent found any describtion how it works

Thanks)

Mallikarjuna Kuppauru-Oracle · Answer

Hi  DENISKA80

Check below for Parameter Description - low priority query governance limits

LOW_PRIORITY_MAX_SERVER_THREAD_COUNT   --> the maximum number of server threads can be used to run the low priority queries, 0 means no low priority query allowed.

LOW_PRIORITY_DB_MAX_ROWS      --> the maximum number of rows can be returned from physical DB for low priority queries, 0 is unlimited size.

LOW_PRIORITY_DB_MAX_DATA_SIZE       --> the maximum data size can be returned from physical DB for low priority queries, 0 is unlimited size.

LOW_PRIORITY_DB_MAX_EXEC_TIME       --> the maximum physical DB query execution time for low priority queries, 0 is unlimited.

LOW_PRIORITY_MAX_LOGICAL_QUERY_EXEC_TIME  --> the maximum logical query execution time for low priority queries, 0 is unlimited.

Regards,

Arjun

Steve F-2 · Answer

@DENISKA80 , to add to @GayathriAnand-Oracle's correct answer, you can reference: Setting Query Limits

What are these parameters in these sections?

Marking queries are secondary queries for operations,  such as brushing. Those settings are not relevant to your question.

Hope that helps. Other comments welcomed.

GayathriAnand-Oracle · Answer

Have you set RPD , query limit as below :

In the RPD =>  manage Identity => Application Role => Permissions => Set the Query Limits. You can limit queries by the number of rows received, by maximum run time, etc.