How quickly can you diagnose and resolve performance issues in your Oracle database?

In an on-demand webcast, Bert Scalzo — Oracle ACE and technology innovator on the Toad development team — demonstrates multiple methods for discovering, visualizing and optimizing problematic SQL using Toad for Oracle.

During the initial live presentation of the webcast, Bert received many more follow-up questions than he could respond to before the webcast concluded. He and the Toad development team have updated the Toad World FAQs with written answers to more than 60 questions about installation, configuration, SQL construction tips, SQL execution plans, optimization and licensing.

I’ll highlight some of the FAQ in this series of posts. This time, the topic is . . .

SQL construction tips

Q: Will optimizations work with SQL statements embedded in a programming language?

A: Toad offers a cool feature called make/strip code. It allows you to copy code from, say, C# or Java into Toad, turn it into simple SQL, optimize it, then send it back to the original format for inclusion in the programming language. In fact, we eat our own dog food here: We code in Delphi, load all our SQL embedded in Delphi into Toad to optimize it, then put it back into the Delphi code.

Q: What about qblock in usage?

A: It should be there in the Display options. If not, then post a Toad World Idea Pond recommendation to add it.

Q: Can we get your query examples in plain text?

A: Most of them are very simple. I think the one for refactoring is worth sharing:

select e1.empno

from scott.emp e1

where e1.deptno in (select d1.deptno

from scott.dept d1

where d1.deptno in (10,20,30)

and e1.deptno in (select e2.deptno

from scott.emp e2

where e1.job='CLERK'

and e2.deptno in (1,2,3)

and d1.deptno in (4,5,6)))

and e1.deptno in (select d1.deptno

from scott.dept d1

where d1.deptno in (10,20,30)

and e1.deptno in (select e2.deptno

from scott.emp e2

where e1.job='CLERK'

and e2.deptno in (1,2,3)

and d1.deptno in (4,5,6)));"

Q: Are there recommendations for using EXISTS vs. IN? NOT IN vs NOT EXISTS?

A: If we ignore readability (i.e., we assume all our SQL developers can do either with equal skill), then using the EXISTS and/or NOT EXISTS is preferable in most cases. But I find that many programmers simply cannot work with the EXISTS and NOT EXISTS as easily as the IN and NOT IN. If the tables are small, then it really does not matter.

Q: Are there recommendations for using co-related sub-queries vs. using simple sub-queries?

A: There are no recommendations because they solve different problems. There are some guidelines for using a sub-query vs. a join, but I find that the guidelines are rarely applied as intended and the readability is often more important, since SQL Optimizer for Oracle often does this internally anyhow since Oracle 11g. But here's the advice for what it's worth (use with caution): When the number of rows from the sub-query will be small compared to the main table, use the sub-query; otherwise, do the join.

Q: How about when we use "WITH" and have about 1000 lines of code?

A: Not a problem now. There were some problems with long SQL and lockups in older versions, but I've worked hard with the SQL Optimizer team during the Toad 12.1 and 12.5 dev cycles to fix this. The same goes for Advanced SQL Optimization hanging up with some code due to multiple WITH clauses. There were some scenarios where it would lock up either at the start or at some percentage complete. I think we have this fixed now in 12.1.1 and/or 12.5. I'd recommend 12.5 just to be sure/safe.

Q: What is the benefit of doing a CASE statement instead of DECODE?

A: First, DECODE is a relic from the old days when there was no CASE statement. Second, CASE is the ANSI standard, whereas DECODE is proprietary to Oracle. Finally, CASE is part of the standard SQL processing, whereas DECODE is a function call. Function calls are inherently less optimal; for example, the database optimizer won’t use index on a column within a DECODE call unless there’s a function-based index defined for that specific expression. Plus, a function call means a possible context swap or switch. The run-time difference may be negligible to radical - it all depends. In short, DECODE is the 1990s way of doing things vs. the current method.

Q: Does Toad have a 'convert to ANSI' function?

A: Yes. In Editor, right-click and choose Refactor: convert to/from ANSI join. The query builder also offers this functionality. For example, you can send Editor contents to the query builder to "see" your query. It will convert this automatically for you, then you can always send it back to Editor.