Skip navigation.

DBA Blogs

Oracle EMEA Partner Community Forums - April 2014, Prague, Czech Republic

We are delighted to invite you to the 2014 editions of the Exadata, Exalogic & Manageability and of the Servers and Storage Partner Community Forums for EMEA partners, which will take place...

We share our skills to maximize your revenue!
Categories: DBA Blogs

Speaking at RMOUG TD 2014

DBASolved - Tue, 2014-02-04 14:22


Let the conference season begin! 

I will be presenting at RMOUG Training Days 2014 this month in Denver, CO.   The presentation that I will be presenting is a revised version of my OOW 2014 presentation on Oracle Enterprise Manager 12c, Database 12c and You!  This presentation will cover a few basics of Database 12c and how Oracle Enterprise Manager 12c can be used to monitor a Database 12c environment.

Being that this is my first RMOUG conference, I’m looking forward to an exciting 3 days of presentations and networking.   This is going to be fun!


twitter: @dbasolved


Filed under: General
Categories: DBA Blogs

Puppet and MySQL: A How-To Guide

Pythian Group - Tue, 2014-02-04 12:24

Today’s blog post will include a how-to guide on setting up Puppet Master and Agents for streamlined server management. First off you’ll have to configure each of your puppet agent nodes (i.e. clients.)

Get the latest repo from Puppet Labs for your distro — These will have to be added both to the puppet master and to the puppet agent nodes.

Log in to each node and install/configure the puppet agent:

apt-get install puppet #[or yum install puppet for RHEL derivatives]

Edit your /etc/puppet/puppet.conf and add your puppet master servername under the [main] section:

server= # e.g. dev-laptop in my case

Also, edit your /etc/default/puppet file and set START to yes:

# Start puppet on boot?

Now restart the puppet agent service on all nodes and enable the puppet service:

service puppet restart
puppet resource service puppet ensure=running enable=true

After running the above commands on each of your agent nodes, it’s now time to hop over onto your puppet master server node and configure your puppet master:

apt-get install puppetmaster #[or yum install puppet-server for RHEL]

sudo puppet resource service puppetmaster ensure=running enable=true

By running the following command you should now see the signed certificate requests made by your puppet agents:

sudo puppet cert list
“deb-box-1.lan” (09:00:F5:B9:CF:D0:E7:BF:C5:D3:8B:74:BC:3E:87:E2)
“deb-box-2.lan” (AC:B5:D0:BD:CF:AC:C8:9C:83:21:86:09:40:D3:ED:1E)
“deb-box-3.lan” (67:4E:81:48:18:73:47:16:2F:2C:3D:31:4D:4D:80:F8)

You will have to certify each request in order to enable the agent:

puppet cert sign “deb-box-1.lan”
notice: Signed certificate request for deb-box-1.lan
notice: Removing file Puppet::SSL::CertificateRequest deb-box-1.lan at ‘/var/lib/puppet/ssl/ca/requests/deb-box-1.lan.pem’

puppet cert sign “deb-box-2.lan”
notice: Signed certificate request for deb-box-2.lan
notice: Removing file Puppet::SSL::CertificateRequest deb-box-2.lan at ‘/var/lib/

puppet cert sign “deb-box-3.lan”
notice: Signed certificate request for deb-box-3.lan
notice: Removing file Puppet::SSL::CertificateRequest deb-box-3.lan at ‘/var/lib/puppet/ssl/ca/requests/deb-box-3.lan.pem’

Finally, test the client puppet agent by running:

puppet agent –test

Now you are ready to create/install modules to manage various server components. Activities such as managing your MySQL databases can be performed by installing the puppetlabs/mysql module as follows:

puppet module install puppetlabs/mysql

Your module has been added! From here it is as simple as adding your requirements to your “site.pp” file in “/etc/puppet/manifests” e.g. (1.) to ensure mysql-server is installed on the node <serverbox.lan> with (2.) a root password = “root_pass_string” & (3.) max_connections = “1024″ the following configuration will suffice (all options are configurable, just specify the section you would like modified & the variable as in the example ~ for more info):

# vi /etc/puppet/manifests/site.pp

node ‘deb-box-1.lan’ {
class { ‘::mysql::server’:
root_password => ‘root_pass_string’,
override_options => { ‘mysqld’ => { ‘max_connections’ => ’1024′ } }

node ‘deb-box-2.lan’ {
class { ‘::mysql::server’:
root_password => ‘root_pass_string’,
override_options => { ‘mysqld’ => { ‘max_connections’ => ’1024′ } }

node ‘deb-box-3.lan’ {
class { ‘::mysql::server’:
root_password => ‘root_pass_string’,
override_options => { ‘mysqld’ => { ‘max_connections’ => ’1024′ } }

Since site.pp is configured run the following command on the puppet agent node to update the configuration:

puppet agent -t

Voilà! Your MySQL server is now under new management =)

Optionally you can also install puppet dashboard by performing the following steps:

apt-get install puppet-dashboard # RHEL # yum install puppet-dashboard

Edit your database.yml (using standard YAML notation) adding the dashboard database name, username and password – it is recommended to use the same database for production/development/testing environments:

cd /usr/share/puppet-dashboard/config;
vi database.yml # add the database name, username & password you intend to setup in the database (see the following step for details)

Connect to your mysql instance (hopefully installed already) and create the puppet-dashboard database & users:


CREATE USER ‘dashboard’@'localhost’ IDENTIFIED BY ‘my_password’;

GRANT ALL PRIVILEGES ON dashboard.* TO ‘dashboard’@'localhost’;

Now time to populate the database using rake by running:

rake RAILS_ENV=production db:migrate

rake db:migrate db:test:prepare

Your dashboard is now setup – you can run it with the builtin webserver WEBrick:

sudo -u puppet-dashboard ./script/server -e production

Here are some useful Puppet commands that will come in handy:

# Apply changes with noop
puppet apply –noop /etc/puppet/modules/testit/tests/init.pp

# Full apply changes
puppet apply /etc/puppet/modules/testit/tests/init.pp

# Print a puppet agent’s full configuration
puppet –configprint all

Categories: DBA Blogs

Merge Replication Identity Range Management – An Identity Disorder? Part 2

Pythian Group - Tue, 2014-02-04 11:58

In Part 1 of this series, we explored Merge Replication Identity Range Management of tables with identity columns. We outlined how important it is to select the right datatype for identity columns and assign suitable ranges for your identity to reduce the overhead of allocating new ranges frequently.

There are some cases that can affect the range management due to the changes they make to the identity values allocation , this includes batches and transactions!
The case of batches and transactions on publisher

I had worked recently with a case where the client’s apps fail intermittently with this error:

The insert failed. It conflicted with an identity range check constraint in database %, replicated table %, column %. If the identity column is automatically managed by replication, update the range as follows: for the Publisher, execute sp_adjustpublisheridentityrange; for the Subscriber, run the Distribution Agent or the Merge Agent.

The client just followed the advice and executed sp_adjustpublisheridentityrange to assign a new range on publisher but that was not convenient. The user was a db_owner so it should assign a new range by itself, but the error kept appearing.

Initially, as a best practice, we advised changing the range to something larger in order to accommodate new rows — but this did not work. We began looking at processes inserting the data, and we found few stored procedures that insert single rows — nothing abnormal.

It wasn’t until we ran a trace to capture the load for few hours — we found a job running SSIS package on another instance. The ETL SSIS package calls a stored procedure on publisher to load data from another system and dumps thousands of rows inside a transaction.

Worse, the job never failed because the SSIS package used conditional constraints to suppress the stored procedure failure through conditional constraints, it also did not include logic to email anyone if the SP step failed.

The code was something like this, logic wise:

SELECT DISTINCT rows    INTO   #temp
    from staging_instance.staging_database.dbo.table


INSERT INTO dbo.table1 (col1, col2,....., colZ)
SELECT *	FROM   #temp

INSERT INTO dbo.table1_details
(col1, col2,....., colZ)

SELECT t.* , stag.col1, stag.col2
 From table1 t1 join  #temp t

on =

and t.date_ < gedate() -2

join stag

on t.Detail_id = stag.detail_id

Insert into tracking  select ‘Complete_ETL’, getdate() , @@rowcount

Commit Tran1

The problem here became obvious: Triggers fire once per statement, not once per row. It is possible to create triggers to cope with multiple rows using INSERTED but a replication trigger is not the place we want to mess with! Let us test this on our existing simple schema , please refer to Part 1 of this series for the schema. We will insert 400 rows into a temp table and insert them at once to the published table.

use pub

Create table #tmp (col2 datetime)
Insert into #tmp (col2) select getdate();
Go 400

select count(*) from #tmp

Let’s record the identity value of the table on publisher

Dbcc checkident (tbl_pub , noreseed)

Checking identity information: current identity value ’4602′

The primary range is 4601-4701 & the secondary is 4701-4801 so adding 400 rows should override this

Insert into pub.dbo.tbl_pub (col2) select * from #tmp

Msg 548, Level 16, State 2, Line 1
The insert failed. It conflicted with an identity range check constraint in database ‘Pub’, replicated table ‘dbo.tbl_pub’, column ‘col1′. If the identity column is automatically managed by replication, update the range as follows: for the Publisher, execute sp_adjustpublisheridentityrange; for the Subscriber, run the Distribution Agent or the Merge Agent.

Dbcc checkident (tbl_pub , noreseed)

Checking identity information: current identity value ’4802′

The current identity pushed to the limit but no rows were inserted.

Here is what happened:

  1. SQL Server began inserting the data and assigning new Identity values; since the trigger fires per statement, it will not fire until the batch completes.
  2. Because the batch has more rows than the available free Identity slots (in this case, fewer than 200) it fails because the constraint is violated. However, the next identity value is already greater than the max value defined by the constraint.
  3. Because the batch failed, the trigger never fired and never allocated a new range.
  4. Any subsequent processes, even if ONE single row, will fail because the next available Identity slot, 4802, is greater than the limit of the secondary range (4801). This is when the applications fail and return error.

With single row operations , the trigger will fire and assign new ranges if both primary and secondary ranges were exhausted and the new ranges will be ready for future operations.
Temporary resolution was following the advice of the error message and run sp_adjustpublisheridentityrange. The disadvantage however, is that it creates some gaps in the identity column because the insert rolled back and never used the Identity slots. Kindly note that rolling back a transaction does NOT set identity Seed to the value before the transaction start.

If we want to reduce the gap then we can reseed the column to a value close to the most recent inserted value within the range.

select max(col1) +5 [reseed] from pub.dbo.tbl_pub where col1 between

from Distribution..MSmerge_identity_range_allocations
where publication='pub1' and article= 'tbl_pub' and subscriber_db='Pub' and subscriber = 'MYINSTANCE')


max(next_range_end) from Distribution..MSmerge_identity_range_allocations
where publication='pub1' and article= 'tbl_pub' and subscriber_db='Pub' and subscriber = 'MYINSTANCE');

Both are manual workarounds, and this was not desired.

Few workarounds can work here, they all evolve around ensuring that the triggers fire to allocate new ranges including

  1. Increase the range assuming that a larger range can accommodate larger batches. Well,not quite true since the batches have varied sizes and user processes can reduce available identity slots to a number less than the batch rows count.
  2. Always assign new ranges when batches start. This creates gaps.
  3. Add some logic in the code that will check for the available Identity range VS batch row counts and insert if there is enough slots or assign new ranges if the rows count are more. We tried this but sometimes failed because some user processes added rows, exhausting range mid-way. It also created big gaps sometimes>If Current ID in the primary range then check the available ID count
    >>>> If the Available empty IDs > batch count then proceed
    >>>>>>>>>> If Available empty IDs If Current ID in the Secondary range then check the available ID count
    >>>> If the Available empty IDs > batch count then proceed
    >>>>>>>>>> If Available empty IDs < batch count then run sp_adjustpublisheridentityrange to assign new ranges
  4. Insert everything as single rows. This works, but it’s very slow sometimes.

We ended up having a “Hybrid” approach :

  • Batch insert if the range permits, much faster
  • Row by row if the range does NOT accommodate all the rows. We could have stretched it a bit more by batch inserting till the end of the range and insert the rest using row-by-row but that’s complicating it more.
Create table #temp
        SNo INT IDENTITY(1,1) ,
		CARTONID	nvarchar(40),
		STOREID	int,
		STORECODE	nvarchar(20),

    -- insert the rows required into the temp table.
    SELECT DISTINCT rows    INTO   #temp
    from staging_instance.staging_database.dbo.table

    select @count= COUNT(*) +500 from #temp --five hundered rows in case user processes add rows in the meantime

	declare @range_begin bigint,@range_end bigint,@next_range_begin bigint,@next_range_end bigint ,
	@current bigint

	select  top 1
	@range_begin =range_begin,@range_end =range_end,@next_range_begin =next_range_begin,
	where article = 'table1' and subscriber = 'Publisher'
	order by range_begin desc

	select @current=IDENT_CURRENT('dbo.table1')

    set @i=1


    If (@current< @range_end and @count < (@range_end - @current) ) or (@current > @next_range_begin AND @count <  (@next_range_end - @current) )
--Batch Insert
				FROM   #temp

If (@current< @range_end and @count > (@range_end - @current) )  or (@current > @next_range_begin AND @count >  (@next_range_end - @current) )
    WHILE (@i <= @count)

				FROM   #temp
		where ID = @i
		SELECT @i = @i + 1



Besides the recommendations in Part 1 of this series, the lesson we learned from that engagement was basic : You need to understand your environment! If you have transactions or batches that run against tables with identity columns engaged in Merge replication, then you want to ensure that the batch rows can fit within the available ranges.

Categories: DBA Blogs

Hotsos 2014 is just around the corner

Grumpy old DBA - Tue, 2014-02-04 08:51
Last year I arrived in Dallas on sunday and it was warm.  Running on monday it was in the hot range like 80+ degrees.  I was out there in shorts and tank top and there are these local runners with full set of equipment jackets and everything.  Wow I guess I was dying in the heat and these people still think it is cold?

Getting pretty excited about presenting again but time to start looking at and reviewing my presentation.  I vowed a while back to NOT add in any more slides but ... well ... there is one thing I discovered recently about how to view bind variable values for statements that are executing and are getting monitored by the Oracle real time monitoring capability ( which kicks in for sql statements executing 5 seconds or so ).  It not exactly directly related to "shared pool monitoring" but it kind of related to custom monitoring ... so darn I think it is going in there.

Need to practice my presentation in front of some of my local gang here also and take some comments from the peanut gallery.  Hope to see you down in Dallas!
Categories: DBA Blogs

SQL Server versions – Should you upgrade to the next release?

Pythian Group - Tue, 2014-02-04 00:58

Today there are five versions of SQL Servers serving at the hearts of business enterprises, and the sixth is coming soon. Current SQL Server versions are 2000, 2005, 2008, 2008R2, 2012 and the upcoming version SQL Server 2014. The question CIOs, VPs of IT and/or IT managers are asking themselves is, “Shall I upgrade SQL Server boxes to the next release, or not?”

The answer is seemingly easy — yes. You need to upgrade to the next version to take advantage of more features, reduced total cost of ownership, more stability, more scalability, less bugs, etc. However, there are major challenges that are obligating IT Managers to count to 10 before thinking of upgrade.

A study conducted by RockSolidSQL suggests that the majority of 60% of installations-base are using SQL Server 2008/2008R2. Around 27% are using SQL Server 2005, 11% are using SQL Server 2000, and a humble percentage of 2% only started to use SQL Server 2012 recently.

SQL Server Version Usage

While there is an evidence of rapid adoption and migration to the newest SQL Server 2012 within the previous period, especially among new installations and development projects, we still witness kind of adverseness to migrate to newer versions among SQL Server 2000/2005 installations, which amounts to a total of 38% installations when combined according to the earlier source.

The reluctance of upgrading to SQL Server 2008/2008 R2 or later editions, though nine years have passed since the release of SQL Server 2005, and 14 years since the release of SQL Server 2000, might be attributed – in our opinion – to two main reasons:

  • A financial aspect: we all remember the financial crisis that has emerged at the end of 2008 and has severely hit most business enterprises on the planet and till today, the US and world economies are not fully recovered yet.
    Enterprises had to cut their costs, and Information Technology departments, being looked at in most of the times as cost-centers, had to absorb a major cut into their budgets. Therefore, CIOs and IT Managers did not give much priority to upgrade their existing applications and database management systems, and their top priority went to acquiring new technologies and applications, while at the same time capping or even cutting their operational expenses.
    Upgrading applications and services is a lengthy task that requires significant investments. Migration project costs include acquiring new hardware, planning for re-coding deprecated features of prior SQL Server versions, doing unit testing, quality control testing and users testing, etc… This will amount to significant cost and time that CIOs had to give it less priority and eventually skip it for several years in the previous decade.
  • A technical aspect: there were significant changes at the core level of SQL Server versions 2005, 2008, and 2008R2 that made many enterprises and application vendors hesitant to upgrade from earlier versions. These changes go beyond deprecated features and direct into core database engine of SQL Server. Thus, enterprises and application vendors had not only to ensure syntax validity, functions and tools expiration, but in many cases they had to rewrite wide portions of their code and even to implement different programming strategies and techniques to make the application compliant with later versions. This is true for migrating SQL Server 2000 to 2005 path, and for migrating of SQL Server 2005 to 2008/2008R2 paths. The core differences between SQL Server 2008 and SQL Server 2008 R2 were not as significant.

While it is pretty easy for a programmer to re-write code to eliminate deprecated features and tools which are well-documented, it is extremely difficult to re-code or tune the application for the unknown changes within SQL Server engine that is black-box for both developers and enterprises and, in many scenarios re-writing the code completely to be compliant with the newer versions might be a favorable alternative rather than tuning performance of the code that is written for a previous versions.

As such, while it is always recommended to upgrade to newer versions of SQL Server, or at least to stay a version or two lagging to the current SQL Server version, other factors should be considered including to have sufficient budget for the upgrading to cover hardware, testing, licensing, application support, and to consider re-coding and testing the applications for not only deprecated features within the newer versions, but also unannounced changes to the SQL Server core, which can be only chased by running the code through extensive cycles of testing for all features, stress testing and other types of tests.

Categories: DBA Blogs

Parse time by day of the week

Bobby Durrett's DBA Blog - Mon, 2014-02-03 15:58

I wanted to find out if queries against a particular table were experiencing parse time during the week.  We had previously seen issues where queries on the table would be stuck parsing for tens of minutes before they started actually running.  I think this has been resolved but I’m looking for some validation from ASH.  I know that on Sundays we recompress some subpartitions for the table and this leads to higher parse times if the users run queries then, but I’m not trying to resolve that at this time.  I just care about the other days of the week.  So, here is the query I used:

to_char(sample_time,'DAY') sample_day,
count(*)*10 total_seconds
a.sql_id= s.sql_id
group by to_char(sample_time,'DAY'),in_parse
order by to_char(sample_time,'DAY'),in_parse;

The table “sqlids” has a list of all the SQL statements that refer to the table.  I populated sqlids like this:

create table sqlids as select SQL_ID
        where 1=2;

            SQL_TEXT like '%MYTABLE%' and
            SQL_ID = SQL_ID_ARGUMENT and
            DBID = DBID_ARGUMENT;

            insert into sqlids values (TEXT_REC.SQL_ID);
         END LOOP;
     END LOOP;

Here is the output of the query I mentioned earlier with the parse time in seconds by day of the week:

--------- - -------------
FRIDAY    N       1092220
FRIDAY    Y        281980
MONDAY    N       2158620
MONDAY    Y         77860
SATURDAY  N       1483420
SATURDAY  Y        259680
SUNDAY    N      18939770
SUNDAY    Y      65665540
THURSDAY  N       1180780
THURSDAY  Y        300800
TUESDAY   N       1286980
TUESDAY   Y        108510
WEDNESDAY N       1399100
WEDNESDAY Y        306890

As I hoped Sunday is the only day of the week that parse time exceeds non-parse time for the sql statements against the given table.  I’m still validating this in other ways – most importantly by trying to contact the users to see if they perceive the problem to be gone.

But, I thought I’d pass along the query in case others find it useful.

- Bobby


Categories: DBA Blogs

How to Configure Transactional Replication-Mirroring Failover

Pythian Group - Mon, 2014-02-03 11:20

Configure SQL Server Transactional Replication with Mirroring – this was one of the recent interesting and unusual projects that came our way. At first it looked really odd and intriguing at the same time. The question was, can it really be done?

Well, as it happens, Microsoft SQL Server has a pretty straight forward method to set it up – at least the Publishers-failover part. The Subscribers-failover is not a straight forward method, but is definitely possible through workarounds.

In this blog post, we’ll discuss how to configure a simple Transactional Replication-Mirroring failover configuration, particularly configuring failover of publications. This may be of some help if you do not want to reset publications and subscribers in case the published (and mirrored) database fails over to its mirror. While setting up the actual configuration, we’ll also discuss a couple of tweaks involved with the process.

Here’s the Scenario to begin with:

  1. Current setup contains 2 servers.
    • The OLTP Server (S_OLTP1) contains databases which support main portal application, and are highly volatile.
    • The application middle tier server (S_Apps) hosts SSIS and SSRS instances, besides basic configuration and metadata databases, like, SSISDB (for SSIS) and ReportServer (for SSRS).
    • Both the servers have SQL Server 2008 installed.
  2. Following requirements must be met for the project:
    • Provisioning a DR solution for main portal databases, by adding a new mirror server to the setup. So, Database Mirroring is the selected DR strategy.
    • Offloading the reporting requirements from S_OLTP1 server, by adding a new reporting server. This new server should be configured to subscribe data from S_OLTP1 and make it available for reporting purposes, by means of transactional replication.
    • A “complementary” requirement that comes up is that the transactional replication should not break in case any of the mirrored databases failover.

Note: Although, all the servers used in our project are SQL Server 2008’s, this blog post is valid for all implementations of SQL Server versions 2005 and higher.

Based on above requirements, we now have the following servers in our configuration:

  • S_OLTP1: The primary OLTP server, hosting all application databases. Transactional replication publisher. Mirror member server.
  • S_OLTP2: The new OLTP DR server. Mirror member server, with S_OLTP1 as Principal. Transactional replication publisher (obviously, as we want the replication to be working after failover).
  • S_Apps: The original middle tier server. (This will also be used as replication Distributor. Optionally, this server could be used as a Witness server for the mirrored pair of S_OLTP1 and S_OLTP2.)
  • S_Rept1: The new reporting server. (This server will host all databases required for reporting. These databases will be subscribers of publication(s) from S_OLTP1/2.)

Note: It is a must to have a remote Distributor if you want replication to failover. It is a MUST to configure a common (ideally, remote) distributor for publishers to failover.

Diagrammatic representation:


The Setup will be done as discussed below.

First step we will take in this document is mirroring on all the required databases. Since there is no special configuration required for “Replication-failover” to be done at mirroring end, we will exclude details of mirroring setup in this blog post.

Note: Database Mirroring can also be done after setting up publication. So, this step can be performed after publishing steps discussed later in this blog post.

Next, we will configure replication. As per the required setup, we will have to perform following actions:

  1. Setup Distributor on central remote distributor server (S_Apps1).
  2. Add S_OLTP1 and S_OLTP2 as publishers on Distributor.
  3. Add new Publications on the Publisher server.
  4. Configure the agent profiles or jobs to failover.
  5. Add subscription to S_Rept1.
  6. Repeat steps 3 & 4 for all the databases that require publishing.

Now that we know the process, let’s discuss these steps in detail starting with setting up the distributor.

  1. To setup a common remote distributor, we will connect to S_Apps1 server, using SSMS. Once connected, expand the server and right click on Replication and select “Configure Distribution…” from the context action menu. See figure 2 below.

    Figure 2:

  2. In the first page of Configure Distribution Wizard, press Next, as shown below in Figure 3.Figure 3:
  3. On the next page, keep the default option (Server will act as its own distributor) on, and press Next button. Refer Figure 4 below.Figure 4:
  4. As shown in Figure 5 below, select path for generating snapshots, and click Next to proceed.Figure 5:
  5. On the next screen, specify the details for distribution database, as shown in Figure 6.Figure 6:
  6. Next, enable this server to act as Distributor for the mirrored pair – S_OLTP1 and S_OLTP2. By default, you will find the server itself is listed as Publisher (refer Figure 7).
    a) Click on Add, to open “Connect to Server” dialog box, as shown in Figure 8.
    b) Specify the name for Primary server (S_OLTP1).
    c) Click Connect. This adds a new server to list of Publishers.Figure 7:

    1. ConfigDistributor_Step6Figure 8:

    d) Click Add again from main screen, and connect to Secondary server (S_OLTP2).
    e) Finally, you will see both servers are listed as Publishers, along with the distributor. Your screen should look like the snapshot shown in Figure 9 below.

    1. Figure 9:
      Note: I always use SQL Server Agent account, which is a domain account, for such connections — this saves a lot on connectivity and permissions issues.
  7. On the next screen, we will need to enter a custom password, which will be used by the Publishers to connect to the Distributor. This password is not related to SQL Server or Agent service. Make a note of the password you enter and keep it safe, because this is the one which you will need to provide when configuring the publishers to use remote distributor. A snapshot of this screen is shown in Figure 10 below.Figure 10:
  8. As in Figure 11, we are now done with configuration. Click Next.Figure 11:
  9. On the next screen, review the summary and press Finish button.
  10. The “Configuring…” screen will appear, and at the end of a successful completion, the final screen will look something like Figure 12 below.Figure 12:

Now that Distributor is successfully set up, let’s proceed to creating publications step — I won’t get into the details of creating a publication, to keep myself from deviating from the topic. Instead, let’s discuss which actions are differently performed when creating a publication for failover scenario. Here is how it goes:

  1. In the “New Publication Wizard”, when selecting distributor, choose to use remote distributor. Click Add and connect to the distributor configured in the steps above. The “Distributor” screen of “New Publication Wizard” will look something like Figure 13 below.Figure 13:
  2. On the next screen, we will specify the password that we provided while configuring the distributor for “Distributor Password” – refer Step 7, in configuring Distributor steps above. The wizard screen to specify the password will look like Figure 14 below.Figure 14:
  3. We will continue with rest of the wizard normally, as we would while creating a usual transactional publication.

Once we have the publication in place, we are done with setting up Distributor, Publishers and the Publication.

Next step up will be to configure the agents to failover.

Let’s discuss what needs to be done to make the replication agents to recognize the failover partner. There are a few ways we can do it:

a) Modify agent profile to change the “PublisherFailoverPartner” property, using Distributor properties dialog box.
b) Modify agent profile to change the “PublisherFailoverPartner” property, using TSQL.
c) Modify each agent’s job individually, to add the parameter “-PublisherFailoverPartner”.

The difference between changing agent profiles and agent job properties is that by changing profile, we can alter the behaviour of all publisher side agents. By changing the job properties, we only affect those agents whose job is modified. We will look at options A and C in this blog post.

To modify agent profile using Distributor properties, we will go through the following steps:

  1. Connect to the distributor. Right click “Replication” and select “Distributor Properties” from the context action menu. See Figure 15 below.Figure 15:
  2. This will open up the Distributor Properties dialog box for your distributor, as show below in Figure 16.Figure 16:
  3. Click on “Profile Defaults…” button to open up “Agent Profiles” dialog box. Choose profiles for log reader agents as show in Figure 17.Figure 17:

  4. Click on the ellipsis button “…” in the “Type” column for Default agent profile, to open up “Default agent profile Properties” dialog box. See Figure 18 below.Figure 18:
  5. By default, the dialog box shows only the properties that are configured. To see the complete list, including the property we are interested in, uncheck the Check box “Show only parameters used in this profile”.  Figure 19 shows what the screen looks like. Profile property “PublisherFailoverPartner” is highlighted in the snapshot below.Figure 19:
  6. We will not be able to change values here, as this is a System type profile. We will need to create a new agent profile and enable it for Log Reader agents to use. To do so, close the current properties and click the “New” button to open “New Agent Profile” dialog, shown below in Figure 20.Figure 20:
  7. Click “OK”, and open the “New Agent Profile” dialog box. Specify the Secondary Mirror server (S_OLTP2 in our case) as value of parameter “PublisherFailoverPartner”, as shown in Figure 21 below, and click “OK”.Figure 21:

  8. Now, the new profile is shown in the Agent profiles list for Log Reader Agents. Click the check box next to “NewProfile” profile. This makes a default for any new log reader agents that will be created. Figure 22 shows how the screen will look.Note: If you want to change all existing agents to use the newly created settings, click the “Change Existing Agents” button given below in the box.

    Figure 22:

Click OK, and voila! The log reader agents for this project are all set for automatic failover.

Alternatively, we can also customize automatic failover for specific agents by just modifying Agent job parameters.

To achieve this, simply open the properties and modify the job step “Run agent”. Figure 23 shows how it should be put in. We will simply add the parameter “-PublisherFailoverPartner” to the command. A typical command parameter-set will look like this:
-Publisher [S_OLTP1] -PublisherDB [DB1] -Distributor [S_Apps1] -DistributorSecurityMode 1  -Continuous -PublisherFailoverPartner [S_OLTP2]

The underlined portion is what we need to add to the job. This is how it will look once complete:

Figure 23:

That concludes the failover configuration for publishers. Now we can proceed with adding subscribers normally. Since, there is no configuration required at subscriber end, to configure publisher-failover scenario, we can proceed with adding subscribers normally. And, there we are, with the project successfully implemented!

Food for thought:

Although, we have wonderfully configured publisher failover, this is not a fully established topic and needs some more exploration and discussion. Clearly, not all types of replication setups support publisher-failover. It would be interesting to note which ones.

Moreover, we are talking about two major techniques combined! It increases possibilities and options, but it also introduces some challenges to be dealt with. Replication is highly robust, but what if mirroring breaks, or simply, the principal server is unavailable for some time? We sometimes rely on Replication Monitor, and would also love to think that it provides a complete picture of Replication state – even when mirrored!

Categories: DBA Blogs

A Song of Graph and Report, Part I: A Gathering of Stats

Pythian Group - Mon, 2014-02-03 11:02

Statistics and graphs — they don’t always mean much, but they can be so darn mesmerizing.

It’s in that optic that I was looking at the Map of CPAN the other day and thought, “Wouldn’t it be cool to have the same view within a distribution?” It’s not that I need to do that for anything specific, mind you. But to have a distribution split into territories based on authors and files… Maybe animated throughout different releases, to see the ebbs of contributors and code. It would be so nifty!

Now, considering that there are many things that can be done with those statistics — and because I should probably do some real work between bouts of fooling around — I decided to cut the exercise into different parts. Therefore, today we’ll begin with the essential, if a tad dry, step required for everything that will follow. Namely: the gathering of the statistics.

Lay the blames around.

The good news is that Git already has its git blame sub-command that provide us with a per-line author attribution of the codebase. All we have to do is to interface with it, munge its data to our liking, and we’ll be good to go.

To talk to Git, we’ll use Git::Repository, with the help of its plugin Git::Repository::Plugin::AUTOLOAD to make things a little neater.

Round up the files.

So, first step: get the list of the files of the project for a given version tag.

use Git::Repository 'AUTOLOAD';

my $project_dir = '.';
my $version     = 'v1.2.3';

my $git = Git::Repository->new( work_tree => $project_dir );

my @files = sort $git->ls_tree( '-r', '--name-only', $version );
Who did what?

So far, so good. Now for every file, we want to have a list of its authors, and how many lines they claim as their own:

my %file_attribution = map { 
    $_ => file_authors( $git, $version, $_ ) 
} @files;

sub file_authors {
    my( $git, $version, $file ) = @_;

    return [
        map { /\(\s*(.*?)\s*\d{4}-/ } $git->blame( $version, $file )
Same thing, but compressed.

With the code above, we now have an array for each file, holding the name of the author of each line. If we just want to tally the overall contribution of each author, that’s slight overkill but it’ll become handy when we’re ready to draw the author map of each file.

Still… one entry per line, that’s quite verbose. Instead, let’s try to scrunch that into a tally of the successive lines associated with the same author:

use List::AllUtils qw/ reduce /;

my %file_attribution = map { 
    $_ => file_authors( $git, $version, $_ ) 
} @files;

sub file_authors {
    my( $git, $version, $file ) = @_;

    return reduce {
        if ( @$a and $a->[-1][0] eq $b ) {
        else {
            push @$a, [ $b, 1 ];
    } [ ], map { /\(\s*(.*?)\s*\d{4}-/ } 
               $git->blame( $version, $file );

Aaaah, yes. Not as dirt-simple as before but the data structure is now much less wasteful.

Massively parallel blaming.

Next challenge: git blame is a little bit on the slow side. Not sloth-on-a-diet-of-snails kind of slow, but it does have to spit out all lines of every file. For big projects, that takes a few seconds.

But… isn’t our little file_authors() function work in isolation for each file? Wouldn’t that be a perfect moment to whip out some parallelization ninja trick? Like… Oh, I don’t know, try out that neat new MCE module that makes the rounds these days?

use MCE::Map;

my %file_attribution = mce_map { 
    $_ => file_authors( $git, $version, $_ ) 
} @files;

And indeed, with the 4 cores of my faithful Enkidu, the overall script suddenly goes 4 times faster. Considering that all it took was changing a map to a mce_map… Yeah, I’ll take that.

Sum everything up.

We now have our raw numbers. Getting the stats per author is now simple bookkeeping:

my $nbr_lines;
my %authors;

for my $x ( map { @$_ } values %file_attribution ) {
    $nbr_lines += $x->[1];
    $authors{$x->[0]} += $x->[1];

# switch into percents
$_ = 100 * $_ / $nbr_lines for values authors;
Put it in a big book of stats.

We’ll want to return to those stats, so let’s keep them in a file. Let’s make it a JSON file. Everybody loves JSON.

use JSON_File;

tie( my %stats, 'JSON_FILE', 'stats.json', pretty => 1 );

$stats{$version} = {
    nbr_lines   => $nbr_lines,
    authors     => \%authors,
    files       => \%file_attribution,
Time to generate a report.

The numbers are now ours to do as we see fit. Pretty graphical stuff will have to wait for the next installments, but why not create a quick credit roll for the project version we just munged?

use 5.10.0;

use List::AllUtils qw/ part /;

my( $minor, $major ) = part    { $authors{$_} >= 1 } 
                       sort    { $authors{$a} <=> $authors{$b} } 
                       keys    %authors;

my $lines = $nbr_lines;

# 1000000 => 1,000,000 
1 while $lines =~ s/^(\d+)(\d{3})/$1,$2/;

say <<"END";


This is the list of all persons who had their hands in
crafting the $lines lines of code that make
this version of $project, according to `git blame`.

This being said, don't take those statistics too seriously, 
as they are at best a very naive way to judge the contribution 
of individuals.  Furthermore, it doesn't take into account the army 
of equaly important peoples who report bugs, worked on previous
versions and assisted in a thousand different ways. For a glimpse of
this large team, see the CONTRIBUTORS file.

## The Major Hackarna

All contributors claiming at least 1% of the lines of code the project.


    printf "    * %-50s %2d %%\n", $_, $authors{$_} for @$major;

    if ( $minor ) {
        say "\n\n## The Minor Hackarna\n\n",
            "With contributions of: \n";

        say "    * ", $_ for @$minor;

Running that against the latest release of Dancer gives us:


This is the list of all persons who had their hands in
crafting the 32,944 lines of code that make
this version of Dancer, according to `git blame`.

This being said, don't take those statistics too seriously, 
as they are at best a very naive way to judge the contribution 
of individuals.  Furthermore, it doesn't take into account the army 
of equaly important peoples who report bugs, worked on previous
versions and assisted in a thousand different ways. For a glimpse of
this large team, see the CONTRIBUTORS file.

## The Major Hackarna

All contributors claiming at least 1% of the lines of code the project.

    * Alexis Sukrieh                                     30 %
    * franck cuny                                        17 %
    * Sawyer X                                           10 %
    * Damien Krotkine                                     7 %
    * David Precious                                      6 %
    * ambs                                                5 %
    * Yanick Champoux                                     4 %
    * Michael G. Schwern                                  2 %
    * sawyer                                              1 %
    * Alberto Simoes                                      1 %
    * Mark Allen                                          1 %
    * Ovid                                                1 %

## The Minor Hackarna

With contributions of: 

    * mokko
    * Fabrice Gabolde
    * Philippe Bruhat (BooK)
    * Alex Kapranoff
    * LoonyPandora
    * chromatic
    * Anton Gerasimov
    * geistteufel
    * Paul Driver
    * Pedro Melo
    * sdeseille
    * David Moreno
    * Naveed Massjouni
    * Gabor Szabo
    * Rick Myers
    * Alex Kalderimis
    * Sam Kington
    * Max Maischein
    * Colin Keith
    * pdl
    * David Golden
    * JT Smith
    * Flavio Poletti
    * Nate Jones
    * William Wolf
    * Dagfinn Ilmari MannsÃ?Â¥ker
    * Stefan Hornburg (Racke)
    * Chris Andrews
    * Kaitlyn Parkhurst
    * Oliver Gorwits
    * Perlover
    * Squeeks
    * Rowan Thorpe
    * CPAN Service
    * John Wittkoski
    * David Steinbrunner
    * Felix Dorner
    * Alessandro Ranellucci
    * Danijel Tasov
    * Al Newkirk
    * Mikolaj Kucharski
    * Kent Fredric
    * Kirk Kimmel
    * Vyacheslav Matyukhin
    * niko
    * Tom Hukins
    * jamhed
    * Franck Cuny
    * Joshua Barratt
    * James Aitken
    * Jesse van Herk
    * rowanthorpe
    * Roman Galeev
    * Marc Chantreux
    * Alex C
    * Martin Schut
    * Naveed
    * Michal Wojciechowski
    * Olof Johansson
    * Naveen
    * Ilya Chesnokov
    * Roberto Patriarca
    * boris shomodjvarac
    * Tom Wyant
    * Lee Carmichael
    * Jacob Rideout
    * Richard Sim�µes
    * Rik Brown
    * Alan Haggai Alavi
    * jonathan schatz
    * Dennis Lichtenthaeler
    * Emmanuel Rodriguez
    * smashz
    * David Cantrell
    * Xaerxess
    * Ask Bj�¸rn Hansen
    * Paul Fenwick
    * Lee Johnson
    * Jonathan "Duke" Leto
    * Tim King
    * Olivier Mengu�©
    * Brian Phillips
    * Fayland Lam
    * adamkennedy
    * Craig Treptow
    * Jonathan Scott Duff
    * Joel Roth
    * Mark A. Stratman
    * ironcamel
    * John Barrett
    * Brian E. Lozier
    * Florian Larysch
    * Nicolas Oudard
    * Grzegorz Ro�¼niecki
    * Christian Walde
    * Maurice
    * Sebastian de Castelberg
    * scoopio
    * Paul Tomlin
    * Fran�§ois Charlier
    * Anirvan Chatterjee
    * jonasreinsch
    * Neil Hooey
    * Hans Dieter Pearcey
    * Lars Thegler
    * Murray
    * Adam J. Foxson
    * Tatsuhiko Miyagawa
    * sebastian de castelberg
    * Craig
    * Jakob Voss
    * isync
    * Mark Stosberg
    * Assaf Gordon
    * Hagen Fuchs
    * Ivan Paponov
    * Roman Nuritdinov
    * Tom Heady
    * Michael Genereux
    * Duncan Hutty
    * Alfie John
    * Matthew Horsfall (alh)
On the next episode…

Things get interesting as we’ll tackle transforming those stats into an author-colored map of the distribution. Weeee!

Categories: DBA Blogs

How To Improve SQL Statements Performance: Using SQL Plan Baselines

Pythian Group - Mon, 2014-02-03 09:25

The performance of any Oracle database heavily relies on query execution(s). The reasons for any SQL statement’s execution plan changes could include a variety of actions, like gathering optimizer statistics (table, index, schema etc.)  changing optimizer parameters, schema definitions, adding indexes etc.  As experienced Oracle DBAs, we should be aware of the fact that the above mentioned actions meant to improve SQL performance will not always guarantee positive results.

So in this case, many of us would try to freeze execution plans (Stored Outlines) or lock the optimizer statistics. However, doing so prevents such environments/databases of taking advantage of new optimizer functionality or access paths, which would improve the SQL statements performance. That is where SQL Plan Management comes in very handy…

SQL Plan Management (SPM) provides a framework for completely transparent controlled execution plan evolution. With SPM the optimizer automatically manages execution plans and ensures only known or verified plans are used. When a new plan is found for a SQL statement it will not be used until it has been verified by the database to have comparable or better performance than the current plan.


Next, I will explain the steps for forcing a bad query to use a better execution plan by loading SQL Plans into SPM using AWR.

Identifying the Slow Query

We have the following scenario:

- Oracle version single instance database

- Performance issue caused by the following bad query:


with initial explain plan :


As shown in the Plan output, a full table scan was used, resulting in excessive IO for this query.  It seemed this query needed an index to reduce the IO. Therefore I have added two indexes on ‘status’ and ‘prevobjectid’ columns for the EMPLOYEES table, gathered table statistics and then checked again the explain plan. We will see now that due to index creation the DISPLAY_AWR program shows a newly generated explain plan with improved cost using an index range scan versus the full table scan used by the initial plan (Plan hash value: 2172072736).


Now we have obtained a new, better execution plan in AWR for the SQL statement, but our next question would be, “How can we make sure it will be the only plan picked by the Cost Based Optimizer for future executions”?

The answer:  “Create a SQL Tuning Set for the SQL, then create a new SQL Baseline from the STS so the Optimize will choose the preferred Execution Plan”.

Each time a SQL statement is compiled, the optimizer first uses a cost-based search method to build a best-cost plan, then tries to find a matching plan in the SQL plan baseline.  If a match is found, the optimizer will proceed using this plan. Otherwise, it evaluates the cost of each accepted plan in the SQL plan baseline and selects the plan with the lowest cost.

Here are the steps for loading SQL Plans into SPM using AWR by implementing SQL Baselines for the bad query.

Step 1: Set up a SQL Baseline using known-good plan, sourced from AWR snapshots.

To do so, SQL Plan Management must be active and the easiest condition to checking optimizer_use_sql_plan_baselines which needs to be TRUE.

sql2 Step 2: Create SQL Tuning Set (STS).

SQL tuning set (STS) is a database object that includes one or more SQL statements along with their execution statistics and execution context, and could include a user priority ranking. You can load SQL statements into a SQL tuning set from different SQL sources, such as AWR, the shared SQL area, or customized SQL provided by the user. An STS includes:

-          A set of SQL statements

-          Associated execution context, such as user schema, application module name and action, list of bind values, and the cursor compilation environment

-          Associated basic execution statistics, such as elapsed time, CPU time, buffer gets, disk reads, rows processed, cursor fetches, the number of executions, the number of complete executions, optimizer cost, and the command type

-          Associated execution plans and row source statistics for each SQL statement (optional)

The concept of SQL tuning sets, along with the DBMS_SQLTUNE package to manipulate them, was introduced in Oracle 10g as part of the Automatic SQL Tuning functionality. Oracle 11g makes further use of SQL tuning sets with the SQL Performance Analyzer, which compares the performance of the statements in a tuning set before and after a database change. The database change can be as major or minor as you like, such as:

  • Database, operating system, or hardware upgrades.
  • Database, operating system, or hardware configuration changes.
  • Database initialization parameter changes.
  • Schema changes, such as adding indexes or materialized views.
  • Refreshing optimizer statistics.
  • Creating or changing SQL profiles.

Now I create a SQL Tuning Set based on the slow query with a SQL_ID of 9kt723m2u5vna.


Step 3: Populate STS from AWR.

Now I will locate the AWR snapshots required to populate the STS, and load the STS based on those snapshot ID’s and the SQL_ID.


Step 4: List out SQL Tuning Set contents.

Now I can query the STS to verify it contains the expected data.


Step 5: List out SQL Tuning Set contents

Though I have created and verified the STS, the Baseline has not yet been created.


Step 6: Load desired plan from STS as SQL Plan Baseline

Now I will load the known good plan that uses the newly created index into the Baseline.


Step 7: List out the Baselines again.

Now verify the Baseline contains the desired plan.


Step 8. Flush the current bad SQL Plan.

After loading the baseline, the current cursor must be flushed from the cache to make sure the new plan will be used on next execution of the sql_id 9kt723m2u5vna

sql1 Conclusion

As this blog post demonstrates, SQL Plan Management (SPM) allows database users to maintain stable yet optimal performance for a set of SQL statements and baselines seem to be a definite step in the right direction. Baselines can be captured from multiple sources, SPM allowing new plans to be used if they perform better than the baseline fact that could improve the overall application/system functionality.

Categories: DBA Blogs

RMOUG Training Days 2014

Galo Balda's Blog - Sun, 2014-02-02 11:29


I’ll be presenting on February 6th. Here are the details of my session:

SQL Pattern Matching in Oracle 12c

Room 501, 11:15 – 12:15

Last year I had a great time, so I can’t wait to be back in Denver.

I hope to see you there!

Updated on 02/17/2014: The presentation is available on Slideshare

Filed under: 12C, RMOUG, SQL Tagged: 12C, RMOUG, SQL
Categories: DBA Blogs

Find what databases you have quickly – #EM12c Inventory Search

DBASolved - Sat, 2014-02-01 22:51

Thought I would write a quite tip on how to find what versions of databases you have in your environment.  Oracle Enterprise Manager 12c provides a quick and easy way to find this information once it is configured within your environment. 

Starting at with the Enterprise menu, you will go to Configuration and then Inventory and Usage Details (Enterprise –> Configuration – Inventory and Usage Details) (Figure 1).

Figure 1: Inventory and Usage Details Menu Item


After clicking Inventory and Usage Details, you will land on the associated page.  In the drop down box labeled “Show”, select Databases (Figure 2).  This will show you all the databases that are listed in the Oracle Enterprise Manager 12c that are being monitored.

Figure 2: Show Menu


Once all the databases are displayed (listed), you can see exactly what you have in your environment (Figure 3).  In this case, you test environment is comprised of 2 11gR2 databases ( & and a set of 12cR1 databases (CDB & 2 PDBs).

Figure 3:  Database Inventory listed (not a great graphic)


Hope this quick tip helps you find additional information about targets in your environment.


twitter: @dbasolved


Filed under: OEM
Categories: DBA Blogs

Log Buffer #357, A Carnival of the Vanities for DBAs

Pythian Group - Fri, 2014-01-31 09:16

This Log Buffer captures the excitement which is flowing unbridled after the new year frenzy, and the blogosphere is bubbling with new and novel technical features, tricks and tips. Enjoy!!!


Jeffrey Kemp asks. Need to change the label of an item on-the-fly?

As a system grows, it accumulates technical debt – improvements and cleanup that your really ought to get around to doing.

Sometimes statements take longer than you’d like to complete. This can happen for a number of reasons including data changes, table growth, stale statistics and the occasional optimizer bug you haven’t encountered before.

The JSON-P API as described in JSR-353 is a limited API for working with JSON; but at the basic level it will do the job. It is possible to add a few utility methods that can make your code, in my eyes, prettier.

Oracle Database 11gR2 ( installation on Oracle Linux 6.4

SQL Server:

Finding “Active” Rows for the Previous Month (SQL Spackle)

How do you configure where your database backups are created? Do you have problems updating all of your backup jobs if the backup location needs to change?

Stairway to Advanced T-SQL Level 2: Writing Subqueries

Getting Starting with Change Data Capture (CDC) in SQL Server – Part 1

In this article, Gail Shaw looks at how you can identify common types of deadlock, the difference between a deadlock and severe blocking, and how to avoid and fix the most common deadlock types.


Percona XtraDB Cluster 5.6 GA release now available.

In MySQL 5.7.2 a new include file “–source include/“ made its appearance in most of the .test files in the MTR suite.

Daniel thought it would be nice to be able to query the FOSDEM schedule for the MySQL devroom with SQL.

Replicate from Oracle to Oracle, Oracle to MySQL, and Oracle to Analytics.

They say there are two hard things in software development: cache invalidation, naming things, and off-by-one errors.

Categories: DBA Blogs

Purge statspack snaps of source db on test db having different DBID?

Pythian Group - Fri, 2014-01-31 09:07

As most DBAs are aware, the clean deletion of the old statspack snaps is very difficult up to rdbms version 9i. Thanks to the statspack.purge procedure introduced by oracle from 10gR1 version, now it’s possible to purge un-referenced data too. This blog post explains about how to use statspack.purge procedure, but for the test/clone databases which uses different DataBase IDentifier(DBID) compared to the source database. Please remember the steps explained on this blog are not required when the source and test databases have the same DBID.

Normally DBID gets changed on cloned databases during the following scenarios.

1. The most commonly used RMAN ‘duplicate database’ feature to create the test database.

2. The database utility ‘nid’ used to change the test database name and dbid.

3. The test database controlfile gets created using syntax based on the scripts available on text backup controlfile.

Consider you have production/source database is configured to generate statspack snaps once in 20 minutes and the retention were 90 days. When this source database gets cloned using above methods to create test database, it inherits the same behavior. Now the test database contains statspack snaps belongs to source database as well as for the current database too. Even when you modify the existing purge script to retain less snaps, it would valid only for the snaps belong to the current DBID. The snaps belongs to other than current DBID would never get purged by this script, even though they are no longer valid for this test database.

1. Gather the DBID details from stats$snapshot table on the test database.

For example,

SQL> select distinct dbid from stats$snapshot;

1215068670 ==> This is the source database DBID
393689388 ==> This is the test database DBID

2. Gather the snaps range handled by the source database using the following queries.

For eg:

SQL> select min(snap_id) from stats$snapshot where dbid=1215068670;


SQL> select max(snap_id) from stats$snapshot where dbid=1215068670;


3. Gather the row count on various tables to verify the successful purge activity completion.

For eg:

SQL> select count(1) from stats$snapshot where dbid=1215068670;


SQL> select count(1) from stats$sqltext where last_snap_id < 93776;


SQL> select count(1) from STATS$STATSPACK_PARAMETER where dbid=1215068670;


SQL> select count(1) from STATS$UNDOSTAT where snap_id < 93776;


4. Copy the $ORACLE_HOME/rdbms/admin/sppurge.sql to your home directory and modify it accordingly.

i) Remove the dbid column from this select statement on the script.

select d.dbid dbid ==> Remove this column being selected.
, db_name
, i.instance_number inst_num
, i.instance_name inst_name
from v$database d,
v$instance i;

ii) Substitute the source database DBID on this location on the script.

:dbid := &dbid; ==> Change the ‘&dbid’ value as 1215068670
:inst_num := &inst_num;
:inst_name := ‘&inst_name’;
:db_name := ‘&db_name’;

iii) Change the variable “i_extended_purge” value as ‘true’ on the script.

:snapshots_purged := statspack.purge( i_begin_snap => :lo_snap
, i_end_snap => :hi_snap
, i_snap_range => true
, i_extended_purge => false ==> Change the value as true
, i_dbid => :dbid
, i_instance_number => :inst_num);

5. Execute this custom purge script on the test database and provide the snaps range when requested.

For eg:

Specify the Lo Snap Id and Hi Snap Id range to purge
Enter value for losnapid: 90920
Using 90920 for lower bound.

Enter value for hisnapid: 93775
Using 93775 for upper bound.

Deleting snapshots 90920 – 93775.

6. Now logged into the test database and execute the queries to verify the deletion happened.

SQL> select count(1) from stats$snapshot where dbid=1215068670;


SQL> select count(1) from stats$sqltext where last_snap_id < 93776;


SQL> select count(1) from STATS$STATSPACK_PARAMETER where dbid=1215068670;


SQL> select count(1) from STATS$UNDOSTAT where snap_id < 93776;


As you noticed, This is very simple action plan, which may require some more modification on the custom purge script when it was used on RAC database.

Categories: DBA Blogs

New member of The OakTable: me!

The Oracle Instructor - Thu, 2014-01-30 01:20

I am an Oakie now :-)




To be a part of this highly respected circle of Oracle Scientists is something that I consider one of the greatest achievements of my professional life.

Categories: DBA Blogs

Query tuning example

Bobby Durrett's DBA Blog - Wed, 2014-01-29 16:58

A coworker of mine cleaned up a bunch of old data to improve performance on an older system of ours and one of the queries started running slower.  It looks like the optimizer was choosing a full table scan when an index existed that was much faster.  So, I took at look at why it was choosing the full scan and what could be done to get the query to run with the index.

This is a 64 bit HP-UX PA-Risc system.  The query without any hints runs like this:

Elapsed: 00:04:54.65

Execution Plan
   0      SELECT STATEMENT Optimizer=CHOOSE 
          (Cost=23208 Card=1 Bytes=12)
   1    0   SORT (AGGREGATE)
              (Cost=23208 Card=68262 Bytes=819144)

          0  recursive calls
          0  db block gets
     242929  consistent gets
     238854  physical reads
          0  redo size
        203  bytes sent via SQL*Net to client
        242  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

I renamed the production table to MYTABLE to hide its name.  Here is how the query runs with the index hint:

Elapsed: 00:00:00.21

Execution Plan
   0      SELECT STATEMENT Optimizer=CHOOSE 
          (Cost=34865 Card=1 Bytes=12)
   1    0   SORT (AGGREGATE)
              (Cost=34865 Card=68262 Bytes=819144)
                (Cost=194 Card=68262)

          0  recursive calls
          0  db block gets
       1180  consistent gets
          0  physical reads
          0  redo size
        203  bytes sent via SQL*Net to client
        242  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

I got this output by putting these commands before the sql:

set autotrace on
set timing on

The query looks something like this:

   WHERE MYTABLE.COLUMN1 = '01242014';

The index is on COLUMN1 only.

Here is what the query looks like with the index hint:

   WHERE MYTABLE.COLUMN1 = '01242014';

So, the question is why does the optimizer choose a full scan which runs for almost 5 minutes instead of the index scan which runs in a fifth of a second?  I think that the answer is that the optimizer assumes there is no caching.  Notice the number of consistent gets and physical reads in each case:


     242929  consistent gets
     238854  physical reads


       1180  consistent gets           
          0  physical reads

If you look at the autotrace output and you see consistent gets but no physical reads it means that the blocks are being read from memory and not the disk.  So, just about every block read by the full scan is read from the disk and not from memory.  In the case of the index scan all the blocks are in memory.

Other than the caching the optimizer’s cost estimates aren’t that far off.  I think the optimizer’s cost units are the equivalent of single block reads.  I think I read this in Jonathan Lewis’ book.  These are the sections of the plan that show the cost of the two ways of reading from the table:

              (Cost=23208 Card=68262 Bytes=819144)

              (Cost=34865 Card=68262 Bytes=819144)
                (Cost=194 Card=68262)

So the full scan has a cost of 23208 which is the equivalent of that many 10 millisecond single block reads.  That’s about 232 seconds which is about 4 minutes.  So, that’s not far off for the cost of the full scan which ran in 5 minutes.  Also, full table scans do multi block reads so when autotrace says physical reads = 238854 what it really means is that many blocks were read in through physical reads.  Our system has db_file_multiblock_read_count=16 so probably 16 blocks are read per physical read.  The optimizer estimated a cost of 23208 which is about 10% of the physical blocks and this wasn’t a bad estimate because the blocks are read together.  So it assumed that the 238854 blocks would be read in the time it would take for 23208 single block reads and this was pretty close.

But, the index scan estimated 34865 sequential reads which is about 6 minutes.  Now, if there was no caching this wouldn’t be such a bad estimate.  Autotrace says there are 1180 blocks read from memory.  If these were read from disk each block would be a separate disk I/O so it would be about 12 seconds.  I checked and I did find that some values of the column had more rows than others and for the value 01242014 that I was testing with was one of the values with fewer rows.  So, for different values of COLUMN1 without caching the index would be a lot closer to the full scan:

SELECT COLUMN1,count(*) cnt
group by COLUMN1
order by cnt desc;

COLUMN1         CNT
-------- ----------
11262013     873867
11252013     576299
09222013     237098
08052013     179476
12082013     177359
11102013     175178
09152013     174220
10132013     174204
11172013     174102
08252013     173758
... skipped some ...
01242014      53801
10232013      53785
10072013      53335

So the value 01242014 only has 53801 rows but 11262013 has 863867 rows so it probably has blocks closer to the estimated 34865.  Here is a query to show how many blocks each value of the column has:

count(distinct DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid)) cnt
group by COLUMN1
order by cnt desc;

COLUMN1        CNT
-------- ----------
11262013      16338
11252013      10847
09222013       4409
08052013       3437
12082013       3337
11102013       3305
09152013       3290
10132013       3286
11172013       3285
08252013       3281
10272013       3255
... skipped some ...
01242014        926
01232014        924
09262013        922
01132014        915

So, for the values of the column with the most rows there are 16338 blocks, somewhere in the vicinity of the estimated 34865 single block reads.  Anyway, if the optimizer is off by less than a factor of 100 I’d say it is pretty close.  But, the run time is off by about 600 – 5 minutes versus .2 seconds so that is a real problem.

Oracle’s method of dealing with caching of index blocks and indexed table blocks is one of the optimizer parameters.  Here are the settings as we have them on our system:

NAME                                 VALUE
------------------------------------ -----
optimizer_index_caching              0
optimizer_index_cost_adj             100

I messed with different values of optimizer_index_cost_adj and found that 66 was the highest value that would cause the plan to switch to an index scan.

Execution Plan
   0      SELECT STATEMENT Optimizer=CHOOSE 
          (Cost=23011 Card=1 Bytes=12)
   1    0   SORT (AGGREGATE)
              (Cost=23011 Card=68262 Bytes=819144)
                (Cost=194 Card=68262)

Note that the cost of the index access to the table has come down to 23011 which is less than the 23208 estimated for the full scan.  Also note that 23011/34865=.66=66/100.  So, the cost of the index access to the table is just multiplied by the value of optimizer_index_cost_adj divided by 100.

So, now we have come down to where the rubber meets the road.  What should we then do with all of this information?  The development team has told us that they can add a hint to their code, but they are concerned that there is some general problem with the database and they will have to keep adding hints in various other places.  Note that this is a very old system with vendor supplied code.  Presumably before the data was cleaned up the cost of the full scan exceeded that of the index scan and that it is only because our new table has so much less data that the index scan has become less costly.

So, we could set optimizer_index_cost_adj to 66 or some other value but then we would need to test the entire system.  How much effort are we going to put into a system that we need to retire anyway?  This is really where I come down with hints and other performance methods.  I’ve known about using optimizer_index_cost_adj to encourage index scans for a long time.  But, if I’m supporting a bunch of older systems I can’t just go in and start tweaking parameters without appropriate testing.  So, I’d love to adjust the parameter rather than using the hint to band aid this one query, but we have a lot of databases to support and some times you just have to apply the quick fix rather than taking the time to apply a more global solution.

- Bobby









Categories: DBA Blogs

Database Machine (EXADATA) Schematic problem in OEM12c

DBASolved - Tue, 2014-01-28 11:45

When adding an Exadata to Oracle Enterprise Manager 12c (OEM), it is pretty easy yet at times you may run into a problem or two.  Many of these problems can be minimized by using the Oracle Enterprise Manager 12c: Exadata Discovery Cookbook (cookbook).  The cookbook can be found here, so you don’t have to search to far for it.

As with adding any target in OEM, you add the Exadata the same way (Setup –> Add Target –> Add Targets Manually).  I’m not going to go into details here on adding the Exadata target because it is covered in the cookbook and it is also a basic function of OEM.   Just keep in mind, that adding an Exadata, you will need the databasemachine.xml or the catalog.xml file.  With the Exadata’s I’ve been adding, I have been using the databasemachine.xml.

What I do want to go over, is when you add an Exadata and the schematic of the Exadata box does not appear in OEM.  In figure 1, you see that I have two Exadata boxes listed on the Oracle Exadata Database Machines page. 

Figure 1: Oracle Exadata Database Machines


One of these Exadata, was added to OEM without a problem; the other the schematic didn’t come up.  Just to make this a bit easier, the top database machine is the one that is missing the schematic.  Figure 2, shows what I’m referring to about missing the schematic.  What shows up is only the legend, but no graphic for the machine itself.

Figure 2: Missing schematic


So, the question has to be asked, Why did this happen? Everything was configured correctly!  Well, I was so puzzled by this, I had to ask Oracle for a bit of help.  What we (myself and Oracle) identified was that the databasemachine.xml file had a duplicate entry in it. 

Fixing the databasemachine.xml file is explained in a bit.  First, you need to remove the database machine that is having the problem from OEM.  This can be done from the Database Machine menu (Figure 3). 

Figure 3: Database Machine menu


After clicking the Remove Target… option, you are taken to a page that provides options for removing the Exadat from OEM (Figure 4).   To remove the whole Exadata, the default radio button is fine.  Just make sure you select the option to Unsubscribe SNMP Subscriptions. Once you check the box, make sure all the passwords are correct.   Then click the Yes button in the right-hand corner.

Figure 4: Remove Database Machine


When the Exadata is removed, OEM will drop you back into the All Targets page.  At this point, the Exadata has been removed from OEM.

Now, you are back to a clean slate for adding the Exadata to OEM.  Before you rush to re-add the machine, we need to take a look at the databasemachine.xml file.  What you are looking for is any possible duplicates in the file.

Note: The EMOMS.log file on the OEM server helps in resolving this issue.

In reviewing the EMOMS.log, you will want to search for any interactions between OEM on the name of your Exadata.  In searching the EMOMS.log you may find an Oracle error, in my case the error was ORA-00001.  OEM couldn’t add a record to the EM_DBM_RACK_COMPONENTS table due to a PK error (Listing 1).

Listing 1: PK Error in EMOMS.log

ERROR model.DbmDiscovery logp.251 – ORA-00001: unique constraint (SYSMAN.EM_DBM_RACK_COMPONENTS_PK) violated

Knowing that there is a primary key error in the EM_DBM_RACK_COMPONETS table, this should lead you to think that the problem is in the databasemachine.xml file.   The databasemachine.xml file can be edited with any text editor; the file is an XML file.  A simple search for each of the values in the <ADMINNAME>.  Listing 2, shows you what I found in the databasemachine.xml file I’m working with.

Listing 2: Duplicates in databasemachine.xml

   <ITEM ID=”1″>







    <ITEM ID=”13″>







One of these entries in the databasemachine.xml file has to go; however, do not remove the whole <ITEM_ID></ITEM_ID>.  Just remove the name from the <ADMINNAME></ADMINNAME> in the second entry.  In this example, it would be <ITEM_ID=13> is the duplicate and I removed the name from the <ADMINNAME> tags.

After editing the databasemachine.xml file,  copy it to all the nodes within the Exadata.  This way you make sure that all the machines have the same file.  To do this,  use SCP (Listing 3).

Listing 3: SCP to copy to other compute nodes

scp ./databasemachine.xml root@db02:/opt/oracle.SupportTools/onecommand

Now, with the databasemachine.xml file in place, you can try and re-add the Exadata to OEM.  Again, keep in mind the easiest way to add the Exadata is to follow the cookbook (here).  That way you make sure all the points are covered.

Once you rediscover the Exadata in OEM, you should be able to pull the Database Machine Schematic up correctly (Figure 5). 

Figure 5: The correct view of Database Machine Schematic


Now, that the Exadata has been imported into OEM successfully, you can now add all the associated targets that are running on the Exadata (ie, database, ASM, listener, etc..). 


twitter: @dbasolved


Filed under: Exadata, OEM
Categories: DBA Blogs

My first Backup and Recovery Quiz

Hemant K Chitale - Tue, 2014-01-28 09:45
I have created a short 5 question quiz on Oracle Backup and Recovery at QuizBean.  Please test it.  You can submit your comments against this blog post.  (I want to see if QuizBean is reliable).
Categories: DBA Blogs

Partner Webcast – Oracle Virtual Assembly Builder with Oracle VM 3.2

Increased operating costs, inefficient hardware utilization and rapidly expanding data centers have made virtualization the most compelling IT technology in years. Virtualization for desktop and...

We share our skills to maximize your revenue!
Categories: DBA Blogs

Most Simplest Example of Big Data Ever

Pakistan's First Oracle Blog - Mon, 2014-01-27 22:03
When I have to travel by air to somewhere, I go to a specific website to search and book my flight. Every time I visit that website, I encounter the same experience. I have to go through the same steps according to my same preferences, every time I need to book the flight.

Here is what I always do to book my flight at that website on each visit:

  1. Sign in with my userid
  2. Type in my origin and destination (90% of time same)
  3. Type in dates
  4. Sort the results by lowest price
  5. Search for flights with lowest price and least hops (Direct are ideal)
  6. I prefer red eye flights, so I also look for them.
  7. After searching for long time, I come close to what I want and then book it.
Despite of my log-in, despite of my usual pattern of purchase, website doesn't know what I am looking for. After my log-in, all they present to me is my personal details, limited travel history, reward points, and offers based on those reward points.

In other words, they present me with Small Data.

How nice would it be, if after sign-up they ask me in simple words:

Hey Fahd, are you still your old miser-self and want to get the cheapest direct flight(s) from Darwin to Sydney at that godforsaken time at night?

I would then just laugh, click yes, give me travel dates, and they would present me with options tailored to me habits. I know they would have to go through lots of data, anlayze it, slice it, dice it, and then come up with that above statement. I know I am not their only customer, and so they have to do the same for hundreds of thousands of their customers with more complex patterns, along with sifting through data of their partner airlines, may be hotels, car rentals, etc.

In other words, they have to go through Big Data.
Categories: DBA Blogs