RE: How to tune this query:

Gene

> This does not look too bad. The tables that are joined via a
> cartesian join are small. The large table (OFFR) is
> being acessed via an index OFFR_ALT8. The partition pruning seem to
> be used too. The index itself is about 15G
> and it has 14 partitions.

With little information is difficult to give you sensible advices... But I guess the problem is the access path for index OFFR_ALT8. In fact it is a FAST FULL SCAN. This means you will read whole partitions for each row produced by the MERGE JOIN CARTESIAN operation. And in average the partitions are about 1GB.

> When the query is running I see a lot of waits for the db file
> scattered read . The files are the ones in the tablesaces
> where the index OFFR_ALT8 is located. When I check the long ops (via
> OEM) I see about 30+ full scans of that index and nothing else.

Even if nothing else is going on, this could be enough to kill the performance of that specific statement.

> vmstat shows 1% waits for IO and 80%+ idle CPU.

How many CPU are available? If 4 or more, you have plenty of idle CPU probably because you don't parallelize the CTAS (i.e. the query is working on a single CPU).

HTH
Chris

--
http://www.freelists.org/webpage/oracle-l