To reduce the amount of data sent across the network,
move this procedure to the remote database identified by the database link HR_LINK and remove
the reference to that database link from the from clause in the procedure. Then, call the procedure
from the local database by using the database link, as shown here:
execute MY_RAISE@HR_LINK(1234,2000);
In this case, two parameters are passed to the procedure??”My_Emp_No is set to 1234, and Raise
is set to 2000. The procedure is invoked using a database link to tell the database where to find
the procedure.
The tuning benefit of performing a remote procedure call is that all of the procedure??™s
processing is performed in the database where the data resides. The remote procedure call
minimizes the amount of network traffic necessary to complete the procedure??™s processing.
To maintain location transparency, you may create a local synonym that points to the remote
procedure. The database link name will be specified in the synonym so that user requests will
automatically use the remote database:
create synonym MY_RAISE for MY_RAISE@HR_LINK;
A user could then enter the command
execute MY_RAISE(1234,2000);
and it would execute the remote procedure defined by the synonym MY_RAISE.
Using the Automatic Workload Repository
In Oracle Database 10g and earlier, STATSPACK gathers and reports on database statistics, albeit
in a strictly text-based format! As of Oracle 10g, the Automatic Workload Repository (AWR)
provides enhancements to the STATSPACK concept, generating all statistics found in STATSPACK,
and more.
Pages:
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462