Home » RDBMS Server » Performance Tuning » Collecting Stats taking long to execute in PL/SQL code
Collecting Stats taking long to execute in PL/SQL code [message #184905] Fri, 28 July 2006 09:51 Go to next message
a_developer
Messages: 194
Registered: January 2006
Senior Member
I have a procedure P1 that calls dbms_stats.gather_table_stats:
  procedure P1 ( tablename in varchar2) is
  begin
       dbms_stats.gather_table_stats('PRD_1', tabname => tablename, estimate_percent =>100, method_opt => 'for all indexed columns size auto', degree => dbms_stats.default_degree ,cascade => true);
  end;

It is called by another procedure P2:
  procedure P2 is
  begin
       . . .
       P1 ('ACCOUNT');
       . . .
  end;

Please don't ask me why the need for P1 instead of directly calling DBMS_STATS.gather_table_stats Smile That's how 'they' want it..

ACCOUNT has 3.5M of records. When I run P2, it's taking so much time.. I aborted it after 2 hours. While if I ran it separately using an anonymous block like below, it just takes 2 minutes!
begin
     P1 ('ACCOUNT');
end; 

What's the problem here??? Why it's taking so long to finish in P2????

[Updated on: Fri, 28 July 2006 09:53]

Report message to a moderator

Re: Collecting Stats taking long to execute in PL/SQL code [message #184949 is a reply to message #184905] Fri, 28 July 2006 18:17 Go to previous messageGo to next message
wagnerch
Messages: 58
Registered: July 2006
Member
What else is in P2? The way you presented the code it seems to imply it is analyzing other tables.

SQL> host cat t.sql
CREATE TABLE t1 AS
WITH generator AS (
   SELECT --+materialize
          ROWNUM FROM all_objects
   WHERE ROWNUM <= 1582
)
SELECT ROWNUM ID, MOD(ROWNUM,100000) n1
FROM generator, generator;

CREATE OR REPLACE PROCEDURE p1 (
   tablename   IN   VARCHAR2
)
IS
BEGIN
   DBMS_STATS.gather_table_stats
                          (USER
                          ,tabname               => tablename
                          ,estimate_percent      => 100
                          ,method_opt            => 'for all indexed columns size auto'
                          ,DEGREE                => DBMS_STATS.default_degree
                          ,CASCADE               => TRUE
                          );
END;
/

CREATE OR REPLACE PROCEDURE p2
IS
BEGIN
   p1 ('T1');
END;
/

SET TIMING ON
BEGIN
   p2;
END;
/

BEGIN
   p1 ('T1');
END;
/

BEGIN
   DBMS_STATS.gather_table_stats
                          (USER
                          ,tabname               => 'T1'
                          ,estimate_percent      => 100
                          ,method_opt            => 'for all indexed columns size auto'
                          ,DEGREE                => DBMS_STATS.default_degree
                          ,CASCADE               => TRUE
                          );
END;
/
SQL> @t.sql

Table created.


Procedure created.


Procedure created.


PL/SQL procedure successfully completed.

Elapsed: 00:00:02.84

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.62

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.53

SQL> select count(*) from t1;

  COUNT(*)
----------
   2502724

Elapsed: 00:00:00.35
Re: Collecting Stats taking long to execute in PL/SQL code [message #185077 is a reply to message #184949] Sun, 30 July 2006 22:37 Go to previous messageGo to next message
a_developer
Messages: 194
Registered: January 2006
Senior Member
It's not analyzing other tables.. Actually before and after the call to dbms_starts.gather_table_stats, I put a message 'Start' and 'End'. It just displays 'Start' and does not display 'End'.. it's taking so much time in dbms_stats.gather_table_stats:

  procedure P1 ( tablename in varchar2) is
  begin
       dbms_output.put_line ('Start');
       dbms_stats.gather_table_stats('PRD_1', tabname => tablename, estimate_percent =>100, method_opt => 'for all indexed columns size auto', degree => dbms_stats.default_degree ,cascade => true);
       dbms_output.put_line ('End');
  end; 
Re: Collecting Stats taking long to execute in PL/SQL code [message #185261 is a reply to message #184905] Mon, 31 July 2006 16:44 Go to previous messageGo to next message
wagnerch
Messages: 58
Registered: July 2006
Member
What version of Oracle do you have? Perhaps it is a bug? Perhaps there is table corruption? Perhaps it is caused by bug #3485930, or bug #3297301?

Check metalink, verify you are not affect by these bugs.
Re: Collecting Stats taking long to execute in PL/SQL code [message #185360 is a reply to message #185261] Tue, 01 August 2006 06:05 Go to previous messageGo to next message
a_developer
Messages: 194
Registered: January 2006
Senior Member
I'm using Oracle Database 10g Enterprise Edition Release 10.2.0.2.0..
Re: Collecting Stats taking long to execute in PL/SQL code [message #185447 is a reply to message #184905] Tue, 01 August 2006 16:16 Go to previous message
wagnerch
Messages: 58
Registered: July 2006
Member
Hmm, that's a new one I guess. I tested it with 10.2.0.1. This may be a stupid question, is both procedures owned by the same user? When you are executing a procedure you will run it as the OWNER of the procedure, perhaps there is another procedure with the exact same name in the OWNER's schema?

It might be worth checking.
Previous Topic: Is there enough SGA
Next Topic: Find Session with high cpu usage
Goto Forum:
  


Current Time: Fri Dec 02 20:23:35 CST 2016

Total time taken to generate the page: 0.15904 seconds