Monthly Archives: August 2008

10g & 11g Sql tuning sets

Sql tuning sets is a basic building block for many oracle tuning features, built into the oracle 10g and 11g databases. A sql tuning set captures one or more sql statements issued by database users and their corresponding execution statistics (think, some of the statistics available in v$sqlarea),execution plans and row source statistics (think, some of the information from v$sql_plan etc), and execution context (think, bind varaibles etc…).

Sql tuning sets are used as inputs for the “Sql tuning advisor”. Sql tuning sets can be moved between databases. This comes in handy when you want to tune sql statements captured from a production environment in a development environment (So that you do not do development activities on production). sql tuning sets are used by the ‘sql performance analyzer’ component of ‘Real Application Testing’ and also the ‘sql plan stability’ feature.

Sql tuning sets can be created from the following four sources
– The database cursor cache (sqlarea)
– AWR reports
– An existing sql tuning set
– A sql trace file (generated using the 10046 database event)

Typically You would perform the following operations on a sql tuning set.
– Create a sql tuning set
– Add sql statements to the sql tuning set
– List the contents of a sql tuning set
– Modify a sql tuning set
– Transport a sql tuning set
– Drop a sql tuning set

Creating a sql tuning set

You can use the dbms_sqltune.create_sqlset procedure to create a new sql tuning set.
It takes 3 arguments.

DBMS_SQLTUNE.CREATE_SQLSET (
sqlset_name IN VARCHAR2,
description IN VARCHAR2 := NULL
sqlset_owner IN VARCHAR2 := NULL);

typically you can just specify the sqlset_name and a description, that lets you know why you created the sql tuning set in the first place.

Now i went ahead and used swingbench to run some transactions on the database for 10 minutes. This generates numerous sql statements in the shared pool.

Adding sql statements to a sql tuning set

Once we have run a load on the database, there will be sql statements in the shared pool that we want to analyze.
You can use the dbms_sqltune.load_sqlset procedure to popluate the sql tuning set with sql statements.

DBMS_SQLTUNE.LOAD_SQLSET (
sqlset_name IN VARCHAR2,
populate_cursor IN sqlset_cursor,
load_option IN VARCHAR2 := ‘INSERT’,
update_option IN VARCHAR2 := ‘REPLACE’,
update_condition IN VARCHAR2 := NULL,
update_attributes IN VARCHAR2 := NULL,
ignore_null IN BOOLEAN := TRUE,
commit_rows IN POSITIVE := NULL,
sqlset_owner IN VARCHAR2 := NULL);

For example you might want to create a sql tuning set with sql tuning statements that executed more than 5 times and had buffer_gets greater than 100000

So as you can see we used dbms_sqltune.select_cursor_cache to find the high resource using sql statements from the shared pool. So you have the following four different methods to get sql statements and populate the sql tuning set.

dbms_sqltune.select_cursor_cache
dbms_sqltune.select_sql_trace
dbms_sqltune.select_sql_set
dbms_sqltune.select_sql_workload_repository

All the above procedures return a table of object type sqlset_row

List the contents of a sql tuning set

You can list the statements that have been included in the sql tuning set by invoking the dbms_sqltune.select_sqlset procedure

Modify sql tuning sets

You can delete statements that have been included in the sql tuning set by invoking dbms_sqltune.delete_sqlset

You can update attributes of the sql tuning set by invoking dbms_sqltune.update_sqlset

Transporting a sql tuning set

If you need to move the sql tuning set from one database to another you can follow the procedure below

First create the sql tuning set staging table (This will be table where we temporarily store the information regarding the sql tuning set and then use exp/imp to move it to a different database).

dbms_sqltune.create_stgtab_sqlset
BEGIN
DBMS_SQLTUNE.CREATE_STGTAB_SQLSET( table_name => ‘my_first_stage_tab’ );
END;
/

Copy the sql tuning set into the staging table

dbms_sqltune.pack_stgtab_sqlset
BEGIN
DBMS_SQLTUNE.PACK_STGTAB_SQLSET(
sqlset_name => ‘my_first_sqlset’,
staging_table_name => ‘my_first_stage_tab’);
END;
/

Now you can exp/imp this staging table from production to dev or test.
Once the staging table is in the destination database, you have to move the sql tuning set from the staging table into the data dictionary.

dbms_sqltune.unpack_stgtab_sqlset

BEGIN
DBMS_SQLTUNE.UNPACK_STGTAB_SQLSET(
sqlset_name => ‘%’,
replace => TRUE,
staging_table_name => ‘my_first_stage_tab’);
END;
/

You can also use enterprise manager to perform all the above operations.

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

11g Snapshot Standby

Oracle 11g provides customers the “Snapshot Standby” database feature, so that customers can leverage their investments in standby database systems. When leveraging the “Snapshot Standby” feature, customers can temporarily open up the standby database and run their tests on this system. For example, customer might want to test an application upgrade that alters tables or insert/update/deletes data.

When you convert a physical standby database to a snapshot standby in 11g, the database automatically creates a guaranteed restore point (Obviously “Flashback Database” has to be enabled in order to do this). Once you are done with your testing, the database gets automatically flashed back to this guaranteed restore point and then the log apply starts again.

Point to note with the snapshot standby database is that, in 11g the log transport continues to work. So all the logs are being received on the standby database and gap resolution continues to work (So in case of a primary failure, the logs just need to get applied on the standby). In 10g you could open up the standby database and test on it, but the log transport used to stop.

Let us look at some of the steps involved in setting up and using a “Snapshot standby” database in 11g. For similar functionality with 10g refer to the following documents.

Using 10g Physical standby database for Read/Write Testing and Reporting

10g Snapshot Standby – Dell Case Study

First follow all the normal steps that you would in setting up a physical standby database.
Make sure that you enable “Flashback Database” on both the primary and the standby.
Make sure that log shipping and apply are working properly.
In our example RK01 is the primary database and RK01DG is the standby database.

Check the salary for empno 7934

Update the emp table on the primary database
Switch the logfile to ensure that the change gets propagated and applied on the standby database.

SQL> Update scott.emp set sal=1310 where empno=7934;
SQL> Commit;
SQL> connect / as sysdba
SQL> alter system switch logfile;

Login using sqlplus to the standby database RK01DG

Check the role of this standby database

Use the command “alter database convert to snapshot standby” to try and switch the database to a “Snapshot Standby”

Since the database is in managed recovery mode you get an error.
Cancel the managed recovery
Then convert the database using the “alter database convert to snapshot standby”
This command leaves the standby database in nomount mode.
Shutdown the database and start it back up (In my screen shot i am starting up in mount mode, you can start it all the way up)

Check the role of the standby database, it should say “Snapshot Standby”
Query v$restore_point to see the details of the guaranteed restore point that it created

List the columns in the employee table on the primary database (RK01). This is just for reference, because as part of our testing we are going to add columns to this table.

Login as the scott user to the standby database and run some transactions

Now that testing is over, we can convert this back to the physical standby database.
Issue the “alter database to convert to physical standby” command to convert this from a “Snapshot Standby” to a “Physical standby” database.

Since the database is in open mode, it complains.
Restart the database in mount mode and then issue the same convert command again

Once the command succeeds the database has to be restarted.
Startup the database in mount mode and put the database in managed recovery mode.
Check the role of the standby database. It should now say “physical standby”.

Run transactions on primary to make sure that log transport and apply are working

Put the standby database in read only mode and check if the transactions got applied on the standby

Also make sure that the changes made during testing have been rolled back.

Those are the basic steps to setup and test a “Snapshot Standby” database.

11g Active Dataguard

Oracle introduced the Active Dataguard option in 11g, to allow customers to have read-only access to the physical standby databases. This option gives customers the flexibility to offload resource intensive queries to the physical standby database, while the log shipping and apply continues its work.

In oracle 10g in order to run queries on database tables, in a physical standby database, one had to stop the log apply and open the database in read-only mode. This means that the log files are being queued up during that period and will have to be applied once the database is reverted to the managed recovery mode.

The steps to follow in order to setup Active dataguard are exactly the same as setting up a physical standby database (Creating a Physical Standby). Once the physical standby database is configured and log transport and log apply are working correctly, you can just do an “alter database open” to enable active dataguard.

Let us look at an example.

I have a primary database RK01 and a regualar physical standby database RK01DG.

If you try to query the table emp while the database is in managed recovery mode you will get an error

Make sure that you can update the table emp from the primary database.

In order to enable Active Dataguard, ie enable the ability to query the database while log apply is in progress. Issue the following commands

The main concept to note here is that you are doing a “alter database open” (As opposed to an “alter database open read only”).

Update the table emp again to check if the values you are updating to on the primary are query able on the standby (ie log apply is continuing)

Check the updated value on the standby

Try updating the emp table on the standby database (It should only allow you to query , not update)

The procedure’s to switchover and failover remain exactly the same as before (Irrespective of whether you are in active dataguard mode or not).