Pinning/keeping plans in shared pool
- From: "Allen, Brandon" <Brandon.Allen@xxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 7 Jul 2006 09:10:54 -0700
Oracle 10.2.0.2, AIX 5.3
I've got a couple queries that are taking a lot longer to parse since
upgrading from 8.0.6 to 10.2.0.2. I've posted about these earlier -
they are 20+ table outer joins and they now take about 10-20 seconds to
parse in 10g vs. < 1 second in Oracle 8. I've opened an SR with Oracle
but haven't gotten any response yet. In the meantime, I thought a good
solution/workaround would be to keep/pin these queries in shared memory
and thereby avoid reparsing them, so I did this with the
dbms_shared_pool.keep procedure, passing it the address and hash_value
of the queries, e.g.:
execute dbms_shared_pool.keep('07000000A5B25F50,2379011822','z');
This seems to be work - I don't get any errors, and I can see the cursor
is pinned by querying v$db_object_cache where kept = 'YES' and type =
'CURSOR', and by querying v$sql where kept_versions > 0. The problem is
that the execution plan still seems to get aged out of the pool - if I
query v$sql_plan for these hash_values, I get no rows, and when I run
the queries again, they still take a long time to parse. This is my
first experience with keeping objects in the shared pool so I don't know
if this is the normal behaviour. Has anyone else seen the same? Is
there anyway to make it keep the plan in the pool also? If it doesn't
keep the plan in the pool, then what is the point of keeping a cursor at
all?
Thanks,
Brandon Allen
Privileged/Confidential Information may be contained in this message or
attachments hereto. Please advise immediately if you or your employer do not
consent to Internet email for messages of this kind. Opinions, conclusions and
other information in this message that do not relate to the official business
of this company shall be understood as neither given nor endorsed by it.
Other related posts: