 |
|
Oracle Tips by Burleson |
Troubleshooting Distributed
Oracle Database Queries
Tuning distributed Oracle databases is
fundamentally different than tuning a single Oracle database.
A single SQL query may touch many Oracle database instances,
and because of this the optimizer resolution becomes more complex. Tuning an SQL statement involves extracting the execution plan for the
SQL. This is a much more difficult task in distributed Oracle
databases.
The main areas for troubleshooting and tuning
distributed queries include:
* Performance issues with distributed
queries
* Creating cross-databases execution plans
* The problem of remote joins
* Determining the driving site and driving
table for cross-database queries
* Tips for faster cross-database queries
Let’s start with a discussion of the
performance issues that happen when an SQL query spans several Oracle
databases.
Performance issues with distributed
queries
The phenomenal challenge of optimizing SQL
gets even harder when the query spans Oracle databases. The
Oracle database is forced to “break-up” a distributed SQL statement
into several components and ship each query to the remote Oracle
database for execution. The Database must then gather the result
set from each remote database and prepare a single result from the
query.
The physical challenge for optimizing
cross-database SQL is compounded by these issues:
* Unknown remote database characteristics
– The database involved in a distributed query may be vastly different
from each other. For example, you can join an EMP table on a
giant IBM Mainframe with a SALARY table that resides on a tiny Windows
server. The optimizer cannot always compute the relative expense
of an operation on a remote server.
* Hard to see the entire distributed
execution plan – As we shall see, a remote execution plan display
will only show the SQL that was sent to the remote instance for
execution. To see the execution plan for the remote instance, we
must go to that instance and execute the sub-component to get the
execution plan.
* Oracle*Net has no knowledge of network
latency – Oracle cannot factor-in the costs of network
transmission time between the instances. Hence, Oracle may
choose a sub-optimal execution plan, resulting in excessive network
traffic and slow results. However, Oracle has some safeguards against
this problem. For example, if all the objects in a query are
remote (and use the same database link), Oracle will attempt to send
the complete query to the remote database for processing.
* No cohesive SQL execution - If some
tables in an SQL query are remote and some are local, then Oracle will
break up the query into individual SQL statements and pass the SQL to
the remote database for independent execution. If one database
is very slow, the whole query will wait.
Now that we see the issues, let’s see how to
display cross-database execution plans.
Creating cross-databases execution plans
As we know from , Oracle develops an execution
plan for every SQL statement prior to starting the execution. In
a single Oracle database, determining the execution plan is
straightforward because a single Oracle data dictionary contains the
object statistics.
Execution plan for distributed queries are
different. The “set autotrace on” command does not work for
distributed queries, and you must run the “explain plan” syntax to
populate your plan_table and run remote_plan.sql to display the remote
execution plan. Please copy this code onto your PC as
remote_plan.sql:
REM
remote_plan.sql
set long
2000
set arraysize 1
col
operation format a22
col options format a8
col object_name format a10
col object_node format a5
col other format a20
col position format 99999
col optimizer format a10
select
lpad(' ',2*(level-1))||operation operation,
options,
object_name,
optimizer,
object_node,
other
from
plan_table
start with id=0
connect by prior id=parent_id
;
Note that this differs from the standard SQL
execution plan display because of the inclusion of the OTHER column in
the query. For distributed queries, Oracle uses plan_table.other
column to show the remote queries that were sent to the remote
instance for execution.
For example:
connect
pubs/pubs@pubs
truncate
table plan_table;
explain plan
for
select
initcap(book.book_title) c1,
book_retail_price c2,
publishing_qty
c3
from
book
natural join
book_details@newpubs
order by
book_title
;
@remote_plan
Here we see an interesting execution plan.
In this case we are doing a remote hash join to the book_details table
in the newpubs database. It is very important to note that
Oracle has “split” this query. Note that Oracle sent the select
statement for the book_details table over Oracle*Net to be executed on
the remote instance (NEWPUBS), and we do not see the execution plan
for this piece of the distributed query.
OPERATION
OPTIONS OBJECT_NAM OPTIMIZER OBJEC OTHER
---------------------- -------- ---------- ---------- -----
-----------
SELECT STATEMENT
CHOOSE
SORT
ORDER BY
HASH JOIN
TABLE ACCESS FULL
BOOK ANALYZED
REMOTE
NEWPU SELECT
"BOOK_KEY","P
BS.US UBLISHING_QTY" FROM
.ORAC "BOOK_DETAILS"
"BOOK
LE.CO _DETAILS" M
Now that we see how to get remote execution
plans, let’s look at how to control the site for the remote execution.
Determining the driving site and driving
table for cross-database queries
Just like an SQL query has a “driving table”,
a distributed query has a “driving site”. The choice of
the driving site can be manipulated with the driving_site SQL hint.
Here is an example of a remote query with a driving_site hint:
Select /*+
driving_site(R1) */
initcap(book.book_title) c1,
book_retail_price c2,
publishing_qty
c3
from
book@pubs
r1
natural join
book_details@newpubs r2
order by
book_title
;
In this example, we see that we have directed
Oracle to make the r1 site (PUBS) the driving site for this query.
The problem of remote joins
create
public database link
diogenes
connect to pubs
identified by pubs
using 'diogenes';
From your NEWPUBS instance, execute the
following SQL. Note that 3 of the four database tables are
remote, and only the book_details table is local to this instance.
connect
pubs_remote/pubs_remote@newpubs
truncate
table plan_table;
explain plan
for
select
initcap(book.book_title) c1,
author_last_name,
book_retail_price c2,
publishing_qty
c3
from
book@pubs
natural join
book_author@pubs
natural join
author@pubs
natural join
book_details
order by
book_title
;
@remote_plan
Here is the execution plan. Closely
examine the output.
OPERATION
OPTIONS OBJECT_NAM OPTIMIZER OBJEC OTHER
---------------------- -------- ---------- ---------- -----
-------------------SELECT STATEMENT
CHOOSE
SORT ORDER
BY
HASH JOIN
HASH JOIN
HASH JOIN
REMOTE
DIOGE SELECT"AUTHOR_KEY",
NES.N "AUTHOR_LAST_NAME"
F EWPUB ROM "AUTHOR"
"AUTHORS"
REMOTE
DIOGE SELECT"AUTHOR_KEY",
NES.N "BOOK_KEY"
FROM
"BOO EWPUB
K_AUTHOR" S
OR"
REMOTE
DIOGE SELECT
"BOOK_KEY","B
NES.N OOK_TITLE","BOOK_RET
EWPUB AIL_PRICE" FROM
"BOO S
K" "BOOK"
TABLE ACCESS FULL
BOOK_DETAI
LS
In the remote execution plan we see several
steps:
1 - Select from the remote AUTHOR table
2 – Select from the remote BOOK_AUTHOR table
3 – Select from the remote BOOK table
4 – Perform three HASH JOINS, using the data
returned from the remote Oracle database.
5 – Select from the local book_details
table, and join this into the result set.
6 – Sort the result set on the originating
Oracle database.
Let’s take a closer look at what is happening
inside a distributed SQL statement.
Note that Oracle has broken-up the query into
many separate pieces:
1 – Independent SQL is sent to each remote
Oracle database
2 – Result sets are sent back from each
remote Oracle database. The query cannot continue until all
remote Oracle databases have sent their results back over
Oracle*Net.
3- The independent result from each remote
request are collected inside the SGA RAM memory. Only after
all results are collected can the SQL progress to join the tables.
4 – Oracle performs hash joins on the remote
result sets, creating a single result set for the query.
5 – Oracle then sorts the result set (for
the ORDER BY clause) in on the originating Oracle database.
6 – Oracle passes the completed result back
to the originating query.
Tips for faster cross-database queries
Given the challenges of distributed queries,
there are several things that you can do to improve the performance of
cross-database queries:
* Pre-join remote tables with views -
If you want to join the remote tables, the best way to achieve this is
by building a view of these tables on the remote site. Remote
views allow join operations to happen on the remote Oracle database,
thereby reducing the amount of Oracle*Net traffic. Note that the
predicates do not have to be created within the view as they should be
passed within the SQL statement sent from the local to remote node.
* Replicate the tables locally – If the
tables are small and non-volatile, using Oracle replication to keep a
local copy of the tables is the fastest alternative to distributed
SQL.
* Remember sorting – When a Oracle
database is accessed remotely by establishing a Oracle database link,
Oracle*Net sometimes uses the temporary tablespace on the remote
Oracle database, regardless of the Oracle database that invoked the
SQL. In other cases, applications on one instance that accessed
another instance (with a Oracle database link), will use the temporary
tablespaces on the originating Oracle database, and not the processor
that contains the link. The moral is that Oracle*Net will
sometimes use the temporary tablespaces on the remote Oracle database,
and temporary tablespaces must be sized to accommodate remote data
requests.
For more details,
see the "Easy
Oracle Series" a set of books especially designed by Oracle
experts to get you started fast with Oracle database technology.
Remote DBA
Services
Burleson Consulting can offer world-class
remote Oracle database support at super-affordable prices.
Our remote
Oracle DBA service provides 100% of the remote Oracle database
administration needs for your company, and includes 24x7 access to our
staff of 100% OCP Certified Oracle DBAs.
We require a 12 month service commitment and include the following
services:
- Initial configuration
review and problem identification
- Installation of Oracle
statistics collection mechanisms and quarterly database growth
summaries
- Hourly monitoring of your
Oracle database for pending problems
- Periodic performance
analysis & identification of tuning activities
- Twenty Four hour Oracle
emergency support
- Reporting and resolving
all serious Oracle alert log messages
- Free use of the BC
TablePack,
ServerPack
and
AuditPack
services
- Quick response emergency
support for production database outages
- Four hours of free remote
DBA support every month. You can use these free hours for any DBA
activity, including database analysis, system design, production
migrations or personal mentoring.
For more
information, please visit
www.remote-dba.cc or email
info@remote-dba.net.
 |
