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.
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.