Naming Your DBA User

Even if it seems irrelevant how you name your DBA user, the one you would use for creating most of the database objects, actually it could be of importance. There are some scenarios where this could speed up the system beyond expectations. Here’s one.

In a fairly complex implementation of the role-based access control (RBAC) while using multiple views to constrain the data users can work with in several ways, with the users accessing the system using the same application, there is need for excessive use of synonyms. The main idea is that application can execute queries like “select a,b from somename” where somename is the synonym for a view that’s reducing the table data for the logged-in user.

For each user and all the views she is granted, private synonyms are created. All the synonyms related to views that are built upon a same table have the same name, so a frequently used table would, in this manner, produce that many same-name private synonyms as there are users on a system. Also, for administrators’ use, additional public synonym with the same name is created for each table. Public synonyms are also used for accessing the data in many stored procedures that are used by other group of applications. This results in having thousands of same-name private synonyms and one public. Hope you’ve not given up on reading this already, this is where complexity ends.

The problems (aside from obvious administration nightmare) are performance-related. In cases where mainly public synonyms are used, there are many systables reads. In fact, majority of the reads are those on systables table. If you remember, there is a composite index on systables, on tabname and owner columns, but it seems that, in order to determine the public synonym is the one to be used, the system has to go through other synonyms with the same name.

Now for the numbers regarding this story: I got a hint from an Informix developer, to create a user with the name that should order before others in used locale, and use that user to re-create all public synonyms. My current DBA user is named with “h”, so I created one called “aaaaa”, and re-created the public synonyms. This reduced the systables reads to about 40%, and sped up the system more that a double!

So there you have it, changing the name of a user that we created only the public synonyms with reduced the time of queries by about 50-60%. I’m not sure if this kind of algorithm is used elsewhere by Informix in determining actual objects to be used while parsing the queries. Only Informix developers could say, maybe there is still some possible performance gain. But from now on, my DBA users on all systems would be named “aaadba”.

Advertisements

, ,

  1. Leave a comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: