Finding the Difference in Table Stats

In my previous post I have explained how to capture different statistics from the same table into temporary stattab’s for further analysis (Or to provide to support). In 10.2.0.4 and 11g, dbms_stats has some procedures that can help you get a report on the difference between the two sets of statistics.

DBMS_STATS.DIFF_TABLE_STATS_IN_STATTAB is one such procedure. Continuing from my last example where we created two separate stats tables stattab_old and stattab_new, you can now check the difference  between the two sets of stats by running the following sql statement

set long 500000 longchunksize 500000

select report, maxdiffpct from
table(DBMS_STATS.DIFF_TABLE_STATS_IN_STATTAB(null,’DEPT’,’STATTAB_OLD’,’STATTAB_NEW’));

You will see an output similar to the one below (The stats are not really what you will see on a dept table, but you get the general idea)

###############################################################################

STATISTICS DIFFERENCE REPORT FOR:
.................................

TABLE	      : DEPT
OWNER	      : SCOTT
SOURCE A      : User statistics table STATTAB_OLD
	      : Statid	   :
	      : Owner	   : SCOTT
SOURCE B      : User statistics table STATTAB_NEW
	      : Statid	   :
	      : Owner	   : SCOTT
PCTTHRESHOLD  : 10
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

NO DIFFERENCE IN TABLE / (SUB)PARTITION STATISTICS
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

COLUMN STATISTICS DIFFERENCE:
.............................

COLUMN_NAME	SRC NDV     DENSITY    HIST NULLS   LEN  MIN   MAX   SAMPSIZ
...............................................................................

DEPTNO		A   1	    .000000203 YES  0	    3	 C105  C105  9363
		B   9	    .000000341 YES  0	    3	 C102  C10A  1465265
DNAME		A   21	    .000000203 YES  0	    6	 C4032 C4032 9363
		B   315     .003967048 YES  0	    6	 C4032 C4032 1465265
LOC		A   25110   .000039824 NO   0	    5	 C2061 C32D1 47114
		B   161368  .000006197 NO   0	    5	 C114  C33C3 1465265
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

NO DIFFERENCE IN INDEX / (SUB)PARTITION STATISTICS
###############################################################################

NDV – Number of distinct values

Density – 1/NDV

11g range partition addition script in perl

Recently I wrote a perl script that adds partitions to an existing table
The scripts makes a few assumptions
– The table exists and is range partitioned on date
– There is one partition per day
– There is a local (pre-fixed or non pre-fixed) index on the table
– There is a partition (named tableshortname_default) that is the upperbound of the date range (This can be split into the partitions that we need).
– The partition names are of the format tableshortname_mmddyyyy

The script takes the following arguments
– The table full name
– The table short name (Which becomes the prefix for the partition names)
– The number of partitions to create

The script does the following
– Queries the data dictionary (user_tab_partitions) to find out the highest partition in the table (ie the highest date for which the partition has been created)
– Uses the perl function Add_Delta_Days (Which is the part of the perl library Date::Calc) to generate the future dates for which partitions need to be created.
– Generates partition creation scripts and executes them.
– When you create a table partition, oracle automatically creates the new local index partition too.

The script has been tested against an oracle 11g database.

#!/usr/bin/perl
#There are two arguments to this script
#The first argument is the tablename
#The second argument is the tableshortname used for prefixing the partition names
#The third argument is the number of days for which you want to create new partitions

#This program
#   Assumes that the table is partitioned on date with one partition per day
#   Assumes that the highest partition is the one defined with maxvalues
#   Assumes that the maxvalue partition is called tableshortname_default
#   Takes the tablename
#   Queries the data dictionary to find the partition that has the highest date
#   Creates alter table split partition statements to create the additional partitions (determined by the 3rd argument)
#When the table is split the local index automatically gets split
#But you will have to rename the index partition(So that it gets a good prefix) (The code for that has to be added to this script)

use DBI;
use Date::Calc qw(Add_Delta_Days);

if ( @ARGV < 3 )
{
print “Syntax : crpartitions.pl tablename tableshortname numberofpartitions \n”;
exit;
}

# Connect to the database
# LongReadLen and LongTruncOk are required to specify the buffer size
# For the high_value column from user_tab_partitions which is a long datatype

my $db = DBI->connect( “dbi:Oracle:OracleSid”, “username”, “password” )
|| die( $DBI::errstr . “\n” );
$db->{AutoCommit}    = 0;
$db->{RaiseError}    = 1;
$db->{ora_check_sql} = 0;
$db->{RowCacheSize}  = 16;
$db->{LongReadLen}   = 5242880;
$db->{LongTruncOk}   = 0;

#Setup tablenames for target table
#Setup a tablename and tableshortname in upper case, to be used in queries

my $l_tablename=shift;
my $l_tableshortname=shift;
my $l_numpartitions=shift;

my $l_tablename_uc=uc($l_tablename);
my $l_tableshortname_uc=uc($l_tableshortname);

# Query user_tab_partitions to determine the highest partition
# And the date that sets the limit for rows that are in that partition

$l_highvalue = $db->selectrow_array(”
select high_value from user_tab_partitions
where table_name = ‘$l_tablename_uc’ and
partition_position = (select max(partition_position)-1 from user_tab_partitions
where table_name = ‘$l_tablename_uc’)
“);

# The output of the query is a string
# eg: TO_DATE(‘ 2008-11-16 00:00:00’, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIA
# Parse the string to get the full date, year, month and day

$l_highdate = substr $l_highvalue,10,19;
$l_year     = substr $l_highdate,0,4;
$l_month    = substr $l_highdate,5,2;
$l_day      = substr $l_highdate,8,2;

# Loop through each new partition you need to create
# Use the Add_Delta_Days function from the Date::Calc perl library
# To add days to the start date to get the upper limit of dates that go into each partition
# Prepare the alter table split partition statement and then invoke the function createPartition
# to execute the statement

for ($i=1 ; $i <= $l_numpartitions; $i++) {
($n_year,$n_month,$n_day)=Add_Delta_Days($l_year,$l_month,$l_day,$i);

$l_alterstmt=”alter table “.$l_tablename_uc.” split partition “.$l_tableshortname_uc.”_default at (TO_DATE(‘”.$n_day.”-“.$n_month.”-“.$n_year.”‘, ‘DD-MM-YYYY’)) into
(PARTITION “.$l_tableshortname_uc.”_”.$n_month.$n_day.$n_year.”,partition “.$l_tableshortname_uc.”_default)”;

&createPartition($db,$l_alterstmt);

}

# Takes the statement passed in as argument and executes it
sub createPartition {

$ldb = shift;
$p_alterstmt=shift;
# execute alter table statement
my $sthd = $ldb->prepare(“$p_alterstmt”)
|| die(“could not prepare alter statement”. $sthd->errstr . “\n” );

$sthd->execute()
|| die(“could not execute drop statement”. $sthd->errstr . “\n” );

}

Installing Grid control 10.2.0.4 with oracle database 10.2.0.4 for the repository

Below is what i think is the quickest (And possibly the only supported way to do this directly (instead of installing 10.2.0.2, finishing the grid control install and upgrading to 10.2.0.4))set of steps to install grid control 10.2.0.4, while using oracle database version 10.2.0.4 for the grid control management repository on Linux x86.

Typically you would install grid control 10.2.0.1, which installs a 10.1.0.4 database as the management repository, then you would upgrade enterprise manager to 10.2.0.4 and then eventually upgrade the database to 10.2.0.4. Apart from the numerous steps in this method you also take more time because the entreprise manager database is fully configured when you install 10.2.0.1 and then it takes even more time updating all this configuration when you install the 10.2.0.4 patch. In the method outlined below all the configuration is done when you run the configuration script in the end, saving you precious time.

  • Install Oracle RDBMS 10.2.0.1
  • Upgrade Oracle RDBMS to 10.2.0.4
  • Create a listener
  • Create a Database
    • When you create/configure the database make sure that, you DO NOT choose the option to enable database control for the instance. (If you choose to enable database control, you will have to de-configure it).
  • Configure the Initialization parameters
  • Install Oracle Grid Control 10.2.0.1, software only.
    • Install Oracle Grid Control 10.2.0.1, software only, Subsection titled ” Enterprise Manager Grid Control Using an Existing Database“.
    • Here be extremely careful when you are configuring the em_using_existing_db.rsp file. When you are setting the value for the parameter s_reposPort=”1521″, remember to use the double quotes around the 1521 or else you will have issues with the installation.
  • Install Oracle Grid Control 10.2.0.4, software only.
    • In the section of the documentation you were following above, follow instructions in “Step 7 Apply the 10.2.0.4 patch set to OMS
  • Apply the 10.2.0.4 patch set to the Agent on the management server
    • Follow instructions in “Step 9, “Apply the 10.2.0.4 patch set to Agent
  • Run the configuration scripts to configure grid control to use the already created 10.2.0.4 database.
    • Follow instructions in “Step 10, 11 Configure Enterprise Manager Grid control by running the ConfigureGc.pl….

Instructions on how to upgrade this installation to Enterprise Manager 10.2.0.5 can be found in my followup post , Upgrading Enterprise Manager to 10.2.0.5.

Some Useful Metalink Notes for Grid control Installation and Upgrades

Documentation Reference for 10.2.0.5 Install and Upgrades

Installing Enterprise Manager 10.2.0.4 using an 11g Database for the repository

Steps to upgrade 10.2.0.2 or higher Repository to 11g

Install grid control 10.2.0.5 on enterprise linux 4

Install grid control 10.2.0.5 on enterprise linux 5

You can find samples of the rsp files below

em_using_existing_db.rsp

patchsetoms.rsp

Installing Ruby and Rails 2 on Ubuntu Lucid running Oracle 11g

Updated this post from Ubuntu Jaunty to Lucid.

Unless you have been living on one of the planets orbiting alpha centauri (or if you have nothing to do with computers, in which case you would not be reading this post), you must have heard of ruby on rails.

Below are the steps i went through to get ruby and rails installed and configured on a Ubuntu Jaunty (10.04) system.

Make sure that you have installed Oracle 11g or the Oracle 11g instant client on the Ubuntu server, before you start.

Get the ruby packages for ubuntu

sudo apt-get install ruby  ruby-dev  libopenssl-ruby1.8 irb ri rdoc

sudo apt-get install sqlite3

sudo apt-get install libsqlite3-ruby libsqlite3-dev

Download, Install setup Gems

wget  http://production.cf.rubygems.org/rubygems/rubygems-1.3.6.zip
unzip rubygems-1.3.6.zip
cd rubygems-1.3.6
sudo ruby setup.rb

sudo ln -s /usr/bin/gem1.8 /usr/local/bin/gem

Install Rails 2

sudo gem install rails

Download and install the ruby interface for oracle

Download ruby-oci8 from http://rubyforge.org/frs/?group_id=256

http://rubyforge.org/frs/download.php/69454/ruby-oci8-2.0.4.tar.gz

tar -xzvf ruby-oci8-2.0.4.tar.gz

cd ruby-oci8-2.0.4/

make

sudo make install

Get the Enhanced ActiveRecord adapter for oracle

wget http://rubyforge.org/frs/download.php/64456/activerecord-oracle_enhanced-adapter-1.2.2.gem

sudo gem install activerecord-oracle_enhanced-adapter-1.2.2.gem

You are all set to write and test your first ruby program using the rails framework .

Please see my followup post on writing a simple rails program with 11g