RE: How to tune this query:

Christian,

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                                                    
             Antognini"                                                    
             <Christian.Antogn                                          To 
             ini@xxxxxxxxxxxx>         <genegurevich@xxxxxxxxxxxxxxxxxxxxx 
             Sent by:                  >                                   
             oracle-l-bounce@f                                          cc 
             reelists.org              <oracle-l@xxxxxxxxxxxxx>            
                                                                   Subject 
                                       RE: How to tune this query:         
             12/12/2006 05:44                                              
             PM                                                            
                                                                           
                                                                           
             Please respond to                                             
             Christian.Antogni                                             
              ni@xxxxxxxxxxxx                                              
                                                                           
                                                                           




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






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

Other related posts: