A simple way to reduce network traffic is to replicate data from one node to another. You
can do this manually (via the SQL*Plus copy command), or it can be done automatically by the
database (via materialized views). Replicating data improves the performance of queries against
remote databases by bringing the data across the network once??”usually during a slow period on
the local host. Local queries can use the local copy of the data, eliminating the network traffic
that would otherwise be required.
Let??™s consider a simple task??”selecting a value from a sequence. A company has created a
distributed application in which a new sequence value is generated for each row. However, the
sequence is local, whereas the insert is being performed in a far distant database. Because the
trigger that generates the sequence value is executed for each row, each insert generates a remote
operation to generate the next sequence value.
The impact of this design is apparent when a session??™s trace file is examined:
SELECT NEWID_SEQ.NEXTVAL
FROM
DUAL
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.13 0 0 0 0
Execute 53 0.01 0.01 0 0 0 0
Fetch 53 0.06 6.34 0 159 0 53
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 107 0.09 6.50 0 159 0 53
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
0 SEQUENCE (REMOTE)
0 TABLE ACCESS (FULL) OF 'DUAL'
Elapsed times include waiting on following events:
Event waited on Times Max.
Pages:
910
911
912
913
914
915
916
917
918
919
920
921
922
923
924
925
926
927
928
929
930
931
932
933
934