Enlever un énoncé SQL de la cache (SHARED POOL)

Voici un script que j'utilise pour supprimer une requête (énoncé SQL) de la cache partagée nommé "shared pool". Ce script est fort utile lorsque vous effectuez du "tuning" de requête.

REM *******************************************************************
REM * Script: FlushSQL.sql
REM * Titre : Chercher et générer la commande pour enlever un énoncé
REM * SQL du SHARED POOL
REM * Auteur: Eric Cloutier
REM * Date modif. : 15-06-2010
REM * Parametres :
REM * Aucun
REM *******************************************************************
set pagesize 9999 feed off linesize 200 trimspoo on verify off
--
-- Sous 10g (10.2.0.4) :
-- Alter session set events '5614566 trace name context forever';
--
spool FlushSQL.log

Accept sql_text prompt 'Inscrire une partie de la requête (LIKE est utilisé alors inscrire %): '

col cmd_sql format a80
col username format a20
col executions format 999999
col sql_text format a30 wrap

select 'exec sys.dbms_shared_pool.purge('''||address||','||hash_value||''',''C'',1);' cmd_sql,
sql_id, child_number, executions, u.username, sql_text
from v$sql s, dba_users u
where upper(sql_text) like upper(nvl('&sql_text',sql_text))
and sql_text not like '%from v$sql where sql_text like nvl(%'
and u.user_id = s.parsing_user_id
/

spool off
set feed on linesize 5000
Prompt
Prompt -- Résultat dans le fichier : FlushSQL.log
Prompt

Cette fonctionnalité peut être utilisé en 10g (10.2.0.4) cependant, vous devez initialiser un "event" soit à la session ou au niveau de la base de données que qu'elle fonctionne.

Un merci particulier à Kerry Osborne pour son blog.

Jouons à cache-cache

À l'occasion, il peut être très payant de conserver en mémoire certains objets qui sont accédés fréquemment.

Objets PL/SQL
Les objets sont conservés dans l'espace mémoire partagé (shared pool) en utilisant le package « dbms_shared_pool ». Ce dernier doit être installé à partir du script « dbmspool.sql ».

Voici comment mettre en mémoire un objet :

execute dbms_shared_pool.keep('propriétaire.nom_objet');

Pour lister tous les objets qui sont conservés dans l'espace mémoire partagé :

select owner,name,type,sharable_mem from v$db_object_cache where kept='YES';

Pour identifier les objets qui pourrait être conservés en mémoire :

select substr(owner,1,10)'.'substr(name,1,35) "ObjectName",
type, sharable_mem,loads, executions, kept
from v$db_object_cache
where type in ('TRIGGER','PROCEDURE','PACKAGE BODY','PACKAGE')
and executions > 0
order by executions desc,loads desc,sharable_mem desc;

Certains objets appartenant à SYS pourraient être des candidats très intéressants, dépendamment de leur utilisation.

Tables et Indexes

Initialiser le cache « KEEP »

Alter system set db_keep_cache_size = 208M scope=spfile;
-- Peut être très intéressant pour les grosses tables qui sont chargées à 'occasion. Les données misent en cache n'affecteront pas les caches habituelles.
-- alter system set db_recycle_cache_size = 50M;
shutdown immediate
startup

Générer la commande mettant les tables candidates en cache « KEEP »

select 'Alter table 'owner'.'table_name
' storage (buffer_pool keep);', num_rows
from dba_tables
where owner in ('SCHEMA_1','SCHEMA_2')
and table_name in ('TABLE_1','TABLE_2')
order by num_rows
/

Générer la commande mettant les indexes candidates en cache « KEEP »

select 'Alter index 'owner'.'index_name
' storage (buffer_pool keep);', num_rows
from dba_indexes
where owner in ('SCHEMA_1','SCHEMA_2') and table_name in
('TABLE_1','TABLE_2')
order by num_rows
/

Charger les données dans le pool « KEEP »

select 'Select /*+ FULL(TABL) */ * from '
owner'.'table_name' TABL;'
from dba_tables
where owner in ('SCHEMA_1','SCHEMA_2')
and table_name in ('TABLE_1','TABLE_2')
order by num_rows
/