(1) Yes, the second run caused a hard parse because of the different literal value.

(2) The soft parse run must be done as a another user, not the same user. Most of the work of a soft parse is the authorization, and that's where the synonym issue has the biggest impact. Because authorizations are cached a second parse by the same user would not show most of the difference. There would be a few more gets against the row cache objects latch (which is the least expensive of the three latches involved) but that's all.

I intentionally used the term "soft parse" to mean a soft parse that requires an authorization, because that is representative of the soft parses actually performed by applications that use synonyms heavily.

In Ixora news you describe the parse test:

"After bouncing the instance, we ran the test script once to insert rows containing the literal value 1 into each test table. This populated the library cache with the metadata for each test table, and the buffer cache with the required database blocks. We then ran the test script again to measure the hard parse performance, this time inserting rows containing the value 0 into each test table. We then repeated that as another user to measure the soft parse performance. Here are the results from a small desktop system running release 8.1.6."

I'm not sure I understand how this worked.

(1) Did the second run cause a hard parse because the same user was running the same script, but a change in the literal value (from 1 to 0) caused Oracle to see it as a different SQL statement? (I.e., no bind variable?)

(2) For the third run to get a soft parse, why was it necessary to repeat the run as another user, instead of as the same user?

Thanks for any help understanding this.