Re: Performance issue with dbms_metadata.get_ddl

From: Brent Day <coloradodba_at_gmail.com>
Date: Tue, 16 Oct 2012 09:48:28 -0600
Message-ID: <CAEz8shwF6mvMQS+MP=5ZH7312q+vv4zBzf6jWkq=4tz74G428g_at_mail.gmail.com>

In the future I would recommend you provide more information about your environment, some sample output/timings and what you consider too slow. Without this information you may not get a response. I took your code and ran it in a fairly large development system and for my test schema it generated 287 rows in 6 seconds.

Thans,
Brent

On Tue, Oct 16, 2012 at 8:08 AM, Eriovaldo Andrietta <ecandrietta_at_gmail.com>wrote:

> Hi Friends,
> I am running the pl/sql block below and it is too slow.
>
> This query returns only 266 lines.
>
> SELECT index_name
> FROM user_indexes
> WHERE index_type = 'DOMAIN'
>
> Any idea ?
>
>
> -- start of script
>
> DECLARE
> vddl VARCHAR2(4000);
> vobjname VARCHAR2(30);
> vsql VARCHAR2(400);
> vstring VARCHAR2(4000);
> vwhat VARCHAR2(200);
> vinterval VARCHAR2(4000);
> BEGIN
> DELETE FROM TMP_EXP_TBD_SSG;
> DBMS_METADATA.SET_TRANSFORM_PARAM( DBMS_METADATA.SESSION_TRANSFORM,
> 'PRETTY', TRUE );
> DBMS_METADATA.SET_TRANSFORM_PARAM( DBMS_METADATA.SESSION_TRANSFORM,
> 'SQLTERMINATOR', true );
>
> FOR l IN (SELECT index_name
> FROM user_indexes
> WHERE index_type = 'DOMAIN')
> LOOP
> SELECT dbms_metadata.get_ddl('INDEX', l.index_name)
> INTO vddl
> FROM dual;
>
> INSERT INTO TMP_EXP_TBD_SSG (TYPE, STATMENT)
> VALUES (1, VDDL);
>
> COMMIT;
>
> END LOOP;
>
> END;
>
> /
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

--
http://www.freelists.org/webpage/oracle-l

Received on Tue Oct 16 2012 - 17:48:28 CEST