Cannot properly seed the cache due to DATETIME.

Question

OAS 7.0

Hi!

I want to seed a really slow dashboard, and I want to set the filter to properly seed a big date range (20 years).

I used the server variable current year which results in [01.01.2023 00:00:00]. This method results in a really swift load.

I then thought I could just choose a distinct value [01.01.2000 00:00:00] and it would work fine, but it did not work at all. I then tested to choose distinct value [01.01.2023 00:00:00] and proceeded to seed, which also resulted in a really slow load - which suprised me since it was so swift with the server variable.

I believe I am missing some knowledge or something. Is it the dateformat that makes it sluggish and slow, which is somewhat alleviated with a server variable, or is there something else at play?

It returns a row for every unpaid invoice past due date in the date range, which is why I want to do 20 years - 5 years is also fine if the date format makes it slow.

What is a good way to go about achieving a swift load like I want?

Madasamy -Oracle · Accepted Answer

Hi @Kristian B. Joergensen

So when we say slower load times, i do understand that its the cache seed time , ie the time taken for the agent that executes and seeds nqsserver cache takes a lot of time for the records over 20 years?

Do you see much difference in the "physical query execution time" between the good one and the slower one? Check if you need to tune the CACHE parameters in the NQSCONFIG.INI File based on the size of the cache that gets saved for that analysis and for all larger analyses.

DATA_STORAGE_PATHS = "resultscache" 500 MB;
MAX_ROWS_PER_CACHE_ENTRY = 100000;  # 0 is unlimited size
MAX_CACHE_ENTRY_SIZE = 20 MB;  # This Configuration setting is managed by Oracle Enterprise Manager Fusion Middleware Control
MAX_CACHE_ENTRIES = 1000;  # This Configuration setting is managed by Oracle Enterprise Manager Fusion Middleware Control
POPULATE_AGGREGATE_ROLLUP_HITS = NO;
USE_ADVANCED_HIT_DETECTION = NO;

MAX_SUBEXPR_SEARCH_DEPTH = 7;
DISABLE_SUBREQUEST_CACHING = NO;

#Cache file buffer size.Default is 128 KB
CACHE_FILE_BUFFER_SIZE = 128 KB;

# Cluster-aware cache.
# Note that since this is a network share, the directory should not be
# relative.
GLOBAL_CACHE_STORAGE_PATH = "" 0 MB;  # This Configuration setting is managed by Oracle Enterprise Manager Fusion Middleware Control
MAX_GLOBAL_CACHE_ENTRIES = 1000;
CACHE_POLL_SECONDS = 300;
CLUSTER_AWARE_CACHE_LOGGING = NO;

GayathriAnand-Oracle · Accepted Answer

Hi

Please check session.log when you execute the dashboard after seeding cache using both methods and verify if the one taking longer is producing a cache hit or is it sending to database.

Check Table 5-3 Factors That Determine Whether Cache Is Hit in the following Cache hit section of doc :  https://docs.oracle.com/middleware/1221/biee/BIESG/querycaching.htm#BIESG254 to understand why a query might not be producing a cache hit.

Thanks

Gayathri

Madasamy -Oracle · Accepted Answer

to add on top of @GayathriAnand-Oracle inputs, @Kristian B. Joergensen begin with the session log with higher loglevel and check if the logical query and the plan are same of the one that is generated by the users and the one that you seeded the cache.

If the logical query and its plan changes for the case#1 , it may not be consuming the cache

Federico Venturin - Oracle · Accepted Answer

Hi @Kristian B. Joergensen ,

Can you share more details about the dashboard prompt? e.g. the data type of the prompt, whether it's column based or a variable one, the way you are populating the list of available and default values, the way you are referencing it in any analyses in the dashboard, etc.