Home » RDBMS Server » Performance Tuning » DBMS_STATS Problem
DBMS_STATS Problem [message #285319] Tue, 04 December 2007 04:24 Go to next message
babuknb
Messages: 1734
Registered: December 2005
Location: NJ
Senior Member

Hi,

Actually i am taken backup (hot backup) from my production database. And successfully restored in my standby machine.

When collecting statistics from schema i am facing the below error message.

Quote:

Errors in file d:\oracle\admin\samidb\udump\samidb_ora_2440.trc:
ORA-12012: error on auto execute of job 123
ORA-06521: PL/SQL: Error mapping function
ORA-06512: at "SYS.DBMS_STATS", line 9339
ORA-06512: at "SYS.DBMS_STATS", line 9821
ORA-06512: at "SYS.DBMS_STATS", line 10005
ORA-06512: at "SYS.DBMS_STATS", line 10059
ORA-06512: at "SYS.DBMS_STATS", line 10036
ORA-06512: at line 1


Even all parameter properly configured. like

SQL> show parameter job

NAME                                 TYPE        VALUE
------------------------------------ ----------- --------------
job_queue_processes                  integer     10


Trace File information:

Quote:

Dump file d:\oracle\admin\samidb\udump\samidb_ora_2440.trc
Tue Dec 04 15:48:47 2007
ORACLE V9.2.0.1.0 - Production vsnsta=0
vsnsql=12 vsnxtr=3
Windows 2000 Version 5.0 Service Pack 4, CPU type 586
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
Windows 2000 Version 5.0 Service Pack 4, CPU type 586
Instance name: samidb

Redo thread mounted by this instance: 1

Oracle process number: 16

Windows thread id: 2440, image: ORACLE.EXE


*** SESSION ID:(21.11) 2007-12-04 15:48:47.000
*** 2007-12-04 15:48:47.000
ORA-12012: error on auto execute of job 123
ORA-06521: PL/SQL: Error mapping function
ORA-06512: at "SYS.DBMS_STATS", line 9339
ORA-06512: at "SYS.DBMS_STATS", line 9821
ORA-06512: at "SYS.DBMS_STATS", line 10005
ORA-06512: at "SYS.DBMS_STATS", line 10059
ORA-06512: at "SYS.DBMS_STATS", line 10036
ORA-06512: at line 1




May i know, what's the problem ?

[Updated on: Tue, 04 December 2007 05:39]

Report message to a moderator

Re: DBMS_STATS Problem [message #285393 is a reply to message #285319] Tue, 04 December 2007 06:44 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>>When collecting statistics from schema i am facing the below error message.
Post what you did. I mean, the exact statement used to collect statistics.
If you can afford, delete the existing statistics for the concerned table and collect again.
Re: DBMS_STATS Problem [message #285407 is a reply to message #285393] Tue, 04 December 2007 07:22 Go to previous messageGo to next message
babuknb
Messages: 1734
Registered: December 2005
Location: NJ
Senior Member

DECLARE
   v_job   NUMBER;  -- Job Number 110
BEGIN
   DBMS_JOB.SUBMIT(v_job,
                   'DBMS_STATS.GATHER_SCHEMA_STATS
                       (ownname=>''AFLMIS'', 
                        estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE, 
                        method_opt=>''FOR ALL INDEXED COLUMNS SIZE AUTO'', 
                        cascade=>TRUE);',
                   SYSDATE,
                   'TRUNC(SYSDATE)+1+0.5/24');
   COMMIT;
END;


>>If you can afford, delete the existing statistics for the concerned table and collect again.

I will try and let you know
Re: DBMS_STATS Problem [message #285410 is a reply to message #285407] Tue, 04 December 2007 07:28 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
This could be a known issue and the recent versions may have this fixed.
Instead of estimate, use compute. Early versions of Oracle 9i had some random problems with estimate when used with GATHER_SCHEMA_STATS/GATHER_DATABASE_STATS

[Updated on: Tue, 04 December 2007 07:28]

Report message to a moderator

Re: DBMS_STATS Problem [message #285416 is a reply to message #285410] Tue, 04 December 2007 07:58 Go to previous message
babuknb
Messages: 1734
Registered: December 2005
Location: NJ
Senior Member

Yes. Your correct. Just now only i found the problem.

Quote:

GATHER_SCHEMA_STATS/GATHER_DATABASE_STATS - Fixed in 9.2.0.2.1


I installed this patch. Now everything fine.

Thanks

Babu

[Updated on: Tue, 04 December 2007 07:59]

Report message to a moderator

Previous Topic: Shows difference in EXPLAIN PLAN result for same query.
Next Topic: Faceing Database Performance Related Problem
Goto Forum:
  


Current Time: Sat Dec 03 15:57:18 CST 2016

Total time taken to generate the page: 0.19653 seconds