Oracle Performance Tuning - Part 2

Wednesday Sep 28th 2005 by Steve Callan
Share:

There are several relatively easy steps you can take to improve performance. From the user's perspective, one of the most frequently used interfaces with a database involves SQL statements, so getting a handle on them is a good place to start, in terms of being able to see an immediate improvement.

As mentioned in Part 1, there are several relatively easy steps you can take to improve performance. From the user's perspective, one of the most frequently used interfaces with a database involves SQL statements, so getting a handle on them is a good place to start in terms of being able to see an immediate improvement.

In the interest of being complete, I will cover some preliminary steps that will be needed in order to view what is taking place. These steps include running the plustrce SQL script, creating an "EXPLAIN_PLAN" table, granting a role, and configuring your SQL*Plus environment to see execution plans. All of these steps are covered in "Using Autotrace in SQL*Plus" in Oracle9i Database Performance Tuning Guide and Reference Release 2 (9.2). For Oracle10g, the steps are covered in "Tuning SQL*Plus" in SQL*Plus® User's Guide and Reference Release 10.2.

Preliminary Steps

If the PLUSTRACE role does not exist, create it using the PLUSTRCE SQL script found in ORACLE_HOME\sqlplus\admin. The script is pretty simple:

drop role plustrace;
create role plustrace;
 
grant select on v_$sesstat to plustrace;
grant select on v_$statname to plustrace;
grant select on v_$mystat to plustrace;
grant plustrace to dba with admin option;

Check for the role using:

SQL> select role from dba_roles where role = 'PLUSTRACE';
 
ROLE
----------------
PLUSTRACE

The user must have (or have access to) a PLAN_TABLE (it can named something else, but for now, the "default" name is fine). This table is created using the UTLXPLAN SQL script found in ORACLE_HOME\rdbms\admin.

SQL> show user
USER is "SYSTEM"
SQL> @?\rdbms\admin\utlxplan
 
Table created.
 
SQL> create public synonym plan_table for system.plan_table;
 
Synonym created.
 
SQL> grant select, update, insert, delete on plan_table to <your user name>;
 
Grant succeeded.
 
SQL> grant plustrace to <your user name>;
 
Grant succeeded.

The user for these examples is HR (found in the sample schemas provided by Oracle).

SQL> conn hr/hr
Connected.
SQL> set autotrace on
SQL> select * from dual;
 
D
-
X

With autotrace set to on, you can confirm your ability to see an execution plan and some statistics. You should see output similar to the following:

Execution Plan
----------------------------------------------------------
   0    SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=1 Bytes=2)
   1    0 TABLE ACCESS (FULL) OF 'DUAL' (TABLE) (Cost=2 Card=1 Bytes=2)
 
Statistics
----------------------------------------------------------
         24  recursive calls
          0  db block gets
          6  consistent gets
          1  physical reads
          0  redo size
        389  bytes sent via SQL*Net to client
        508  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

To suppress the results of the query, use "traceonly" in the set statement.

Using Bind Variables

On any number of DBA help type of Web sites, a frequently seen bit of advice is to use bind variables, but rarely are the steps or instructions for this step included. Here is a simple way to create and use a bind variable.

SQL> variable department_id number
SQL> begin
  2  :department_id := 80;
  3  end;
  4  /
 
PL/SQL procedure successfully completed.
 
SQL> print department_id
 
 
DEPARTMENT_ID
-------------
           80

Now let's make a comparison between querying for employee ID and name with and without the bind variable (with the output turned off using traceonly).

Now let's use the bind variable.

Okay, so the difference isn't that great (the cost went from 3 to 2), but this was a small example (the table only has 107 rows). Is there much of a difference when working with a larger table? Use the SH schema and its SALES table with its 900,000+ rows.

SQL> select prod_id, count(prod_id)
  2  from sales
  3  where prod_id > 130
  4  group by prod_id;

Same query, but this time using a bind variable.

SQL> variable prod_id number
SQL> begin
  2  :prod_id := 130;
  3  end;
  4  /
 
PL/SQL procedure successfully completed.
 
SQL> print prod_id
 
 
   PROD_ID
----------
       130
 
SQL> select prod_id, count(prod_id)
  2  from sales
  3  where prod_id > :prod_id
  4  group by prod_id;

The cost went from 540 to 33, and that is fairly significant. One of the main benefits is that the query using the bind variable, that is, the work done parsing the query, stays the same each and every time. All you have to do is substitute a new value for the variable.

Use Efficient SQL

Suppose you have a choice between the following two queries (using the HR schema again):

Query 1

select d.department_id, 
 d.department_name, 
 r.region_name
from departments d, 
 locations l, 
 countries c, regions r
where d.location_id=l.location_id
and l.country_id=c.country_id
and c.region_id=r.region_id;

and

select department_id, 
 department_name, 
 region_name
from departments natural join locations
natural join countries natural join regions;

This leads to four questions.

1.  Are these queries querying for the same result set?

2.  If they are the same, would you expect any difference in their execution plans?

3.  If the plans are the same, what is it that makes these queries different?

4.  Can anything be done to improve the cost?

The answer to the first question is yes, they are the same. The answer to the second question is no, not really, because the same steps are involved in terms of joining tables. The answer to the third question has to do with the amount of typing or coding involved.

The use of the "natural join," "join on" and "right/left outer join" keywords is what matters in this example. If you understand what a natural join is (still joining two tables, but the column names involved are the same), doesn't it look easier to use the second query?

The proof of the answer to the second question is shown below.

Query 1's Execution Plan

Query 2's Execution Plan

As for the answer to the last question, efficient SQL can mean different things to different people. In this case, what about using a view? Will the cost be any different from either of the original queries (you can see for yourself what the answer is), or are there other considerations to take into account?

Suppose we have a view named cost_example, created as follows:

create or replace view cost_example
as
select department_id, department_name, region_name
from departments natural join locations
natural join countries natural join regions;
 

Let's look at a record in the view.

SQL> select department_id, department_name, region_name
  2  from cost_example
  3  where department_id=70;
 
DEPARTMENT_ID DEPARTMENT_NAME                REGION_NAME
------------- ------------------------------ ------------
           70 Public Relations               Europe
 

Out of the three columns or fields, can any of them be changed? If so, why? If not, why not?

Let's suppose the region name is now Asia instead of Europe.

SQL> update cost_example
  2  set region_name = 'Asia'
  3  where region_name = 'Europe';
set region_name = 'Asia'
    *
ERROR at line 2:
ORA-01779: cannot modify a column which maps to a non key-preserved table

Can the department name be changed?

SQL> update cost_example
  2  set department_name = 'PR'
  3  where department_name = 'Public Relations';
 
1 row updated.

The reason why the record in the view can be updated (the department name, anyway) is that DEPARTMENTS is a key-preserved table (its primary key DEPARTMENT_ID was used in the creation of the view).

The point of this example is this: just because you obtain the lowest cost does not mean you cannot do anything else to make a query better. Better, in this case, applies to developers using simpler join constructs, and applies to users in that providing views for their use saves you the effort of having to explain how to do complex joins. The caution on views is to keep track of key-preserved versus non key-preserved tables so that what you intend to be modifiable is indeed just that.

In Closing

The main points of this article are:

  • Use bind variables
  • Use efficient SQL
  • Use coding standards
  • Consider the technical or SQL know-how of your user population and create views as appropriate

None of these steps is especially difficult to perform or implement. For programmers used to using the "tableA.column_name = tableB.column_name" format for joins, moving to the use of natural joins saves quite a bit of typing, plus there is the benefit of having key column names match up (the foreign key column in the child table has the same column name as the primary key in the parent table). As shown, some measures may not have a big impact, but when taken as a whole, every little bit helps to improve performance. In Part 3, we will look at more examples of steps you can take to improve performance.

» See All Articles by Columnist Steve Callan

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved