Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Pinning/keeping plans in shared pool

Pinning/keeping plans in shared pool

From: Allen, Brandon <>
Date: Fri, 7 Jul 2006 09:10:54 -0700
Message-ID: <04DDF147ED3A0D42B48A48A18D574C45059E1A48@NT15.oneneck.corp>

Oracle, AIX 5.3  

I've got a couple queries that are taking a lot longer to parse since upgrading from 8.0.6 to 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?  

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.

Received on Fri Jul 07 2006 - 11:10:54 CDT

Original text of this message