XTRSBY – Tuning Oracle Data Guard

If you’re reading this, you probably love SQLT and want to learn more about it, but sometimes, a SQLT Xtract isn’t the best tool for the job. Here’s an example, tuning Data guard. Using the right tool always makes the job a little easier.

Human beings love to use tools, but sometimes we make life much harder by using the wrong tool for the job.
Maybe you don’t have any option, maybe you’re trying to use the tools you have to hand; a couple of weeks ago I tried to dig away a portion of flooring with a screwdriver. The screwdriver was handy and I didn’t have a chisel. Naturally, it didn’t turn out too well. The screwdriver was ruined and the job, well let’s just say it could have been better, especially if I’d used the correct tool!

The same can be said of tuning. If you have a Data Guard standby instance and you need to tune, you can’t use SQLT XTRACT. It just doesn’t work. You’ll get a message about read only databases that can’t allow write functions.

ORA-16000: database open for read-only access

For Data Guard you need to use the right tool. Luckily XTRSBY has been created specifically for this purpose.

XTRSBY (SQL script sqltxtrsby.sql) works with a database link to collect information from the read-only database and store that information in the read-write database. You need only create the database link to use it (if there isn’t one there already).

Here’s an example:

SQL> create database link “TO_STANDBY” connect to sqltxplain identified by oracle using ‘(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=<host-name>)(PORT+1521))(Connect_Data=(SID=SNC2)))’;

Once you have the SQL ID it is a simple matter of running SQLTXTRSBY from the read-write database, as in this example.

SQL> @sqltxtrsby gaz0dgkpffqrr to_standby

The resulting files have most of the useful utilities of SQLT. We can also see that in the execution plan and the SQL text there is a link. I’ve highlighted it below.

Tuning Data Guard
Tuning Data Guard

If you have performance problems on Data Guard, SQLT Xtract doesn’t work, but you do have a tool that will do the job; It’s called XTRSBY. Use the right tool, and get the result you want.
I wish I had. Then I wouldn’t have ruined a perfectly good screwdriver.

What is SQLT?

There’s nothing more annoying than having to struggle to do something because you don’t have the right tools. That’s why I love my swiss army knife and why when working for Oracle, I love SQLT. What is SQLT? Read on and find out.

Victorinox Swiss Army knife, photo taken in Sw...
Victorinox Swiss Army knife, photo taken in Sweden. This is a Mountaineer model. (Photo credit: Wikipedia)

My wife often complains that I’m difficult to buy gifts for, but this year, on my birthday, she chose a winner; a bigger, better Swiss Army Knife. I used to carry a torch, a calculator and a smaller SAK, but now I have a single tool. If there’s one thing I’ve learned in life it’s that using the right tool simplifies the job. That’s not just for DIY. The right tool simplifies any job. A hammer will get a nail in better than a shoe, and a nail gun, well, there’s no comparison. That’s why I was annoyed when I ‘discovered’ SQLT.

Having been a DBA for over twenty years, I’d dealt with hundreds of tuning problems and spent thousands of hours looking at 10046 and 10053 trace files and sorting through tables of statistics. All that time I’d lacked the best tool for the job, the tool that would have made the task simpler and a whole lot faster. Since I’d just begun to work for Oracle, I made the assumption that the tool, called SQLTXplain (or just SLQT) was for internal Oracle use only, but I was wrong. SQLT is available, free, to all supported customers, the problem is, most supported customers don’t know it exists.

Working for Oracle, I use SQLT every day and every day I talk to customers who’ve never heard of it, don’t know what it does, or don’t know how to use it, so I decided to write a book to help solve part of the problem. Some said SQLT could be documented in a single page. A book was unnecessary. Those of us who use SQLT on a daily basis know that’s not true.

Most DBA’s agree that tuning is the trickiest of the DBA’s tasks, whether you’re tuning the database generally or a specific SQL statement. To make matters worse, tuning problems often have to be solved to a deadline. Everything was fine, then, for no apparent reason, everything slowed down and the company is losing millions as a result. It’s just another day in the life of a DBA, but imagine you work in Oracle support, on the team devoted to tuning problems. You deal with problems like that on a daily basis, and often more than one in a day. There’s no way you can support customers if you have to spend hours pouring over reports, so it’s not surprising that it was an Oracle Support engineer, Carlos Sierra, who developed SQLT. The goal was not to be able to fix SQL problems, but to be able to gather all the necessary information in a readily useable form. Half a day with SQLT will usualy provide the explanation you need. You’ll know why your SQL was slow, or you’ll understand why it can’t go any faster.

SQLT isn’t part of the Oracle product set, and it isn’t mentioned in the documentation, but its available, free, to download, and Carlos Sierra, the engineer who created SQLT spends much of his time improving and adapting it. It’s a practical tool, and well worth learning. I can’t imagine life without SQLT, I only wish I’d learned about it long ago.