Re: Need help on query performance issue

  • From: "Shastry(DBA)" <shastry17@xxxxxxxxx>
  • To: oracle-l <oracle-l@xxxxxxxxxxxxx>
  • Date: Thu, 1 Oct 2009 19:04:40 +0530

Hi All/Balakrishna,

Thanks for notifying it. Please find attached my explain plan and the SQL
script. Please help.

Reg,
An

On Thu, Oct 1, 2009 at 3:04 PM, Balakrishna Y <krishna000@xxxxxxxxx> wrote:

> Hi,
>
> There is no evidence for your question at all .... just post you query and
> statistics of your table & indexes ...
>
> Only then some one can help you out in this .
>
> Regards
>
> Bala
>
>
> On 10/1/09, Shastry(DBA) <shastry17@xxxxxxxxx> wrote:
>>
>> Hi Gurus,
>> I have Oracle query which runs fine in production and test1 server but
>> taking more time in the recently cloned two more dev servers. i checked for
>> explain plan and got that two tables are having FULL TABLE SCANS  and didnt
>> find anything FTS issue in production and test1 servers with the same data.
>> I did analyze two databases completely to have latest stats collection but
>> didnt help :(.
>> How to tackle this issue please guide.
>> Thanks in advance.
>>
>> Reg,
>> An
>>
>
>
Operation       Object Name     Rows    Bytes   Cost    Object Node     In/Out  
PStart  PStop

SELECT STATEMENT Optimizer Mode=FIRST_ROWS              1               4236    
                                 
  SORT UNIQUE           1       457     4236                                    
 
    HASH JOIN OUTER             1       457     4235                            
         
      HASH JOIN OUTER           1       435     3884                            
         
        NESTED LOOPS            1       413     3534                            
         
          HASH JOIN OUTER               1       401     3532                    
                 
            HASH JOIN OUTER             1       379     3182                    
                 
              NESTED LOOPS              1       357     2832                    
                 
                HASH JOIN OUTER         1       345     2828                    
                 
                  HASH JOIN OUTER               1       323     2478            
                         
                    HASH JOIN OUTER             1       301     2128            
                         
                      HASH JOIN OUTER           1       279     1778            
                         
                        TABLE ACCESS BY INDEX ROWID     TEST.PAGE_TWO   1       
36      3                                        
                          NESTED LOOPS          1       257     1427            
                         
                            NESTED LOOPS                1       221     1425    
                                 
                              NESTED LOOPS              1       199     1421    
                                 
                                NESTED LOOPS            1       176     1419    
                                 
                                  NESTED LOOPS          2       306     1416    
                                 
                                    NESTED LOOPS                36      4 K     
1364                                     
                                      NESTED LOOPS              181     14 K    
663                                      
                                        NESTED LOOPS            181     10 K    
402                                      
                                          NESTED LOOPS          82      4 K     
88                                       
                                            TABLE ACCESS FULL   
TEST.PANUFACTURERS      1       24      57                                      
 
                                            TABLE ACCESS BY INDEX ROWID 
TEST.PANU_PARTS 82      2 K     32                                       
                                              INDEX RANGE SCAN  
TEST.PANUPART_IDX1      82              2                                       
 
                                          TABLE ACCESS BY INDEX ROWID   
TEST.PANU_BY    2       22      4                                        
                                            INDEX RANGE SCAN    
TEST.PANUBY_IDX1        2               3                                       
 
                                        TABLE ACCESS BY INDEX ROWID     
TEST.ITEM       1       20      2                                        
                                          INDEX RANGE SCAN      TEST.ITEM_PK    
1               1                                        
                                      TABLE ACCESS BY INDEX ROWID       
TEST.REV        1       39      4                                        
                                        INDEX RANGE SCAN        TEST.REV_IDX1   
2               3                                        
                                    TABLE ACCESS BY INDEX ROWID TEST.CHANGE     
1       33      2                                        
                                      INDEX RANGE SCAN  TEST.CHANGE_PK  1       
        1                                        
                                  TABLE ACCESS BY INDEX ROWID   TEST.NODETABLE  
1       23      2                                        
                                    INDEX UNIQUE SCAN   TEST.NODETABLE_PK       
1               1                                        
                                TABLE ACCESS BY INDEX ROWID     TEST.NODETABLE  
1       23      2                                        
                                  INDEX UNIQUE SCAN     TEST.NODETABLE_PK       
1               1                                        
                              TABLE ACCESS BY INDEX ROWID       TEST.ITEM       
1       22      4                                        
                                INDEX RANGE SCAN        
TEST.ITEM_NUMBER_CLASS_IDX      1               3                               
         
                            INDEX RANGE SCAN    TEST.PAGE_TWO_PK        1       
        2                                        
                        TABLE ACCESS FULL       TEST.PASSTRY    24 K    520 K   
281                                      
                      TABLE ACCESS FULL TEST.PASSTRY    24 K    520 K   281     
                                 
                    TABLE ACCESS FULL   TEST.PASSTRY    24 K    520 K   281     
                                 
                  TABLE ACCESS FULL     TEST.PASSTRY    24 K    520 K   281     
                                 
                TABLE ACCESS BY INDEX ROWID     TEST.PAGE_TWO   1       12      
4                                        
                  INDEX RANGE SCAN      TEST.PAGE_TWO_PK        1               
3                                        
              TABLE ACCESS FULL TEST.PASSTRY    24 K    520 K   281             
                         
            TABLE ACCESS FULL   TEST.PASSTRY    24 K    520 K   281             
                         
          TABLE ACCESS BY INDEX ROWID   TEST.PAGE_THREE 1       12      3       
                                 
            INDEX RANGE SCAN    TEST.PAGE_THREE_PK      1               2       
                                 
        TABLE ACCESS FULL       TEST.PASSTRY    24 K    520 K   281             
                         
      TABLE ACCESS FULL TEST.PASSTRY    24 K    520 K   281                     
                 

Other related posts: