RE: How to tune this query:

  • From: genegurevich@xxxxxxxxxxxxxxxxxxxxx
  • To: oracle-l@xxxxxxxxxxxxx
  • Date: Thu, 14 Dec 2006 11:14:35 -0600


Thank you for your thoughts.  I thought that the explain plan indicated
that the index OFFR_ALT8 will
be read only for 1 partition based on this line in my explain plan:

|* 14 |       INDEX FAST FULL SCAN        | OFFR_ALT8     |    20 |   480 |
3 |   KEY |   KEY |

I thought that KEY KEY indicated that it will only look at one partition.
Am I misinterpreting that?

I have 16 CPUs so yes, I think there is plenty of CPU resources left. And
you are correct in saying that 30+ index
scans will kill the performance, I'm just not understanding why are there
so many of them

thank you again.

Gene Gurevich

             <Christian.Antogn                                          To 
             ini@xxxxxxxxxxxx>         <genegurevich@xxxxxxxxxxxxxxxxxxxxx 
             Sent by:                  >                                   
             oracle-l-bounce@f                                          cc 
                                       RE: How to tune this query:         
             12/12/2006 05:44                                              
             Please respond to                                             


> 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).



Other related posts: