Oracle RAC : HOW TO install and use Statspack

Si vous n’avez pas accès à AWR à cause de l’absence de licences Diagnostic pack / tuning Pack, ou tout simplement vous souhaitez utiliser le bon vieux statspack sur une base Cluster, cet article vous permettera de le configurer de telle façon à ce que la collecte sur les différentes instances ne pose pas de problème.

En effet, surtout sur les RAC disposant de plusieurs noeuds (6 – 8), il peut être généré des locks dans les tables statspack quand une instance tente de collecter et de mettre à jour les tables, pendant qu’une autre est déja en train d’insérer des données…

Même si on revoit l’ordonnancement, cette problématique peut arriver.

-> L’idéal est donc d’avoir une procédure qui réalise la collecte tout en vérifiant qu’il n’y a pas de lock pour pourrait empecher la collecte de clichés.

Cette procédure devra être appelée par un job, qui lui même fera parti d’une CLASS, lié à un service RAC défini sur une instance nommée.

Dans cet article, il s’agit d’un RAC 2 node, mais bien sur cela peut être fait de façon identique (moyennant l’ajout d’actions relatif au nombre de noeuds) sur un Rac 4 – 6 – 8 …. noeuds.

Methode :

## Installation statspack :

L’installation est identique à une instance single :

conn / as sysdba
@?/rdbms/admin/spcreate.sql

## Attribuer les droits sur DBMS_LOCK pour Perfstat :

grant execute on dbms_lock to perfstat;

## creation d’une procédure qui gerera les clichés, appartenant au user « perfstat »

create or replace procedure db_proc_rac_statspack as
w_status number(38);
w_handle varchar2(60);
w_snap_level number;
begin
w_snap_level := 7;
sys.dbms_lock.allocate_unique(
lockname => 'Synchronize Statspack',
lockhandle => w_handle
);
w_status := sys.dbms_lock.request(
lockhandle => w_handle,
lockmode => dbms_lock.x_mode,
timeout => 300, -- en secondes, default est: dbms_lock.maxwait
release_on_commit => false -- Defaut
);
if (w_status = 0 ) then
dbms_output.put_line(
to_char(sysdate,'dd hh24:mi:ss') ||
': Acquired lock, running statspack'
);
statspack.snap(w_snap_level);
dbms_output.put_line(
to_char(sysdate,'dd hh24:mi:ss') ||
': Snapshot completed'
);
w_status := sys.dbms_lock.release(
lockhandle => w_handle
);
else
dbms_output.put_line(
to_char(sysdate,'dd hh24:mi:ss') ||
case w_status
when 1 then ': Lock wait timed out'
when 2 then ': deadlock detected'
when 3 then ': parameter error'
when 4 then ': already holding lock'
when 5 then ': illegal lock handle'
else ': unknown error'
end
);
end if;
end;
/

## Ajout de services RAC qui seront utilisés par les jobs pour pouvoir prendre des clichés sur les 2 instances :

– Création :

srvctl add service -d ASPUTF8 -s statspack_node1_srvc -r myinstance1
srvctl add service -d ASPUTF8 -s statspack_node2_srvc -r myinstance2

– Démarrage des services :

srvctl start service -d MYBASE -s statspack_node1_srvc
srvctl start service -d MYBASE -s statspack_node2_srvc

– Status / config :

srvctl status service -d MYBASE
srvctl config service -d MYBASE

## Creation des jobs class en spécifiant les services crées, via le compte SYS


BEGIN
DBMS_SCHEDULER.create_job_class(
job_class_name => 'statspack_node1_class',
service => 'statspack_node1_srvc');
DBMS_SCHEDULER.create_job_class(
job_class_name => 'statspack_node2_class',
service => 'statspack_node2_srvc');
END;
/

## Une petite vérification :

select job_class_name, service from dba_scheduler_job_classes

JOB_CLASS_NAME SERVICE
------------------------------ ----------------------------------------------------------------
ORA$AT_JCMED_SQ
AQ$_PROPAGATION_JOB_CLASS
XMLDB_NFS_JOBCLASS
STATSPACK_NODE1_CLASS statspack_node1_srvc
STATSPACK_NODE2_CLASS statspack_node2_srvc

## Ajout des permissions sur les class précedemment crées pour perfstat :

GRANT EXECUTE ON sys.statspack_node1_class TO perfstat;
GRANT EXECUTE ON sys.statspack_node2_class TO perfstat;

## Creation d’un job en associant la class qui convient (user perfstat)

Prerequis : en sys

grant create job to perfstat;
grant execute on dbms_scheduler to perfstat;

Depuis le user Perfstat :

BEGIN
DBMS_SCHEDULER.create_program (
program_name => 'PROC_RAC_STATSPACK',
program_type => 'STORED_PROCEDURE',
program_action => 'db_proc_rac_statspack',
enabled => TRUE
);
END;
/

##note : si des jobs existent :


BEGIN
dbms_scheduler.drop_job('MYJOBDECOLLECTE');
END;
/

## A ce moment, il reste à creer les jobs (collecte / purge), et ce pour toutes les class précédemment crées (pour tous les noeuds du cluster) :

Toujours depuis le compte perfstat, pour une planification toutes les 30 min :

--- creation du job pour le noeud 1 :

BEGIN
dbms_scheduler.create_job(
job_name => 'CMS_PERFSTAT_COLLECT_N1',
program_name => 'PROC_RAC_STATSPACK',
start_date => SYSTIMESTAMP,
repeat_interval=>'FREQ=hourly; INTERVAL=1; BYMINUTE=30',
job_class => 'statspack_node1_class',
comments => 'This job will run on MYINSTANCE1',
enabled => TRUE);

dbms_scheduler.create_job
(job_name=>'CMS_PERFSTAT_PURGE_N1',
job_type=>'PLSQL_BLOCK',
job_action=>'begin STATSPACK.PURGE(31); end;',
start_date=>SYSTIMESTAMP,
repeat_interval=>'FREQ=DAILY; BYHOUR=23; BYMINUTE=30',
job_class => 'statspack_node1_class',
enabled=>TRUE);
END;
/

--- creation du job pour le noeud 2 :

BEGIN
dbms_scheduler.create_job(
job_name => 'CMS_PERFSTAT_COLLECT_N2',
program_name => 'PROC_RAC_STATSPACK',
start_date => SYSTIMESTAMP,
repeat_interval=>'FREQ=hourly; INTERVAL=1; BYMINUTE=30',
job_class => 'statspack_node2_class',
comments => 'This job will run on MYINSTANCE2',
enabled => TRUE);

dbms_scheduler.create_job
(job_name=>'CMS_PERFSTAT_PURGE_N2', job_type=>'PLSQL_BLOCK',
job_action=>'begin STATSPACK.PURGE(31); end;',
start_date=>SYSTIMESTAMP,
repeat_interval=>'FREQ=DAILY; BYHOUR=23; BYMINUTE=30',
job_class => 'statspack_node2_class',
enabled=>TRUE);
END;
/

## Verifier les jobs

select OWNER, JOB_NAME, STATE, START_DATE, ENABLED from dba_scheduler_jobs where owner= 'PERFSTAT';

OWNER JOB_NAME STATE START_DATE ENABL
---------- ------------------------------ --------------- --------------------------------------------------------------------------- -----
PERFSTAT CMS_PERFSTAT_PURGE_N1 SCHEDULED 20/03/14 16:46:07,623233 +01:00 TRUE
PERFSTAT CMS_PERFSTAT_COLLECT_N2 SCHEDULED 20/03/14 16:47:59,093053 +01:00 TRUE
PERFSTAT CMS_PERFSTAT_COLLECT_N1 SCHEDULED 20/03/14 16:46:07,549197 +01:00 TRUE
PERFSTAT CMS_PERFSTAT_PURGE_N2 SCHEDULED 20/03/14 16:47:59,151279 +01:00 TRUE

## On vérifie les snapshots , via le user perfstat

Sur le noeud 1 :

EXEC dbms_scheduler.run_job('CMS_PERFSTAT_COLLECT_N1');

Sur le noeud 2 :

EXEC dbms_scheduler.run_job('CMS_PERFSTAT_COLLECT_N2');

Vérification :

alter session set nls_date_format='dd-mm-yy hh24:mi:ss';
select INSTANCE_NUMBER, SNAP_TIME from stats$snapshot order by 1, 2;

INSTANCE_NUMBER SNAP_TIME
--------------- -----------------
1 20-03-14 17:01:09
2 20-03-14 17:06:19

Enjoy !!

Oracle – Etude du clustering Factor

Etude du clustering Factor

Qu’est ce que le clustering Factor ?

Une définition assez simple : Le clustering factor, ou facteur d’ordonnancement des indexes, est un indicateur qui permet d’identifier l’ordonnancement des données au sein d’un index, plus particulièrement au niveau des « feuilles » de ce dernier. Il permet à l’optimiseur Oracle de préférer soit un scan table, ou un scan index. Il a donc une importance fondamentale sur le comportement et la performance d’une base de données Oracle.

Une requète simple sur la vue « dba_indexes » permet de mettre en relation cet indicateur, ainsi que le nombre de block et de rows contenu(e)s dans une table.

break on table_name skip 1;
select t.table_name,i.index_name,i.clustering_factor,t.blocks,t.num_rows
from dba_indexes i , dba_tables t
where i.table_name=t.table_name and t.owner='MYUSER'
order by t.num_rows ;

Exemple de résultat :

TABLE_NAME INDEX_NAME CLUSTERING_FACTOR BLOCKS NUM_ROWS
------------------------------ ------------------------------ ----------------- ---------- ----------
LAD LAD_IDX1 706753 84224 9794553
LAD_IDX2 676953 84224 9794553

TIE_CMP TIE_CMP_IDX1 10144987 204688 10170913
IDX_TYPTIE_VALZN90 291720 204688 10170913

Attention : Les statistiques du schéma se doivent d’être à jour pour réaliser cette étude.

Pour résumer le résultat et l’interpretation de cette requète :

Le clustering factor pour la table LAD reste plus proche du nombre de block de la table que celui du nombre total de rows. On peut donc indiquer que l’ordonnencement des données dans les feuilles de l’index est somme toute correct. En effet, l’idéal est d’avoir un clustering factor le plus proche possible du nombre de blocks d’une table.

En revanche, pour la table TIE_CMP, le clustering factor pour l’index TIE_CMP_IDX1 est quasiment égal au nombre de lignes de la table, ce qui indique que les données de l’index seraient ordonnancés de manière « aléatoire » , et que l’optimiseur oracle pourrait préferer un full scan de la table plutot que d’utiliser cet index.

Pour tenter d’améliorer cet indicateur et favoriser l’utilisation d’un indexes, soit il faut revoir son code, soit tenter une réorganisation de la table avec reconstrution de son index.

Oracle : Max sessions connectées depuis le dernier startup

Une petite requète bien pratique, qui peut donner l’idée de faire un trigger (pour effectuer un historique) concernant le nombre maximal de sessions connectées depuis le dernier startup :

SELECT RPAD(C.NAME||':',11)||RPAD(' current logons='||
(TO_NUMBER(B.SESSIONS_CURRENT)),20)||' maximum connected='||
B.SESSIONS_HIGHWATER INFORMATION
FROM V$LICENSE B, V$DATABASE C;

Mickael.

Oracle 10G – Etude des Caches

Etudes de la taille des caches, sous Oracle 10G

-> tout d’abord, le paramètre DB_CACHE_ADVICE doit être positionné à ON sur l’instance souhaitée.

Ensuite, lancer la requète ci dessous et il suffit ensuite d’analyser pour quelle valeur les balayages physiques sont moins importants.


break on NAME skip 1
set lines 120
col NAME for a10
select NAME, SIZE_FOR_ESTIMATE, SIZE_FACTOR, ESTD_PHYSICAL_READS, ESTD_PCT_OF_DB_TIME_FOR_READS
from v$db_cache_advice
[/code]

Exemple de sortie :

NAME SIZE_FOR_ESTIMATE SIZE_FACTOR ESTD_PHYSICAL_READS ESTD_PCT_OF_DB_TIME_FOR_READS
---------- ----------------- ----------- ------------------- -----------------------------
KEEP 192 ,0938 5733378 51,8
384 ,1875 4472606 40,1
576 ,2813 3592785 31,9
768 ,375 2684520 23,4
960 ,4688 1724608 14,5
1152 ,5625 1150278 9,1
1344 ,6563 674594 4,7
1536 ,75 359401 1,8
1728 ,8438 230522 ,5
1920 ,9375 226079 ,5
2048 1 226079 ,5
2112 1,0313 226079 ,5
2304 1,125 226079 ,5
2496 1,2188 226079 ,5
2688 1,3125 226079 ,5
2880 1,4063 226079 ,5
3072 1,5 226079 ,5
3264 1,5938 226079 ,5
3456 1,6875 226079 ,5
3648 1,7813 226079 ,5
3840 1,875 226079 ,5

RECYCLE 192 ,0938 1476336 2
384 ,1875 1428971 1,9
576 ,2813 1413849 1,9
768 ,375 1404536 1,9
960 ,4688 1401400 1,9
1152 ,5625 1398679 1,9
1344 ,6563 1395143 1,9
1536 ,75 1387350 1,9
1728 ,8438 1379717 1,9
1920 ,9375 1378053 1,9
2048 1 1376933 1,9
2112 1,0313 1376085 1,9
2304 1,125 1373653 1,9
2496 1,2188 1372292 1,9
2688 1,3125 1369476 1,9
2880 1,4063 1367284 1,9
3072 1,5 1365476 1,9
3264 1,5938 1363380 1,9
3456 1,6875 1361731 1,9
3648 1,7813 1360403 1,9
3840 1,875 1359411 1,9

DEFAULT 3872 ,0997 189349654 790,8
7744 ,1993 83663642 108,9
11616 ,299 81728075 96,4
15488 ,3987 80641806 89,4
19360 ,4984 80364716 87,6
23232 ,598 80329910 87,4
27104 ,6977 80237998 86,8
30976 ,7974 80227056 86,7
34848 ,897 80216114 86,6
38720 ,9967 80112635 86
38848 1 80101902 85,9
42592 1,0964 77752731 70,7
46464 1,196 76069661 59,9
50336 1,2957 74947231 52,6
54208 1,3954 74113772 47,2
58080 1,4951 73707464 44,6
61952 1,5947 73638165 44,2
65824 1,6944 73637748 44,2
69696 1,7941 73637748 44,2
73568 1,8937 73637748 44,2
77440 1,9934 72858373 39,1
912 ,099 47243757 146,1
1824 ,1979 34839783 101,6
2736 ,2969 27376986 74,9
3648 ,3958 22708043 58,1
4560 ,4948 20285785 49,5
5472 ,5938 19087348 45,2
6384 ,6927 18461519 42,9
7296 ,7917 18057366 41,5
8208 ,8906 17689480 40,1
9120 ,9896 17284356 38,7
9216 1 17246452 38,6
10032 1,0885 16956866 37,5
10944 1,1875 16323977 35,3
11856 1,2865 16045408 34,3
12768 1,3854 15808225 33,4
13680 1,4844 15594294 32,6
14592 1,5833 15384834 31,9
15504 1,6823 15212536 31,3
16416 1,7813 15059228 30,7
17328 1,8802 14925157 30,2
18240 1,9792 14809486 29,8

Dans cet exemple, pour le CACHE de type DEFAULT, la valeur la plus efficace serait aux alentours de 9G / 10G , car le % de DB READ TIME change peu et reste autour de 40%.

Dans cet exemple, mon DB_CACHE_SIZE est positionné à 11G. Je suis donc sur un réglage correct.

Requètes utiles pour analyses de requètes / plan d’éxécution

Sans passer par le package DBMS, lecture de la plan_table :

SELECT * FROM table(dbms_xplan.display_cursor(NULL, NULL, 'OUTLINE'));

Verifier si un plan d’execuction existe dans l’historique (shared pool) a partir d’un sqlid (remplacer le sqlid ci dessous par celui que vous recherhez)

select sql_id, child_number, executions, PARSING_SCHEMA_NAME, round(elapsed_time/1000000,2) "elapsed_sec",round((elapsed_time/1000000)/executions,2) "elapsed_per_exec",plan_hash_value, buffer_gets
from v$sql where sql_id='3sgs77a28skwq';

Pour forcer un plan d’éxécution, utilisez DBMS_SQLTUNE via un profile sql :

Pensez à adapter le SQLID et la Hash du plan d’éxécution .

Source du code ci dessous : Blog d’Ahmed AANGOUR , http://ahmedaangour.blogspot.com/2011/01/forcer-un-plan-dexecution-via-un-sql.html

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
v$sql_plan
WHERE sql_id = 'asth1mx10aygn'
AND plan_hash_value = 4270555908
AND other_xml IS NOT NULL)) d;

SELECT sql_fulltext
INTO cl_sql_text
FROM v$sql
WHERE sql_id = 'asth1mx10aygn'
and rownum=1;

DBMS_SQLTUNE.import_sql_profile (sql_text => cl_sql_text,
profile => ar_profile_hints,
category => 'DEFAULT',
name => 'PROFILE_asth1mx10aygn',
force_match => TRUE);
END;
/
;