Hang Analyze

I got an email from a user about incredibly slow response times and never ending queries. When I attempted to check for users holding locks, the query would lock up and not run. So, I decided to run a hang analyze in order to get some trace dumps for Oracle Support. Here is how:

Login as sysdba

oradebug hanganalyze 3;

wait 2 minutes and note the location of the trace file

oradebug hanganalyze 3;

Start another sqlplus session as sysdba

ALTER SESSION SET EVENTS '10998 trace name context forever, level 1';

alter session set events 'immediate trace name SYSTEMSTATE level 10';

After generating the dumps, used the UNIX top command to look for Oracle processes that were hung. Found an Oracle user process that was using 99% CPU and all of the other Oracle processes were "waiting". So, I decided to kill the process. First I did ps -ef pipe to grep 99999 (where 99999 is the PID) to see what this process was. Then I determined that the process could be safely killed. Next, I killed it and the PID (Process Id) that was associated with the rogue process. [su - oracle followed by kill -9 99999] Once the PID was killed, all other Oracle processes began "running" and the crisis was over. My next step is to open a SR with Oracle Support and upload the trace files.

Check out the comments for more instructions by me.

Grouping Objects Into Tablespaces

We need to revisit our tablespaces for migration to a new set of hardware. So, I am thinking about how best to carve up our space into tablespaces. We have let our tablespaces and objects get out of hand. With applications and users putting data everywhere. For our new system, I plan on enforcing tablespace quotas. However, we still need to rethink how to logically divide up the Oracle database into tablespaces. Of course, the required tablespaces will be present: SYSTEM, TEMP, UNDOTBS, SYSAUX, etc. I prefer to keep everything system managed. No more manual extent and FREESP management for me.

The new system is a 10g data warehouse/DSS hybrid. So, tables with similar characteristics will be grouped into a tablespace. For example, all tables that are read-only could be grouped into a single, read-only tablespace. Tables with random I/O patterns could also be grouped together; all small tables should be grouped together. Segregate Indexes in separate tablespaces away from their tables. Partition really large table and indexes into separate tablespaces. Keep working or intermediate objects that will be dropped following a processing cycle in separate tablespaces. These are just a few examples.

Now how large is really large? For my databases, any tables over 100GB would be considered really large. Of course, what constitutes really large in your environment will depend upon the underlying hardware and applications of your database server.

My First Blog

This is my first blog and first post. If you have any suggestions, please leave a comment. This blog will mainly deal with Oracle DBA topics. However, other topics will be covered as well. Work keeps me so busy that until now didn't think I needed a blog. Then, I attended a class by Mike Ault on RAC and saw his blog. Also, one of my best friends has his own DBA blog (SAMDBA). Thought it was a good idea because Mike said that it is useful for him to leave things on his blog that he can access from anywhere in the world. It would also be awesome to have feedback from other DBAs and provide information that can help other DBAs. I have a ton of notes that I keep and thought it would be better to just keep things that I use often in a blog. The tough part will be to not disclose any proprietary information from my real job. Again, if you have any questions or comments please leave them.