Re: Performance issue with dbms_metadata.get_ddl
- From: Tim Gorman <tim@xxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Tue, 16 Oct 2012 10:15:12 -0600
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@xxxxxxxxx
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@xxxxxxxxx> 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@xxxxxxxxx> 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;
>>>
>>> /
>>>
>>>
>>> --
>>> //www.freelists.org/webpage/oracle-l
>>>
>>>
>>>
>
> --
> //www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
