Oracle Database Discussions

I have a view creation script which is running in a fraction of a second in the dev environment but takes over an hour to run in the test environment.

The test environment is off-site, so I have no direct access to it. It does have a lot more data in than dev, but this is not a materialised view so as far as I understood it the amount of data in the system shouldn't affect the time to create the view.

I haven't been able to find any information on what can make this happen. In the development environment I can be querying the view (or can lock it directly), and am still able to run the CREATE OR REPLACE statement without any performance impact. The view does contain function calls and is created with the FORCE keyword, but I can't find any evidence that this should affect the speed of creation.

I am running Oracle 10g (10.2.0.4.0) on Solaris in both dev and test environments.

Any ideas on what could be causing this are very welcome.

CREATE OR REPLACE FORCE VIEW DB_OWNER.PER$AGE
(
SYSTEM,
LOCATION,
EVENT_START,
SURNAME,
DOB_STRING,
DESCRIPTION,
GENDER,
OLD_ID,
URN,
COMBINED_SCORE,
LOAD_DATETIME,
AGE,
EVENTDATE_FROM,
EVENTDATE_TO,
DETAILS,
AOB
)
AS
SELECT /*+ leading(ad) */
p.system,
p.location,
p.event_start,
p.surname,
CAST (
NVL (
NVL2 (
p.dob,
TO_CHAR (p.dob, 'dd/mm/yyyy'),
TO_CHAR (
p.apparent_age
+ ROUND ( (SYSDATE - p.created_datetime_from) / 365, 0))),
0) AS VARCHAR2 (10))
AS dob_string,
p.description,
p.gender,
p.old_id,
p.urn,
NVL2 (
p.dob,
db_owner.app_age_score (p.dob, ssearch.get_target_age),
db_owner.age_score (p.apparent_age,
p.created_datetime_from,
ssearch.get_target_age))
AS combined_score,
p.load_datetime,
psd.age,
psd.eventdate_from,
psd.eventdate_to,
e.details,
NVL (e.aob, 'N/A') aob
FROM db_owner.person p,
db_owner.psd psd,
db_owner.entity e
WHERE p.db_key = psd.db_key
AND psd.age IS NOT NULL
AND e.system(+) = p.system
AND p.system IN (SELECT internal_name
FROM ops$tomcat.feed_details
WHERE inSearchList = 'true')
AND e.urn(+) = p.urn;