Toad World® Forums

Spotlight for Oracle: Hard Parse Tuning Question


#1

Hello,

Recently installed TOAD 12.7.0.121 and Spotlight 10.3.0.1632. Total Parse Wait Time Ratio Alarm constantly fires [yellow and sometimes red] 10.7 % wait over 30 sec. There is a Library Cache Miss Rate alarm on Shared Pool Used as well [aqua color].

Vendor reviewed Spotlight data and says “there isn’t actually a problem. TOAD is complaining because the axiUm application is sending a lot of “literals” to the database, and those literals have to be parsed (hence, high level of parsing). And that’s true, we are, because that’s just the way the application is written.”

Vendor requires CURSOR_SHARING must be set to ‘EXACT’. SESSION_CACHED_CURSORS currently set to default 50, OPEN_CURSORS set to 900 per vendor recommendation. Shared Pool size is 1.7 Gb 73% used on average.

Is it possible to tune this situation?

Thank you

Carol


#2

Hi Carol,

Other than increasing the size of the SGA probably not a lot you can do here with the vendor recommendations. How is your response time and performance overall? If it’s okay then you can adjust the thresholds in Spotlight higher so they won’t alert you.

One other thing you could try is running SQL Optimizer’s Index Optimization module to see if it can find index recommendations that would benefit your overall workload. While that wouldn’t necessarily help the hard parse situation, it might free up SGA and CPU resources that would help the situation indirectly.

Hope that helps!

Kevin


#3

Hello Kevin,

Thank you for your suggestions. Response time and performance overall is good w/no complaints so perhaps theshold adjustments are warranted.

Oracle directed me to 2 docs:

High SQL Version Counts - Script to determine reason(s) ( Doc ID 438755.1 ).

How To Use SQL Profiles for Queries Using Different Literals Using the Force_Match Parameter of DBMS_SQLTUNE.ACCEPT_SQL_PROFILE ( Doc ID 1253696.1 )

The approach would be as follows:

  • run script and get some cursors that use same hash values

  • using SQL_Profile with force_match (By default DBMS_SQLTUNE.ACCEPT_SQL_PROFILE executes force_match parameter set to false.

When set true, analogous to matching algorithm used by FORCE option of cursor_sharing parameter in that it forces literals in the statement to be converted to binds and then the statement can be shared when different literals are supplied. ) … and this way reducing the hard parsing

I’ld like to explore Oracle’s suggestion and it will be a mini-project. Since no pressing complaints I need to take care of more pressing matters like a storage and oracle upgrade. At some point I’ld like to circle back to this.

Carol


#4

Hello Carol,

I think we cannot base on one or two indicators to judge whether a system is healthy or not. The parsing time in your system may be necessary. Some applications using hardcoded literals to guarantee better query plan generation for each SQL statement. A hardcoded literal SQL gives better information to database SQL optimizer to make decision without guessing. Bind variables may save you some time, but a wrong query plan may be a disaster to your system. Bind sensitive feature may have some time delay to react actual bind values; it is not a good approach for mission critical system. Force_match in DBMS_SQLTUNE.ACCEPT_SQL_PROFILE has the same problem that the query plan either be fixed for similar SQL or has the same delay as bind sensitive SQL. My recommendation is not do anything until there is something abnormal happen to your system.

Richard