by Robert Buda | Jan 3, 2011 | Best Practices, Database, Database Security
As the New Year begins, many of us take the beginning of a new calendar year as a cue to to clean out and shred our old files and reorganize our storage spaces so that we have room for all the new supplies and equipment we will need in the new year.
We do this because we know that if we did not do this each year, the task would get overwhelming and would likely not get done at all.
We know that clutter is bad. Clutter slows us down when we need to find something. Clutter can cause injuries as we step over old boxes. Clutter can cause us to lose track of important files and may prevent us from securing sensitive files properly.
As we do this with our physical files in our file cabinets and with the supplies in our closets, we should take the same steps with our database systems.
A thorough database security audit should be performed each year and one deliverable of the audit should be a list of potential items to be removed.
Clutter in our database system takes various forms. This includes user clutter and object clutter.
- Obsolete user accounts (accounts for terminated employees or contractors).
- Unused test accounts.
- Obsolete tables and columns.
- Unused stored procedures and functions
- Unused database links
Of course, these items should be cleaned out more often than once per year. In a perfect world, we would never have obsolete user accounts or unused tables in any database. But performing an annual cleanup ensures that nothing gets missed.
This cleanup should be performed on both Production and Development databases. There will likely be more object clutter (unused tables or stored procedures) in the development database than in the production database. The production database will likely have more user clutter (obsolete user accounts).
Obviously, extreme care must be taken when performing such a cleanup. Appropriate backups should be performed beforehand as with any maintenance task.
When cleaning up obsolete users, you can start by asking your HR department for a list of employees that have been terminated during the year. Check to see if their corresponding accounts have been removed.
It is common to find that the account of a terminated employee is still in use by employees that have assumed his duties. In this case, the privileges (and any objects) of the terminated user should be transferred to the user that remains and the account should be removed.
When identifying obsolete objects, look for the following as potential candidates for removal:
- Tables with names indicating a temporary nature. Tables with Test or Temp as part of the name
- Tables with a persons name attached.
- Tables with a date attached.
- Tables that do not follow the naming convention for the system.
- Empty tables
Before removing any table, check with the development team and the application owners to see what they know about it. Of course backup any table or other object individually before removing it. Also check for any foreign key constraints involving the table.
Keeping your database clean in this fashion will make it more secure and easier to maintain and understand as the years go by. A fifteen or twenty year old system can accumulate a surprising amount of clutter if left unchecked. Just like that storage space in the basement.
Happy New Year!
by Robert Buda | Oct 21, 2010 | Best Practices, Oracle
One of our Oracle Consultants shared a story with me today that illustrates the importance of periodically reorganizing objects in an oracle database.
He was examining a performance problem with a very small oracle table. The table had only 154 rows!. Hardly a large table, and yet it was taking over a minute to do a full table scan.
After some investigation, he found that in the past the table had over a million rows. These rows had been deleted but the table was never reorganized to remove the unused space. The table was taking up over 130,000 blocks of space, for 154 rows.
Not only was this a great deal of wasted space that could have been used for something else, it also caused a serious performance problem.
He copied the table to a temp table, truncated the original, and then reimported the table data to reorganize the table, and the performance problem was solved.
There are a few methods that can be used to reorganize an Oracle table. These include:
- Using ‘Create table as select * from …’, truncating the table, and then re-inserting from the copy, as was done in this case.
- Using export/import to dump the table out to an external file and then bring it back in.
- Unload the data to a flat file and use sqlloader to bring it back in.
Each of these options has its pros and cons and we will explore these in more detail in an upcoming white paper.
The important thing to remember is that no matter how you reorganize your database objects, best practice is to be sure that this is done on a periodic basis to avoid space and performance problems.
by Robert Buda | Sep 27, 2010 | Best Practices, Database Security, Oracle
Database administrators often have to provide test data sets to developers, QA teams, or UAT teams.
This can be done manually creating test data sets, which can take a very long time especially when the goal is to stress test a new system and a large amount of data is required.
An alternative approach often taken by database administrators is to copy the production database. Too often, an exact copy of the production data is used for this purpose, which of course raises a number of database security risks.
To mitigate these risks, database administrators typically mask or obfuscate the production data set before sending it out.
This process involves writing a set of scripts that will apply a mask or function to each sensitive piece of data so that the true data is hidden. Writing these scripts is very time consuming because there are many factors to consider when masking the data, including the need to preserve the relationship between data in tables, in order for the new data to make sense.
Oracle Enterprise Manager can now help significantly reduce the amount of work necessary to do this task. By using the data masking capability in OEM, a database administrator can specify the mask or function that must be applied to each sensitive column of the database. Oracle data masking will then generate a set of pl/sql scripts that will perform the masking for you.
It will automatically handle the preservation of relationships between tables, as specified either by existing foreign key constraints in the database, or by relationships that the dba can specify in the Oracle masking tool. It also enables the dba to specify fields that must remain logically in sync after the masking operation, such as a city and state. The generated scripts will ensure that those relationships remain consistent in the masked data.
This Oracle masking feature makes it much easier for oracle database administrators to quickly provide data sets to development and testing teams while reducing the risks of exposing sensitive data.
An excellent article about this feature with additional usage detail can be found at the following link: at datamasking.html
by Robert Buda | Aug 5, 2010 | Oracle
I recently worked with a client to resolve a significant performance issue related to painfully slow queries in an Oracle database. One of the queries that they wished to execute was taking over five hours to run.
This is an Oracle 10g database running on a Solaris platform. The server has 8 processors so there is plenty of processing power. The database is very large with a very high insertion rate. The database is about 10 Terabytes and most of the data gets inserted into one large table and is partitioned by date, with a few partitions per day. Therefore the query, which specifies a small date range should only need to look in one or two partitions, so queries should be relatively fast.
Before I became involved, the in-house dba looked at a number of the typical issues. He confirmed that there were appropriate indexes on the table, and that the query was expected to access only one partition. The cause of the significant execution time was not clear.
When I became involved, I was given limited access to the server. I was not given DBA access but I did have select access to the catalog tables so I was able to poke around a bit.
I also received execute privilege to dbms_workload_repository so I could create AWR snapshots and reports. Fortunately, the client had set up hourly snapshots so I was able to select snapshots before and after the query ran. The resulting report is where I found the clue to one cause of the problem.
I ran a report for an 9 hour period during which the query executed.
The top wait event in the AWR report for the day was log sync waits. These were very high, 1,104,069 with a total wait time of 72,781 seconds.
There are a number of causes of log file wait events. Some of these are described in this excellent post.
However, in this case, the cause was something else.
Because of a similar situation that I saw in the past with very high insertion rates, I thought that frequent log file switches may be the cause. I checked v$loghist and found that the logs were switching every five seconds! The optimal frequency of log switches is twenty minutes so this clearly a problem.
The log size was set at 131M which was fine in the past when the insertion rates were lower, but as the rates grew, the logs were never increased to handle the higher rates.
We first increased the logs to 1G and the log switches dropped to every 2-3 minutes. Much better but still far too high. We increased them again to 4G and now the logs switch every 30-40 minutes. This is higher than it needs to be and we will now reduce the size a bit in order to reduce the recovery time in the event of instance failure.
After this change, the log file sync rates disappeared from the top wait events and the system performance has improved significantly. The query that was taking 4-5 hrs is now taking about 3 minutes.
I cannot be certain that insufficient log file size was the only cause of the performance problems because other steps were taken simultaneously including refreshing statistics which can have a significant impact as well.
However, I am certain that the insufficient log size was the cause of the log file sync waits And with a total of 72,781 seconds of wait time over nine hour period (that is about 2.25 hours of wait per processor if it is distributed evenly), it very likely had a significant impact on overall system performance as well.
