Gestion des OUTLINEs sous ORACLE

1

Rappel

Les outlines sont des directives passées à l’optimizer sous forme de hint afin de fixer – avec plus ou moins de réussite – les plans d’exécution.

 

Objectif

Obtenir des informations sur les outlines sur une base.

 

Problème

La requête SQL sur laquelle a été définie la outline apparait dans les vues comme un champ LONG ce qui ne facilite pas la consultation.

On ne peut notamment pas faire de substr() sur un champ LONG.

 

SQL> select substr(SQL_TEXT,1,100) from DBA_OUTLINES;

select substr(SQL_TEXT,1,100) from DBA_OUTLINES

              *

ERROR at line 1:

ORA-00932: inconsistent datatypes: expected NUMBER got LONG

 

SQL> select substr(to_char(SQL_TEXT),1,100) from DBA_OUTLINES;

select substr(to_char(SQL_TEXT),1,100) from DBA_OUTLINES

              *

ERROR at line 1:

ORA-00932: inconsistent datatypes: expected CHAR got LONG

 

 

Exemple de listing :

 

Stored Ouline.......: OUTLN_MSG_POSTING_1

Category............: APPLI

Creator / Owner.....: user/ pass

hash_value..........: 2014187508

hash_value2.........: 4144314494

Enabled.............: Yes

Used................: Yes

Creation date.......: 28/10/2009 16:07:25

Hint count..........: 44

Sql Text Length.....: 1210

______________SQL TEXT EXTRACT________________________________

select ID,ACCOUNT_ENTITY_ID,POSTING_TYPE,POSTING_RULE_ID,TRADE_ID,VERSION_ID,GENERATION_DATE-TO_DATE('01/01/1904','DD/MM/YYYY'),POSTING_DATE-TO_DATE('

01/01/1904','DD/MM/YYYY'),ACCOUNT_NUMBER,ACCOUNT_CURRENCY,AMOUNT,CREDIT_DEBIT,THIRD_PARTY_ID,INSTRUMENT_ID,CURRENCY,QUANTITY,SIGN,AUXILIARY1_ACCOUNT,AUXILIARY2_ACCOUNT,AUXILIARY3_ACCOUNT,JOURNAL_ENTRY

,COMMENTS,STATUS,AUXILIARY_DATE-TO_DATE('01/01/1904','DD/MM/YYYY'),ACCOUNT_NAME_ID,AUXILIARY1_ACCOUN

UXILIARY2_ACCOUNT,AUXILIARY3_ACCOUNT,JOURNAL_ENTRY,COMMENTS,STATUS,AUXILIARY_DATE-TO_DATE('01/01/1904','DD/MM/YYYY'),ACCOUNT_NAME_ID,AUXILIARY1_ACCOUNT_ID,AUXILIARY2_ACCOUNT_ID,AUXILIARY3_ACCOUNT_ID,L

INK_ID,RULE_TYPE,POSITION_ID,AUXILIARY_ID,DATENEG-TO_DATE('01/01/1904','DD/MM/YYYY'),HEURENEG from MSG_POSTING where POSTING_DATE-TO_DATE('01-01-1904','DD-MM-YYYY')<=:c1 and (POSTING_TYPE=:c2 or

POSTING_TYPE=:c3 or POSTING_TYPE=:c4 or POSTING_TYPE=:c5

SG_POSTING where POSTING_DATE-TO_DATE('01-01-1904','DD-MM-YYYY')<=:c1 and (POSTING_TYPE=:c2 or POSTING_TYPE=:c3 or POSTING_TYPE=:c4 or POSTING_TYPE=:c5 or POSTING_TYPE=:c6) and (STATUS=:c7 or

STATUS=:c8 or STATUS=:c9) and ACCOUNT_ENTITY_ID=:c10 and ACCOUNT_CURRENCY=:c11 and INSTRUMENT_ID=:c12 and AUXILIARY1_ACCOUNT=:c13 and AUXILIARY2_ACCOUNT=:c14 and AUXILIARY1_ACCOUNT_ID=:c15 and

AUXILIARY2_ACCOUNT_ID=:c16 and (AUXILIARY3_ACCOUNT=:c17 or  AUXILIARY3_ACCOUNT is NULL ) and (AUXILIARY3_ACCOUNT_ID=:c18 or  AUXILIARY3_ACCOUNT_ID is NULL ) order by AUXILIARY_ID, DATENEG, HEURENEG,

POSTING_DATE, TRADE_ID, ID

XILIARY1_ACCOUNT=:c13 and AUXILIARY2_ACCOUNT=:c14 and AUXILIARY1_ACCOUNT_ID=:c15 and AUXILIARY2_ACCOUNT_ID=:c16 and (AUXILIARY3_ACCOUNT=:c17 or  AUXILIARY3_ACCOUNT is NULL ) and

(AUXILIARY3_ACCOUNT_ID=:c18 or  AUXILIARY3_ACCOUNT_ID is NULL ) order by AUXILIARY_ID, DATENEG, HEURENEG, POSTING_DATE, TRADE_ID, ID

______________END OF SQL TEXT EXTRACT_________________________

 

 

 

 

Stored Ouline.......: OUTLN_MSG_POSTING_2

Category............: APPLI

Creator / Owner.....: user / pass

hash_value..........: 3171669409

hash_value2.........: 937014734

Enabled.............: Yes

Used................: Yes

Creation date.......: 28/10/2009 16:07:34

Hint count..........: 44

Sql Text Length.....: 1167

______________SQL TEXT EXTRACT________________________________

select ID,ACCOUNT_ENTITY_ID,POSTING_TYPE,POSTING_RULE_ID,TRADE_ID,VERSION_ID,GENERATION_DATE-TO_DATE('01/01/1904','DD/MM/YYYY'),POSTING_DATE-TO_DATE('

01/01/1904','DD/MM/YYYY'),ACCOUNT_NUMBER,ACCOUNT_CURRENCY,AMOUNT,CREDIT_DEBIT,THIRD_PARTY_ID,INSTRUMENT_ID,CURRENCY,QUANTITY,SIGN,AUXILIARY1_ACCOUNT,AUXILIARY2_ACCOUNT,AUXILIARY3_ACCOUNT,JOURNAL_ENTRY

,COMMENTS,STATUS,AUXILIARY_DATE-TO_DATE('01/01/1904','DD/MM/YYYY'),ACCOUNT_NAME_ID,AUXILIARY1_ACCOUN

UXILIARY2_ACCOUNT,AUXILIARY3_ACCOUNT,JOURNAL_ENTRY,COMMENTS,STATUS,AUXILIARY_DATE-TO_DATE('01/01/1904','DD/MM/YYYY'),ACCOUNT_NAME_ID,AUXILIARY1_ACCOUNT_ID,AUXILIARY2_ACCOUNT_ID,AUXILIARY3_ACCOUNT_ID,L

INK_ID,RULE_TYPE,POSITION_ID,AUXILIARY_ID,DATENEG-TO_DATE('01/01/1904','DD/MM/YYYY'),HEURENEG from MSG_POSTING where POSTING_DATE-TO_DATE('01-01-1904','DD-MM-YYYY')<=:c1 and (POSTING_TYPE=:c2 or

POSTING_TYPE=:c3) and (STATUS=:c4 or STATUS=:c5) and ACC

SG_POSTING where POSTING_DATE-TO_DATE('01-01-1904','DD-MM-YYYY')<=:c1 and (POSTING_TYPE=:c2 or POSTING_TYPE=:c3) and (STATUS=:c4 or STATUS=:c5) and ACCOUNT_ENTITY_ID=:c6 and ACCOUNT_NUMBER=:c7 and

ACCOUNT_NAME_ID=:c8 and ACCOUNT_CURRENCY=:c9 and INSTRUMENT_ID=:c10 and AUXILIARY1_ACCOUNT=:c11 and AUXILIARY2_ACCOUNT=:c12 and AUXILIARY1_ACCOUNT_ID=:c13 and AUXILIARY2_ACCOUNT_ID=:c14 and

(AUXILIARY3_ACCOUNT=:c15 or  AUXILIARY3_ACCOUNT is NULL ) and (AUXILIARY3_ACCOUNT_ID=:c16 or  AUXILIARY3_ACCOUNT_ID is NULL ) order by DATENEG, HEURENEG, POSTING_DATE, TRADE_ID, ID

ILIARY2_ACCOUNT=:c12 and AUXILIARY1_ACCOUNT_ID=:c13 and AUXILIARY2_ACCOUNT_ID=:c14 and (AUXILIARY3_ACCOUNT=:c15 or  AUXILIARY3_ACCOUNT is NULL ) and (AUXILIARY3_ACCOUNT_ID=:c16 or

AUXILIARY3_ACCOUNT_ID is NULL ) order by DATENEG, HEURENEG, POSTING_DATE, TRADE_ID, ID

______________END OF SQL TEXT EXTRACT_________________________

 

Code PL/SQL

set linesize 200

set serveroutput on size 100000

alter session set nls_date_format='DD/MM/YYYY HH24:MI:SS';

 

DECLARE

 

    CURSOR c_ol

    IS  SELECT   a.OL_NAME

                ,a.HASH_VALUE

                ,a.HASH_VALUE2

                ,a.CATEGORY

                ,a.SQL_TEXT

                ,a.CREATOR

                ,a.TEXTLEN

                ,a.HINTCOUNT

                ,b.USED

                ,b.ENABLED

                ,b.OWNER

                ,b.TIMESTAMP as TIM

        FROM    OUTLN.OL$ a,

                DBA_OUTLINES b

        WHERE   a.OL_NAME = b.NAME;

 

 

    TMP1 VARCHAR2(30);

 

BEGIN

    FOR v_ol IN c_ol

    LOOP

        DBMS_OUTPUT.PUT_LINE('Stored Ouline.......: ' || v_ol.OL_NAME);

        DBMS_OUTPUT.PUT_LINE('Category............: ' || v_ol.CATEGORY);

        DBMS_OUTPUT.PUT_LINE('Creator / Owner.....: ' || v_ol.CREATOR || ' / ' || v_ol.OWNER );

        DBMS_OUTPUT.PUT_LINE('hash_value..........: ' || v_ol.HASH_VALUE);

        DBMS_OUTPUT.PUT_LINE('hash_value2.........: ' || v_ol.HASH_VALUE2);

        select decode(v_ol.ENABLED,'ENABLED','Yes','DISABLED','No') into TMP1 from dual;

        DBMS_OUTPUT.PUT_LINE('Enabled.............: ' || TMP1);

        select decode(v_ol.USED   ,'USED'   ,'Yes','UNUSED'  ,'No') into TMP1 from dual;

        DBMS_OUTPUT.PUT_LINE('Used................: ' || TMP1);

        DBMS_OUTPUT.PUT_LINE('Creation date.......: ' || v_ol.TIM);

        DBMS_OUTPUT.PUT_LINE('Hint count..........: ' || v_ol.HINTCOUNT);

        DBMS_OUTPUT.PUT_LINE('Sql Text Length.....: ' || v_ol.TEXTLEN);

        DBMS_OUTPUT.PUT_LINE('______________SQL TEXT EXTRACT________________________________');

        DBMS_OUTPUT.PUT_LINE(substr(v_ol.SQL_TEXT,  1, 150));

        DBMS_OUTPUT.PUT_LINE(substr(v_ol.SQL_TEXT,151, 300));

        DBMS_OUTPUT.PUT_LINE(substr(v_ol.SQL_TEXT,301, 451));

        DBMS_OUTPUT.PUT_LINE(substr(v_ol.SQL_TEXT,601, 751));

        DBMS_OUTPUT.PUT_LINE(substr(v_ol.SQL_TEXT,901,1051));

        DBMS_OUTPUT.PUT_LINE('______________END OF SQL TEXT EXTRACT_________________________');

        DBMS_OUTPUT.PUT_LINE(chr(10));

        DBMS_OUTPUT.PUT_LINE(chr(10));

    END LOOP;

END;

/