I have an oracle 9i web application with a slow main menu (3-5 seconds) page. The menu design is such that the page is about 20 KB in size because it builds dynamically list of vendors to view/edit data for and list of contacts, etc. Also, all the application links are listed on the main menu.
It also has a few derived numbers that are calculated real time (i.e open orders). so every time
user navigates to main menu we run the sql again.
I did a SQL trace and checked the tkprof report and found that two SQL commands that use a dblink to lookup an organzation and contact table cause the delay sometimes. It could be a network or machine issue.
The tables in the remote db which is on same machine do not get updated that often. I am also
looking up a few columns in those tables.
i am thinking of copying those remote tables locally to speed things up and refresh the tables
nightly from remote tables.
Do you agree? and how would you implement this.
a) create a local table with same structure and procedure and job to DELETE data and the INSERT new
data from remote table.
b Run DDL (CREATE TABLE AS SELECT * FROM TABLE@LINK) in local database to create the local table
from remote table every night..
c) WOuld you create Materialized view for local table and schedule to refresh nightly from remote
Can you advise?