| Problems after copying a database [message #63780] |
Sat, 06 November 2004 03:42  |
Jeremy Forman
Messages: 1
Registered: November 2004
|
Junior Member |
|
|
I copied a database from one windows server to another using the "alter database backup controlfile to trace" command and a cold backup. Everything went great, the database came up without any problems. I ran my application against it and things looked great. I analyzed all indexes and tables in the main schema of the database. Then I was submitted a new script from a developer. The script has an insert statement based on a select statement w/ 6 subqueries. It was something like this:
insert into a
select distinct x, y, z
from b
where exists (select * from c, d where c.id = d.id)
or exists (select * from e where e.id = c.id and e.empno = d.empno)
or exists (select * from f where f.id = c.id and f.empno = d.empno)
or exists (select * from f where f.id = c.id and f.userid = d.empno)
or exists (select * from f where f.id = c.id and f.userid = d.empno)
or exists (select * from f where f.id = c.id and f.person = d.empno)
or exists (select * from g where g.id = c.id and g.empno = d.empno)
This is generally what the query does (one of the subqueries was repeated). On database 2, the subquery hangs indefinitely. On database 1 it executes in about 5 minutes. Is there any reason the query would hang on database 2 even though it is an exact copy of database 1? Database 1 wasn't changed at all after it was copied to database 2, no users were allowed on, no data was changed.
Using Oracle 8.1.7.4 on both machines. Both machines are windows 2000 servers.
Thanks for any help,
Jeremy.
|
|
|
|
| Re: Problems after copying a database [message #63789 is a reply to message #63780] |
Sun, 07 November 2004 23:12  |
Frank
Messages: 7901
Registered: March 2000
|
Senior Member |
|
|
Why did you analyze the schema ? You copied the dbf-files, including all analysis-data !
- Is your original schema analyzed ?
- Hardware configs are alike ? (location of db-files, log-files, memory, cpu etc.)
Explain-plan the script in both db's and compare the plans. (above outline doesn't tell nothing, because it won't compile: tables c and d are not accessible in the or-exists)
hth
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
