Encounter another performance issue after application and database been upgraded to latest version.
The application using c language where it will open and close cursor connect to database. This is OLTP environment and expecting 100-300 concurrent user access. There are quite a number of open and close cursor as well. we discovered that the performance always happen at the first set of open and close cursor. the rest of the open and close are very fast. However, this scenario not always happen, it only happen once in a while. I still can't find the pattern when it happen slowness of open and close cursor.
Appreciate if someone can share the info if encounter this scenario before ? or Advise what are the performance could help to boost up the performance for this kind of environment.
Could this related to db binding ? It seems everytime the first query to connect, it take sometime to perform where proabably require to check the permission,authentication and so on... I just suspect that could be the root cause.
You don't give much detail for us to be able to help you. I will take a wild guess that you are running dynamic SQL. Is it possible your data is heavily skewed? Therefore, sometimes this cursor will run fast and other times it will run slow, due to the amount of data. Have you explained the cursor that is giving you heartburn, tried running any of the advisors against it? Perform some counts on the table in question for your input value columns see if you have some skewed data.