Sunday, December 18, 2005

How do i collect Stats from Oracle?

Every DBA should be well aware of what's going on in their database. You can't expect to install it, mount it, and hope your users never complain about it. OR that is never crashes on you. OR it has Artificial Intelligence and can call you on your mobile telling you it's not feeling well and needs you to do reindexing. hah hah.. this isn't Dexter's Laboratory man..! Hence, today's topic on Maintaining oracle Alert Log files:: The first place DBA's should look for problematic DB's.

What's an Alert Log file?

It's a love letter full of rainbows and sunshine. Yeah right. ;).. Oracle Alert log files are logs that are churned by oracle that consists of a chronology of messages and errors on your database.
A DBA should always check alert log files regularly:

  1. Detect internal errors (ORA-600) and block corruption errors.
  2. Monitor database operations.
  3. View the nondefault initialization parameters

Where is the Alert log file situated?

It's not buried in your backyard i can tell you that much. It's actually located in the folder specified in your init.ora file under the parameter BACKGROUND_DUMP_DEST. The init.ora file can be located in your oracle home directory, under the database folder. Init.ora basically holds all the parameters to initialize your DB instance. This is where you set your SGA (System Global Area), or in other words how much memory is allocated to your database instance. In later posts, i will ellaborate more on how to tune your SGA correctly to ensure your database returns more cache hits than cache misses.

The alert log file contains information for tuning the database. Use it.

  1. Checkpoint start and end times. (Frequent checkpoints ensures instance recovery takes a shorter time to complete. However, too much checkpoint can also cause an overhead on process. Be aware.)
  2. Incomplete checkpoints.
  3. Time to perform archiving.
  4. Instance recovery start and complete times.
  5. Deadlock and timeout errors.

Moving on...

Background process trace file. This lil' loggie thingy can be obtained from your udump folder, or in other words the folder specified under init.ora file under the parameter USER_DUMP_DEST. Oracle server dumps information errors detected by any background process into trace files. These files don't usually contain tuning information; however if you are tuning user trace files then it will help you gather some stats on what's going on based on a specific user. This is the highlight of today's post:

How do i collect stats from User Trace Files?

First of all, let's look at how we can perform a Session-Level Trace:

SQL> Alter session set timed_statistics = TRUE;

SQL> Alter session set sql_trace = TRUE;

Now, get some information on your session by performing the following query while logged in as sysdba:

SQL> Select sid, serial#, user, osuser, machine from v$session;

You may want to format the columns for a better view (e.g. SQL> col machine format a25). Get the SID and SERIAL# of the user you want to dump to trace. Now, execute this package:

SQL> execute dbms_system.set_sql_trace_in_session ("SID","SERIAL",TRUE);

Once you get the 'procedure compiled successfully notification, return to your C:\> and run TKPROF.

Note: Tkprof is a tool in oracle for formating trace files to a readable format. To run TKPROF perform the following in C:\>

C:\> TKPROF C:\\udump\_ora_####.trc C:\outputfile.out sys=no explain=username/password

where: [""= Instance Name, "outpufile.out"=Name of your output file for review, "sys=no"=Do not report on sys user SQL statements, "explain=username/password"=Your Oracle Username and password.

Your output file should look something like THIS.

How do I interpret this file?

I'd like to say you can burn it in a cup, add some hot water and drink it down while chanting "oracle, oracle, oracle" but that doesn't work. There are some basic legends in the text files itself, but to understand more, it's best you look it up through or better yet if you're a metalink user, then head on down to Metalink. Alternative option will be to look it up at oraperf. Oracle documentation usually helps so use that.

Alot of people prefer gathering stats through OEM (Oracle Enterprise Manager). I prefer using command prompt cause it gives me the whole Matrix feel, especially if you set your screen colour to black and text to neon green. ;).. I guess it's always best to start from command line cause you grasp the concept better.

Stay tuned for next session on running Statspack!



Post a Comment

<< Home