SQL Tuning
SQL (structured query language) is the industry standard for relational databases programming. Oracle has two versions of it implemented in its database, as do most of the major vendors. One is the standard SQL and the other is their procedural version, which for Oracle is called PL/SQL (Procedural Language SQL). This the code that the users either build themselves, or the program that is running on top of the database (the application) builds for them. The difference between a good query and a bad query is how long it takes. A bad query just takes longer. And a long query makes for a frustrated user. This is not good. A basic query almost reads like a regular English sentence. Let's say that I wanted to select an employee by name, get the department that he is working in and what his skill is, a SQL statement could look like this:
Select name, department, skill (these are the columns in the database table)
from EMPLOYEE (this is the name of the database table)
where name = 'SMITH, JOHN' (this is the qualifier so we don't get everyone)
/
Tuning a SQL query is about getting the fastest response from the database possible. My personal best for improving the response of a query was to take one that was running for over 5 hours and with appropriate tuning, getting it to run in 0.7 seconds. Which for you number crunchers is an improvement of over 25,000%. That's not bad!
The secret to tuning is to know how the Oracle database operates. Like all computers programs, it operates on rules. Given a certain situation it will do this, given a different situation, it will do this. As a database administrator, it is our job to know what Oracle will do for any given situation and be able to adjust the code that we write to take advantage of the way we know that Oracle works.
An example will probably help in this case. We are looking for all potential employees who work in the warehouse and have a journeyman's ticket that may not be utilized by the company in one of the 4 categories that we need.
select e.name, f.department, o.skill
from employee e, occupations o, factory f
where f.location = 'WAREHOUSE'
and o.level = 'JOURNEYMAN'
and o.skill in ('WELDER','ELECTRICIAN','STEAMFITTER','PLUMBER')
and e.name = f.name
and e.name = o.name
/
The way that tuning is done is for the DBA to realize that, for example, what Oracle does when it is looking through all the tables is that it actually starts at the last table and works its way back.
-
So first it is going to look in the factory table (it's the last one in the FROM list) and find all occurrences of warehouse in the table. The little e o and f behind each of the names is what they call an alias and is just shorthand so that when we do the select portion and the where portion of a query we just type the table name and column name as e.name instead of employee.name.
-
Then it is going to look at the occupations table and find all qualifying values from the WHERE clause. In this case it is the 4 skills that the company is looking for: welder, electrician, steamfitter and plumber as well as their skill level, journeyman.
-
And then finally it will look in the employee table to find all the names that match these criteria.
What you probably don't realize, and this is what makes the difference, is that we want this first table that is called to be the one that will return the fewest results.
How many people are going to be located in a place called a warehouse? I am willing to be a lot.
How many people will have a skill level of journeyman? Not that many.
So our better tuning option already is going to be to adjust the order of the tables that are going to be called. Like this:
WAS THIS: from employee e, occupations o, factory f
IS NOW: from employee e, factory f, occupations o
Next we have to look at the WHERE clause and check the order of the statements. Oracle prefers to work from the top down, equality statements (=) statements first. as such, we will change the one IN statement placement to be at the bottom of the list instead of in the middle.
WAS THIS:
where f.location = 'WAREHOUSE'
and o.level = 'JOURNEYMAN'
and o.skill in ('WELDER','ELECTRICIAN','STEAMFITTER','PLUMBER')
and e.name = f.name
and e.name = o.name
IS NOW:
where f.location = 'WAREHOUSE'
and o.level = 'JOURNEYMAN'
and e.name = f.name
and e.name = o.name
and o.skill in ('WELDER','ELECTRICIAN','STEAMFITTER','PLUMBER')
This, in testing, will make the query run faster.
The final step is seeing whether there are any indexes on the tables involved that will speed up searching for the required information.
Your telephone book is indexed. Imagine if all the entries in the telephone book were simply put into the book in the order that the telephone company received them. So the people that moved to Edmonton in the 50's and 60's will be in the first 100 pages, and the people that just got a phone in the last month would be on the last page. You need to phone someone. Where do you look? Exactly! Fortunately our telephone books are indexed in alphabetical order. You can get them indexed by street addresses or telephone numbers if that would be of use to you as a tele-marketer, but for most of us the last name alphabetical seems to work.
So it is with your Oracle database tables. You can have multiple indexes on the same table depending on the kind of searching that you are doing, a different order will be of more use to you in one situation than in another.
Want More Information? Please contact us: Ask for more Info