Re: Performance issue with dbms_metadata.get_ddl

From: Tim Gorman <tim_at_evdbt.com>
Date: Tue, 16 Oct 2012 10:15:12 -0600
Message-ID: <507D8810.7030708_at_evdbt.com>

Eriovaldo,
Why not obtain a 10046 SQL trace and have no doubt about what it taking the most time? Why guess?

Please execute the following prior to running the PL/SQL...

    alter session set tracefile_identifier = eriovaldo;     alter session set statistics_level = all;     alter session set max_dump_file_size = unlimited;     alter session set events '10046 trace name context forever, level 12';

Then, after the script completes, completely exit the program back to the operating system, go to the USER_DUMP_DEST directory on the database server, and run TKPROF against the resulting trace file. Alternatively, use TRCANLYZR package (downloadable from MOS) or obtain Hotsos/Method-R profiler product from either www.hotsos.com or www.method-r.com.

Don't guess. Trace it.

Hope this helps...

-- 
Tim Gorman
consultant -> Evergreen Database Technologies, Inc.
postal     => PO Box 352151, Westminster CO 80035
website    => http://www.EvDBT.com/
email      => Tim_at_EvDBT.com
mobile     => +1-303-885-4526
fax        => +1-303-484-3608
Lost Data? => http://www.ora600.be/ for info about DUDE...




On 10/16/2012 9:56 AM, Eriovaldo Andrietta wrote:


> 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



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

Received on Tue Oct 16 2012 - 18:15:12 CEST