Tirer le meilleur parti du Partitioning avec une application existante peut nécessiter un travail important ; il s’agit de :
- trouver une stratégie pertinente, dans la durée, selon des objectifs très différents comme les performances, archivage et purge, alimentation et transformations
- transformer le schéma existant en limitant l’impact et le temps d’indisponibilité, cf « 2-3 trucs à propos de DBMS_REDEFINITION » et « 2-3 trucs (de plus) à propos de DBMS_REDEFINITION«
- faire évoluer l’application, si c’est nécessaire, pour tirer le plus d’avantages possible du partitioning
- gérer les risques, en effet, un retour arrière après implémentation peut couter très cher et les temps de réponses de certaines requêtes peuvent se dégrader après la mise en oeuvre du partitioning comme discuté dans « Partitionner un index différemment de sa table«
- réaliser l’ensemble dans un temps qui se compte en jours
- permettre au projet de tirer bénéfice du travail accompli
- gérer la communication avec les utilisateurs et assurer que le bénéfice dure
Pour réussir ce type de projet, s’appuyer autant que possible sur les outils à votre disposition et ainsi réduire le nombre d’étapes est peut-être une bonne idée. Si vous avez des licences Tuning Advisor, donnez une chance au SQL Access Advisor. Il offre en effet une option qui permet de détecter des stratégies de partitioning « payante ». Dans cet article, vous trouverez une illustration de son utilisation.
Pour commencer et pour tout savoir à propos du SQL Access Advisor, regardez la section 18. SQL Access Advisor du Performance Tuning Guide , les packages DBMS_ADVISOR et DBMS_SQLTUNE dans le guide des packages et types PL/SQL et l’ensemble des vues DBA_ADVISOR_XXX dans le Reference Guide.
Une « charge » comme exemple
Dans un premier temps, créez un utilisateur de démonstration nommé DEMO :
connect demo/demo
create table x (
datetime date,
lib varchar2(10),
key1 number,
key2 number);
J’ai créé un ensemble de 4 requêtes pour que l’advisor propose une stratégie de partitioning. Pour faciliter la capture, le script prend des snapshots AWR au fur et à mesure du déroulement du script :
var sn1 number
exec :sn1:=dbms_workload_repository.create_snapshot('ALL');
print sn1
declare
a date;
b varchar2(10);
c number;
d number;
sql_stmt varchar2(256);
sn number;
begin
for i in 1..1000000 loop
a:=trunc(sysdate,'DD')-mod(i,365);
b:='libelle '||to_char(mod(i,13));
c:= mod(i,23);
d:= mod(i,31);
sql_stmt := 'insert /* demo */ into x values (:1, :2, :3, :4)';
execute immediate sql_stmt using a, b, c, d;
commit;
end loop;
sn:=dbms_workload_repository.create_snapshot('ALL');
dbms_stats.gather_table_stats(user, 'X');
for i in 1..40000 loop
a:=trunc(sysdate,'DD')-mod(i,365);
sql_stmt := 'select /* demo */ sum(key1) key1, avg(key2) key2'||
' from x where datetime=:1';
execute immediate sql_stmt into c, d using a;
end loop;
sn:=dbms_workload_repository.create_snapshot('ALL');
for i in 1..100 loop
a:=trunc(sysdate,'DD')-366+i;
sql_stmt := 'delete /* demo */ from X where datetime=:1';
execute immediate sql_stmt using a;
commit;
end loop;
sn:=dbms_workload_repository.create_snapshot('ALL');
end;
/
select /* demo */ b.datetime, sum(a.key1+b.key2) sx
from x a, x b
where a.datetime=b.datetime
and b.datetime between trunc(sysdate,'DD')-20 and trunc(sysdate,'DD')-10
group by b.datetime
/
/
/
/
/
var sn2 number
exec :sn2:=dbms_workload_repository.create_snapshot('ALL');
print sn2
SQL Access Advisor en action
Dans la section qui suit, nous allons mettre en oeuvre le SQL Access Advisor avec la charge qui précéde et pour celà :
- Donner les privilèges à l’utilisateur
grant advisor to demo;
grant administer sql tuning set to demo;
- Créer une tâche de tuning
variable task_id NUMBER;
variable task_name VARCHAR2(255);
execute :task_name := 'ARKZOYD';
execute dbms_advisor.create_task (-
'SQL Access Advisor',-
:task_id, -
:task_name);
- Positionner les paramètres de l’advisor
execute dbms_advisor.set_task_parameter(:task_name, 'ANALYSIS_SCOPE', -
'INDEX, TABLE, PARTITION')
--execute dbms_advisor.set_task_parameter(:task_name, 'ANALYSIS_SCOPE', 'ALL')
execute dbms_advisor.set_task_parameter(:task_name, 'DML_VOLATILITY', 'TRUE')
execute dbms_advisor.set_task_parameter(:task_name, 'MODE', 'COMPREHENSIVE')
--execute dbms_advisor.set_task_parameter(:task_name, 'PARTITIONING_GOAL', -
'PERFORMANCE')
execute dbms_advisor.set_task_parameter(:task_name, 'PARTITIONING_TYPES', 'RANGE')
execute dbms_advisor.set_task_parameter(:task_name, 'REPORT_DATE_FORMAT', -
'DD/MM/YYYY HH24:MI');
execute dbms_advisor.set_task_parameter(:task_name, 'SQL_LIMIT', -
DBMS_ADVISOR.ADVISOR_UNLIMITED);
execute dbms_advisor.set_task_parameter(:task_name, 'TIME_LIMIT', -
DBMS_ADVISOR.ADVISOR_UNLIMITED);
execute dbms_advisor.set_task_parameter(:task_name, 'WORKLOAD_SCOPE', 'FULL');
col parameter_name format a40
col parameter_value format a80
set lines 140
select PARAMETER_NAME, PARAMETER_VALUE
from dba_advisor_parameters
where task_name=:task_name;
- Créer un SQL Tuning Set depuis AWR et l’associer à la tâche
variable workload_name varchar2(255);
execute :workload_name := 'ARKZOYD_STS';
-- create the tuning set
execute DBMS_SQLTUNE.CREATE_SQLSET(:workload_name);
-- populate the tuning set from the cursor cache
DECLARE
cur DBMS_SQLTUNE.SQLSET_CURSOR;
BEGIN
OPEN cur FOR
SELECT VALUE(P)
FROM table(
DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(
begin_snap =>:sn1,
end_snap =>:sn2,
basic_filter =>'parsing_schema_name=''DEMO'' '||
'and sql_text like ''%demo%'' '||
'and sql_text not like ''%declare%''',
attribute_list=>'ALL')) P;
DBMS_SQLTUNE.LOAD_SQLSET(sqlset_name => :workload_name,
populate_cursor => cur);
END;
/
select sql_id, sql_text, executions
from table(dbms_sqltune.SELECT_SQLSET(:workload_name));
SQL_ID SQL_TEXT EXECUTIONS
------------- ------------------------------------------------------ ----------
0xtz8783893f3 select /* demo */ b.datetime, sum(a.key1+b.key2) sx 5
2s66g4cw1kf5c insert /* demo */ into x values (:1, :2, :3, :4) 1000000
47dyumm422n40 delete /* demo */ from X where datetime=:1 100
a2fhh4rthp9bx select /* demo */ sum( key1) key1, avg(key2) key2 from 40000
execute DBMS_ADVISOR.ADD_STS_REF(:task_name, null, :workload_name);
select task_name, workload_name, is_sts
from DBA_ADVISOR_SQLA_WK_MAP
where task_name=:task_name;
TASK_NAME WORKLOAD_NAME IS_STS
--------- ------------- ------
ARKZOYD ARKZOYD_STS 1
- Exécuter la tâche
execute dbms_advisor.execute_task(:task_name);
Note:
Si vous voulez utiliser le SQL Access Advisor avec une unique requête, vous pouvez utiliser la procedureDBMS_ADVISOR.QUICK_TUNE
Visualiser les recommandations
select task_name, advisor_name, status
from dba_advisor_executions
where task_name=:task_name;
TASK_NAME ADVISOR_NAME STATUS
--------- ------------------ ---------
ARKZOYD SQL Access Advisor COMPLETED
SELECT REC_ID, RANK, BENEFIT
FROM DBA_ADVISOR_RECOMMENDATIONS
WHERE TASK_NAME = :task_name;
SELECT sql_id, rec_id, precost, postcost,
(precost-postcost)*100/precost AS percent_benefit
FROM DBA_ADVISOR_SQLA_WK_STMTS
WHERE TASK_NAME = :task_name AND workload_name = :workload_name;
SQL_ID REC_ID PRECOST POSTCOST PERCENT_BENEFIT
------------- ---------- ---------- ---------- ---------------
0xtz8783893f3 1 11985 10750 10.3045474
2s66g4cw1kf5c 1 1000000 1031000000 -103000
47dyumm422n40 1 110100 2200 98.0018165
a2fhh4rthp9bx 1 44040000 880000 98.0018165
SELECT rec_id, action_id, SUBSTR(command,1,30) AS command
FROM user_advisor_actions
WHERE task_name = :task_name
ORDER BY rec_id, action_id;
REC_ID ACTION_ID COMMAND
------ --------- ---------------
1 1 PARTITION TABLE
Vous pouvez récupérer un script qui effectue la transformation. Bien sur, vous utiliserez plutôt DBMS_REDEFINITION ou GoldenGate/Streams si votre système est déjà en production :
var scr clob
exec :scr:=dbms_advisor.get_task_script(:task_name)
set long 10000
set longchunksize 10000
print report
SCR
--------------------------------------------------------
Rem SQL Access Advisor: Version 11.2.0.2.0 - Production
Rem
Rem Username: DEMO
Rem Task: ARKZOYD
Rem Execution date:
Rem
Rem
Rem Repartitioning table "DEMO"."X"
Rem
SET SERVEROUTPUT ON
SET ECHO ON
Rem
Rem Creating new partitioned table
Rem
CREATE TABLE "DEMO"."X1"
( "DATETIME" DATE,
"LIB" VARCHAR2(10),
"KEY1" NUMBER,
"KEY2" NUMBER
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
TABLESPACE "USERS"
PARTITION BY RANGE ("DATETIME") ( PARTITION VALUES LESS THAN (TO_DATE(' 2009-11-08 00:00:00'
, 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')), PARTITION VALUES
LESS THAN (TO_DATE(' 2009-11-15 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
'NLS_CALENDAR=GREGORIAN')), PARTITION VALUES LESS THAN (TO_DATE(' 2009-11-22 00:00:00'
, 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')), PARTITION VALUES
LESS THAN (TO_DATE(' 2009-11-29 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
'NLS_CALENDAR=GREGORIAN')), PARTITION VALUES LESS THAN (TO_DATE(' 2009-12-06 00:00:00'
, 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')), PARTITION VALUES
LESS THAN (TO_DATE(' 2009-12-13 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
'NLS_CALENDAR=GREGORIAN')), PARTITION VALUES LESS THAN (TO_DATE(' 2009-12-20 00:00:00'
, 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')), PARTITION VALUES
LESS THAN (TO_DATE(' 2009-12-27 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
'NLS_CALENDAR=GREGORIAN')), PARTITION VALUES LESS THAN (TO_DATE(' 2010-01-03 00:00:00'
, 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')), PARTITION VALUES
LESS THAN (TO_DATE(' 2010-01-10 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
'NLS_CALENDAR=GREGORIAN')), PARTITION VALUES LESS THAN (TO_DATE(' 2010-01-17 00:00:00'
, 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')), PARTITION VALUES
LESS THAN (TO_DATE(' 2010-01-24 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
'NLS_CALENDAR=GREGORIAN')), PARTITION VALUES LESS THAN (TO_DATE(' 2010-01-31 00:00:00'
, 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')), PARTITION VALUES
LESS THAN (TO_DATE(' 2010-02-07 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
'NLS_CALENDAR=GREGORIAN')), PARTITION VALUES LESS THAN (TO_DATE(' 2010-02-14 00:00:00'
, 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')), PARTITION VALUES
LESS THAN (TO_DATE(' 2010-02-21 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
'NLS_CALENDAR=GREGORIAN')), PARTITION VALUES LESS THAN (TO_DATE(' 2010-02-28 00:00:00'
, 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')), PARTITION VALUES
LESS THAN (TO_DATE(' 2010-03-07 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
'NLS_CALENDAR=GREGORIAN')), PARTITION VALUES LESS THAN (TO_DATE(' 2010-03-14 00:00:00'
, 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')), PARTITION VALUES
LESS THAN (TO_DATE(' 2010-03-21 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
'NLS_CALENDAR=GREGORIAN')), PARTITION VALUES LESS THAN (TO_DATE(' 2010-03-28 00:00:00'
, 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')), PARTITION VALUES
LESS THAN (TO_DATE(' 2010-04-04 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
'NLS_CALENDAR=GREGORIAN')), PARTITION VALUES LESS THAN (TO_DATE(' 2010-04-11 00:00:00'
, 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')), PARTITION VALUES
LESS THAN (TO_DATE(' 2010-04-18 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
'NLS_CALENDAR=GREGORIAN')), PARTITION VALUES LESS THAN (TO_DATE(' 2010-04-25 00:00:00'
, 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')), PARTITION VALUES
LESS THAN (TO_DATE(' 2010-05-02 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
'NLS_CALENDAR=GREGORIAN')), PARTITION VALUES LESS THAN (TO_DATE(' 2010-05-09 00:00:00'
, 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')), PARTITION VALUES
LESS THAN (TO_DATE(' 2010-05-16 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
'NLS_CALENDAR=GREGORIAN')), PARTITION VALUES LESS THAN (TO_DATE(' 2010-05-23 00:00:00'
, 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')), PARTITION VALUES
LESS THAN (TO_DATE(' 2010-05-30 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
'NLS_CALENDAR=GREGORIAN')), PARTITION VALUES LESS THAN (TO_DATE(' 2010-06-06 00:00:00'
, 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')), PARTITION VALUES
LESS THAN (TO_DATE(' 2010-06-13 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
'NLS_CALENDAR=GREGORIAN')), PARTITION VALUES LESS THAN (TO_DATE(' 2010-06-20 00:00:00'
, 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')), PARTITION VALUES
LESS THAN (TO_DATE(' 2010-06-27 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
'NLS_CALENDAR=GREGORIAN')), PARTITION VALUES LESS THAN (TO_DATE(' 2010-07-04 00:00:00'
, 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')), PARTITION VALUES
LESS THAN (TO_DATE(' 2010-07-11 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
'NLS_CALENDAR=GREGORIAN')), PARTITION VALUES LESS THAN (TO_DATE(' 2010-07-18 00:00:00'
, 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')), PARTITION VALUES
LESS THAN (TO_DATE(' 2010-07-25 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
'NLS_CALENDAR=GREGORIAN')), PARTITION VALUES LESS THAN (TO_DATE(' 2010-08-01 00:00:00'
, 'SYYYY-MM -DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')), PARTITION VALUES
LESS THAN (TO_DATE(' 2010-08-08 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
'NLS_CALENDAR=GREGORIAN')), PARTITION VALUES LESS THAN (TO_DATE(' 2010-08-15 00:00:00'
, 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')), PARTITION VALUES
LESS THAN (TO_DATE(' 2010-08-22 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
'NLS_CALENDAR=GREGORIAN')), PARTITION VALUES LESS THAN (TO_DATE(' 2010-08-29 00:00:00'
, 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')), PARTITION VALUES
LESS THAN (TO_DATE(' 2010-09-05 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
'NLS_CALENDAR=GREGORIAN')), PARTITION VALUES LESS THAN (TO_DATE(' 2010-09-12 00:00:00'
, 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')), PARTITION VALUES
LESS THAN (TO_DATE(' 2010-09-19 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
'NLS_CALENDAR=GREGORIAN')), PARTITION VALUES LESS THAN (TO_DATE(' 2010-09-26 00:00:00'
, 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')), PARTITION VALUES
LESS THAN (TO_DATE(' 2010-10-03 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
'NLS_CALENDAR=GREGORIAN')), PARTITION VALUES LESS THAN (TO_DATE(' 2010-10-10 00:00:00'
, 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')), PARTITION VALUES
LESS THAN (TO_DATE(' 2010-10-17 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
'NLS_CALENDAR=GREGORIAN')), PARTITION VALUES LESS THAN (TO_DATE(' 2010-10-24 00:00:00'
, 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')), PARTITION VALUES
LESS THAN (TO_DATE(' 2010-10-31 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
'NLS_CALENDAR=GREGORIAN')), PARTITION VALUES LESS THAN (MAXVALUE) );
Rem
Rem Populating new partitioned table with data from original table
Rem
INSERT /*+ APPEND */ INTO "DEMO"."X1"
SELECT * FROM "DEMO"."X";
COMMIT;
begin
dbms_stats.gather_table_stats('"DEMO"', '"X1"', NULL, dbms_stats.auto_sample_size);
end;
/
Rem
Rem Renaming tables to give new partitioned table the original table name
Rem
ALTER TABLE "DEMO"."X" RENAME TO "X11";
ALTER TABLE "DEMO"."X1" RENAME TO "X";
Conclusion
Avant de terminer, nettoyez votre environnement :
EXECUTE DBMS_ADVISOR.RESET_TASK(:task_name);
execute DBMS_ADVISOR.DELETE_STS_REF (:task_name,null, :workload_name)
EXECUTE DBMS_ADVISOR.DELETE_TASK(:task_name);
exec dbms_sqltune.drop_sqlset(:workload_name)
drop table x purge;
connect / as sysdba
drop user demo cascade;
Pour que l’advisor soit efficace, il faut une certaine charge. C’est la raison pour laquelle l’exemple nécessite un peu de temps pour s’exécuter…
