How to pin the SQL statement into the memory?

junaid_baig1 used Ask the Experts™
Hello Experts

We have ORACLE 10GR2 on UNIX server and some of SQL statements run much longer on first after that it only run few seconds. I know the reason first time SQL statements run need parse and other things, after that SQL statement on cache (SGA) and it will run shorter. But eventually SQL statement still will be age out from SGA.
Does there has way to make SQL statement stay longer on cache (SGA)?

Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Hi Junaid,

I think you problem is related to age out of Data and not the Sql statement.

Let us know the type of sql statement you fire, is it Select or update or delete?

You can keep sql statement in memory using dbms_keep() package, but I think this may not be solution for you if problem is due to data age out.


Most Valuable Expert 2012
Distinguished Expert 2018
I agree with POracle.  More than likely it's the data blocks being swapped out of the SGA not the parsing of the SQL itself.

I would also suggest you not pin data.  This might sound like a silver-bullet but might cause some major performance issues elsewhere.

I just wanted to add that if you were on 11g, you might look into the new result caching feature.


Thanks !!  

  @ POracle:  it's the data blocks being swapped out of the SGA not the parsing of the SQL itself.  You r right!!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial