DISTRIBUTED QUERY ANALYSIS

Abstract

This paper will present how queries involving database links  are processed.  We  will look  at  four cases:  (1) query against a single remote table, (2) a join involving one local table and one remote table, (3) a join of two tables located on the same remote database, and (4) a join of two tables existing on  two  separate remote databases.

Introduction

The method Oracle uses to process distributed queries is  one  of  the  less   understood   features  offered    by   Oracle.   This presentation will explain some of the things that  happen  behind the  scenes  of a distributed query and will give some techniques an application developer can  use  to  write  better  distributed queries.

SQL*Net Demystified

In order to perform a distributed query, an RDBMS needs to have a means of communicating with other databases.  SQL*Net is Oracle's remote  data  access  protocol   and   application   programmatic interface  (API)  that runs on top of existing network protocols.  Its main function is to allow a  front-end  application  such  as SQL*Forms  to  run  on  one  computer while accessing Oracle data which resides on a database either on the same machine  or  on  a remote machine. 

Architecturally, the front-end tool is responsible for generating SQL statements depending upon user input (eg user fills in fields in a form and then hits the  INSERT  key)  or  application design (eg   embedded  SQL  in a C program).  We also refer to this as a client process.  The Oracle  back-end  or   server   process   is responsible for parsing and executing SQL statements as optimally as possible.  Once processed, the results of  the  statement  are returned  to  the  front-end  tool.   In  the  case  of  a SELECT statement, the server  process  retrieves  a  record  or  set  of records  from  the  datafiles  and  sends  them  the  client upon request.  The  front-end  tool  then  decides  how  the  data  is presented  to the user.  This leaves SQL*Net with the deceptively simple tasks of managing  a  communication  session  between  two computers, transporting data between two processes, and providing any necessary character set conversions.   Since  SQL*Net  itself has  no role in the decision making process of executing a query, one can say that this paper is actually an RDBMS topic in SQL*Net clothing!

The Sample Environment

For the purposes of this paper, we will use a sample  environment consisting of:

• three computers running an OS such as Unix (identified as unix1, unix2, and unix3) connected via some networking protocol such as TCP/IP.
  
• an Oracle database running on each machine (identified as instance A, B, and C respectively) 
• an Oracle database user account of scott/tiger on each database

We will assume that our user, scott, is already logged onto unix1 and has initiated a SQL*Plus session with database A.  Theoretically, it should be possible for the three  nodes  to  be running  any  of the operating systems supported by Oracle and be able to communicate with any supported networking protocol.  Some environments,  however,  will  present some minor limitations not covered in this paper.   For example,  DOS cannot act as a server to other machines.

Database Links

In order for two Oracle  databases  to  share  data,  one  Oracle instance  temporarily  acts  as  a  client  to  the  other Oracle instance.  This action  is accomplished  through  database  links stored  in  the  initiating  database.  The syntax for creating a database link is as follows (optional clause enclosed by []):

  CREATE DATABASE LINK dblinkname
        [CONNECT TO user IDENTIFIED BY password]
        USING 'connect_string'

For our example, we initially  create  two  database  links  from unix1 to unix2 and unix3 as follows:

       CREATE DATABASE LINK lax
           USING 'T:unix2:B';

       CREATE DATABASE  LINK syd
           USING 'T:unix3:C';
 
    SQL*Net V2 based DBLINKs using a V2 service name example:
 
       CREATE DATABASE LINK lax
           USING 'v2servicename';
                  -or-
         CREATE DATABASE LINK lax
           USING 'TNS:v2servicename';

Simple Remote Queries
 
Before we look at what happens with a complex  distributed  query involving  joins of tables on  sereral remote databases, it helps to consider what the mechanics are behind a simple query using  a database link.  We will use the following example SQL statement: 

    SELECT MAX(ename), 'dept # =' || deptno
        FROM emp@syd
        WHERE deptno BETWEEN 10 AND 100
        GROUP BY deptno
        ORDER BY 1;

The local database on unix1 will process the query and  send  the following to the database on unix3: 

    SELECT ename, deptno
        FROM emp
        WHERE deptno >= 10 AND deptno <= 100;

As the records are fetched into unix1, it performs the  group  by applies  the MAX() function to each group then orders what it has and sends those results back to its client.  This implies several important points about how a remote query is handled  by  Oracle  V6.   Some  are not easily implied but noted

below:
 

     1. Aggregate functions like MAX(), COUNT() are NOT passed across the net but rather are performed on the local node.
     2. Expressions in the SELECT list are decomposed into columns and evaluated when the data is retrieved.
     3. Only a relevant subset of columns are fetched across the net.
     4. An expression in a WHERE clause is passed across to the remote database if it compares the remote column to a literal (eg ename = 'KING').
     5. Expressions in a WHERE clause are passed to the remote database if all columns are in the expression are located in the same remote table the remote database (eg emp.sal * 0.20 > emp.bonus)
     6. Datatype conversion functions like TO_CHAR() in a WHERE clause are subject to the conditions in #4 and #5.
     7. The optimizer only has knowledge of local indexes when making decisions about how to execute a query.
     8. Remote indexes can be still be used to resolve the decomposed query sent to the remote database.
     9. GROUP BY is not sent to the remote database.
    10. ORDER BY is not sent to the remote database.

 
Simple Distributed Joins

With joins that involve tables on a remote database and  a  local or  second remote database, the points mentioned in the case of a simple remote query are still true.  What happens to  allow  this is  the decomposition of a join into separate independent queries whose results are then joined on  the  local  node.   Here  is  a simple example of how a join is decomposed.
 

    SELECT ename, dname
        FROM emp@syd e, dept@lax d
        WHERE e.deptno = d.deptno
        AND e.job != 'CLERK'
        AND d.loc = 'NEW YORK';

becomes:

unix3:   SELECT ename, job, deptno
             FROM emp
             WHERE job <> 'CLERK';
 
unix2:   SELECT dname, loc, deptno
             FROM dept
             WHERE loc = 'NEW YORK';

Note that, at least in Oracle V6, the  same decomposition  occurs even if both tables exist on the same remote node.
 
Putting It All Together

With these facts in mind, it is easy to see how the use of  views can help in cases where it is clearly better for a function to be performed on a remote node,  rather  than  the  local  one.   For example,  instead  of  issuing  a  count of rows against a remote database and having the data returned to be  counted  locally,  a remote  view  could  be  queried  to return only the count value. This increases performance by reducing network traffic.

Example:

    SELECT COUNT(*)
        FROM emp@syd
        WHERE deptno = 10;

becomes...
 

unix3:

    CREATE VIEW empcount (numemps,  deptno) AS
        SELECT COUNT(empno), deptno
            FROM emp
            GROUP BY deptno;

unix1:

    SELECT numemps, deptno
        FROM empcount@lax
        WHERE deptno = 10;

Views can also be used to help control the node  where  the  join will  actually  take  place.   In the case where there is a small table on the local  database  and  a  large  table  on  a  remote database,  it  is  usually  better to have the smaller table sent across the network to be processed  remotely  than  retrieve  the large  table  and perform a local join.  This method requires the existence of database links from each of the  three  machines  to the other two.
 
Example:

    SELECT ..
        FROM small, big@syd
        WHERE small.key=big.key;

becomes...
 

unix2:

    CREATE VIEW bigsmall AS
        SELECT ..
            FROM small@mia, big
            WHERE small.key=big.key;
 
unix1:

    SELECT ..
        FROM bigsmall@syd;

When both tables being joined are on the same  remote  node,  one can  use  a view to make sure the join is performed on that node. At times, the above scenario exists in a subtle  form.   Consider the following query:
 

    SELECT *
        FROM A@mia a, B@lax b, C@mia c
        WHERE a.x = b.x
        AND b.x = c.x;

Upon examining the WHERE clause, one  sees  that  the  transitive expression  of "a.x = c.x" can also be generated. This shows that tables A and C can be joined at the same remote node rather  than being joined at the local node.  The predicates taken directly do not always show that a remote join is possible.
 
In the case where both tables are on separate databases  one  can create  a view on the machine where the join may be performed the quickest.  While deciding this, one can consider which  node  has the largest table, the fastest CPU, or the lightest load.
 
If views are not  possible,  it  may  be  possible  to  fool  the optimizer  into choosing a different path that may be faster than what the rules would predict.  For example if one were joining  a subset of a local small dept table with a large remote emp table, a nested loops join may return faster than a sort merge.  Here is an example.

unix1:

    SELECT e.ename, d.dname
        FROM emp@hq e, dept d
        WHERE d.loc IN ('BUTTE', 'FLAGSTAFF')
        AND e.deptno = d.deptno;

   
In the above query, the ename and deptno columns of the whole emp table  will  be  pulled  over  the  net to have it's deptno value compared with the department numbers of  departments  located  in Butte,  Montana  and  Flagstaff, Arizona.  If the total number of employees working in those cities are a small percentage  of  the total  employees working worldwide, then much network traffic and IO was wasted  reading  and  sending  data  that  would  just  be rejected. 

If emp had an index on the deptno column, it would  be  ideal  to just  have  it  make an index scan for rows that have departments located in the two cities we want  and  just  return  those  rows back.   The following query will persuade the optimizer to choose that execution path. 

unix1:
    SELECT e.ename, d.dname
        FROM emp@hq e, dept d
        WHERE d.loc IN ('BUTTE', 'FLAGSTAFF')
        AND e.deptno <= d.deptno
        AND e.deptno >= d.deptno

Note that the last two clauses taken together have the same logic as  a  straight  equality.   However, the optimizer analyzes each clause and concludes that it will have to do a  full  table  scan for each dept.deptno. 

In this case, Oracle performs a nested loops join and  sends  the following query to the remote database:
 

    SELECT ename, dept
        FROM emp
        WHERE deptno >= :1
        AND deptno <=: 2;

It then loops through the appropriate rows  in  dept,  binds  the deptno values and executes the remote query. 
One can also use the TKPROF and EXPLAIN utilities as another  way to  see  how  queries are processed.  To enable these facilities, one may turn on global tracing by setting sql_trace = true in the init.ora  file.   Then one may simulate a distributed database by creating and using database links that loop back to the  database where  the  query  originated.   This  will create multiple trace files -- one  for  the  "local"  session  and  another  for  each "remote"  connection  query.  If one had to, one could copy trace files from a remote machine and use the  EXPLAIN  parameter  over SQL*Net with the command "tkprof ..  EXPLAIN=user/pswd@remote."

Note 1004553.6 DISTRIBUTED QUERY ANALYSIS.

* 여기저기서 좋다는 책 몇권 추천합니다. 고수가 되는 그날까지.. 파이팅!!