Archive for the ‘Databases’ Category

HOW TO : Pass environment variables when using sudo

Thursday, May 21st, 2009

Say you need to sudo as a particular user and run a command and at the same time you need to pass an environmental variable to the command, you can do it by passing the command in doublequotes.

For example, I want to start Oracle while I am logged in as another user (vinay), I can start the database using dbstart by issues

"sudo su - oracle -c "dbstart /$ORACLE_HOME"

$ORACLE_HOME is an environmental variable listed under user oracle’s environment.

Needless to say, you need to ensure that you have sudo configured to allow your userID to su to oracle.

Posted in Databases, HOWTO, Linux, Technology | 2 Comments »

Lessons of the trade : Troubleshooting database perfromance

Wednesday, March 11th, 2009

If you have ever worked in an IT shop, you will know that the one thing you cannot escape from is issues related to Database performance. Now, I am no DBA in any way or fashion, but thought I should record some of the common issues and ways they have been overcome in my career so far. More for self records than trying to teach someone :) .

  • Network Related
    • Check if the NIC (network interface) on the DB server has any speed mis match with the network device (most probably a switch) that it is connected to.
    • Check the latency between the application and the DB (pertains to applications connecting to DB over WAN links)
  • System Related
    • Check if a rouge process is using all the system resources.
    • Check if the disk sub system is performing optimally
      • Recommend using RAID 10 for transactional systems.
      • Don’t forget to check those batteries on the RAID controllers :)
    • Check if the DB is just running out of gas.
      • Long term capacity trending records come in handy here.
  • Database Related
    • Views are evil.. if created the wrong way. Esp, on data that is accessed frequently. Remember that you are now making two calls to the database. One to read the data, One to create the view.
    • Ensure your logs are not being writted ot (thx Ray for pointing out the typo) written to the same disk subsystem as your data files.
    • Indexes are good.. But only to an extent. If the size of your indexes is twice the size of your data.. you have an issue.
    • Check for invalid objects. You will be surprised how many times, people overlook this.
    • Sometimes, it helps to flush the SGA (Oracle specific). Be aware that it will slow down the response time for a while (until the cache gets populated again).
    • Avoid excessive monitoring. Esp. with tools that query the system tables quite frequently. This has a negative impact on the database performance.

Did you run into any strange situations and figured out a solution? Please feel free to add your comments below…

Posted in Databases, Technology | 4 Comments »

Lessons of the trade : Data purge in databases..

Tuesday, January 27th, 2009

Quick note to myself.. If you have a high volume transactional database and are looking to purge data from a table(s).. make sure you purge the data in small chunks. If you purge the data in larger chunks (rows), other processes trying to access the data on those tables have to go to the redo logs to access the data, since the purge job will put a lock on the table. This obviously adds latency to the queries.. So purge the data in smaller chunks (rows), forcing the database to flush the redo logs.

Posted in Databases, Technology | 1 Comment »