Intensive Recording Function Architecture

Question

I am using Ruby on the rails supported by the oracle database and memcached for my current project.

There is a rather heavy function used that relies on individual database views as a data source, and this data source internally has other database views and tables inside.

This is a virtual db view to have access to everything from one place, and not to a materialized db view.

Users in most cases, if they are in the function that they want to update, it is therefore important to keep the data up to date.

When retrieving data from this view, I include the connection security table in the view (the security table is not part of the view itself), which contains some fields that we use to control access to data at a more narrow level. For example, the security table has columns user_id, prop_1, prop_2 , where prop_1, prop_2 are the columns available on the db view, and user_id is the registered user. Some users have the same details in the security table, say prop_1 = 1 and prop_2 = 1 , but can also have prop_1 , like another user, but have different prop_2 as prop_1 = 2 and prop_2 = 1 . There are many different combinations of prop_1 and prop_2, think of them as FK for another table, so you can have many records.

To date, the time to get entries in the application is almost 10 seconds, it is quite slow. I am considering an alternative approach.

Firstly, it was a materialized view, but since the user makes frequent updates, this may not be the best choice, since updating the view may take some time.

Secondly, I thought it was a cache to use the combination of prop_1 and prop_2 as a composite key for the underlying data, since many users have the same combination, and one who has the same combination can access those same data.

However, this approach may require more rewriting and logic to save and retrieve data in fragments, rather than from the same place with a single query, as in the database view.

In your experience, how did you feel about the same / similar issue? Or is there a better approach I could try?

For those of you who are going to ask what you tried. At first I think of a solution, collecting information from reliable resources and more experienced people, then I am going to make an informed decision and begin to implement it. The implementation at first, thinking secondly, was mistaken so many times

Answer 1

It is difficult to give a good answer without additional information about your presentation, but I will try to try it.

First of all, I ask a question about using one very complex representation. This is hard to configure and can often cause performance problems, so if you can break it down in the application, which will be my first bet.

Secondly, have you considered an implementation plan (explanation plan) for a request with security filters enabled? Does it use reasonable indexes? If not, create them. Perhaps the security properties are not indexed, for example?

A third option would be to use PL / SQL and call a stored procedure that acts as a view. This gives you more control in the database, allowing you to manage the request and break it down into several stages, but get the same result as today.

Finally, you can rewrite the view for better performance. Often the WITH function is overlooked, which allows you to run a query before the main query and use the result in a table. This has helped me significantly improve performance for complex views.

DBMS_RLS is cool, but can be expensive, it requires Enterprise Edition, and it won’t surprise me if you need a separate license. First I would like to get a software solution.

Answer 2

If you carry some delays, you can probably result in your db, you can transfer some of your views to the REDIS database (data in the structure storage memory), which is probably one of the most efficient in the read / write mode.

Regarding the upgrade problem, you can implement websocket to deploy / push the exact update directly to those who need it.

I emphasize that this feature required some changes both on the client side and on the server side, but I assume that this is the best approach to update the final viewing of users with low latency.

Best wishes

Answer 3

Repeatedly joining a complex view presents performance problems.

The values ​​of prop_1 and prop_2 that you want to limit? That is, do you join the security table in these columns, for example,

 WHERE my_view.prop_1 = security_table.prop_1 AND my_view.prop_2 = security_table.prop_2 AND security_table.user_id = :current_user_id 

?

Next question: do prop_1 and prop_2 map columns in view base tables? If so, can they be used to quickly access rows from base tables (outside your view)?

If so, I would try using DBMS_RLS.ADD_POLICY add security policies to the base tables to ensure your security (i.e. limit the values ​​of prop_1 and prop_2 based on the current user) and not join the opinion table at all.

If you add security policies to the underlying tables, Oracle will add these predicates when accessing the tables before the complexity of your query begins. This can give the Oracle optimizer the extra help needed to speed up the process.

Without seeing your code, it's hard to say more.

Answer 4

"relies on individual database views as a data source, and this data source internally has other database views and tables internally."

If it were an object, we would call it the Object of God , which is bad. This is just an anti-pattern in the database area. Without knowing the details, it's hard to be sure, but you probably have a mess of inner joins, outer joins, and cross joins, which leads to de-normalization, data duplication, and (possibly) integrity issues.

Of course, you have performance problems, which is inevitable, because such a thing does not rebuild. If you want one row or ten thousand rows to match the same query. You are not giving the optimizer the ability to make smart decisions.

So, the first thing you need to do is break this view down into meaningful data objects (views or tables) that map to targeted business domains. You are already using Rails, it should not be so difficult to manage a better level of data access.

In terms of security, Oracle has a built-in implementation of a virtual private database. If you have Enterprise Edition, you should definitely use DBMS_RLS to control access at the row level (and column level). The main advantage of RLS is that it is invisible: set a policy in a table or view and automatically apply to all SQL running on the object.

If you are using the standard version, you are stuck using explicit joins in your security table (but see below).

Regarding the use of memcached , in my experience application developers tend to build external caches because they don’t understand how Oracle databases work, and therefore they implement poor data access strategies - for example, routing through just one monstrous view ...

Interrupting your DAL in discrete significant objects will give you better performance, as the database optimizer will be able to choose the most efficient way to retrieve the exact set of necessary information. In addition, search paths will be better because hot (most frequently requested) blocks will be useful in the database buffer cache, while at the moment I suspect that it is completely destroyed by the excess of a full table scan. You can use Server Result caching, which can help "users have the same combination, and [who] can access the same data" Learn more .

Thus, you may find that you do not need an external cache at all. Of course, by allowing the database to properly manage its data - using technology appropriately - you should find that you need a lot less data stored externally. You describe your application as "write intensive", so you have to spend many cycles to synchronize the cache and the database. Obviously, if you are dealing with the amount of data on Facebook, you need to use Facebook style approaches to data management. But, as a rule, a Simplified thing that could work remains the best starting point.