June 2006

The Specialist
Database News You Can Use
A monthly newsletter for Oracle users

June 2006
From Database Specialists
Expert onsite and remote DBA services
for your Oracle databases
Tel: 415-344-0500 • Toll-Free: 1-888-648-0500

Ask DB Specialists: Automated Statistics Gathering

You’re invited to submit your Oracle-related questions to us at askdbspecialists06@dbspecialists.com. Please include your name and telephone number in your email.

Installation Guide: Oracle 10g on Linux

Continuing with our popular series of Oracle installation guides, Database Specialists’ founder and president Roger Schrag has recently completed his white paper entitled: Installing and Configuring Oracle Database 10g on the Linux Platform.

Roger’s installation guide shares practical, real-life experience that will help “get you up and running as fast as possible, while leveraging best practices in order to set up a scalable, robust database environment that offers high performance.”

The guide thoroughly covers the four key steps to getting Oracle up and running on your server. They include:

  1. Prepare the server
  2. Install the Oracle software and latest patch set
  3. Create a database
  4. Complete the server configuration

This white paper is the first listing on our presentations/resources page, where you’ll find lots of other resources and installation guides: http://dbspecialists.wpengine.com/presentations.html.

Rescuing Deleted Files

While this item is not specific to Oracle technology, we found a resource recently that could be a life-saver for some folks. Have you ever accidentally deleted a file before you had a chance to back it up? Perhaps you used a wildcard in an rm command, and it matched an important file that you had no intention of deleting?

If you work in a Linux environment, then you might want to check out http://wiki.yak.net/592?size=L. This posting includes a simple TCL script that you can run in order to search the unallocated blocks on a filesystem for text that matches a certain string of text that was present in the file you accidentally deleted.

The script copies blocks containing matches into rescue files. You can look at these rescue files and, hopefully, find the code or data that you accidentally deleted. Hopefully you will never need this information. But, if you’ve just lost four hours of programming effort because you cleaned out the wrong directory, you might just find this resource to be a lifesaver!

Oracle Developer Tips

If you’re an Oracle developer looking for tips, check out the library on the TechRepublic website. Here is a sampling of topics covered, mostly written by Scott Stephen.

  • Understand how NULLs affect IN and EXISTS
  • Understand the difference between star and snowflake schemas in OLAP
  • Use the correct looping control in PL/SQL
  • Enable PHP support on Oracle’s default Apache server

You’ll find the library, and more resources, at http://builder.com.com/1200-6388-5212915.html.

 

This month’s question comes to us from Somnath in India: I have a question regarding the Analyze functionality of the DBMS_STATS package. In my case I have a table which I analyzed some days ago, and today when I opened the table statistic information, I see the last_analyzed column has today’s date. My question is whether DBMS_STATS gathers statistics for a table each day after it is called on that table?

Terry Sutton of the Database Specialists team responds: The DBMS_STATS package contains several procedures which can be used to gather optimizer statistics on your data, including GATHER_TABLE_STATS, GATHER_INDEX_STATS, GATHER_SCHEMA_STATS, and GATHER_DATABASE_STATS. When you call one of these procedures, DBMS_STATS will gather the statistics only once. However, you can set up a dbms_job or dbms_scheduler entry in order to call DBMS_STATS at regular intervals such as daily or weekly.

If you’re using Oracle 10g, then you don’t need to regularly gather optimizer statistics on your data because Oracle 10g does this automatically. When you create a new Oracle 10g database, Oracle automatically sets up a job in the dbms_scheduler job queue to call the DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC procedure on a nightly basis, beginning at 10:00 pm. This procedure gathers statistics on objects which have no previously gathered statistics or whose statistics are considered stale because the object has been modified significantly (more than 10% of the rows have changed).

So if the last_analyzed column of DBA_TABLES indicates that optimizer statistics were gathered for a table today even though you last gathered statistics manually some days ago, it is likely that you’re experiencing the result of a scheduled statistics gathering job.

You may find that you aren’t satisfied with the statistics gathering done by the GATHER_DATABASE_STATS_JOB_PROC procedure call. In this case you can disable the job in the dbms_scheduler queue and create jobs of your own which call the DBMS_STATS procedures to gather statistics in the manner you desire. I’ve recently written a white paper called, What’s Up with dbms_stats? to describe some of the options for these procedures and their performance and effectiveness. This paper can be downloaded at http://dbspecialists.wpengine.com/presentations/dbms_stats.html.

Leave a Reply

Your email address will not be published. Required fields are marked *