Follow-up of one discussion

That is short post related to a recent discussion on
Jonanthan Lewis’s blog.

During my experiments I found that sys.dbms_shared_pool.purge removes cursor from vsql (v$sqlarea) but it is still in x$kglob

You can easily check it by yourself

select ‘dskhk56khkj’ FROM dual;

— check that the cursor above in the shared pool (visible part)

select a.* from v$sql a
where sql_text like ‘select ”dskhk56khkj%’;

you can see one record there

— and you can execute below as SYS user
— select kglhdpar, kglhdadr, kglobt03, kglnaobj from x$kglob where kglnaobj
— like ‘select ”dskhk56khkj%’ and kglnaobj not like ‘%kgl%’;

Let’s clean up our cursor

column pexec NEW_VALUE doexec
select ‘sys.dbms_shared_pool.purge(”’|| address || ‘,’ || hash_value || ”’, ”c”)’ pexec from v$sqlarea
where sql_text like ‘select ”dskhk56khkj%’;

exec &doexec

and check shared pool for the cursor again

select a.* from v$sql a
where sql_text like ‘select ”dskhk56khkj%’;

The cursor has gone

However if you execute the query below (AS SYS), you still would see some records (actually 2)
— select kglhdpar, kglhdadr, kglobt03, kglnaobj from x$kglob where kglnaobj
— like ‘select ”dskhk56khkj%’ and kglnaobj not like ‘%kgl%’;

PS
ALTER SYSTEM FLUSH SHARED_POOL
behaves differently and cleans up all stuff completely.

PPS
That “hidden area” can keep some cursor statistics like LOADS so you can see how some attributes were “resurrected from nothing” 🙂

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s