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.

Why Doesn’t My Hint Work?

Have you ever used a hint as a quick fix and ended up spending far too much time on it, wondering why doesn't my hint work?  Here's what might be going on.

A Honda GX160 5.5 HP. pressure washer in action.
A Honda GX160 5.5 HP. pressure washer in action. (Photo credit: Wikipedia)

It’s spring in New Hampshire and therefore time to start spring cleaning. So, I got out my pressure hose to clean the deck, nothing more than a 10 minute job.  But not when the hose doesn’t fit, the power cable doesn’t reach and the water supply is insufficient. In the end, my simple, 10 minute job turned into a 1 hour marathon.

Tuning some SQL can be like that.

Run the SQL and see an execution plan that doesn’t seem right? Then you may turn to a hint to try and get a quick fix. Using hints is usually a sign that something isn’t right, but they do get used as quick fixes (that sometimes stay until the next version of the product).

As a performance analyst, if I see a hint in a statement I think why does this developer think he knows better than the optimizer?

There are many types of case that require the use of a hint: bad statistics, an optimizer bug, maybe even in house rules to ensure the optimizer keeps to the assigned plan.  Usually, setting the hint is a quick job;  just confirm the plan and you’re away. But what happens when your quick fix doesn’t fix anything? Why doesn’t your hint work?

You need to go looking to see if the hint is being used and if not why not. Let’s look at an example

Here’s my SQL created from the example products that come with Oracle

set autotrace traceonly explain;
set lines 100
select
a.first_name
,a.last_name
,a.hire_date
from
employees a
,employees b
where
a.hire_date = b.hire_date
and a.department_id=30
/

The execution plan looks like this

Execution Plan

———————————————————-

Plan hash value: 2254211361

———————————————————————————————–

| Id | Operation                 | Name             | Rows| Bytes | Cost (%CPU)| Time

———————————————————————————————–

| 0 | SELECT STATEMENT           |                   | 7   | 238 | 6 (17)| 00:00:01 |

|*1 | HASH JOIN                  |                   | 7   | 238 | 6 (17)| 00:00:01 |

| 2 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES         | 6   | 156 | 2 (0) | 00:00:01 |

|*3 | INDEX RANGE SCAN           | EMP_DEPARTMENT_IX | 6   |     | 1 (0) | 00:00:01 |

| 4 | TABLE ACCESS FULL          | EMPLOYEES         | 107 | 856 | 3 (0) | 00:00:01 |

———————————————————————————————–

Predicate Information (identified by operation id):

—————————————————

1 – access(“A”.”HIRE_DATE”=”B”.”HIRE_DATE”)

3 – access(“A”.”DEPARTMENT_ID”=30)

The query uses a Hash Join, but now being much cleverer than the optimizer, I decide there should be a Nested Loop instead so I’m going to use the hint so that the first line in my SQL now changes to

SQL> select /*+ use_nl(a b) */

If I re-run my SQL I get, as expected, a new execution plan

Execution Plan

———————————————————-

Plan hash value: 3321434377

———————————————————————————————–

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

———————————————————————————————–

| 0 | SELECT STATEMENT | | 7 | 238 | 12 (0)| 00:00:01 |

| 1 | NESTED LOOPS | | 7 | 238 | 12 (0)| 00:00:01 |

| 2 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 6 | 156 | 2 (0)| 00:00:01 |

|* 3 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX | 6 | | 1 (0)| 00:00:01 |

|* 4 | TABLE ACCESS FULL | EMPLOYEES | 1 | 8 | 2 (0)| 00:00:01 |

———————————————————————————————–

Predicate Information (identified by operation id):

—————————————————

3 – access(“A”.”DEPARTMENT_ID”=30)

4 – filter(“A”.”HIRE_DATE”=”B”.”HIRE_DATE”)

 

No problems so far.  Everything is going just as planned. The cost is higher, but since I know better than the optimizer I’m ignoring that.

So what happens if I add another hint now?

/*+ use_merge (a b) */.

Everything reverts back to the hash join plan. This is because the use_merge hint and the use_nl hint are incompatible. Please note however, there is no error message.  The SQL just runs. At this point you may say well that’s obvious I wouldn’t do that,  but there are some subtle cases of conflict that are not at all obvious.  For example, what about use_NL(a) and Use_Merge (b), would they conflict? Try it for yourself.

Just like my pressure hose and its connections, checking step by step that each part worked (and finding some that didn’t) you need to check,  don’t assume.  In more complex plans it may not be obvious what happened. So how do you check the status of your hints? In all cases get the 10053 trace:

SQL> alter session set max_dump_file_size=unlimited

SQL> alter session set events ‘10053 trace name context forever, level 1’;

Now look for the 10053 trace and have a look at a little section near the end.  Search for “Dumping Hints”

 

Dumping Hints

===========

atom_hint=(@=OCF2D080 err=4 resol=1 used=0 token=923 org=1 lvl=3 txt=USE_MERGE (“B”) )

atom_hint=(@=OCF2D368 err=4 resol=1 used=0 token=924 org=1 lvl=3 txt=USE_NL (“B”) )

atom_hint=(@=OCF2DIF4 err=4 resol=1 used=0 token=923 org=1 lvl=3 txt=USE_MERGE (“A”) )

atom_hint=(@=OCF2D538 err=4 resol=1 used=0 token=924 org=1 lvl=3 txt=USE_NL (“A”) )

************ WARNING: SOME HINTS HAVE ERRORS ***********

The SQL ran, but the 10053 shows clearly that the optimizer thought it had been asked to do something which was not possible, in this case use_merge and use_nl can’t both be used on the same aliases.

Sometimes you see these errors and you still don’t know what the errors mean, because the hints look fine to you. In these cases read the descriptions in the Performance Tuning Guide and think about what you are asking the optimizer to do.

Just like me and the hose connections. The fitment just couldn’t connect to the tap.  Sometimes what you ask for just isn’t possible.

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.

Tuning Oracle SQL – Just Another Blog?

Author of Oracle Tuning with SQlTXplain. Read about Oracle Tuning here.

This is a blog about Oracle SQL Tuning.

It’s a vast subject and lots of blogs cover it, so what makes this one any different?

My goal, in writing this blog, is to create an organised body of information on tuning Oracle SQL. We’ll talk about concepts like Selectivity and Cardinality, look at tools, tips and techniques and cover things like optimizer parameters in a logical way. I aim to make sure you can always find the knowledge you need, quickly and easily. On the way we’ll spend a fair bit of time talking about one of my favorite tuning tools, SQLTXPLAIN, known to its friends as SQLT.

I love SQLT because it creates order out of chaos. It takes all the random information (which may even seem unrelated) from the SQL tuning world and creates a consistent whole which can then be explored in a structured and repeatable manner.

When you set out to solve a tuning problem you’re just like a doctor approaching a patient. The patient complains of an illness, but you have a whole body to examine, so you start with the basics.

Is the body actually ill or is it complaining unnecessary?
Are there any signs or symptoms which point to an underlying problem.

When it comes to Oracle SQL tuning, SQLT is the body and tuning problems are the disease or underlying condition.

Doctors learn by watching more experienced doctors as they examine patients, order tests and reach a diagnosis. You can learn about SQL Tuning in the same way. You’ve all learned how to collect trace files, get execution plans and so on, but just like a junior doctor you need to spend time on example cases; to see how the patient looks arrive at a diagnosis and work out a ‘cure’.

So, if you have an interesting patient (in the form of a SQLT report) send it to me! I’ll pick at least one every month and review it online as an example. I won’t reveal any data, except as is absolutely necessary to explain the case.

So that’s the aim of this blog, and that’s why this blog is different from all the other blogs on Oracle SQl Tuning. If you’re still reading, but you’re not sure where to begin, I’d suggest you start with What is SQLT. Thanks for reading!