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.
