0

new to oracle dba,

i wanted to ask about how the shared pool work especially about library cache and result cache

for example a session issues

select empname from employee where emp_id=10

and i issues similar query

select empname from employee where emp_id=20

does my server process use the parsed sql statement issued by the other session?

Improve this question

1 Answer 1

Reset to default
1

No. You should use a bind variable instead of the literal numbers, this will allow sharing of the same cursor. Use of bind variables is also important for preventing SQL Injection when including user input.

- If you have configured cursor sharing FORCE, then these two statements will be written automatically to use binds, however this is not recommended when you have the ability to just use binds yourself. There are performance implications when you let Oracle just replace every literal with a bind variable.

Improve this answer
4
  • so you saying both session need to use bind variable in order for my sql statement to use the parsed sql statement stored in the library cache by the previous executed sql statement? also since the result is different does that mean my sql statement doesn use the result cache?
    – Silent Boots
    Commented Oct 4, 2021 at 11:52
  • The SQL needs to be identical (including white space and casing) in order to use the same cursor. The result cache would need the same inputs and needs to be enabled to be used. It’s mostly not used in reality as the buffer cache will be doing a lot of the work for you and result cache invalidation is a pain.
    – Andrew Sayer
    Commented Oct 4, 2021 at 12:02
  • this may sound stupid but does the server process access the datablock in buffer cache /datafile when we use parsed sql statement stored in library cache? i assume we acces the datablock since the server process need to return the result to user process
    – Silent Boots
    Commented Oct 4, 2021 at 12:18
  • The buffer cache is a resource available to any cursor, regardless of whether the cursor itself is cached or not. They are separate mechanisms. btw "result cache" is something different.
    – William Robertson
    Commented Oct 4, 2021 at 23:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.