Flush package cache for one sql

[Arun Srinivasan]

I've been trying to tune this complex sql that uses the 'exists'
clause. It is damn slow, even though the tables are not big and I have
indexes on the columns in the sql. Currently I am trying MQTs, but
when I was tuning them, I was changing the reopt parameter in the
set_routine_opt s procedure, and also playing with optimization level.
But I couldnt get db2 to give me different answer when I don't flush
package cache!! (eventhough I used reopt always). But since this is a
big db used by many other applications,
I cannot flush the package cache repeatedly. Is there a way to flush
just the sql's execution plan in the memory instead of the whole
thing? I know this is a big ask!!

[db2start@gmail.com]

Re: Flush package cache for one sql

Do you have an explain plan for this query at the different
optimization levels? Are the statistics current for the tables and
indexes used by the query?

Cheers,


On Feb 6, 6:58 am, Arun Srinivasan <arunro...@gmai l.comwrote:

I've been trying to tune this complex sql that uses the 'exists'
clause. It is damn slow, even though the tables are not big and I have
indexes on the columns in the sql. Currently I am trying MQTs, but
when I was tuning them, I was changing the reopt parameter in the
set_routine_opt s procedure, and also playing with optimization level.
But I couldnt get db2 to give me different answer when I don't flush
package cache!! (eventhough I used reopt always). But since this is a
big db used by many other applications,
I cannot flush the package cache repeatedly. Is there a way to flush
just the sql's execution plan in the memory instead of the whole
thing? I know this is a big ask!!

[Serge Rielau]

Re: Flush package cache for one sql

Arun Srinivasan wrote:

I've been trying to tune this complex sql that uses the 'exists'
clause. It is damn slow, even though the tables are not big and I have
indexes on the columns in the sql. Currently I am trying MQTs, but
when I was tuning them, I was changing the reopt parameter in the
set_routine_opt s procedure, and also playing with optimization level.
But I couldnt get db2 to give me different answer when I don't flush
package cache!! (eventhough I used reopt always). But since this is a
big db used by many other applications,
I cannot flush the package cache repeatedly. Is there a way to flush
just the sql's execution plan in the memory instead of the whole
thing? I know this is a big ask!!

There are two ways to invalidate objects in the dyanamic package cache:
Hard invalidation: Shoot something that object needs to live
Soft invalidation: Add/change something that object might like: RUNSTATS

There is no option to purge a specific object from the cache.
Soft invalidation may depend on your version....

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[jefftyzzer]

Re: Flush package cache for one sql

On Feb 5, 11:58 am, Arun Srinivasan <arunro...@gmai l.comwrote:

I've been trying to tune this complex sql that uses the 'exists'
clause. It is damn slow, even though the tables are not big and I have
indexes on the columns in the sql. Currently I am trying MQTs, but
when I was tuning them, I was changing the reopt parameter in the
set_routine_opt s procedure, and also playing with optimization level.
But I couldnt get db2 to give me different answer when I don't flush
package cache!! (eventhough I used reopt always). But since this is a
big db used by many other applications,
I cannot flush the package cache repeatedly. Is there a way to flush
just the sql's execution plan in the memory instead of the whole
thing? I know this is a big ask!!

So you've altered the SP to be REOPT = ALWAYS?

[Arun Srinivasan]

Re: Flush package cache for one sql

On Feb 5, 3:01 pm, jefftyzzer <jefftyz...@sbc global.netwrote :

On Feb 5, 11:58 am, Arun Srinivasan <arunro...@gmai l.comwrote:
>

I've been trying to tune this complex sql that uses the 'exists'
clause. It is damn slow, even though the tables are not big and I have
indexes on the columns in the sql. Currently I am trying MQTs, but
when I was tuning them, I was changing the reopt parameter in the
set_routine_opt s procedure, and also playing with optimization level.
But I couldnt get db2 to give me different answer when I don't flush
package cache!! (eventhough I used reopt always). But since this is a
big db used by many other applications,
I cannot flush the package cache repeatedly. Is there a way to flush
just the sql's execution plan in the memory instead of the whole
thing? I know this is a big ask!!

>
So you've altered the SP to be REOPT = ALWAYS?

Yes, I used set_routine_opt s ('REOPT ALWAYS EXPLSNAP ALL QUERYOPT 7')
TO increase the optimization level. But the following is a part in the
output from db2exfmt.
Also, I ran the set_routine_opt s and the sql in the same command
window. Thus the options should be valid for the session right?

SQL Type: Dynamic
Optimization Level: 5
Blocking: Block All Cursors
Isolation Level: Cursor Stability
Please advise.