Categories
Cannot properly seed the cache due to DATETIME.
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?
Best Answers
-
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;
0 -
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
1 -
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
1 -
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.
1
Answers
-
No, it is not the seeding that takes a lot of time (it seems to run fast) - it is the dashboard loading.
After seeding the dashboard uses a lot of time to load when seeded with distinct value, while when seeded with server variables (both values being '01-01-2023 00:00;00') results in vastly different load times.
Seeded with distinct value '01-01-2023 00:00;00': 25 seconds+ (Feels unseeded)
Seeded with server variable '01-01-2023 00:00;00': 1 second
The object that carries the values is the dashboard prompt.
0 -
When seeding with a specific date, it does not hit the cache, so I am a step further - thank you!
0 -
When I look it up in DV: 'date' in I use in dashboard prompt is datatype: Time, is set to be treated as attribute and the table that it presents in a workbook does not show all dates (saturdays and sundays are excluded - I anticipate that the db has no records on saturdays/sundays), but has single entries per date - for example, the earliest is 01/02/2012 12:00:00 AM.
These insights effectively solved the issue. I cached it now and it works when I input the earliest record of the time attribute of the database.
The reason for cache miss was that the specific value I set was 01/01/2023 23:00:00 instead of 00:00:00 and I did not see that it had 23 because the box was too small to show the complete entry.
Thank you to all contributors!
0