Tutorial Flow:




Detailed Analysis


The detailed diagnosis of the problem suggests that a larger portion of SQL statements are being compiled rather than retrieving their access plans from the package cache. To analyse the problem further database administrator monitors elements for SQL compilation.

Note: Set the refresh interval on the top right pane to 5 seconds.

Few metrics related to SQL compilation


Ideally, we would like to see very minimal compilations and package cache inserts. At the same time, package cache lookups should be high to indicate package is being reused. The above evidence suggests that a larger portion of SQL statements are being compiled rather than retrieving their access plans from the package cache.