Quest Sql Tuning For Oracle

[Chris Domino]

Viewed50K+ times! This question is You Asked I am new in tuning sql statements. Can u give a methodology of tuning

the sql statements.

and Tom said...Here is a short extract from a book I am working on. The short answer is

if you want a 10 step guide to tuning a query, buy a piece of software. You are not needed in this process, anyone can put a query in, get a query out and run it to see if it is faster. There are tons of these tools on the market. They work using rules (heuristics) and can tune maybe 1% of the problem queries out there. They APPEAR to be able to tune a much larger percent but that is only because the people using these tools never look at the outcome -- hence they continue to make the same basic mistakes over and over and over.

If you want to really be aboe to tune the other 99% of the queries out there, knowledge of lots of stuff -- physical storage mechanisms, access paths, how the optimizer works - thats the only way.

of course, read:

_01/doc/server.817/a76965/toc.htm from cover to cover and _01/doc/server.817/a76992/toc.htm

as well

1.1 Efficient SQL

This was probably the hardest part of the book to write - this chapter. That is not because the material is all that complex, rather because I know what people want - and I know what can be delivered. What people want: The 10 step process by which you can tune any query. What can be delivered: Knowledge about how queries are processed, knowledge you can use and apply day to day as you develop them.

Think about it for a moment. If there were a 10 step or even 1,000,000 step process by which any query can be tuned (or even X% of queries for that matter), we would write a program to do it. Oh don't get me wrong, there are many programs that actually try to do this - Oracle Enterprise Manager with its tuning pack, SQL Navigator and others. What they do is primarily recommend indexing schemes to tune a query, suggest materialized views, offer to add hints to the query to try other access plans. They show you different query plans for the same statement and allow you to pick one. They offer "rules of thumb" (what I generally call ROT since the acronym and the word is maps to are so appropriate for each other) SQL optimizations - which if they were universally applicable - the optimizer would do it as a matter of fact. In fact, the cost based optimizer does that already - it rewrites our queries all of the time. These tuning tools use a very limited set of rules that sometimes can suggest that index or set of indexes you really should have thought of during your design.

I'll close this idea out with this thought - if there were an N step process to tuning a query, to writing efficient SQL - the optimizer would incorporate it all and we would not be having a discussion about this topic at all. It is like the search for the holy grail - maybe someday the software will be sophisticated enough to be perfect in this regards, it will be able to take our SQL, understand the question being asked and process the question - rather then syntax.

To me - writing efficient SQL requires a couple of things:

o Knowledge of the physical organization of what I'm asked to query against. That is - the schema. Knowledge that the physical organization was actually designed in order to help me answer my frequently asked questions (refer back to the chapter on designing an efficient schema for advice in that arena)

o Knowledge of what the database is capable of doing. If I did not know about "skip scan indexes" and what they did (we'll cover them below) - I might look at a schema and say "ah hah, we are missing an index" when in fact we are not.

o Knowledge of all of the intricacies of SQL - from the lowly "WHERE" clause on up to analytics and psuedo columns. Knowledge of what using a particular construct will do to my runtime processing.

o And most importantly of all - a solid understanding of the goal, of what the question is. Tuning a query or process is really hard (impossible I would say) - unless you understand the question in the first place. I cannot tell you how many times I've not been able to tune a query until I had the question in hand. Certainly you can derive a question from a query - however, many times that derived question is much more confining then the real question being asked. For example, many people use outer joins in all queries - they are "afraid" of losing a row (perhaps they got "burned" in some past experience and now use outer joins everywhere). If the objects are related in a one to one mandatory fashion - we don't need an outer join at all. The question derived from the query is much more confining then reality.

That last topic or point is so important, I'll close out this section with it. In this chapter we'll cover the topics of what the database is capable of doing in general - looking at many of the access paths and join operations available to us. We'll look at what SQL is capable of doing - not by discussing the entire language, that in itself is a book. Rather, we'll look at a couple of things that will whet you appetite - show you how powerful this language can be, how much more than just "SELECT" "FROM" "WHERE" and "ORDER BY" there is. Then we'll close up with a look at that most important topic - why understanding the question is more important then having a query at hand to tune.

So, this section will not provide you with the N steps you need to follow in order to tune a query or write the best queries in the world. For every rule of thumb out there anyone has ever shown me regarding writing "efficient SQL", I've been able to come up with a slew of common (not esoteric) counter cases to prove that rule of thumb is wrong in as many cases as it is right. I've talked to people who swear "NOT IN" is fatal, never use it - always use NOT EXISTS. Then I show them NOT IN running a query 10 times faster then NOT EXISTS. I talk with people who feel NOT EXISTS is the worst construct on the planet - you must use IN. Then I do the same - showing them how NOT EXISTS can run many times faster then IN.

.....

Rating (891 ratings)

Is this answer out of date? If it is, please let us know via a Comment Comments Comment SQL TuningRakesh Dudhia, March 24, 2003 - 12:47 pm UTC

I am looking for a simple tool that can be used as an advisor for tuning my SQL statements, especially the slow ones. I have worked with a tool called Lecco by Leccotech, but it seems like it no longer exists.

It is not another SQL Tuning tool There have been already a lot of SQL tuning products in the market providing better query plan visualization, better statistics analysis, high cost query plan steps indication or even rule-of-thumb syntax...

Tosska SQL Tuning Expert (TSE) is a SQL tuning tool that optimizes your SQL statements without the need of users involvement. The product will give you the ultimate SQL performance solution by just point and click. What you have to do is to input your problematic SQL statement into the product and press a button.

Oracle Enterprise Manager OEM12c with diagnostic and tuning packs is the best tool for the job. The SQL tuning advisor provides re-writes of the SQL based on the system load which you can implement or save as a profile to replace the bad SQL every time it is executed.

As AlanM says - statspack is free. Others have recommended OEM or SQL Developer, but please be aware that AWR reporting requires a separate license which can only be purchased for Oracle Enterprise editions.

Why pay when you can have it for free?

Why use a 3rd party tool when you can have the original?

The answer to these 2 questions is ORACLE SQL DEVELOPER, which is free to use and download.

Give it a try, you will be surprised.

For some time now, we've been running Quest's ActiveRoles product for administering our Active Directory, and it's been generally fine. However, one area where we're hitting significant performance issues is with their QuickConnect module, where you can create scheduled tasks to update multiple AD objects based on data sources outside AD.

This isn't a pure database tuning question, since in database terms I know exactly why the performance is lousy - a task for mapping 658 user names in an OLEDB data source to associated AD user objects is creating huge numbers of records in an already large table (StoredRequests table, 340 users mapped equating to approx 27,000 new storedrequests records), and the application's placing a very heavy reliance on cursors. However, I obviously can't change the way the application is written, so the DML thrown at the database is what it is. What I'm far more interested in is whether anyone else here has had experience of administering a SQL db that underpins an ActiveRoles environment, and how they've got on.

For reference, we're currently running ActiveRoles v5.2.5 against a SQL 2000 server, and are waiting for Quest to get rid of some of the more virulent bugs in ActiveRoles v6x before we upgrade (allowing us to migrate the db to SQL2k5).

So you can sync the data from your database, you can choose from (MS SQL, MySQL, DB 2, Oracle, still have OLEDB, ODBC) and directly sync to AD.. without Active Roles in the middle.. because the Version is too old...

3a8082e126