Session Limitation to Save Data

Question

I have a query slow in Oracle to list all the accesses of a user, so I thought to record the result in a Session, the average of records returned is 600, it’s too much for a Session?

Would there be some other better way to save this data?

Answer 1

The average of records returned is 600, it’s too much for a Session?

Not. Sessions theoretically have no storage limit.

Would there be some other better way to save this data?

Yes, on a key-value referral server, such as the Redis. Sessions limit you to only one server, while Redis can serve you in a distributed context.

Answer 2

It doesn’t have a question of being too much for the session, it might be too much for the memory you have available. If you have 600 records in the session, it’s little. If you have 1000 simultaneous sessions, 600,000 can be a lot, it may not be.

I I answered something about this, The best solution is a cache system. It is most suitable in most situations because it can be released if necessary and lasts beyond sessions, which in many scenarios can be an interesting gain. There I tell you not to overdo the burden of session. But I’m talking about really exaggerating. And the size of the exaggeration depends on the available memory.

Remember that the database has a cache as well (see how to implement in the Onosendai response). It may be enough as well. Measured to see if you really need to keep something in memory? Do you have reasonable gain? Without measuring you can not affirm anything. Even if it is intuitive, sometimes in practice, something other than expected may occur.

If you see that you need another cache level, look for a solution in memory on the same server. This should solve 99.99% of cases. If you have a rare case at hand and need to scale horizontally, look for software to help with this, like a Memcached, Redis, etc..

There’s a framework that helps in this and has several options. On their page it confirms that the simplest cache is the fastest and that databases serve the most when there is need for distribution between processes (where the performance is absurdly lower, to the point of being not useful as cache in some scenarios). The use of dictionary is also the one that occupies less memory, which is useful to give space for more useful content.

Answer 3

As Gypsy and Maniero said, no, there are no limits.

But if your problem is performance in an Oracle database, I have a suggestion: Materialized Views.

One Materialized View is basically a snapshot of a query cached in the database, which can be indexed.

This is an example of creating Materialized View:

CREATE MATERIALIZED VIEW MV_LOGINS_USUARIO_52
NOLOGGING
CACHE
BUILD IMMEDIATE 
REFRESH FAST ON COMMIT 
AS
[Sua query aqui]

Once created, the view will contain the result of the last query until it is destroyed or refreshed. As you are not running the query again, but only by accessing curly data, the response time is much higher.

The advantage of this method is that you do not consume web server resources.