|Featured Blog Entries|
SQL queries taking 100% CPU in the database
KavyasudhaV 2700052TNM Comments (9) Visits (2193)
De-enveloping in Sterling Integrator (SI) can take a long time as the following query which gets executed in the de-enveloping process will be running really slow and will be using high amounts of CPU. In this case, the slowness when the EDIF
Re-indexing of database will fix this issue. If the indexes used in the above query are not re-indexed properly, this query will take a large amount of time to get executed. Regular re-indexing of the DB tables is always suggested.
But processing the same CONTRL message multiple times can cause CONTRL processing to run very slow. Though the rebuilding of the indexes are done, this issue can still occur if the duplicate data is being sent from the Supplier. This issue can occur if the supplier sends the same CONTRL message repeatedly. As a result the SQL query uses high CPU and takes a long time to return a result when processing the CONTRL message.
We suggest to get a explain plan (execution plan) for this query. This issue relates to Oracle not choosing the index - SCI_IDX_57 in the explain plan. It seems to think the cost of choosing 57 too great and instead uses PERF_IDX_6 through out the plan. A simple way to get this to choose 57 is to add 2 more columns onto the existing 57 index.
The following statements can be executed to achieve this and reanalyze the table.
The slow execution of the SQL queries and using high amounts of CPU can happen in GIS 4.3, SI 5.0 and SI 5.1 versions. This issue can happen to other SQL queries in SI also. The first step is to check if the indexes are regularly re-built or not. Index rebuilds are recommended for SI when the data in the DB tables change by approximately 20%. The second step is to collect the execution plan and checking if proper indexes are used or not with the help of DBA.
IBM Support can be contacted if any help is needed to verify this.