Oracle DBA Question:
What are bind variables and why are they important?
Answer:
With bind variables in SQL, Oracle can cache related queries a single time in the SQL cache (area). This avoids a hard parse each time, which saves on various locking and latching resources we use to check objects existence and so on. BONUS: For rarely run queries, especially BATCH queries, we explicitely DO NOT want to use bind variables, as they hide information from the Cost Based Opitmizer.
BONUS BONUS: For batch queries from 3rd party apps like peoplesoft, if we can't remove bind variables, we can use bind variable peeking!
BONUS BONUS: For batch queries from 3rd party apps like peoplesoft, if we can't remove bind variables, we can use bind variable peeking!
| Previous Question | Next Question |
| What is the difference between RMAN and a traditional hotbackup? | In PL/SQL, what is bulk binding, and when/how would it help performance? |