142 lines
4.0 KiB
Plaintext
Executable File
142 lines
4.0 KiB
Plaintext
Executable File
-- SQL Profile -- chargement à partir d'un plan d'exécution du Library Cache
|
|
-- d'après le blog de Kerry Osborne ( http://kerryosborne.oracle-guy.com/2009/04/oracle-sql-profiles/ )
|
|
-- et un exemple de Christian Antognini ( http://antognini.ch/top/ )
|
|
|
|
DROP TABLE t1;
|
|
|
|
CREATE TABLE t1 (id, col1, col2, pad)
|
|
AS
|
|
SELECT rownum, CASE WHEN rownum>5000 THEN 5000 ELSE rownum END, rownum, lpad('*',100,'*')
|
|
FROM dual
|
|
CONNECT BY level <= 10000;
|
|
|
|
CREATE INDEX t1_col1 ON t1 (col1);
|
|
|
|
-- Calcul des statistiques avec des histogrammes automatiques
|
|
BEGIN
|
|
dbms_stats.gather_table_stats(
|
|
ownname=>user,
|
|
tabname=>'T1',
|
|
cascade=>TRUE,
|
|
estimate_percent=>100,
|
|
method_opt=>'for all columns size skewonly',
|
|
no_invalidate=>FALSE);
|
|
END;
|
|
/
|
|
|
|
-- Du moment que cursor_sharing n'est pas FORCE, des plans d'exécution differents sont
|
|
-- générés en foction de la valeur du litteral
|
|
-- On peut vérifier avec une 2ème session SYSDBA et les requêtes suivantes:
|
|
-- SQL> select sid,serial#,SQL_ID,SQL_CHILD_NUMBER,PREV_SQL_ID,PREV_CHILD_NUMBER from v$session where username=&username;
|
|
-- SQL> select * from table(dbms_xplan.display_cursor(&SQL_ID,&SQL_CHILD_NUMBER,'typical'));
|
|
-- probablement &PREV_SQL_ID et &PREV_CHILD_NUMBER car les requêtes sont rapides
|
|
|
|
|
|
-------------------------------------
|
|
-- Dans une 2ème session AS SYSDBA =>
|
|
-- nettoyage du Library Cache
|
|
alter system flush shared_pool;
|
|
-------------------------------------
|
|
-- Session applicative =>
|
|
-- FULL SCAN
|
|
select * from t1 where col1=5000;
|
|
|
|
-- Dans une 2ème session AS SYSDBA =>
|
|
-- 1er snapshoot AWR
|
|
execute dbms_workload_repository.CREATE_SNAPSHOT;
|
|
|
|
|
|
-- Session applicative =>
|
|
-- ACCES PAR INDEX
|
|
select * from t1 where col1=128;
|
|
|
|
|
|
-- Dans une 2ème session AS SYSDBA =>
|
|
-- 2ème snapshoot AWR
|
|
execute dbms_workload_repository.CREATE_SNAPSHOT;
|
|
|
|
------------------------------------------------------
|
|
-- SESSION 2 as SYSDBA
|
|
-- on récupère le SQL_ID, ainsi que le Plan hash value du SQL qui fait en FULL
|
|
|
|
set pages 999
|
|
set line 200
|
|
col SQL_TEXT for a70 wrap
|
|
|
|
select dbid,sql_id,sql_text from DBA_HIST_SQLTEXT where SQL_TEXT like '%select * from t1 where col1=%';
|
|
|
|
select * from table(dbms_xplan.display_awr('b9tum9b80gsjx')); -- FULL
|
|
|
|
-- Nous alons créer un SQL Profile qui utilise ce denier plan d'exécution (FULL)
|
|
-- les valeurs d'entrée:
|
|
-- sql_id = 'b9tum9b80gsjx' / plan_hash_value=3617692013 / category => 'SQLPROF_CAT_01' / name => 'SQLPROF_SQLPROF_02' / force_match => true
|
|
|
|
/* Randolf Giest */
|
|
-- create sql profile from awr
|
|
-- sql_id plan_hash_valeu category force_matching
|
|
declare
|
|
ar_profile_hints sys.sqlprof_attr;
|
|
cl_sql_text clob;
|
|
begin
|
|
select
|
|
extractvalue(value(d), '/hint') as outline_hints
|
|
bulk collect
|
|
into
|
|
ar_profile_hints
|
|
from
|
|
xmltable('/*/outline_data/hint'
|
|
passing (
|
|
select
|
|
xmltype(other_xml) as xmlval
|
|
from
|
|
dba_hist_sql_plan
|
|
where
|
|
sql_id = 'b9tum9b80gsjx'
|
|
and plan_hash_value = 3617692013
|
|
and other_xml is not null
|
|
)
|
|
) d;
|
|
|
|
select
|
|
sql_text
|
|
into
|
|
cl_sql_text
|
|
from
|
|
dba_hist_sqltext
|
|
where
|
|
sql_id = 'b9tum9b80gsjx';
|
|
|
|
dbms_sqltune.import_sql_profile(
|
|
sql_text => cl_sql_text
|
|
, profile => ar_profile_hints
|
|
, category => 'SQLPROF_CAT_01'
|
|
, name => 'SQLPROF_SQLPROF_02'
|
|
-- use force_match => true
|
|
-- to use CURSOR_SHARING=SIMILAR
|
|
-- behaviour, i.e. match even with
|
|
-- differing literals
|
|
, force_match => true
|
|
);
|
|
end;
|
|
/
|
|
|
|
-- On vérifie que le SQL Plan a été créé
|
|
-- normallement c'est afficher dans la note
|
|
|
|
set line 128
|
|
col sql_text for a40 wrap
|
|
select name,category,sql_text,status,force_matching from DBA_SQL_PROFILES;
|
|
|
|
------------------------------------------------------
|
|
-- On reviens dans la première session (celle applicative)
|
|
-- on change la catègorie courante de SQL Tune
|
|
-- on vérifie que le pla d'exécution est bien celui du SQL Profile
|
|
|
|
alter session set sqltune_category='SQLPROF_CAT_01';
|
|
|
|
explain plan for select * from t1 where col1=5000;
|
|
select * from table(dbms_xplan.display);
|
|
|
|
explain plan for select * from t1 where col1=1200;
|
|
select * from table(dbms_xplan.display);
|