Re: Analyze_schema - What method does it use really?

In article <36FA0C5A.EF8EE0E_at_zv.fhg.de>,   Reinhard Wahl <wahl_at_zv.fhg.de> wrote:
> Das war nicht meine Frage.
>
> Welche Methode nutzt die Prozedur wenn ich COMPUTE eingebe.
>
> Für ein COMPUTE STATISTICS läuft es mir zu schnell, da einzelne Tabellen
> mit dem ANALYZE TABLE -COMMAND viel länger dauern.
>
> Aber danke trotzdem.
>
> Reinhard
>

I apologize for misunderstanding your question. I was tired when I was reading it and it appeared to me as a syntax related question. I'll write my reply in English, because I'd like other members of the group to participate in this discussion as well (ideally someone from Oracle Corp.). a) When you generate 'ANALYZE TABLE' commands from sqlplus by using "select 'analyze table '||table_name||' compute statistics;'", then all generated SQL statements are different and have to be parsed separately. As you probably know, parsing is a complex a process and represents significant overhead in SQL processing. That is one of the reasons why Oracle has introduced the shared pool architecture in the first place. Because of this overhead, I would expect the SQL script generated this way to take at least twice as long as well written PL/SQL procedure. b) I believe that dbms_utility package body avoids using SQL layer completely, thus further reducing the overhead of 'ANALYZE TABLE' command. I believe that they are invoking a C routine from $ORACLE_HOME/bin/oracle to do the job. That would explain why dbms_utility.analyze_schema runs so much faster. Of course, I don't work for Oracle Corp and I have no way of knowing for sure, because prvtutil.plb in wrapped and thus unreadable. c) I've never had any problems with the results of dbms_utility.analyze_schema and I've always relied upon it completely. If anyone has different experience proving that dbms_utility is messed up, I would certainly like to know that. Mladen Gogala

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Thu Mar 25 1999 - 20:07:32 CST