Impact of large numbers of table insertion on the cache log

Hi Peter

Your replies to my queries on the multiple caches available from 5.3.2 onwards have raised some questions in my mind.

We have schemas for which the data is fully refreshed on a regular basis. In some circumstances we don't wish to provide full schema level access and instead provide controlled access to the table or tables that are to be refreshed via an access account, which is used to run the refresh process.

Normally a pl/sql package that is owned by the schema owner is granted to the access account. This will include functionality to truncate the table (or tables) to be refreshed, and to drop and re-create the indexes.

In practice we've found that dropping and re-creating Jchem indexes in this way doesn't work. Although the pl/sql runs under the authority of the owning schema and we can supply the correct password the Jserver attempts to drop and re-create the index as the access account, and so the re-creation fails.

Until recently I wasn't too worried by this. As I've said we can truncate the table and leave any Jchem indexes in place and then re-insert the rows. We are talking of several million rows here so there would be a performance overhead of carrying out the index maintenance - but that isn't too large an issue. What does concern me is that we may end up with very large numbers of entries in the cache log for each table - and that this could slow down and use of the index.

The questions that come to mind are:

(a) is my interpretation of the caching log mechanism correct ?

(b) is there any way that the cache can be emptied, other than by re-building the index. Does stopping a Jserver empty all logging table caches ?

(c) is there a way to alleviate the problem by telling the Jserver what account it should be connecting as, when building an index  ?  Functionally this may mean that a call to jchem_core_pkg.use_password would have a second 'account' parameter which would default to the currently connected user. 

(d) Irrespective of the described 'full refresh' scenario, for tables with significant dml activity, is the current cache logging process likely to lead to index perfomance degradation over time ?

Regards

Ant

Hi Ant,

Just to make sure that I properly understand the details of the issue:

By access account, do you mean the account specified by oracle.server.login jcart property?

Thanks

Peter

Hi Peter

No, by 'access account' I'm referring to an oracle account that would have a limited set of privileges and would commonly be used by first tier support staff. So in the case described the account may have direct insertion privileges on the tables in question and the ability to drop indexes/truncate/create indexes granted via a pl/sql package, but no other privileges. This is another example of the sort of security that is commonly encountered in larger corporate environments.

Regards

Ant

Hi Ant,

> (a) is my interpretation of the caching log mechanism correct ?

I think your interpretation of the cache log mechanism is correct to the extent that your performance concerns about INSERTs vs. INDEXing are valid.

> (d) Irrespective of the described 'full refresh' scenario, for tables with significant dml activity, is the current cache logging process likely to lead to index perfomance degradation over time ?

If you perform structure search on any given JChem index only through JChem Cartridge (in a non-RAC environment) -- including other ChemAxon tools (such as IJC or JC4XL) searching only through JChem Cartridge -- INSERT performance will be not impacted at all compared to previous JChem versions. If multiple caches operate on the same indices, each structure INSERT will entail an extra log entry INSERT into the _UL tables for each additional structure cache.

> (b) is there any way that the cache can be emptied, other than by re-building the index. Does stopping a Jserver empty all logging table caches ?

I am not sure how this would help address your concerns. Perhaps, on this point there is some misunderstanding of the mechanism.

> (c) is there a way to alleviate the problem by telling the Jserver what account it should be connecting as, when building an index  ?  Functionally this may mean that a call to jchem_core_pkg.use_password would have a second 'account' parameter which would default to the currently connected user.

Your suggestion is perfectly feasible (at the first glance). A similar purpose could be achieved in another way: we could provide a separate stored procedure which could be used to instruct JChem Cartridge to start using the impersonated user (the schema owner of the pl/sql package). This second way would perhaps better fold into Oracle's concept about identification and be significantly safer (regarding potential user errors), while your suggestion is certainly more flexible. What do you think?

Peter

 

What does concern me is that we may end up with very large numbers of entries in the cache log for each table - and that this could slow down and use of the index.

Cache logs for any particular cache instance are purged when that cache is updated (i.e. when a search is performed using that cache).

So no need to worry about an ever growing amount of cache logs.  

Thanks Peter - my worries are largely allayed. For the time being we'll accept the overhead of inserting to the cache log when the table is being re-populated. Once a search is carried out the log entries should disappear.

Going forward, I think that the idea of being able to run a cartridge database connection as an index owner when the parent session is connected as a different account would be most useful. To do this securely the parent session would need to supply the (encrypted) password for the table owner. The presence of a cached password should be ignored in this case.

Please add this to your list of possible future enhancements.

Regards

Ant

 

 

Hi Ant,

JChem 5.3.6 has been released including the ability to specify a different account for use_password.

Best regards,

Peter