Re: Performance issue with dbms_metadata.get_ddl

From: Eriovaldo Andrietta <ecandrietta_at_gmail.com>
Date: Tue, 16 Oct 2012 12:56:13 -0300
Message-ID: <CAJdDhaNsKM1PojdGCuxsqanPKAJqELKYEcA7kZj_K0q8ASNpxA_at_mail.gmail.com>

Ok Brent.
You are right.
Now I have parameter to compare. :)

The same script in my environment takes 4528 seconds. Very, very strange and slow.
Thanks for giving your time. (6 seconds) I also understand that it is not normal.

I need to investigate when running what is waiting at this time. But, I think that the dictionary of instance is the root cause of this delay.
If I have news, I will post here to increase our experience.

Best regards
Eriovaldo

On Tue, Oct 16, 2012 at 12:48 PM, Brent Day <coloradodba_at_gmail.com> wrote:

> 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:56:13 CEST