0

Whenever an insert is done in a table these tasks are executed:

  1. One Trigger (see down).

  2. Three indexes: one checks that a primary key is unique and the rest makes sure that two columns don't have unique values.


TRIGGER "TMMGR"."USR_SETTINGS_CNFG_PK" 
   BEFORE INSERT
   ON USR_SETTINGS_CNFG_T
   FOR EACH ROW
BEGIN
   :NEW.USR_SETTINGS_CNFG_ID := USR_SETTINGS_CNFG_TSEQ.NEXTVAL;
EXCEPTION
   WHEN OTHERS
   THEN
      raise_application_error (-20000, SQLERRM (SQLCODE));
END USR_SETTINGS_CNFG_PK;

I need to know how can I make the insert/trigger/index execution faster.

These are some of the DB settings:

max_string_size string EXTENDED                       
db_cache_size big integer 112M                        
shared_pool_size big integer 160M                        
java_pool_size big integer 160M                   
log_buffer big integer 7616K                        
nls_length_semantics string BYTE                 
open_cursors integer 500   
optimizer_mode string ALL_ROWS    
optimizer_features_enable string 12.1.0.2                          
compatible        string  12.1.0.2 
noncdb_compatible boolean FALSE                      
cursor_sharing string EXACT                   
db_block_size integer 16384                         
job_queue_processes integer 4000                                    
optimizer_index_cost_adj integer 10                                  
aq_tm_processes           integer 1     
asm_io_processes          integer 20    
db_writer_processes       integer 1     
gcs_server_processes      integer 0     
global_txn_processes      integer 1     
job_queue_processes       integer 4000  
log_archive_max_processes integer 4     
processes                 integer 500

Thanks.

  • 1
    Aside from you question, let me point out that your EXCEPTION handler is nothing but a bug. You swallow the actual error, making it nearly iimpossible to debug an actual run-time error. You'd be better off without any EXCEPTION handler at all and simply let the error propagate as designed by oracle. – EdStevens May 6 at 13:41
  • "15 minutes" is "fast" for me (data warehouse). what is "slow" for you? can you please add PROOF that this is "slow"? – Michael Kutz May 6 at 17:20
  • @MichaelKutz Hello, the insert process takes 2 minutes. But I have compared it to another DB with the same version Oracle Database 12c Standard Edition Release 12.2.0.1.0 - 64bit Production and in there it almost instantaneous. Also I can't prove it because I unfortunately don't know how to do it. – Jorge Luís Segura Oñate May 7 at 6:39
  • I have changed the sequence that's associated to the table because the nextval that it was going to give already existed in the table. This imporved the performance, before it took 2 minutes now it takes 20 seconds. This is definitely better but it should be faster. – Jorge Luís Segura Oñate May 8 at 9:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.