The definitive Oracle Guide

[OracleStream]

Hi, I've a problem with Oracle 10g R2 x64.

Some query are very slow than Oracle 9i.

[myOracle]

I have import an export of a user of Oracle 9i into Oracle 10g R2 x64 on Windows 2003 x64.

The problem is:
- i execute a query: the response time is 30 seconds and the CPU goes to 80%-90%.
- I re-execure the same query: the response time is 2 seconds and the CPU goes to 4% and the qry return the rows immediatly.

In oracle 9i:
- first time: CPU 6/7%, time 3 seconds
- same query other time: CPU 4%, time immediadly.

[forumadmin]

Hello OracleStream, MyOracle,

Sounds like a hard parse problem.

- Can yrou upload the query?
- The executin plans use
- the spfile/init.ora from the 10g system

Can you please do the following in SQL Plus:

1. alter session set events '10046 trace name context forever, level 8';
2. <run your query>
3. alter session set events '10046 trace name context off';

Please do it for the bad good running query.
This creates a trace file in the user_dump_dest.
Please upload the raw trace file and not a tkprof output.