tag:blogger.com,1999:blog-21415799134682367182024-02-08T13:40:57.141+01:00Base de données Oracle : partage d'expérience...loranhttp://www.blogger.com/profile/16224793437590995506noreply@blogger.comBlogger33125tag:blogger.com,1999:blog-2141579913468236718.post-36388359048382895392012-02-13T17:22:00.001+01:002012-02-13T17:22:54.372+01:00AUDIT : principes<br />
<u>Paramètres d'initialisation concernant l'audit :</u><br />
<br />
<div style="background-color: #cccccc; font-family: "Courier New",Courier,monospace;">
<span style="font-size: small;">SQL> col name for a25<br />SQL> col value for a45<br />SQL> select name, value from v$parameter where name like '%audit%';<br /><br />NAME VALUE<br />------------------------- -------------------------------------</span></div>
<div style="background-color: #cccccc; font-family: "Courier New",Courier,monospace;">
<span style="font-size: small;">audit_sys_operations FALSE<br />audit_file_dest /opt/oracle/product/10gr2/rdbms/audit<br />audit_syslog_level<br />audit_trail DB</span></div>
<br />
<span style="font-size: small;"><span style="color: red;">audit_sys_operations</span> : permet l'audit de toutes les opérartions réalisées par SYS (y compris AS SYSDBA ou SYSOPER). Les traces ainsi générées sont générées sur l'OS (et non en base puisque la base peut être fermée lorsque SYS est connecté). Par conséquent on doit avoir obligatoirement <b>audit_trail </b>OS.</span><br />
<span style="font-size: small;"><br /></span><br />
<span style="font-size: small;"><span style="color: red;">audit_file_dest</span> : chemin de destination des traces d'audit lorsque celles-ci sont de type "OS"</span><br />
<span style="font-size: small;"><br /></span><br />
<span style="font-size: small;"><span style="color: red;">audit_syslog_level</span> : paramètre indiquant le niveau de trace que l'on souhaite pour l'audit des actions SYS</span><br />
<br />
<pre class="oac_no_warn"> ex : AUDIT_SYSLOG_LEVEL=local1.warning</pre>
<br />
<span style="font-size: small;"><span style="color: red;">audit_trail</span> : décrit le type de trace d'audit : </span><br />
<br />
<span style="font-size: small;"> <code>DB : dans la table sys.aud$</code></span><br />
<span style="font-size: small;"><code> OS : dans des fichiers de trace sur le serveur</code></span><br />
<span style="font-size: small;"><code> </code></span><code>DB + EXTENDED : en base avec possibilité de tracer les queries associées</code><br />
<code> </code><span style="font-size: small;"><code>XML : dans des fichiers de trace XML sur le serveur</code></span><br />
<span style="font-size: small;"><code> </code></span><code><span style="font-size: small;">D</span>BMS_FGA.DB + DBMS_FGA.EXTENDED : idem DB + EXTENDED mais pour le <span style="color: red;">FGA</span>.</code><br />
<span style="font-size: small;"><br /></span><br />
<u><span style="font-size: small;">Par défaut, l'audit du privilège CREATE SESSION est activé :</span></u><br />
<br />
<div style="background-color: #cccccc; font-family: "Courier New",Courier,monospace;">
<span style="font-size: small;">SQL> select user_name, privilege from dba_priv_audit_opts;<br /><br />USER_NAME PRIVILEGE<br />------------------------------ ---------------------------<br /> CREATE SESSION</span></div>
<br />
<u><span style="font-size: small;">Déclanchement de l'audit d'un objet :</span></u><br />
<span style="font-size: small;"><br /></span><br />
<div style="background-color: #cccccc; font-family: "Courier New",Courier,monospace;">
<span style="font-size: small;">SQL> <span style="color: red;">audit</span> select, insert, delete on lc.titi by access;<br /><br />Audit succeeded.</span></div>
<div style="background-color: #cccccc; font-family: "Courier New",Courier,monospace;">
<span style="font-size: small;"><br /></span></div>
<div style="background-color: #cccccc; font-family: "Courier New",Courier,monospace;">
<span style="font-size: small;">SQL> col owner for a5<br />SQL> col object_name for a11<br />SQL> col object_type for a11<br />SQL> select * from <span style="color: red;">dba_obj_audit_opts</span> where object_name='TITI';<br /><br />OWNER OBJECT_NAME OBJECT_TYPE ALT AUD COM DEL GRA IND INS LOC REN SEL UPD REF EXE CRE REA WRI FBK<br />----- ----------- ----------- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- ---<br />LC TITI TABLE -/- -/- -/- A/A -/- -/- A/A -/- -/- A/A -/- -/- -/- -/- -/- -/- -/-</span></div>
<span style="font-size: small;"><br /></span><br />
<u><span style="font-size: small;">Trace d'audit :</span></u><span style="font-size: small;"> </span><br />
<br />
<div style="background-color: #cccccc; font-family: "Courier New",Courier,monospace;">
<span style="font-size: small;">SQL> delete from titi;<br /><br />0 rows deleted.<br /><br />SQL> select count(*) from titi;<br /><br /> COUNT(*)<br />----------<br /> 0</span></div>
<div style="background-color: #cccccc; font-family: "Courier New",Courier,monospace;">
<br /></div>
<div style="background-color: #cccccc;">
<span style="font-size: small;"><span style="font-family: "Courier New",Courier,monospace;">SQL> col username for a11</span><br style="font-family: "Courier New",Courier,monospace;" /><span style="font-family: "Courier New",Courier,monospace;">SQL> col os_username for a11</span><br style="font-family: "Courier New",Courier,monospace;" /><span style="font-family: "Courier New",Courier,monospace;">SQL> col terminal for a15</span><br style="font-family: "Courier New",Courier,monospace;" /><span style="font-family: "Courier New",Courier,monospace;">SQL> col action_name for a11</span><br style="font-family: "Courier New",Courier,monospace;" /><span style="font-family: "Courier New",Courier,monospace;">SQL> alter session set NLS_DATE_FORMAT='DD/MM/YYYY HH24:MI:SS';</span><br style="font-family: "Courier New",Courier,monospace;" /><br style="font-family: "Courier New",Courier,monospace;" /><span style="font-family: "Courier New",Courier,monospace;">Session altered.</span><br style="font-family: "Courier New",Courier,monospace;" /><br style="font-family: "Courier New",Courier,monospace;" /><span style="font-family: "Courier New",Courier,monospace;">SQL> select username, os_username, terminal, action_name, timestamp from (select * from <span style="color: red;">dba_audit_session</span> where username='LC' order by timestamp desc nulls last) where rownum < 2;</span><br style="font-family: "Courier New",Courier,monospace;" /><br style="font-family: "Courier New",Courier,monospace;" /><span style="font-family: "Courier New",Courier,monospace;">USERNAME OS_USERNAME TERMINAL ACTION_NAME TIMESTAMP</span><br style="font-family: "Courier New",Courier,monospace;" /><span style="font-family: "Courier New",Courier,monospace;">----------- ----------- --------------- ----------- -------------------</span><br style="font-family: "Courier New",Courier,monospace;" /><span style="font-family: "Courier New",Courier,monospace;">LC Clala PFIXE080686 LOGON 13/02/2012 16:58:18</span></span></div>
<div style="background-color: #cccccc;">
<br /></div>
<div style="background-color: #cccccc;">
<span style="font-size: small;"><span style="font-family: "Courier New",Courier,monospace;">SQL> select os_username, username, terminal, timestamp, action_name from <span style="color: red;">dba_audit_object</span> where owner='LC' and obj_name='TITI';<br /><br />OS_USERNAM USERNAME TERMINAL TIMESTAMP ACTION_NAM<br />---------- ---------- --------------- ------------------- ----------<br />Clala LC PFIXE080686 13/02/2012 16:58:23 DELETE<br />Clala LC PFIXE080686 13/02/2012 16:58:47 SELECT </span></span></div>
<span style="font-size: small;"></span><br />
<span style="font-size: small;"></span><br />
<span style="font-size: small;"></span><br />
<span style="font-size: small;"></span><br />
<span style="font-size: small;"></span><br />
<br />
<u><span style="font-size: small;">Suppression de l'action d'audit :</span></u><br />
<span style="font-size: small;"></span><br />
<br />
<div style="background-color: #cccccc; font-family: "Courier New",Courier,monospace;">
<span style="font-size: small;">SQL> <span style="color: red;">noaudit</span> all on lc.titi;<br /><br />Noaudit succeeded.<br /><br />SQL> select * from <span style="color: red;">dba_obj_audit_opts</span> where object_name='TITI';<br /><br />no rows selected</span></div>
<span style="font-size: small;"></span><br />
<span style="font-size: small;"><span style="background-color: #cccccc;"></span><br /></span><span style="font-size: small;"><br /></span>loranhttp://www.blogger.com/profile/16224793437590995506noreply@blogger.com0Paris, France48.856614 2.352221948.773036 2.1942934 48.940192 2.5101504tag:blogger.com,1999:blog-2141579913468236718.post-11411368976763681152011-12-05T10:59:00.001+01:002012-02-13T17:23:31.314+01:00COMPUTE STATISTICS : obsolèteINDEX CREATION : COMPUTE STATISTICS<br />
<br />
Dans les versions antérieurs à Oracle 10g, on pouvait utiliser la clause COMPUTE STATISTICS dans une commande de CREATE INDEX.<br />
<br />
<br />
Cette clause a été dépréciée. Elle est toujours supportée pour la compatibilité ascendante, et ne causera pas d'erreur.<br />
<br />
<br />
Oracle collecte désormais les statistiques automatiquement durant la création ou le rebuild de l'index.loranhttp://www.blogger.com/profile/16224793437590995506noreply@blogger.com0tag:blogger.com,1999:blog-2141579913468236718.post-46429557862588588962011-12-05T10:39:00.002+01:002011-12-05T10:41:49.388+01:00STATISTICS : NO_INVALIDATELe calcul de statistiques via <span style="color: red;">DBMS_STATS</span> n'a pas, par défaut, d'impact immédiat sur les plans d'exécution. Ceci est un choix délibéré d'Oracle pour éviter de démultiplier les parsing nécessaires à l'exécution des requêtes. Sauf qu'en contre-partie, on risque d'avoir des surprises.<br />
<br />
Par exemple : si j'ai une table vide qui possède des statistiques à jour, et que je lance une requête qui utilise cette table, puis que je charge cette table, que je recalcule les statistiques, et que je relance la requête dans laquelle cette table est impliquée (jointure par ex.). Surprise : le temps d'exécution est extrêmement long (bien trop long), le plan d'exécution n'a pas changé !!<br />
<br />
Oracle utilise par défaut la fonction <span style="color: red;">DBMS_STATS.AUTO_INVALIDATE</span><code>,</code> <code></code>fonction qui indique à Oracle d'invalider les plans d'exécution liés à la table en question, lorsque cela lui convient (dans un maximum de 5H de temps), ce afin d'étaler la charge de recalcule des plans d'exécution.<br />
<br />
Aussi, pour forcer l'invalidation des plans d'exécution, Oracle nous laisse la possibilité d'indiquer <span style="color: red;">NO_INVALIDATE => FALSE</span>.<br />
<br />
En conclusion, voici ce que je retiens de ce problème :Lorsque l'on charge des tables en annule et remplace (surtout si le nombre de lignes chargé est très variable), il faut tout de suite après le chargement lancer un calcul de statistiques, et avec l'option <span style="color: red;">NO_INVALIDATE => FALSE</span>.loranhttp://www.blogger.com/profile/16224793437590995506noreply@blogger.com0tag:blogger.com,1999:blog-2141579913468236718.post-17622289216292033622011-08-03T14:15:00.002+02:002011-08-03T14:24:45.898+02:00STATISTIQUES : effet du truncate<div style="font-family: "Courier New",Courier,monospace;">Une question que je me pose souvent : le <span style="color: red;">TRUNCATE </span>invalide-t'il les statistiques d'une table ? Réponse : non.</div><div style="font-family: "Courier New",Courier,monospace;"><br />
</div><div style="font-family: "Courier New",Courier,monospace;"><u>Démonstration :</u><br />
<u> </u></div><div style="font-family: "Courier New",Courier,monospace;"></div><div style="background-color: #cccccc; font-family: "Courier New",Courier,monospace;"> </div><div style="background-color: #cccccc; font-family: "Courier New",Courier,monospace;">SQL> CREATE TABLE toto (a number);<br />
<br />
Table created.<br />
<br />
SQL> INSERT INTO toto VALUES (999);<br />
<br />
1 row created.<br />
<br />
SQL> COMMIT;<br />
<br />
Commit complete.<br />
<br />
SQL> SELECT blocks, avg_row_len FROM USER_TAB_STATISTICS WHERE TABLE_NAME='TOTO';<br />
<br />
BLOCKS AVG_ROW_LEN<br />
---------- -----------<br />
<br />
<br />
SQL> exec dbms_stats.gather_table_stats(USER,'TOTO');<br />
<br />
PL/SQL procedure successfully completed.<br />
<br />
SQL> SELECT blocks, avg_row_len FROM USER_TAB_STATISTICS WHERE TABLE_NAME='TOTO';<br />
<br />
BLOCKS AVG_ROW_LEN<br />
---------- -----------<br />
5 4<br />
<br />
SQL> <span style="color: red;">TRUNCATE </span>TABLE toto;<br />
<br />
Table truncated.<br />
<br />
SQL> SELECT blocks, avg_row_len FROM USER_TAB_STATISTICS WHERE TABLE_NAME='TOTO';<br />
<br />
<div style="color: red;"> BLOCKS AVG_ROW_LEN</div><div style="color: red;">---------- -----------</div><div style="color: red;"> 5 4</div><br />
SQL> exec dbms_stats.gather_table_stats(USER,'TOTO');<br />
<br />
PL/SQL procedure successfully completed.<br />
<br />
SQL> SELECT blocks, avg_row_len FROM USER_TAB_STATISTICS WHERE TABLE_NAME='TOTO';<br />
<br />
BLOCKS AVG_ROW_LEN<br />
---------- -----------<br />
0 0<br />
</div><div style="font-family: "Courier New",Courier,monospace;"><br />
</div>loranhttp://www.blogger.com/profile/16224793437590995506noreply@blogger.com0tag:blogger.com,1999:blog-2141579913468236718.post-34245073187774210412011-08-03T11:04:00.003+02:002011-08-03T11:15:06.426+02:00UPDATE : critère supplémentaire dans la table source<div style="font-family: "Courier New",Courier,monospace;">On souhaite mettre à jour une table CIB à partir d'une table SRC sur un critère de jointure conditionné par un prédicat supplémentaire.</div><div style="font-family: "Courier New",Courier,monospace;"><br />
</div><div style="font-family: "Courier New",Courier,monospace;">La difficulté est due au fait que la table à mettre à jour contient des lignes qu'il ne faudra pas mettre à jour et que ce critère est indiqué dans la table source (src.c='NON')</div><div style="font-family: "Courier New",Courier,monospace;"><br />
</div><div style="font-family: "Courier New",Courier,monospace;"><u>Préparation :</u></div><div style="font-family: "Courier New",Courier,monospace;"><br />
</div><div style="background-color: #cccccc;"><span style="font-family: "Courier New",Courier,monospace;">SQL> CREATE TABLE src (a NUMBER, b NUMBER, c CHAR(3));<br />
<br />
Table created.<br />
<br />
SQL> CREATE TABLE cib (a NUMBER, b NUMBER);<br />
<br />
Table created.<br />
<br />
SQL> INSERT INTO src VALUES (1,11,'OUI');<br />
<br />
1 row created.<br />
<br />
SQL> INSERT INTO src VALUES (1,12,'NON');<br />
<br />
1 row created.<br />
<br />
SQL> INSERT INTO src VALUES (2,22,'NON');<br />
<br />
1 row created.<br />
<br />
SQL> INSERT INTO cib VALUES (1,0);<br />
<br />
1 row created.<br />
<br />
SQL> INSERT INTO cib VALUES (2,0);<br />
<br />
1 row created.<br />
<br />
SQL> COMMIT;<br />
<br />
Commit complete.<br />
<br />
SQL> SELECT * FROM src;<br />
<br />
A B C<br />
---------- ---------- ---<br />
1 11 OUI<br />
1 12 NON<br />
2 22 NON<br />
<br />
SQL> SELECT * FROM cib;<br />
<br />
A B<br />
---------- ----------<br />
1 0<br />
2 0<span style="background-color: white;"></span></span></div><div style="background-color: white;"><br />
</div><div style="background-color: white;"><br />
</div><div style="background-color: white;"><u><span style="font-family: "Courier New",Courier,monospace;">Exécution :</span></u></div><div style="background-color: white;"><span style="font-family: "Courier New",Courier,monospace;"> </span></div><div style="background-color: white;"><span style="font-family: "Courier New",Courier,monospace;">Rappel : </span><span style="font-family: "Courier New",Courier,monospace;">on souhaite mettre à jour la table cib à partir des valeurs de la table src uniquement lorsque la cle de cib est présente dans src et que le champ c de src vaut 'OUI'</span></div><div style="background-color: white;"><br />
</div><div style="background-color: #cccccc;"><span style="font-family: "Courier New",Courier,monospace;">SQL> UPDATE cib<br />
2 SET cib.b = (<br />
3 SELECT src.b<br />
4 FROM src<br />
5 WHERE src.a=cib.a<br />
6 )<br />
7 ;<br />
SELECT src.b<br />
*<br />
ERROR at line 3:</span><br />
<span style="font-family: "Courier New",Courier,monospace;"> <br />
<span style="color: red;">ORA-01427: single-row subquery returns more than one row</span><br />
</span><span style="font-family: "Courier New",Courier,monospace;"><span style="color: red;">La table source renvoie plus d'une valeur pour une valeur de clé donnée</span><br />
</span></div><div style="background-color: #cccccc;"><span style="font-family: "Courier New",Courier,monospace;"> </span><span style="font-family: "Courier New",Courier,monospace;">SQL> UPDATE cib<br />
2 SET cib.b = (<br />
3 SELECT src.b<br />
4 FROM src<br />
5 WHERE src.a=cib.a<br />
6 )<br />
7 WHERE src.c='OUI'<br />
8 ;<br />
WHERE src.c='OUI'<br />
*<br />
ERROR at line 7:<br />
ORA-00904: "SRC"."C": invalid identifier</span></div><div style="background-color: #cccccc;"><span style="font-family: "Courier New",Courier,monospace;"><br />
</span></div><div style="background-color: #cccccc;"><span style="color: red; font-family: "Courier New",Courier,monospace;">Le prédicat est mal placé</span><span style="font-family: "Courier New",Courier,monospace;"></span></div><div style="background-color: #cccccc;"><span style="font-family: "Courier New",Courier,monospace;"><br />
SQL> UPDATE cib<br />
2 SET cib.b = (<br />
3 SELECT src.b<br />
4 FROM src<br />
5 WHERE src.a=cib.a<br />
6 AND src.c='OUI')<br />
7 ;<br />
<br />
2 rows updated.<br />
<br />
SQL> SELECT * FROM cib;<br />
<br />
A B<br />
---------- ----------<br />
1 11<br />
2</span></div><div style="background-color: #cccccc;"><span style="font-family: "Courier New",Courier,monospace;"><br />
</span></div><div style="background-color: #cccccc; color: red;"><span style="font-family: "Courier New",Courier,monospace;">Les valeurs des clés sans correspondance sont mises à jour par null</span></div><div style="background-color: #cccccc;"><br />
<span style="font-family: "Courier New",Courier,monospace;">SQL> ROLLBACK;<br />
<br />
Rollback complete.<br />
<br />
<span style="color: red;"></span>SQL> UPDATE cib<br />
2 SET cib.b = (<br />
3 SELECT src.b<br />
4 FROM src<br />
5 WHERE src.a=cib.a<br />
6 AND src.c='OUI')<br />
7 WHERE EXISTS (<br />
8 SELECT 1<br />
9 FROM src<br />
10 WHERE src.a=cib.a)<br />
11 )<br />
12 ;<br />
<br />
2 rows updated.<br />
<br />
SQL> SELECT * FROM cib;<br />
<br />
A B<br />
---------- ----------<br />
1 11<br />
2</span></div><div style="background-color: #cccccc;"><br />
</div><div style="background-color: #cccccc;"><span style="font-family: "Courier New",Courier,monospace;"><span style="color: red;">La condition where exists est inefficace, des valeurs des clés sans correspondance sont mises à jour par null</span></span></div><div style="background-color: #cccccc;"><span style="font-family: "Courier New",Courier,monospace;"> </span></div><div style="background-color: #cccccc;"><br />
<span style="font-family: "Courier New",Courier,monospace;">SQL> ROLLBACK;<br />
<br />
Rollback complete.<br />
<br />
SQL> UPDATE cib<br />
2 SET cib.b = (<br />
3 SELECT src.b<br />
4 FROM src<br />
5 WHERE src.a=cib.a<br />
6 AND src.c='OUI')<br />
7 WHERE EXISTS (<br />
8 SELECT 1<br />
9 FROM src<br />
10 WHERE src.a=cib.a<br />
11 AND src.c='OUI'<br />
12 )<br />
13 ;<br />
<br />
1 row updated.<br />
<br />
SQL> SELECT * FROM cib;<br />
<br />
A B<br />
---------- ----------<br />
1 11<br />
2 0</span><span style="font-family: "Courier New",Courier,monospace;"> </span></div><div style="background-color: #cccccc; color: red;"><span style="font-family: "Courier New",Courier,monospace;"> </span><br />
<span style="font-family: "Courier New",Courier,monospace;">Écriture correcte, on a le résultat souhaité</span></div><div style="background-color: #cccccc;"><br />
<span style="font-family: "Courier New",Courier,monospace;">SQL> ROLLBACK;<br />
<br />
Rollback complete.<br />
<br />
Écriture équivalente sous forme de <span style="color: red;">MERGE</span></span></div><div style="background-color: #cccccc;"><span style="font-family: "Courier New",Courier,monospace;"><br />
SQL> MERGE INTO cib t_c<br />
2 USING (<br />
3 SELECT *<br />
4 FROM src<br />
5 WHERE c='OUI'<br />
6 ) t_s<br />
7 ON (t_c.a=t_s.a)<br />
8 WHEN MATCHED THEN<br />
9 UPDATE SET t_c.b=t_s.b<br />
10 ;<br />
<br />
1 row merged.<br />
<br />
SQL> SELECT * FROM cib;<br />
<br />
A B<br />
---------- ----------<br />
1 11<br />
2 0<br />
<br />
SQL> ROLLBACK;<br />
<br />
Rollback complete.</span></div>loranhttp://www.blogger.com/profile/16224793437590995506noreply@blogger.com0tag:blogger.com,1999:blog-2141579913468236718.post-69620526656654047522011-08-03T10:30:00.001+02:002011-08-03T10:37:47.304+02:00UPDATE : vue de jointure in-line<div style="font-family: "Courier New",Courier,monospace;">Update de vue de jointure "in-line" et index unique </div><div style="font-family: "Courier New",Courier,monospace;"> </div><div style="background-color: #cccccc; font-family: "Courier New",Courier,monospace;">SQL> create table SRC (a number, b number);<br />
<br />
Table created.<br />
<br />
SQL> create table CIB (a number, b number);<br />
<br />
Table created.<br />
<br />
SQL> insert into SRC values (1,11);<br />
<br />
1 row created.<br />
<br />
SQL> insert into SRC values (2,12);<br />
<br />
1 row created.<br />
<br />
SQL> insert into CIB values (1,0);<br />
<br />
1 row created.<br />
<br />
SQL> insert into CIB values (2,0);<br />
<br />
1 row created.<br />
<br />
SQL> select * from SRC;<br />
<br />
A B<br />
---------- ----------<br />
1 11<br />
2 12<br />
<br />
SQL> select * from CIB;<br />
<br />
A B<br />
---------- ----------<br />
1 0<br />
2 0<br />
<br />
SQL> update (<br />
2 select SRC.b srcb, CIB.b cibb<br />
3 from SRC, CIB<br />
4 where SRC.a=CIB.a<br />
5 )<br />
6 set cibb = srcb<br />
7 ;<br />
set cibb = srcb<br />
*<br />
ERROR at line 6:<br />
<div style="color: red;">ORA-01779: cannot modify a column which maps to a non key-preserved table</div></div><div style="font-family: "Courier New",Courier,monospace;"><br />
<br />
<span style="background-color: white;">Un index unique doit être créé sur la clé de la table source</span></div><div style="font-family: "Courier New",Courier,monospace;"><br />
</div><div style="background-color: #cccccc; font-family: "Courier New",Courier,monospace;"><span style="background-color: white;"></span>SQL> create unique index IU_SRC on SRC (a);<br />
<br />
Index created.<br />
<br />
SQL> update (<br />
2 select SRC.b srcb, CIB.b cibb<br />
3 from SRC, CIB<br />
4 where SRC.a=CIB.a<br />
5 )<br />
6 set cibb = srcb<br />
7 ;<br />
<br />
2 rows updated.<br />
<br />
SQL> select * from SRC;<br />
<br />
A B<br />
---------- ----------<br />
1 11<br />
2 12<br />
<br />
SQL> select * from CIB;<br />
<br />
A B<br />
---------- ----------<br />
1 11<br />
2 12<br />
</div><div style="font-family: "Courier New",Courier,monospace;"></div>loranhttp://www.blogger.com/profile/16224793437590995506noreply@blogger.com0tag:blogger.com,1999:blog-2141579913468236718.post-86558739101136085562011-08-02T17:10:00.000+02:002011-08-02T17:10:07.267+02:00MERGE : unicité des clés de la table source<div style="font-family: "Courier New",Courier,monospace;">Dans un MERGE, la table appelée en source du MERGE (using) ne doit pas avoir de doublons sur sa clé de jointure.</div><div style="font-family: "Courier New",Courier,monospace;"> <span style="background-color: #cccccc;"> </span></div><div style="background-color: #cccccc; font-family: "Courier New",Courier,monospace;">SQL> CREATE TABLE nwprices (<br />
2 article_name VARCHAR2(20),<br />
3 price NUMBER,<br />
4 last_price CHAR(1)<br />
5 );<br />
<br />
Table created.<br />
<br />
SQL> INSERT INTO articles<br />
2 VALUES ('CABLE 13mm CUIVRE',15.50);<br />
<br />
1 row created.<br />
<br />
SQL> INSERT INTO articles<br />
2 VALUES ('CABLE 25mm CUIVRE',19.25);<br />
<br />
1 row created.<br />
<br />
SQL> INSERT INTO nwprices<br />
2 VALUES ('CABLE 13mm CUIVRE',15.80,'O');<br />
<br />
1 row created.<br />
<br />
SQL> INSERT INTO nwprices<br />
2 VALUES ('CABLE 25mm CUIVRE',21.05,'N');<br />
<br />
1 row created.<br />
<br />
SQL> INSERT INTO nwprices<br />
2 VALUES ('CABLE 25mm CUIVRE',21.45,'O');<br />
<br />
1 row created.<br />
<br />
SQL> COL price FOR 99.00<br />
SQL> SELECT * FROM articles;<br />
<br />
ARTICLE_NAME PRICE<br />
-------------------- ------<br />
CABLE 13mm CUIVRE 15.50<br />
CABLE 25mm CUIVRE 19.25<br />
<br />
SQL> SELECT * FROM nwprices;<br />
<br />
ARTICLE_NAME PRICE L<br />
-------------------- ------ -<br />
CABLE 13mm CUIVRE 15.80 O<br />
CABLE 25mm CUIVRE 21.05 N<br />
CABLE 25mm CUIVRE 21.45 O<br />
<br />
SQL> MERGE INTO articles ar<br />
2 USING nwprices nw<br />
3 ON (ar.article_name=nw.article_name)<br />
4 WHEN MATCHED<br />
5 THEN UPDATE<br />
6 SET ar.price=nw.price<br />
7 ;<br />
USING nwprices nw<br />
*<br />
<span style="color: red;">ERROR at line 2:</span><br />
<span style="color: red;">ORA-30926: unable to get a stable set of rows in the source tables</span><br />
<br />
<br />
SQL> MERGE INTO articles ar<br />
2 USING (<br />
3 SELECT * FROM nwprices<br />
4 WHERE last_price='O') nw<br />
5 ON (ar.article_name=nw.article_name)<br />
6 WHEN MATCHED<br />
7 THEN UPDATE<br />
8 SET ar.price=nw.price<br />
9 ;<br />
<br />
2 rows merged.<br />
<br />
SQL> SELECT * FROM articles;<br />
<br />
ARTICLE_NAME PRICE<br />
-------------------- ------<br />
CABLE 13mm CUIVRE 15.80<br />
CABLE 25mm CUIVRE 21.45<br />
<br />
SQL></div>loranhttp://www.blogger.com/profile/16224793437590995506noreply@blogger.com0tag:blogger.com,1999:blog-2141579913468236718.post-67732118036046177762011-08-02T16:26:00.001+02:002011-08-02T16:28:57.450+02:00OUTER JOIN : syntaxe ANSI et syntaxe Oracle<div style="font-family: "Courier New",Courier,monospace;">Ci-dessous un aide-mémoire pour se souvenir de l'écriture des jointures externes avec les deux syntaxes autorisées dans Oracle.</div><div style="font-family: "Courier New",Courier,monospace;"><br />
</div><div style="background-color: #cccccc; font-family: "Courier New",Courier,monospace;">SQL> CREATE TABLE pays (id_pays NUMBER, pays VARCHAR2(10));<br />
<br />
Table created.<br />
<br />
SQL> CREATE TABLE villes (<br />
2 id_ville NUMBER,<br />
3 ville VARCHAR2(10),<br />
4 id_pays NUMBER);<br />
<br />
Table created.<br />
<br />
SQL> INSERT INTO pays VALUES (1, 'USA');<br />
<br />
1 row created.<br />
<br />
SQL> INSERT INTO pays VALUES (2, 'RUSSIE');<br />
<br />
1 row created.<br />
<br />
SQL> INSERT INTO pays VALUES (3, 'FRANCE');<br />
<br />
1 row created.<br />
<br />
SQL> INSERT INTO villes VALUES (1, 'DENVER', 1);<br />
<br />
1 row created.<br />
<br />
SQL> INSERT INTO villes VALUES (2, 'CHICAGO', 1);<br />
<br />
1 row created.<br />
<br />
SQL> INSERT INTO villes VALUES (3, 'MOSCOU', 2);<br />
<br />
1 row created.<br />
<br />
SQL> INSERT INTO villes VALUES (4, 'DJAKARTA', 5);<br />
<br />
1 row created.<br />
<br />
SQL> COMMIT;<br />
<br />
Commit complete.<br />
<br />
SQL> -- LEFT OUTER JOIN<br />
SQL> SELECT pays, ville<br />
2 FROM pays p <span style="color: red;">LEFT OUTER JOIN</span> villes v<br />
3 <span style="color: red;">ON</span> p.id_pays=v.id_pays<br />
4 ORDER BY 1,2<br />
5 ;<br />
<br />
PAYS VILLE<br />
---------- ----------<br />
FRANCE<br />
RUSSIE MOSCOU<br />
USA CHICAGO<br />
USA DENVER<br />
<br />
SQL> SELECT pays, ville<br />
2 FROM pays p, villes v<br />
3 WHERE p.id_pays=v.id_pays<span style="color: red;">(+)</span><br />
4 ORDER BY 1,2<br />
5 ;<br />
<br />
PAYS VILLE<br />
---------- ----------<br />
FRANCE<br />
RUSSIE MOSCOU<br />
USA CHICAGO<br />
USA DENVER<br />
<br />
SQL> -- RIGHT OUTER JOIN<br />
SQL> SELECT pays, ville<br />
2 FROM pays p <span style="color: red;">RIGHT OUTER JOIN</span> villes v<br />
3 <span style="color: red;">ON </span>p.id_pays=v.id_pays<br />
4 ORDER BY 1,2<br />
5 ;<br />
<br />
PAYS VILLE<br />
---------- ----------<br />
RUSSIE MOSCOU<br />
USA CHICAGO<br />
USA DENVER<br />
DJAKARTA<br />
<br />
SQL> SELECT pays, ville<br />
2 FROM pays p, villes v<br />
3 WHERE p.id_pays<span style="color: red;">(+)</span>=v.id_pays<br />
4 ORDER BY 1,2<br />
5 ;<br />
<br />
PAYS VILLE<br />
---------- ----------<br />
RUSSIE MOSCOU<br />
USA CHICAGO<br />
USA DENVER<br />
DJAKARTA<br />
<br />
SQL> -- FULL OUTER JOIN<br />
SQL> SELECT pays, ville<br />
2 FROM pays p <span style="color: red;">FULL OUTER JOIN</span> villes v<br />
3 <span style="color: red;">ON</span> p.id_pays=v.id_pays<br />
4 ORDER BY 1,2<br />
5 ;<br />
<br />
PAYS VILLE<br />
---------- ----------<br />
FRANCE<br />
RUSSIE MOSCOU<br />
USA CHICAGO<br />
USA DENVER<br />
DJAKARTA<br />
<br />
SQL> SELECT pays, ville<br />
2 FROM pays p, villes v<br />
3 WHERE p.id_pays=v.id_pays<span style="color: red;">(+)</span><br />
4 <span style="color: red;">UNION</span><br />
5 SELECT pays, ville<br />
6 FROM pays p, villes v<br />
7 WHERE p.id_pays<span style="color: red;">(+)</span>=v.id_pays<br />
8 ORDER BY 1,2<br />
9 ;<br />
<br />
PAYS VILLE<br />
---------- ----------<br />
FRANCE<br />
RUSSIE MOSCOU<br />
USA CHICAGO<br />
USA DENVER<br />
DJAKARTA</div><div style="background-color: #cccccc; font-family: "Courier New",Courier,monospace;"><br />
</div><div style="font-family: "Courier New",Courier,monospace;"></div>loranhttp://www.blogger.com/profile/16224793437590995506noreply@blogger.com0tag:blogger.com,1999:blog-2141579913468236718.post-12385277076229707172011-08-02T13:51:00.006+02:002011-08-04T11:45:17.473+02:00BIND VARIABLE PEEKING : fonctionnement<div style="font-family: "Courier New",Courier,monospace;">Très bon article sur le peeking :<br />
<br />
<a href="http://hungrydba.com/bindpeeking.aspx">To peek or not to peek</a><br />
<br />
<br />
Un autre excellent lien sur le bind variable peeking :<br />
<br />
<a href="http://prutser.wordpress.com/2009/04/19/flushing-a-cursor-out-of-the-library-cache/">Flushing a cursor out of the Library Cache</a><br />
<br />
<br />
<u>Extrait de la doc Oracle 10gR2 :</u><br />
<br />
From Chapter 13 of the Performance tuning Guide:<br />
<br />
“13.4.1.2 <span style="color: red;">Peeking </span>of User-Defined <span style="color: red;">Bind Variables</span><br />
<br />
The query optimizer peeks at the values of user-defined bind variables on the first invocation of a cursor. This feature lets the optimizer determine the selectivity of any WHERE clause condition, as well as if literals have been used instead of bind variables. On subsequent invocations of the cursor, no peeking takes place, and the cursor is shared, based on the standard cursor-sharing criteria, even if subsequent invocations use different bind values.<br />
<br />
When bind variables are used in a statement, it is assumed that cursor sharing is intended and that different invocations are supposed to use the same execution plan. If different invocations of the cursor would significantly benefit from different execution plans, then bind variables may have been used inappropriately in the SQL statement. Bind peeking works for a specific set of clients, not all clients.”<br />
<br />
<u>Oracle 11g :</u><br />
<u> </u></div><div style="font-family: "Courier New",Courier,monospace;"><span style="font-size: small;">Solution définitive aux problèmes de bind varable peeking en 11g avec "<span style="color: red;">Adaptive Cursor Sharing</span>"</span><br />
<br />
<span style="font-size: small;"><a href="http://www.oracle-base.com/articles/11g/AdaptiveCursorSharing_11gR1.php">Adaptive Cursor Sharing : explication</a> </span></div>loranhttp://www.blogger.com/profile/16224793437590995506noreply@blogger.com0tag:blogger.com,1999:blog-2141579913468236718.post-42814628783765343422011-08-02T10:33:00.002+02:002011-08-02T10:37:00.466+02:00STATISTIQUES : pending statistics 11g<div style="background-color: white; font-family: "Courier New",Courier,monospace;">Pending Statistics<br />
<br />
Dans les versions d'Oracle antérieures à la 11g, les nouvelles statistiques de l'optimiseur étaient mises à disposition tout de suite. En 11g, c'est toujours le comportement par défaut, mais on a maintentant la possibilité de conserver les statistiques nouvellement générées dans un état d'attente jusqu'à ce que l'on choisisse de les publier.<br />
<br />
Par défaut Les statistiques sont automatiquement publiées :</div><div style="background-color: #cccccc; font-family: "Courier New",Courier,monospace;">SQL> <span style="color: red;">SELECT DBMS_STATS.get_prefs</span>('PUBLISH') FROM dual;</div><div style="background-color: #cccccc; font-family: "Courier New",Courier,monospace;"><br />
DBMS_STATS.GET_PREFS('PUBLISH')<br />
-------------------------------------------<br />
TRUE</div><div style="background-color: #cccccc; font-family: "Courier New",Courier,monospace;">1 row selected.</div><div style="font-family: "Courier New",Courier,monospace;"><br />
<span style="background-color: white;"></span><span style="background-color: white;">Pour modifier le compo</span>rtement par défaut, et ne pas publier automatiquement les futures mises à jour </div><div style="font-family: "Courier New",Courier,monospace;">des statistiques de la table SCOTT.EMP :</div><div style="font-family: "Courier New",Courier,monospace;"></div><div style="font-family: "Courier New",Courier,monospace;"><span style="background-color: #cccccc;">EXEC <span style="color: red;">DBMS_STATS.set_table_prefs</span>('SCOTT','EMP','PUBLISH','false');</span><br />
<br />
Pour publier automatiquement les futures mises à jour des statistiques de la table SCOTT.EMP :</div><div style="font-family: "Courier New",Courier,monospace;"><span style="background-color: #cccccc;">EXEC <span style="color: red;">DBMS_STATS.set_table_prefs</span>('SCOTT','EMP','PUBLISH','true');</span><br />
<br />
Vues listant les statistiques à publier :</div><div style="font-family: "Courier New",Courier,monospace;"><span style="background-color: #cccccc;">SELECT * FROM <span style="color: red;">[DBA|ALL|USER]_TAB_PENDING_STATS</span>;</span><br />
<span style="background-color: #cccccc;">SELECT * FROM <span style="color: red;">[DBA|ALL|USER]_IND_PENDING_STATS</span>;</span><br />
<br />
Pour publier toutes les statistiques en attente :</div><div style="font-family: "Courier New",Courier,monospace;"><span style="background-color: #cccccc;">EXEC <span style="color: red;">DBMS_STATS.publish_pending_stats</span>(NULL, NULL);</span><br />
<br />
Pour publier les statistiques en attente de l'objet SCOTT.EMP :</div><div style="font-family: "Courier New",Courier,monospace;"><span style="background-color: #cccccc;">EXEC <span style="color: red;">DBMS_STATS.publish_pending_stats</span>('SCOTT','EMP');</span><br />
<br />
Pour supprimer les statistiques en attente de l'objet SCOTT.EMP :</div><div style="font-family: "Courier New",Courier,monospace;"><span style="background-color: #cccccc;">EXEC <span style="color: red;">DBMS_STATS.delete_pending_stats</span>('SCOTT','EMP');</span><br />
<br />
L'optimiseur peut utiliser les statistiques en attente (pending) en positionnant le paramètre <span style="color: red;">OPTIMIZER_USE_PENDING_STATISTICS</span> à TRUE (FALSE par défaut), ce qui revient à supprimer la fonctionnalité de statistiques en atente.<br />
<br />
Il est possible également de modifier le paramètre au niveau session, ce qui permet de tester le bénéfice des nouvelles statistiques dans une session avant de les publier.</div><div style="font-family: "Courier New",Courier,monospace;"></div><div style="font-family: "Courier New",Courier,monospace;"><span style="background-color: #cccccc;">ALTER SESSION SET <span style="color: red;">OPTIMIZER_USE_PENDING_STATISTICS</span>=TRUE;</span><br />
<br />
Il est possible de transférer les statistiques en attente d'une base à une autre avec la procédure <span style="color: red;">DBMS_STATS.EXPORT_PENDING_STATS</span></div>loranhttp://www.blogger.com/profile/16224793437590995506noreply@blogger.com0tag:blogger.com,1999:blog-2141579913468236718.post-56174023391075760042011-08-01T15:24:00.012+02:002011-08-02T11:03:16.313+02:00STATISTIQUES : extended statistics 11g<u><span style="font-family: "Courier New",Courier,monospace;">Statistiques étendues</span></u><br />
<br />
<span style="font-family: "Courier New",Courier,monospace;">Oracle peut désormais en 11g, recueillir de nouveaux types de statistiques, rendant la qualité de l'estimation de sélectivité bien meilleure dans les cas concernés.</span><br />
<br />
<span style="color: red; font-family: "Courier New",Courier,monospace;">Statistiques multi-colonnes</span><br />
<br />
<span style="font-family: "Courier New",Courier,monospace;">Lorsque dans la clause where d'une requête, figurent plusieurs prédicats mono-colonnes d'une même table, la corrélation entre ces colonne peut avoir</span><span style="font-family: "Courier New",Courier,monospace;"> des conséquences sur la sélectivité de ce groupe de colonnes.</span><br />
<span style="font-family: "Courier New",Courier,monospace;">Il est par conséquent très utile de pouvoir définir des groupes de colonnes sur lesquels on déterminera des statistiques de répartition de données </span><span style="font-family: "Courier New",Courier,monospace;">afin de rendre plus optimal le choix de l'optimiseur dans ces cas.</span><br />
<br />
<div style="background-color: #cccccc;"><span style="font-family: "Courier New",Courier,monospace;"><u>Creating a Column Group</u><br />
<br />
DECLARE<br />
cg_name varchar2(30);<br />
BEGIN<br />
cg_name := <span style="color: red;">dbms_stats.create_extended_stats</span>(null,'customers', <br />
'(cust_state_province,country_id)');<br />
END;<br />
/<br />
<br />
<u>Getting a Column Group</u><br />
<br />
select<span style="color: red;"> sys.dbms_stats.show_extended_stats_name</span>('sh','customers',<br />
'(cust_state_province,country_id)') col_group_name <br />
from dual;<br />
<br />
The output is similar to the following:<br />
<br />
COL_GROUP_NAME<br />
----------------<br />
SYS_STU#S#WF25Z#QAHIHE#MOFFMM<br />
<br />
<u>Dropping a Column Group</u><br />
<br />
exec <span style="color: red;">dbms_stats.drop_extended_stats</span>('sh','customers',</span></div><div style="background-color: #cccccc;"><span style="font-family: "Courier New",Courier,monospace;">'(cust_state_province,country_id)');<br />
<br />
<u>Monitoring Column Groups</u><br />
<br />
Use the dictionary table user_stat_extensions to obtain information about MultiColumn statistics:<br />
<br />
Select extension_name, extension <br />
from <span style="color: red;">user_stat_extensions </span><br />
where table_name='CUSTOMERS';<br />
<br />
EXTENSION_NAME EXTENSION<br />
-----------------------------------------------------------------<br />
SYS_STU#S#WF25Z#QAHIHE#MOFFMM_("CUST_STATE_PROVINCE","COUNTRY_ID")<br />
<br />
Use the following query to find the number of distinct values and find whether a histogram has been created for a column group:<br />
<br />
select e.extension col_group, t.num_distinct, t.histogram<br />
2 from <span style="color: red;">user_stat_extensions</span> e, user_tab_col_statistics t<br />
3 where e.extension_name=t.column_name<br />
4 and e.table_name=t.table_name<br />
5 and t.table_name='CUSTOMERS';<br />
<br />
COL_GROUP NUM_DISTINCT HISTOGRAM<br />
-----------------------------------------------------------------<br />
("COUNTRY_ID","CUST_STATE_PROVINCE") 145 FREQUENCY<br />
</span></div><br />
<br />
<span style="color: red; font-family: "Courier New",Courier,monospace;">Statistiques d'expressions</span><br />
<br />
<span style="font-family: "Courier New",Courier,monospace;">Lorsque une fonction est appliquée à une colonne dans la clause "where" (fonction(col1)=constant), l'optimiseur n'a aucun moyen de savoir comment cette fonction affecte la sélectivité de la colonne.</span><br />
<span style="font-family: "Courier New",Courier,monospace;">En recueillant les statistiques de cette expression, l'optimiseur obtient une valeur de sélectivité bien plus juste.</span><br />
<span style="font-family: "Courier New",Courier,monospace;"> </span><br />
<div style="background-color: #cccccc;"><span style="font-family: "Courier New",Courier,monospace;"><u>Creating Expression Statistics</u> </span><br />
<br />
<span style="font-family: "Courier New",Courier,monospace;">select <br />
<span style="color: red;">dbms_stats.create_extended_stats</span>(null,'customers',</span></div><div style="background-color: #cccccc;"><span style="font-family: "Courier New",Courier,monospace;">'(lower(cust_state_province))') <br />
from dual;<br />
<br />
<u>Monitoring Expression Statistics</u><br />
<br />
Select extension_name, extension <br />
from <span style="color: red;">user_stat_extensions</span> <br />
where table_name='CUSTOMERS';<br />
<br />
EXTENSION_NAME EXTENSION<br />
------------------------------------------------------------------<br />
SYS_STUBPHJSBRKOIK9O2YV3W8HOUE (LOWER("CUST_STATE_PROVINCE"))<br />
<br />
<u>Dropping Expression Statistics</u><br />
<br />
exec <span style="color: red;">dbms_stats.drop_extended_stats</span>(null,'customers',</span></div><div style="background-color: #cccccc;"><span style="font-family: "Courier New",Courier,monospace;">'(lower(country_id))'); </span><br />
<div style="background-color: white;"><br />
</div><div style="background-color: white;"><br />
</div><div style="background-color: white;"><span style="font-family: "Courier New",Courier,monospace;">Il est possible de calculer directement les statistiques sur une extension (groupe ou fonction) de colonne(s) sans avoir à créer cette extension explicitement, celle-ci l'est implicitement et demeure dans la liste des extension.</span><br />
<span style="font-family: "Courier New",Courier,monospace;"> </span></div><span style="font-family: "Courier New",Courier,monospace;"><u>Gathering </u></span><span style="font-family: "Courier New",Courier,monospace;"><u>Extended </u></span><span style="font-family: "Courier New",Courier,monospace;"><u>Statistics</u></span><span style="font-family: "Courier New",Courier,monospace;"> </span><br />
<span style="font-family: "Courier New",Courier,monospace;"> </span><br />
<span style="font-family: "Courier New",Courier,monospace;">exec <span style="color: red;">dbms_stats.gather_table_stats</span>('sh','customers',method_opt =><br />
'for all columns size skewonly<br />
for columns (cust_state_province,country_id) <span style="color: red;">size skewonly</span>');</span><br />
<div style="font-family: "Courier New",Courier,monospace;"><br />
et<br />
</div><span style="font-family: "Courier New",Courier,monospace;">exec <span style="color: red;">dbms_stats.gather_table_stats</span>('sh','customers', method_opt =><br />
'for all columns size skewonly <br />
for columns (lower(cust_state_province)) <span style="color: red;">size skewonly</span>');<br />
</span><br />
<br />
<span style="font-family: "Courier New",Courier,monospace;">Nb : <span style="color: red;">skewonly </span>indique à Oracle de ne calculer des histogrammes que pour les colonnes qui ont une répartition très inégale.</span></div>loranhttp://www.blogger.com/profile/16224793437590995506noreply@blogger.com0tag:blogger.com,1999:blog-2141579913468236718.post-2688856884136188892011-08-01T13:54:00.016+02:002011-08-01T16:49:33.583+02:00STATISTIQUES : lock statistics<span style="font-family: "Courier New",Courier,monospace;">Le package DBMS_STATS offre la possibilité des verrouiller les statistiques d'une table ou de toutes les tables d'un schéma.</span><br />
<br />
<span style="font-family: "Courier New",Courier,monospace;">Ceci peut être utile lorsque l'on souhaite que la collecte automatique ne passe pas sur certaines tables dont on maîtrise le chargement et donc le moment opportun pour passer les statistiques.</span><br />
<br />
<div style="font-family: "Courier New",Courier,monospace;">Procédures de lock de statistiques :</div><br />
<div style="background-color: #cccccc;"><span style="font-family: "Courier New",Courier,monospace;"> </span></div><div style="background-color: white;"><span style="font-family: "Courier New",Courier,monospace;">DBMS_STATS.<span style="color: red;">LOCK_TABLE_STATS</span> (</span><br />
<span style="font-family: "Courier New",Courier,monospace;"> ownname VARCHAR2,tabname VARCHAR2);</span><br />
<span style="font-family: "Courier New",Courier,monospace;">DBMS_STATS.<span style="color: red;">UNLOCK_TABLE_STATS</span> (</span><span style="font-family: "Courier New",Courier,monospace;"> </span><br />
<span style="font-family: "Courier New",Courier,monospace;"> ownname VARCHAR2,</span><span style="font-family: "Courier New",Courier,monospace;">tabname VARCHAR2);</span></div><div style="background-color: white;"><span style="font-family: "Courier New",Courier,monospace;">DBMS_STATS.<span style="color: red;">LOCK_SCHEMA_STATS</span> (</span><br />
<span style="font-family: "Courier New",Courier,monospace;"> ownname VARCHAR2);</span><br />
<span style="font-family: "Courier New",Courier,monospace;">DBMS_STATS.<span style="color: red;">UNLOCK_SCHEMA_STATS </span>(</span><br />
<span style="font-family: "Courier New",Courier,monospace;"> ownname VARCHAR2);</span></div><div style="background-color: white;"></div><div style="background-color: #cccccc; font-family: "Courier New",Courier,monospace;"><div style="background-color: white;"><br />
</div><div style="background-color: white;"><u>Exemples de comportement en LOCK ou UNLOCK :</u></div><div style="background-color: white;"><br />
</div>SQL> drop table couuk8.toto;<br />
<br />
Table dropped.<br />
<br />
SQL> CREATE TABLE couuk8.toto (<br />
2 a NUMBER<br />
3 );<br />
<br />
Table created.<br />
<br />
SQL> SELECT last_analyzed FROM dba_tables<br />
2 WHERE table_name='TOTO'<br />
3 AND owner='COUUK8';<br />
<br />
LAST_ANA<br />
--------<br />
<br />
<br />
SQL> -- test 1<br />
SQL> EXEC DBMS_STATS.<span style="color: red;">LOCK_TABLE_STATS</span> ('COUUK8','TOTO');<br />
<br />
PL/SQL procedure successfully completed.<br />
<br />
SQL> col owner for a8 trunc<br />
SQL> col table_name for a10 trunc<br />
SQL> SELECT owner, table_name, <span style="color: red;">stattype_locked</span><br />
2 FROM <span style="color: red;">dba_tab_statistics</span><br />
3 WHERE owner = 'COUUK8'<br />
4 AND stattype_locked IS NOT NULL<br />
5 ;<br />
<br />
<br />
OWNER TABLE_NAME <span style="color: red;">STATT</span><br />
-------- ---------- -----<br />
COUUK8 TOTO <span style="color: red;">ALL</span><br />
<br />
SQL> EXEC DBMS_STATS.<span style="color: red;">GATHER_SCHEMA_STATS</span> ('COUUK8');<br />
<br />
PL/SQL procedure successfully completed.<br />
<br />
SQL> SELECT last_analyzed FROM dba_tables<br />
2 WHERE table_name='TOTO'<br />
3 AND owner='COUUK8';<br />
<br />
LAST_ANA<br />
--------<br />
<br />
<br />
SQL> -- test 2<br />
SQL> EXEC DBMS_STATS.<span style="color: red;">UNLOCK_TABLE_STATS</span> ('COUUK8','TOTO');<br />
<br />
PL/SQL procedure successfully completed.<br />
<br />
SQL> EXEC DBMS_STATS.<span style="color: red;">GATHER_SCHEMA_STATS</span> ('COUUK8');<br />
<br />
PL/SQL procedure successfully completed.<br />
<br />
SQL> SELECT last_analyzed FROM dba_tables<br />
2 WHERE table_name='TOTO'<br />
3 AND owner='COUUK8';<br />
<br />
LAST_ANA<br />
--------<br />
01/08/11<br />
<br />
SQL> -- test 3<br />
SQL> EXEC DBMS_STATS.<span style="color: red;">DELETE_TABLE_STATS</span> ('COUUK8','TOTO');<br />
<br />
PL/SQL procedure successfully completed.<br />
<br />
SQL> SELECT last_analyzed FROM dba_tables<br />
2 WHERE table_name='TOTO'<br />
3 AND owner='COUUK8';<br />
<br />
LAST_ANA<br />
--------<br />
<br />
<br />
SQL> EXEC DBMS_STATS.<span style="color: red;">LOCK_TABLE_STATS</span> ('COUUK8','TOTO');<br />
<br />
PL/SQL procedure successfully completed.<br />
<br />
SQL> EXEC DBMS_STATS.<span style="color: red;">GATHER_TABLE_STATS</span> ('COUUK8','TOTO');<br />
BEGIN DBMS_STATS.GATHER_TABLE_STATS ('COUUK8','TOTO'); END;<br />
<br />
*<br />
ERROR at line 1:<br />
ORA-20005: object statistics are locked (stattype = ALL)<br />
ORA-06512: at "SYS.DBMS_STATS", line 13437<br />
ORA-06512: at "SYS.DBMS_STATS", line 13457<br />
ORA-06512: at line 1<br />
<br />
<br />
SQL> -- test 4<br />
SQL> EXEC DBMS_STATS.<span style="color: red;">UNLOCK_TABLE_STATS</span> ('COUUK8','TOTO');<br />
<br />
PL/SQL procedure successfully completed.<br />
<br />
SQL> EXEC DBMS_STATS.<span style="color: red;">GATHER_TABLE_STATS</span> ('COUUK8','TOTO');<br />
<br />
PL/SQL procedure successfully completed.<br />
<br />
SQL> SELECT last_analyzed FROM dba_tables<br />
2 WHERE table_name='TOTO'<br />
3 AND owner='COUUK8';<br />
<br />
LAST_ANA<br />
--------<br />
01/08/11</div>loranhttp://www.blogger.com/profile/16224793437590995506noreply@blogger.com0tag:blogger.com,1999:blog-2141579913468236718.post-77217183901518657492011-08-01T12:17:00.005+02:002011-08-01T16:03:40.646+02:00STATISTIQUES : dynamic sampling<span style="font-family: "Courier New",Courier,monospace;">Oracle offre la possibilité à l'optimiseur de calculer ses statistiques à la volée, en utilisant le mécanisme <span style="color: red;">DYNAMIC SAMPLING</span><span style="font-family: "Courier New",Courier,monospace;">. </span>Ceci peut se révéler efficace lorsque Oracle détecte que des statistiques sont absentes ou insuffisament précises, en contre-partie, le parsing de la requête durera un peu plus longtemps...</span><br />
<br />
<span style="font-family: "Courier New",Courier,monospace;">Pour activer ce mécanisme, il faut </span><br />
<ul><li><span style="font-family: "Courier New",Courier,monospace;">soit que le paramètre d'initialisation <span style="color: red;">OPTIMIZER_DYNAMIC_SAMPLING</span> soit positionné à une valeur entre 1 et 10</span></li>
<li><span style="font-family: "Courier New",Courier,monospace;">soit que le hint <span style="color: red;">/*+ DYNAMIC_SAMPLING(tab value) */</span> soit indiqué dans une requête.</span></li>
</ul><div style="background-color: #cccccc; font-family: "Courier New",Courier,monospace;"></div><div style="background-color: #cccccc;"><span style="font-family: "Courier New",Courier,monospace;">Exemple :</span></div><div style="background-color: #cccccc;"><span style="font-family: "Courier New",Courier,monospace;"> </span><br style="font-family: "Courier New",Courier,monospace;" /><span style="font-family: "Courier New",Courier,monospace;">SELECT /*+ dynamic_sampling(e 1) */ count(*) </span><br style="font-family: "Courier New",Courier,monospace;" /><span style="font-family: "Courier New",Courier,monospace;">FROM employees e;</span></div><br />
<span style="font-family: "Courier New",Courier,monospace;">Oracle détermine ainsi au moment de la compilation si une requête peut bénéficier du <span style="color: red;">DYNAMIC SAMPLING</span>. Si tel est le cas, un SQL recursif est exécuté pour scanner un échantillon des blocks de la table et comparer les résultats aux valeurs des prédicats de la requête avant de choisir le meilleur plan d'exécution.</span>loranhttp://www.blogger.com/profile/16224793437590995506noreply@blogger.com0tag:blogger.com,1999:blog-2141579913468236718.post-38228919450228608232011-08-01T12:16:00.013+02:002011-08-01T16:12:48.200+02:00STATISTIQUES : statistiques système<div style="font-family: "Courier New",Courier,monospace;"><u>Statistiques "</u><span lang="EN-GB"><u>SYSTEM"</u> </span><br />
<br />
<span lang="EN-GB">En plus des statistiques sur la composition des données des tables, l'optimiseur Oracle doit connaître l'état du système sur lequel il détermine ses plans d'exécution. Pour ce faire, les statistiques systèmes doivent être calculées et fournies à l'optimiseur.</span></div><div style="font-family: "Courier New",Courier,monospace;"><span lang="EN-GB"><br />
</span></div><div style="font-family: "Courier New",Courier,monospace;"><span lang="EN-GB">Ces statistiques comportent les informations de performances générales de la CPU et des I/O du serveur. Les besoins en CPU et I/O sont estimés par l'optimiseur pour chaque requête, et sont mises en regard des performances du serveur précédemment déterminées par le calcul de statistiques système. Ainsi Oracle est en mesure de choisir le meilleur plan d'exécution.</span></div><div style="font-family: "Courier New",Courier,monospace;"></div><div style="font-family: "Courier New",Courier,monospace;"><br />
</div><div style="font-family: "Courier New",Courier,monospace;">Oracle analyse l'activité du sytème dans une période de temps donnée (<span style="color: red;">workload statistics</span>) ou simule une activité (<span style="color: red;">noworkload statistics</span>). Les statistiques système sont recueillies par la procédure suivante <span style="color: red;">DBMS_STATS.GATHER_SYSTEM_STATS</span>.</div><br />
<div class="MsoNormal" style="font-family: "Courier New",Courier,monospace;">Extrait de la doc Oracle.<br />
<b><span lang="EN-GB" style="font-family: "Times New Roman"; font-size: 12pt;">Oracle® Database Performance Tuning Guide - 10g Release 2 (10.2)</span></b><br />
<b><span lang="EN-GB" style="font-family: "Times New Roman"; font-size: 12pt;">Chap 14.</span></b><br />
<b><span lang="EN-GB" style="font-family: "Times New Roman"; font-size: 12pt;"> </span></b><br />
<blockquote><div style="background-color: white;"><u>Gathering Workload Statistics</u></div><div style="background-color: white;"><br />
</div><div style="background-color: white;">To gather workload statistics, either:</div><div style="background-color: white;"><br />
</div><div style="background-color: white;">• Run the <span style="color: red;">dbms_stats.gather_system_stats('start')</span> procedure at the beginning of the workload window, then the <span style="color: red;">dbms_stats.gather_system_stats('stop')</span> procedure at the end of the workload window.</div><div style="background-color: white;"><br />
</div><div style="background-color: white;">• Run <span style="color: red;">dbms_stats.gather_system_stats('interval', interval=>N)</span> where N is the number of minutes when statistics gathering will be stopped automatically.</div><div style="background-color: white;"><br />
</div><div style="background-color: white;">To delete system statistics, run <span style="color: red;">dbms_stats.delete_system_stats()</span>. Workload statistics will be deleted and reset to the default noworkload statistics.</div><div style="background-color: white;"><br />
</div><div style="background-color: white;"><u>Gathering Noworkload Statistics</u></div><div style="background-color: white;"><br />
</div><div style="background-color: white;">To gather noworkload statistics, run <span style="color: red;">dbms_stats.gather_system_stats()</span> with no arguments. There will be an overhead on the I/O system during the gathering process of noworkload statistics. The gathering process may take from a few seconds to several minutes, depending on I/O performance and database size.</div><div style="background-color: white;"> </div><div style="background-color: white;">The information is analyzed and verified for consistency. In some cases, the value of noworkload statistics may remain its default value. In such cases, repeat the statistics gathering process or set the value manually to values that the I/O system has according to its specifications by using the <span style="color: red;">dbms_stats.set_system_stats </span>procedure.</div><div style="background-color: white;"><br />
</div><div style="background-color: white;"><span lang="EN-GB" style="font-size: small;">"Oracle Corporation highly recommends that you gather system statistics"</span></div></blockquote><br />
<br />
Détermination des valeurs des statistiques système :<br />
<br />
<div class="MsoNormal" style="background-color: #cccccc; font-family: "Courier New",Courier,monospace;"><span lang="EN-GB" style="font-size: small;">SQL> select version from v$instance;</span></div><div class="MsoNormal" style="background-color: #cccccc; font-family: "Courier New",Courier,monospace;"><span style="font-size: small;"><br />
</span></div><div class="MsoNormal" style="background-color: #cccccc; font-family: "Courier New",Courier,monospace;"><span lang="EN-GB" style="font-size: small;">VERSION</span></div><div class="MsoNormal" style="background-color: #cccccc; font-family: "Courier New",Courier,monospace;"><span lang="EN-GB" style="font-size: small;">-----------------</span></div><div class="MsoNormal" style="background-color: #cccccc; font-family: "Courier New",Courier,monospace;"><span lang="EN-GB" style="font-size: small;">10.2.0.4.0</span></div><div class="MsoNormal" style="background-color: #cccccc; font-family: "Courier New",Courier,monospace;"><span style="font-size: small;"><br />
</span></div><div class="MsoNormal" style="background-color: #cccccc; font-family: "Courier New",Courier,monospace;"><span lang="EN-GB" style="font-size: small;">SQL> select pname, pval1 from <span style="color: red;">sys.aux_stats$</span> where sname = 'SYSSTATS_MAIN';</span></div><div class="MsoNormal" style="background-color: #cccccc; font-family: "Courier New",Courier,monospace;"><span style="font-size: small;"><br />
</span></div><div class="MsoNormal" style="background-color: #cccccc; font-family: "Courier New",Courier,monospace;"><span lang="EN-GB" style="font-size: small;">PNAME PVAL1</span></div><div class="MsoNormal" style="background-color: #cccccc; font-family: "Courier New",Courier,monospace;"><span lang="EN-GB" style="font-size: small;">------------------------------ ----------</span></div><div class="MsoNormal" style="background-color: #cccccc; font-family: "Courier New",Courier,monospace;"><span lang="EN-GB" style="font-size: small;">SREADTIM 8,855</span></div><div class="MsoNormal" style="background-color: #cccccc; font-family: "Courier New",Courier,monospace;"><span lang="EN-GB" style="font-size: small;">MREADTIM 39,009</span></div><div class="MsoNormal" style="background-color: #cccccc; font-family: "Courier New",Courier,monospace;"><span lang="EN-GB" style="font-size: small;">CPUSPEED 432</span></div><div class="MsoNormal" style="background-color: #cccccc; font-family: "Courier New",Courier,monospace;"><span lang="EN-GB" style="font-size: small;">MBRC 28</span></div><div class="MsoNormal" style="background-color: #cccccc; font-family: "Courier New",Courier,monospace;"><span lang="EN-GB" style="font-size: small;">MAXTHR 164772864</span></div><div class="MsoNormal" style="background-color: #cccccc; font-family: "Courier New",Courier,monospace;"><span lang="EN-GB" style="font-size: small;">SLAVETHR 538624</span></div><div class="MsoNormal" style="background-color: #cccccc; font-family: "Courier New",Courier,monospace;"><span style="font-size: small;"><br />
</span></div><div style="background-color: #cccccc;"><span lang="EN-GB" style="font-size: small;">6 rows selected.</span></div><br />
<br />
<u>Statistiques sur les FIXED OBJECTS </u><br />
<br />
Il est également nécessaire de recueillir les statistiques sur les objets fixes "fixed objects" comme les tables de performance dynamiques en utilisant la procédure <span style="color: red;">DBMS_STATS.GATHER_FIXED_OBJECTS_STATS</span>.<br />
Ces objets enregistrent l'activité courante de la base.<br />
<br />
Ces statistiques doivent être collectées lorsque la base a une activité représentative.<br />
<br />
<br />
<u>Statistiques sur les </u><u>SCHEMAS SYSTEMES</u><br />
</div><div class="MsoNormal" style="font-family: "Courier New",Courier,monospace;"><span lang="EN-GB">Enfin pour calculer les statistiques sur les schémas système (SYS, SYSTEM, CTXSYS, DRSYS), on peut utiliser la procédure </span><span style="color: red;">DBMS_STATS.</span><span lang="EN-GB"><span style="color: red;">GATHER_DICTIONARY_STATS</span>.</span></div>loranhttp://www.blogger.com/profile/16224793437590995506noreply@blogger.com0tag:blogger.com,1999:blog-2141579913468236718.post-39129813198261639902011-08-01T11:39:00.036+02:002011-08-01T17:47:21.218+02:00STATISTIQUES : collecte automatique<div style="font-family: "Courier New",Courier,monospace;"><u>DBMS_STATS</u><br />
<br />
Le package <span style="color: red;">DBMS_STATS</span> est au centre du processus de calcul de statistiques sur les objets de la base Oracle depuis la version Oracle 8i.<br />
<br />
Pour les procédures <span style="color: red;">GATHER_SCHEMA_STATS</span> et <span style="color: red;">GATHER_DATABASE_STATS</span>, L'option <span style="color: red;">GATHER_STALE</span> permet de calculer les statistiques devenues obsolètes (contenu des tables modifié de plus de 10%). L'option <span style="color: red;">GATHER_AUTO</span> calcule en plus les statistiques des tables qui n'en n'ont pas (comme l'option <span style="color: red;">GATHER_EMPTY</span>)<br />
<div style="color: red;"><br />
</div><div style="color: red;">GATHER_AUTO = GATHER_STALE + GATHER_EMPTY</div><br />
NB : L'option <span style="color: red;">GATHER </span>(tout court), indique de recalculer tout le schéma sans autre considération.<br />
<br />
<br />
<u>MONITORING</u><br />
<br />
Le <span style="color: red;">monitoring </span>est le process qui enregistre le nombre de modifications survenues sur une table.<br />
<br />
En 9i, les paramètres <span style="color: red;">MONITORING </span>et <span style="color: red;">NOMONITORING </span>des tables indiquaient si les modifications subies par ces tables devaient être monitorées ou pas.<br />
<br />
A partir de la 10g, ce paramètre est obsolète et n'est plus pris en compte. Seul le paramètre d'init <span style="color: red;">STATISTICS_LEVEL</span> commande le monitoring et le calcul de statistiques des objets de la base.<br />
<br />
Valeurs possibles :<br />
<br />
* <span style="color: red;">ALL </span>(collecte automatique des statistiques activé + statistiques supplémentaires OS et plans). The additional statistics are timed OS statistics and plan execution statistics.<br />
* <span style="color: red;">TYPICAL </span>(valeur par défaut - collect automatique des statistiques activée)<br />
* <span style="color: red;">BASIC </span>(désactive le monitoring des tables ainsi que le calcul automatique des statistiques - non recommandé par Oracle)<br />
<br />
Le monitoring des tables, actualise les nombre de lignes mises à jour depuis le dernier calcul de statistiques de cette table. Toutes les 3 heures environ, SMON flush les valeurs de monitoring contenues dans la SGA dans le dictionnaire de données. (vue <span style="color: red;">DBA_TAB_MODIFICATIONS</span>).<br />
<br />
Flush manuel :<br />
<br />
<div style="background-color: #cccccc;">EXEC dbms_stats.flush_database_monitoring_info; </div><br />
<br />
<u>GATHER_STATS_JOB</u><br />
<br />
Le job <span style="color: red;">GATHER_STATS_JOB</span> est prédéfini lors de la création de la base Oracle. Il se charge de collecter les statistiques pour tous les objets de la base pour lesquels il n'y a pas de statistiques ou pour lesquels les statistiques sont obsolètes.<br />
<br />
Le <span style="color: red;">scheduler </span>exécute ce job lorsque la fenêtre de maintenance est ouverte. (Par défaut <span style="color: red;">tous les jours de 22H00 à 06H00</span>) - Le job s'interrompt à la fermeture de la fenêtre de maintenance. Le Job appelle la procédure <span style="color: red;">DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC</span>. Cette procédure est similaire à la procédure <span style="color: red;">DBMS_STATS.GATHER_DATABASE_STATS</span>, mais en plus elle priorise les objets à analyser.<br />
<br />
Lister le job GATHER_STATS_JOB :<br />
<br />
<br />
<div style="background-color: #cccccc;">SELECT *<br />
FROM <span style="color: red;">DBA_SCHEDULER_JOBS</span></div><div style="background-color: #cccccc;">WHERE JOB_NAME = 'GATHER_STATS_JOB';</div><br />
<br />
Si l'on souhaite collecter les statistiques manuellement et donc désactiver le calcul automatique :<br />
<br />
<br />
<div style="background-color: #cccccc;">en 10g :</div><div style="background-color: #cccccc;"><br />
</div><div style="background-color: #cccccc;">BEGIN</div><div style="background-color: #cccccc;"> <span style="color: red;">DBMS_SCHEDULER.DISABLE</span>('GATHER_STATS_JOB');</div><div style="background-color: #cccccc;">END;</div><div style="background-color: #cccccc;"></div><div style="background-color: #cccccc;">/</div><div style="background-color: #cccccc;"><br />
</div><div style="background-color: #cccccc;">en 11g :</div><div style="background-color: #cccccc;"><br />
</div><div style="background-color: #cccccc;">BEGIN</div><div style="background-color: #cccccc;"> <span style="color: red;">DBMS_AUTO_TASK_ADMIN.DISABLE</span>(</div><div style="background-color: #cccccc;"> client_name => 'auto optimizer stats collection', </div><div style="background-color: #cccccc;"> operation => NULL, </div><div style="background-color: #cccccc;"> window_name => NULL);</div><div style="background-color: #cccccc;">END;</div><div style="background-color: #cccccc;">/</div><br />
<br />
En 11g deux nouveaux concepts sont introduits :<br />
<br />
- prise de statistiques sur des colonnes multiples et sur des fonctions de colonnes<br />
- possibilité de publier les statistiques </div>loranhttp://www.blogger.com/profile/16224793437590995506noreply@blogger.com0tag:blogger.com,1999:blog-2141579913468236718.post-19681270856396799062011-07-27T18:05:00.009+02:002011-07-27T18:23:52.984+02:00TRUNCATE : Appel de Procédure Stockée via db link<div style="font-family: "Courier New",Courier,monospace;"></div><div style="font-family: "Courier New",Courier,monospace;">Alors que le delete, lui, fonctionne sans problème, la commande <span style="color: red;">truncate </span>en tant que DDL ne passe pas au travers d'un db link.</div><div style="font-family: "Courier New",Courier,monospace;"><br />
</div><div style="font-family: "Courier New",Courier,monospace;"><br />
</div><div style="background-color: #cccccc; font-family: "Courier New",Courier,monospace;"></div><div style="background-color: #cccccc; font-family: "Courier New",Courier,monospace;"><div style="background-color: #cccccc; font-family: "Courier New",Courier,monospace;"><u>Sur la base cible :</u></div><div style="background-color: #cccccc; font-family: "Courier New",Courier,monospace;"><br />
</div><div style="background-color: #cccccc; font-family: "Courier New",Courier,monospace;">SQL> SELECT COUNT(*) FROM toto;<br />
<br />
COUNT(*)<br />
----------<br />
1</div><br />
<u>Sur la base source :</u><br />
<br />
SQL> DELETE FROM toto@versqua;<br />
<br />
1 row deleted.<br />
<br />
SQL> ROLLBACK;<br />
<br />
Rollback complete.<br />
<br />
SQL> <span style="color: red;">TRUNCATE</span> TABLE toto@versqua;<br />
TRUNCATE TABLE toto@versqua<br />
*<br />
ERROR at line 1:<br />
ORA-02021: <span style="color: red;">DDL operations are not allowed on a remote database</span> </div><div style="font-family: "Courier New",Courier,monospace;"><span style="background-color: #cccccc;"> </span></div><div style="font-family: "Courier New",Courier,monospace;"><br />
<br />
Il existe une solution qui consiste à disposer une procédure stockée sur la base cible qui effectue le <span style="color: red;">truncate</span>. Cette procédure étant appelée depuis la base source via db link.<br />
</div><div style="font-family: "Courier New",Courier,monospace;"></div><div style="background-color: #cccccc; font-family: "Courier New",Courier,monospace;"><u>Sur la base cible :</u></div><div style="background-color: #cccccc; font-family: "Courier New",Courier,monospace;"><br />
</div><div style="background-color: #cccccc; font-family: "Courier New",Courier,monospace;">SQL> SELECT COUNT(*) FROM toto;<br />
<br />
COUNT(*)<br />
----------<br />
1<br />
</div><div style="background-color: #cccccc; font-family: "Courier New",Courier,monospace;">SQL> CREATE PROCEDURE p_u_truncate (p_table IN VARCHAR2)<br />
2 IS<br />
3 BEGIN<br />
4 EXECUTE IMMEDIATE '<span style="color: red;">TRUNCATE</span> TABLE ' || p_table;<br />
5 END;<br />
6 /<br />
<br />
Procedure created.<br />
<br />
</div><div style="background-color: #cccccc; font-family: "Courier New",Courier,monospace;"><u>Sur la base source :</u></div><div style="background-color: #cccccc; font-family: "Courier New",Courier,monospace;"><br />
</div><div style="background-color: #cccccc; font-family: "Courier New",Courier,monospace;">SQL> EXEC <span style="color: red;">p_u_truncate</span>@versqua('TOTO');<br />
<br />
PL/SQL procedure successfully completed.</div><div style="background-color: #cccccc; font-family: "Courier New",Courier,monospace;"><u><br />
</u></div><div style="background-color: #cccccc; font-family: "Courier New",Courier,monospace;"><u><br />
</u></div><div style="background-color: #cccccc; font-family: "Courier New",Courier,monospace;"><u>Sur la base cible :</u></div><div style="background-color: #cccccc; font-family: "Courier New",Courier,monospace;"><br />
</div><div style="background-color: #cccccc; font-family: "Courier New",Courier,monospace;">SQL> SELECT COUNT(*) FROM toto;<br />
<br />
COUNT(*)<br />
----------<br />
0</div><div style="background-color: white; font-family: "Courier New",Courier,monospace;"><br />
<u>Explication</u> : la procédure <b>stockée sur la base cible</b> est appelée depuis la base source et <b>s'exécute sur la base cible</b>.</div><div style="font-family: "Courier New",Courier,monospace;"></div>loranhttp://www.blogger.com/profile/16224793437590995506noreply@blogger.com0tag:blogger.com,1999:blog-2141579913468236718.post-6276565471873593382011-07-19T18:15:00.007+02:002011-08-02T10:05:02.363+02:00DOUBLONS : détecter les doublons partiels<div style="font-family: "Courier New",Courier,monospace;">Comment détecter les lignes d'une table faisant doublon sur une liste de colonne (et non pas sur l'ensemble de la ligne).</div><div style="font-family: "Courier New",Courier,monospace;"><br />
</div><div style="font-family: "Courier New",Courier,monospace;">Exemple :</div><div style="font-family: "Courier New",Courier,monospace;"><br />
</div><div style="background-color: #cccccc; font-family: "Courier New",Courier,monospace;">SQL> CREATE TABLE toto (<br />
2 pays VARCHAR2(3),<br />
3 ville VARCHAR2(10),<br />
4 note NUMBER<br />
5 );<br />
<br />
Table created.<br />
<br />
SQL> INSERT INTO toto VALUES ('FRA','PARIS',1);<br />
<br />
1 row created.<br />
<br />
SQL> INSERT INTO toto VALUES ('FRA','PARIS',2);<br />
<br />
1 row created.<br />
<br />
SQL> INSERT INTO toto VALUES ('FRA','PARIS',3);<br />
<br />
1 row created.<br />
<br />
SQL> INSERT INTO toto VALUES ('FRA','PARIS',3);<br />
<br />
1 row created.<br />
<br />
SQL> INSERT INTO toto VALUES ('FRA','LYON',4);<br />
<br />
1 row created.<br />
<br />
SQL> INSERT INTO toto VALUES ('FRA','LYON',5);<br />
<br />
1 row created.<br />
<br />
SQL> INSERT INTO toto VALUES ('FRA','MARSEILLE',5);<br />
<br />
1 row created.<br />
<br />
SQL> INSERT INTO toto VALUES ('USA','NEW YORK',5);<br />
<br />
1 row created.<br />
<br />
SQL> COMMIT;<br />
<br />
Commit complete.</div><div style="background-color: white; font-family: "Courier New",Courier,monospace;"><br />
</div><div style="background-color: white; font-family: "Courier New",Courier,monospace;"></div><div style="background-color: white; font-family: "Courier New",Courier,monospace;">Pour le cas classique :</div><div style="background-color: white; font-family: "Courier New",Courier,monospace;"><br />
</div><div style="background-color: #cccccc; font-family: "Courier New",Courier,monospace;">SQL> SELECT t1.pays, t1.ville, t1.note, count(*)<br />
2 FROM toto t1<br />
3 GROUP BY t1.pays, t1.ville, t1.note<br />
4 HAVING COUNT(*) > 1<br />
5 ;<br />
<br />
PAY VILLE NOTE COUNT(*)<br />
--- ---------- ---------- ----------<br />
FRA PARIS 3 2<br />
<br />
Et la liste :</div><div style="background-color: #cccccc; font-family: "Courier New",Courier,monospace;"><br />
SQL> SELECT t1.* FROM toto t1 WHERE t1.rowid >= (<br />
2 SELECT MIN(t2.rowid) FROM toto t2<br />
3 WHERE t1.pays=t2.pays<br />
4 AND t1.ville=t2.ville<br />
5 AND t1.note=t2.note<br />
6 HAVING COUNT(*) > 1<br />
7 )<br />
8 ;<br />
<br />
PAY VILLE NOTE<br />
--- ---------- ----------<br />
FRA PARIS 3 </div><div style="background-color: #cccccc; font-family: "Courier New",Courier,monospace;">FRA PARIS 3 </div><div style="background-color: white; font-family: "Courier New",Courier,monospace;"><br />
</div><div style="background-color: white; font-family: "Courier New",Courier,monospace;"></div><div style="background-color: white; font-family: "Courier New",Courier,monospace;">Et par extension, pour le cas des doublons partiels :</div><div style="background-color: white; font-family: "Courier New",Courier,monospace;"><br />
</div><div style="background-color: #cccccc; font-family: "Courier New",Courier,monospace;">SQL> SELECT t1.pays, t1.ville, COUNT(*)<br />
2 FROM toto t1<br />
3 GROUP BY t1.pays, t1.ville<br />
4 HAVING COUNT(*) > 1<br />
5 ;<br />
<br />
PAY VILLE COUNT(*)<br />
--- ---------- ----------<br />
FRA LYON 2<br />
FRA PARIS 4<br />
<br />
Et la liste :<br />
<br />
SQL> SELECT t1.* FROM toto t1 WHERE t1.rowid >= (<br />
2 SELECT MIN(t2.rowid) FROM toto t2<br />
3 WHERE t1.pays=t2.pays<br />
4 AND t1.ville=t2.ville<br />
5 HAVING COUNT(*) > 1<br />
6 )<br />
7 ;<br />
<br />
PAY VILLE NOTE<br />
--- ---------- ----------<br />
FRA PARIS 1<br />
FRA PARIS 2<br />
FRA PARIS 3<br />
FRA PARIS 3<br />
FRA LYON 4<br />
FRA LYON 5</div><div style="background-color: white; font-family: "Courier New",Courier,monospace;"></div><div style="font-family: "Courier New",Courier,monospace;"><span style="background-color: white;"></span> </div>loranhttp://www.blogger.com/profile/16224793437590995506noreply@blogger.com3tag:blogger.com,1999:blog-2141579913468236718.post-8679674936403242772011-07-19T17:06:00.003+02:002011-07-19T17:24:50.221+02:00APPEND NOLOGGING : accélérer les insertions<div style="font-family: "Courier New",Courier,monospace;">Pour les chargements massifs, il peut être intéressant d'utiliser certaines options qui permettent d'accélérer le temps de traitement. Bien sûr ces options ont des contre-parties.</div><div style="font-family: "Courier New",Courier,monospace;"><br />
</div><div style="font-family: "Courier New",Courier,monospace;">L'option <span style="color: red;">APPEND </span>pour l'insert force Oracle à passer directement au-delà du HWM (High Water Mark) pour insérer les nouvelles lignes. Elle évite ainsi la recherche des blocks libres et réduit fortement le nombre de blocks à sauvegarder dans les UNDO. Mais elle conduit à une éventuelle perte d'espace disque.</div><div style="font-family: "Courier New",Courier,monospace;"><br />
</div><div style="font-family: "Courier New",Courier,monospace;">L'option <span style="color: red;">NOLOGGING </span>indique à Oracle qu'il n'est pas nécessaire de logguer dans les REDO les changements qui ont lieu dans la table. Il s'ensuit une écriture moins importante dans les REDO, mais également l'impossibilité de récupérer la table avec les nouvelles lignes en cas de restauration de cette dernière.</div><div style="font-family: "Courier New",Courier,monospace;"><br />
</div><div style="font-family: "Courier New",Courier,monospace;"><u>Exemple</u></div><div style="font-family: "Courier New",Courier,monospace;"><br />
</div><div style="font-family: "Courier New",Courier,monospace;">Préparation :</div><div style="font-family: "Courier New",Courier,monospace;"><br />
</div><div style="background-color: #cccccc; font-family: "Courier New",Courier,monospace;">SQL> CREATE TABLE toto AS<br />
2 SELECT rownum ID, dbms_random.string('A', 12) STR,<br />
3 trunc(dbms_random.value(1,1000)) NUM<br />
4 FROM all_objects<br />
5 WHERE rownum <= 200000<br />
6 ;<br />
<br />
Table created.</div><div style="background-color: #cccccc; font-family: "Courier New",Courier,monospace;">SQL> INSERT INTO toto SELECT * FROM toto;<br />
<br />
200000 rows created.<br />
SQL> INSERT INTO toto SELECT * FROM toto;<br />
<br />
400000 rows created.<br />
SQL> COMMIT;<br />
<br />
Commit complete.</div><div style="background-color: #cccccc; font-family: "Courier New",Courier,monospace;"></div><div style="font-family: "Courier New",Courier,monospace;"><br />
</div><div style="font-family: "Courier New",Courier,monospace;">Session 1 :</div><div style="font-family: "Courier New",Courier,monospace;"><br />
</div><div style="background-color: #cccccc; font-family: "Courier New",Courier,monospace;">SQL> SET TIMING ON<br />
SQL> INSERT INTO toto SELECT * FROM toto;<br />
<br />
800000 rows created.<br />
<br />
Elapsed: 00:00:31.04<br />
SQL> ROLLBACK;<br />
<br />
Rollback complete.<br />
<br />
Elapsed: 00:00:02.09<br />
SQL> SELECT name, value FROM v$mystat NATURAL JOIN v$statname WHERE name = 'undo change vector size';<br />
<br />
NAME VALUE<br />
----------------------------------------------------- ----------<br />
undo change vector size 2270900<br />
</div><div style="background-color: #cccccc; font-family: "Courier New",Courier,monospace;">SQL> SELECT name, value FROM v$mystat NATURAL JOIN v$statname WHERE name = 'rollback changes - undo records applied';<br />
<br />
NAME VALUE<br />
----------------------------------------------------- ----------<br />
rollback changes - undo records applied 6087<br />
<br />
SQL> SELECT name, value FROM v$mystat NATURAL JOIN v$statname WHERE name = 'redo size';<br />
<br />
NAME VALUE<br />
----------------------------------------------------- ----------<br />
redo size 31320224</div><div style="background-color: #cccccc; font-family: "Courier New",Courier,monospace;"><br />
</div><div style="font-family: "Courier New",Courier,monospace;"><br />
</div><div style="font-family: "Courier New",Courier,monospace;">Session 2 :</div><div style="font-family: "Courier New",Courier,monospace;"><br />
</div><div style="background-color: #cccccc; font-family: "Courier New",Courier,monospace;">SQL> SET TIMING ON<br />
SQL> INSERT /*+ <span style="color: red;">APPEND </span>*/ INTO toto SELECT * FROM toto;<br />
<br />
800000 rows created.<br />
<br />
Elapsed: 00:00:12.06</div><div style="background-color: #cccccc; font-family: "Courier New",Courier,monospace;">SQL> ROLLBACK;<br />
<br />
Rollback complete.<br />
<br />
Elapsed: 00:00:00.00<br />
SQL> SELECT name, value FROM v$mystat NATURAL JOIN v$statname WHERE name = 'undo change vector size';<br />
<br />
NAME VALUE<br />
----------------------------------------------------- ----------<br />
undo change vector size 258516<br />
<br />
SQL> SELECT name, value FROM v$mystat NATURAL JOIN v$statname WHERE name = 'rollback changes - undo records applied';<br />
<br />
NAME VALUE<br />
----------------------------------------------------- ----------<br />
rollback changes - undo records applied 0<br />
<br />
SQL> SELECT name, value FROM v$mystat NATURAL JOIN v$statname WHERE name = 'redo size';<br />
<br />
NAME VALUE<br />
----------------------------------------------------- ----------<br />
redo size 25438660</div><div style="font-family: "Courier New",Courier,monospace;"><span style="background-color: #cccccc;"></span></div><div style="font-family: "Courier New",Courier,monospace;"><span style="background-color: #cccccc;"></span></div><div style="font-family: "Courier New",Courier,monospace;"><br />
<br />
Session 3 :</div><div style="font-family: "Courier New",Courier,monospace;"><br />
</div><div style="background-color: #cccccc; font-family: "Courier New",Courier,monospace;">SQL> SET TIMING ON<br />
SQL> INSERT /*+ <span style="color: red;">APPEND NOLOGGING</span> */ INTO toto SELECT * FROM toto;<br />
<br />
800000 rows created.<br />
<br />
Elapsed: 00:00:04.01</div><div style="background-color: #cccccc; font-family: "Courier New",Courier,monospace;">SQL> ROLLBACK;<br />
<br />
Rollback complete.<br />
<br />
Elapsed: 00:00:00.00<br />
SQL> SELECT name, value FROM v$mystat NATURAL JOIN v$statname WHERE name = 'undo change vector size';<br />
<br />
NAME VALUE<br />
----------------------------------------------------- ----------<br />
undo change vector size 208<br />
<br />
SQL> SELECT name, value FROM v$mystat NATURAL JOIN v$statname WHERE name = 'rollback changes - undo records applied';<br />
<br />
NAME VALUE<br />
----------------------------------------------------------------<br />
rollback changes - undo records applied 0<br />
<br />
SQL> SELECT name, value FROM v$mystat NATURAL JOIN v$statname WHERE name = 'redo size';<br />
<br />
NAME VALUE<br />
----------------------------------------------------- ----------<br />
redo size 24488552 <br />
<div style="background-color: white;"><br />
</div><div style="background-color: white;">en résumé :</div><div style="background-color: white;"><br />
</div><div style="background-color: white;">APPEND NOLOGGING INSERT(sec) UNDO_VECTOR REDO_SIZE</div><div style="background-color: white;"> N N 31 2270900 31320224</div><div style="background-color: white;"> <span style="color: red;">Y</span> N 12 258516 25438660</div><div style="background-color: white;"> <span style="color: red;">Y</span> <span style="color: red;">Y </span> 4 208 24488552</div><div style="background-color: white;"></div><div style="background-color: white;"><br />
</div><div style="background-color: white;"><br />
</div></div><div style="font-family: "Courier New",Courier,monospace;"></div>loranhttp://www.blogger.com/profile/16224793437590995506noreply@blogger.com0tag:blogger.com,1999:blog-2141579913468236718.post-73836712238969589292011-07-19T11:52:00.005+02:002011-07-19T15:37:05.738+02:00ENABLE ROW MOVEMENT : tables partitionnées<div style="font-family: "Courier New",Courier,monospace;">Oracle garantit qu'une ligne ne sera pas déplacée physiquement par un update et donc que son ROWID ne sera pas changé. Ce phénomène pourrait en effet survenir dans une table partitionnée avec un update sur la colonne de partitionnement, forçant Oracle à déplacer la ligne dans la bonne partition. (Déplacement physique, changement de ROWID).</div><div style="font-family: "Courier New",Courier,monospace;"></div><div style="font-family: "Courier New",Courier,monospace;">Exemple :</div><div style="font-family: "Courier New",Courier,monospace;"><br />
</div><div style="background-color: #cccccc; font-family: "Courier New",Courier,monospace;">SQL> CREATE TABLE toto(a NUMBER)<br />
2 PARTITION BY RANGE (a)(<br />
3 PARTITION p1 VALUES LESS THAN (10),<br />
4 PARTITION p2 VALUES LESS THAN (20),<br />
5 PARTITION p3 VALUES LESS THAN (MAXVALUE));<br />
<br />
Table created.<br />
<br />
SQL> INSERT INTO toto VALUES (5);<br />
<br />
1 row created.<br />
<br />
SQL> UPDATE toto SET a=15;<br />
UPDATE toto SET a=15<br />
*<br />
ERROR at line 1:<br />
<span style="color: red;">ORA-14402: updating partition key column would cause a partition change</span></div><div style="font-family: "Courier New",Courier,monospace;"><span style="background-color: #cccccc;"> </span></div><div style="font-family: "Courier New",Courier,monospace;"><span style="background-color: #cccccc;"> </span> </div><div style="font-family: "Courier New",Courier,monospace;">Pourquoi Oracle provoque-t'il cette erreur ?</div><div style="font-family: "Courier New",Courier,monospace;"><br />
</div><div style="font-family: "Courier New",Courier,monospace;">A priori on ne voit pas de raison pour laquelle on ne pourrait pas tranquillement mettre à jour une table, même sur la colonne de partitionnement, sans provoquer une erreur... Ceci est en fait un héritage, qu'Oracle nous a transmis pour que lors de l'introduction de tables partitionnées, les programmes qui utilisaient les ROWID n'aient pas de mauvaise surprise en partitionnant leurs tables. Le changement du ROWID lors d'un update pouvant être catastrophique avec ce genre de conception de programme.</div><div style="font-family: "Courier New",Courier,monospace;"><br />
</div><div style="font-family: "Courier New",Courier,monospace;">Mais comment faire alors ?</div><div style="font-family: "Courier New",Courier,monospace;"><br />
</div><div style="font-family: "Courier New",Courier,monospace;">Par défaut, donc, Oracle empêche les mises à jour des colonnes de partitionnement des tables partitionnées. Pour autoriser ces mises à jour, il est donc nécessaire de passer la commande <span style="color: red;">ENABLE ROW MOVEMENT</span> en préalable :</div><div style="font-family: "Courier New",Courier,monospace;"><br />
</div><div style="font-family: "Courier New",Courier,monospace;">Exemple :</div><div style="font-family: "Courier New",Courier,monospace;"><br />
</div><div style="background-color: #cccccc; font-family: "Courier New",Courier,monospace;">SQL> ALTER TABLE toto <span style="color: red;">ENABLE ROW MOVEMENT</span>;<br />
<br />
Table altered.<br />
<br />
SQL> UPDATE toto SET a=15;<br />
<br />
1 row updated.</div><div style="font-family: "Courier New",Courier,monospace;"><br />
</div><div style="font-family: "Courier New",Courier,monospace;">Bien qu'il existe la commande inverse qui rétablit l'impossibilité de mettre à jour ces colonnes (<span style="color: red;">DISABLE ROW MOVEMENT</span>), si aucun de vos programmes n'utilise les ROWID, il n'est pas nécessaire de repasser les tables en mode par défaut (disable).</div><div style="font-family: "Courier New",Courier,monospace;"><br />
</div><div style="font-family: "Courier New",Courier,monospace;">Voici tout de même la commande :</div><div style="font-family: "Courier New",Courier,monospace;"><br />
</div><div style="background-color: #cccccc; font-family: "Courier New",Courier,monospace;">SQL> ALTER TABLE toto <span style="color: red;">DISABLE ROW MOVEMENT</span>;<br />
<br />
Table altered.</div>loranhttp://www.blogger.com/profile/16224793437590995506noreply@blogger.com0tag:blogger.com,1999:blog-2141579913468236718.post-55924245491145489022011-07-12T18:17:00.000+02:002011-07-12T18:25:55.835+02:00VARIABLES : bind, substitution, programme<div style="font-family: "Courier New",Courier,monospace;">Dans cet exemple sont illustrés les emplois des différents types de variable suivants :</div><div style="font-family: "Courier New",Courier,monospace;"> </div><div style="font-family: "Courier New",Courier,monospace;">- variable de substitution (define) - interprétée par SqlPlus</div><div style="font-family: "Courier New",Courier,monospace;">- bind variable (variable) - SQL - PL/SQL<br />
- variable de programme (declare) - PL/SQL</div><div style="font-family: "Courier New",Courier,monospace;"></div><div style="font-family: "Courier New",Courier,monospace;"><br />
</div><div style="font-family: "Courier New",Courier,monospace;">Ces variables sont utilisées pour des raisons bien spécifiques, et ne doivent en aucun cas être confondues...</div><div style="font-family: "Courier New",Courier,monospace;"><br />
</div><div style="font-family: "Courier New",Courier,monospace;"><u>Exemple</u></div><div style="font-family: "Courier New",Courier,monospace;"> </div><div style="background-color: #cccccc; font-family: "Courier New",Courier,monospace;">SQL> SET VERIFY OFF<br />
SQL> <span style="color: red;">DEFINE </span>matable=dba_users;<br />
SQL> <span style="color: red;">VARIABLE </span>b NUMBER;<br />
SQL> <span style="color: red;">DECLARE</span><br />
2 res VARCHAR2(30);<br />
3<br />
4 BEGIN<br />
5 :b :=0;<br />
6 SELECT username INTO res FROM &matable WHERE user_id=:b;<br />
7 dbms_output.put_line('USER IS : '||res);<br />
8 END;<br />
9 /<br />
USER IS : SYS<br />
<br />
PL/SQL procedure successfully completed.</div><div style="font-family: "Courier New",Courier,monospace;"><br />
<br />
<u>Autre exemple pour une bind variable en SQL pur</u><br />
<br />
<div style="background-color: #cccccc;">SQL> <span style="color: red;">VARIABLE </span>b NUMBER;<br />
SQL> EXECUTE :b:=0;<br />
<br />
PL/SQL procedure successfully completed.<br />
<br />
SQL> SELECT username<br />
2 FROM all_users<br />
3 WHERE user_id = :b;<br />
<br />
USERNAME<br />
------------------------------<br />
SYS</div><br />
<br />
</div>loranhttp://www.blogger.com/profile/16224793437590995506noreply@blogger.com0tag:blogger.com,1999:blog-2141579913468236718.post-60504911829113772682011-07-12T18:12:00.002+02:002011-07-27T18:07:47.488+02:00OUT : valeur de retour d'une procedure<div style="background-color: white; font-family: "Courier New",Courier,monospace;">Les procédures Oracle offrent la possibilité de renvoyer une valeur en sortie au travers d'une variable <span style="color: red;">OUT</span> définie dans la liste des paramètres de la procédure.</div><div style="background-color: white; font-family: "Courier New",Courier,monospace;"><br />
</div><div style="background-color: #cccccc; font-family: "Courier New",Courier,monospace;">CREATE OR REPLACE PROCEDURE p_sqrt(<br />
a IN NUMBER, b <span style="color: red;">OUT </span>NUMBER<br />
) AS<br />
BEGIN<br />
b:=a*a;<br />
END;<br />
/<br />
<br />
SET SERVEROUTPUT ON SIZE 2000<br />
<br />
DECLARE<br />
entree NUMBER;<br />
sortie NUMBER;<br />
BEGIN<br />
entree:=9;<br />
p_sqrt(entree,sortie);<br />
dbms_output.put_line(entree||'² = '||sortie);<br />
END;<br />
/</div><div style="background-color: #cccccc; font-family: "Courier New",Courier,monospace;"><span style="color: red;">9² = 81</span><br />
<br />
PL/SQL procedure successfully completed.</div>loranhttp://www.blogger.com/profile/16224793437590995506noreply@blogger.com1tag:blogger.com,1999:blog-2141579913468236718.post-6610998133178391172011-07-12T16:34:00.000+02:002011-07-12T18:30:24.891+02:00CUBE : requête analytique<div style="background-color: white; font-family: "Courier New",Courier,monospace;">Il existe de nombreuses fonctions analytiques permettant d'obtenir des rapports multi-dimensionnels plus ou moins complexes.</div><div style="background-color: white; font-family: "Courier New",Courier,monospace;"><br />
</div><div style="background-color: white; font-family: "Courier New",Courier,monospace;"><u>Exemple de Cube :</u></div><div style="background-color: white; font-family: "Courier New",Courier,monospace;"><br />
</div><div style="background-color: #cccccc; font-family: "Courier New",Courier,monospace;"></div><div style="background-color: #cccccc; font-family: "Courier New",Courier,monospace;">SET LINES 150<br />
SET PAGES 30<br />
SET FEED OFF<br />
SET ESCAPE \<br />
<br />
BREAK ON typ SKIP 1 ON tbs SKIP 0<br />
COL tbs FOR A10 TRUNC<br />
<br />
SELECT<br />
NVL(t.typ,'ALL') AS typ,<br />
NVL(t.tablespace_name,'________') AS tbs,<br />
ROUND(SUM(t.bytes)/1024/1024,0) AS siz_mo<br />
FROM (<br />
SELECT<br />
(CASE <br />
WHEN s.segment_type LIKE 'TAB%' THEN 'TAB'<br />
WHEN s.segment_type LIKE 'IND%' THEN 'IND'<br />
ELSE 'OTH'<br />
END<br />
) AS typ,<br />
s.tablespace_name,<br />
s.bytes<br />
FROM dba_segments S<br />
WHERE owner='SYS'<br />
) T<br />
<span style="color: red;">GROUP BY CUBE</span>(t.typ, t.tablespace_name)<br />
ORDER BY 1 DESC, 2 DESC<br />
/<br />
<br />
SET FEED ON<br />
SET ESCAPE OFF</div><div style="font-family: "Courier New",Courier,monospace;"></div><div style="font-family: "Courier New",Courier,monospace;"></div><div style="font-family: "Courier New",Courier,monospace;"> <br />
<br />
<u>Résultat :</u><br />
<u><br />
</u></div><div style="font-family: "Courier New",Courier,monospace;"></div><div style="background-color: #cccccc; font-family: "Courier New",Courier,monospace;">TYP TBS SIZ_MO<br />
--- ---------- ----------<br />
TAB TOOLS 23<br />
SYSTEM 402<br />
SYSAUX 205<br />
________ 629<br />
<br />
OTH UNDOTBS 52<br />
SYSTEM 218<br />
SYSAUX 21<br />
________ 291<br />
<br />
IND TOOLS 0<br />
SYSTEM 370<br />
SYSAUX 283<br />
________ 654<br />
<br />
ALL UNDOTBS 52<br />
TOOLS 23<br />
SYSTEM 990<br />
SYSAUX 508<br />
________ 1573</div><div style="background-color: #cccccc; font-family: "Courier New",Courier,monospace;"></div>loranhttp://www.blogger.com/profile/16224793437590995506noreply@blogger.com0tag:blogger.com,1999:blog-2141579913468236718.post-10007122071365460002011-07-12T15:41:00.000+02:002011-07-12T17:25:53.321+02:00ORA_HASH : fonction de hachage<div style="font-family: "Courier New",Courier,monospace;">La fonction de hachage <span style="color: red;">ORA_HASH</span> introduite en 10G, permet de répartir des valeurs en entrée vers une sortie homogène. C'est cette même fonction qui est utilisée par Oracle pour répartir les valeurs d'une colonne dans une table partitionnée. (Voir exemple ci-dessous).</div><div style="font-family: "Courier New",Courier,monospace;"><br />
</div><div style="font-family: "Courier New",Courier,monospace;">Cette fonction a deux ou trois arguments</div><div style="font-family: "Courier New",Courier,monospace;"><br />
</div><div style="font-family: "Courier New",Courier,monospace;">select <b>ORA_HASH(expr, max_bucket, seed_value</b><b>)</b> from DUAL;</div><div style="font-family: "Courier New",Courier,monospace;"> </div><div style="font-family: "Courier New",Courier,monospace;"><b>expr </b>: expression sur laquelle on applique le hachage</div><div style="font-family: "Courier New",Courier,monospace;"> </div><div style="font-family: "Courier New",Courier,monospace;"><b>max_bucket</b> : numéro maximal de sortie de fonction en partant de 0</div><div style="font-family: "Courier New",Courier,monospace;"> </div><div style="font-family: "Courier New",Courier,monospace;"><b>seed_value</b> (otionnelle) : valeurs prise en compte dans la fonction de hachage pour permettre une variation des résultats avec une même expression en entrée (par défaut seed_value = 0).</div><div style="font-family: "Courier New",Courier,monospace;"><br />
</div><div style="font-family: "Courier New",Courier,monospace;"> </div><div style="background-color: #cccccc; font-family: "Courier New",Courier,monospace;">SQL> CREATE TABLE test PARTITION BY <span style="color: red;">HASH </span>(a) (<br />
2 PARTITION p1,<br />
3 PARTITION p2,<br />
4 PARTITION p3,<br />
5 PARTITION p4<br />
6 )<br />
7 AS<br />
8 SELECT ROWNUM AS a<br />
9 FROM ALL_OBJECTS<br />
10 WHERE ROWNUM < 1000<br />
11 /<br />
<br />
Table created.<br />
<br />
SQL> SELECT COUNT(*) FROM test PARTITION (p1)<br />
2 WHERE <span style="background-color: #cccccc; color: red;">ORA_HASH</span>(a,3)+1<>1;<br />
<br />
COUNT(*)<br />
----------<br />
0<br />
<br />
SQL> SELECT COUNT(*) FROM test PARTITION (p2)<br />
2 WHERE <span style="color: red;">ORA_HASH</span>(a,3)+1<>2;<br />
<br />
COUNT(*)<br />
----------<br />
0<br />
<br />
SQL> SELECT COUNT(*) FROM test PARTITION (p3)<br />
2 WHERE <span style="color: red;">ORA_HASH</span>(a,3)+1<>3;<br />
<br />
COUNT(*)<br />
----------<br />
0<br />
<br />
SQL> SELECT COUNT(*) FROM test PARTITION (p4)<br />
2 WHERE <span style="color: red;">ORA_HASH</span>(a,3)+1<>4;<br />
<br />
COUNT(*)<br />
----------<br />
0</div><div style="font-family: "Courier New",Courier,monospace;"><span style="background-color: #cccccc;"><br />
</span></div><div style="font-family: "Courier New",Courier,monospace;"><br />
</div>loranhttp://www.blogger.com/profile/16224793437590995506noreply@blogger.com0tag:blogger.com,1999:blog-2141579913468236718.post-4509832534735141202011-07-12T15:22:00.000+02:002011-07-12T17:27:22.347+02:00CASE : plusieurs COUNT en une seule lecture<div style="background-color: #cccccc; font-family: "Courier New",Courier,monospace;"></div><div style="background-color: #cccccc; font-family: "Courier New",Courier,monospace;"></div><div style="background-color: white; font-family: "Courier New",Courier,monospace;">Si possible regrouper les COUNT sur une même table dans la même requête via un CASE, ce qui vous évitera de lire plusieurs fois la table en FULL.<br />
</div><div style="background-color: white; font-family: "Courier New",Courier,monospace;"></div><div style="background-color: white; font-family: "Courier New",Courier,monospace;"><u>Exemple</u> </div><div style="background-color: white; font-family: "Courier New",Courier,monospace;"> </div><div style="background-color: #cccccc; font-family: "Courier New",Courier,monospace;">SQL> CREATE TABLE t1 AS<br />
2 SELECT ora_hash(ROWNUM,1000) PRICE<br />
3 FROM ALL_OBJECTS<br />
4 WHERE ROWNUM < 1000;<br />
<br />
Table created.<br />
<br />
SQL> SELECT <span style="color: red;">COUNT </span>(*) FROM t1<br />
2 WHERE price < 100 ;<br />
<br />
COUNT(*)<br />
----------<br />
104<br />
<br />
SQL> SELECT <span style="color: red;">COUNT </span>(*) FROM t1<br />
2 WHERE price BETWEEN 100 AND 900 ;<br />
<br />
COUNT(*)<br />
----------<br />
799<br />
<br />
SQL> SELECT <span style="color: red;">COUNT </span>(*) FROM t1<br />
2 WHERE price > 900 ;<br />
<br />
COUNT(*)<br />
----------<br />
96<br />
<br />
SQL> SET AUTOTRACE ON EXPLAIN<br />
SQL> SELECT<br />
2 <span style="color: red;">COUNT </span>(<span style="color: red;">CASE </span>WHEN price < 100<br />
3 THEN 1 ELSE null END) count1,<br />
4 <span style="color: red;">COUNT </span>(<span style="color: red;">CASE </span>WHEN price BETWEEN 100 AND 900<br />
5 THEN 1 ELSE null END) count2,<br />
6 <span style="color: red;">COUNT </span>(<span style="color: red;">CASE </span>WHEN price > 900<br />
7 THEN 1 ELSE null END) count3<br />
8 FROM t1;<br />
<br />
COUNT1 COUNT2 COUNT3<br />
---------- ---------- ----------<br />
104 799 96<br />
<br />
<br />
Execution Plan<br />
----------------------------------------------------------<br />
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=1 Bytes=13)<br />
1 0 SORT (AGGREGATE)<br />
2 1 TABLE ACCESS (FULL) OF 'T1' (TABLE) (Cost=3 Card=999 Byt<br />
es=12987)<br />
<br />
<br />
SQL> SET AUTOTRACE OFF</div><div style="font-family: "Courier New",Courier,monospace;"><span style="background-color: #cccccc;"></span></div><div style="font-family: "Courier New",Courier,monospace;"><span style="background-color: #cccccc;"></span></div>loranhttp://www.blogger.com/profile/16224793437590995506noreply@blogger.com0tag:blogger.com,1999:blog-2141579913468236718.post-62245077628596441962011-07-12T14:34:00.001+02:002011-08-02T15:51:14.279+02:00COUNT : passage par un index ou pas<div style="background-color: white; font-family: "Courier New",Courier,monospace;">Le <span style="background-color: white; color: red;">COUNT</span>(*) renvoie le nombre total de lignes de la table. Il ne peut donc utiliser qu'un index Bitmap. Par contre il peut utiliser n'importe quel index Bitmap (car pour chaque valeur du Bitmap, on a une image de toutes les lignes - donc des lignes a valeur nulle).</div><div style="background-color: white; font-family: "Courier New",Courier,monospace;"></div><div style="background-color: white; font-family: "Courier New",Courier,monospace;"></div><div style="background-color: white; font-family: "Courier New",Courier,monospace;">Si la colonne est NOT NULL, alors toutes les valeurs de cette colonne seront renseignées, donc un index B*Tree indexera lui aussi toutes les lignes de la table, il pourra donc être utilisé pour le <span style="color: red;">COUNT</span>(*) dans ce cas uniquement.<br />
<br />
</div><div style="background-color: white; font-family: "Courier New",Courier,monospace;">Le <span style="color: red;">COUNT</span>(colonne) renvoie le nombre de lignes ayant une valeur non nulle pour la colonne. Il peut donc passer par un index B*Tree (et Bitmap) pour évaluer le résultat.<br />
<br />
Le <span style="color: red;">COUNT</span>(n) est équivalent au <span style="color: red;">COUNT</span>(*) mais la méthode recommandée est le <span style="color: red;">COUNT</span>(*).<br />
<br />
<br />
<u>Exemples</u> </div><div style="background-color: white; font-family: "Courier New",Courier,monospace;"><br />
</div><div style="background-color: #cccccc; font-family: "Courier New",Courier,monospace;">SQL> CREATE TABLE t1 (a NUMBER);<br />
<br />
Table created.<br />
<br />
SQL> INSERT INTO t1 VALUES (1);<br />
<br />
1 row created.<br />
<br />
SQL> INSERT INTO t1 VALUES (null);<br />
<br />
1 row created.</div><div style="background-color: #cccccc; font-family: "Courier New",Courier,monospace;"></div><div style="background-color: #cccccc; font-family: "Courier New",Courier,monospace;"><br />
SQL> INSERT INTO t1 VALUES (3);<br />
<br />
1 row created.<br />
<br />
SQL> SELECT * FROM T1;<br />
<br />
A<br />
----------<br />
1<br />
<br />
3<br />
<div style="background-color: white;"><br />
</div><div style="background-color: white;"> </div><div style="background-color: white;"><u>Exemple d'utilisation d'index B*Tree avec des NULLs</u><br />
<u><br />
</u></div><div style="background-color: white;"><u> </u></div><br />
SQL> CREATE INDEX i_t1 ON t1 (a);<br />
<br />
Index created.<br />
<br />
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER,'t1',no_invalidate=>FALSE);<br />
<br />
PL/SQL procedure successfully completed.<br />
<br />
SQL> SET AUTOTRACE ON EXPLAIN<br />
SQL> SELECT COUNT(*) FROM T1;<br />
<br />
COUNT(*)<br />
----------<br />
3<br />
<br />
<br />
Execution Plan<br />
----------------------------------------------------------<br />
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=1)<br />
1 0 SORT (AGGREGATE)<br />
2 1 TABLE ACCESS (FULL) OF 'T1' (TABLE) (Cost=3 Card=3)<br />
<br />
<br />
<br />
SQL> SELECT COUNT(1) FROM T1;<br />
<br />
COUNT(1)<br />
----------<br />
3<br />
<br />
<br />
Execution Plan<br />
----------------------------------------------------------<br />
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=1)<br />
1 0 SORT (AGGREGATE)<br />
2 1 TABLE ACCESS (FULL) OF 'T1' (TABLE) (Cost=3 Card=3)<br />
<br />
<br />
Dans les deux exemples précédents, l'index B*Tree ne peut être utilisé car il n'indexe pas la totalité des lignes de la table (valeurs nulles exclues).<br />
<br />
SQL> SELECT COUNT(a) FROM T1;<br />
<br />
COUNT(A)<br />
----------<br />
2<br />
<br />
<br />
Execution Plan<br />
----------------------------------------------------------<br />
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=1 Card=1 Bytes=13)<br />
1 0 SORT (AGGREGATE)<br />
2 1 INDEX (FULL SCAN) OF 'I_T1' (INDEX) (Cost=1 Card=3 Bytes<br />
=39)</div><div style="background-color: #cccccc; font-family: "Courier New",Courier,monospace;"><br />
Le count(colonne) décompte toutes les lignes ayant une valeur non nulle de cette colonne, donc autorise le passage par un index B*Tree </div><div style="font-family: "Courier New",Courier,monospace;"><br />
<br />
<u>Exemple d'utilisation d'index Bitmap avec des NULLs</u><br />
<br />
<div style="background-color: #cccccc;">SQL> DROP INDEX i_t1;<br />
<br />
Index dropped.<br />
<br />
SQL> CREATE BITMAP INDEX i_t1 ON t1 (a);<br />
<br />
Index created.<br />
<br />
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER,'t1',no_invalidate=>FALSE);<br />
<br />
PL/SQL procedure successfully completed.<br />
<br />
SQL> SET AUTOTRACE ON EXPLAIN<br />
SQL> SELECT COUNT(*) FROM t1;<br />
<br />
COUNT(*)<br />
----------<br />
3<br />
<br />
<br />
Execution Plan<br />
----------------------------------------------------------<br />
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=1 Card=1)<br />
1 0 SORT (AGGREGATE)<br />
2 1 BITMAP CONVERSION (COUNT) (Cost=1 Card=3)<br />
3 2 BITMAP INDEX (FAST FULL SCAN) OF 'I_T1' (INDEX (BITMAP<br />
))<br />
<br />
<br />
L'index Bitmap enregistre une image bitmap de toutes les lignes de la table pour une colonne donnée (si index mono colonne), et donc index les valeurs nulles. Par conséquent le count(*) qui décompte toutes les lignes de la table, peut utiliser cet index.<br />
<br />
SQL> SELECT COUNT(a) FROM t1;<br />
<br />
COUNT(A)<br />
----------<br />
2<br />
<br />
<br />
Execution Plan<br />
----------------------------------------------------------<br />
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=1 Card=1 Bytes=2)<br />
1 0 SORT (AGGREGATE)<br />
2 1 BITMAP CONVERSION (TO ROWIDS) (Cost=1 Card=3 Bytes=6)<br />
3 2 BITMAP INDEX (FAST FULL SCAN) OF 'I_T1' (INDEX (BITMAP<br />
))<br />
<br />
SQL> SET AUTOTRACE OFF</div><br />
<br />
<u>Exemple d'utilisation d'un index B*Tree avec contrainte NOT NULL</u><br />
<br />
<div style="background-color: #cccccc;"><br />
</div><div style="background-color: #cccccc;">SQL> DROP INDEX i_t1;<br />
<br />
Index dropped.<br />
<br />
SQL> CREATE INDEX i_t1 ON t1 (a);<br />
<br />
Index created.<br />
<br />
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER,'t1',no_invalidate=>FALSE);<br />
<br />
PL/SQL procedure successfully completed.<br />
<br />
SQL> DELETE FROM t1 WHERE a IS NULL;<br />
<br />
1 row deleted.<br />
<br />
SQL> SET AUTOTRACE ON EXPLAIN<br />
SQL> SELECT COUNT(*) FROM t1;<br />
<br />
COUNT(*)<br />
----------<br />
2<br />
<br />
<br />
Execution Plan<br />
----------------------------------------------------------<br />
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=1)<br />
1 0 SORT (AGGREGATE)<br />
2 1 TABLE ACCESS (FULL) OF 'T1' (TABLE) (Cost=3 Card=3)<br />
<br />
<br />
<br />
SQL> SET AUTOTRACE OFF<br />
SQL> ALTER TABLE t1 MODIFY a NOT NULL;<br />
<br />
Table altered.<br />
<br />
SQL> SET AUTOTRACE ON EXPLAIN<br />
SQL> SELECT COUNT(*) FROM t1;<br />
<br />
COUNT(*)<br />
----------<br />
2<br />
<br />
<br />
Execution Plan<br />
----------------------------------------------------------<br />
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=1 Card=1)<br />
1 0 SORT (AGGREGATE)<br />
2 1 INDEX (FULL SCAN) OF 'I_T1' (INDEX) (Cost=1 Card=3)<br />
<br />
<br />
<br />
SQL> SET AUTOTRACE OFF</div><br />
Cette fois l'index B*Tree est bien utilisé pour le count(*)</div>loranhttp://www.blogger.com/profile/16224793437590995506noreply@blogger.com0