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_at_trivadis.com>         <genegurevich_at_discoverfinancial.com 
             Sent by:                  >                                   
             oracle-l-bounce_at_f                                          cc 
             reelists.org              <oracle-l_at_freelists.org>            
                                                                   Subject 
                                       RE: How to tune this query:         
             12/12/2006 05:44                                              
             PM                                                            
                                                                           
                                                                           
             Please respond to                                             
             Christian.Antogni                                             
              ni_at_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






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