Months after a database platform migration, a frustrated client asked for assistance concerning a consistent latency issue. Another engineer already working on the problem had been able to confirm, using network packet traces, that many SQL calls took three to almost twenty seconds from the network capture point in front of the database host server and back. Meaning, three to nearly twenty seconds of total transaction time was being burned somewhere between the NIC, OS and database.
After being briefed in on the problem description, mitigation actions already taken and planned, and which additional checks were going to happen, the triage team decided to look more closely at session traces, this time adding ‘sys=yes’ to the tkprof command.
Immediately, a red flag went up. What are those cryptic-looking SQL statements (Figures 1 and 2) strung in between the application code and why are the statements working so hard?
After a quick check of the summary numbers (Figures 3 and 4), what we saw, hiding in plain sight, was the fact that the recursive SQL statements were consuming up to five times more clock time and work than the application SQL statements!
The triage team, which now included an Oracle performance engineer, realized that the impacted sessions were hard-parsing most statements, including the recursive statements. The team took a quick look at the instance configuration parameters (such as spfile, pfile, init.ora), and found that two cursor parameters—session_cached_cursors and open_cursors—were still set to the base install default values.
After looking at the instance configuration files on the old host servers, the team confirmed the migration miss and identified the needed parameter values. After testing, the changes were planned, approved and implemented.
Setting the parameters to the pre-migration values improved performance for the impacted sessions by nearly 35% (Figure 5).
Although a second set of changes are planned to further reduce recursive call parse times by tuning the cursor parameters to meet the expanded environment for which the hardware migration was meant to achieve, the results from changing the parameter values alone have made a significant difference.