| | |||||
| Modified 26-OCT-2009 Type BULLETIN Status PUBLISHED | |||||
Checked for relevance on 26-Oct-2009
Checked for relevance on 20-Feb-2008
Checked for relevance on 08-Aug-2006
PURPOSE
-------
General tips tuning HS/Gateways
SCOPE & APPLICATION
-------------------
This bulletin will give some hints how to tune
generic connectivity or open gateways.
Tips tuning generic connectivity/gateways
-----------------------------------------
There are no real parameters to tune generic connectivity or the
open gateways. Everything in the following article is to demonstrate
the behaviour of heterogeneous services, to explain the working mechanism and
what can be done to improve the performance.
Background information: HS connects from an Oracle database to a foreign
data source and fetches records. The amount of the fetched records
reflects the time needed to transfer them.
The simplest way to improve performance is to reduce the amount
of fetched data.
But how to achive this?
- RULE 1:
AVOID select * from remote table
That's a very important statement!
But fetching all records from the remote database can sometimes
occur without wanting to fetch all the records. The cause is
called POST PROCESSING:
The Transparent Gateways tell the Oracle database during connect time
which functions and operators they support. If the remote database does
not support this function or operator, then the Oracle database must
execute the function or operand. But to do this ALL records from
the remote database must be fetched and processed locally in the Oracle
database.
- RULE 2:
pay attention to which functions/operands are supported by HS.
Unsupported functions are post processed!
How to determine what's going on and how many records are fetched:
The IBM gateways support explain plan. With this feature you're able
to see the statements passed and processed.
But this feature is not yet implemented in the generic connectivity
or open gateways.
So the only way to figure out what statements are passed between the
Oracle database and the remote database is to verify the HS traces.
One option to solve this issue is to CREATE VIEWS.
With access to the remote database:
it is possible to create a view at the remote database that
pre selects the records.
HS now performs its own query only to this view and processes only
the pre selected records.
With no access to the remote database:
the whole operation could be used in conjunction with
DBMS_HS_PASSTHROUGH packages.
DBMS_HS_PASSTHROUGH allows the creation of views at the remote database
as well as the sending of select statements as they are
to the foreign database without being pre prosssed.
Another option is to use the COST-BASED optimizer.
The cost-based optimizer uses indexes on remote tables and considers
more execution plans then the rule based optimizer.
The real performance tuning is to reduce the amount of data fetched by
the HS agent.
But sometimes adapting the interface might improve performance as well.
By default, an agent fetches data from the non-Oracle system until
it has enough data retrieved to send back to the HS related part in the
Oracle database. The agent reblocks the data between the agent and the
Oracle database server in sizes defined by the value of HS_RPC_FETCH_SIZE.
The other part is the transfer of the foreign database related interface
like odbc and the agent.
This transfer can be manipulated by setting the parameter
HS_FDS_FETCH_ROWS.
The default value is 20, but it makes sense to increase this value by
adapting it to the fetched rows of the remote database.
While some ODBC drivers initialize the SQL_FETCH_ROWS to 100 the
HS_FDS_FETCH_ROWS should be set to 100 or even to n*SQL_FETCH_ROWS.
For example, assume that you set HS_RPC_FETCH_SIZE to 64K and HS_FDS_FETCH_ROWS
to 100 rows. Assume that each row is approximately 600 bytes in size,
so that the 100 rows are approximately 60K.
The agent starts fetching 100 rows from the non-Oracle system.
Because there is only 60K bytes of data in the agent, the agent does not
send the data to the Oracle database server. Instead, the agent
fetches the next 100 rows from the non-Oracle system.
Now the agent is filled with 120K of data and the first 64K can be sent
to the Oracle database server.
There is 56K of data left in the agent. The agent fetches another 100 rows
from the non-Oracle system before sending the next 64K of data to the Oracle
database server....
From the description above it looks like the agent is a bottle neck. It only
sends data after a buffer is filled. So a better idea would be to STREAM the
data.
If the hs agent supports array fetching (please check out the documentation for
a specific type of agent) the blocking can be switched of. Set the
initialization parameter
HS_RPC_FETCH_REBLOCKING
to OFF.
According to the sample from above this means that the first 100 rows are
immediately sent to the Oracle server.
So in theory a performance improvement will take place by setting
HS_RPC_FETCH_REBLOCKING to OFF and HS_FDS_FETCH_ROWS to a value of
n*SQL_FETCH_ROWS.
KEEP IN MIND:
All these parameters manipulate the DATA TRANSFER. While each fetched
record depends on networking capacity, on CPU usage..., the best tuning
mechanism is still to reduce the amount of transferred data.
And this can be achieved by CREATING VIEWS to preselect
the fetched records as described at the beginning of this document.
RELATED DOCUMENTS
-----------------
Note.234996.1
Products
|
Related
0 ความคิดเห็น:
แสดงความคิดเห็น