Skip to main content

Hi All,

 

A certain Test environment has over 1 million records in transaction_sys_local_tab.

This table is being accessed through Transaction_SYS.Get_Posted_Job_Arguments to obtain jobs of a particular procedure_name_ where the state is posted.

 

 

Obtaining results from this query takes over 1 second. Where are in the Core environment, it takes only 0.1 seconds.

 

The Test environments ‘System Parameters’ have been set as below.

 

As there is a large amount of records in the mentioned table, are there recommended values that need to be put in the above parameters to improve the performance of retrieving data from the mentioned table?

Or is there anything else that needs to be done to improve the performance? 

 

Any help is much appreciated.

 

Best regards,

Devni

Hello Devni,

One million rows stored in an Oracle table should not be a big problem for database performance.  But in the case you’ve described, i.e. searching for values within the PROCEDURE_NAME column while at the same time using the UPPER() function, will slow your search by causing the Oracle SQL parsing routines which send this query to the database to search every row in the table (full table scan).  When a table gets above 50,000-100,000 rows then this can become time consuming. 

A way to work around this problem would be to create an index for the PROCEDURE_NAME column.  But even if this were to be done there is a second issue created by use of the UPPER() function.  When SQL functions are used in a query then indexes won’t be used and Oracle would go back to the full table scan method to find the wanted value(s).  Also, creation of the index I suggested could add to performance problems on the table in use here, TRANSACTION_SYS_LOCAL_TAB.  So consult with a person who has Oracle database design background when considering how to go about tuning of your query above to improve the response.

Reply


Terms & ConditionsCookie settings