Home » RDBMS Server » Performance Tuning » on consumption of resources
icon7.gif  on consumption of resources [message #109966] Wed, 02 March 2005 07:32 Go to next message
Tads
Messages: 10
Registered: February 2005
Junior Member
Hi all,

I´m trying tunning a query and I would like to know your opinions about the following results of the generated trace:

Before:

call    count   cpu elapsed  disk  query curr   rows
------- -----  ---- ------- ----- ------ ---- ------
Parse       1  0.19    0.16     1      2    0      0
Execute     1  0.00    0.00     0      0    0      0
Fetch     328  5.19   18.26 52380 138758    4   4905
------- -----  ---- ------- ----- ------ ---- ------
total     330  5.39   18.43 52381 138760    4   4905




After:

call    count   cpu elapsed  disk  query curr   rows
------- -----  ---- ------- ----- ------ ---- ------
Parse       1  0.06    0.06     0      0    0      0
Execute     2  0.00    0.00     0      0    0      0
Fetch     328  8.18   13.01 55067 212876    6   4905
------- -----  ---- ------- ----- ------ ---- ------
total     331  8.24   13.07 55067 212876    6   4905



As noted, only elapsed reduced.

Somebody has some tips for this?

Thanks!!!

[]´s
Re: on consumption of resources [message #109969 is a reply to message #109966] Wed, 02 March 2005 07:49 Go to previous message
Tads
Messages: 10
Registered: February 2005
Junior Member
Sorry,

I forgot the explain plan:

Before:

   IO_COST   CPU_COST       CARD Pstart       Pstop        Execution Plan
---------- ---------- ---------- ------------ ------------ ------------------------------------------------------------------------------   
       253                     5                           SELECT STATEMENT                                                                 
       253                     5                           SORT ORDER BY                                                                    
       249                     5                             NESTED LOOPS OUTER                                                             
                               1                               INDEX UNIQUE SCAN PK_STATUS_ARS_HSBC                                         
       249                     5                               NESTED LOOPS OUTER                                                           
         1                     1                                 TABLE ACCESS BY INDEX ROWID ARS_RESPONSAVEL_HSBC                           
                               1                                   INDEX UNIQUE SCAN PK_ARS_RESP_HSBC                                       
       247                     5                                 NESTED LOOPS OUTER                                                         
         1                     1                                   TABLE ACCESS BY INDEX ROWID ARS_CATEGORIA_HSBC                           
                               1                                     INDEX UNIQUE SCAN PK_ARS_CATEGORIA_HSBC                                
       245                     5                                   NESTED LOOPS OUTER                                                       
         2                     1                                     TABLE ACCESS BY INDEX ROWID SPECIFIC_ERROR                             
         1                     1                                       INDEX RANGE SCAN IND_SPE_ERROR_PK_ALT                                
       242                     5                                     NESTED LOOPS OUTER                                                     
         3                     1 ROW LOCATION ROW LOCATION             TABLE ACCESS BY GLOBAL INDEX ROWID DEVICE_HISTORY                    
         2                     1                                         INDEX RANGE SCAN INDEX_FK_DEV_HIST_VS_H_HIST                       
       238                     5                                       NESTED LOOPS OUTER                                                   
       234                     5                                         NESTED LOOPS                                                       
       232                     5                                           NESTED LOOPS                                                     
       232                     5                                             NESTED LOOPS                                                   
       230                     5                                               NESTED LOOPS OUTER                                           
       228                     5                                                 NESTED LOOPS                                               
        33                     5                                                   NESTED LOOPS                                             
        27                     6                                                     HASH JOIN                                              
        14                    36                                                       HASH JOIN                                            
         7                     6                                                         NESTED LOOPS                                       
         2                     1                                                           TABLE ACCESS BY INDEX ROWID UNIDADE_HSBC         
         1                  1451                                                             INDEX UNIQUE SCAN PK_UNIDADE_HSBC              
         5                     1                                                           TABLE ACCESS BY INDEX ROWID UNIDADE_HSBC         
         1                     6                                                             INDEX RANGE SCAN IND_FK_UNIDADE_VS_PAI         
         6                  1451                                                         TABLE ACCESS FULL UNIDADE_HSBC                     
        12                   256                                                       TABLE ACCESS FULL ATM_HSBC                           
         1                    20                                                     TABLE ACCESS BY INDEX ROWID ATM                        
                              27                                                       INDEX UNIQUE SCAN PK_ATM                             
        39                     1 ROW LOCATION ROW LOCATION                         TABLE ACCESS BY GLOBAL INDEX ROWID HISTORY               
         2                   751                                                     INDEX RANGE SCAN INDEX_FK_HISTORY_VS_ATM               
         1                     1                                                 TABLE ACCESS BY INDEX ROWID ARS_HSBC                       
                               1                                                   INDEX RANGE SCAN IND_ARS_HSBC_NR                         
         1                     1                                               TABLE ACCESS BY INDEX ROWID ERROR_CODE                       
                               1                                                 INDEX UNIQUE SCAN PK_ERROR_CODE                            

   IO_COST   CPU_COST       CARD Pstart       Pstop        Execution Plan                                                                   
---------- ---------- ---------- ------------ ------------ ------------------------------------------------------------------------------   
                               1                                             INDEX UNIQUE SCAN PK_SITE_HSBC                                 
         1                     9                                           TABLE ACCESS BY INDEX ROWID MODEL                                
                               1                                             INDEX UNIQUE SCAN PK_MODEL                                     
         3                     1                                         TABLE ACCESS BY INDEX ROWID HARDWARE_HISTORY                       
         2                     1                                           INDEX RANGE SCAN INDEX_HARD_HISTORY_EVENT                        


After:

   IO_COST       CARD Pstart       Pstop        Execution Plan
---------- ---------- ------------ ------------ ------------------------------------------------------------------------------              
        29          1                           SELECT STATEMENT                                                                            
        29          1                           SORT ORDER BY                                                                               
        25          1                             NESTED LOOPS                                                                              
        24          1                               NESTED LOOPS OUTER                                                                      
        23          1                                 NESTED LOOPS OUTER                                                                    
        23          1                                   NESTED LOOPS OUTER                                                                  
        22          1                                     NESTED LOOPS                                                                      
        21          1                                       NESTED LOOPS OUTER                                                              
        20          1                                         NESTED LOOPS                                                                  
        19          1                                           NESTED LOOPS                                                                
        18          1                                             NESTED LOOPS OUTER                                                        
        15          1                                               NESTED LOOPS OUTER                                                      
        12          1                                                 NESTED LOOPS                                                          
        12          1                                                   NESTED LOOPS                                                        
        11          1                                                     NESTED LOOPS                                                      
         7          4                                                       HASH JOIN OUTER                                                 
         4          4                                                         NESTED LOOPS                                                  
         2          1                                                           TABLE ACCESS BY INDEX ROWID UNIDADE_HSBC                    
         1       1451                                                             INDEX UNIQUE SCAN PK_UNIDADE_HSBC                         
         2          4 ROW LOCATION ROW LOCATION                                 TABLE ACCESS BY GLOBAL INDEX ROWID HISTORY                  
         1       8978                                                             INDEX RANGE SCAN IND_HIS_FG_ERR_MONIT                     
         2          1                                                         TABLE ACCESS FULL ARS_HSBC
         1          1                                                       TABLE ACCESS BY INDEX ROWID ATM                                 
                   27                                                         INDEX UNIQUE SCAN PK_ATM                                      
         1          1                                                     TABLE ACCESS BY INDEX ROWID ATM_HSBC                              
                   19                                                       INDEX UNIQUE SCAN PK_ATM_HSBC                                   
                    1                                                   INDEX UNIQUE SCAN PK_SITE_HSBC                                      
         3          1                                                 TABLE ACCESS BY INDEX ROWID HARDWARE_HISTORY                          
         2          1                                                   INDEX RANGE SCAN INDEX_HARD_HISTORY_EVENT                           
         3          1 ROW LOCATION ROW LOCATION                     TABLE ACCESS BY GLOBAL INDEX ROWID DEVICE_HISTORY                       
         2          1                                                 INDEX RANGE SCAN INDEX_FK_DEV_HIST_VS_H_HIST                          
         1          6                                             TABLE ACCESS BY INDEX ROWID UNIDADE_HSBC                                  
                    1                                               INDEX UNIQUE SCAN PK_UNIDADE_HSBC                                       
         1          6                                           TABLE ACCESS BY INDEX ROWID UNIDADE_HSBC                                    
                  242                                             INDEX UNIQUE SCAN PK_UNIDADE_HSBC                                         
         2          1                                         TABLE ACCESS BY INDEX ROWID SPECIFIC_ERROR                                    
         1          1                                           INDEX RANGE SCAN IND_SPE_ERROR_PK_ALT                                       
         1          1                                       TABLE ACCESS BY INDEX ROWID ERROR_CODE                                          
                    1                                         INDEX UNIQUE SCAN PK_ERROR_CODE                                               
         1          1                                     TABLE ACCESS BY INDEX ROWID ARS_CATEGORIA_HSBC                                    

   IO_COST       CARD Pstart       Pstop        Execution Plan                                                                              
---------- ---------- ------------ ------------ ------------------------------------------------------------------------------              
                    1                                       INDEX UNIQUE SCAN PK_ARS_CATEGORIA_HSBC                                         
                    1                                   INDEX UNIQUE SCAN PK_STATUS_ARS_HSBC                                                
         1          1                                 TABLE ACCESS BY INDEX ROWID ARS_RESPONSAVEL_HSBC                                      
                    1                                   INDEX UNIQUE SCAN PK_ARS_RESP_HSBC                                                  
         1          1                               TABLE ACCESS BY INDEX ROWID MODEL                                                       
                    1                                 INDEX UNIQUE SCAN PK_MODEL


Previous Topic: Why Performance and Tuning is important
Next Topic: Performance difference BOLB vs. LONG RAW
Goto Forum:
  


Current Time: Sun Mar 03 20:48:38 CST 2024