Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: How to tune this query:

RE: How to tune this query:

From: Christian Antognini <Christian.Antognini_at_trivadis.com>
Date: Wed, 13 Dec 2006 00:44:26 +0100
Message-ID: <F2C9CCA71510B442AF71446CAE8AEBAF8B55C0@MSXVS04.trivadis.com>


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
Received on Tue Dec 12 2006 - 17:44:26 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US